Mastering Enterprise BI: Working with Measure Groups - Page 7

June 12, 2006

Verification

Deploy the Analysis Services Solution

Deploying the solution will dispatch the code we have created using the Cube Wizard and Designer interfaces. Once the XML reaches the targeted Analysis Server (the destination server must be targeted, before beginning deployment, within the project's Deployment Configuration Properties page), the Analysis Services database is created and processed. Once this has been accomplished, we can fully leverage the browser that the Cube Designer makes available to us.

Processing begins, as we can see in the Deployment Progress pane. Once deployment has completed successfully, we see a message to this effect in the Status area, toward the bottom of the pane, as depicted in Illustration 47.

We are left with a processed Analysis Services database and cube. We are ready for the final part of our article, where we will verify the effectiveness of our handiwork via the Cube Browser.

Browse the Cube

We can get to the Cube Browser, at this stage, and see our Measure Groups at work within the cube, by taking the following steps.

The Browser pivot table area appears as depicted in Illustration 51.

We might assemble any number of browse scenarios at this point. This includes juxtaposing various column and row combinations, as well as crossjoining on either column or row axes, adding filters of various types, and more, within the Pivot Table (assuming that we have enacted the appropriate Dimension Usage settings to support the combinations we select). For the purposes of our exercise, we will simply verify the granularity we have imposed over the Time dimension hierarchy for each of the measures we have engaged above.

The Time dimension hierarchy expands to reveal the totals, by level of the Time dimension, as shown in Illustration 52.

We note that the Sales Amount Quota measure remains fixed at the annual value, within all Time dimension levels, while the Sales Amount measure "drills down" to the individual date level of the Time dimension hierarchy. This circumstance allows us to verify the expected operation of the Dimension Usage settings, which dictate that the granularity of the Sales Amount Quota (a member of the Fact Sales Quota Measure Group) is the CalendarYear level. Moreover, we can see that the granularity of the actual Sales Amount (a member of the Fact Sales Quota Measure Group) is established at the individual date level.

We can visualize an array of reporting capabilities at this point, once we add formatting and other nuances. For now, we will conclude our session, having focused upon the establishment of granularity for different Measure Groups to extend our cube to leverage multiple fact tables for desired reporting and analysis presentations.

Conclusion

In this article, we examined Measure Groups, which debut in Analysis Services 2005. After introducing the concept of Measure Groups, we overviewed their creation, and discussed ways in which they can offer flexibility in cube and solution / application design and development. In preparation for our examination of the steps involved in extending our cubes to leverage multiple fact tables, we prepared Analysis Services, and our environment, by creating an Analysis Services Project to house our development steps, and to serve as a platform for the design of a quick cube model, within which to perform subsequent procedures in our session. We next performed the steps that are common to the design and creation of any cube within Analysis Services 2005, including the creation of a Data Source, containing the information Analysis Services needs to connect to a database, and a Data Source View containing schema information.

We next created a basic cube, referencing our Data Source and Data Source View, which contained data from our sample relational tables. We then focused upon the addition of examples of Measure Groups as part of cube design, from the perspective of the Cube Wizard. We then got some hands-on exposure to assigning, via the Dimensional Usage tab of the Designer, granularity at measure / dimension intersects for members of the new Measure Groups. Finally, after deploying our Analysis Services solution, we browsed the cube, focusing on the new Measure Groups and associated details.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.