Using a Cognos Data Module with custom SQL query
Symptoms
Customer was trying to get data using custom SQL in a data module. When opening Data Module > New Data module > Create table using SQL, the only source available was Team content. When user selected Changepoint unsecured, there was no option for creating a table.
Reason
Data Server source connection with SignOn credentials was not set up.
Resolution
In Cognos 11.1.5, customers can use data sets and data modules for their dashboards and Self-Service Analytics. This requires the Hosted team to first set up a Data Server source connection with SignOn credentials. Use the non-production environment to test first.
Do the following:
- Create SQL user account and grant the user dbo access to the Changepoint database.
- Login to Cognos Analytics as a Cognos Administrator.
- Go to Manage > Data Server Connections and then click +.
- Select Microsoft SQL Server.
- Input the SQL ServerName and port.
- Create a Signon user. Use the same SQL user account that was earlier created in Step 1.
- Go to the Schemas tab. The Changepoint objects appear.
You must create the data module by using the newly created Data Server instead of Team content.
You can create new tables in a data module that are based on a custom SQL syntax. The SQL is executed against a source that is already in the data module.
If the SQL validation is successful, the table is populated with a set of projected column names and rows of data.
- From the data module context menu , select Create table using SQL.
- In the table editor, type the table name.
- From the SQL type drop-down menu, select the native SQL.
- From the Source drop-down menu, select the source to associate the table with.
- For data server connections, select the connection name.
To edit the table SQL, from the table context menu, click Edit SQL table.
You can also edit a column expression in an SQL-based table. However, subsequent updates to the original SQL statement might overwrite the updated expression.
You can use and model SQL-based tables in the same way as other data module tables. For example, you can create relationships between this type of tables and other tables. You can also create calculations and navigation paths that include columns from these tables.
Additional notes
Using a data server source:
Creating tables using SQL:
IBM Cognos Analytics Version 11.1 Data Modeling Guide: