We are using AS 2005 and Excel 2003 as the client front end. I have one particular view built in an Excel Pivottable that takes about 10 minutes to return results.
I was pretty sure that the aggregations should support a quicker response so I used XMLA on the server to look at the Sessions whilst the query was running. Using this I grabbed the MDX and ran it interactively to see if I could figure the problem .... it returned in 1 second when I ran it interactively !
Anyone any idea what could be happening here?
I do notice that when I use XMLA to look at Locks while the query is running that the Excel process does have a Lock. It has a <LOCK_STATUS> of 1 and a <LOCK_TYPE> of 8 ... I have no idea what this means or even if it is related.
Thanks for any help
Could you give some idea of what the MDX query sent by Excel looks like? And how is the view organized (rows, columns, data, filters, ..)?|||Sorry probably should have provided this to start ...
Here is what I see ... I open my Excel 2003 spreadsheet and hit Refresh Data ... the following is the MDX that first goes to the server and returns within 5 seconds
SELECT
NON EMPTY
CROSSJOIN(
CROSSJOIN(
HIERARCHIZE(
AddCalculatedMembers(
{DrillDownMember(
{{DrillDownMember(
{DrillDownLevel(
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[All]})},
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Grand Total].&[2]})}},
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Sub Total].&[2]})})),
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Cost Center].[Cost Center].[All]})}))),
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Cost Element].[Cost Element].[All]})})))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [CONS_OPS_CO_TRANS_CUBE]
WHERE
([Measures].[Cons USD Amt],
[Cost Center].[Company Code].[All],
[Cost Center].[Site].&[18],
[Time].[Time Hierarchy].[Year].&[2006],
[Cost Center].[Profit Center Hierarchy].[All],
[Source].[Source].[All])
I then filter a couple of things on the Ops Cost Stmt and the following MDX goes to the server but takes over 10 minutes to return
SELECT
NON EMPTY
CROSSJOIN(
CROSSJOIN(
HIERARCHIZE(
Except(
{AddCalculatedMembers(
{DrillDownMember(
{Except(
{AddCalculatedMembers(
DrillDownMember(
{DrillDownLevel({[Cost Element Grp 1 Dim].[Ops Cost Stmt].[All]})},
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Grand Total].&[2]}))},
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Grand Total].&[3],
[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Grand Total].&[0]})},
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Sub Total].&[2]})})},
{[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Grand Total].&[3],
[Cost Element Grp 1 Dim].[Ops Cost Stmt].[Grand Total].&[0]})),
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Cost Center].[Cost Center].[All]})}))),
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Cost Element].[Cost Element].[All]})})))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [CONS_OPS_CO_TRANS_CUBE]
WHERE
(
[Measures].[Cons USD Amt],
[Cost Center].[Company Code].[All],
[Cost Center].[Site].&[18],
[Time].[Time Hierarchy].[Year].&[2006],
[Cost Center].[Profit Center Hierarchy].[All],
[Source].[Source].[All]
)
What is particularly confusing is that when I take this second MDX command and execute it via Management Studio, it returns in less than 1 second. It returns 4 rows and 441 columns of data
Thanks for any help
|||
A couple of questions:
- Is [Measures].[Cons USD Amt] a calculated measure and, if so, is its Non Empty Behavior property set?
- If you're currently excluding hidden items from totals in the pivot table, does the data come back faster if you select "Include Hidden Items in Totals" on the PivotTable toolbar? This could be a difference between executing the query from Excel 2003 vs. Management Studio, since Excel may use the Visual Totals mode:
http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/fe7f645622c760b0
>>
in this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anserd...
What the paper calls 'Visual Totals Mode' is switched on by using the
'Default MDX Visual Mode' connection string property, and this is what Excel
uses to calculate its subtotals. So once you know that the subtotals are
really just parent members with their values overwritten, it's clear that
calculated measures are going to be evaluated after this has taken place and
so aren't going to be affected - and that's why, to get the behaviour needed
in this case, it was necessary to create a real measure and get the desired
values into it somehow.
>>|||
[Measures].[Cons USD Amt] is not a calculated measure ... but thank you .. when I turn on the "Include Hidden Items in Totals" on the PivotTable toolbar, the answer comes back immediately
now I need to read the article you attached to understand why ... :-)
|||
Deepak,
Thank you for your suggestions to this point. Unfortunately my users do not accept the turning on "Include Hidden Items in Totals" on the PivotTable toolbar as an acceptable solution because it destroys the integrity of the sub totals.
I am still really confused by all of this. I checked the query sent to the server in both cases (when the button in Excel is on and when it is off) and it is the EXACT same MDX in both cases. However when the button is on it takes 62 milliseconds to run (according to the Session XML returned) and when the button is off it takes 499515 milliseconds to run. So it would appear that the button has an effect (somehow?) on the server side even though I would have expected the button to reduce client side CPU time after the query returns ?
Any further suggestions welcome ...
Thanks again
|||
I am very confused to ask such simple question but how do you turn off the "Include Hiddens Items in Totals" Excel pivot feature assuming you use Excel GUI to build and access your pivot?
I see an option called "Subtotal hidden page item" in the pivot option form but it is greyed-out.
Thanks,
Philippe
|||I finally managed to display the toolbar in question and changed the button "Subtotal hidden page item" It does not make any difference.
I have a related question. Everyone says, make sure you set the Non-Empty behavior.
OK Well but, questions.
-1) if I have a measures which uses the aggregate SUM I would assume SUM eliminates nulls and non-empty will cube still not consider this as empty? it can be 0 and never empty?
-2) if the calculated measure uses multiple measures, what if the best bet, Test the non-empty only on the most proeminent measure or set non-empty to test all the measures?
-if What I think in case 1, aggregate eliminates nulls, then is NON-EMpty of any benefit? Can I get any speed boost if I make sure I have nulls instead of 0 in the aggregated measure result? Just thinking loud, removing all 0 from the base table and instead of using a straight SUM aggregated measure, use one that has NULLS then base the calculated measure on that one? Or DO NOT use non-empty because by definition nothing is empty when the measure you test uses an aggregate?
Again, I am just thinking loud.
There is a problem given the number of posts about this issue, any workaround would be greatly appreciated.
Without calculated measures, 2005 cubes update 10 times faster on the client than their 2000 counterpart, with calculated measures, you can still update the client which was not always possible before but you "walk your dog" while it is done as it has been advised in this forum. (OK now I am going to walk the 5 miles with my Rottie).
Regards,
Philippe
|||Hi ,
When you say: "by definition nothing is empty when the measure you test uses an aggregate", are you referriing to a calculated measure which uses MDX functions like Sum(), or a base measure? In either case, the value should be null when the set to sum is empty, or when there is no fact data underlying a cell. Eg:
>>
With Member [Measures].[TestEmpty] as
iif(IsEmpty(Sum({}, [Measures].[Order Quantity])),
"True", "False")
select [Measures].[TestEmpty] on 0
from [Adventure Works]
TestEmpty
True
>>
My understanding is that, when selecting the "Non Empty Behavior" measures, all measures that need to be null (to guarantee that the calculated measure is empty) should be included.
|||
The MDX is the same because the button only toggles the Visual Totals mode (a connection property), which is described in the paper as follows:
"Visual Totals mode applies a proprietary algorithm on a query, after the resolution of axes, to determine which members on each axis are viewable. Then the algorithm aggregates totals for cells based only on the viewable members on each axis. For each cell with an aggregated total, that is, visual total, the information is stored in a separate area and referenced from the original cell. The pointer for each cell that has a visual total now points to the area in memory that contains the visual total instead of pointing the area in memory that contains the original total. The user, when referencing the cell, receives the visual total instead of the original total. Because of the referencing behavior of Visual Totals mode, this mechanism retains state and is applied on a dimension-by-dimension basis."
I 'm not sure exactly why this makes such a big difference in your case; but if you analyze the hierarchy in the query, you could check how many members represent totals which have to be aggregated "on-the-fly" from their visible children. This may take much longer than fetching the pre-aggregated value directly. My guess is that, if you deselect members at lower levels, this will result in more "visual total" aggregation and hence time.
|||Okay I think I understand what is happening now. Doesn't leave me any better off but at least I understand the architecture a bit better. I hadn't realized that everything is happening server-side regardless of the Excel button state.
I was assuming since the MDX I saw via the Session XML was the same in both cases that the totalling was being taken care of on the client side (the measure is just a simple standard SUM) for the case where Visual Totals mode is on and the aggregated totals from the cube were being ignored by Excel. I now understand that there is a second query being performed on the server using the VisualTotals functions to get the Sub and Grand Totals but I obviously don't see this second query via the XMLA Session query and it is clearly this second query which takes all the time.
I guess it is a little disappointing that it goes to the cube again to so this second query against the low level data as opposed to just calc'ing the Visual Totals from the resultset returned from the first (60ms) query. Since it is not a calc measure involved here but rather just a simple sum, the Visual Totals can easily be calc'ed from the small resultset returned by the first query. I don't suppose there are any tricks/settings that can force that to happen?
Thanks for your help and input.
sql
No comments:
Post a Comment