THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Visual Totals and Distinct Count

One of the most talked about improvements in Analysis Services 2005 was support for Distinct Count measure over arbitrary set of members. I.e. the user can select subset of members over which he wants to get the value of Distinct Count measure, and it gives correct results, whereas in Analysis Services 2000 it would return an error. The support extends on all possible ways to do it - through MDX function Aggregate (the most common and therefore the most discussed approach), sets in the WHERE clause, subselects, CREATE SUBCUBE statement, dimension security Visual Totals, grouping cubes - and lastly through MDX function VisualTotals and through "Default MDX Visual Mode" command and connection string property. The last two are the subject of this article. It is well known, that both Excel and OWC by default show the visual totals in the Pivot Table, i.e. if some of the hierarchy members are hidden, then the totals reflect only the selected members. Excel before 2007 version achieved that by setting "Default MDX Visual Mode=1" on the connection string, and then let Analysis Services to automatically determine when visual totals are needed. OWC took more explicit approach by creating session sets using VisualTotals function to affect the way totals were calculated. Excel 2007 also uses VisualTotals function, but with the query sets. In either case, the internal implementation for both "Default Visual Totals Mode" and for VisualTotals function is exactly the same, so the results are the same as well and performance is the same as well.

Let's check how Distinct Count measures work with VisualTotals. We will use Adventure Works cube, which has [Internet Order Count] measure with DistinctCount aggregation set on it. We will emulate the queries generated by Excel 2007 or OWC. Let's first look at breakdown of Internet Order Count by Product Categories:

select [Measures].[Internet Order Count] on 0
,Descendants([Product].[Product Categories].[All Products],[Product].[Product Categories].[Category],SELF_AND_BEFORE) on 1
from [Adventure Works]

We get a total of 27,659 orders. Now, let's hide Components and Clothing categories, and leave only Accessories and Bikes:

select [Measures].[Internet Order Count] on 0
,visualtotals({[Product].[Product Categories].[All Products]
,[Product].[Product Categories].[Category].&[4]
,[Product].[Product Categories].[Subcategory].&[26]
,[Product].[Product Categories].[Subcategory].&[27]
,[Product].[Product Categories].[Category].&[1]})
on 1
from [Adventure Works]

The total is still 27,659 0 which is still correct, since we didn't turn Visual Totals yet. By applying VisualTotals function we will get

select [Measures].[Internet Order Count] on 0
,VisualTotals({[Product].[Product Categories].[All Products]
,[Product].[Product Categories].[Category].[Accessories]
,[Product].[Product Categories].[Category].[Bikes]}) on 1
from [Adventure Works]

Now, as expected, the total drops to 26,166 - since we excluded some categories. Of course, since the measure is DistinctCount, the total is always less (or equal) then the sum of the components. So far - so good. Let's now do one more step, and drill down into Accessories:

select [Measures].[Internet Order Count] on 0
,VisualTotals({[Product].[Product Categories].[All Products]
,[Product].[Product Categories].[Category].[Accessories]
,[Product].[Product Categories].[Category].[Accessories].Children
,[Product].[Product Categories].[Category].[Bikes]}) on 1
from [Adventure Works]

Since we included all the children of Accessories - the total didn't change - it is still 26,166 as expected. Now we will hide everything but Bike Racks and Bike Stands.

select [Measures].[Internet Order Count] on 0
,VisualTotals({[Product].[Product Categories].[All Products]
,[Product].[Product Categories].[Category].[Accessories]
,[Product].[Product Categories].[Subcategory].[Bike Racks]
,[Product].[Product Categories].[Subcategory].[Bike Stands]
,[Product].[Product Categories].[Category].[Bikes]}) on 1
from [Adventure Works]

Now we expect the total count to decrease,  as well as count for Accessories, since we only have two subcategories underneath it. And indeed, the value of Accessories drops from 18,208 to 577. However, we notice that value for All Products stays the same as before - 26,166. This is clearly wrong ! We want count in grand total to be visual total of Accessories and Bikes, and Accessories is a visual total of its subcategories - this is the way it works with all other measure aggregation functions. However it turns out, that Analysis Services 2005 shipped with a limitation, that for DistinctCount measures, nested Visual Totals don't work - they go only one level down.

While it could've been documented and positioned as a limitation - in user's eyes this is really a bug - they get wrong results. Perhaps Analysis Services 2000 behaved better - because while it didn't produce the correct answer, it also didn't produce incorrect answer - it simply gave up and produced an error. Another scenario where nested visual totals would produce wrong results is with many-to-many dimensions, which are really similar to Distinct Count - but instead of counts they compute Distinct Sum. Wrong results are always a bad thing, therefore in SP2 the implementation of Visual Totals (both function and the mode) underwent serious change. Internally, this implementation got unified with implementation of visual totals for subselects - so now they all behave similarly. Running the last query with SP2 produces correct answer for [All Products] - 15,599. Additional bonus of changed implementation is that not only it is now always correct for nested visual totals of Distinct Count and many-to-many dimensions, but also the fact that this implementation usually delivers much better performance, since the layer at which visual totals are computed pushed from the calculations of FE deeper into SE layers.

Published Tuesday, November 07, 2006 9:36 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement