Creating SSAS Measure Groups
A Measure is single Numeric value (Means single column in a table) whereas a Measure Group is a collection of measures or we can simply say group of all the columns present in a table. For instance, In fact internet sale table [Sales Amount] and [Tax Amount] are the individual measures and group of all the columns are considered as Measure group.
A Measure is single Numeric value (Means single column in a table) whereas a Measure Group is a collection of measures or we can simply say group of all the columns present in a table. For instance, In fact internet sale table [Sales Amount] and [Tax Amount] are the individual measures and group of all the columns are considered as Measure group.
Creating SSAS Measure Group Example
After the completion of creating Empty OLAP Cube using Wizard we have to create Measures or Measure Groups. Before we start creating New Measure Group let us see our Measures Pane in Cube Editor
To create a new measure group, Within the Measures pane ,right-click on the cube name will open the context menu for creating Measures.
From the above context menu we have two options such as New Measure and New Measure Group. In this example we are creating measure group so please select the New Measure Group option.
NOTE: When we select the New Measure Group option then all the columns present in the table will be selected by default. Most of the times we don’t require all the columns present in the fact table, so this is very bad option to be frank.
Once you selected the New Measure Group option, New Measure Group window will be opened and it will display all the tables available in the Data Source View.
Here we are selecting FactInternetSales table from the list.
Click ok to finish configuring the SSAS Measure Group (Fact Internet Sales). Let us see the Measures Pane for the newly created Measure Group.
If you look at the above screenshot you can understand why this is bad choice. If we don’t want any measure like Revision Member then we have to remove it explicitly.
Thank you for Visiting Our Blog
After the completion of creating Empty OLAP Cube using Wizard we have to create Measures or Measure Groups. Before we start creating New Measure Group let us see our Measures Pane in Cube Editor
To create a new measure group, Within the Measures pane ,right-click on the cube name will open the context menu for creating Measures.
From the above context menu we have two options such as New Measure and New Measure Group. In this example we are creating measure group so please select the New Measure Group option.
NOTE: When we select the New Measure Group option then all the columns present in the table will be selected by default. Most of the times we don’t require all the columns present in the fact table, so this is very bad option to be frank.
Once you selected the New Measure Group option, New Measure Group window will be opened and it will display all the tables available in the Data Source View.
Here we are selecting FactInternetSales table from the list.
Click ok to finish configuring the SSAS Measure Group (Fact Internet Sales). Let us see the Measures Pane for the newly created Measure Group.
If you look at the above screenshot you can understand why this is bad choice. If we don’t want any measure like Revision Member then we have to remove it explicitly.
Thank you for Visiting Our Blog
It is always better to have seperate cube for each measure group if there are no common dimension between them. But if you have added multiple measure group in same cube and not added the common dimension in the dimension usage, SSAS gives warning message " The measure group has zero dimensional overlap with all of the other measure groups in the cube. Consider moving it to the seperate cube"
To demonstrate this, I have created cube with internetsales and internetSalesReason from AdventureworksDW database. Initially there are no common dimension and so I have received the error as showned in Fig 0.1.
 |
| Fig 0.1 |
There are no common dimension between the measure group InternetSales and InternetSalesReason as shown in Fig 0.2
 |
| Fig 0.2 |
In order to avoid this issue, we can have common dimension between these two tables by adding SalesOrderNumber as cube dimension. SalesOrderNumber is actually saved in InternetSales table itself which is a fact table and so to have this field as dimension, we need to create fact relationship.
Click on the Add dimension option from Dimension usage tab and select FactInternetSales from the list to add InternetSales as dimension.
 |
| Fig 0.3 |
Once you add this, you can see the dimension usage tab with common dimension.
Previous Warning/Error message also disappears once this common dimension is in place.
