An Online Analytical Process (OLAP) cubes are used as an analytical tool. An OLAP Cube is a multi-dimensional database that is optimized for reporting purposes.
Let’s get started with Cubes in their pictorial representation. For that, I have put the below screenshot from Youtube.com
The above OLAP cube has measures, Sales Amount, Sales Cost, Sales Qty and Profit Margin. These measures are analyzed using Product Categories, Time dimensions. If we further analyze, Time Dimension, we will see that year and month are the main attributes in the Time Dimension.
In the Microsoft BI family, SQL Server Analysis Service (SSAS) supports cubes.
Let us open SQL Server Data Tools (SSDT) and create an Analysis Services Multi-Dimensional and Data Project is created as shown in the following screen.
Following is the Solution Explorer for the sample project created in the previous screen.
Let us create a data source for the sample project. We will be using the AdventureWorksDW sample database in this article.
Next is to create a data source view, from that we will be selecting the needed Fact and Dimension tables.
From the above screen, the FactInternetSales fact table and Sales Territory, Currency, Product, Customer, and Promotion dimensions are added.
There is an easy way to add all the above tables with the least number of clicks. First, select the FactInterSales fact table and move it to the right-hand side then click the Add Related Tables button. With this, another fact table, FactInternetSalesReason will be added which should be removed as it is not required in this example. After these few clicks, you will end up with the above screen.
The following screen is the star-schema for the chosen data source view.
Since foreign key constraints are implemented in these tables, relationships are automatically created. If the foreign key constraints are not implemented, you need to create the relationships manually.
Then we need to start creating the OLAP Cube. Right-click the Cube node and select the New Cube…. This will take you through a cube creation wizard.
First, you need to choose the measures columns.
Measures are the core element of the dimensional model. Measures are data values that can be aggregated as summed, averaged, minimized, etc.
Let us see how to choose a measure from the following screenshot.
As we know, FactInterSales is the measure group table. If you are not sure, which should not be the case, click the Suggest button. Suggest button will provide you the suggestion for the measure groups.
Next is to select measure columns from the following screen.
It is important to note that you have to choose only the required measure columns. If unnecessary columns are selected, it will cause delays in cube processing. In the above example, we have eliminated the Revision Number column which is not a business measure column.
In an OLAP Cube, after the measures next important concept is Dimension. Dimension is a collection of referenced information so that measures can be analyzed into detail.
From the following screen, you can choose the required dimensions and modified them as shown below.
With this basic OLAP Cube configuration is completed and you will the Project Explorer as shown in the below screenshot.
Though the cube is configuration is completed, every dimension is empty. So it is important to add attributes to the dimensions.
It is essential to add only the required attributes. Otherwise, the cube process will take longer and the cube will be larger. If the cubes are larger, cube accessing also will have a negative impact. Apart from the attributes, hierarchies can be created so that users can analyze data much effectively.
Now you are ready to process the OLAP Cube. By default, Cube will have MOLAP storage. This means all the measures and dimensions are stored in the cube after processing. Since all the data is stored in the cube, data accessing is very fast as no processing is required.
After the cube process, now the cube is ready to access. There are multiple ways to access the processed cubes.
The following screenshot shows how to access the cube using the visual studio itself.
In this, it is simply a matter of drag and drop the columns. You will be able to see the necessary data in a quick time.
One of the main methods of accessing the OLAP Cube is using an Excel pivot table. Since pivot tables are more used by the business users, they can leverage the excel features by using the cubes.
In the Excel pivot tables, you can use columns as well as rows to select dimensions. This means you can perform the ad-hoc analysis much easier.
In addition to the simple analysis, users can use created hierarchies as shown in the below screenshot.
Apart from EXCEL, SSRS, PowerBI, SSMS can be used to access the cubes. There is a special type of MDX queries which can be used to retrieve data from Cubes.
This article covers the basics of OLAP Cubes implementation using the SQL Server Analysis Service. However, there are additional features in cubes such as Calculations, KPI, Actions, Partitions, Aggregations, Perspectives, and Translation, etc. Those options need to be discussed in separate articles. Even without those advanced options, OLAP Cube is an important option that is available for the end-users.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021