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 delete an Analysis Service DB?

Hi,

when i try do deploy an AS-Projekt, i get follwing error Massage:

Error 1 Errors in the metadata manager. The dimension with ID of 'v Kundeneigenschaft', Name of 'v Kundeneigenschaft' referenced by the 'v Kundeneigenschaft ~MC' cube, does not exist. Errors in the metadata manager. An error occurred when loading the v Kundeneigenschaft ~MC cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Project3.0.db\v Kundeneigenschaft ~MC.3.cub.xml'. 0 0

By the way, the related Cube is not Part of the AS-Projekt i wish to deploy. This Error above occurs on everything i do: Try to delete this Analysis Service Database, ReDeploy another AS-Projekt, Build and Deploy a new projekt and so on...

Cleanup (delete) of C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data doesn't help, some more error messages appear.

Anybody an idea?

Greetings, Torsten

Make sure you restart your Analysis Server after deleting files from the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data folder.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Yes, thanks. Thought i've tried that - but it seem that not...

Now it works!

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.