Hi,

I have a problem. Following is my fact(example).

id Product ProductSubType ModifiedDate RefDate
IdleTime (calc member)
================================================== ==========
1 P1 SP1 13-Jan-2006
15-May-2006 121days
2 P1 SP2 13-Jan-2006
15-May-2006 121days
3 P1 SP3 14-Jan-2006
15-May-2006 120
4 P2 SP4 15-Jan-2006
15-May-2006 119
5 P2 SP5 16-Jan-2006
15-May-2006 118
6 P3 SP6 17-Jan-2006
15-May-2006 117
7 P3 SP7 18-Jan-2006
15-May-2006 116

IdleTime (CalC Member) in days is RefDate - ModifiedDate in days.

Aggregation will be on idle time. i.e For example , For P1 , Idle Time
will be 121+121+120 days

For P3 117+116.

Now the problem is Idle time need to be calc member , Where
ModifiedDate and RefDate are NOT Time dimensions(There is one more time
dimension for this fact which is not mentioned). Thats why I thaught I
will have two more columns ModifiedDateinMins, RefDateInMins which will
be numeric columns calculated in Minutess with some base
date(ex:1-1-1900). .

Thus I can use those columns as measures for calculating datediff(We
can diff the column values and convert them to days). Now the problem
is this datediff should happen on every fact records under specified
rollup. i.e for P1 rollup , idle time should be calculated for first
three records and then aggregated. similarily for P3 idle time should
be calculated for last two rows and aggregated.

Please help me with MDX for it. ....

Could this be simply implemented as a "Sum" base measure, from an "Idle
Time" calculated field of the fact table - in which case no calculated
member MDX is required?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak, What do you mean by calculated field of fact table??? Yep I can
introduce a computed column at fact table level, but my fact table is
huge and spans across monthly partitions and I need to calculate this
metric for many rows based on some "Status" which may span across
partitions . And my ref date is current date. So, every day this ref
date changes. Iam planning to get this ref dateas measure from
different measure group for calculating this idle time.

Could you please give me a hint, If we need to write MDX , How we can
do that??

Deepak, frankly I didnt understand what is base measure in my case. May
be that is the solution of my problem.

Actually , Ref date which i have shown as part of my fact is not in my
fact because this date always changes , it needs to refer current date.
So, I cannot have it in every row of fact. Iam planning to have that
date in seperate fact as single row as different measure group in my
cube. Every day this date changes and measure group is reprocessed.

Now , Every day my Idle time calculation need to happen based on my
latest ref date and get aggregated based on dimensions.

Can you please help me in this. Thanks.

If the cube can be re-processed daily, then Idle Time can be added as a
named calculation, taking the current date into account. For example, in
SQL Server, it could be like:

DATEDIFF(day, ModifiedDate, GETDATE())


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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