Defining Dimension Granularity within a Measure Group

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 Microsoft SQL Server 2005 Analysis Services (SSAS).

By default in SQL Server 2005 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, 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, Defining 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 Time dimension in this measure group to be monthly. You then define attribute relationships between the month attribute and other dimension attributes to ensure that Analysis Services aggregates values correctly.

Adding Tables and Defining the Sales Quotas Measure Group

To add tables and define the Sales Quotas measure group

1. Switch to Data Source View Designer for the Adventure Works DW data source view.

2. Right-click anywhere in the Diagram Organizer pane, click New Diagram, and then specify Sales Quotas as the name for the new diagram. For more information, see Working with Diagrams in a Data Source View (Analysis Services).

3. Drag the Employee, Sales Territory, and Time tables from the Tables pane to the Diagram pane.

4. 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.

5. 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.

6. In Data Source View Designer, change the FriendlyName property of the FactSalesQuota table to SalesQuotas.

7. Switch to Cube Designer for the Analysis Services Tutorial cube, and then click the Cube Structure tab.

8. 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 Time cube dimension is also defined, based on the Time database dimension. A new time-related cube dimension is defined because Analysis Services does not know which of the existing time-related cube dimensions to relate to the TimeKey column in the FactSalesQuota fact table that underlies the Sales Quotas measure group. You will change this later in another task in this topic.

9. Expand the Sales Quotas measure group.

   Notice that three new measures are defined.

10. In the Measures pane, select Sales Amount Quota, and then set the value for the FormatString property to Currency in the Properties window.

11. Select the Sales Quotas Count measure, and then set the value for the FormatString property to #,# in the Properties window.

12. Delete the Calendar Quarter measure from the Sales Quotas measure group.

    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 Time dimension later in this topic.

13. In the Measures pane, right-click the Sales Quotas measure group, and then click New Measure. For more information, see Defining and Configuring a Measure.

    The New Measure dialog box opens, containing the available source columns for a measure with a usage type of Sum.

14. 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 Microsoft SQL Server 2005 are created in their own measure groups to maximize processing performance.

15. Change the value for the Name property for the Employee Key Distinct Count measure to Sales Person Count, and then set the value for the FormatString property to #,#.

Browsing the Measures in the Sales Quota Measure Group by Time

To browse the measures in the Sales Quota measure group by time

1. On the Build menu, click Deploy Analysis Services Tutorial.

2. When deployment has successfully completed, click the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

3. Clear all hierarchies and measures from the Data pane, and then clear the dimension member from the Filter pane.

4. Expand the Sales Quotas measure group in the Metadata pane, and then add the Sales Amount Quota measure to the data area.

5. Add the Sales Territories user-defined hierarchy in the Sales Territory dimension to the column area.

   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 task in this topic you will define a reference dimension relationship between this dimension and this fact table.

6. In the Data pane, click the down arrow next to Sales Territory Group, and then clear all check boxes except for the check box for North America, to change the dimension members that are displayed in Sales Territory Group to North America.

7. In the Metadata pane, expand Time, and then expand Fiscal.

8. Add the Time.Fiscal Time user hierarchy to the row area, and then click the down arrow next to Fiscal Year in the Data pane and clear all check boxes other than FY 2004, to display only fiscal year 2004.

9. In the Data pane, expand FY 2004, expand H1 FY 2004, expand Q1 FY 2004, and then expand July 2003.

   Notice that only the July 2003 member of the Month level appears, instead of the July, 2003, August, 2003, and September, 2003 members of Month level, and that only the July 1, 2003 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 Time 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

To define dimension usage properties for the Sales Quotas measure group

1. Open Dimension Designer for the Employee dimension, and then add the SalesTerritoryKey attribute based on the SalesTerritoryKey column in the Employee table as a hidden, non-optimized, and non-ordered attribute.

   This attribute is required to link the Sales Territory dimension to the Sales Quotas and Sales Quotas 1 measure groups as a referenced dimension.

2. In Cube Designer for the 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 Time cube dimensions are linked to the Sales Quotas and 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.

3. Define a materialized referenced relationship between the Sales Territory dimension and the Sales Quotas measure group, specifying Employee as the intermediate dimension, Sales Territory Region as the reference dimension attribute, and SalesTerritoryKey as the intermediate dimension attribute. (The key column for the Sales Territory Region attribute is the SalesTerritoryKey column.)

4. Repeat the previous step for the Sales Quotas 1 measure group.

5. Delete the Time 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.

6. In the Dimensions list, rename the Time (Order Date) cube dimension to Time (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.

7. Click the ellipsis button (…) in the cell at the intersection of the Sales Quotas measure group and the Time (Date) dimension.

8. In the Define Relationship dialog box, select Regular in the Select relationship type list.

9. 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.

   The following image shows the Define Relationship dialog box.

   

10. In the Relationship area of the Define Relationship dialog box, link the CalendarYear and CalendarQuarter dimension columns from the table that underlies the Time (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 Time (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.

11. Repeat the previous four steps for the Sales Quotas 1 measure group.

    The following image shows Calendar Quarter defined as the granularity attribute for the Time (Date) cube dimension for both the Sales Quotas and Sales Quotas 1 measure groups.

    

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

To define attribute relationships between the Calendar Quarter attribute and the other dimension attributes in the Time dimension

1. Switch to Dimension Designer for the Time dimension, and then click Dimension Structure.

2. In the Attributes pane, expand the following attributes:

   • Calendar Quarter
   • Calendar Semester
   • Fiscal Quarter
   • Fiscal Semester

   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.

3. Drag the Fiscal Quarter attribute to the <new attribute relationship> tag for the Calendar Quarter attribute.

   Notice that a warning message appears stating that the Time dimension contains one or more redundant attribute relationships that may prevent data from being aggregate when a non-key attribute is used as a granularity attribute. The warning goes on to suggest that Fiscal Quarter attribute relationship to the Month Name attribute is not needed.

4. Delete the Fiscal Quarter attribute relationship to the Month Name attribute.

   The previous warning disappears.

Browsing the Measures in the Sales Quota Measure Group by Time

To browse the measures in the Sales Quota measure group by time

1. On the Build menu, click Deploy Analysis Services Tutorial.

2. When deployment has successfully completed, click the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

   Notice that the Sales Amount Quota measure is correctly dimensioned by Sales Territory because the Sales Territory dimension is now defined as a referenced dimension.

3. Add the Date.FiscalTime user hierarchy to the row area from the Date cube dimension, and then click the down arrow next to Fiscal Year and clear all check boxes other than FY 2004, to display only fiscal year 2004.

4. Click OK.

5. Expand FY 2004, expand H1 FY 2004, and then expand Q1 FY 2004.

   Notice that the measures in the Sales Quotas measure group are correctly dimensioned. Notice also that each member of the fiscal quarter level appears, with the value for each member being the value of the quarter level. This behavior occurs because the grain of the data in the fact table is at the quarter level and the grain of the Time dimension is also at the quarter level. In Lesson 6, you will learn how to allocate the quarterly amount proportionally to each month.

   The following image shows Cube Designer for the Analysis Services Tutorial cube, with the Sales Quota measure group dimensioned correctly.

   

Note

A completed project through Lesson 5 is available by downloading and installing the updated samples. For more information, see Obtaining Updated Samples in Installing Samples.

Next Lesson

Lesson 6: Defining Calculations

See Also

Other Resources

Dimension Relationships
Defining a Regular Relationship and Regular Relationship Properties

Help and Information

Getting SQL Server 2005 Assistance