June 9, 2014 at 3:02 am
Background: I have a fact table with transactional data from a sales system which contains values in different currencies. I have only one reference currency in the reports. The user wants to be able to view the values in the reference currency but with the exchange rate of the selected date.
I have a table with a row per currency/date combination and the exchange rate to the reference currency.
Records in the fact table also have a reference to the currency and a date.
Initial approach:
I created 2 measure groups, one base on the transactional data from the fact table and one with the exchange rates.
Both measure groups have in common the currency and date dimensions. A many to many relationship makes no sense because I have only one reference currency.
I create a calculated member to aggregate the data up to a selected date, something like this:
with member [Measures].[Net Sales Local Currency] AS (
Aggregate({null:[Time Hierarchy].[Date].CurrentMenber}, [Measures].[Net Sales LC])
)
member [Measures].[Exchange Rate to Reference] AS (
([Time Hierarchy].[Date].CurrentMenber, [Measures].[Exchange Rate])
)
member [Measures].[Net Sales Refence Currency] AS (
SUM([Currencies].[ISO Code].[ISO Code], [Measures].[Net Sales Local Currency]/[Measures].[Exchange Rate to Reference])
)
Results:
When I select only a date member, let's say the 01.01.2014 I get a result within a second. If a put 2 dates in the select the result takes about 10 minutes. I suppose I am making something wrong in my calculated members that cause this peformance penalty.
Could you please help me to debug my MDX code?
Any comments would be highly appreciated.
Kind Regards,
Paul
June 9, 2014 at 5:08 am
You'd most probably be better off putting your currency conversion logic within a SCOPE. Have a look at this article by Mosha Pasumansky on it: http://sqlblog.com/blogs/mosha/archive/2005/12/06/performance-of-multiplication-in-mdx.aspx
Or this one by Chriss Webb: http://cwebbbi.wordpress.com/2011/01/12/tuning-the-currency-conversion-calculations-created-by-the-add-business-intelligence-wizard/
June 10, 2014 at 8:28 am
Hi yayomayn,
thanks so much for your useful answer.
Both references were very hepful. At the end I implemented something like this and it works correctly:
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Sales Euro] AS [Measures].[Gross Sales Local Currency]*[Measures].[Exchange Rate To Euro];
SCOPE
(MeasureGroupMeasures("Sales"), Leaves([Time]), [Currencies].[ISO Code].[ISO Code]);
[Measures].[Gross Sales Euro] = [Measures].[Gross Sales Euro]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
That is just an adaptation from Mosha´s article.
I tried also to implement a measure expression as Crhis Webb proposed, but my data model is not exactly the same one. My Sales measure group is not directly related to the same Time dimension as the exchange rates, instead I am using the linkmember function inside a calculated measure. I've read in the book "Expert Cube Development with SSAS Multidimensional Models" that "The MeasureExpression expression is evaluated before the calculations in the MDX Script are evaluated".
Kind Regards,
June 11, 2014 at 5:24 am
Glad to point you in the right direction.
Be careful of that LinkMember though :unsure:
From Chris Webb's blog (again): http://cwebbbi.wordpress.com/2011/05/24/replacing-linkmember-with-a-many-to-many-relationship/
June 13, 2014 at 7:10 am
Hi yayomayn,
thanks again for your useful tips.
I just want to extend this discussion.
I wrote some MDX code like this in order to get the value in a reference currency across the time hierarchy:
SCOPE
(MeasureGroupMeasures("Exchange Rates"), Leaves([Time]), [Currencies].[ISO Code].[ISO Code]);
[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
SCOPE
(MeasureGroupMeasures("Exchange Rates"), [Time].[Month].[Month], [Currencies].[ISO Code].[ISO Code]);
[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
-- Scope for the Quarters
SCOPE
(MeasureGroupMeasures("Exchange Rates"), [Time].[Quarter].[Quarter], [Currencies].[ISO Code].[ISO Code]);
[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
-- Scope for the Years
SCOPE
(MeasureGroupMeasures("Exchange Rates"), [Time].[Year].[Year], [Currencies].[ISO Code].[ISO Code]);
[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
//-- Scope for the All members
SCOPE
(MeasureGroupMeasures("Exchange Rates"), [Time].[Year - Week - Date], [Currencies].[ISO Code].[ISO Code]);
[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
The idea is when roll up get the last non empty value of the period, i.e., for a month I get the value with the exchange rate of the last day of the month and so on.
My code works, however I am not conformed because:
- When I reapeat and repear the same code just changing some paremeter I feel like something is not smart enough.
- I am wondering if the order of the scope statements matters or not.
- At the beggining I was getting wrong results for the year level but after comment and uncomment some code it works :w00t:
The challenge hier is not to get the conversion with the rate at the transaction rate but dinamically assign a fx rate depending on the selected date. I am building snapshots of the opend orders and I want to be able to show the value of them in a selected date.
Any comment would be appreciated.
Kind Regards,
June 13, 2014 at 7:41 am
Hi Paul,
I think this may be a case for a little restructuring of your cube rather than more MDX trickery to be honest. Sorry to just give you another link, but many other people have written on this subject better than I can. So here's Christian Wade describing a solution to what is essentially what you are doing but in a slightly different way...http://consultingblogs.emc.com/christianwade/archive/2006/08/24/currency-conversion-in-analysis-services-2005.aspx
In answer to your question, ordering of scopes doesn't matter unless they contradict previous scopes. If they contradict previous scopes then SSAS will use the last one (as it appears in the code) as the actual calculation/whatever. If you have nested scopes then the order can be significant, check out Chris Webb's article about it here (more urls!):http://cwebbbi.wordpress.com/2010/08/03/order-of-nested-scope-statements/
Let everyone know how you get on 🙂
June 13, 2014 at 8:04 am
Greetings to my namesake!
The links are enough help, so please do not say sorry.
The first one was not exactly my case, I implemented a many-to-one and not a one-to-many currency conversion.
The second one was the key to rewrite my code:
SCOPE ([Measures].[OOHB Net Sales Value EUR]);
SCOPE ([Time].[Date].Members, [Currencies].[ISO Code].[ISO Code]);
this = [Measures].[OOH Bookings Net Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);
END SCOPE;
END SCOPE;
Then I do not feel like the Captain Caveman for a few hours.
Regards,
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply