This topic explains the steps involved in creating a custom SQL query for an Enterprise Reporting View:
Integration Studio automatically provides a database connection profile, as it downloads connection details each time its local repository is refreshed. To update (refresh) your database connection details, do the following:
If you need to use a database connection different from the default BMC TrueSight Capacity Optimization database connection, you can create a new connection profile in Integration Studio. To do this, perform the following steps:
You can view the available database connection profiles in the Database Development perspective. To display this perspective in the Integration Studio API, perform the following steps:
The Database Development perspective shows a tree view, the Data Source Explorer, listing all available connection profiles. If you double-click a particular connection profile, you get connected to that profile, and a more detailed tree view is displayed that shows all schemas related to the connection. If you click further on a schema, you can see the tables, views, and other objects it contains. This pane lets you browse the database structure.
Data Source Explorer
If you select an open database and click on the(Scrapbook) icon in Data Source Explorer, a new SQL Scrapbook open us that points to that database. Within the Scrapbook, you can select a connection profile, write and run SQL code on the selected database, and view results in the corresponding SQL Result pane.
SQL Query Builder is a graphical tool to create SQL queries. To open this tool in Integration Studio, perform the following task:
With your SQL code in the SQL Scrapbook, you can copy and use it to create a new Enterprise Reporting View within the Administration section of the Console. This view will be available for your reports.
For more information on how to create an Enterprise Reporting View, see SQL-based data marts.