Step By Step Guide To Setup Sample SSAS Multi Dimension Cube

Introduction

This article gives the step by step process to set up sample SSAS multidimensional cube which includes -

• Sample OLTP warehouse database attachment
• Cube Deployment
• Cube Processing
• Cube Browsing

Link to SQL Server installation guide

Step 1

Click this link to download SQL Server Sample AdventureWorks data warehousing model.



Step 2

Paste the downloaded AdventureWorksDW2012_Data database to the below folder.

C:\Program Files\Microsoft SQL Server\MSSQL12.MSBI_TRAINING\MSSQL\DATA (Default place where SQL Server is installed)

 

Step 3

Open SSMS and connect to the SQL Server instance where you want to attach the sample OLTP database. Right-click on the database folder -> click Attach.



Step 3

Click Add and browse to the folder where you pasted the database. Select the database and click OK.



Step 4

Click the OK button to add the database.



Once the database is attached successfully, you can see the AdventureWorkDW2012 database.



Step 5

Download the sample SSAS Solution attached to this article. Open the solution from SSDT Tool.



Step 6

Double click on the AdventureWorks DW2012.ds Data Source -> click Edit in the Connection string. Configure your SQL Server Connection and DW Database. Click "Test Connection >> OK.



Step 7

Right-click on the Solution Explorer -> Build.

Once the build is successfully completed, it will create a ".asdatabase" file in the Bin folder.

Step 8

Go to Start menu -> Microsoft SQL Server 2014 -> click Deployment wizard.



Step 9

This is just an introduction to Analysis Services Deployment wizard. Click "Next".



Browse and select (.asdatabase) file in the Bin folder which will get created after the successful building of SSAS Solution.

Step 9

Provide the Analysis Server instance where you are going to deploy the cube. Enter database in which you are going to deploy SSAS Cube. If the database already exists, the deployment wizard will overwrite the database.



Step 10

Select any option based on your requirement. I go with default options. Click "Next".

Step 11

Impersonation is an account used by Analysis Services to read the data from OLTP Database.

Create one Windows account and give full access to that account to read OLTP database or add an account on which SSAS Service is running and give full permission to that account for OLTP Database.

Here, I have used my username and password in Impersonation information.

Step 12

Click processing option as none because I always prefer to do processing in SSMS XMLA Query.



Step 13

If you want, you can create the Deployment Script. In future, you can run that script to do the deployment.



Step 14

This wizard will take some time to deploy the cube structure in to Analysis Server database. Once deployment is completed, click Next.



Click "Finish" to close the deployment wizard.



Step 15

Connect to the Analysis Server.



If you see, there are two cubes available under AWD Database.

Right-click on the cube and click Browse. You are not able to browse the cube because I deployed the cube with none processing.



Step 16

In order to process the cube, right-click on the Database then click Process. A new wizard will open; click the Script button which will create XMLA Script to process the Cube database.



Step 17

Click F5 to run the XMLA Script.



Step 18

Once the cube process is completed successfully, you can browse the cube.