• Prerequisites for Connecting via Virtual SQL Server API
• Connecting to CData Connect Cloud via Virtual SQL Server API
  • Step 1: Preparing Visual Studio
  • Step 2: Creating the Data Model Project
  • Step 3: Importing Data
  • Step 4: Deploying the Project
• Prerequisites for Connecting via OData Endpoint
• Connecting to CData Connect Cloud via OData Endpoint

This page outlines the steps to connect Azure Analysis Services 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 performance of Azure Analysis Services is much slower with an OData connection.

Prerequisites for Connecting via Virtual SQL Server API

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

• Create a server in Azure Analysis Services to which you will deploy your data from CData Connect Cloud.
• An on-premises data gateway is required when one or more Azure Analysis Services servers in the same region connect to on-premises data sources. To install and configure an on-premises gateway to access data sources, refer to the Microsoft documentation: Install an On-Premises Gateway.
• 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 Azure Analysis Services to the CData Connect Cloud Virtual SQL Server API, follow these steps.

Note: These steps involve creating a tabular model project through Visual Studio and deploying the model to Azure Analysis Services. For further instructions on using Azure Analysis Services, please refer to Microsoft documentation.

Step 1: Preparing Visual Studio

1. Open Visual Studio. In the navigation bar, select Extensions > Manage Extensions.

2. In the search bar, enter Microsoft Analysis Services Projects 2022. When it appears in the list, click Download.

3. When the installer file finishes downloading, close Visual Studio and double-click the extension installation file to install it.

4. Follow the installation prompts. When the installation finishes, restart Visual Studio.

Step 2: Creating the Data Model Project

1. In Visual Studio, create a new project.

2. Select Analysis Services Tabular Project.

3. In Configure your new project, enter a name for your project in the Project name field.

4. Specify a location for the project files and set Framework to the latest available value.

   

5. Click Create. The Tabular model designer dialog opens.

6. Select Workspace server and enter the address of your Azure Analysis Services server (for example, asazure://eastus.azure.windows.net/myAzureServer).

7. Set Compatibility Level to SQL Server 2022 / Azure Analysis Services (1600).

   Note: The rest of this guide is written for this compatibility level. If you select a different compatibility level, the steps below may not apply, and you might encounter issues when connecting to your data.

   

8. Click Test Connection and sign in to your server.

9. Click OK to create the project. Your Visual Studio window should resemble the following screenshot:

   

Step 3: Importing Data

1. In Visual Studio, in Tabular model Explorer, right-click Data Sources and select Import From Data Source.

   

2. In Table Import Wizard, select SQL Server database and click Connect.

   

3. In the Server field, enter the Virtual SQL Server endpoint and port separated by a comma: tds.cdata.com,14333.

   

4. Click OK. In the new window, select Database.

5. Enter the following information:

   • User name—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.

     

6. Click Connect. In the Navigator window that opens, select your desired data source and click OK.

   

7. Navigator refreshes and displays the tables in your data source. Select your tables and click Load.

   

Step 4: Deploying the Project

1. In Visual Studio, select Solution Explorer.

   

2. Right-click your project and select Deploy.

   • For more information about deploying data, please see this Microsoft help page.

   • For more information about on-premises data gateways, please see this Microsoft help page.

Once you deploy your data to Azure Analysis Services, you can use your data to create data models. For more information please see Azure Analysis Services documentation.

Prerequisites for Connecting via OData Endpoint

If you do not want to use a local gateway with Azure Analysis Services, you can use an OData endpoint to connect to CData Connect Cloud. However, the performance is slower than with a Virtual SQL Server connection.

• 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.
• 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. After creating a project in Azure Analysis Services, right-click the data source option and select New Data Source.

   

2. Search for OData Feed.

   

3. In the OData feed dialog, enter the following URL: https://cloud.cdata.com/api/odata/{workspace_name}. Then click OK.

   

4. Next, enter Basic credentials. The User name is your CData Connect Cloud user name, and the Password is the PAT from the prerequisites.

   

5. Click Connect to test the new connection.

6. In the Navigator, select the desired table and click Load.

   

7. You can now view your data in the Navigator.