Deploying An Analysis Services Project (SSAS)

Introduction

I assume that you know the basic concepts of the Dara warehouse and are familiar with:

• Fact Table
• Dimensions Table
• Fact Table and Dimension table Relation (one to many)

Make sure you have installed it.

• Visual Studio with SSDT (SQL Server Data Tools) which can be downloaded from here.
• SQL Server

To get started with the SSAS project, you should be clear about the business requirements. 

Determine Business goals

Raise Business Analysis Questions like

• What products are profitable?
• Who are our customers, and what and how are they buying?
• What accounts are most profitable? What is the performance of each distribution channel?
• Is there still a seasonal variance in the business?

Identifying Required Business Facts

• Sales
• Units
• Change in the sales, compared to the previous period.
• Percent change in the sales, compared to the previous period.
• Change in the sales, compared to the previous period.
• Percent change, compared to the previous period.

Designing a Logical Data Model for Global Computing

• Identify dimensions.
• Identify levels.
• Identify hierarchies.
• Identify stored measures.

Design your Schema whatever it is

• Star Schema.
• Snowflake Schema.
• Galaxy Schema.

In this article, we are going to use a case scenario provided by ORACLE.

Create a Table and load the data in it, start the schema according to the scenario, which will look as shown below.

Our design is ready.

Load data in the tables.

Open Visual Studio.

Select Analysis Service Project.

Type name, location and click OK.

A new Project will look as shown below:

Right-click Data Source and Create New Data Source

Click New

Type the Server name and select the database, as shown below.

A new connection has been created

Hit Next,

Select the Service account to avoid the formation of impersonation information.

Click Finish and a new data source will appear in Solution Explorer,

The next step is to create a Data source.

Right-click Data Source Viewer and create a new one.

Select an existing Data Source View,

Click Next.

Select according to your database design and click Next.

Your database table will appear, add it to project using the>> button,

Finish the wizard by clicking Finish.

A database design will appear on the screen. If you have already created Dimension table P.K, in relation to Fact Table F.K. relation, then it's fine, otherwise Visual studio allows us to set Primary key and Foreign key relation, which can be done by dragging key attribute of Fact table(Many) to key attribute of Dimension table (one).

My final star schema will look as shown below:

The next step is to create the dimensions.

Use the existing table and create a new dimension for each Dimension table and not the Fact table.

I am going to show only dimensions, created on only one table,

Select the Dimension table, P.K of Table, and click Next.

The table relates to the Dimension table and will automatically detect. Click Next and select the related table.

Select the attribute you want to involve in an analysis.

Click Next and Finish.

Note

If you don’t have a time dimension in your database schema, you can create a time dimension directly from here.

Select Create New Dimension and select time dimension and on the next step don’t forget to choose Generate schema now.

Now the last and final step is to create a cube,

 

Select the Suggest button and it will detect your Fact table.

Click Next and select the tables you wanted to involve in an analysis and finish the wizard,

Right-click the cube and process it.

Click Process and on the next screen click Run.

On the next screen, you may get an error shown below.

This error arises because an analysis Service is trying to connect to our OLAP Service account, which is not created by default.

To solve this error, go to SQL Server and connect to the database Server.

Enter the login name NT SERVICE\MSSQLServerOLAPService, as shown below:

Go to Server role, select System Admin, and public.

A new user will be created.

Now process the cube again.

If you still get an error; make sure that you have created the correct relation between the Fact table and the Dimension table. Repair your data source, view create dimension again, and process the cube again.

Select the Browser from the top right corner of the tab, drag the attribute form, measure the Dimension table, and do an analysis, according to the requirement.

Summary

This article taught us about Deploying An Analysis Services Project (SSAS).

Reference

• Create an Analysis Services Project