Measure expressions and currency conversion

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris|||Hi Chris,

Sound very reasonable... I will give it a try. Do you think this approach will perform better than an MDX script like this (inspired by one of your own blog entries: http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry)

([Measures].[Sales],LEAVES([Time]),LEAVES([Currency])) = [Measures].[Sales] / [Measures].[Exchange Rate]

?

Chris, thanks for your input. It would be a shame to say that Microsoft has spent too much time documenting how to use measure expressions. |||Well, I think it might perform a little bit better, but to be honest I'm not sure. You'll have to try both!|||Michael,

Since you've marked my first reply as solving your problem, I don't suppose you can tell us what the performance on your cube is like now? Do your currency conversion queries run fast enough with measure expressions? I'd really be interested to know.

Chris|||Hi Chris

Unfortunately, I have not yet had the time to implement the solution with measure expressions yet (currently, we are using the MDX Script approach for solving the problem). I will, however, test it with measure expressions as well and report back my findings. I am sure that the solution you pointed out is correct wrt. using measure expressions (also after having reviewed the implementation in the AdventureWorks cube), which is why I marked your answer as correct.

I really appreciate your input!|||

Mosha's latest blog entry is also highly relevant to this problem:
http://sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx

|||OMG... Really a very nice article. It guess it also means that if we have to divide two measures with each other in a measure expression like this: A / B, and B has the higher granularity, then it would be a good idea to invert the B measure (1/B) and instead make a measure expression like this: B * A...

Lesson learned... I guess.

Still has not tested the measure expression vs. the MDX Script approach, though...|||Hi,

I finally got around to test this... My results show that measure expressions performs marginally better compared to using MDX scripts. But the two approaches perform almost just as well (and they do perform remarkably good) - a statement Mosha has previously made as well. So which approach do I prefer? Well - since using measure expressions has the best peformance (not by much, though), I will be using these. Perhaps there are scenarios where you cannot model your UDM to fit this approach, and MDX scripts might be more appropriate then...

I hope that these findings are useful to others...|||Well... What do you know?! I just found a scenario myself in which using measure expressions for currency conversion is not possible. One of my measures used "LastNonEmpty" as the aggregation function and measure expressions do not support this... Will of course be using the MDX script approach for solving this...

Measure expressions and currency conversion

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris|||Hi Chris,

Sound very reasonable... I will give it a try. Do you think this approach will perform better than an MDX script like this (inspired by one of your own blog entries: http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry)

([Measures].[Sales],LEAVES([Time]),LEAVES([Currency])) = [Measures].[Sales] / [Measures].[Exchange Rate]

?

Chris, thanks for your input. It would be a shame to say that Microsoft has spent too much time documenting how to use measure expressions. |||Well, I think it might perform a little bit better, but to be honest I'm not sure. You'll have to try both!|||Michael,

Since you've marked my first reply as solving your problem, I don't suppose you can tell us what the performance on your cube is like now? Do your currency conversion queries run fast enough with measure expressions? I'd really be interested to know.

Chris|||Hi Chris

Unfortunately, I have not yet had the time to implement the solution with measure expressions yet (currently, we are using the MDX Script approach for solving the problem). I will, however, test it with measure expressions as well and report back my findings. I am sure that the solution you pointed out is correct wrt. using measure expressions (also after having reviewed the implementation in the AdventureWorks cube), which is why I marked your answer as correct.

I really appreciate your input!|||

Mosha's latest blog entry is also highly relevant to this problem:
http://sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx

|||OMG... Really a very nice article. It guess it also means that if we have to divide two measures with each other in a measure expression like this: A / B, and B has the higher granularity, then it would be a good idea to invert the B measure (1/B) and instead make a measure expression like this: B * A...

Lesson learned... I guess.

Still has not tested the measure expression vs. the MDX Script approach, though...|||Hi,

