By: Siddharth Mehta
Overview
SQL Server Analysis Services (SSAS) is the Online Analytical Processing (OLAP) technology for the Microsoft Business Intelligence stack. The next step after developing the dimensional model is populating the data warehouse is to develop a data mart. In terms of implementation, we would develop a cube using the data from the AdventureWorks DW.
Explanation
Developing a cube using SSAS and understanding the concepts of the same requires elaborate explanation and hands-on exercise. To develop the cube and create a report for our business scenario, please follow the SSAS tutorial. It has the exact exercise that corresponds to our business scenario using the same set of tables that we discussed in our previous chapters. Below are the logical steps that are followed to develop any cube and generate a report:
- Identify the source tables that contains the data to be analyzed. Create appropriate views in the Data Warehouse database that can be used to expose data to the SSAS cube.
- Create a new SSAS project and select the appropriate fact and dimension tables as the data source for the cube design.
- Using the Dimension Wizard create cube dimensions, and further refine them to create appropriate hierarchies and relationships.
- Using the Cube Wizard create measures and measuregroups from the fact tables.
- After the dimensions and fact are created, they need to be deployed and processed. Processing these objects results in sourcing the data from the Data Warehouse and loading the data into the cube with the aggregation level defined during design time.
- Now this data is ready to be sourced into any reporting solution. To start with, you can use Excel to read data from this cube and create a report. Ideally you should use SQL Server Reporting Services to develop reporting solutions. We would look at reporting in the next chapter.
After you follow the step by step exercise in the SSAS tutorial, you should be able to create a Sales cube as shown below. This cube is the base for us to analyze data in different ways and then the same analysis would have to be reported in a visually intuitive manner.
Additional Information
- SSAS is a very advanced subject and needs careful study of the subject. For a detailed understanding of SSAS, consider reading SSAS Development Tips and the SSAS Tutorial.
