Introduction

It is possible to create a SQL Server Analysis Services (SSAS) on-premises cube using the Azure SQL Data Warehouse ASDW as the data source. In this tip, we will do the following:

• Create a Data Source - The Data Source creates a Connection to ASDW.

• Create a Data Source View - Data Source View is used to add tables or views to create an SSAS cube.

• Create a Dimension - Dimensions are part of the cube to analyze areas of interest of your reports.

• Create a Cube - The cube is a combination of measures and dimensions to create analytic reports.

• Create queries in the cube - Finally, we write queries to the cube.

Requirements

1. An Azure subscription
2. An ASDW created with an AdventureWorksLT database created (if you do not have it, check this article to create the Database)
3. SSAS on-premises installed (included in the SQL Server installer)
4. SSDT installed.

Create a Data Source

Open SSDT and go to File>New>Project:

Select the Analysis Services Multidimensional and Data Mining Project:

We will first create a New Data Source. In Solution Explorer, right click Data Sources and select New Data Source:

You will receive a Welcome message. Press next:

Select the option Create a data source based on an existing or new connection and press the new button:

In provider, select NATIVE OLE DB\SQL Server Native Client. In Server name, specify the Azure SQL Server Name (we will explain later how to get this name using the Azure Portal). In Authentication, we will use SQL Server Authentication. This is the easiest option. In the Select or enter the database name, write sqlcentralwarehouse. This is the name of the Azure SQL Database. The User name and password are defined when we create the database in Azure:

You may need to add a rule to Azure. To do this, in the Azure Portal, go to more services (>) and click on SQL Servers:

Make sure that your IP address with SSAS on-premises is added as a firewall rule. If not, press Add client IP and save the IP added:

If you do not know the name of your Azure SQL Server, in the Portal click on the Databases icon and then click on your ASDW Database:

In Overview, you can find the Azure SQL Server Name used for the connection in SSAS:

Return to SSAS on-premises and press Finish after creating the Data Source connection:

Create a Dataview

We will create a DataView. In the Data View we select the tables or views for the Data Warehouse to SSAS. In the Solution Explorer, right click Data Source Views and select New Data Source View:

Check the Don't show this page again option and press Next:

Select the data source with the connection to Azure and press Next:

SSAS cannot detect Azure primary and foreign keys. You can try to create foreign keys by checking matching columns:

We will select one Fact table (FactInternetSales) and two Dimensions (Dimcurrency and DimDate). We will only work with the Fact table and the Dimcurrency Dimension in this tutorial. FactInternetSales, will be used in a next chapter:

At the end of the Wizard, press Finish:

You will now be able to see the tables added:

If you right click on the table, you can Explore Data:

This option will show you the data available:

By default, the Azure Primary keys are not detected. We can set the primary key manually. Right click on the attribute and select Set Logical Primary Key:

To relationship between a Fact Table and a Dimension must be created manually if it is not detected by default. Drag and drop CurrencyKey from the Fact table to the dimension attribute with the same name. This will create a relationship between Fact and Dimension tables. The information will be used to create the cube in the future:

Create a Dimension

The dimension will help us to analyse the reports based on the dimension specified. For example, if I want to analyse the total sales I can analyse the data per year or month using a time dimension.

In this example, we will be able to analyse Unit Prices, Sales Amounts by Currency. We will create a New Dimension based on the Table DimCurrency added in the Data Source View. In Solution Explorer, right click and select New Dimension:

Check the option don't show this page again:

There are nice options to create time dimensions. In this case it is a currency dimension, so we will generate the dimension based on an existing table:

Make sure that the CurrencyKey is the key column. If it is not selected by default, use the Add key column:

Select the attributes that you want to display:

Press Finish:

In the Dimension Structure tab, drag and drop Currency Name to Hierarchy. Here you defining which attributes of the dimension will be visible:

Press the process icon:

It may ask you for a password. This means that the impersonation must be configured. Write any password:

You may receive an error message when trying to process:

It is a typical problem for newbies to receive an error message in SSAS after trying to process the cube. To solve this problem, in Solution Explorer, double click the Data Source:

Check the connection string if the credentials are OK:

Click on Impersonation Information tab. Select the option Inherit. This option will help you to process the cube:

If you try again to process the dimension, you will be able to process successfully:

When you press Browser, you will be able to check the dimension structure for reports:

This is the information of the dimension displayed:

Create Cubes

The cube is the combination of measures and dimensions. You can create queries on it. It is like a multidimensional table. In Solution Explorer, right click Cubes and select New Cube:

Check the Don't show this page again in the Welcome wizard and press Next:

You can create cubes based in existing tables, create an empty cube (and add data later), or generate tables based on templates:

We can create measure based on Fact tables or Dimensions. The information usually comes from fact tables, but sometimes we want to measure some information from dimension also. In this example, we will create measure groups of the FactInternetSales. The measure groups are groups to measure things like the Amount of sales, Discounts and other enterprise information that we want to measure:

The Measures are the units that you want to add in the cube. For example, the order quantity, Unit Price, Extended Amount. Add this measures to the cube:

The existing dimensions will be detected based on the relationship created in the Data Source View. If the relationship is not detected between the fact table and the dimensions, they must be created manually as we did when we created the Data Source View. Select the Dim Currency dimension:

Press the Finish button:

The cube created will be similar to this one:

Press the Process icon:

If it is the first time, you will be asked to deploy the project. Press yes:

Press Run to start processing:

The process will upload the data and create aggregations in the cube:

Once that the cube is created go to Browser to create queries in the cube:

Go to Measures>Fact Internet Sales drag and drop from Order Quantity and Extended Amount to the design pane:

In Dim Currency drag and drop Currency Name to the design pane:

As you can see, you can see the Order Quantity and Extended Amount,

Conclusions

In this article, we show how to extract information from Azure to a SSAS on-premises. Basically, it is similar to a connection to a SQL Server database on-premises, but you need to get the Server name and change the impersonation information to inherit.

We used a SQL Authentication to connect to Azure.

After the connection, you will need to manually create primary keys and relationships. After those steps, the cube creation is like a Database on-premises.

References

• What is Analysis Services?
• Analysis Services Tutorial Scenario
• Dimensions - Introduction