How To Create Data Cube In Sql Server 2008

Download SalesDataAnalysis.cipher - 53.vii KB
Download Data_WareHouse__SQLScript.zip - 8.one KB

Introduction

This commodity 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 volition first take a glimpse at the basic introduction to requirement of OLAP Cube, and so create OLAP Cube in SQL Server Analysis Service by following ten like shooting fish in a barrel steps.

Earlier preparing OLAP Cube, we need to create and populate our data warehouse. If you lot are totally new to Data Warehouse concepts, please accept a quick look at my previous commodity "Create First Information Warehouse" which can give you a cursory idea on data warehouse concepts which are used in this commodity.

Brief Introduction to OLAP Cube

What is OLAP Cube & Why do nosotros need it?

An OLAP cube is a engineering 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 terminate 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 information mining functionality.
BIDS (Business Intelligence Development Studio) provides environs for developing your OLAP Cube and Deploy on SQL Server.
BIDS (Business Intelligence Development Studio) comes with Microsoft SQL Server 2005, 2008 (e.g. Programmer, Enterprise Edition) .
Nosotros take to choose OLAP Cube when performance is a fundamental factor, the key conclusion makers of the company can ask for statistics from the information someday from your huge database.
We can perform various types of analysis on data stored in Cube, it is also possible to create information mining structure on this data which can be helpful in forecasting, prediction.

What is the deviation between OLAP and OLTP?

Online Transaction Processing (OLTP)	Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your awarding	Designed to agree historical data for analyses and forecast business concern needs
Holds daily Latest Transactional Information related to your application	Data is consistent up to the last update that occurred in your Cube
Information stored in normalized format	Data stored in denormalized format
Databases size is usually around 100 MB to 100 GB	Databases size is normally effectually 100 GB to a few TB
Used by normal users	Used by users who are associated with the conclusion making procedure, e.thousand., Managers, CEO.
CPU, RAM, HDD infinite requirement is less.	CPU, RAM, HDD infinite requirement is college.
Query response may exist slower if the amount of data is very big, it can impact the reporting performance.	Query Response is quicker, management can exercise Trend analysis on their information easily and generate quicker reports.
T-SQL linguistic communication used for query	MDX is used for querying on OLAP Cube

Basic Architecture

In our case, data warehouse is used every bit a source of data to Cube in BIDS. Once Cube gets ready with information, users can run queries on Cube created in SSAS. SSRS Reports and Excel Pivoting/Power Pivot tin 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.

Scenario

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

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

Creating Data Warehouse

Let us execute our T-SQL Script to create data warehouse with fact tables, dimensions and populate them with appropriate examination 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 1
To run the given SQL Script, press F5
Information technology will create and populate "Sales_DW" database on your SQL Server

Developing an OLAP Cube

For creation of OLAP Cube in Microsoft BIDS Surround, follow the 10 easy steps given below.

Step 1: Beginning BIDS Environment

Click on Beginning Bill of fare -> Microsoft SQL Server 2008 R2 -> Click SQL Server Business Intelligence Development Studio.

Pace 2: Start Analysis Services Project

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

Footstep 3: Creating New Data Source

3.ane In Solution Explorer, Correct click on Data Source -> Click New Data Source

iii.two Click on Side by side

3.iii Click on New Push button

iii.4 Creating New connection

Specify Your SQL Server Proper name where your Information Warehouse was created
Select Radio Button co-ordinate to your SQL Server Authentication mode
Specify your Credentials using which yous tin connect to your SQL Server
Select database Sales_DW.
Click on Examination Connection and verify for its success
Click OK.

iii.5 Select Connexion created in Information Connections-> Click Next

iii.6 Select Option Inherit

iii.7 Assign Data Source Proper name -> Click Cease

Step 4: Creating New Data Source View

4.one In the Solution Explorer, Right Click on Data Source View -> Click on New Information Source View

4.ii Click Next

4.3 Select Relational Information Source nosotros have created previously (Sales_DW)-> Click Next

iv.4 First move your Fact Tabular array to the right side to include in object list.

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

4.five 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

4.half-dozen Information technology will add all associated dimensions to your Fact tabular array as per relationship specified in your SQL DW (Sales_DW).

Click Next.

4.vii Assign Proper noun (SalesDW DSV)-> Click Finish

four.viii At present Data Source View is fix to apply.

Step 5: Creating New Cube

5.i In Solution Explorer -> Right Click on Cube-> Click New Cube

5.two Click Next

5.iii Select Option Use existing Tables -> Click Next

5.four Select Fact Table Proper noun from Measure Group Tables (FactProductSales) -> Click Next

5.5 Choose Measures from the Listing which you want to place in your Cube --> Click Side by side

5.6 Select All Dimensions hither which are associated with your Fact Table-> Click Side by side

v.vii Assign Cube Name (SalesAnalyticalCube) -> Click Stop

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

Footstep six: Dimension Modification

In Solution Explorer, double click on dimension Dim Product -> Drag and Drop Product Name from Tabular array in Information Source View and Add in Aspect Pane at left side.

Stride seven: Creating Aspect Hierarchy In Engagement Dimension

Double click On Dim Appointment 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 Proper noun, Month Proper noun, Calendar 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.ii Set up Deployment Backdrop First

In Configuration Properties, Select Deployment-> Assign Your SQL Server Instance Name Where Analysis Services Is Installed (mubin-pc\fairy) (Machine Proper noun\Example Name) -> Choose Deployment Mode Deploy All equally of now ->Select Processing Option Practice Not Procedure -> Click OK

8.3 In Solution Explorer, correct click on Projection Name (SalesDataAnalysis) -- > Click Deploy

8.iv Once Deployment volition finish, you can see the message Deployment Completed in deployment Backdrop.

Step 9: Process the Cube

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

9.2 Click on Run button to process the Cube

9.3 Once processing is consummate, yous tin can see Condition as Process Succeeded -->Click Shut to close both the open windows for processing one after the other.

Footstep 10: Browse the Cube for Analysis

ten.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 Cavalcade fields.

Now to Browse Our Cube

Product Proper name Drag & Drop into Column
Full Date United kingdom of great britain and northern ireland Drag & Drop into Row Field
FactProductSalesCount Driblet this measure in Detail area

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

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

Enjoy SQL Intelligence.