Showing posts with label member. Show all posts
Showing posts with label member. Show all posts

Friday, March 9, 2012

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"?

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"?

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"?

Sunday, February 19, 2012

How to use a parameter in Calculated Member

I have an MDX query that has two calculated members that use dates.

Currently these dates are "hardcoded" in the calculated member definition.

I want to change this to use input parameters, thereby allowing the user to set the dates. I will then deploy the .rdl file to the Report Server. I have found no examples of using parameters in calculated members, so I am wondering if this is a bad idea.

I have explored the "filter wizard" in Visual Studio, and I am only offered the opportunity to filter on the fields that appear in the report.

I have made several attempts to alter the statement within Visual Studio 2005 while having the MDX statement in "Design Mode", but as soon as I attempt to execute the statement (or change to another view, such as "Preview", Visual Studio tells me that the "query cannot be retrieved from the query builder" and then continues with the (older) most valid query, which does not have reference to the parameter. (This is a Reporting Services project).


Here is a statement with hard coded dates that works properly.


WITH

MEMBER [Measures].[Completed Work On Period Start] AS

(

[Date].[Year Month Date].[Date].&[2006-04-30T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work On Period End] AS

(

[Date].[Year Month Date].[Date].&[2006-05-06T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed_Work] AS

(

[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]

)

SELECT

NON EMPTY

(

FILTER

(

NONEMPTYCROSSJOIN

(


[Assigned To].[Person].[Person],

[Work Item].[System_Id].[System_Id],

[Work Item].[System_Title].[System_Title],

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Measures].[Current Work Item Count],

4

),

[Measures].[Completed_Work] > 0

)

)

ON ROWS,

NON EMPTY

{

[Measures].[Work Item Url],

[Measures].[Completed_Work]

}

ON COLUMNS

FROM

[Team System]

You need to be in the MDX view to hand author the query to do this.

- In the Query Designer, switch to MDX view

- Change the query to be a parameterised query, e.g. replacing the date with StrToSet(@.Date, CONSTRAINED)

- Select the "Query Parameters" option from the toolbar, to define the parameter. You must provide a default value, that will be used when previewing/preparing the query

|||

Following Paul's guidance, I was able to edit the query without VS flagging the query as invalid.

BTW: I used StrToMember instead of StrToSet, since my parameter was in the Members section. The results of the query are valid.

WITH

MEMBER [Measures].[Completed Work On Period Start] AS

(

(STRTOMember(@.prmStartDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work On Period End] AS

(

(STRTOMember(@.prmEndDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed_Work] AS

(

[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]

)

SELECT

NON EMPTY

(

FILTER

(

NONEMPTYCROSSJOIN

(


[Assigned To].[Person].[Person],

[Work Item].[System_Id].[System_Id],

[Work Item].[System_Title].[System_Title],

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Measures].[Current Work Item Count],

4

),

[Measures].[Completed_Work] > 0

)

)

ON ROWS,

NON EMPTY

{

[Measures].[Work Item Url],

[Measures].[Completed_Work]

}

ON COLUMNS

FROM

[Team System]