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

1. Sign in to Cognos Analytics, and select Manage from the (collapsed) menu.

2. Select Data server connections, and then select the Add data server button.

3. On the Create data server connection dialog, change the connection name from the default to something else, for example, SingleStore-test.

4. From the Connection type list, select SingleStoreDB. Select Next.

   

5. Under Connection, in the JDBC URL box, enter the connection parameters for your cluster in the jdbc:singlestore://<host>:<port>/database format.

   Note

   Your SingleStore cluster's endpoint (IP address) must be externally accessible to connect with IBM Cognos Analytics Cloud.

   

6. Under Authentication, from the Method list, select Use signon.

7. Select the Add Signon button.

8. On the User ID and password dialog, enter the login credentials for your cluster. Select Done to save the credentials.

   

   To test connectivity to your cluster, select the Test connection button.

   

9. Select Next, and then select Create to save the connection configuration.

10. On the Data server connections screen, select the recently created connection. Select the vertical three-dot menu next to the connection, and select Assets.

    

11. 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,

1. On the Cognos Analytics home page, select New > Data module (from the collapsed menu).

2. On the left navigation menu, select data servers and schemas.

3. 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,

1. Assign NULL values instead of zeroes.

2. 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 cluster specific constructs when using custom SQL syntax.

Solution: Use the Pass-Through SQL type to run queries in cluster. 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.