Search

MicroStrategy

MicroStrategy

This page outlines the steps to connect MicroStrategy to CData Connect Cloud. You can use either CData Connect Cloud Virtual SQL Server API or the CData Connect Cloud OData endpoint to connect. Note that the Virtual SQL Server API performs better for large datasets, while OData performs well with smaller datasets.

Prerequisites for Connecting via Virtual SQL Server API

Before you connect via Virtual SQL Server API, you must first do the following:

  • Connect a data source to your CData Connect Cloud account. See Connections for more information.
  • Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connecting to CData Connect Cloud via Virtual SQL Server API

To establish a connection from MicroStrategy to the CData Connect Cloud Virtual SQL Server API, follow these steps.

  1. Log in to MicroStrategy.

  2. Click Add External Data > Databases.

  3. Set the Import Option to Select Tables.

  4. In the Import from Tables wizard, click “+” to add a new data source.

  5. Set the connection properties.

    • Database—select SQL Server
    • Version—select SQL Server 2017
    • Server Name—enter tds.cdata.com
    • Port Number—enter 14333
    • Database Name—enter the Connection Name of the CData Connect Cloud data source you want to connect to (for example, Salesforce1).
    • User—enter your CData Connect Cloud username. This is displayed in the top-right corner of the CData Connect Cloud interface. For example, test@cdata.com.
    • Password—enter the PAT you generated on the Settings page.
    • Data Source Name—enter a name for the data source

  6. Click Save.

  7. Right-click on the new data source and choose Edit Catalog Options.

  8. Edit the SQL column retrieval query to include TABLE_SCHEMA = '#?Schema_Name?#' in the WHERE clause. The complete query is below.

     SELECT DISTINCT 
 TABLE_SCHEMA NAME_SPACE, 
 TABLE_NAME TAB_NAME, 
 COLUMN_NAME COL_NAME, 
 (CASE 
     WHEN 
     (DATA_TYPE LIKE '%char' AND (CHARACTER_SET_NAME='utf8' OR CHARACTER_SET_NAME='usc2')) 
     THEN 
     CONCAT('a',DATA_TYPE) 
     ELSE 
     DATA_TYPE 
 END) DATA_TYPE, 
 CHARACTER_MAXIMUM_LENGTH DATA_LEN, 
 NUMERIC_PRECISION DATA_PREC, 
 NUMERIC_SCALE DATA_SCALE 
 FROM 
 INFORMATION_SCHEMA.COLUMNS 
 WHERE 
 TABLE_NAME 
 IN 
 (#TABLE_LIST#) AND TABLE_SCHEMA='#?Schema_Name?#' 
 ORDER BY 
 1,2,3

  9. Click Apply > OK.

  10. Select the new data source and select the Namespace that corresponds to your virtual database (like SharePoint1).

  11. Drag tables into the pane to insert them.

  12. Click Finish and choose the option to connect live.

  13. Click Create Dossier.

  14. You can now create a new visualization of your connected data.

Prerequisites for Connecting via OData Endpoint

Before you connect to CData Connect Cloud via the OData endpoint, you must first do the following:

  • Connect a data source to your CData Connect Cloud account. See Connections for more information.
  • Create a virtual dataset Workspace. Later, you will use the OData Service URL on that page (https://cloud.cdata.com/api/odata/{workspace_name}) with the relevant workspace name to establish the connection. See Virtual Datasets for more information.
  • Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connecting to CData Connect Cloud via OData Endpoint

To connect to CData Connect Cloud using the OData protocol, follow these steps.

  1. Log in to MicroStrategy.

  2. Click Add Data.

  3. In Data Sources, select Data from URL.

  4. In the Data from URL dialog, set the connection properties.

    • In the text box, paste the OData URL copied from the Endpoints dialog of your workspace. The URL is in the following format: https://cloud.cdata.com/api/odata/{workspace_name}.
    • Select OData from the drop-down list.

  5. Click Add to add the new connector.

  6. Click Prepare Data to see a preview of your data.

  7. Click Finish when you are done adding your connector(s).

  8. You can now create a new visualization of your connected data.