In some cases the Analysis Server returns wrong results: If You at first make
a query containing several restrictions and after that make a similar query with less
restrictions, You sometimes get the result of the first query (which is wrong
for the second query).
Of course this error does not occur in every situation. In our environment it occurs
when CustomRollupFormulas of ParentChild-Dimensions are involved.
My questions:
- Has anyone seen this error before?
- Is there a way to report this as an error to microsoft?
- Are bug fixes or workaround available?
(At the moment we have SQL Server 2005 SP2, Version 9.0.3042)
Any help is welcome.
The following contains a description of a small sample where this error occurs.
(A Cubedefinition and the underlying Datawarehouse can be provided).
Detailled Description:
=====================
The OLAP-Database contains:
- A Year dimension with one member '2007'
(to make this example simple we have no quarters, months and so on)
- An Account dimension with Attributes "Key", "UserKey" and "Type" and 3 elements:
Key: UserKey: Type:
1 "1000" A
2 "2000" B
3 "3000" C
- A dimension for KPIs with Attributes "Key" and "Parent". "Parent" is a ParentChild
Dimension. This dimension has a CustomRollupColumn with MDX-Formulas.
The dimension has two elements:
Key: Designation: Parent: MDX-Forumula
-1 All NULL NULL
1 KPI 1 NULL Sum(
Filter(
CrossJoin(
{
([Account].[UserKey].&[1000]:
[Account].[UserKey].&[3000])
},
[KPI].[Parent].&[-1]
),
[Measures].CurrentMember > 0
)
)
- A fact-table with 3 lines:
AccountKey: Amount: Year: KPIKey
1 1000.00 2007 -1
2 2000.00 2007 -1
3 -4000.00 2007 -1
We use two Queries:
- Query 1:
SELECT {[Measures].[Amount]} ON 0,
NON EMPTY
AddCalculatedMembers(
Hierarchize(
Descendants(
{[Year].[Key].&[2007]},
[Year].[Key].[Key],
SELF_AND_BEFORE
)
)
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_TYPE ON 1
FROM [PRO110530]
WHERE(
{
[Account].[Type].&[Type A],
[Account].[Type].&[Type C]
},
[KPI].[Parent].&[1]
);
This query first restricts the accounts to "1000" (Type A) und "3000" (Type C)
and the formula of "KPI 1" filters out account "3000".
The correct result is "1000.00".
- Query 2:
SELECT {[Measures].[Amount]} ON 0,
NON EMPTY
AddCalculatedMembers(
Hierarchize(
Descendants(
{[Year].[Key].&[2007]},
[Year].[Key].[Key],
SELF_AND_BEFORE
)
)
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_TYPE ON 1
FROM [PRO110530]
WHERE(
[KPI].[Parent].&[1]
);
This query is nearly the same as query 1, but the accounts are not filtered out
by their type. Therefore the formula of "KPI 1" applies to all accounts and
only account "3000" is filtered out.
The correct result is "3000.00" (Sum of accounts "1000" and "2000")
Analysis Server returns this result if You run Query 2 as first query.
The error occurs in the following case:
- Process the cube (or restart) the Analysis Server.
- Connect to the database.
- Run Query1:
The server returns result: "1000.00". That's correct.
- Run Query2 (on the same connection):
The server returns result: "1000.00". That's wrong.
The error does not occur in the following cases:
- If you restart the server between Query1 and Query2.
(That is no pratical workaround).
- If You do not put the Year-dimension on Axis 1
(In real life that is no practical workaround, because we have a more complex
Time-dimension and want for instance to see two years on Axis 1)
- If You use different connections for the two Queries
(That is no workaround for us, because we use a third-party tool, which
calls the Analysis Server)
- If You add the VisualTotals-function to the MDX for Axis 1
(That is a bad workaround, because the runtime of complex KPIs rises up significantly)
It's hard to tell, but it almost feels like an issue with the formula cache.
Your options would be
1. Ring Product Support - If you need to get a fix in a timely manner, then this is your best bet. You would have to check when you call, but I think you would get your fee refunded if the call is confirmed as a bug in the product.
2. You could log the bug in the SQL Server section on the connect website connect.microsoft.com, but you are not guarenteed of a timely response (the product team does go through these issues, but there is just no telling when this will happen)
It could be something to do with the CustomRollup formulas, have you tried turning off the custom rollups and moving the calc into a scope statement in the calc script? If this work around does fix your issue I would still encourage you to log a bug on connect.
|||Other than filing a bug, if you need an immediate workaround, try issuing the following ClearCache command through XML/A between the two queries.
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Execute xmlns="urn
<Command>
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Your Database ID</DatabaseID>
</Object>
</ClearCache>
</Batch>
</Command>
<Properties/>
</Execute>
</Body>
</Envelope>
|||Good idea Jeffrey, I did think of this, but forgot to mention it. If it is a cache issue this would probably work too. The only issue being that you cannot "chain" this command onto the start or the end of a query, so the problem then becomes "how do we know when to run this command".
I also have a variation on this command wrapped in a stored proc on the AS Stored Procedure project on codeplex www.codeplex.com/asstoredprocedures - it is not part of the lastest release, you would have to download the source and compile it (we are planning to compile another release soon), there is docmentation on this command on the codeplex site.
|||Darren and Jeffrey,
thank Your for the replies.
Unfortunately I can't clear the cache, because I only fill the OLAP-Database and the Database is called by a third party tool.
Therefore I will report this problem to Microsoft Product Support at next.
Hopefully they know a workaround or have a hotfix.
|||We reported the error to Microsoft report. I'm citing from the answer:
"...the problem is really because of the CustomRollupFormula, where we are seeing performance issues as well as problems like yours. Our developer team recommended that these people should start using “Cache Policy = 9” with SP2 in order to obtain better performance (connection string setting). Now, Cache Policy is not doable in SSMS or BIDS, so what you can do is to change this setting server wide by changing inside the msmdsrv.ini: <CalculationCoverPolicy>9</CalculationCoverPolicy> Rebooting the server will use this new setting. Something to remember is that this will change the behavior of the query engine. So, some of your queries will go faster and some slower. So, remember that and monitor the server."
We changed this setting and the error does not occur anymore. Moreover the performance of some testcases which use custom rollup formulas was significantly better. I hope the overall performance of the system remains OK with this setting.
My question at this point is: Custom Rollup Formulas are a very powerful part of Analysis Services -- You can create very interesting solutions with them. But it seems to me that the use of Custom Rollup Formulas is not in the main focus of the Analysis Services development team. Otherwise I could not explain the error and performance problems in SP2 (the performance in SP1 was better!). Perhaps it would help, if Microsoft has real-life examples of the usage of this feature. Is it possible to establish a deeper contact between the development team and users of custom rollup formulas to exchange testcases and experiences for this topic?
|||have you written any custom aggregations - either through xlma or the aggregation design wizard?