Mastering Enterprise BI: Working with Measure Groups

Monday Jun 12th 2006 by William Pearson
Share:

Leverage multiple fact tables within a cube with Measure Groups. Join Architect Bill Pearson in a hands-on exploration of Measure Groups, another exciting feature that debuts with Analysis Services 2005.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.

Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine

  • Microsoft SQL Server 2005 Analysis Services

  • Microsoft SQL Server 2005 Integration Services

  • Business Intelligence Development Studio

  • Microsoft SQL Server 2005 sample databases

  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:

    • the Administrators local group on the Analysis Services computer

    • the Server role in the instance of Analysis Services

  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, as appropriate.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

About the Mastering Enterprise BI Articles ...

Having implemented, and developed within, most of the major enterprise BI applications for over fourteen years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I have become convinced that the components of the Microsoft integrated business intelligence solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of Cognos (and other) enterprise business intelligence to the Microsoft solution. In addition to converting formerly dominant business intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel – or outstrip - those found in the more "mature" enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost. The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the dominant enterprise BI vendors, to date, represents a serious "undersell" of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves – and, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the Microsoft solution will commoditize business intelligence.

For more information about the Mastering Enterprise BI articles, see the section entitled "About the Mastering Enterprise BI Articles" in my article Relative Time Periods in an Analysis Services Cube, Part I.

Introduction

The advent of MSSQL Server Analysis Services 2005 witnessed the introduction of many new concepts within a dramatically more sophisticated design environment. Measure Groups represent one of myriad enhancements that we encounter early in exploring and implementing Analysis Services 2005 for use within enterprise Business Intelligence environments. A Measure Group not only holds the measures from a given fact table, but it also houses the aggregations of those measures for various dimensional hierarchies that we designate.

When we couple a dimension with a Measure Group, we associate the measures within the group with the appropriate levels of the hierarchy within that dimension. This allows us the flexibility of using the same "grain mapping" between the level and other measures we might wish to add to the same group. The most obvious advantage that accrues is the capability to maintain different Measure Groups with different meaningful levels, eliminating confusion and delivering new levels of design friendliness.

Measure Groups are, therefore, logical collections of related measures, whose purpose is to make life easier for solution and application designers. In this article, we will examine Measure Groups, and get hands-on exposure to the process of adding them to a basic cube we construct within the new Business Intelligence Development Studio. We will overview the creation of Measure Groups, and discuss ways in which they can offer flexibility in cube and solution / application design and development. As a part of our examination of the steps, we will:

  • Prepare 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;
  • Create a Data Source containing the information Analysis Services needs to connect to a database;
  • Create a Data Source View containing schema information;
  • Build a cube based upon our Data Source and Data Source View, containing data from our sample relational tables;
  • Add examples of Measure Groups as part of cube design;
  • Assign, via the Dimensional Usage tab of the Designer, granularity at measure / dimension intersects for representative members of the new Measure Groups;
  • Deploy our Analysis Services Solution;
  • Browse the Cube, focusing on the new Measure Groups and associated details.

Working with Measures and Measure Groups

Overview and Discussion

We will create an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in the past, the pre-assembled Analysis Services Project that makes its home within the Studio assists us in organizing and managing the numerous objects that we will need to support our efforts to create and deploy our Analysis Services database.

We will leverage the Cube Wizard in this article to quickly design and create a cube, allowing us to focus on the subject matter of the article with minimal peripheral distraction. The Cube Wizard not only helps us simplify the design and creation of our cubes, as it did within Analysis Services 2000: the Analysis Services 2005 Cube Wizard is more powerful, leveraging IntelliCube technology to examine and classify many of the attributes of our data. Analysis Services can determine, for example, prospective fact tables, dimensions, hierarchies, levels and other structural members of our cubes from a given database schema at which it is pointed. Regardless of whether we make a habit of using the wizard in our cube development efforts, it certainly provides a way to rapidly generate a cube, if only to eliminate part of the repetitive work involved to create a "starting point" model, which we can then "prune and groom" to more precisely meet the business requirements of our employers and customers.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Hands-On Procedure

We will begin by creating a new project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

Preparation

Create a New Analysis Services Project

