• Prerequisites
• Connecting to CData Connect

This page outlines the steps to connect MicroStrategy to the CData Connect Virtual SQL Server API.

Prerequisites

Before you connect, you must first do the following:

• Connect a data source to your CData Connect 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

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

1. Log into 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 data source you want to connect to (for example, Salesforce1).
   • User—enter your CData Connect username. This is displayed in the top-right corner of the CData Connect 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.