Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

BottomCount, Filter and Null in SSAS2005 and ProClarity

Posted by thomasivarssonmalmo on August 14, 2007

One common business analytical problem is to find customers with low sales. In MDX we have the BottomCount function for that but compared to the TopCount function, that return the best customers,  BottomCount can return less clear results.

If you run this MDX Select in management studio, on the Adventure Works cube, you will see the 50 customers with least Internet Sales Amount in 2003 and what they have bought in 2004.  The MDX select is generated in ProClarity Professional 6.3 and I have used the filter functionality in the client.

SELECT { [Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,

 

NON EMPTY { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Date].[Calendar].[Calendar Year].&[2003],

[Measures].[Internet Sales Amount] ) ) } ON ROWS 

FROM [Adventure Works]

WHERE ( [Measures].[Internet Sales Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL ;

The problem with this result is that I have probably received 50 customers that not were customers in 2003 but started buying in 2004.  Also note that I have put the Non Empty keyword before BottomCount and it will only remove empty rows after BottomCount have returned the bottom 50 customers. Because all these customers have bought something in 2004, no rows will be removed.

Now I will show you an even more strange result with BottomCount:

SELECT { [Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003] } ON COLUMNS ,

 

NON EMPTY { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Date].[Calendar].[Calendar Year].&[2003],

[Measures].[Internet Sales Amount] ) ) } ON ROWS 

FROM [Adventure Works]

WHERE ( [Measures].[Internet Sales Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL ;

This MDX query will return only seven records. The only difference with the first select is that I have changed the year 2004 to 2002, on columns, but the BottomCount function is unchanged. I have no explanation for this behavior.

Internet Sales Amount in the AdventureWorks cube comes from the SalesAmount column in the FactInternetSales tables in the AdventureWorksDW database. If you run sql-statements on that column and check for, is null and = 0 , it will show that no rows in that table have null or 0 in that column. Conclusion: the nulls or empties in the cube do not come from the fact table in the data source.

If you open the Adventure Works cube project and check the measure [Internet Sales Amount] you can see that it uses an advanced property called measure expressions to calculate this value with a currency rate. Measure expressions are not the key problem here so I will leave that subject.

So back to the problem of removing the nonexistent customer that appears in the cube but not in the fact table, how do we deal with this problem?

After reading MDX-solutions I knew that SSAS2005 have a little problem with null.  With measures a null value can both be zero and null so you can you can use both values to search for these values and remove them.  In MDX the Filter function will do the job of removing members will cell values according to a criteria.

I searched in the SSAS2005 newsgroup on BottomCount and only found 7 posts, so this problem only concerns me and a few others. In one of these posts there was an answer that worked.

Select {[Measures].[Internet Sales Amount]} On Columns,

{Order(BottomCount(Filter([Customer].[Customer Geography].[Customer].members,

[Measures].[Internet Sales Amount] <> Null),50,

[Measures].[Internet Sales Amount]),[Measures].[Internet Sales Amount],BDESC)} On Rows

From [Adventure Works]

Where ([Date].[Calendar].[Calendar Year].&[2002])

This solution put a filter in the BottomCount function and disregards cells with null values. You can change the null in the filter to zero with the same result. I use the slicer (the Where-clause) to point at the year I am interested in. The slicer is the first part of an MDX-select that is evaluated and it points the BottomCount function to right year without having to enter it in to that function.

I also quickly checked that these values were correct by querying the fact table with the following TSQL-Select:

Select Min(fs.SalesAmount)

From FactInternetSales fs Join dimTime t On

fs.OrderDateKey       = t.TimeKey

Where Year(t.FullDateAlterNateKey) = 2004

In ProClarity Professional 6.3 I have combined the BottomCount function with a second filter that should remove values less than 0, 00  . You can see this in the following MDX Select but it will not work. It is a little bit sad that this business problem did not get more attention and was not implemented correctly.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,

{ EXCEPT( { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Measures].[Internet Sales Amount] ) ) },

{ FILTER( { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Measures].[Internet Sales Amount] ) ) },

( [Measures].[Internet Sales Amount] ) < 0.00000000000000 ) } ) } ON ROWS 

FROM [Adventure Works]

WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) 

 

Edit: My point here is to show the possibilities with MDX to solve a business problem but also point to that some client implementation of this were not good enough. Still ProClarity have been succesful and good enough in most areas but this important part was missed.

Advertisements

4 Responses to “BottomCount, Filter and Null in SSAS2005 and ProClarity”

  1. Darren said

    Hi Thomas,
     
    I think I am able to explain your issues with the BottomCount function, but my explanation was getting quite long, so I put it on my blog here http://geekswithblogs.net/darrengosbell/archive/2007/08/15/Explaining-BottomCount-Filter-and-Null-in-SSAS2005-and-ProClarity.aspx. I hope this is helpful.
     
    Cheers
    Darren

  2. Thomas said

    Thank’s for the attention Darren. I have posted a comment on your Blog as well. An interesting thing is that these null values are not in the fact table in the Adventure Works DW database.
     
    BottomCount probably do not return what users would like to have without a Filter-clause in this function. ProClarity did not do this correct and should have used both functions in order to produce more valuable results for users.
     
    And there are no null or zero sales records in the source fact table. It is SSAS2005 that produce them.
     
    /Thomas 
     

  3. Darren said

    You are correct, there are no zero or null sales records, this is part of the power of the cubes. It is probably dangerous for Microsoft to change the behaviour of BottomCount, but it might make sense for them to add a NonEmptyBottomCount() or to add an optional parameter to the BottomCount() function to tell it to ignore empty cells. As you say, most people are probably after the BottomCount of members with data.
     
    Also, I just wanted to comment on a couple of things on the alternate solution you posted on my blog. Personally, I would use the NonEmtpy() function instead of Filter(… <> NULL) because the Filter() function will force cell-by-cell evaluation where as the NonEmpty() function will try to use block computation (especially with SP2) which can be orders of magnitude faster. And with SSAS 2005 you may actually find that doing a TopCount instead of an Order will be faster eg. TopCount(BottomCount(… ,50, …),50, …) . This is due to some inefficiencies in the Order function in SSAS 2005. Chris Webb blogged about this here http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1262.entry. I am hoping that this will be fixed in 2008, so I would not play with this unless performance is an issue. On a small database like Adventure Works you will not see a lot of difference, but on a larger database it could be signficant.
     
    Cheers
    Darren

  4. Thomas said

    I agree that we need a NonEmptyBottomCount in the next release, That was my main point in the post but it will only help if the client tools will use it.
     
    I also saw your solution with NonEmpty that I will try. Actually I have tried it, before writing the post, but did it wrong.
     
    Regards
    Thomas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: