Skip to main content

Connect with IBM Cognos Analytics

You can load your SingleStoreDB database in IBM Cognos Analytics and perform analytics and BI operations.

You need to add a data server connection to SingleStoreDB in Cognos Analytics and load the metadata for one (or more) schema(s). Once you have added a data server, you can create data modules, reports, dashboards, create a new table using SQL etc. See IBM Cognos Analytics documentation.

Prerequisites

Add a Data Server Connection to SingleStoreDB 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. Search SingleStore in the Select a type list. Select SingleStore from the search results.

  4. Change the connection name from the default to something else, for example, SingleStore-test.

  5. Under Edit SingleStore connection, enter the connection parameters for your workspace.

    cognos1.png

    Note

    On the SingleStore customer 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 SingleStore customer portal, select Workspaces > <your_workspace> > Firewall. Under IP Allowlist, select Allow from any IP address.

  6. On the Settings tab, under Authentication method, select Use the following signon.

  7. Select the Add Signon (+) button.

  8. On the Credentials tab, enter the login credentials for your workspace. To test connectivity to your workspace, select the Test button.

    Select Save.

    cognos2.png
  9. On the Schemas tab, load metadata for your schemas. Use Load options to modify Load metadata settings.

    cognos4.png

Create a New Data Module

To connect to a SingleStoreDB 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: SingleStoreDB 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 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.