Lesson 5-4 - Defining Dimension Granularity within a Measure Group

Article
12/09/2022

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Users will want to dimension fact data at different granularity or specificity for different purposes. For example, sales data for reseller or internet sales may be recorded for each day, whereas sales quota information may only exist at the month or quarter level. In these scenarios, users will want a time dimension with a different grain or level of detail for each of these different fact tables. While you could define a new database dimension as a time dimension with this different grain, there is an easier way with SQL Server Analysis Services.

By default in SQL Server Analysis Services, when a dimension is used within a measure group, the grain of the data within that dimension is based on the key attribute of the dimension. For example, when a time dimension is included within a measure group and the default grain of the time dimension is daily, the default grain of that dimension within the measure group is daily. Many times this is appropriate, such as for the Internet Sales and Reseller Sales measure groups in this tutorial. However, when such a dimension is included in other types of measure groups, such as in a sales quota or budget measure group, a monthly or quarterly grain is generally more appropriate.

To specify a grain for a cube dimension other than the default grain, you modify the granularity attribute for a cube dimension as used within a particular measure group on the Dimension Usage tab of Cube Designer. When you change the grain of a dimension within a specific measure group to an attribute other than the key attribute for that dimension, you must guarantee that all other attributes in the measure group are directly or indirectly related to new granularity attribute. You do this by specifying attribute relationships between all other attributes and the attribute that is specified as the granularity attribute in the measure group. In this case, you define additional attribute relationships rather than move attribute relationships. The attribute that is specified as the granularity attribute effectively becomes the key attribute within the measure group for the remaining attributes in the dimension. If you do not specify attribute relationships appropriately, SQL Server Analysis Services will not be able to aggregate values correctly, as you will see in the tasks in this topic.

For more information, see Dimension Relationships, Define a Regular Relationship and Regular Relationship Properties.

In the tasks in this topic, you add a Sales Quotas measure group and define the granularity of the Date dimension in this measure group to be monthly. You then define attribute relationships between the month attribute and other dimension attributes to ensure that SQL Server Analysis Services aggregates values correctly.

Adding Tables and Defining the Sales Quotas Measure Group

Switch to the Adventure Works DW 2019 data source view.
Right-click anywhere in the Diagram Organizer pane, click New Diagram, and then name the diagram Sales Quotas.
Drag the Employee, Sales Territory, and Date tables from the Tables pane to the Diagram pane.
Add the FactSalesQuota table to the Diagram pane by right-clicking anywhere in the Diagram pane and selecting Add/Remove Tables.

Notice that the SalesTerritory table is linked to the FactSalesQuota table through the Employee table.
Review the columns in the FactSalesQuota table and then explore the data in this table.

Notice that the grain of the data within this table is the calendar quarter, which is the lowest level of detail in the FactSalesQuota table.
In Data Source View Designer, change the FriendlyName property of the FactSalesQuota table to SalesQuotas.
Switch to the SQL Server Analysis Services Tutorial cube, and then click the Cube Structure tab.
Right-click anywhere in the Measures pane, click New Measure Group, click SalesQuotas in the New Measure Group dialog box, and then click OK.

The Sales Quotas measure group appears in the Measures pane. In the Dimensions pane, notice that a new Date cube dimension is also defined, based on the Date database dimension. A new time-related cube dimension is defined because SQL Server Analysis Services does not know which of the existing time-related cube dimensions to relate to the DateKey column in the FactSalesQuota fact table that underlies the Sales Quotas measure group. You will change this later in another task in this topic.
Expand the Sales Quotas measure group.
In the Measures pane, select Sales Amount Quota, and then set the value for the FormatString property to Currency in the Properties window.
Select the Sales Quotas Count measure, and then type #,# as the value for the FormatString property in the Properties window.
Delete the Calendar Quarter measure from the Sales Quotas measure group.

SQL Server Analysis Services detected the column that underlies the Calendar Quarter measure as a column that contains measures. However, this column and the CalendarYear column contain the values that you will use to link the Sales Quotas measure group to the Date dimension later in this topic.
In the Measures pane, right-click the Sales Quotas measure group, and then click New Measure.