I finally got around to test this... My results show that measure expressions performs marginally better compared to using MDX scripts. But the two approaches perform almost just as well (and they do perform remarkably good) - a statement Mosha has previously made as well. So which approach do I prefer? Well - since using measure expressions has the best peformance (not by much, though), I will be using these. Perhaps there are scenarios where you cannot model your UDM to fit this approach, and MDX scripts might be more appropriate then...

I hope that these findings are useful to others...|||Well... What do you know?! I just found a scenario myself in which using measure expressions for currency conversion is not possible. One of my measures used "LastNonEmpty" as the aggregation function and measure expressions do not support this... Will of course be using the MDX script approach for solving this...

MDX Statement

Hi
I've four Dimensions
1. WAGroup (it's a Value type tree)
2. HDS (it's an organisation tree)
3. Dates
4. ValueLayer (a scenario selector)
I’ve to calculated Members
1. WAGroupVector based on the Dimension WAGroup-- Memberproperty
“WAGroupVector”
2. Vector based on the Dimension HDS-Memberproperty “Vector”
Now I’ve the follwing MDX-Query:
WITH MEMBER Measures.WAGroupVector AS
'[WAGroup].CURRENTMEMBER.PROPERTIES("WAGroupVector")'
MEMBER Measures.Vector AS '[HDS].CURRENTMEMBER.PROPERTIES("Vector")'
SELECT{Measures.WAGroupVector,Measures.Vector,Measures.[Value]} ON
COLUMNS ,
NonEmptyCrossJoin(
NonEmptyCrossJoin(
DESCENDANTS(HDS.[85000 (20050228)]),
Dates.Members
),
WAGroup.Members
) ON ROWS
FROM [CostOnMonthlyBasis]
WHERE (ValueLayer.IST)
Now to my question:
Is there a way to show within the result ONLY the Calculated Members and the
Measures –Value without all the other dynamic fields? I’m getting back s
o
many fields. But I’m only interested for the mentioned ones. And if I remo
ve
the Joins and use only the Dates Dimension (ON ROWS) - I’m only getting th
e
top level values…
Thanks a lot for any suggestions….
Regards,
DominicIf you're using Reporting Services (i.e. flattened rowset), then this
version may eliminate unwanted columns:
[vbcol=seagreen]
SELECT{Measures.[Value]} ON COLUMNS ,
NonEmptyCrossJoin(
NonEmptyCrossJoin(
DESCENDANTS(HDS.[85000
(20050228)]),
Dates.Members
),
WAGroup.Members
)
DIMENSION PROPERTIES
[WAGroup].[WAGroupVector],
[HDS].[Vector]
ON ROWS
FROM [CostOnMonthlyBasis]
WHERE (ValueLayer.IST)[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

MDX Question on getting the top record for each....

I have scenario where a Item/Store combination can appear multiple times for the same period and to get the most recent record within a range ([Dim ZeroSalesDays].[Zero Sales Days]), I need to return the most current record. So I created measure with aggregation usage as last non empty.

So the below query works fine in that scenario.

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],(Aggregation usage – sum)

[Measures].[Last Tagged],[Measures].[Last Void](Aggregation usage – Last Non Empty)

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children},

{[Dim Store].[StoreName].Children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[30]}, {[Dim Period].[Period].[200702]}

)

Now they want to return [Dim ZeroSalesDays].[Zero Sales Days] value which was selected. How do I return that in the rows or columns. I can do order by and get the top record, but that will work only if I select a particular store and item, not for the entire set of all items/products.

with set

[Elapseddays] as

'order(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]},

cint([Dim ZeroSalesDays].[Zero Sales Days].value), desc)

'

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void]

} ON COLUMNS,

topcount(

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].&[4-DELMO -00090A]},

{[Dim Store].[StoreName].&[11733]},

{[Elapseddays]}

)

, 1)

ON ROWS

FROM sifdw

Any kind of help is appreciated.

Thank you in advance

Thank you,

Manish

Hi Manish,

Each row can include the last date of each individual Product/Store combination, like:

Code Snippet

SELECT

{[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void] } ON COLUMNS,

Generate(([Dim Product].[Item Id].Children, [Dim Store].[StoreName].Children),

Tail(NONEMPTY(([Dim Product].[Item Id].CurrentMember, [Dim Store].[StoreName].CurrentMember,

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]}),

([Dim Period].[Period].[200702], [Measures].[Tagged])))) ON ROWS

FROM sifdw

|||

Hi Deepak,

Somehow I was not able to get the query run successfully.

Let me attach the query what I have

SELECT

{

[Measures].[Last on Shelf]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children}

,{[Dim Store].[Store Id].children}

,{[Dim Date].[Date].children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[25]},

{[Dim ZeroSales].[Zero Sales].&[61]},

{[Dim ZeroSales].[Zero Sales Period].&[402006]},

{[Dim ZeroSales].[Team ID].&[4]}

)

Now in this given scenario, for each item / store combination, there can be multiple date. I want the most recent date for Item/store combination (Rows should display 3 columns - Item, Store and Date).

Thank you,

Manish

|||

Hi Manish,

Could you explain the realtion between [Dim ZeroSalesDays] and [Dim Date] - the latter wasn't mentioned in your original description? And along which dimension is the most recent value (LastNonEmpty) found - since your original question related to determining the member along that dimension?

|||

Hi Deepak,

Sorry for the confusion.

[Dim ZeroSalesDays] and [Dim Date] are 2 dimension.

In the fact table, lets say we have these 6 colums. Item, Store, Date, ZerosalesDays and period are dimension and on shelf is a measure.

Now in a query, for a period '5', between 1 & 45 zero sales days, I wan't the most recent record for that Item/Store combination along with the date. (It should return me 2nd record, if I change the zero sales days between 1 and 25 then it should return me 1st record and so on)

Item Store Date On Shelf ZeroSalesDays Period

00016 0800209 11/02/2006 2 24 5
00016 0800209 11/15/2006 5 37 5

00016 0800209 11/25/2006 2 47 5

I hope I made it clear. Let me know if you need any other information.

I am trying to use Generate function... but it's just spinning for ever.. May be I am missing something.

Thank you,

Manish

MDX Question on getting the top record for each....

I have scenario where a Item/Store combination can appear multiple times for the same period and to get the most recent record within a range ([Dim ZeroSalesDays].[Zero Sales Days]), I need to return the most current record. So I created measure with aggregation usage as last non empty.

So the below query works fine in that scenario.

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],(Aggregation usage – sum)

[Measures].[Last Tagged],[Measures].[Last Void](Aggregation usage – Last Non Empty)

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children},

{[Dim Store].[StoreName].Children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[30]}, {[Dim Period].[Period].[200702]}

)

Now they want to return [Dim ZeroSalesDays].[Zero Sales Days] value which was selected. How do I return that in the rows or columns. I can do order by and get the top record, but that will work only if I select a particular store and item, not for the entire set of all items/products.

with set

[Elapseddays] as

'order(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]},

cint([Dim ZeroSalesDays].[Zero Sales Days].value), desc)

'

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void]

} ON COLUMNS,

topcount(

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].&[4-DELMO -00090A]},

{[Dim Store].[StoreName].&[11733]},

{[Elapseddays]}

)

, 1)

ON ROWS

FROM sifdw

Any kind of help is appreciated.

Thank you in advance

Thank you,

Manish

Hi Manish,

Each row can include the last date of each individual Product/Store combination, like:

Code Snippet

SELECT

{[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void] } ON COLUMNS,

Generate(([Dim Product].[Item Id].Children, [Dim Store].[StoreName].Children),

Tail(NONEMPTY(([Dim Product].[Item Id].CurrentMember, [Dim Store].[StoreName].CurrentMember,

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]}),

([Dim Period].[Period].[200702], [Measures].[Tagged])))) ON ROWS

