Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Values in refence currency calculation - Performance problem Expand / Collapse
Author
Message
Posted Monday, June 9, 2014 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:46 AM
Points: 119, Visits: 484
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


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1578730
Posted Monday, June 9, 2014 5:08 AM This worked for the OP Answer marked as solution
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 513, Visits: 1,000
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/





I'm on LinkedIn
Post #1578762
Posted Tuesday, June 10, 2014 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:46 AM
Points: 119, Visits: 484
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,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1579234
Posted Wednesday, June 11, 2014 5:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 513, Visits: 1,000
Glad to point you in the right direction.

Be careful of that LinkMember though

From Chris Webb's blog (again): http://cwebbbi.wordpress.com/2011/05/24/replacing-linkmember-with-a-many-to-many-relationship/





I'm on LinkedIn
Post #1579578
Posted Friday, June 13, 2014 7:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:46 AM
Points: 119, Visits: 484
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

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,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1580505
Posted Friday, June 13, 2014 7:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 513, Visits: 1,000
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





I'm on LinkedIn
Post #1580521
Posted Friday, June 13, 2014 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:46 AM
Points: 119, Visits: 484
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,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1580530
Posted Friday, June 13, 2014 8:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 513, Visits: 1,000
Yes, Paul is indeed a great name

Glad that it helped you out!





I'm on LinkedIn
Post #1580545
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse