SSAS Tutorial: OLAP CUBE

SQL Server Analysis Services (SSAS) is a Microsoft tool used for building multidimensional models like OLAP cubes and performing data mining. These cubes allow fast, complex analytical and ad-hoc queries on large datasets. In this article, you will get an understanding of SSAS and OLAP Cube. So, let’s dive in.

Table of Contents

• • What is SSAS?
    • Key Features of SSAS
    • Why do we use SSAS?
  • What is OLAP Cube? Uses of OLAP Cube
    • How to create an OLAP Cube in SSAS
  • Conclusion

What is SSAS?

SSAS is a Microsoft Business intelligence tool for creating Online analytical processing and data mining functionality. We can create an OLAP (online analytical processing)  cube using the SQL server analysis system.

Key Features of SSAS

• • Ease of use with many wizards and designers
  • Data model creation and management is flexible
  • OLAP handling from scalable architecture
  • Customize application from extensive support

Why do we use SSAS

• • Speed
  • Shared metadata
  • Security
  • Multidimensional analysis
  • Avoid resource contention with the source system
  • Consolidate from multiple sources

What is OLAP  Cube? Uses of OLAP Cube

It is a technology that stores the data in an optimized way to easily fetched from different types of complex queries by using various measures and dimensions. We can develop the OLAP cube  using the BIDS (business intelligence  development studio)

Follow the below steps to run the query in SSMS (SQL Server management studio)

• • Open SSMS 2008
  • Connect the database engine
  • Open new query editor
  • Paste the SQL script here
  • Press F5 to run the script
  • It will create and populate the “Sales_DW” database.

How to Create an OLAP Cube in SSAS

Step 1: Start a Business intelligence development studio environment (BIDS)

Start menu –> Microsoft SQL Server 2008 R2  –>Select SQL Server BIDS

Step 2: Create a New Analysis Services Project

SSAS can create physical tables in the data source it will use to occupy the dimension maintained in the SSAS databases, On the specific source information.

 File–> New –> Project –> Business Intelligence projects –> select Analysis service project –> give  project name –> Click OK .

Step 3: Creating a New Data Source

Right-click on Data source –> Click  New Data Source

Click on the next new button. Create a new connection

• •  Select the SQL server name  where we created the data warehouse
  • Select server authentication mode
  • Enter your username and password  to connect SQL Serve
  • Select database Sales_DW
  • Test connection –> OK.

Select Connection created in Data connections –>next –> select option Inherit –> next –> assign data source name –> finish

Step 4: Creating a New Data Source View

We create a data source view (DSV) as an abstraction of the tables from the data source. In the solution explorer, right-click on the Data Source view then click new data source view to create the new one data source view, after that click on the next and select relational data source then next.

Right click on Data Source view –> New data Source view –> next –> Select Relational data source which we have created previously i.e., Sales_DW –> next

Shift Fact Table from available objects to Included objects

Select FactProductSales  –> Add related tables –> next –> next –> Assign Name  (SalesDW DSV)  –> Finish
Now data Source view is ready to use.

Step 5: Creating New Cube

In solution Explorer –> Cube (Right click) –> New cube –> Next –>.Select Use existing  tables –> Next –> FactProductSites –> Next –>Choose measure–>Next –> Select all the  Check box in select new dimensions –>Next –> Assign cube name –> Finish.
Now Cube is ready

Step 6: In Solution Explorer

Click on dimension DimProduct a drag and drop the product name

Step 7: Deploy the Cube

In the Solution Explorer, right click on project name –> properties –> In configuration properties, Select Deployment –> Assign  SQL Server Instance Name –> Deploy All –> Do not process –> OK.
Right-click on Project Name –> Click Deploy
We can see the  message Deployment Completed in properties

Step 8: Process the Cube

Right-click on Project Name –> Process

Click on Run to process the cube after completing the process, we can see the status as the process succeed –> close.

Step 9: Browse the cube for analysis

Right-click on the cube name à browse
Follow the steps to browse our cube

• Product Name Drag & Drop into Column
• Full Date UK Drag & Drop into Row Field
• FactProductSalesCount drag and drop this measure in the Detail field

Conclusion

By following these steps, you can create, deploy, and browse an OLAP cube in SSAS to perform advanced data analysis. This enables better insights from multidimensional data models in enterprise environments.

Learn MSBI, the easy way with our detailed cheat sheets, designed to simplify both basic and advanced concepts!

1. SSAS User Handbook
2. SSIS Cheat Sheet
3. SSIS Data Types Cheat Sheet
4. SSRS Cheat Sheet