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.

1.  Right-click the ANSYS048 Measure Groups solution, atop the tree in Solution Explorer.

2.  Select Deploy Solution from the context menu that appears, as depicted in Illustration 46.


Illustration 46: Deploying the Solution ...

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.


Illustration 47: Indication of Successful Deployment Appears ...

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.

3.  Click the Browser tab within the Cube Designer, as shown in Illustration 48.


Illustration 48: Click the Browser Tab ...

4.  Within the tree appearing inside the Metadata pane, on the left side of the Browser tab, expand Dim Time by clicking the "+" sign to its immediate left.

5.  Drag Hierarchy Dim Time.CalendarYear - CalendarQuarter - EnglishMonthName – FullDateAlternateKey to the area marked Drop Row Fields Here within the Pivot Table area, as depicted in Illustration 49.


Illustration 49: Creating a PivotTable Browse ...

6.  Within the Metadata pane, once again, expand Measures.

7.  Expand the Fact Internet Sales folder.

8.  Drag Sales Amount to the area marked Drop Totals or Detail Fields Here within the Pivot Table area, as shown in Illustration 50.


Illustration 50: Adding the First Measure, from the Fact Internet Sales Measure Group ...

9.  Expand the Fact Sales Quota folder.

10.  Drag Sales Amount Quota to the area marked Drop Totals or Detail Fields Here within the Pivot Table area, dropping it to the immediate right of the Sales Amount measure.

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


Illustration 51: The Browser Pivot Table with Additions ...

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.

11.  Within the column headed "CalendarYear," expand the year 2003 by clicking the "+" sign to its immediate left.

12.  Expand quarter 4 (underneath the CalendarQuarter column heading) within the expanded year.

13.  Expand the month of December (underneath the EnglishMonthName column heading) within the expanded quarter.

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


Illustration 52: The Measures Totals Appearing at Time Dimension Hierarchy Levels

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.

14.  Examine further the Measure Groups within the Cube Browser as desired, perhaps with corresponding alterations in the Dimension Usage settings to obtain specific effects. (Remember to re-process the cube after any changes, before browsing).

15.  Select File -> Save All from the main menu, to save our work through this point, as depicted in Illustration 53.


Illustration 53: Saving All Work from Our Session

16.  Select File -> Exit, when ready, to leave the Business Intelligence Development Studio.

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.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers