Microsoft SQL Server Analysis Services

This article explains how to connect Microsoft SQL Server Analysis Services to Exasol.

Exasol takes no responsibility for changes in functionality or terms of use for third-party tools. For more details about the compatibility and use of these tools with Exasol, refer to the respective software vendor’s website.

Introduction

Microsoft SQL Server Analysis Services is an analytical data engine used in decision support and business analytics. It provides semantic data models for business reports and client applications such as Power BI, Excel, SQL Server Reporting Services, and other data visualization tools. Analysis Services is a .NET application and requires an ADO.NET data provider to connect to Exasol.

This documentation only explains how to connect to the Exasol database and assumes that you know how to use Microsoft SQL Server Analysis Services and SQL Server Management Studio (SSMS). For more help, refer to Microsoft SQL documentation on the Microsoft Learn website.

Prerequisites

  • Microsoft SQL Server Management Studio (SSMS)and SQL Server Analysis Services (SSAS) must be installed.

  • Your Exasol SaaS database must be running.

  • The IP address of the host where the tool is running must be in the allowed IP address list.

    To learn more about how to allow traffic to your database, see Network Security.

  • The Exasol ADO.NET data provider must be installed in both your design environment and deployment environment. If the SSAS engine is running, you must restart it after installing the data provider.

    To learn how to download and install the Exasol ADO.NET data provider, see ADO.NET Data Provider.

Get the connection details

  1. On the Databases page in the Exasol SaaS web console, click on the info button  on the cluster.

  2. Copy the connection string, port, and username from the cluster details window. You will need this information when you configure the connection in the client.

    saas cluster details

  3. If you do not already have a personal access token (PAT), follow the instructions in Personal access token to create a new token. The token and the username will be used by the client to authenticate to Exasol.

    The token is only shown once and will disappear when you close or refresh the page. Make sure that you copy the token, since it cannot be retrieved if you lose it. In that case you must create a new token.

Connect SQL Server Analysis Services to Exasol

  1. In SQL Server Management Studio (SSMS), create a new data source in the Solution Explorer and select Exasol Data Provider from the installed data providers.

  2. Enter the connection string and the username and password for the Exasol database.

  3. Click on Test Connection to verify that the connection details are valid.

  4. If the connection attempt was succesful, click on OK and then Next.

    If the connection attempt was not successful, check that the connection details (connection string, username, password) are correctly entered and that the database is reachable.

  5. On the Impersonation Information tab, select Inherit and then click on Next.

  6. Click on Finish to create the new data source.

The new Exasol data source should now be displayed in the Solution Explorer.

To learn more about how to use Analysis Services, see SQL Server Analysis Services (SSAS) on the Microsoft Learn website.