I haven't written any MDX for about 7 years, but I'm trying to give
some advice to my developers.

Let's say we have a cube with these dimensions:
Time
Location
Product
and the metric
Quantity

Time is Year | Month | Day

Let's say that our data source gives us inventory as a daily snapshot
by product and by location. So if I want to see total inventory for a
day at any location or product, all we have to do is aggregate with SUM
for the given filter (for one day). But we want a report that shows
total inventory by month, and each month should be an average of the
daily totals. So I want to SUM over all of the non-time dimensions and
then AVG those results over the time dimension. I seem to remember
using SOLVE_ORDER to accomplish this, and I've been searching on the
web for an example that supports my hypothesis, but I haven't found
anything yet. Am I searching down the wrong path? Can anyone offer some
specific examples of the code for this?

chuck

Hi Chuck,

You can refer to the MDX Solutions book (link below) for a deeper
discussion of SOLVE_ORDER, but it typically needs to be tweaked when
dealing with intersecting calculations along different dimensions. In
your scenario, if you only need a calculated measure which averages the
Quantity measure over time, SOLVE_ORDER shouldn't be a concern (assuming
that summation over other dimensions is intrinsic to the Quantity
measure). If you're using AS 2000, something like:

Avg(Descendants([Time].CurrentMember, [Time].[Day]),
[Measures].[Quantity)

With AS 2005 Enterprise Edition, the Quantity measure could directly
have "Average of Children" semi-additive behavior.

MDX Solutions book link:

http://www.wiley.com/WileyCDA/WileyT...471748080.html


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,
I apologize that my MDX knowledge is so rusty that I didn't follow your
answer.

Let's say that I have a cube with three dimensions:
Date
Location
Product
and 1 metric:
Quantity_on_hand

What I'm looking at is an inventory cube that has daily snapshots of my
inventory, by product, at each location.

Date Location Product Q_on_hand
10/1/06 Houston Sable 100
10/1/06 Houston Taurus 250
10/1/06 Galveston Taurus 100
10/1/06 Austin Taurus 250
10/2/06 Houston Sable 50
10/2/06 Houston Taurus 250
10/2/06 Galveston Sable 25
10/2/06 Galveston Taurus 125
10/2/06 Austin Taurus 280

So if I look at totals by location for 10/1/06, I expect to see
10/1/06 Houston 350
Galveston 125
Austin 250

If I look at totals for the month by location, I expect to see
10/06 Houston 325
Galveston 125
Austin 265

I need to sum across product and location and average over time.
Is there a single MDX statement that can do that?
chuck


Deepak Puri wrote:

Chuck,

Assuming that you're using AS 2000, you can try something like this
(won't work with multiple selections of Date):

With
Member [Measures].[AvgQuantity] as
'Avg(Descendants([Date].CurrentMember, [Date].[Day]),
[Measures].[Quantity_on_hand])'

Select {[Measures].[Quantity_on_hand],
[Measures].[AvgQuantity]} on 0,
[Date].[Month].Members on 1
from [InventoryCube]


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***