FROM sifdw

|||

Hi Deepak,

Somehow I was not able to get the query run successfully.

Let me attach the query what I have

SELECT

{

[Measures].[Last on Shelf]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children}

,{[Dim Store].[Store Id].children}

,{[Dim Date].[Date].children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[25]},

{[Dim ZeroSales].[Zero Sales].&[61]},

{[Dim ZeroSales].[Zero Sales Period].&[402006]},

{[Dim ZeroSales].[Team ID].&[4]}

)

Now in this given scenario, for each item / store combination, there can be multiple date. I want the most recent date for Item/store combination (Rows should display 3 columns - Item, Store and Date).

Thank you,

Manish

|||

Hi Manish,

Could you explain the realtion between [Dim ZeroSalesDays] and [Dim Date] - the latter wasn't mentioned in your original description? And along which dimension is the most recent value (LastNonEmpty) found - since your original question related to determining the member along that dimension?

|||

Hi Deepak,

Sorry for the confusion.

[Dim ZeroSalesDays] and [Dim Date] are 2 dimension.

In the fact table, lets say we have these 6 colums. Item, Store, Date, ZerosalesDays and period are dimension and on shelf is a measure.

Now in a query, for a period '5', between 1 & 45 zero sales days, I wan't the most recent record for that Item/Store combination along with the date. (It should return me 2nd record, if I change the zero sales days between 1 and 25 then it should return me 1st record and so on)

Item Store Date On Shelf ZeroSalesDays Period

00016 0800209 11/02/2006 2 24 5
00016 0800209 11/15/2006 5 37 5

00016 0800209 11/25/2006 2 47 5

I hope I made it clear. Let me know if you need any other information.

I am trying to use Generate function... but it's just spinning for ever.. May be I am missing something.

Thank you,

Manish

MDX question

Hi guys

I’ve got a question. Very simple scenario: a stand alone dimension exists, called Trip with only 2 columns - trip number and Audited flag (values can be Audited or Not Audited). Total row number around 2500000.

with

member [Measures].x as count([Trip].[Trip].members)

select

[Measures].x on 0

from [$Trip] --2500000

Half rows are Audited, half Not Audited. SP2 pack is installed. I need to find a number of Audited and Not Audited trips. I found that when I use slice – the whole number of rows returned, which is wrong but query is fast (only 1 second)

with

member [Measures].x as count([Trip].[Trip].members)

select

[Measures].x on 0

from [$Trip]

where ([Trip].[Audited Trip Flag].[Not Audited]) –2500000

The following query returns the correct number of rows, but considerably slower comparing with the “slice” version (around 30 sec.)

with

member [Measures].x as Exists([Trip].[Trip].members, [Trip].[Audited Trip Flag].[Not Audited]).Count

select

[Measures].x on 0

from [$Trip] –1250000

Question: why “slice” version does not work and would that be a better query to respond quicker.

Thank you, for any input.

Just found that "set" version is twice as fast - 15 secs

with

set notAudited as ([Trip].[Trip].members, [Trip].[Audited Trip Flag].[Not Audited])

member [Measures].x as notAudited.Count

select

[Measures].x on 0

from [$Trip] --1250000 --15 sec

|||

Hi Konstantin,

have you set right relationship between attributes?

Francesco

|||

Hi Francesco

Yes, the right relationship exists by default - Trip number attribute is a key, Trip Flag is tight to a key through attribute relationship. Any ideas?

MDX help required

Hi,

I have a requirement for which i need to write an MDX. The scenario is, i have a fact table with dimensions. The FactStudent consists of keys from dimensions like location, ranks dimension and period dimension. i want to know the students who have got same rank for an year and previous year (to check consistency in performance). how should be the MDX for getting this info.please help.

Thanks & regards,

Vivek S

Some reading for you

http://www.databasejournal.com/features/mssql/article.php/10894_2238011_1

Bit outdated, but you should be able to use the basics with AS2005 as well.

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