The New Measure dialog box opens, containing the available source columns for a measure with a usage type of Sum.
In the New Measure dialog box, select Distinct count in the Usage list, verify that SalesQuotas is selected in the Source table list, select EmployeeKey in the Source column list, and then click OK.

Notice that the measure is created in a new measure group named Sales Quotas 1. Distinct count measures in SQL Server are created in their own measure groups to maximize processing performance.
Change the value for the Name property for the Employee Key Distinct Count measure to Sales Person Count, and then type #,# as the value for the FormatString property.

Browsing the Measures again in the Sales Quota Measure Group by Date

On the Build menu, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, click the Browser tab in Cube Designer for the SQL Server Analysis Services Tutorial cube, and then click the Reconnectbutton.
Click the Excel shortcut, and then click Enable.
In the PivotTable Field List, expand the Sales Quotas measure group, and then drag the Sales Amount Quota measure to the Values area.
Expand the Sales Territory dimension, and then drag the Sales Territories user-defined hierarchy to Row Labels.

Notice that the Sales Territory cube dimension is not related, directly or indirectly, to the Fact Sales Quota table, as shown in the following image.

In the next series of steps in this topic you will define a reference dimension relationship between this dimension and this fact table.
Move the Sales Territories user hierarchy from the Rows Labels area to the Column Labels area.
In the PivotTable Field list, select the Sales Territories user-defined hierarchy, and then click the down arrow to the right.
In the filter, click the Select All checkbox to clear all the selections, and then choose just North America.
In the PivotTable Field List, expand Date.
Drag the Date.Fiscal Date user hierarchy to Row Labels
On the PivotTable, click the down arrow next to Row Labels. Clear all of the years except for FY 2008.

Notice that only the July 2007 member of the Month level appears, instead of the July, 2007, August, 2007, and September, 2007 members of Month level, and that only the July 1, 2007 member of the Date level appears, instead of all 31 days. This behavior occurs because the grain of the data in the fact table is at the quarter level and the grain of the Date dimension is the daily level. You will change this behavior in the next task in this topic.

Notice also that the Sales Amount Quota value for the month and day levels is the same value as for the quarter level, $13,733,000.00. This is because the lowest level of data in the Sales Quotas measure group is at the quarter level. You will change this behavior in Lesson 6.

The following image shows the values for Sales Amount Quota.

Defining Dimension Usage Properties for the Sales Quotas Measure Group

Open Dimension Designer for the Employee dimension, right-click SalesTerritoryKey in the Data Source View pane, and then click New Attribute from Column.
In the Attributes pane, select SalesTerritoryKey, and then set the AttributeHierarchyVisible property to False in the Properties window, set the AttributeHierarchyOptimizedState property to NotOptimized, and set the AttributeHierarchyOrdered property to False.

This attribute is required to link the Sales Territory dimension to the Sales Quotas and Sales Quotas 1 measure groups as a referenced dimension.
In Cube Designer for the SQL Server Analysis Services Tutorial cube, click the Dimension Usage tab, and then review the dimension usage within the Sales Quotas and Sales Quotas 1 measure groups.

Notice that the Employee and Date cube dimensions are linked to the Sales Quotasand Sales Quotas 1 measure groups through regular relationships. Notice also that the Sales Territory cube dimension is not linked to either of these measure groups.
Click the cell at the intersection of the Sales Territory dimension and the Sales Quotas measure group and then click the browse button (...). The Define Relationship dialog box opens.
In the Select relationship type list, select Referenced.
In the Intermediate dimension list, select Employee.
In the Reference dimension attribute list, select Sales Territory Region.
In the Intermediate dimension attribute list, select Sales Territory Key. (The key column for the Sales Territory Region attribute is the SalesTerritoryKey column.)
Verify that the Materialize check box is selected.
Click OK.
Click the cell at the intersection of the Sales Territory dimension and the Sales Quotas 1 measure group and then click the browse button (...). The Define Relationship dialog box opens.
In the Select relationship type list, select Referenced.
In the Intermediate dimension list, select Employee.
In the Reference dimension attribute list, select Sales Territory Region.
In the Intermediate dimension attribute list, select Sales Territory Key. (The key column for the Sales Territory Region attribute is the SalesTerritoryKey column.)
Verify that the Materialize check box is selected.
Click OK.
Delete the Date cube dimension.

Instead of having four time-related cube dimensions, you will use the Order Date cube dimension in the Sales Quotas measure group as the date against which sales quotas will be dimensioned. You will also use this cube dimension as the primary date dimension in the cube.
In the Dimensions list, rename the Order Date cube dimension to Date.

Renaming the Order Date cube dimension to Date makes it easier for users to understand its role as the primary date dimension in this cube.
Click the browse button (...) in the cell at the intersection of the Sales Quotas measure group and the Date dimension.
In the Define Relationship dialog box, select Regular in the Select relationship type list.
In the Granularity attribute list, select Calendar Quarter.

Notice that a warning appears to notify you that because you have selected a non-key attribute as the granularity attribute, you must make sure that all other attributes are directly or indirectly related to the granularity attribute by specifying them as member properties.
In the Relationship area of the Define Relationship dialog box, link the CalendarYear and CalendarQuarter dimension columns from the table that underlies the Date cube dimension to the CalendarYear and CalendarQuarter columns in the table that underlies the Sales Quota measure group, and then click OK.

Note

The Calendar Quarter is defined as the granularity attribute for the Date cube dimension in the Sales Quotas measure group, but the Date attribute continues to be the granularity attribute for the Internet Sales and Reseller Sales measure groups.
Repeat the previous four steps for the Sales Quotas 1 measure group.

Defining Attribute Relationships Between the Calendar Quarter Attribute and the Other Dimension Attributes in the Date Dimension

Switch to Dimension Designer for the Date dimension, and then click the Attribute Relationships tab.

Notice that although Calendar Year is linked to Calendar Quarter through the Calendar Semester attribute, the fiscal calendar attributes are linked only to one another; they are not linked to the Calendar Quarter attribute and therefore will not aggregate correctly in the Sales Quotas measure group.
In the diagram, right-click the Calendar Quarter attribute and then select New Attribute Relationship.
In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Quarter. Set the Related Attribute to Fiscal Quarter.
Click OK.

Notice that a warning message appears stating that the Date dimension contains one or more redundant attribute relationships that may prevent data from being aggregated when a non-key attribute is used as a granularity attribute.
Delete the attribute relationship between the Month Name attribute and the Fiscal Quarter attribute.
On the File menu, click Save All.

Browsing the Measures in the Sales Quota Measure Group by Date

On the Build menu, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, click the Browser tab in Cube Designer for the SQL Server Analysis Services Tutorial cube, and then click Reconnect.
Click the Excel shortcut, and then click Enable.
Drag the Sales Amount Quota measure to the Values area.
Drag the Sales Territories user hierarchy to the Column Labels, and then filter on North America.
Drag the Date.FiscalDate user hierarchy to the Row Labels, and then click the down arrow next to Row Labels on the PivotTable, and clear all check boxes other than FY 2008, to display only fiscal year 2008.
Click OK.
Expand FY 2008, expand H1 FY 2008, and then expand Q1 FY 2008.

The following image shows a PivotTable for the SQL Server Analysis Services Tutorial cube, with the Sales Quota measure group dimensioned correctly.

Notice that each member of the fiscal quarter level has the same value as the quarter level. Using Q1 FY 2008 as an example, the quota of $9,180,000.00 for Q1 FY 2008 is also the value for each of its members. This behavior occurs because the grain of the data in the fact table is at the quarter level and the grain of the Date dimension is also at the quarter level. In Lesson 6, you will learn how to allocate the quarterly amount proportionally to each month.

Next Lesson

Lesson 6: Defining Calculations

Share via