Calculated Measure

Hi,

I am trying to create a calculated measure called revenue which is equal to the hourly rate of an item mutiplied by the time the item is used for.

Engine hours is a measure within the fact table and hourly rate is a attribute of one of my dimensions. If i set the calculated measure simply to the value of the hourly rate (Asset Dimension) when the measure revenue is displayed in the cube no data is shown even though i know data should be shown.

Do have any explanation for this?

Cheers,

Unless you have selected a particular Asset which has an hourly rate, the context of the Asset dimension is probably at the [All] member which does not have an hourly rate.

You could:

1) calculate the hourly rate in MDX using something like SUM(EXISTING [Asset].[Asset].[Hourly Rate], [Asset].[Asset].[Hourly Rate].memberValue)

2) You could make the Asset table play the role of a fact and dimension. Then you could use a member expression to calculate the revenue. This should give you pretty good performance too. I think the Adventure Works sample database uses measure expression for some of it's currency conversion stuff.

|||

Thanks for the Reply,

Several Questions:

Firstly I had selected a particular asset which had an hourly rate but the hourly rate still did not show.

How do I make my asset table play the role of a fact and dimension table?

Is it possible to within a calculated measure use an attribute from the fact table (Engine Hours) and mutiplie this with an attribute in a dimension (Hourly Rate of Asset)?

Cheers,

|||

medphis wrote:

Firstly I had selected a particular asset which had an hourly rate but the hourly rate still did not show.

Hmm, might be something to do with data types. Have you tried using the .MemberValue function or wrapping the .CurrentMember in a call to the Val() function?

medphis wrote:

How do I make my asset table play the role of a fact and dimension table?

In the Cube structure window you create a new measure group and select the Asset dimension table.

medphis wrote:

Is it possible to within a calculated measure use an attribute from the fact table (Engine Hours) and mutiplie this with an attribute in a dimension (Hourly Rate of Asset)?

Yes, you would normally do something like [Measures].[Engine Hours] * [Asset].[Asset].[Hourly Rate].MemberValue. But you would need to make sure that this is executed at a level where the individual hourly rates are available. It's hard to be sure without knowing more, but maybe it would need to look something like this:

SUM(EXISTING [Asset].[Asset].[Hourly Rate] ,[Measures].[Engine Hours] * [Asset].[Asset].[Hourly Rate].MemberValue)

|||

Hi,

Thanks again for your help, the expression: SUM(EXISTING [Asset].[Asset].[Hourly Rate] ,[Measures].[Engine Hours] * [Asset].[Asset].[Hourly Rate].MemberValue) will correctly calculate the revenue at the level of 'Hourly Rate' but the grand total within the cube and when the hourly rate attribute from the asset dimension has not been added to the cube then the error '#Value ' appears within the intersections for the measure Revenue saying:

The following system error occured: Type mismatch... Query(3, 37). There is a type mismatch for the '*' operator.

Both the hourly rate and engine hours are of type decimal and if there is no data for either of these values then a 0.00 is inserted instead of a Null which was done to try and aviod and issue like this.

I am a little unsure as to the approach i should take to debug such an issue?

Your help is appreciated.

Cheers,

|||

You would not need to insert 0.00 for the engine hours measure as SSAS does not do null propagation like SQL does and it inherantly evaulates null/empty measures as 0 (if it did not do this, every measure would pretty much be null at the top level). But it is probably wise to do this for the Hourly Rate attribute.

Looking at the code again I think the problem might be the use of the [Asset].[Asset] hierarchy. This looks like it might be a user defined hierarchy and if it is, it would be possible to have a context which is something other than an hourly rate which would lead to EXISTING [Asset].[Asset].[Hourly Rate] returning an empty set which might be the cause of the type mismatch.

Try using something like this:

SUM(EXISTING [Asset].[Hourly Rate].[Hourly Rate] ,[Measures].[Engine Hours] * [Asset].[Asset].[Hourly Rate].MemberValue)

PS. Sorry for the delay in replying, I have been on holidays.