Microsoft SQL Server Analysis Services

This section provides you with information on how to connect the Microsoft SQL Server Analysis Services (MS SSAS) tool to Exasol using ADO.Net Data provider.

Background

SQL Server Analysis Services is an analytical data engine used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.

A typical workflow includes creating a tabular or multidimensional data model project in Visual Studio, deploying the model as a database to a server instance, setting up recurring data processing, and assigning permissions to allow data access by end-users. When it's ready to go, your semantic data model can be accessed by client applications supporting Analysis Services as a data source.

Environment

The following environment is used as an example:

  • SQL Server Express for Windows
  • Windows Operating System
  • SQL Server Management Studio (SSMS)
  • SQL Server Analysis Services with Visual Studio

Prerequisite

You must have ADO.NET driver installed as an Administrator (open a Windows-Terminal and execute msiexec /i EXASOL_ADO.NET-<Version>.msi). You can download the drive from the Downloads page.

Procedure

To use the SQL Server Analysis Service to create a semantic data models, you need to first Add Data Source and Add Data Source View.

Add Data Source

To create/add a data source, you can follow these steps: 

  1. Install the Exasol Data Provider (part of the Exasol ADO.NET-Package) on both your design environment and your deployment environment. You must restart Analysis Services after installation.
    For more information on ADO.NET, refer the ADO.NET Data Provider section.
  2. In your multidimensional project (or Analysis Services project), in the Solution Explorer section, right click the Data Sources folder and then click New Data Source.
  3. On the Welcome to the Data Source Wizard page of the Data Source Wizard, click Next.
  4. On the Select how to define the connect page, you can either define a data source based on a new connection or based on an existing connection. Select the option Create a data source based on an existing or new connection and click New.
  5. In the Connection Manager dialog box, define the connection properties for the data source. From Provider drop-down list, select EXASolution Data Provider.
  6. Define the Server (Connection String) details of your Exasol database and enter the user credentials.
  7. Click Test Connection to test the connection to the database. Click Ok and then click Next.
  8. On the Impersonation Information page of the wizard, select Inherit and click Next.
  9. Click Finish to create the new data source. The data source created is displayed under Data Sources in the Solution Explorer section.

Add Data Source View

Once you have added the data source to the Analysis Service project, you then need to add a data source view for the project. A data source view is a set of relational data source that is the base of the cubes and dimensions you create in your project. To create/add a data source view, follow these steps:

  1. In your Multidimensional project, right click on the Data Source View in the Solution Explorer and then click New Data Source View. The Data Source View Wizard is displayed.
  2. Click Next. On the Select a Data Source page, select the EXASolution Data Source and then click Next.
  3. On the Select Tables and Views page, select the desired tables and views from the list under Available objects for the selected data source.
  4. Click >> or > arrow button to add the selected tables to the Included Objects section and click Next.
  5. Click Finish.The data source view created is displayed under Data Source Views in the Solution Explorer section.
  6. Double click this data source view to view the selected tables and the relationships as shown in the image below. You can start to build your cubes on top of the relational tables in Exasol.

Set the date format in Exasol

If you use date attributes in your hierarchies, ensure that SQL Server Analysis Services has the same date format setting as Exasol. To set the date format in Exasol use the ADO.Net connection string parameter onConnect to set the date format according to the SQL Server Analysis Services setting. For example,

ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY'