tim laqua dot com Thoughts and Code from Tim Laqua

3Feb/122

SSAS Cache Isn’t Making Cents

I stole the pun from my Business Analyst, Mr. John Seiler 😉 Now on to my issue - when SSAS caches the value for my [Actual] measure, it seems to do so based on the results of the first query that requests that coordinate. In this particular cube, there's bunches of tiny fractions and depending on how you slice it, it aggregates a little different. This is a fun problem in itself, but the part that drives me (and the Finance department) crazy is that if you go and slice on something OTHER than that first query that created the cache, the values they see don't always add up to the "Grand Total" in Excel - aka. "All"

These are the queries used for this test:

Query A

SELECT 
	{Actual} ON 0
FROM [GL] 
CELL PROPERTIES VALUE

Query B

SELECT 
	{Actual} ON 0,
	NON EMPTY Hierarchize({DrilldownLevel({[Account].[Accounts].[All]},,,INCLUDE_CALC_MEMBERS)}) ON 1
FROM [GL] 
CELL PROPERTIES VALUE

Query C

SELECT 
	{Actual} ON 0,
	NON EMPTY Hierarchize({DrilldownLevel({[Date].[Fiscal].[All]},,,INCLUDE_CALC_MEMBERS)}) ON 1
FROM [GL] 
CELL PROPERTIES VALUE
GO

Results (Cache was cleared with ClearCache on the entire Database before each Series)

Series 1 Series 2 Series 3

So basically the Grand Total of this GL cube is a random number based on whoever sneaks the first query in after the cache is cleared (processing, sync, etc).

And for all of you that think the MDX script breaks everything everywhere - I did comment out my entire MDX script before running these tests.

Comments (2) Trackbacks (0)
  1. I’d say this was a bug – have you reported it to MS? I’ve seen bugs similar to this before, but always related to MDX calculations; have you got any custom rollups or unary operators here?

    Chris

    • No, there aren’t any unary operaters anywhere in the cube (and all attributes in the Account dimension are just Regular, no fancy stuff). Also no custom rollups, no measure expressions, and while we have a ridiculous MDX script – this behavior is constant with the entire script commented out. Actual measure is real and bound to the DSV which is bound directly to a table (not a calculated column or a named query).


Leave a comment

No trackbacks yet.