In this two-part article by Chris Webb, we will look at measures and measure groups, ways to control how measures aggregate up, and how dimensions can be related to measure groups. In this part, will cover useful properties of measures, along with built-in measure aggregation types and dimension calculations.
Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures: how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on. It’s therefore no surprise that we’ll spend a lot of our cube development time thinking about measures.
Apart from the AggregateFunction property of a measure, which we’ll come to next, there are two other important properties we’ll want to set on a measure, once we’ve created it.
The Format String property of a measure specifies how the raw value of the measure gets formatted when it’s displayed in query results. Almost all client tools will display the formatted value of a measure, and this allows us to ensure consistent formatting of a measure across all applications that display data from our cube.
A notable exception is Excel 2003 and earlier versions, which can only display raw measure values and not formatted values. Excel 2007 will display properly formatted measure values in most cases, but not all. For instance, it ignores the fourth section of the Format String which controls formatting for nulls. Reporting Services can display formatted values in reports, but doesn’t by default; this blog entry describes how you can make it do so: http://tinyurl.com/gregformatstring.
There are a number of built-in formats that you can choose from, and you can also build your own by using syntax very similar to the one used by Visual BASIC for Applications (VBA) for number formatting. The Books Online topic FORMAT_STRING Contents gives a complete description of the syntax used.
Here are some points to bear in mind when setting the Format String property:
Many cubes have a lot of measures on them, and as with dimension hierarchies, it’s possible to group measures together into folders to make it easier for your users to find the one they want. Most, but not all, client tools support display folders, so it may be worth checking whether the one you intend to use does.
By default each measure group in a cube will have its own folder containing all of the measures on the measure group; these top level measure group folders cannot be removed and it’s not possible to make a measure from one measure group appear in a folder under another measure group. By entering a folder name in a measure’s Display Folder property, you’ll make the measure appear in a folder underneath its measure group with that name; if there isn’t already a folder with that name, then one will be created, and folder names are case-sensitive. You can make a measure appear under multiple folders by entering a semi-colon delimited list of names as follows: Folder One; Folder Two.
You can also create a folder hierarchy by entering either a forward-slash / or back-slash delimited list (the documentation contradicts itself on which is meant to be used—most client tools that support display folders support both) of folder names as follows: Folder One; Folder TwoFolder Three.
Calculated measures defined in the MDX Script can also be associated with a measure group, through the Associated_Measure_Group property, and with a display folder through the Display_Folder property. These properties can be set either in code or in Form View in the Calculations tab in the Cube Editor:
If you don’t associate a calculated measure with a measure group, but do put it in a folder, the folder will appear at the same level as the folders created for each measure group.
The most important property of a measure is AggregateFunction; it controls how the measure aggregates up through each hierarchy in the cube. When you run an MDX query, you can think of it as being similar to a SQL SELECT statement with a GROUP BY clause—but whereas in SQL you have to specify an aggregate function to control how each column’s values get aggregated, in MDX you specify this for each measure when the cube is designed.
Anyone with a passing knowledge of SQL will understand the four basic aggregation types available when setting the AggregateFunction property:
There isn’t a built-in Average aggregation type—as we’ll soon see, AverageOfChildren does not do a simple average—but it’s very easy to create a calculated measure that returns an average by dividing a measure with AggregateFunction Sum by one with AggregateFunction Count, for example:
CREATE MEMBER CURRENTCUBE.[Measures].[Average Measure Example] AS
IIF([Measures].[Count Measure]=0, NULL,
[Measures].[Sum Measure]/[Measures].[Count Measure]);
The DistinctCount aggregation type counts the number of distinct values in a column in your fact table, similar to a Count(Distinct) in SQL. It’s generally used in scenarios where you’re counting some kind of key, for example, finding the number of unique Customers who bought a particular product in a given time period. This is, by its very nature, an expensive operation for Analysis Services and queries that use DistinctCount measures can perform worse than those which use additive measures. It is possible to get distinct count values using MDX calculations but this almost always performs worse; it is also possible to use many-to-many dimensions to get the same results and this may perform better in some circumstances; see the section on “Distinct Count” in the “Many to Many Revolution” white paper, available at http://tinyurl.com/m2mrev.
When you create a new distinct count measure, BIDS will create a new measure group to hold it automatically. Each distinct count measure needs to be put into its own measure group for query performance reasons, and although it is possible to override BIDS and create a distinct count measure in an existing measure group with measures that have other aggregation types, we strongly recommend that you do not do this.
The None aggregation type simply means that no aggregation takes place on the measure at all. Although it might seem that a measure with this aggregation type displays no values at all, that’s not true: it only contains values at the lowest possible granularity in the cube, at the intersection of the key attributes of all the dimensions. It’s very rarely used, and only makes sense for values such as prices that should never be aggregated.
If you ever find that your cube seems to contain no data even though it has processed successfully, check to see if you have accidentally deleted the Calculate statement from the beginning of your MDX Script. Without this statement, no aggregation will take place within the cube and you’ll only see data at the intersection of the leaves of every dimension, as if every measure had AggregateFunction None.
The semi-additive aggregation types are:
They behave the same as measures with aggregation type Sum on all dimensions except Time dimensions. In order to get Analysis Services to recognize a Time dimension, you’ll need to have set the dimension’s Type property to Time in the Dimension Editor.
Sometimes you’ll have multiple, role-playing Time dimensions in a cube, and if you have semi-additive measures, they’ll be semi-additive for just one of these Time dimensions. In this situation, Analysis Services 2008 RTM uses the first Time dimension in the cube that has a relationship with the measure group containing the semi-additive measure. You can control the order of dimensions in the cube by dragging and dropping them in the Dimensions pane in the bottom left-hand corner of the Cube Structure tab of the Cube Editor; the following blog entry describes how to do this in more detail: http://tinyurl.com/gregsemiadd. However, this behavior has changed between versions in the past and may change again in the future.
Semi-additive aggregation is extremely useful when you have a fact table that contains snapshot data. For example, if you had a fact table containing information on the number of items in stock in a warehouse, then it would never make sense to aggregate these measures over time: if you had ten widgets in stock on January 1, eleven in stock on January 2, eight on January 3 and so on, the value you would want to display for the whole of January would never be the sum of the number of items in stock on each day in January. The value you do display depends on your organization’s business rules.
Let’s take a look at what each of the semi-additive measure values actually do:
The following screenshot of an Excel pivot table illustrates how each of these semi-additive aggregation types works:
Note that the semi-additive measures only have an effect above the lowest level of granularity on a Time dimension. For dates like July 17th in the screenshot above, where there is no data for the Sum measure, the LastNonEmpty measure still returns null and not the value of the last non-empty date.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…