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:



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 Server name and select data base, as shown below:



A new connection has been created

Hit Next,

Select Service account to avoid formation of impersonation information.





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


Next step is to create 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 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:

Next step is to create the 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,



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.





Select the attribute you want to involve in an analysis.

Click Next and 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.



Now the last and final step is to create 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 next screen click Run.

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