5.4 define the dimension granularity in the measurement value group

Define dimension granularity in a measurement value group :

Users may need to define fact data table dimensions of different granularities or specificity for different purposes. For example, sales data for distributors or Internet sales can be recorded once a day, while sales quota information may be recorded on a monthly or quarterly basis. In these cases, you may need time dimensions to have different granularities or levels of detail for these different fact data tables. Although new database dimensions can be defined as time dimensions with different granularities, Microsoft SQL Server 2005 Analysis Services (SSAs) provides a simpler approach.

By default, when a dimension is used in a measurement value group in SQL Server 2005 analysis services, the data granularity in the dimension is based on the key attribute of the dimension. For example, if a measurement value group contains a time dimension and the default granularity of the time dimension is daily, the default granularity of the dimension in the measurement value group is daily. This is suitable in most cases, for example, in this tutorial"Internet sales"And"Distributor sales"A measurement value group is an appropriate example. However, when other types of measurement value groups (such as "sales quota" or "budget" measurement value groups) include such dimensions, it is more appropriate to use the monthly granularity or quarterly granularity.

To specify a granularity other than the default granularity for a dimension"Dimension usage"On the dimension tab, modify the granularity attribute of the dimension of the cube used in a specific measurement value group. When you change the granularity of a dimension in a specific measurement value group to an attribute other than the key attribute of the dimension, all other attributes in the measurement value group must be directly or indirectly related to this new granularity attribute. The method is to specify the property relationship between all other attributes and the attributes specified as the granularity attribute in the measure group. In this case, you can define other property relationships rather than moving them. For other attributes in a dimension, attributes that are effectively specified as granularity attributes become the key attribute in the measurement value group. If the property relationship is not properly specified, analysis services cannot aggregate values correctly. You will see this situation in the tasks of this topic.

For more information, see And .

In each task of this topic, you add a sales quota measurement value group and define the granularity of the time dimension in the measurement value group as monthly. Define the attribute relationships between monthly attributes and other dimension attributes to ensure that analysis services correctly aggregates values.

Add a table and define a sales quota measurement group
Add a table and define a sales quota measurement group

2. Switch to the data source view designer of adventure works DW Data Source view.

4. Right-click"Graph OrganizationProgram"In the left-side Navigation Pane, click"Create a graph", And then"Sales quota"The name of the new graph. For more information, see .

6. SetEmployee,Sales TerritoryAndTimeTable slave"Table"Drag pane"Relationship Diagram"Pane.

8. Right-click"Relationship Diagram"Any position in the pane and select"Add/Delete tables"ToFactsalesquotaAdd Table"Relationship Diagram"Pane.

   Note,SalesterritoryTable passedEmployeeTable LinkFactsalesquotaTable.

10. CheckFactsalesquotaAnd then browse the data in the table.

    Note that the data granularity in this table is calendar quarter, and the granularity is detailed at the lowest level in the factsalesquota table.

12. In the data source view designerFactsalesquotaTableFriendlynameProperty changedSalesquotas.

14. Switch to the Analysis Services tutorial cube designer, and then click"Multidimensional Dataset structure"Tab.

16. Right-click"Metric value"In the left-side Navigation Pane, click"New Measurement Value group"And then click"New Measurement Value group"In the dialog boxSalesquotasAnd then click"OK".

    "Sales quota"The measurement value group will appear in"Metric value"Pane. Note that"Dimension"In the pane"Time"The database dimension defines the new"Time"Multidimensional Dataset dimension. Because Analysis Services does not know which existing time-related cube dimension andFactsalesquotaIn fact data tablesTimekeyColumn-related, so a new time-related Multidimensional Dataset dimension is defined. You can change it later in other tasks of this topic.

18. Expand"Sales quota"Measurement Value group.

    Note that three new metric values are defined.

20. In"Metric value"In the pane, select"Sales quota"In the "properties" windowFormatstringSet the attribute valueCurrency.

22. Select"Sales quota count"Measure, and then in the "properties" windowFormatstringSet the attribute value#,#.

24. Slave"Sales quota"Delete a measurement value group"Calendar quarter"Measurement Value group.

    Analysis Services detects that the column under the "calendar quarter" Metric value is a column containing the metric value. However, this column and the calendaryear column are included in the later part of this topic and will be used to link the value of the sales quota measurement value group and time dimension.

26. In"Metric value"Right-click"Sales quota"Measure Value group, and then click"New Measurement Value". For more information, see .

    Will open"New Measurement Value"Dialog box, which contains the usage type:SumThe available source column of the measurement value.

28. In"New Measurement Value"In the dialog box, select"Usage"In the list"Non-repeated count"Make sure that"Source table"Select from the listSalesquotas, In"Source column"Select from the listEmployeekeyAnd then click"OK".

    Note that"Sales quota 1"This measurement value is created in the new level value group. Non-repeated count metric values in Microsoft SQL Server 2005 are created in their own metric value groups to maximize processing performance.

30. SetEmployee key distinct countMeasure ValueNameProperty value changedSales personnel count", And thenFormatstringSet the attribute value#,#.

Browse the metric values in the sales quota measurement group by Time
Browse the metric values in the sales quota measurement group by Time

2. In"Generate"Click"Deployment Analysis Services tutorial".

4. After the deployment is complete, click"Browser"Tab, and then click"Reconnect".

6. Clear"Data"All hierarchies and measurement values in the pane, and then clear"Filter"Dimension member in the pane.

8. In"Metadata"Expand in the pane"Sales quota"Measure Value group, and then"Sales quota"Add the measurement value to the data area.

10. Set"Sales region"Dimension"Sales region"Add a user-defined hierarchy to the column area.

    Note that the dimensions of the "sales region" multi-dimensional dataset are not directly or indirectly related to the fact sales quota table, as shown in.

    In the next task of this topic, you define a reference dimension relationship between the dimension and the fact data table.

12. In"Data"In the pane, click"Sales region group"Next to the down key, and then clearNorth AmericaAll check boxes"Sales region group"The Dimension member displayed in is changedNorth America.

14. In"Metadata"In the pane, expand"Time", And then expand"Accounting".

16. SetTime. Fiscal timeAdd the user hierarchy to the row area, and then"Data"In the pane, click"Fiscal Year"Next to the down key, and clearFY 1, 2004To display only the 2004 accounting year.

18. In the "data" pane, expand FY 2004 , H1 FY 2004 , q1 FY 2004 , and July 2003 .

    Note: Only July 2003 Members of the month level are displayed, July, 2003 , August, 2003 and September, 2003 Members, and only July 1, 2003 Members, not all 31 days. This behavior occurs because the granularity of data in a fact data table is quarterly, and that of the "time" dimension is daily. You can change this behavior in the next task of this topic.

    note that the "sales quota" values of the month, day, and quarter levels are the same, both of which are $13,733,000.00. This is because the lowest data level in the "sales quota" Metric value group is the quarterly level. You can change this behavior in Lesson 6th.

    the "sales quota" value is displayed.

Define the dimension usage attribute of the "sales quota" Metric value group
Define the dimension usage attribute of the "sales quota" Metric value group

2. Open"Employee"The dimension designer of the dimension, and thenSalesterritorykeyAttribute is hidden and unordered.

   You need to use this property to link the "sales region" dimension as a reference dimension to the "sales quota" and "sales quota 1" measurement value groups.

4. In the multidimensional cube designer of the Analysis Services tutorial cube, click"Dimension usage"Tab, and then view"Sales quota"And"Sales quota 1"Dimension usage in a measurement value group.

   Note that the "employee" and "time" cubes are linked to the "sales quota" and "sales quota 1" measurement value groups through regular links. Note that the "sales region" cube dimension is not linked to any group of these metric value groups.

6. Definition"Sales region"Dimension and"Sales quota"Specify the reference relationship between measurement value groups."Employee"Is specified as an intermediate dimension"Region of the sales region"Specify as reference dimension attribute, SetSalesterritorykeySpecify as the intermediate dimension attribute. (The key column of the region attribute is the salesterritorykey column .)

8. Pair"Sales quota 1"The measurement value group repeats the preceding steps.

10. Delete"Time"Multidimensional Dataset dimension.

    If you do not use four time-related cube dimensions"Sales quota"In the measurement value group"Order Date"The Multidimensional Dataset dimension is the date on which the sales quota dimension is defined. This cube dimension is also used as the primary date dimension in the cube.

12. In"Dimension"In the list, SetTime (Order Date)Rename a cube dimensionTime (date).

    SetOrder DateRename a cube dimensionDateTo make it easier for users to understand the role of the date in this multi-dimensional dataset as the primary date.

14. Click"Sales quota"Measure Value group andTime (date)The ellipsis button (...).

16. In"Define link"In the dialog box, select"Select link type"In the list"Regular".

18. In"Granularity attribute"List, select"Calendar quarter".

    Note that a warning is displayed, indicating that non-key attributes have been selected as the granularity attribute, therefore, you must specify all other attributes as member attributes to ensure they are directly or indirectly related to the granularity attributes.

    Shown"Define link"Dialog box.

20. In"Define link"Dialog Box"Relationship"Region.CalendaryearAndCalendarquarterLink the dimension column to the table in the measurement value group of sales quota.CalendaryearAndCalendarquarterColumn, and then click"OK".

    "Calendar quarter" is defined as the granularity attribute of the time (date) Cube dimension in the "sales quota" Metric value group, however, the "date" attribute continues as the granularity attribute of the "Internet sales" and "distributor" measurement value groups.

     

22. PairSales quotas 1The measurement value group repeats the previous four steps.

    Shows the granularity attribute of the time (date) Cube dimension in the "calendar quarter" defined as "sales quota" and "sales quota 1" Metric value group.

Defines the attribute relationship between the "calendar quarter" attribute and other dimension attributes in the "time" dimension.
Defines the attribute relationship between the "calendar quarter" attribute and other dimension attributes in the "time" dimension.

2. Switch to the dimension designer of the "time" dimension, and click"Dimension Structure".

4. In"Attribute"In the pane, expand the following attributes:

   • Calendar quarter

   • Calendar half period
   • Accounting quarter
   • Accounting half period

   Note: Although "calendar year" is linked to "calendar quarter" through the "calendar half period" attribute, accounting calendar attributes are linked to each other only; they are not linked to the "calendar quarter" attribute, therefore, they are not correctly aggregated in the sales quota measurement value group.

6. Set"Accounting quarter"Drag property"Calendar quarter"Attribute"<New property relationship>"Mark.

   Note that a warning message is displayed, indicating"Time"A dimension contains one or more redundant attribute relationships. When a non-key attribute is used as a granularity attribute, these redundant attribute relationships may disable data aggregation. This warning will continue to be recommended, not required"Month name"Attribute"Accounting quarter"Attribute relationship.

8. Delete"Month name"Attribute"Accounting quarter"Attribute relationship.

   Previous warnings will disappear.

Browse the metric values in the sales quota measurement group by Time
Browse the metric values in the sales quota measurement group by Time

2. In"Generate"Click"Deployment Analysis Services tutorial".

4. After the deployment is complete, click"Browser"Tab, and then click"Reconnect".

   Note that"Sales region"A dimension is defined as a reference dimension. Therefore"Sales quota"The measurement value has been"Sales region"The dimension is correctly determined.

6. SetDate. fiscaltimeAdd User hierarchies"Date"The row area of the cube dimension, and then click"Fiscal Year"Next to the down key, and clearFY 1, 2004Only the 2004 accounting year is displayed.

8. Click OK ".

10. ExpandFY 1, 2004,H1 FY 2004AndQ1 FY 1, 2004.

    Note that the measurement value in the "sales quota" Metric value group correctly defines the dimension. Note that each member of the accounting quarter level is displayed, and the value of each member is the value of the quarterly level. This behavior occurs because the data granularity in the fact data table belongs to the quarterly level, and the granularity of the "time" dimension also belongs to the quarterly level. In Lesson 6th, you will learn how to allocate a quarterly volume to each month proportionally.

    Shows the multidimensional cube designer for the Analysis Services tutorial cube, which correctly defines the dimensions of the "sales quota" measurement value group.

Download and install the updated example to obtain the complete project used in Lesson 5th. For more information, see To obtain the updated example ".