Calculated member and Calculated Cells

What is the different between calcualted member and calculated cells?
Is there any guildeline in using calculated member and calculated cells?
Calculated Members are simply formula that calculate values that do not
already exist in the cube. They do not take up any disk space as they
are calculated on the fly. A simple example would be if you had and
[income] and an [expenses] measure you could calculate profit by
subtracting expenses from income.
Nearly every cube will have some Calculated members.
Calculated cells on the other hand you do not see all that often. I
usually think of them as conditional overrides. Based on a subset of the
members from any of the dimensions in your cube and an optional
conditional statement, you can define an MDX expression that returns a
value which overrides the value that is actually stored in the cube.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> What is the different between calcualted member and calculated cells?
> Is there any guildeline in using calculated member and calculated cells?
>
|||Do you have any simple real live example to help me to understand when I
should use Calculated Cells?
"Darren Gosbell" wrote:

> Calculated Members are simply formula that calculate values that do not
> already exist in the cube. They do not take up any disk space as they
> are calculated on the fly. A simple example would be if you had and
> [income] and an [expenses] measure you could calculate profit by
> subtracting expenses from income.
> Nearly every cube will have some Calculated members.
> Calculated cells on the other hand you do not see all that often. I
> usually think of them as conditional overrides. Based on a subset of the
> members from any of the dimensions in your cube and an optional
> conditional statement, you can define an MDX expression that returns a
> value which overrides the value that is actually stored in the cube.
> HTH
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
> Kam@.discussions.microsoft.com says...
>
|||I used it once in an accounting situation.
The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.
I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)
But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.
There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <43339F67-CA70-4679-9F42-9B076A94EDEE@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> Do you have any simple real live example to help me to understand when I
> should use Calculated Cells?
> "Darren Gosbell" wrote:
>

Calculated member and Calculated Cells

What is the different between calcualted member and calculated cells?
Is there any guildeline in using calculated member and calculated cells?Calculated Members are simply formula that calculate values that do not
already exist in the cube. They do not take up any disk space as they
are calculated on the fly. A simple example would be if you had and
[income] and an [expenses] measure you could calculate profit by
subtracting expenses from income.
Nearly every cube will have some Calculated members.
Calculated cells on the other hand you do not see all that often. I
usually think of them as conditional overrides. Based on a subset of the
members from any of the dimensions in your cube and an optional
conditional statement, you can define an MDX expression that returns a
value which overrides the value that is actually stored in the cube.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> What is the different between calcualted member and calculated cells?
> Is there any guildeline in using calculated member and calculated cells?
>|||Do you have any simple real live example to help me to understand when I
should use Calculated Cells?
"Darren Gosbell" wrote:

> Calculated Members are simply formula that calculate values that do not
> already exist in the cube. They do not take up any disk space as they
> are calculated on the fly. A simple example would be if you had and
> [income] and an [expenses] measure you could calculate profit by
> subtracting expenses from income.
> Nearly every cube will have some Calculated members.
> Calculated cells on the other hand you do not see all that often. I
> usually think of them as conditional overrides. Based on a subset of the
> members from any of the dimensions in your cube and an optional
> conditional statement, you can define an MDX expression that returns a
> value which overrides the value that is actually stored in the cube.
> HTH
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
> Kam@.discussions.microsoft.com says...
>|||I used it once in an accounting situation.
The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.
I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)
But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.
There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <43339F67-CA70-4679-9F42-9B076A94EDEE@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> Do you have any simple real live example to help me to understand when I
> should use Calculated Cells?
> "Darren Gosbell" wrote:
>
>

Calculated Member - #VALUE! in the Grand Total

Hi all

This is a simple question on MDX.

We have created a calcualted member that basically compares an existing measure to an attribute, and displays a certain value based on what's in the attribute (simple case statement)

The calcualtion works fine against the proper dimension, but for Grand Total displays #VALUE!. What do I have to do to make the grand total actually display a grand total of this calculated member in the cube?

Thanks in advance

Moe

Hi Moe,

Can you give an idea of what the MDX for the calculated member looks like?

|||

Hi Deepak

Here's the code for the calculated member. Please let me know if you need anything else.

CREATE MEMBER CURRENTCUBE.[MEASURES].[calc - Spare Cost]

AS case when [Repairs].[Repair Status].&[Done] then [Measures].[ARC Buy Price]

when [Repairs].[Repair Status].&[Not Done] then 0

else -1 end,

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [ARC Buy Price] },

VISIBLE = 1;

|||

If the intent is to return the value of [Measures].[ARC Buy Price] associated with the [Repairs].[Repair Status].&[Done] member, maybe something simpler like this will work:

CREATE MEMBER CURRENTCUBE.[MEASURES].[calc - Spare Cost]

AS iif([Repairs].[Repair Status].CurrentMember is [Repairs].[Repair Status].&[Not Done],

0, ([Repairs].[Repair Status].&[Done], [Measures].[ARC Buy Price])),

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [ARC Buy Price] },

VISIBLE = 1;

|||

Hi Deepak

Thanks for the reply.

The code sample you sent me gives me a value in the Grand Total now, but I am confused as to what this Grand Total actually is .

To simplify my problem, I created the following example based on Northwind:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]

AS iif(

[Orders].[Customer ID].CurrentMember = [CHOPS], 5, 1),

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [Orders Count] },

VISIBLE = 1 ;

The above code gives me a Grand Total of 1! is this correct?

Thanks again

Moe

|||

Hi Moe,

I'm not clear how to run your sample code - could you provide an Adventure Works equivalent? Also, what should your results layout look like - and how many members of [Repairs].[Repair Status] are there (I assumed that there are just 2)?

Calcualted formula

Please someone helpe me out writing a caculated memeber which calcautle, "sale shipe date" minus 14.

If you already know the "sale ship date", you can use .Lag( 14 ) on that member.

e.g.:

Code Snippet

[Sales ship date].CurrentMember.Lag( 14 )

Best regards

- Jens

Calcualted formula

Please someone helpe me out writing a caculated memeber which calcautle, "sale shipe date" minus 14.

If you already know the "sale ship date", you can use .Lag( 14 ) on that member.

e.g.:

Code Snippet

[Sales ship date].CurrentMember.Lag( 14 )

Best regards

- Jens

Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.

Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.