We begin our preparation within Business Intelligence Development Studio, where we will create a new Microsoft SQL Server 2005 Analysis Services (SSAS) Project. As we have noted in other articles, those of us who have worked within earlier versions of Reporting Services, or within the Visual Studio development environment under other circumstances, will recognize the general look and feel of the Studio. Under this new style of development for Analysis Services, to which much of the documentation refers as "project mode," we create an Analysis Services Project, which houses a set of Analysis Services objects. The object set typically includes Data Sources, Data Source Views, Dimensions, Cubes, and other components.

Using an Analysis Services Project template affords us many benefits, such as helping us to organize and manage the items that are required to build, debug, and deploy an Analysis Services database. The development of an Analysis Services cube, which is our objective within this session, as well as one of the most common tasks in Analysis Services, can be accelerated though the use of the Analysis Services 2005 Cube Wizard, which both guides and simplifies the process, as is the way of wizards. IntelliCube technology, which can detect the relationships between attributes, dimensions, and facts (as we have already noted), makes the wizard more effective than its counterpart in Analysis Services 2000.

While we will not go into the processes and structures that underlie our immediate topic, working with Measure Groups, let's make a mental note that all objects that we create and store within a given project are defined as files (using an XML representation) that eventually are deployed to an Analysis Services database.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.


Illustration 1: Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.


Illustration 2: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4.  Close the Start Page tab.

5.  Select File -> New on the Visual Studio main menu.

6.  Select Project from the cascading menu, as depicted in Illustration 3.


Illustration 3: Beginning a New Project ...

The New Project dialog appears.

7.  Select Business Intelligence Projects in the Project types pane of the dialog.

8.  Select Analysis Services Project in the Templates pane, as shown in Illustration 4.


Illustration 4: Select Analysis Services Project

NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2005 components are installed in your environment, as well as upon whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.

9.  Change the project Name (currently displaying a default) to the following:

ANSYS048 Measure Groups

The Solution Name changes to match the project Name by default.

10.  Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly (Visual Studio will create a directory based upon our input here).

The relevant section of the New Project dialog appears similar to that depicted in Illustration 5.


Illustration 5: The New Project Dialog (Relevant Section), with our Input

11.  Click OK to accept our input and to create the new Analysis Services Project.

The New Project dialog closes. Visual Studio creates the project, which appears within solution ANSYS048 Measure Groups, in the Solution Explorer as shown in Illustration 6.


Illustration 6: The New Analysis Services Project Appears

Solution Explorer presents a tree view of the objects contained in the solution, the primary management unit within the Business Intelligence Development Studio, which can contain multiple projects. Individual projects, such as the one we have created, themselves contain folders for the objects that can be defined for projects of a similar type. It is, after all, the purpose of an Analysis Services Project to group related files containing the XML code behind the objects in a given Analysis Services database.

As we can see in the present instance, the Analysis Services Project template, upon which our ANSYS048 Measure Groups project was based, contains the following folders:

  • Data Sources
  • Data Source Views
  • Cubes
  • Dimensions
  • Mining Structures
  • Roles
  • Assemblies
  • Miscellaneous

Anytime we create a new Analysis Services Project, the Solution Explorer and the Properties window are visible and docked, as we see them in the present case. The following windows are hidden and docked, initially, and appear on the right, bottom, or left side of the development environment, depending upon where they are docked:

  • Server Explorer
  • Toolbox
  • Task List
  • Error List

Viewing any of the hidden windows is as simple as positioning the pointer over it – its mouseover behavior is to reappear. We can also click the Auto Hide button (depicted for the Solution Explorer in Illustration 7) to hide or unhide a window.


Illustration 7: The Auto Hide Button – Solution Explorer

Finally, we can always open a closed window by selecting it from the View menu atop the development environment, as shown in Illustration 8.


Illustration 8: Reopen Closed Windows from the View Menu ...

Having created a new Analysis Services Project, we are ready to define a Data Source and Data Source View.

Define a Data Source

Defining a Data Source, as we have discovered in past articles, is typically the first step we take with a new Analysis Services Project. A project can have multiple Data Sources, within which we are essentially defining the connection string used to connect to the actual sources of our data. While we can define connections to source databases on remote computers in the business environment, we will, in this article, be working with one of the sample databases that are available to us when we install MSSQL Server 2005, the AdventureWorksDW database.

Let's create a Data Source within our new ANSYS048 Measure Groups project by taking the following steps:

1.  Right-click Data Sources folder within the Solution Explorer.

2.  Select New Data Source ... from the context menu that appears, as depicted in Illustration 9.


Illustration 9: Initial Step in Creating a New Data Source

The Data Source Wizard appears, opening with the Welcome to the Data Source Wizard page, as shown in Illustration 10.


Illustration 10: Welcome to the Data Source Wizard ...

3.  Click Next.

The Select how to define the connection page appears. This page allows us to define a Data Source based upon a new or existing connection. (It also allows us to base a Data Source upon a connection that has been previously created.)

4.  Click New on the Select how to define the connection page.

The Connection Manager dialog appears.

5.  Ensure that .Net Providers\SqlClient Data Provider is selected in the Provider selection box atop the Connection Manager dialog.

6.  Type / select the appropriate server name / server name with instance into the Server name box.

We can substitute "localhost" or "localhost\<instance name>" here, if it is appropriate to our environment. (My server name with instance is MOTHER1\MSSQL2K5, as may be seen in several of the illustrations in this and other articles.)

7.  Select / complete the appropriate authentication choice for your environment in the section labeled Log on to the server. (I selected the radio button to the left of Use Windows Authentication in the lab environment in which I prepared this article.)

8.  Select AdventureWorksDW in the Select or enter a database name selector box, within the Connect to a database section of the dialog.

The Connection Manager dialog appears similar to that depicted in Illustration 11.


Illustration 11: Connection Manager Dialog in My Lab Environment

9.  Click the Test Connection button (in the bottom left corner of the Connection Manager dialog) to ascertain that we have a valid connection.

We receive a positive confirmation message, Test connection succeeded, assuming settings in the dialog are in accordance with the above steps, as appropriate for our own environments. The confirmation message box appears as shown in Illustration 12.


Illustration 12: Confirmation Message Indicating Successful Connection Test

10.  Click OK to dismiss the message box and return to the Connection Manager dialog.

11.  Click OK on the Connection Manager dialog to accept our settings and create the data connection.

We return to the Select how to define the connection page of the Data Source Wizard, which appears similar to that depicted in Illustration 13.


Illustration 13: Select How to Define the Connection Page with New Data Connection

12.  Click Next.

We arrive at the Impersonation Information page, where we define connection credentials for the data source.

13.  Click the radio button to the immediate left of Use the service account (or supply other appropriate local credentials), as shown in Illustration 14.


Illustration 14: Select the Use the Service Account Option

14.  Click Next.

The Completing the Wizard page appears, as depicted in Illustration 15.


Illustration 15: Completing the Wizard ...

We will leave the assigned Data Source name in place for purposes of our practice session.

15.  Click Finish to create the new Data Source, and to dismiss the Data Source Wizard.

The Wizard closes, and the new Data Source appears in the Data Sources folder within the Solution Explorer as shown in Illustration 16.


Illustration 16: The New Data Source Appears in the Solution Explorer

Having established the basic prerequisites, we are ready, at this stage, to create a Data Source View.

Procedure

Define a Data Source View

Having defined the Data Source within our Analysis Services Project, our next steps surround the creation of the Data Source View, a procedure that is customary at this stage in most Analysis Services Projects. (It is important to realize, as we work with a "live" data connection that we have defined, that we could certainly continue our development efforts with the metadata without an open connection.) The Data Source View provides a single, unified view of the metadata from the tables and views that concern us within our project.

NOTE: For more information on Data Source Views, see my article Introduction to MSSQL Server Analysis Services: Introducing Data Source Views at Database Journal.

To define a Data Source View, we will take the following steps:

1.  Right-click Data Source Views folder within the Solution Explorer.

2.  Select New Data Source View ...from the context menu that appears, as depicted in Illustration 17.


Illustration 17: Initial Step in Creating a New Data Source View

The Data Source View Wizard appears, opening with the Welcome to the Data Source View Wizard page, as shown in Illustration 18.


Illustration 18: The Initial Page of the Data Source View Wizard

3.  Click Next.

We arrive at the Select a Data Source page, where we see our Adventure Works DW Data Source in the Relational data sources list box on the left side of the page, as depicted in Illustration 19.


Illustration 19: Adventure Works DW Appears in the Relational Data Sources

Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise. It is particularly useful to note, as we have mentioned in Introducing Data Source Views, as well as other articles, that we can define a primary data source (a single Data Source like Adventure Works DW is an example), and then add tables and views from additional data sources. As we have noted in other articles of the Introduction to MSSQL Server Analysis Services series, heterogeneous queries are supported as long as one Data Source is a MSSQL Server Data Source.

4.  Click Next.

Analysis Services scans Adventure Works DW schema, and then we arrive at the Select Tables and Views page. Here we see the various tables of the Adventure Works DW data source appear in the Available objects list box on the left of the page. We will be selecting two fact tables, to allow us to house both sales and "budget" data within our cube.

5.  Click FactInternetSales in the Available objects list to select it.

6.  Click the button marked ">" to move FactInternetSales to the Included objects list on the right half of the page.

7.  Click FactSalesQuota in the Available objects list to select it.

8.  Click the button marked ">" to move FactSalesQuota to the Included objects list on the right half of the page.

The added tables appear within the Included objects list of Select Tables and Views page as shown in Illustration 20.


Illustration 20: Table Selections for Inclusion in the Data Source View

9.  Click the newly added FactInternetSales table in the Included objects list to select it.

10.  Click the Add Related Tables button, located underneath the Included objects list.

11.  Click the newly added FactSalesQuota table in the Included objects list to select it.

12.  Click the Add Related Tables button, located underneath the Included objects list.

Several related tables appear within the Included objects list, as depicted in Illustration 21.


Illustration 21: Our Tables Selection for the Data Source View

13.  Click Next.

The Completing the Wizard page appears, as shown in Illustration 22.


Illustration 22: The Final Page of the Data Source View Wizard

As indicated in the Completing the Wizard page, our selection includes the following tables:

  • FactInternetSales
  • FactSalesQuota
  • DimProduct
  • DimCurrency
  • DimTime
  • FactInternetSalesReason
  • DimSalesTerritory
  • DimPromotion
  • DimCustomer
  • DimEmployee

14.  Click Finish to create the new Data Source View, and to dismiss the Data Source View Wizard.

Our new Data Source View, Adventure Works DW, appears in the Data Source Views folder within the Solution Explorer window, as depicted in Illustration 23.


Illustration 23: The New Data Source View in the Solution Explorer

The Data Source View is also presented within the Data Source View Designer, which opens simultaneously with the Data Source View. The contents of the Designer appear within Business Intelligence Development Studio, as shown in Illustration 24.


Illustration 24: The New Data Source View in the Data Source View Designer

One of several designers within the Studio, the Data Source View Designer for our new Data Source View contains numerous elements that aid us in our organization and design efforts. We can modify the composition of the tables that comprise our view, as well as performing other activities, here.

We will next create a cube based upon the Data Source and Data Source View we have assembled. As a part of this process, we will get some hands-on exposure to working with Measure Groups.

Create a Cube and Work with Measure Groups

Create a Basic Cube

We will create a basic cube, to house our Measures, and the Measure Groups to which they will belong. We will focus upon other areas within the overall design and creation process in other articles of the series – our primary objective here is to gain some familiarity with Measure Groups – not to concern ourselves with the myriad other options and topics contained within general cube design.

1.  Right-click the Cubes folder within the Solution Explorer.

2.  Select New Cube ... from the context menu that appears, as depicted in Illustration 25.


Illustration 25: Select New Cube to Begin Cube Creation

The Welcome to the Cube Wizard dialog appears, as shown in Illustration 26.


Illustration 26: The Welcome to the Cube Wizard Dialog Appears ...

3.  Click Next.

4.  Ensure that Build the cube using a data source checkbox is selected on the Select Build Method dialog that appears next, along with the Auto build checkbox, and with Create attributes and hierarchies selected in the drop-down selector underneath the Auto build setting.

These default settings are depicted in Illustration 27.


Illustration 27: Ensuring the Default Settings on the Select Build Method Dialog

These settings will allow the Wizard to leverage the technology built into Analysis Services, and to detect the relationships between attributes, dimensions, and facts within the data source we have defined. While we will not accept all the judgments that the wizard makes, we can still take advantage of the actions it performs, and modify or eliminate those that are inappropriate or of no use to us.

5.  Click Next.

6.  Ascertain that the Data Source View we have created, Adventure Works DW, is selected within the Available data source views list on the left side of the Select Data Source View dialog, which appears next. (The tables contained within the Data Source View are displayed on the right side of the dialog).

The Select Data Source View dialog appears, as shown in Illustration 28.


Illustration 28: Select AdventureWorks DW as the Data Source View

7.  Click Next.

The Cube Wizard begins scanning the relational schema, identifying fact and dimensional tables, as indicated on the Detecting Fact and Dimension Tables page that appears next. We quickly receive a message indicating that the Wizard has completed its analysis of the relationships between tables in the Data Source View, and that it proposes its suggestions on the following page, as depicted in Illustration 29.


Illustration 29: The Detecting Fact and Dimension Tables Page Indicates Completion ...

8.  Click Next.

The Identify Fact and Dimension Tables dialog next appears. Let's select a Time dimension table, and narrow the table selection somewhat, for the purposes of our rudimentary cube.

9.  Select the DimTime table in the drop-down Time dimension table selector.

10.  Uncheck the following checkboxes:

Dimension Column: DimCurrency

NOTE: We can also make table selections graphically via the Diagram tab of the Identify Fact and Dimension Tables dialog.

The Identify Fact and Dimension Tables dialog appears, as shown in Illustration 30.


Illustration 30: The Identify Fact and Dimension Tables Dialog, with Our Modifications

11.  Click Next.

The Select Time Periods dialog appears.

12.  Leaving any unspecified columns blank, select the Time Table Columns values shown in Table 1 for each of the listed Time Property Names.

Time Property Name

Time Table Columns

Year

CalendarYear

Quarter

CalendarQuarter

Month

EnglishMonthName

Date

FullDateAlternateKey


Table 1: Selections within Respective Time Table Columns in Select Time Periods Dialog

The Select Time Periods dialog appears, with our selections, as depicted in Illustration 31.


Illustration 31: The Select Time Periods Dialog, with Our Selections

13.  Click Next.

The Select Measures dialog appears next.

14.  Uncheck the following checkboxes:

  • Revision Number
  • Discount Amount
  • Product Standard Cost
  • Tax Amt
  • Freight

The Select Measures dialog appears, as partially shown in Illustration 32.



Illustration 32: The Select Measures Dialog (Partial View), with Adjusted Selections

15.  Click Next.

The Detecting Hierarchies page appears next, as hierarchy detection begins. We receive rapid indication that dimension tables have been scanned and hierarchies detected, as depicted in Illustration 33.


Illustration 33: The Detecting Hierarchies Page, with Indication of Relationships Detected

16.  Click Next.

We move to the Review New Dimensions page, which appears as shown in Illustration 34.


Illustration 34: The Review New Dimensions Page

17.  Click Next.

The Completing the Wizard page appears next.

18.  Change the default Cube name to the following:

ANSYS048 – Measure Groups 

The Completing the Wizard page appears as depicted in Illustration 35.


Illustration 35: The Completing the Wizard Page

19.  Click Finish.

The Wizard concludes, and the Cube Designer opens, as shown in Illustration 36.


Illustration 36: The Cube Designer Opens ...

Here, within the multi-tabbed Cube Designer component of the Business Intelligence Development Studio, we can perform myriad enhancements and extensions to, and operations surrounding, the selected cube. We examine these new features in other articles of my Introduction to MSSQL Server Analysis Services series, where we define business needs and then meet them with the appropriate functionality(ies). For the purposes of this article, let's get some exposure to Measure Groups, and verify their effectiveness in our basic cube.

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.


Illustration 37: The Measures within their Respective Groups ...

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.


Illustration 38: Click the Dimension Usage Tab ...

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.


Illustration 39: Initial Dimension Usage Settings Made by Analysis Services ...

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.


Illustration 40: Accessing a Dimension Usage Setting ...

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.


Illustration 41: Select CalendarYear in the Relationships Table ...

The Define Relationship page appears, with our new settings, as depicted in Illustration 42.


Illustration 42: Define Relationship Page with Our Settings

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.


Illustration 43: Defining Relationship for Fact Internet Sales – Dim Time

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.


Illustration 44: The Define Relationship Page with Our Input

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


Illustration 45: Dimension Usage Settings, with Modifications ...

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.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved