MDX question

Hi All

Please help me with this task

One dimensional member (a particular route) has several fact records (driver completed several trips on the same route). Measures are: route_minutes, route_miles and route_flag which can be ether False or True. How do I write a query to total minutes and miles for this route but excluding rows having False route_flag? I know if I make route_flag as a degenerate dimension then query is doable, but I want to preserve an initial design.

Thank you

When you say you want to preserve the original design, do you mean that you don't want to change anything or that you don't want to add a new degenerate dimension?

The problem is that boolean values like the route_flag are not additive, so this is only possible if your granularity allows you to get right down to the row level and then you would need to get right down to the leaf of every dimension, filter out all the tuples with route_flag= 0 and sum them back up which will not perform well.

Could you alter the design by adding a could of measures? If it's only miles and minutes that you want filtered by this flag then I would suggest creating columns in the DSV which had logic like:

CASE WHEN route_flag = 0 THEN NULL ELSE route_minutes END

to create a measure for the route_minutes where the flag was true. You could repeat the same logic for the miles measure and you would end up with two filtered measures that would be additive. It would add next to nothing to the processing time and would be really fast at query time.

|||

Thanks Darren - great idea regarding 'CASE' clause. I didn't want to expand cube with another (degenerate) dimension, but willing to add 3 more fields to DSV. Another problem - I need to find an average of all trips so, I've added 3 more columns to the fact table – 2 for data and 1 for denominator (null or 1).

with

set currentRoute as {[Route].[Route].&[14]}

member [Measures].[Valid Routes] as

crossjoin(currentRoute, [Measures].[Include Leg]).Item(0)

member [Measures].[Average Miles] as

(currentRoute.Item(0), [Measures].[Leg Distance Miles]) / [Measures].[Valid Legs]

member [Measures].[Average Minutes] as

(currentRoute.Item(0), [Measures].[Leg Distance Minutes]) / [Measures].[Valid Legs]

select {[Measures].[Average Miles], [Measures].[Average Minutes], [Valid Routes]} on 0

from [Leg Data]