SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 3)

9/17/2012 7:16:47 PM

Member Grouping

Our next stop is the Products dimension. Start by renaming the Vw Products attribute to Product Name and then creating a hierarchy called Category - Product using the Category Name and Product Name attributes, in that order, as its levels. Save and deploy your changes and, on the dimension designer’s Browser tab, select Product Name from the Hierarchy drop-down list. Drill down on the All member, and you will see that the number of products is rather large. Using the Category - Product hierarchy makes the product list more manageable, but you might not always want to break up products along category lines.

One solution to this problem is to use an Analysis Services feature called member grouping, or discretization. Essentially, this feature groups members of a particular attribute by creating a new attribute/level “above” it. Each member of the new level ends up containing a specific number of child members from the original attribute.

To implement member grouping for the Products dimension, return to the Dimension Structure tab. Drag the ProductName column from the Data Source View pane to the Attributes pane, and change its name from the default Product Name 1 to Product Group. Next, set this new attribute’s DiscretizationBucketCount property to 10 and its DiscretizationMethod property to Automatic. This creates 10 discrete parent members that can be used in a new hierarchy along with the Product Name attribute. Create a new hierarchy by dragging the Product Group attribute into the Hierarchies pane, and then drag the Product Name attribute underneath it into the new hierarchy. Name the new hierarchy Group - Product, and then save and deploy your changes.

Use the new hierarchy in the cube browser (first reconnecting, if necessary) to drill down on Unit Sales By Product Group (using the Group - Product hierarchy, rather than the Product Group attribute). When sales in a particular group look especially high or low, drill down on that group to see which product or products are responsible for the high or low sales. You’ll find that looking through the limited number of members in the Product Group level is much easier than looking through the entire product list to find outlier data.

Server Time Dimensions

You might have thought that building a special dimension table for the Time dimension was a bit wasteful. After all, the concept of time is fairly universal, and there’s a limited set of possible attributes and hierarchies that might be needed, any of which could be easily keyed off a date field in the fact table rather than an arbitrary time key.

In Analysis Services 2000, the concept of a date column–derived time dimension was very basic and easily implemented. Analysis Services 2005 has enhanced this facility, but its use is slightly less obvious. The new implementation of time dimensions is based on a servergenerated pseudo-dimension table and an array of predefined attributes and hierarchies. In fact, time dimensions are implemented by creating a special dimension type called a server time dimension.

Let’s add a server time dimension to our cube. Simply invoke the Dimension Wizard, and on the Select The Dimension Type page select the Server Time Dimension option button. On the Define Time Periods page, specify January 1, 1996, and June 30, 1998, as the first and last calendar days, respectively, and select Year, Quarter, Month, and Date from the Time Periods list box, as shown in Figure 13.

Figure 13. The Define Time Periods page of the Dimension Wizard

The Dimension Wizard’s Define Time Periods page appears only for server time dimensions. It allows you to specify the (automatically built) levels of your server time dimension simply by selecting items from a list. It also lets you specify the beginning and end dates for the dimension, which should correlate to the fact table date columns’ data, for which the dimension will be built.

Accept the defaults for all other settings, and name your new dimension ServerTime before clicking Finish. Add the dimension to your cube as described earlier, and then click on the cube designer’s Dimension Usage tab. This tab is where you configure dimensions that typically are not based on conventional relationships between fact and dimension tables.

Click in the cell at the intersection of the ServerTime dimension row and the Main measure group column, and then click the cell’s ellipsis (...) button. In the Define Relationship dialog box, specify a Regular relationship, Date as the Granularity Attribute, and OrderDate as the Measure Group Column, as shown in Figure 14.

Figure 14. The Define Relationship dialog box, configured for a server time dimension

Notice the “Server provided” designation under the Dimension Columns header; this indicates that the dimension table is server-generated and therefore does not require you to specify a dimension column. Click OK, and note that the cell on the Dimension Usage tab now reads Date, indicating the granularity attribute of the server time dimension. Save and deploy your changes, and then inspect them in the cube browser (reconnecting first, as necessary).

More Info

If you drill down on the ServerTime dimension in the cube browser’s metadata tree view, you will see that server time dimensions offer an array of default attributes. If you open the ServerTime dimension in the dimension designer, you will see an even larger array of columns (from which new attributes can be created) in the Dimension Structure tab’s Time Periods pane.

Fact Dimensions

One hallmark of Analysis Services 2005 is its ability to adapt to your database’s structure rather than making you perform ETL (extract, transform, and load) gymnastics to satisfy Analysis Services requirements. Suppose, for example, that you want to create a dimension out of data that is stored in your fact table. With Analysis Services 2000, you would have to duplicate that data within the cube, and then relate that data back to your fact table to use it as a dimension. Analysis Services 2005, on the other hand, is much less rigid: You simply specify that your dimension data is in your fact table, and Analysis Services will accommodate you.

For example, the Orders table in the Northwind database contains geographic information pertaining to the shipping location that can be constructed into a useful dimension. Our current Geography dimension, derived from data in the Customers table, was easy to construct. But it is less obvious how to use fields such as ShipCountry and ShipRegion from our fact table (and derived from the Northwind database’s Orders table) as levels in a new dimension.

It is less obvious, but it is still feasible to build this dimension with Analysis Services 2005. We’ll show you how to build such a dimension, but first run the script tblFact.sql in the Management Studio solution included with the sample code for this project. This script drops tblFact and then re-creates it with a modified structure and repopulates it. After you run the script, open the Northwind Data Source View and click its Refresh toolbar button so that the new structure of tblFact is properly reflected. The Refresh Data Source View dialog box will appear, asking you to confirm the new changes to the structure of tblFact. Click OK.

Next, create a new standard, regular dimension called Ship Geography, making sure to identify tblFact as the dimension table, Ship Region as the key column, and Ship Country as the only attribute column. (Do not select any related tables.) Open the new dimension in its designer if it is not already open, and rename the Tbl Fact attribute to Ship Region. Create a user hierarchy called Country - Region using the Ship Country and Ship Region attributes, in order, as its levels, and then add the new dimension to your cube if you have not already done so.

Before you can save and deploy your changes, a little more work is required. You must explicitly tell Analysis Services that your new dimension is a fact dimension. You do this on the Dimension Usage tab of the cube designer. The adjustment is rather simple. On the tab, scroll down to the bottom of the list of dimensions and locate the cell where the Ship Geography dimension row intersects with the Main measure group column. The Ship Region field name is displayed in that cell. Everything appears to be normal, but an adjustment is indeed necessary.

Click within the Ship Region cell, and you should see an ellipsis button appear (as shown in Figure 15).

Figure 15. The Dimension Usage tab with the ellipsis button for the Ship Geography/Main measure group cell highlighted

Because Ship Geography is a fact dimension, you’ll need to override its default usage and tell Analysis Services to establish a fact relationship as its basis. Click the ellipsis button to invoke the Define Relationship dialog box, change the relationship type from Regular to Fact, as shown in Figure 16, and then click OK.

Figure 16. The Define Relationship dialog box with the Fact relationship type selected

Back on the Dimension Usage tab of the cube designer, note that the special fact dimension icon now appears in the dimension’s cell, as shown in Figure 17.

Figure 17. The Dimension Usage tab with the Ship Geography’s fact dimension icon highlighted

Save and deploy your changes. On the Browser tab of the cube designer, reconnect if necessary, and you should be able to drill down on any measure by the Ship Country or Ship Region attributes or through the hierarchy that includes both. As you do so, consider how quickly you were able to create this dimension, add it to your cube, and start using it. We had to run a script to modify our fact table, but that’s merely an artifact of how we built the fact table in the first place. In many real-world scenarios, adding a fact dimension involves simply using fields that were already there. No ETL is required, and you don’t need to create a special dimension table, a view, or even a named query. This kind of flexibility—what we might call a casual ability to enhance a cube—simply didn’t exist in Analysis Services 2000. That Analysis Services 2005 offers it is a true revolution in the BI space.


Each measure group in a cube can have at most one fact dimension, and each fact dimension can be related to only one measure group.

Role-Playing Dimensions

Fact dimensions are not the only specialized type of dimension; there are many others. One of these, role-playing dimensions, allows you to reuse a single database dimension as multiple distinct cube dimensions. This can be especially useful with time dimensions, which usually have identical structures.

Our newly modified tblFact table now contains three date fields: OrderDate (which was there all along), RequiredDate, and ShippedDate. Wouldn’t it be nice if we could reuse our ServerTime dimension twice more, relating the new “instances” to RequiredDate and ShippedDate, respectively? As it turns out, we can do just that.

Simply use the Add Cube Dimension option described previously, select the ServerTime dimension in the Add Cube Dimension dialog box, and click OK. You should see your new dimension added as ServerTime 1. Repeat the process to add dimension ServerTime 2. In the Dimensions pane of the cube designer’s Cube Structure tab, rename ServerTime 1 and ServerTime 2 to Shipped Date and Required Date, respectively; finally, rename the original ServerTime dimension to Order Date to better identify it in relation to these new dimensions.

Now move to the Dimension Usage tab. You should see all three of the cube dimensions that are based on the ServerTime database dimension, including the original ServerTime (now Order Date) dimension, listed with names appearing in the format ServerTime (cubedim), where cubedim is the name of the individual cube dimension. You should also see that the cells at which the two new dimensions intersect with the Main measure group are gray and empty.

Click in one of the empty cells, and then click the ellipsis button to invoke the Define Relationship dialog box. Select Regular from the Relationship Type drop-down list, select Date from the Granularity Attribute drop-down list, and select the appropriate fact table date column (ShippedDate or RequiredDate) under Measure Group Columns. These settings explicitly tell Analysis Services the foreign key in the fact table on which to join the server time pseudo-dimension table. Click OK, repeat this process for the remaining unconfigured dimension, save and deploy your changes, and then examine the changes in the cube browser.

In Analysis Services 2000, each distinct time dimension requires its own dimension table (or at least its own view). As with fact dimensions, Analysis Services handles things so that no ETL tasks, views, or named queries are required to implement the three different time dimensions. As you might have surmised, role-playing dimensions are so named because they allow a single database dimension to play the “role” of multiple distinct cube dimensions by linking to different columns in the measure group (fact table). This is similar to using the same table more than once in a SQL query and assigning it an alias: The various role-playing dimensions in the cube are essentially aliases of the one physical database dimension.

Advanced Measures

With all this work on our dimensions, we shouldn’t forget measures. Shortly, we’ll discuss how to create measures that are completely calculated by our cube, but for now let’s just concentrate on tidying up the measures we have.

Measure Formatting

As you browsed the cube, you might have noticed that the dollar amounts stored in the measures are displayed as raw numbers and are not formatted as currency data. This can make the data difficult to read, but the problem is easily solved.

Open the Sales cube in the cube designer, click on the Cube Structure tab, and select the Total Sales measure in the Measures pane. Then, in the Properties window, set the FormatString property to Currency and do the same for the other two measures. Note the other formatting options available for use when you design cubes in the future. Analysis Services 2005 makes it easy to implement flexible server-side formatting of all your measures.

Special Aggregates

The Properties window lets you do more than simply format your measures; it also lets you alter their formulaic definitions. The Aggregate property allows you to create measures whose aggregations are not simple sums.

For example, in addition to our Total Sales measure, we might want to have a measure that reflects average total sales for a given dimensional slice. Adding such a measure to our cube is easy. On the Cube Structure tab of the cube designer, click the New Measure toolbar button (third from the left), select Cube/New Measure... from the main menu, or select the New Measure... option on the Measures pane’s shortcut menu. In the resulting New Measure dialog box, select Average Over Time from the Usage drop-down list, select the Total Sales item in the Source Column list box, and then click OK.

You’ll see the new measure appear as Total Sales – Main. Using its shortcut menu, the F2 key, or the Name property in the Properties window, rename it to Avg Total Sales. In the Properties window, notice that the AggregateFunction property is set to AverageOfChildren; this is the result of selecting the Average Over Time option in the New Measure dialog box. As you did with the other measures, set the FormatString property of this new measure to Currency.

More Info

Clicking the drop-down arrow in the AggregateFunction property value cell reveals numerous other aggregate functions that you can select for your measures. The MeasureExpression property allows you to enter an MDX expression that will determine the value of the leaf levels of your dimensions before the aggregate function is applied.

Display Folders

When your cube has a large number of measures, you might want to group related ones together, something Analysis Services 2005 lets you do with display folders. By simply entering a folder name into a measure’s DisplayFolder property, you can segregate related sets of measures. The cube browser, embedded in the cube designer and in Management Studio, offers direct support for display folders.

Once the display folder name is entered in the Properties window, it morphs into a drop-down list item. You can then easily assign the same display folder name to other measures in your cube without having to type it from scratch and risk entering it inconsistently. Enter the name Non-Sum as the DisplayFolder property value for the Avg Total Sales measure you previously created, and enter Sum as the DisplayFolder property value for the Total Sales measure. Then select one of the remaining measures, and notice that both Sum and Non-Sum appear in its DisplayFolder property drop-down list. Select Sum and do likewise for the remaining measure.

Save and deploy your changes, and then click on the cube designer’s Browser tab. Reconnect and then browse your cube. The original three measures should appear under the Sum display folder, and the Avg Total Sales measure should appear under the Non-Sum display folder (both within the Main measure group). When browsed, each measure’s data should be formatted as currency values, with dollar signs and decimals displayed.


User hierarchies within dimensions also have a DisplayFolder property that you can use in the same way as outlined here for measures. You can enter folder names using a filespec-like syntax (for example, \parent folder\child folder), allowing you to create display folder hierarchies.

  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - Database Maintenance
  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - SQL Server Components
  •  Microsoft Visual Basic 2008 : Processing and Storing Data in SQL Server 2005 - Optimizing the LINQSQL Class
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 4) - Direct Data Access - The DataReader
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 3) - Direct Data Access - Creating a Connection, The Select Command
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 2) - SQL Basics
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 1) - Understanding Databases, Configuring Your Database
  •  SQL Server 2008 : Failover clustering - Installing a clustered SQL Server instance
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us