Click here to Skip to main content
15,877,573 members
Articles / Web Development / HTML

Create First OLAP Cube in SQL Server Analysis Services

Rate me:
Please Sign up or sign in to vote.
4.83/5 (281 votes)
21 Jul 2014CPOL8 min read 1M   31.8K   201   174
Learn creation of OLAP Cube in SSAS (SQL Server Analysis Services) using Microsoft BIDS (Business Intelligence Development Studio) Environment and Data Warehouse


This article is created to help those technical 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. 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. If you are totally new to Data Warehouse concepts, please take a quick look at my previous article “Create First Data Warehouse” which can give you a brief idea on data warehouse concepts which are used in this article.

Brief Introduction to OLAP Cube

What is OLAP Cube & Why do we need it?

  • An OLAP cube is a technology that stores data in an optimized way to provide a quick response to various types of complex queries by using dimensions and measures.
    Most cubes store pre-aggregates of the measures with its special storage structure to provide quick response to queries.
  • SSRS Reports and Excel Power Pivot is used as front end for Reporting and data analysis with SSAS (SQL Server Analysis Services) OLAP Cube.
  • SSAS (SQL Server Analysis Services) is Microsoft BI Tool for creating Online Analytical Processing and data mining functionality.
  • BIDS (Business Intelligence Development Studio) provides environment for developing your OLAP Cube and Deploy on SQL Server.
  • BIDS (Business Intelligence Development Studio) comes with Microsoft SQL Server 2005, 2008 (e.g. Developer, Enterprise Edition) .
  • We have to choose OLAP Cube when performance is a key factor, the key decision makers of the company can ask for statistics from the data anytime from your huge database.
  • We can perform various types of analysis on data stored in Cube, it is also possible to create data mining structure on this data which can be helpful in forecasting, prediction.

What is the difference between OLAP and OLTP?

Online Transaction Processing (OLTP) Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your application Designed to hold historical data for analyses and forecast business needs
Holds daily Latest Transactional Data related to your application Data is consistent up to the last update that occurred in your Cube
Data stored in normalized format Data stored in denormalized format
Databases size is usually around 100 MB to 100 GB Databases size is usually around 100 GB to a few TB
Used by normal users Used by users who are associated with the decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less. CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount of data is very large, it can impact the reporting performance. Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
T-SQL language used for query MDX is used for querying on OLAP Cube

Basic Architecture

In our case, data warehouse is used as a source of data to Cube in BIDS. Once Cube gets ready with data, users can run queries on Cube created in SSAS. SSRS Reports and Excel Pivoting/Power Pivot can use OLAP Cube as source of data instead of OLTP database to get performance for resolving Complex Queries.

SSRS Reports, Excel Power Pivot can be used for visualization/analysis of data from cube.

Image 1


X-Mart is having different malls in our city, where daily sales take place for various products. Higher management is facing an issue while decision making due to non availability of integrated data they can’t do study on their data as per their requirement. So they asked us to design a system which can help them quickly in decision making and provide Return on Investment (ROI).

So as a part of the design, we had completed designing of First Data Warehouse in my previous article. Now we have to Design / Create OLAP Cube in SSAS, on which our reports can do a quick query and we can also provide self service BI capability to users later on.

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

  1. Open SQL Server Management Studio 2008
  2. Connect Database Engine
  3. Open New Query editor
  4. Copy paste Scripts given below in various steps in new query editor window one by one
  5. To run the given SQL Script, press F5
  6. 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.

 Image 2

Step 2: Start Analysis Services Project

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

Image 3

Step 3: Creating New Data Source

3.1 In Solution Explorer, Right click on Data Source -> Click New Data Source

Image 4

3.2 Click on Next

Image 5

3.3 Click on New Button

Image 6

3.4 Creating New connection

  1. Specify Your SQL Server Name where your Data Warehouse was created
  2. Select Radio Button according to your SQL Server Authentication mode
  3. Specify your Credentials using which you can connect to your SQL Server
  4. Select database Sales_DW.
  5. Click on Test Connection and verify for its success
  6. Click OK.

Image 7

3.5 Select Connection created in Data Connections-> Click Next

Image 8

3.6 Select Option Inherit

Image 9

3.7 Assign Data Source Name -> Click Finish

Image 10

Step 4: Creating New Data Source View

4.1 In the Solution Explorer, Right Click on Data Source View -> Click on New Data Source View

Image 11

4.2 Click Next

Image 12

4.3 Select Relational Data Source we have created previously (Sales_DW)-> Click Next

Image 13

4.4 First move your Fact Table to the right side to include in object list.

Image 14

Select FactProductSales Table -> Click on Arrow Button to move the selected object to Right Pane.

4.5 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

Image 15

4.6 It will add all associated dimensions to your Fact table as per relationship specified in your SQL DW (Sales_DW).

Click Next.

Image 16

4.7 Assign Name (SalesDW DSV)-> Click Finish

Image 17

4.8 Now Data Source View is ready to use.

Image 18

Step 5: Creating New Cube

5.1 In Solution Explorer -> Right Click on Cube-> Click New Cube

Image 19

5.2 Click Next

Image 20

5.3 Select Option Use existing Tables -> Click Next

Image 21

5.4 Select Fact Table Name from Measure Group Tables (FactProductSales) -> Click Next

Image 22

5.5 Choose Measures from the List which you want to place in your Cube --> Click Next

Image 23

5.6 Select All Dimensions here which are associated with your Fact Table-> Click Next

Image 24

5.7 Assign Cube Name (SalesAnalyticalCube) -> Click Finish

Image 25

5.8 Now your Cube is ready, you can see the newly created cube and dimensions added in your solution explorer.

Image 26

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.

Image 27

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),

Image 28

Step 8: Deploy the Cube

8.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Properties

Image 29

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

Image 30

8.3 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Deploy

Image 31

8.4 Once Deployment will finish, you can see the message Deployment Completed in deployment Properties.

Image 32

Step 9: Process the Cube

9.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Process

Image 33

9.2 Click on Run button to process the Cube

Image 34

9.3 Once processing is complete, you can see Status as Process Succeeded -->Click Close to close both the open windows for processing one after the other.

Image 35

Step 10: Browse the Cube for Analysis

10.1 In Solution Explorer, right click on Cube Name (SalesDataAnalysisCube) -- > Click Browse

Image 36

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

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

Image 37

We will see some more features shortly - how can we assign user friendly names, named calculations, named query, ordering on hierarchy, hiding dimension attributes, creating perspective, KPI, security roles, etc.

Friends, if you liked my article, please do not forget to vote for me.

Enjoy SQL Intelligence.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Architect Cybage Software Pvt. Ltd.
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Microsoft Certified Technology Specialist with more than 16+ years of expertise to architect and implement effective solutions for Data Analytics, Reporting and Data Visualization solutioning need on Azure Cloud or On-Premise

Technology :
Azure (Data Lake, Data Factory, Synapse Analytics, Databricks, SQL),
Microsoft BI (SSIS, SSAS, SSRS, SQL-Server), C#.Net, Pentaho,
Data Warehousing, Dimension modelling, Snowflake DW, SQL DW, MySQL
Data Visualization using (Tableau, Power BI, QlikView, Pentaho),
Domain : Sales, Retail, CRM, Public Transport, Media & Entertainment, Insurance
Data Integration and Analytics Experience with MS. Dynamic CRM, Salesforce CRM, Dataverse, SAP- FI, Dynamics AX etc.

Linked In Profile:
Click Here to View Linked In Profile

Change will not come if we keep waiting for some other person !!, or keep waiting for some other time !!, We are the one we are waiting for, We are the change that we are looking for.

Comments and Discussions

GeneralRe: mistake Pin
Bùi Long Nghĩa24-Jun-14 20:17
Bùi Long Nghĩa24-Jun-14 20:17 
SuggestionRe: mistake Pin
Mubin M. Shaikh24-Jun-14 20:24
professionalMubin M. Shaikh24-Jun-14 20:24 
GeneralRe: mistake Pin
Bùi Long Nghĩa24-Jun-14 20:31
Bùi Long Nghĩa24-Jun-14 20:31 
GeneralRe: mistake Pin
Bùi Long Nghĩa24-Jun-14 20:53
Bùi Long Nghĩa24-Jun-14 20:53 
GeneralRe: mistake Pin
Mubin M. Shaikh24-Jun-14 20:56
professionalMubin M. Shaikh24-Jun-14 20:56 
GeneralRe: mistake Pin
Bùi Long Nghĩa24-Jun-14 21:00
Bùi Long Nghĩa24-Jun-14 21:00 
GeneralRe: mistake Pin
HTSoft13-Jul-14 16:17
HTSoft13-Jul-14 16:17 
GeneralRe: mistake Pin
Bùi Long Nghĩa13-Jul-14 16:25
Bùi Long Nghĩa13-Jul-14 16:25 
thanks so much
QuestionVery nice start for SSAS Pin
RonaldCastillo16-Jun-14 20:56
RonaldCastillo16-Jun-14 20:56 
QuestionCongratulations Pin
Member 983824415-May-14 9:36
Member 983824415-May-14 9:36 
GeneralVery nice article Pin
dineshkumarw15-May-14 3:13
professionaldineshkumarw15-May-14 3:13 
Questionproject server cube hold on the processing Pin
Member 1081419112-May-14 20:39
Member 1081419112-May-14 20:39 
QuestionRe: project server cube hold on the processing Pin
Mubin M. Shaikh14-May-14 1:04
professionalMubin M. Shaikh14-May-14 1:04 
QuestionOLAP cubes and Sharepoint 2013 Pin
Mike Bishop12-May-14 4:53
Mike Bishop12-May-14 4:53 
QuestionVery helpfull Pin
Member 81206977-May-14 0:14
Member 81206977-May-14 0:14 
GeneralBuilt my first cube using this article Pin
Member 108004256-May-14 20:53
Member 108004256-May-14 20:53 
QuestionRegarding MS-BI Pin
Member 107910832-May-14 11:28
Member 107910832-May-14 11:28 
GeneralMany Thanks.... Pin
sonianupam29-Apr-14 17:46
sonianupam29-Apr-14 17:46 
GeneralMy vote of 3 Pin
Shadab Shah22-Apr-14 0:28
Shadab Shah22-Apr-14 0:28 
Questionvote for you Pin
Member 1041279331-Mar-14 16:52
professionalMember 1041279331-Mar-14 16:52 
QuestionHelp Pin
Member 1003423731-Mar-14 5:43
professionalMember 1003423731-Mar-14 5:43 
GeneralMy vote of 3 Pin
Bao Tran 198230-Mar-14 18:36
professionalBao Tran 198230-Mar-14 18:36 
QuestionExcellent explanation really helpfull Pin
Pradeep Equifax23-Mar-14 1:59
professionalPradeep Equifax23-Mar-14 1:59 
QuestionExcellent explanation!!! Thank you Pin
Member 998922719-Mar-14 9:09
Member 998922719-Mar-14 9:09 
QuestionThanks, very helpfull Pin
Member 1032287516-Mar-14 6:35
Member 1032287516-Mar-14 6:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.