Microsoft Azure Analysis Services Tabular

Owned by Robert Gomez (Deactivated)
Last updated: Oct 01, 2018 by Alexandra Verlaan (Deactivated)
5 min read

Introduction

The Analysis Services Tabular OLAP adapter creates a direct connection to Microsoft Azure Analysis Services by creating a model. 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.


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.



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


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

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. 

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.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. 


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.

  


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



Creating Calculated Columns

In a tabular model, calculated columns are just references to certain columns within the specified SQL table. For example, if our excel spreadsheet has a column for 'Date', but not broken down by Months/Years, then we could use calculated columns to create different dimensions for those that will be used within CXO.

On the Azure web designer, click the 3 dots next to your table, and select 'Calculated Columns'. Seen below:

Here are some examples of calculated columns we can create:


Creating Measures

Measures are necessary to create a sum calculation for a certain column. This would be the column in the Excel spreadsheet that would be the result when a certain intersection is retrieved.

Here is an example of a Measure on the Azure portal web designer

Creating Hierarchies

Using Azure model designer it is also possible to create hierarchies for flat SQL tables. A good example of a hierarchy that can be created in our example would be the 'Period' hierarchy. We started off with just a 'Date' column which could have been in the format XX/XX/XXXX. We used calculated columns to split that date into separate dimensions i.e 'Month', 'Quarter', 'Year'. Now with a hierarchy we are able to create a dimension called 'Period' which would be a simple hierarchy showing Quarters → Month. Within CXO this means we can map the Year and Period dimensions separately and within the Period, be able to see a hierarchy.

The hierarchy settings should look as so:

Note: 'Year' could have been included as part of the hierarchy, but was not included so that within CXO we would be able to have 'Year' and 'Period' as separate dimensions.

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.

Installation Prerequisites

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

See Also

Limit Metadata with MDX Filters

CONTENT