Create OLAP Cube in SQL Server Analysis Services

While leaning Data Warehouse, eventually we face to creation OLAP Cube. Every newbies who want to learn creation of OLAP Cube in SSAS (SQL Server Analysis Services) using Microsoft BIDS (Business Intelligence Development Studio) Environment and Data Warehouse face with quite difficult implementation. Most students would give up, when they surface numerous failures during installation or implementation, I was one of them.
1 answer

Create First OLAP Cube in SQL Server Analysis Services

We will first take a glimpse at the basic introduction to requirement of OLAP Cube, and then create OLAP Cube in SQL Server Analysis Service by following 10 easy steps.

Before preparing OLAP Cube, we need to create and populate our data warehouse.
Creating Data Warehouse

Let us execute our T-SQL Script to create data warehouse with fact tables, dimensions and populate them with appropriate test values.

Download T-SQL script attached with this article for creation of Sales Data Warehouse or download from this article “Create First Data Warehouse” and run it in your SQL Server.

Follow the given steps to run the query in SSMS (SQL Server Management Studio).

Open SQL Server Management Studio 2008
Connect Database Engine
Open New Query editor
Copy paste Scripts given below in various steps in new query editor window one by one
To run the given SQL Script, press F5
It will create and populate “Sales_DW” database on your SQL Server

Developing an OLAP Cube

For creation of OLAP Cube in Microsoft BIDS Environment, follow the 10 easy steps given below.
Step 1: Start BIDS Environment

Click on Start Menu -> Microsoft SQL Server 2008 R2 -> Click SQL Server Business Intelligence Development Studio.
Step 2: Start Analysis Services Project

Click File -> New -> Project ->Business Intelligence Projects ->select Analysis Services Project-> Assign Project Name -> Click OK

Step 3: Creating New Data Source

3.1 In Solution Explorer, Right click on Data Source -> Click New Data Source
3.2 Click on Next
3.3 Click on New Button
3.4 Creating New connection

Specify Your SQL Server Name where your Data Warehouse was created
Select Radio Button according to your SQL Server Authentication mode
Specify your Credentials using which you can connect to your SQL Server
Select database Sales_DW.
Click on Test Connection and verify for its success
Click OK.
3.5 Select Connection created in Data Connections-> Click Next
3.6 Select Option Inherit
3.7 Assign Data Source Name -> Click Finish
Step 4: Creating New Data Source View
Select FactProductSales Table -> Click on Arrow Button to move the selected object to Right Pane.
Now to add dimensions which are related to your Fact Table, follow the given steps:
Select Fact Table in Right Pane (Fact product Sales) -> Click On Add Related Tables
Now Data Source View is ready to use.

Step 5: Creating New Cube
5.1 In Solution Explorer -> Right Click on Cube-> Click New Cube
5.2 Click Next
5.3 Select Option Use existing Tables -> Click Next
5.4 Select Fact Table Name from Measure Group Tables (FactProductSales) -> Click Next
5.5 Choose Measures from the List which you want to place in your Cube --> Click Next
5.6 Select All Dimensions here which are associated with your Fact Table-> Click Next
5.7 Assign Cube Name (SalesAnalyticalCube) -> Click Finish
5.8 Now your Cube is ready, you can see the newly created cube and dimensions added in your solution explorer

Step 6: Dimension Modification

In Solution Explorer, double click on dimension Dim Product -> Drag and Drop Product Name from Table in Data Source View and Add in Attribute Pane at left side.

Step 7: Creating Attribute Hierarchy In Date Dimension

Double click On Dim Date dimension -> Drag and Drop Fields from Table shown in Data Source View to Attributes-> Drag and Drop attributes from leftmost pane of attributes to middle pane of Hierarchy.
Drag fields in sequence from Attributes to Hierarchy window (Year, Quarter Name, Month Name, Week of the Month, Full Date UK),

Step 8: Deploy the Cube

8.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Properties
8.2 Set Deployment Properties First
In Configuration Properties, Select Deployment-> Assign Your SQL Server Instance Name Where Analysis Services Is Installed (mubin-pc\fairy) (Machine Name\Instance Name) -> Choose Deployment Mode Deploy All as of now ->Select Processing Option Do Not Process -> Click OK
8.3 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Deploy

Step 9: Process the Cube

9.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Process
9.2 Click on Run button to process the Cube

Step 10: Browse the Cube for Analysis

10.1 In Solution Explorer, right click on Cube Name (SalesDataAnalysisCube) -- > Click Browse
10.2 Drag and drop measures in to Detail fields, & Drag and Drop Dimension Attributes in Row Field or Column fields.
Now to Browse Our Cube

Product Name Drag & Drop into Column
Full Date UK Drag & Drop into Row Field
FactProductSalesCount Drop this measure in Detail area