How to use information from a dimension in a calculated member

It would be really useful to be able to extract information from an Analysis Services 2005 dimension such as:-

"The number of seconds in the current time dimension member".

This will enable calculated members such as the following to compute a process percent operation time.

Member = [Measures].[Sum of seconds the process was operating] / "No of seconds in the current time dimension member" (eg.Day, Week, Month etc.)

How can I get this information from the dimension for use in a calculated member?

Many thanks

There could be various approaches to this - here's an example for Adventure Works, which computes the number of seconds for the selected Date dimension members, based on counting the number of days (leaf level) which "exist" with the currently selected Date members:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from [Adventure Works]

where {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]}

-

Seconds
172,800

>>

But this approach may not work as desired with nested from sub-cube queries:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from

(select {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]} on 0

from [Adventure Works])

Seconds
100,051,200

>>

A more tedious approach, which will work in both scenarios above, is to create a separate measure group on the Date dimension table, with a Named Calculation like: Seconds = 24 * 60 * 60. Then a "Seconds" sum measure can be created for this Named Calculation, which returns the seconds spanned by the selected Date members.

|||

Thanks for the suggestions Deepak however what I am trying to do does not directly involve the time dimension.

I have another dimension called Shift which contains information about the process operator shifts. The shift dimension information comes from a SQL table which has ShiftID, Shift Start Date Time, and Shift End Date Time fields. The main process data table links to the shift table using ShiftID.

Using the shift dimension I can now extract from the cube the process run time per operator shift - all works very well so far.

What I now want to do for each of the shifts, is to set up a calculated measure to compute the shift length in seconds based on the shift start and shift end fields in the shift dimension. I can therefore compute the percent run time per shift using this information together with the process run time.

I am very new to MDX so any help would be appreciated.

Thanks in advance.

|||

Add a Named Calculation to the Shift dimension table, and make this an Attribute (its hierarchy doesn't need to be enabled) - the attribute value can be set equal to the key (ie. integer):

ShiftLength: datediff(second, "Shift Start Date Time", "Shift End Date Time")

Then, [Measures].[ShiftLength]: [Shift].[Shift].Properties("ShiftLength", TYPED)

|||

Hi Deepak,

I have tried out your suggestion:-

- Set up the calculated member ShiftLength which works fine
- Added ShiftLength as an attribute to the Shift Dimenson (Property of the key ShiftID)

I can now browse the shift dimension and see the ShiftLength member property which is fine.

I have created a calculated member:-

CREATE MEMBER CURRENTCUBE.[MEASURES].[ShiftLength]

AS [Shift].[Shift ID].Properties( "Shift Length", TYPED),

VISIBLE = 1 ;

However for some reason no values are returned from the cube for this calculated member. What am I doing wrong?

Thanks for your continued help.

|||

A couple of things to consider:

Is "Shift Length" the name of the attribute, or ShiftLength - the same spelling should be used everywhere?|||

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

|||

Adrian West wrote:

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

I'm struggling a bit here
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=524766&SiteID=1

I changed mine to this, no luck (doesn't show up on Browser)

CREATE MEMBER CURRENTCUBE.[MEASURES].[% Capacity Sold]

AS [Dim Performances].[Dim Performances].Properties( "Perf Capacity", TYPED),

VISIBLE = 1 ;

What exactly goes into the 2nd [ ] bracket? Does it have to be a hierarchy, or can it be an attribute? Does it have to be "Key"?