Ran into a rather odd issue today. End-user reported that the chart being displayed to them was returning the wrong results and different filters were returning the same results. Here is the example of two queries that I used to validate the results and I was able to reproduce the issue:
SELECT NON EMPTY { { [Date].[Quarter Year].&[2007]&[3], [Date].[Quarter Year].&[2007]&[4], [Date].[Quarter Year].&[2008]&[1] } } ON COLUMNS,
NON EMPTY { Crossjoin({ [Brand].[Brand Label].[Brand1]}, { [Measures].[TBA], [Measures].[TBA(Base)] } ) } ON ROWS
FROM [cube] WHERE ( ( [Market].[Market].[USS] ), { [State].[State].&[Georgia], [State].[State].&[Florida], [State].[State].&[South Carolina] } )
AND
select {[Measures].[TBA], [Measures].[TBA(Base)]} on columns,
non empty([Brand].[Brand Label].[Brand1]) on rows
from cube where ([Market].[Market].&[USS], [Date].[Quarter Year].&[2007]&[3])
If I ran these queries in this order the results for 2007 Q3 were 56% and 1082. This was correct for the first one based on the WHERE clause, but not for the second MDX statement. I was really puzzled, but I decided to clear the cache and try again. This time I ran the queries in reverse order and I got the correct values (second query was 49.6% and 3312. I found a posting about setting the Cache Policy in the connection string to 9 or setting this at the server level in the msmdsrv.ini file (setting CalculationCoverPolicy to 9 instead of 0 which is the default). When I did this modification and restarted SSAS everything worked properly no matter which order I ran the MDX statements in. Really odd behavior still since both queries have different filters for the WHERE clause. Luckily I saw the Cache Policy setting in Mosha’s posting and Chris Webb also had a similar posting about this type of issue. There was also a posting in the Microsoft Forums that I used for reference.
I tested this in SQL 2008 Nov CTP5 and similar results. When I ran them in this order the second query actually returned 151.9% instead of 56% even though the correct result was supposed to be 49.6%. The second value was the same in SQL 2005. The same fix to the msmdsrv.ini file fixes the issue though. I will have to make a call ticket with MS Support and make them aware of this bug. I will let you know if they tell me to make a different fix to resolve this issue.
UPDATE (12/19/2007):
Just wanted to provide an update in regards to this issue with the cache reuse in Analysis Services. I did test both these MDX statements in Mosha’s MDX Studio and when the 2nd statement is executed it will only return the results from the cache and it will not perform any calculations. If you clear the cache and run them in the reverse order then it will perform the necessary calculations and everything is fine. Making the ‘Cache Policy’ setting change does resolve the issue and you can run the statements in either order.
MS Support got back to me and they did state that there is an issue with the cache reuse with the current product (and actually the next version of the product) and they have filed the issue with the development team. The interesting thing is that another way that they reported that this can be resolved was swapping out the function being used in one of the calculations. The current calculation is using the Aggregate function and they switched it to the Sum function and this fixed the problem also. Really odd since these are basically setup to do the same thing. They stated that it can be problematic using the Aggregate function to aggregate a calculated member.
In regards to the Cache Policy setting here is what they had to say:
The cache policy setting (9 instead of 0) you were using as a workaround is intended to change how the formula engine answers queries; it will cause the formula engine to lean towards using block evaluation mode more, instead of cell-by-cell mode. Generally, this can result in faster queries, but it can also slow down some queries, so there is a risk involved with it. Microsoft CSS does not usually recommend this setting as a general approach/resolution.
It’s usually a better idea to find ways to improve the queries without using cache policy; it should only be used as a last resort, and only under the direction of CSS. We generally do not make the details of the cache policy settings public, because they do carry risks, and they are intended only for special cases that should be determined by customer support.
Makes sense, but how would I have ever come to the conclusion that the issue was actually with the function we were using to perform the calculation? Extremely odd behavior if you ask me and this is pretty scary. Hopefully they will come up with a solution to resolve the incorrect cache reuse. How do I know that this is the only scenario that is going to return incorrect results?
Subscribe to Blog feed
Dan English's BI Blog 