Creating a SQL query
This topic explains the steps involved in creating a custom SQL query for an Enterprise Reporting View:
Creating the connection profile (optional)
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:
- From the Integration Studio menu, select Window, and click Preferences
- In the left pane of the Preferences dialog box, select the Integration Studio node from the tree view, and click Refresh Local Database.
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:
- From the Integration Studio menu, select File > New, and click on Other.
- In the Select a wizard dialog box, expand the Connection Profiles node in the tree view, select Connection Profile, and click Next.
- In the New Connection Profile dialog box, select a database of your choice from the list of databases, enter a name for your new profile, enter a description for this profile (optional), and click Next.
- In the Driver and Connection Details dialog box, select your primary database driver (if not selected already) from the Drivers drop-down, enter other required details like username, password, host, SID.
- Click Test Connection to verify the connection details you just entered.
- Click Finish to create the connection.
Using the Database Development perspective and the SQL Scrapbook
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:
- From the Integration Studio menu, select Window > Open Perspective, and click Other.
- From the Open Perspective dialog-box, select Database Development.
- Click OK to add the perspective to the API.
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.
Building queries in SQL Query Builder
SQL Query Builder is a graphical tool to create SQL queries. To open this tool in Integration Studio, perform the following task:
- While working in SQL Scrapbook, right-click the edit area and click Edit in SQL Query Builder. SQL Query Builder is now open, and enables you to:
- Add tables to your database.
- Drag and drop a column from one table to another.
- Create Joins.
- Perform grouping and aggregation.
- View and modify the resulting SQL query.
- Run the query and evaluate results.
- After you are done with editing your query, click OK.
The query you just created is displayed in the SQL Scrapbook.
Creating an Enterprise Reporting view
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.