MicroStrategy

This page outlines the steps to connect MicroStrategy to Connect AI. You can use either Connect AI Virtual SQL Server API or the Connect AI 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 Connect AI account. See Sources for more information.
• Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connect to Connect AI via Virtual SQL Server API

To establish a connection from MicroStrategy to the Connect AI 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 Connect AI data source you want to connect to (for example, Salesforce1).
   • User—enter your Connect AI username. This is displayed in the top-right corner of the Connect AI interface. For example, [e-mail].
   • 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 Connect AI via the OData endpoint, you must first do the following:

• Connect a data source to your Connect AI account. See Sources for more information.
• Create a 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.
• Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connect to Connect AI via OData Endpoint

To connect to Connect AI 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.