Excel Pivot, OLAP, Top N calculated member?
-
I'm trying to limit a pivot table based on a Top 10 of a calculated member (measure) rather than an actual measure from a fact table. For some reason, the Excel Pivot table seems to return a random, limited list that does not represent the top ten of anything.
However, if I switch the the top 10 to be based on an actual measure in the fact table, the pivot table correctly returns the top ten results. It seems as if excel has no way to assess the top N of calculated members because those members don't exist as actual aggregate data.
Does anybody know how to make the pivot table correctly return a Top 10 based on a calculated member?
-
The first thing I would do is capture the MDX Excel is sending to the server so you can determine why it's going wrong, additionally you could always send it through to Msft as a bug if it is one. To get the mdx, add the 'Log File=<Drive>:\<path>\<filename>' property to your connection string (e.g Log File=C:\log.txt). You'll find your connection information by searching your documents and settings directory for *.oqy files.
If you could post the mdx I'd be interested to see it.
A quick question regarding the excel report -> You've obviously used the Field Settings|Advanced|Auto Show (top 10) to show the top 10, have you changed the sort order of the members from the default (which looks to be cube order)? I did a quick test here tonight with a calc measure and at first thought the result was incorrect, but after sorting in descending order, I saw it was actually correct.
Steve.
-
Steve,
Thanks for the reply -- but still have problems. The Excel pivot table is directly querying the external olap data (using the pivot table wizard), so I don't write any MDX in excel myself. Consequently, I'm not sure your initial suggestion about viewing the mdx is possible. (I suppose if I were obtaining the data via VBA, I'd have some MDX to provide.) Is there a way to get at the MDX that is generated from the pivot table wizard?
As for your second question, yes, I did use Field Settings|Advanced|Auto Show, (top 10), but I also specified descending order, yet it made no difference. How did it work for you? If I can get it to work on my end, then my problem is solved.
--Pete
-
Pete,
When you set the descending order, did you change the 'using Field' value to be that of the measure (calculated member)? Can you get this top 10 (with ordering) work with say Foodmart:Sales using profit as the measure (it's a calc one) and maybe stores at the city level on rows? This works for me but profit is an extremely simple calculated member. Is your calc member complex or simple?
I still think capturing the mdx is worthwhile. If you revisit my initial post it has the steps required to enable you to capture the MDX generated by the pivot table in excel.
Steve.
-
Steve,
(thanks for sticking with me on this...) Yes, I've specified in the pivot table that descending order be performed on the calculated member (CasesYTD), and that it identify the Top 10 by CasesYTD. In the meantime, I've obtained the mdx as you suggested.
Below is the mdx I've manually programmed in the MDX Sample Application to generate the desired results I'm looking for: (i.e., top ten procedures by casesytd in yr2003) -- works like a charm.
select {measures.casesytd} on columns,
topcount(order([PROCEDURES].[PROC_FAMILY].members, ([Measures].[casesytd]),bDESC),10) on rows
from test2
where[TIME_INCURRED].[All TIME_INCURRED].[2003]
BUT...Excel is generating the following mdx: (looks insane!)
SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownLevel({[TIME_INCURRED].[All TIME_INCURRED]}))}, {[TIME_INCURRED].[All TIME_INCURRED].[2004], [TIME_INCURRED].[All TIME_INCURRED].[2002], [TIME_INCURRED].[All TIME_INCURRED].[2001], [TIME_INCURRED].[All TIME_INCURRED].[2000], [TIME_INCURRED].[All TIME_INCURRED].[1999], [TIME_INCURRED].[All TIME_INCURRED].[1998], [TIME_INCURRED].[All TIME_INCURRED].[1997], [TIME_INCURRED].[All TIME_INCURRED].[1996]}))}, {[TIME_INCURRED].[All TIME_INCURRED].[2004], [TIME_INCURRED].[All TIME_INCURRED].[2002], [TIME_INCURRED].[All TIME_INCURRED].[2001], [TIME_INCURRED].[All TIME_INCURRED].[2000], [TIME_INCURRED].[All TIME_INCURRED].[1999], [TIME_INCURRED].[All TIME_INCURRED].[1998], [TIME_INCURRED].[All TIME_INCURRED].[1997], [TIME_INCURRED].[All TIME_INCURRED].[1996]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevelTop({[PROCEDURES].[All PROCEDURES]}, 10, , [Measures].[CasesYTD])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [test2]
WHERE ([Measures].[CasesYTD], [HARP].[All HARP], [PRODUCT].[All PRODUCT], [HCG].[All HCG])
Again, Excel's mdx merely returns a limited list of procedures that do not appear in any particular order. Seems like just an arbitrary 10 rows of procedures are returned.
It seems to me that the function 'DrillDownLevelTop' is the problem. What do you think?
--Peter
-
Got to love the Excel generated MDX....
The drilldownleveltop isn't the problem, it could be seen as basically the same function as your TopCount (from BOL heres the drilldownleveltop description -> DrilldownLevelTop(«Set», «Count»[, [«Level»][, «Numeric Expression»]]). So it basically does the same thing.
I can only assume that you've got the time dim on columns, with all years *except* 2003 unchecked, hence the exclusion of all the members...
To get a similar (prob never the same) MDX statement as your manual one, could you try throwing the 2003 member into a page filter rather than on columns? This would leave only your measure on cols as per your own MDX, and the procedures on rows, hopefully sorted and top 10'd (is that a word?:crazy
. It would also force the year into the Where clause (i hope).Steve.
-
Huh -- interesting...
Yup, putting the time dimension into the pages area of the excel pivot yielded correct results.
Here's excel's mdx:
SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevelTop({[PROCEDURES].[All PROCEDURES]}, 10, , [Measures].[CasesYTD])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [test2]
WHERE ([Measures].[CasesYTD], [HARP].[All HARP], [PRODUCT].[All PRODUCT], [HCG].[All HCG], [TIME_INCURRED].[All TIME_INCURRED].[2003])
What seems odd is that excel lists the procedures on the rows in descending order according to casesytd, but the mdx is actually putting results on columns. Putting the mdx into the MDX sample app yields data horizontally, not vertically as in the pivot, nor are the columns in any order. Huh?
So, it seems the pivot table problem maybe solved, but not sure how excel interprets the presentation of the data differently than the mdx sample app.
Again, thanks for your help.
--Pete
-
Glad you got it sorted.
The difference in returned results of excel vs sample app is that when the excel pivot control sees only column values, and the measure is in the where clause, then it keeps the measure on the column and throws the column values to rows. They put the "rows" on axis 0 because MDX won't let u skip axes (ie must have something on 0 if u want something on 1). Would be nicer all round if they acutally put the measure member on columns and the true rows on rows. Just out of interest, this doesn't change with then new Accelerator/addin (for excel xp and 2003).
Steve.
-
Steve / Peter,
Am I missing something here? You two have agreed that the problem is solved, but I see no solution. Whenever a dimention is used on columns (as Peter had in his original post), the Top 10 function realized by DrillDownLevelTop() simply returns a result that is unexplainable. It is not top 10. It seems to be something random.
The only way I could get Top 10 to work properly is to not have a column dimention (which basically defeats the purpose). It is hard for me to believe that Microsoft intended this feature to work like it does. What am I missing?
Alex
BTW, it seems to be immaterial wether it is a calculated member or a base table member. The results are still wrong.
-
Funnily enough I can't get it to fail for me now. The sorting/ordering of the members isn't right but the top n (5 in my case) seems to be correct.
Tested using Foodmart:Sales, Office/excel 2k3
Poor ordering but correct result :=
Products.Food.children on rows
Time.1997.children (except Q4) on cols
Measure is profit
Set top top 5 on the food members returns correct result (based on sum of Q1-Q3) but incorrectly ordered
Correct ordering and correct result :=
Products.Food.children on rows
Time.1997.children (except Q4) in filters
Measure is profit
Set top top 5 on the food members returns correct result ordered correctly.
cheers,
Steve.
-
I think I might have an idea of why Top 10 is not working right. It looks like the Top 10 functionality will always select the top 10 records based on the entire dimention which is on columns (e.g: over all Time). It appears to ignore what is currently selected to be on columns.
If the total population result and the selected set result match, you are are lucky, if they do not, the results make no sense to the user.
Alex
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply