Microsoft SQL Server Analysis Services

This article explains how to connect the Microsoft SQL Server Analysis Services (MS SSAS) tool to Exasol using ADO.Net Data provider.

Exasol takes no responsibility for any changes in functionality or terms of use for the third-party software described in this section. For more information, refer to the respective software vendor’s website.

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

Prerequisites

Procedure

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

Add Data Source

To create/add a data source, complete the following 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, see ADO.NET Data Provider.
  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 connection 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 then 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 Services project, you then need to add a data source view for the project. A data source view is a set of relational data sources 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. To add tables to the Included Objects section, do one of the following:
    • To add one or more specific tables, select desired table(s), click > and then click Next.
    • To add all tables to the Included Objects section, click >> and then 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

To set the date format in Exasol, use the ADO.Net connection string parameter onConnect to run an ALTER SESSION command which sets the date format according to the SQL Server Analysis Services setting. For example,

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