RS Aggregate and Manual MDX

In a recent post, I said RS Report Designer is smarter than I thought. And it is. But it is also really, really weird when you try customizing your MDX. (Not that I ever do that.)

This might be a bug, but it seems to happen only when you customize the MDX. Here's a scenario:

1) Use the wizard to create a table report with Country, State-Region, and City levels. Use Sales Amount (additive) and Average Unit Price (non-additve) as measures, and include subtotals.
 [At this stage, the automatic MDX includes just the City level]

2) Change the Sum function for Average Unit Price (and Sales Amount too, if you feel like it), to Aggregate. [At this stage, the MDX changes to include the Country and State-Region attributes.]

3) Delete the Country group from the report. [The auto-MDX changes to remove the Country attribute, and the report still works fine.]

BUT. If between step 2 and step 3, you go make an intsy-weentsy change to the MDX, then the auto-MDX does not remove the Country attribute. IN THIS CASE, none of the lower-level Aggregate functions work. They all return Null. If you have unused higher-level attributes in your MDX, lower-level Aggregate functions return Null. 

The really wacko part is that if you never touch the MDX, and just let the Report Designer fiddle with it, everything seems to work OK. But if you do use the Aggregate function and you do customize the MDX statement, make sure that you have a grouping in the report for each attribute that has higher-level totals.

Incidentally, the place I ran into this big time was when I was decided to add an All level to a hierarchy in the cube. When I had the All level there, the report showed all blanks. When I removed the All level in the cube, the report worked. It was really strange. Why would a change like that in the cube definition make the report switch between showing correct values and showing nulls? Well, naturally, I was writing my own MDX, and I asked for [Hierarchy].Members, rather than explicitly cross-joining specific attributes. So having the All level in the hierarchy returned an extra level in the result set--a level that wasn't "used" in a grouping in the report, so all the Aggregate functions below that level returned Null. Removing the All level from the hierarchy made it so that all the levels in the result set were actually used in the report.

- Reed Jacobson

Happiness: RS Report Designer smarter than I thought

Ever since the earliest Yukon beta's I've been frustrated with how RS report wizard handles AS as a data source. The problem has to do with non-additive measures--for example, a distinct count. In the cube, you create a measure or calculated member that gets the right answer at every point in the cube. But when you use the RS wizard to create the MDX and the report, it only queries for the lowest level used in the report, and then uses Sum for all the higher levels. This is fine when it is a Sum, but doesn't work at all for Distinct Count.

Brian Welcker kept telling me that they added the Aggregate function and fixed the problem, but every time I tried, I still saw the same leaf-level MDX and Sum function. And I know that you can't get the right answer with only leaf-level MDX. So I would change the MDX to return the multi-level values, and then change the report to use the Aggregate function instead of Sum, and it did work. But it really didn't make a good story.

Well, earlier this week, I discovered the secret. I had always fixed the MDX first, and then changed the Sum function to Aggregate. That was obvious to me, because how would Aggregate get the higher level value if the MDX is wrong? Well, I "accidentally" changed the Sum function to Aggregate first, and when I previewed the report, it showed the right answer. How could it do that? Switching to the data pane, the MDX was still the old detail level. So I used Profiler to capture the MDX, and the MDX was different--it included the additional levels.

The bottom line is that if you use the MDX designer to create the MDX, and then modify the report to use the Aggregate function instead of the Sum function (assuming you have non-additive measures), then the report designer changes the MDX to make it work. When I think about it, this really does make sense. If all you have is Sum formulas, it's more efficient to just add them up in RS. RS can't really know whether a measure is additive or not, so it does default to use the Sum function. But all you have to do as a user is build the report (with the wizard) and then go change the function for any non-additive measures to Aggregate. The report then adjusts the MDX to make it work.

It's a bit creepy for me to have it do all that "automatically" for me. (The windows on my car open with crank handles--or zippers--and don't even talk to me about automatic door locks.) But it actually does make sense and it does work. Maybe it's even documented in the new July BOL ;-).

- Reed Jacobson

Top 10 Plus Other Query

On Teo Lachev's forum at Prologika, there was a question from gudjonv about how to do a RS chart that showed the top 10 members plus an "all other" member (thus, 11 slices of the pie). [See http://prologika.com/CS/forums/1465/ShowThread.aspx]. I didn't feel like registering so I could post a response, so I'll put the answer (converted to Adventure Works) here.

This is the general method I've used to do "Top 10 Plus Other" since OLAP 7.0. I can't think of any compelling functionality in 2005 that would do the job better. (Of course, in 2005, the Aggregate function will work even with a distinct count measure, so that makes this solution more functional, but it doesn't change the solution.)

WITH
  SET [TCat] AS TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
  MEMBER [Product].[Subcategory].[Other] AS
Aggregate([Product].[Subcategory].[Subcategory] - TCat)
 SELECT { [Measures].[Sales Amount] } ON COLUMNS,
 TCat + [Other] ON ROWS
FROM [Adventure Works]
;

- Reed Jacobson

Sadness: Excel 2007, Subcube Subqueries, and Growth Formulas

When I first saw a demo of Excel 2007 in the Beta 1 timeframe, I was told that the Slicers in Excel 2007 would still use the WHERE clause, not the new Subcube Subquery available in SQL2005. I was very disappointed (partly because in the beta timeframe for SQL 2005, the Where clause behaved so badly). But when I got my copy of Excel 2007 and looked at the actual MDX, it turns out that they do use the subcube subquery. At first I was happy, but then I discovered a strange consequence of using the subcube subquery (while, in a bit of irony, by RTM the relative benefit of a subcube subquery has decreased because they fixed the behavior of the Where clause).

Suppose you have a "Sales Growth" measure. In all likelihood, the MDX will include something like ([Measures].[Sales],[Time].[Calendar].CurrentMember.PrevMember). Now suppose you have a query with [Nov 2007] as the slicer. If the slicer goes into the WHERE clause, then the current member of Time.Calendar is [Nov 2007] and the prev member is [Oct 2007] and all is good. But if the slicer goes into a subcube subquery, then the Time.Calendar dimension gets collapsed to [All]-->[2007]-->[Q4]-->[Nov 2007], but the current member is [All], not [Nov 2007]. With the subquery, the value of [All] equals the value of [Nov 2007], so the basic measures look alright, but the prev member for [All] is Nothing, so the growth calculation screws up.

The only way I've been able to get the correct growth to show up is by explicitly putting the Time dimension onto the Row or Column axis, so there's a meaningful CurrentMember. It works, but it looks a bit stupid.

I was hoping that the Existing operator (that I recently discovered) would provide a workaround for the problem (because the formula could then use Existing Time.Calendar.Month.Members. But, sadly, the "current context" for the calculated member does not take into consideration the subcube subquery restriction, so you get the entire set of months, not just the "current" month. So this approach did not work.

If anyone has an idea for how to write a formula that properly calculates growth, even when the filtering is done by using a subcube subquery, I'd love to hear it.

- Reed Jacobson

New Existing operator

I just found out about the Existing operator. You put it in front of a set and it filters the set by using all the current members in the current context. This simplifies using Attributes without User Hierarchies.

For example, suppose you need to do the equivalent of the LastChild aggregation function, but with the Standard Edition: Use the Existing keyword to find the last period. The issue is that without a multi-level time hierarchy, how do you know which is the "last" month in the current filtered space. So Existing Time.Month.Members is essentially equivalent to Descendants(Time.CurrentMember,Time.Month), except that it doesn't require a User Hierarchy. Thus Tail(Existing Time.Month.Members) give the last month in the current filter set. (Note that this doesn't give the Last non-empty member, just the Last Child.)

- Reed Jacobson

Visual Totals and Row Filters

I was troubleshooting with a client about some slow queries using Excel (2003) as a client for AS2005.

We had a large (customer) dimension on rows, and measures on columns. We added a second customer attribute (region) to rows. The pivot table returned very fast. As soon as we filtered by a region member (one or more), the report slowed to a crawl. Putting the region into the slice (filter) area and then selecting one or more items was fine, but with region on rows, it was slow. The problem is that users want to see the region next to the customer name.

Turning off Visual Totals gave fast results as well, but that allowed unwanted customers to make it through the filter, and the grand total was not what we wanted. In effect, we wanted the meaning of Visual Totals (with Region included on rows), with the performance of Region in Slicer (or no Visual Totals).

The best workaround we could come up with was to create two instances of the Region attribute, so there's a regular Region attribute (which goes on rows) and a Filter Region attribute (which goes into slicer). Filtering by the one in the Slicer area allows the report to be fast, even with Visual Totals, but still lets the user see the regions in the body of the report. We had to create similar parallel attributes for any customer attribute the user might want to show on rows--while simultaneously using as a filter.

My speculation as to the reason for this is that if the region filter is in the Slicer, it's the core cube "pyramid" engine that does the operations, and it's fast. Whether Visual Totals is specified or not is irrelevant as long as the Region is in the Slicer, because no "total" is being re-calculated by MDX. If the Region filter is in rows, then the All member of the Region attribute (which is implicitly there for every customer) has to be recalculated as a Visual Total by MDX, and that's really slow. Any time the MDX has to calculate for each member of the Customer hierarchy (whether it eventually gets filtered away by Non Empty or not), the overall report is slow. If an MDX expression only has to calculate for the rows that end up in the final report, it's really fast. The moral for me is to keep the high-cardinality operations in the Pyramid engine and restrict the Calculation engine to relatively small scope.

- Reed Jacobson

 

MDX and NON EMPTY

A few times over the past couple of months I have run into a situation where removing NON EMPTY from a slow MDX query radically improved the response time. The symptom was clear, but not predictable. Earlier this week, we were able to pin down at least one specific scenario that definitely triggered the NON EMPTY slowness.

We were using Excel (2003) as the client, and had a large set (customers) on rows. Excel's default NON EMPTY behavior was filtering away the uninvolved customers. Everything worked well until we added a second measure. The query went from 1 second to 10+ minutes (or so). The measures were both calculated members that did a direct reference to base measures, and they did have the NonEmptyBehavior property set to the base measure.

After some troubleshooting, we observed that the speed hit happened only when the measures were on the Rows axis. If we put the measures on the Columns axis (i.e., a different axis from the large set), it went back to being really fast. The reason adding a second measure slowed it down was because with one measure, Excel puts the measure into the Where clause, but with two, it defaults to put the "Data" on rows.

So the solution (aka workaround) is to simply add the multiple measures to the report before adding the large dimension. With two measures on the report, you can move the measures to columns and then add the large dimension. It's a bit of a pain, but the difference between 10 minutes and 1 second is worth it.

My speculation is that 2005 is trying to optimize NON EMPTY behavior so that it's similar to the speed benefits of NonEmptyCrossjoin. If it gets the optimization right (which happens when the measures are on a different axis), it's blazingly fast. If it gets the optimization wrong (which happens when the measures are on the same axis), then the calculated member is evaluated for each member of the large set and it's distressingly slow.

- Reed Jacobson