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.
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.
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).
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.
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.
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.
Caution
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.
Important
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. |
