Introduction

The Analysis Services Tabular OLAP connector creates a direct connection to Microsoft Azure Analysis Services by creating a model. The following architecture picture shows the system components: Tabular models are Analysis Services databases that run in-memory or DirectQuery mode, allowing us to:

• Leverage Microsoft Cloud stack of products
• Connect to any data source directly from back-end relational data sources (ex. Excel tables)
• Create data models quickly online through the Azure portal and mapping to CXO dimensions automated.
• Quickly deploy multiple relational data sources and give it Financial structure leveraging CXO.

The following components are needed to set up a tabular environment. These can be set up as "Resources" within the Microsoft Azure portal (portal.azure.com).

1. SQL Server Database
   • This will be the place to store the flat table which we will use to run the Analysis Services model on top of.
2. Analysis Services 
   • Used to create a tabular model to reference from the flat table. Allowing us to create hierarchies, calculated columns, and map dimensions to CXO.

Image Added

Loading Flat File to SQL Database

Once you have a SQL database set up, open up Microsoft SQL Server Management Studio (SSMS). There we can follow these steps to load data directly from an Excel spreadsheet on a SQL table.

Image Added

Image Added

Now browse for a Microsoft Excel file as your Data Source.

Image Added

For Destination, we would choose 'SQL Server Native Client' so that we could add this table to a new or existing database. 

Image Added

Click Next, until you get to the screen below, in which you can designate which spreadsheets within your Excel workbook you would like to copy over. Also, you can set the destination within the SQL Database to create the table to. 

Image Added

Next and Finish. You should now see the "NEWTABLENAME" as a table under the database specified.

Creating Analysis Services Tabular Model

Now that you have a SQL table containing your Excel data, you are able to build a model on top of it. This allows to build the dimensions needed to connect to CXO. We can do this by logging on to the Azure Portal (portal.

Image Removed

azure.com), and creating a new Analysis Services Server.

After creating this resource, click on 'New Model', and fill in the analysis services details to link to the database. 

Image Added

Now that the model is set up, go on SQL Server Management Studio and look for the model and right click > Properties > Model > Set Default Mode to "Import" from Direct Query.

Image Added  Image Added

Now, back on the Azure Portal Web Designer, open up the new model.

Creating Calculated Columns

Creating Measures

Creating Hierarchies

Connectivity to CXO

The connection to Azure SSAS is managed in the CXO Source System Manager. Specify the following fields:

• Microsoft Azure Analysis Server: the name or IP address of the Analysis Services server
• Database: The name of the Azure database
• Cube: The name of the Azure SSAS model
• Username & Password: The account which has access to the Analysis Services resource on the Azure Portal.

Use the check button to test if the connection can be made.

Configure CXO to run on Azure Analysis Services Model

In order to successfully run CXO on an Azure SSAS application the following additional configuration is required.

• Dimension Mapping: all of the CXO-Core Dimensions need to be mapped
• Set the Period Frequencies table

Installation Prerequisites

You can find the Installation Prerequisites file for CXO on Azure SSAS in this article.

See Also

Limit Metadata with MDX Filters