Connect with IBM Cognos Analytics

Prerequisites

Access to IBM Cognos Analytics.
Hands-on knowledge of Cognos Analytics application (see IBM Cognos Analytics documentation).

Add a Data Server Connection to SingleStore in Cognos Analytics

Sign in to Cognos Analytics, and select Manage from the (collapsed) menu.
Select Data server connections, and then select the Add data server button.
On the Create data server connection dialog, change the connection name from the default to something else, for example, SingleStore-test.
From the Connection type list, select SingleStoreDB. Select Next.
Under Connection, in the JDBC URL box, enter the connection parameters for your workspace in the jdbc:singlestore://<host>:<port>/database format.
- On the Cloud Portal, select Workspaces > <your_workspace> > Connect > Connect Directly > BI Tools. On the BI Tools tab, select Others from the list. Use these connection parameters.
  
  On the Cloud Portal, select Workspaces > <your_workspace> > Firewall. Under IP Allowlist, select Allow from any IP address.
Under Authentication, from the Method list, select Use signon.
Select the Add Signon button.
On the User ID and password dialog, enter the login credentials for your workspace. Select Done to save the credentials.

To test connectivity to your workspace, select the Test connection button.
Select Next, and then select Create to save the connection configuration.
On the Data server connections screen, select the recently created connection. Select the vertical three-dot menu next to the connection, and select Assets.
On the Assets screen, select the vertical three-dot menu next to the asset (database) you want to import, and then select Load metadata to load metadata for your schema. Use Load options to modify Load metadata settings.

Create a New Data Module

To connect to a SingleStore database,

On the Cognos Analytics home page, select New > Data module (from the collapsed menu).
On the left navigation menu, select data servers and schemas.
Select the required server and database from the list. Select OK.

For more information, see Creating a data module.

Once the database is uploaded, you can view the tables in the selected database on the Grid tab. You can join the tables in the database on the Relationships tab. To create custom tables, use the Custom tables tab.

Troubleshooting

Issue: Cognos Analytics does not support zero values in date.

Solution:SingleStore supports zero values in dates (e.g. 0000-00-00), but Cognos Analytics does not. To solve this issue, you can,

Assign NULL values instead of zeroes.
Use extreme values for these dates, for example 1970-01-01 or 9999-12-31.

If you do not have access to the data, use the SQL View option and change zero values to NULL values.

Issue: Cognos Analytics may not support workspace specific constructs when using custom SQL syntax.

Solution: Use the Pass-Through SQL type to run queries in workspace. Pass-through SQL allows you to run queries without any restrictions that the data source imposes on the subqueries. See Supported SQL types for more information.

Warning

Pass-through SQL type allows a user to perform any SQL operation on the database. Hence, the database administrator must ensure that a database user has necessary permissions configured. For example, read-only permission ensures that a user does not accidentally delete data.