How to derive a calculated from a measure and a dimension attributes?

Hi,Does anyone know how to write an MDX queries that can enable calculation of a measure value and a dimension attributes? Eg: Measure Revenue * Conversion Rate (from dimension attribute).Reason that this conversion rate is not in the fact layer is that the conversion rate is dependant to the Currency the user want to convert to during run-time.Thanks.JLim

Hello

Here you have the mdx query performed on the Food Mart 2000 Sales cube.

with member [Measures].[Modified Unit Sales] as 'iif([Promotions].CurrentMember.Name="Dollar Days",[Measures].[Unit Sales]*0.9,[Measures].[Unit Sales])'
select {[Measures].[Modified Unit Sales],[Measures].[Unit Sales]} on axis(0) , {[Promotions].DefaultMember.Children} on axis(1)

from [sales]

Due to possible performance problems please consider the option to create one measure per each currency.

Regards

Maciej Kiewra

How to define Hierarchy

Hi all,

I have a Store dimension that has 4 attributes.

Store

Category

Department

Store Key (Key column)

|_Store, Category, Department

Then I have creaet one Hierachy as follow:

Store->Category->Department

But I get a warning message "Attribute relationship doesn't exist in one or more level". Do I need to worry about it?

I know how to solve this warning message by creating a member property of the attribute for a level on the level below, but the problem is a Department can be in different Category in different Store. If I apply this solution, then the hierachy can't display correctly

Thanks

Hello! If you check the attribute relation in a natural or user hierarchy they can only have one-to-one or one-to-many relations.

So a child in a natural hierarchy cannot point to more than parent. If they point to several you will have random results when you analyze data. I think that SSAS2005 will pick the first parent it finds and ignore the others.

In your natural hierarchy department can only point to one parent(category).

HTH

Thomas Ivarsson

|||

Take a look at this site, and see if it helps you..

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262358,00.html

|||

Thanks, this site is really helpfull. I think I can define the un-natural hierarchies on my Store dimension, but I need to suffer the query performance.