Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Introduction

The Analysis Services Tabular OLAP connector 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


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.

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

  • No labels