Create a constant calculated member

Hi everyone, my question is related to creating a calculated member that must not change when the user changes the selected filter in the dimensions.
I will try to explain it: I need the total number of rows of my cube for a concrete year (year is a dimension). I will use this value in a calculated formula to define the percentage of rows that contains a concrete value (selected in the filter dimension). It means that my calculated member has a factor that change with the filter (ie. number of rows with value 'XXX'), divide by a factor that not change (total number of rows per year).

I have tried in many different ways, but I did not succeed. I cannot believe this is not possible...Maybe some sample code or DDL will help...

I keep thinking cement for some reason...|||Hi Brett, I understand your question since this is quite strange, nevertheless, I will try to explain.

The main reason is that I need to detect the quality of the data (used to join with the dimensions) in the fact table . The formula to do that is getting the rate of rows with the correct information. One way to do it will be just dividing the total number of rows by the number of rows with dimension column equals null.

I can calculate the total number of rows creating a calculated member like "count([mydimension].AllMembers)", but this number of rows is true only for the dimension "mydimension". Other possibility is using a column with value 1 for every row, and doing a sum([mydimension].AllMembers,mycolumn1), but this is true only when I filter for mydimension.

So, the real question is, how can I get the real total number of rows, valid for any dimension?.

I hope this helps...
thx in advance
Oscar

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

create a "Yesterday", "This Week", "Last Week" calculated member

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:

Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]

But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?

Thanks

Greg
1.

Yes. In the DataSourceView add a named column called SpecialDates with the value 'Normal'. Then add that column as an attribute in the time dimension, and then create the calculated members on that dimensionattribute. Another possibility is to create a Named Set with the same functionality.

2.
i think calculated members are not returned by .MEMBERS. This will work I think

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty ADDCALCULATEDMEMBERS([DATE TIME].[Date].members) on 1
from [MIA DW]
|||Awesome thanks.