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.

SQL Server

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)

SQL Server 

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.

SQL Server

Step 3

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

SQL Server

Step 4

Click the OK button to add the database.

SQL Server

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

SQL Server

Step 5

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

SQL Server

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.

SQL Server

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.

SQL Server

Step 8

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

SQL Server

Step 9

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

SQL Server

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

SQL Server

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.

SQL Server

Step 10

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

SQL Server

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.

SQL Server

Step 12

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

SQL Server

Step 13

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

SQL Server

Step 14

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

SQL Server

Click "Finish" to close the deployment wizard.

SQL Server

Step 15

Connect to the Analysis Server.

SQL Server

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

SQL Server

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

SQL Server

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.

SQL Server

Step 17

Click F5 to run the XMLA Script.

SQL Server

Step 18

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

SQL Server