Deploying An Analysis Services Project (SSAS)

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

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

Make sure you have installed:

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

To get started with 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, 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 to 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 Table and load the data in it, start schema according to scenario, which will look as shown below:

Schema

Our design is ready.

Load data in the tables.

Open Visual Studio.

Select Analysis service Project.

Type name, location and click OK.

Project

A new Project will look as shown below:

new

Right click Data Source and create New Data Source

Create

Create

Click New 

New

Type Server name and select data base, as shown below:

Data Base

A new connection has been created

Hit Next,

Select Service account to avoid formation of impersonation information.

Information

Information

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

Solution
Next step is to create Data source.

Right click Data Source Viewer and create a new one.

Solution

Select an existing Data Source View,

View

Click Next.

Next

Select according to your database design and click Next.

Next

Your database table will appear, add it in project using >> 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:
schema
Next step is to create the dimensions.

dimensions
dimensions

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

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

dimensions

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

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

next

next

Select the attribute you want to involve in an analysis.

Click Next and Finish.

finish
Note

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

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

dimension

Now the last and final step is to create cube,

cube  

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

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.

process

Click Process and on next screen click Run.

On next screen, you may get an error shown below:

error

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.

connect

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

login

Go to Server role, select System Admin and public.

role

A new user will be created.

new user

Now process the cube again.

cube

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

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

analysis