Urgetn MDX question - Calculated measure depends on another dimension members
Author Message
Urgetn MDX question - Calculated measure depends on another dimension members

Dear all,

This is for the MDX specialists out there. I'm sure it can be done, but I've
wracked (or wrecked) my brains trying to figure it out.

I have a client whose basic business is moving things using cranes and other
systems. They gather data about the number of movements they've performed,
which fall into various categories, and the various delays they experience,
which also fall into categories. Not all systems perform all types of move
or experience all types of delay.
They need a cube to allow them to measure the performance of the systems
they have, giving measures like the total and average moves, the total hours
delay etc. So far so easy. They also have standard performance metrics which
are of the form "Net productivity =Total moves of type x, divided by (total
duration less delays of type Y).

There are two ways to build this cube, a brute-force method, and, I hope, a
better method.
1)The brute force method has a fact table with one line per crane or system
per job, which has a measure for every type of move or delay which can
possibly exist, and also the total duration of the job. It is then easy to
create calculated measures which are the sum of the moves over whatever
combination of delays is required. This will then roll up across multiple
jobs etc. and all be fine The problem with this is that the fact table is
VERY wide, full of zeros, and is not extensible if they think of another
type of delay, or another type of movement (which they will). You also can't
drill on delay or move hierarchies.
2) The better method (I think) is to have a fact table with one line per
crane/job, and to have an ActivityID key which is the FK to an Activities
dimension, containing all the move types and delays. The measures on the
fact table are then Moves (an integer which is populated with the number of
moves for Move activities) Hours (a decimal populated with the duration for
delay activities), and Duration (the duration of the job). I can then create
"Gross productivity" which is simply moves/duration (with suitable divide-by
zero traps). What I can't work out how to do is to create a calculated
measure which is "Net Productivity =Moves/(Duration - (Hours if the activity
is of type Y))".  Sample table definitions given at the end.

I'm sure this ought to be possible, but I can't get any MDX syntax to run.
This is gettnig urgent as I need to start cutting code this week, and I
really don't want to have a hugely wide and infflexible fact table if I can
help it. There might of course be a third way, any suggestions welcome!

Regards,

Richard

Simplified Table Definitions:
tbl_Job_Fact:                tblActivity_Dim
KeyMeasures
**********                *************                        **********
SystemID                     ActitvityID
Moves/Duration
TimeID                         DelayOrMove
Moves/(Duration-All Delays)
ActivityID                     SubCategory
Moves/(Duration-Delays of SubCategory X)
Moves                          ActivityDescription
Tonnes/Duration
Tonnes
Tones/(Moves Where Move is of SubCategory Z)
DelayHrs
etc. etc. etc.
DurationHrs

Sat, 28 Feb 2004 18:07:48 GMT

 Page 1 of 1 [ 1 post ]

Relevant Pages