Mastering Enterprise BI: Working with Measure Groups - Page 6
June 12, 2006
Work with Measure Groups
Because our cube incorporates three fact tables, FactInternetSales, FactSalesQuota, and FactInternetSalesReason, which we included within the underlying Data Source View, the Cube Wizard has generated a default Measure Group for each originating fact table. (The Measure Groups are named after the tables they represent, by default.) We can verify this fact by examining the tree within the Measures pane that appears in the upper left corner of the Cube Structure tab, which itself appeared by default within the Designer when the Cube Wizard concluded its work.
1. In the Measures pane, expand the three Measure Groups, Fact Internet Sales, Fact Sales Quota and Fact Internet Sales Reason.
The expanded Measure Groups, with member measures exposed, appear as depicted in Illustration 37.
We will next examine these groups within the Cube Designer, before making alterations to suit the nature of the measures. The reason we need modifications is that the granularities of the Measure Groups are different. As an illustration, Sales Amount, a measure from the Fact Internet Sales table, is related to the actual date (the "TimeKey" field in the Time dimension) of the sale, while the Sales Amount Quota measure, from the Fact Sales Quota table, is a "budget" value, relating to the year level of the Time dimension. To separate conceptual "apples from oranges" (or, to use a more apt metaphor, "single apples from baskets of apples"), we need to ensure that our Measure Groups are dimensionally aligned to reflect their true nature.
2. Click the Dimension Usage tab atop the Cube Designer, as shown in Illustration 38.
The Dimension Usage page appears. It is here that, using the Dimension Usage settings, we can assign the relationships between the dimensions and the Measure Groups that exist within our cube. When we combined our selection of database dimensions and measures within the cube, a couple of things took place. Because we selected measures, via the Cube Wizard, from three independent fact tables, the three Measure Groups we noted earlier were created. Analysis Services attempted to determine the appropriate alignment between the Measure Groups and the dimensions by examining the relationships in the Data Source View, as well as elsewhere. It then made several Dimension Usage settings, based upon the relationships it detected. These settings appear as depicted in Illustration 39.
3. Click the ellipses ("...") button to the immediate right of the box currently containing "TimeKey," at the intersection of the Fact Sales Quota (Measure Group) column and the Dim Time (Dimension) row, as shown in Illustration 40.
The Define Relationship page appears.
4. In the selector box labeled Granularity attribute, select CalendarYear.
5. Click the selector box that appears at the intersection of the row labeled CalendarYear (under the column marked Dimension Columns) and the column marked Measure Group Columns, within the Relationship table of the Define Relationships page.
6. Select CalendarYear in the selector, as depicted in Illustration 41.
The Define Relationship page appears, with our new settings, as depicted in Illustration 42.
7. Click OK to accept settings and close the Define Relationship page.
8. Within the Dimension Usage settings, click the ellipses ("...") button to the immediate right of the box (currently unoccupied) at the intersection of the Fact Internet Sales (Measure Group) column and the Dim Time (Dimension) row, as shown in Illustration 43.
The Define Relationship page appears.
9. In the selector box labeled Select relationship type, select Regular.
10. In the selector box labeled Granularity attribute, select TimeKey.
11. Click the selector box that appears at the intersection of the row labeled TimeKey (under the column marked Dimension Columns) and the column marked Measure Group Columns, within the Relationship table of the Define Relationship page.
12. Select ShipDateKey in the selector, as depicted in Illustration 44.
(We have selected ShipDateKey for purposes of our practice exercise, as if it were organizational accounting policy to record sales as of Ship Date. This is only to make the exercise more convenient.)
13. Click OK to accept settings and close the Define Relationship page.
The Dimension Usage settings appear, with all modifications to this point, as shown in Illustration 45.
We have gained some exposure to aligning measures with dimensions. Each Measure Group, as we have seen, can be aligned differently with the dimensions of the cube. This allows us the flexibility in assigning granularity appropriately when multiple fact tables are involved.
Having created our cube, and made our adjustments to its Dimension Usage settings, we are now ready to browse the cube and verify the effectiveness of our settings. Before we can do this, we must deploy our Analysis Services solution. We will do so in the section that follows.