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.
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.
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
- You must have the ADO.NET driver installed as an Administrator. For more information, see Microsoft msiexec documentation. You can download the driver from the downloads page.
To create/add a data source, complete the following steps:
- 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.
- 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.
- On the Welcome to the Data Source Wizard page of the Data Source Wizard, click Next.
- 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.
- In the Connection Manager dialog box, define the connection properties for the data source. From Provider drop-down list, select EXASolution Data Provider.
- Define the Server (Connection String) details of your Exasol database and enter the user credentials.
- Click Test Connection to test the connection to the database, click Ok and then click Next.
- On the Impersonation Information page of the wizard, select Inherit and then click Next.
- Click Finish to create the new data source. The data source created is displayed under Data Sources in the Solution Explorer section.
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:
- 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.
- Click Next. On the Select a Data Source page, select the EXASolution Data Source and then click Next.
- On the Select Tables and Views page, select the desired tables and views from the list under Available objects for the selected data source.
- 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.
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,