I see the ‘Add Business Intelligence’ wizard in BIDS as a bit of a missed opportunity on Microsoft’s part: it was a great idea to have functionality that would automate difficult stuff like adding MDX calculations to a cube, and it’s a shame that most of the MDX code it generates is so rubbish. Take, for example, the MDX currency conversion calculations that it creates. If you’re using Enterprise Edition the best way to do currency conversion is not to use MDX at all but to use measure expressions, but if you have Standard Edition you have no choice but to use MDX to do your currency conversion and if you use the calculations the wizard produces you’d probably think that this type of calculation has to be painfully slow. That’s not true, though – and in this post I’ll show you how to replace the currency conversion calculations created by the wizard with some that are not only much simpler but also much, much faster.
Let’s use a simple cube to illustrate this. In the Adventure Works database I created a cube with two measure groups: one based on the Internet Sales fact table, the other based on the Currency Rates fact table. I then added the Currency dimension and the Date dimension (joining on OrderDateKey in the Internet Sales table), and ran the ‘Add Business Intelligence’ wizard to add many-to-many currency conversion to the cube. Here’s what my Dimension Usage tab looked like after I’d run the wizard (note that it added a new Reporting Currency dimension to the cube):
And here’s the MDX that it adds to the MDX Script:
// <Currency conversion>
// Currency conversion wizard generated script.
// Currency conversion generated on: 11 January 2011 21:05:19
// by user: Chris
// Currency conversion type: ManyToMany
// Selected members to be converted: Sales Amount
// Please be aware that any changes that you decide to make to it may be
// overridden the next time you run the Currency Conversion wizard again.
// This is the Many to One section
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension
Scope ( { Measures.[Sales Amount]} );
Scope( Leaves([Date]) ,
[Reporting Currency].[USD],
Leaves([Source Currency]));
// Convert Local value into Pivot currency for selected Measures that must be
//converted with Measure rate [Average Rate]
Scope( { Measures.[Sales Amount]} );
This = [Reporting Currency].[Local] / Measures.[Average Rate];
End Scope;
End Scope;
// This is the One to Many section
// All currency conversion formulas are calculated for
//the non pivot currency and at leaf of the time dimension
Scope( Leaves([Date]) ,
Except([Reporting Currency].[Source Currency Code].[Source Currency Code].Members,
{[Reporting Currency].[Source Currency Code].[Source Currency Code].[USD],
[Reporting Currency].[Source Currency Code].[Source Currency Code].[Local]}));
// This section overrides the local values with the Converted
// value for each selected measures needing to be converted with Measure rate [Average Rate]…
// LinkMember is used to reference the currency from the
// source currency dimension in the rate cube.
Scope( { Measures.[Sales Amount]} );
This = [Reporting Currency].[Source Currency Code].[USD] * (Measures.[Average Rate], LinkMember([Reporting Currency].[Source Currency Code].CurrentMember, [Source Currency].[Source Currency Code])) ;
End Scope;
End Scope; // Leaves of time, all reporting currencies but local and pivot currency
End Scope; // Measures
// End of the currency conversion wizard generated script
// </Currency conversion>
Scary, eh? I won’t explain what it does in detail, but basically it does the following:
- Converts the various local currency values held in the Internet Sales fact table into US Dollars
- Converts these US Dollar amounts into whatever Reporting Currency the user has selected
It’s a lot of code for something so straightforward, and the reason why so complex is because it needs to use the Currency Rates measure group for both sets of currency rates: from local currency to US Dollars, and from US Dollars to reporting currency. As we all know overly-complex code is usually poorly-performing code, and in this case the use of LinkMember in particular is a killer; also there are no checks for situations where exchange rate values don’t exist, which is a fairly basic mistake.
Take the following query:
SELECT [Date].[Calendar Year].MEMBERS ON 0,
NON EMPTY
[Reporting Currency].[Source Currency Code].MEMBERS ON 1
FROM [CurrencyTestWizard]
WHERE([Measures].[Sales Amount])
It displays all Years on columns and all Reporting Currencies on rows for the Sales Amount measure, so it’s doing quite a lot of work. On my quite beefy laptop, using the cube I’d just created, it ran in just under 9 seconds on a cold cache to give me this:
We can do a lot better than 9 seconds though. The first step to optimising this is, as always, to push complexity out of the MDX and back into the cube design. Given that a fact table containing exchange rate values is usually pretty small relative to other fact tables, what I decided to do was to add a second measure group to my cube based on exactly the same Exchange Rate fact table, use the original measure group for the conversion from local currency to US Dollars and then the new one for the conversion from US Dollars to reporting currency. This did increase both the size of the cube and the amount of time taken to process it, but only by a tiny amount.
BIDS didn’t let me create a second measure group from a fact table that was already in use, so I had to create a new named query in BIDS that did a SELECT * from the Exchange Rate fact table and then use that. Having done this, I was able to set up my Dimension Usage as follows, with Reporting Currency joining to this new measure group:
Now, I could replace the code above with the following MDX:
SCOPE({[Measures].[Sales Amount]});
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Source Currency].[Source Currency Code].[Source Currency Code].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Source Currency Code].[Source Currency Code].MEMBERS
,{{[Reporting Currency].[Source Currency Code].&[2147483647]}}));
THIS = IIF([Measures].[Average Rate – Reporting Currency Rates]=0
, NULL
,IIF([Measures].[Average Rate]=0
, NULL
,([Measures].[Sales Amount] / [Measures].[Average Rate]))
* [Measures].[Average Rate – Reporting Currency Rates]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
What I’m doing here is using a single set of nested scoped assignments to perform the calculation at the leaf level of the Date, Source Currency and Reporting Currency dimensions. With two different measure groups containing exchange rates there’s no need for the use of LinkMember – I just use two different measures for each step in the conversion – and I make sure I don’t bother doing any conversions where exchange rate values don’t exist.
When I ran my test query, I got exactly the same results but the query ran in around 0.5 seconds on a cold cache – 18 times faster than on the original version of the cube! It just shows you what a few tweaks to your cube design and MDX can achieve, doesn’t it? It’s a shame that MS didn’t invest a bit more time on the ‘Add Business Intelligence’ wizard to improve it – it would probably have improved the quality of many implementations no end. In the future, with PowerPivot and BISM being focused so much on ‘self-service’ BI, I think something similar to the wizard is a ‘must have’ to help users create complex DAX calculations, and I hope MS realise that functionality like this can be key to the overall success of a product.
this is interesting i only have a one-2-many currency conversion. how would this work in that scenario.
It must be along the same lines but i can’t quite work out how to optimise the one-2-many script the bi wizard creates. They are pretty similar and im guessing i don’t need to create a second measure group.
Any help would be greatly apprecited.
Excellent point Chris, I am currently putting together a demo cube for an International Coffee franchise who require currency rate conversions in their reports. Thanks!
H.
If you had multiple measures that you wanted to apply the conversion to, could you use a single calculation for it?
([Measures].currentmember / [Measures].[Average Rate]))
* [Measures].[Average Rate – Reporting Currency Rates])
Off the top of my head, yes, that should work.
Chris, would it be possible to post a sample of the above.
Not sure what you did on setting up your cube against the Adventure Work DW.
Yours,
Ken
I’m not sure I’ve got the sample any more… sorry. But I didn’t do anything complicated for the original cube: I just used the existing SSAS Adventure Works database and created a new cube as detailed above, using the existing Source Currency and Date dimensions.
Hi Chris
Thanks for posting this, I will to optimize my cube with this.
In addition to understanding the currency conversion I have encountered a problem that I hope you can help me resolve.
When working with finance measures and the balance statement the balance (Assets and liabilities) needs to be calculated to ultimo exchange rate.
In my solution, I have a Finance Fact table, and a fact table containing the currency exchange rates per day. the exchange rate table contains three currencies: USD, EUR, and GBP.
The measure “Amount To Date” is calculated as
Sum(Periodstodate([Date]. [Date Hierarchy]. [date], [Date]. [Date Hierarchy]. Currentmember),[Measure].[Amount])
This measure sums up all the postings on the GL Accounts which makes the balance for a specific period.
Now I would like to convert this measure with the ultimo exchange rate(the exchange rate existing on the last day of a specific selection, and I’m really having problems with this.
I need to make a many-to-many currency conversion.
Here’s my logic so far: I have outcommented the Leaves([date])
this is done to make sure it doesn’t calculate per Date, since I want to calculate only the exchange rate existing on the last day of the selected period.
and I have made a measure that should find the last exchange rate within the selected period:
sum(LastPeriod([Date].[Date Hierarchy].[Date], [Date].[Date Hierarchy].Currentmember),Measures.[Exchange rate]
//
// Currency conversion wizard generated script.
// Currency conversion generated on: 18 October 2011 14:09:23
// by user: Anders
// Currency conversion type: ManyToMany
// Selected members to be converted: Sales Amount
// Please be aware that any changes that you decide to make to it may be
// overridden the next time you run the Currency Conversion wizard again.
// This is the Many to One section
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension
Scope ( { Measures.[Amount]} );
Scope( //Leaves([Date]) , — I have removed this.
[Reporting Currency].[EUR],
Leaves([Currency]));
// Convert Local value into Pivot currency for selected Measures that must be
//converted with Measure rate [Average Rate]
Scope( { Measures.[Amount]} );
This = [Reporting Currency].[Local] / sum(LastPeriod([Date].[Date Hierarchy].[Date], [Date].[Date Hierarchy].Currentmember),Measures.[Exchange rate]);
End Scope;
End Scope;
// This is the One to Many section
// All currency conversion formulas are calculated for
//the non pivot currency and at leaf of the time dimension
Scope( //Leaves([Date]) , — I have removed this.
Except([Reporting Currency].[Currency Code].[Currency Code].Members,
{[Reporting Currency].[Currency Code].[Currency Code].[EUR],
[Reporting Currency].[Currency Code].[Currency Code].[Local]}));
// This section overrides the local values with the Converted
// value for each selected measures needing to be converted with Measure rate [Average Rate]…
// LinkMember is used to reference the currency from the
// source currency dimension in the rate cube.
Scope( { Measures.[Amount]} );
This = [Reporting Currency].[Currency Code].[EUR] * sum(LastPeriod([Date].[Date Hierarchy].[Date], [Date].[Date Hierarchy].Currentmember),Measures.[Exchange rate]), LinkMember([Reporting Currency].[Currency Code].CurrentMember, [Currency].[Currency Code])) ;
End Scope;
End Scope; // Leaves of time, all reporting currencies but local and pivot currency
End Scope; // Measures
// End of the currency conversion wizard generated script
//
I hope you can help me, I can provide more information if this is not enough.
Thanks
Anders
Error in the first post:
The measure “Amount To Date” is calculated like this:
Sum(Periodstodate([Date]. [Date Hierarchy]. [date], [(ALL)]. [Date Hierarchy]. Currentmember),[Measure].[Amount])
Are you using Enterprise Edition? If so then the easiest thing to do would be to use the LastNonEmpty aggregate function for your Exchange Rate measure rather than using MDX – it will be much faster.
Apart from that, can you tell me what exactly isn’t working?
Hi Chris
first of all, thanks for your quick response to my question.
unfortunetly it is not a Enterprise Edition, it is a standard edition SQL 2008 R2.
What isn’t working.
Asume this:
Amount = 5000 USD in 2010
Amount = 7000 USD in 2011
then Amount To Date is
Amount To Date = 5000 USD in 2010
Amount To Date = 12000 USD in 2011
Exchange Rate in 2010: 1 EUR = 1.3 USD
Exchange Rate in 2011: 1 EUR = 1.5 USD
First of all when converting to EUR the Amount To Date is wrong when I select the Date.Year 2011
it shows (5000 / 1,3) + (7000 / 1,5) = 8512,8 EUR
it should show (5000+7000) / 1,5) = 8000 EUR
The problem is that it doesn’t use the last Exchange rate available within the daterange i have selected.
let me know if you need more info
thanks
Anders
This could be a solve order issue. Can you move the definition of your Amount to Date calculated measure to the very end of the MDX Script? It sounds like you have it before your currency conversion calculation, which means it will be evaluated first.
Hi Again Chris
The Amount To Date measure was before the currency conversion however when I moved it to be calculated after the Currency Conversion it still shows the same values
I would like to try with , SOLVE_ORDER however I do not know how to apply this to the currency conversion scope. I only know how to apply it ot a calculated measure
If moving the position of the calculation in the script doesn’t change the result, then it isn’t a solve order problem. It might be easier to take this offline – can you send me a copy of your BIDS solution? You can find my contact details at http://www.crossjoin.co.uk
Chris,
as always thanks for the great post. i did have a question on the except clause and the value that you are excluding, [Reporting Currency].[Source Currency Code].&[2147483647]. is this the unknown member, the usd member, or something else.
i was able to get this to work, but i ended up reversing some of the calculations because how i interpretted the local currency and reporting currency as well as the values being stored in the local currency.
my formula looks like this,
scope ({[Measures].[M1]};
scope ([Date].[Date].[Date].members);
scope ([Local Currency].[Currency Code].[Currency Code].members);
scope (except
([Reporting Currency].[Reporting Currency].[Reporting Currency].members
,{{[Reporting Currency].[Reporting Currency].&[-2]}}));– -2 works –local amount
this = iif([Measures].[Average Rate – Reporting Currency Rates] = 0
,NULL
,iif([Measures].[Average Rate]=0
,NULL
,([Measures].[M1] * [Measures].[Average Rate])
* [Measures].[Average Rate – Reporting Currency Rates]));
end scope;
end scope;
end scope;
my parens are also different as i got errors with closing the parans correctly.
Hi Robert,
Off the top of my head it’s the ‘local currency’ member that I’m excluding, so you can see values in their original currency. Looks like you’re doing the same thing in your code too.
Chris
okay, thanks that helps.
any insight on the difference in the parens(). i am leaving performing ((M1*AR)/(AR-RCR) all in the closing iif.
-robert
Do you mean you’re still getting errors? I can see a missing closing round bracket at the end of this line:
scope ({[Measures].[M1]};
I am not getting errors, that was a typo on my part..
here is where the difference is between what you have done and what i ended up doing.
here is my code in the main iif block..the key is the “))” parens at the end.
this = iif([Measures].[Average Rate – Reporting Currency Rates] = 0
,NULL
,iif([Measures].[Average Rate]=0
,NULL
,([Measures].[M1] * [Measures].[Average Rate])
* [Measures].[Average Rate – Reporting Currency Rates]));
here is the code that you have and it seems that you are closing off the calculation before i am.. if you notice you only have a single paren at the end “)”.
THIS = IIF([Measures].[Average Rate – Reporting Currency Rates]=0
, NULL
,IIF([Measures].[Average Rate]=0
, NULL
,([Measures].[Sales Amount] / [Measures].[Average Rate]))
* [Measures].[Average Rate – Reporting Currency Rates]);
As usual your code compiles and runs, i am just a little short on what is happening with the parens.
my interpretation is this…
1.) “This” is scoped to the [Sales Amount] and this is being divided by the [Average Rate]
2.) I can only assume that “* [Average Rate – RCR]” is then applied to the just modified “This” ([Sales Amount]).
if my description is accurate then i believe we are using the dimensions in different context. in order for e to get my code to work properly, i needed to do the following, [Sales Amount] * [Average Rate], the average rate in this case is just the base currency to USD. I then take the above and divide by the Reporting Currency.
the end result is the same either dividing or multiplying first. my main question is centered around the difference in the use of the parens “(” and how “This” is interpretted.
thanks,
robert
Hi Robert,
I don’t think there’s any substantial difference between our calculations – the difference in where the brackets close doesn’t make any difference to the calculations as far as I can see. The brackets here are controlling the order that the calculation is being done, and have nothing to do with how we’re using dimensions.
Chris
okay, thanks.
in the end as always thanks for the great post and i was able to get this to work.
i used this along with the ME for the 1 to many conversion work.
Chris,
have you run the results above without having the reporting or billing currency present. i believe a catch for the all member to at least translate to the usd or the default would be necessary?
-robert
Chris,
just wanted to post, that one does not need to do anything different to the general structure discussed. it was a user error on operatoin order.
-robert
Ah, ok. I was going to say that since there shouldn’t be an All Member on any of the currency dimensions (they should have IsAggregatable set to False) then no extra code is needed.
It is very usefull thanks.
I have a small question.
In your approach you use [Measures].[Sales Amount] in your script.
THIS = IIF([Measures].[Average Rate – Reporting Currency Rates]=0
, NULL
,IIF([Measures].[Average Rate]=0
, NULL
,([Measures].[Sales Amount] / [Measures].[Average Rate]))
* [Measures].[Average Rate – Reporting Currency Rates]);
But wizard script doesnt have MeasureName in the script.
Scope( { Measures.[Sales Amount]} );
THIS = [Reporting Currency].[Source Currency Code].[USD] * (Measures.[Average Rate], LinkMember([Reporting Currency].[Source Currency Code].CurrentMember, [Source Currency].[Source Currency Code])) ;
End Scope;
The question is. In Wizard script we can use more that one MeasureName. But in your script we have to create SCOPEs for every meaure.
Is it possible to make your script multi meauser friendy.
i want to use
Scope( { Measures.[Sales Amount],Measures.[Sales Amount With Tax],Measures.[Discount Amount] } );
in the first SCOPE.
Is it possible?
Thank you.
Yes, it should be possible – you just need to include all the measures in the scope statement (in the way you do in your post) and then use Measures.Currentmember inside the actual calculation.
oops.
ok it works thank you very much.
Sorry there becomes another problem.
I have to create ROLES for users who will see only the SalesQuantities not the Amounts.
I create a new Role and apply Dimension security and Add [XXX\ALogin] for this Roles.
Measures
Measures
Allowed
Measures
{ [Measures].[SalesQuantity],[Measures].[ExchangeRate] }
There is no problem if i dont Add Currency Conversion Script below.
//Currency Conversion
Scope (
{ [Measures].[CompanySales],[Measures].[CompanySalesWithTax]
});
SCOPE (Leaves([Date]),Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[USD]));
This = [Measures].CurrentMember /ValidMeasure([Measures].[ExchangeRate]);
END SCOPE;
END SCOPE;
[XXX\ALogin] only see Sales Quantities. But After adding the Currency Conversion Script Excel show error
[MdxScript(V3) (1170, 13) The ‘[CompanySales]’ member was not found in the cube when the string, [Measures].[CompanySales], was parsed.]
When i check the Profiles Full Message is:
MdxScript(V3) (1170, 13) The ‘[CompanySales]’ member was not found in the cube when the string, [Measures].[CompanySales], was parsed. Leaves function for attributes with mixed granularity in different measure groups is not supported. MdxScript(V3) (1174, 2) Leaves function for attributes with mixed granularity in different measure groups is not supported. The END SCOPE statement does not match the opening SCOPE statement. MdxScript(V3) (1177, 2) The END SCOPE statement does not match the opening SCOPE statement. The END SCOPE statement does not match the opening SCOPE statement. MdxScript(V3) (1178, 1) The END SCOPE statement does not match the opening SCOPE statement.
I understand that [XXX\ALogin] dont have right to see [CompanySales] mesaures.
* But How will i use Currency Conversion and MeasureBased Security together.
Thanks
Cem Üney
The first thing to do is to replace Leaves([Date]) with a reference to the lowest level of the key attribute of the Date dimension. So instead of
SCOPE (Leaves([Date]),Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[USD]));
you’d say
SCOPE ([Date].[Date].[Date].members,Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[USD]));
(I’m assuming that the key attribute of your Date dimension is also called Date)
ok thank you i changed. now error is.
MdxScript(V3) (1169, 7) The ‘[CompanySales]’ member was not found in the cube when the string, [Measures].[CompanySales], was parsed. The END SCOPE statement does not match the opening SCOPE statement. MdxScript(V3) (1174, 1) The END SCOPE statement does not match the opening SCOPE statement.
So is [CompanySales] not accessible by the role? If so, then this post will help: http://cwebbbi.wordpress.com/2008/01/22/dimension-security-tips-tricks-and-problems/
Yes [CompanySales] and [CompanySalesWithTax] is not accessible by the Role.
i changed the script. Now every login can connect the cube without error.
I hope there wont be any performance problem.
Thank you very much again.
CREATE SET CURRENTCUBE.[SET4Currency]
AS iif(ISERROR(STRTOMEMBER(“[Measures].[CompanySales]”)), {}
, {Measures.[CompanySales],Measures.[CompanySalesWithTax] } );
//Currency Conversion
Scope (SET4Currency);
SCOPE ([Date].[Day].[Day].members,Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[TRY]));
This = IIF(ValidMeasure([Measures].[ExchangeRate]) = null,null,
[Measures].CurrentMember /ValidMeasure([Measures].[ExchangeRate]));
END SCOPE;
END SCOPE;
%DimensionPermission%
%CubeDimensionID%Measures%/CubeDimensionID%
%Description%Measures%/Description%
%Read%Allowed%/Read%
%AttributePermissions%
%AttributePermission%
%AttributeID%Measures%/AttributeID%
%AllowedSet%
{ [Measures].[SalesQuantity],[Measures].[ExchangeRate] }
%/AllowedSet%
%/AttributePermission%
%/AttributePermissions%
%/DimensionPermission%
Nice work Chris,
In your example, you scope a single measure (SCOPE({[Measures].[Sales Amount]});)
Is there a way to scope all measures that are formatted as currency?
Regards, Martijn
There’s a comment from earlier this year (in reply to cemuney) above that deals with how to scope on multiple measures. Unfortunately there’s no way of getting a set of measures that are formatting a particular way, so you’ll have to list them all manually.
Hi Chris,
Thanks for this and other blogs which have helped us overcome many issues in creating cubes for the first time.The requirement is that we need to report on measures independently by three currencies Settlement & Original & Reporting and show Settlement & Original Currency values in Reporting. In effect there would be two source currencies in your example and the scope statement works for either Settlement or Original but not both, without the scope the link to currencyrate breaks down when reporting in just reporting currency.
Any help would be great
Thanks Andrew
Hi Andrew,
This sounds like a question that’s too complex to answer in a comment. If you can drop me an email with more detail that would be a better idea – you can find my contact details at http://www.crossjoin.co.uk
Chris
Hi Chris,
Many thanks for your reply & yes I think we will have to evalaute the design here further .
Andrew
Hello Chris. Your solution is great! Thank you. I am just wondering if i would like to define default currency what I should do. I tried to change default member of Reporting Currency dimension to particular currency, however it does not work. Have you any idea? Many thanks.
That’s strange, it should work. Which client tool are you using?
I’m using Excel 2010 and Office Web Components browser. Maybe I did something wrong. I will double check and then I’ll get back to you. Thank you.
Superb post! 🙂 I was struggling with Many to Many Currency conversion. Very informative.
the implementation of this works but i am finding it has performance issues that is related to the surface area of the calculation. If i add the dimensions i am looking at using the exists function the performance issue is resolved. this is impractical though as i am going to have to add every attribute hierarchy within the scope statement clause to make sure performance is ok. is there anything else i can do to get around this?
If you have performance issues, and you are on Enterprise Edition, it might be worth trying to implement currency conversion using measure expressions instead rather than MDX – that should give you the best performance.
thanks for the fast response Chris. It doesn’t seem to accept the expression
(measures.ToGBP / measures.FromGBP) * measures.result
it doesn’t seem to like the division operator. Also i have a question regarding expressions should i get it to work. will it total correctly?
Unfortunately a measure expression can only consist of a single division or multiplication operation. What you would need to do is pre-calculate all of the possible ToGBP/FromGBP values in a new table (which hopefully would not be too large), then use a measure expression like measures.rate * measures.result.
thanks Chris, done this and it is almost instant. thanks for the direction.
Hi Chris,
This is indeed an elegant solution. I am struggling with the previous year amount calculation.
we have two types of exchange rates i.e. Reported and operational. For reported rates the we have actual rates for specific time period lets say for Jan-2013 the exchange rate of Euro was 0.8 and in Jan-2014 the exchange rate is 0.85 but in case of operated rates we take the current rates for current period as well as the previous period i.e. 0.85 for both measures current amount and previous year amount.
If I create a measure in fact table Previous year and then apply the exchange rates in ssas it works fine. but if I create a calculated member Previous Year, and then apply the same logic it doesn’t give any conversions, for your reference please see folowing code:
CREATE MEMBER CURRENTCUBE.[Measures].[Pre Year] // calculated member
AS IIF([Transaction Date].[Date Hierarchy].CurrentMember.level.ordinal = 0,
null,
(ParallelPeriod([Transaction Date].[Date Hierarchy].[Year],
1,
[Transaction Date].[Date Hierarchy].CurrentMember),[Measures].[Transaction Amount Pre]
)
),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Sale’ ;
SCOPE({[Measures].[Transaction Amount]}); // fact measure
SCOPE([Transaction Date].[Date Hierarchy].[Date].MEMBERS);
SCOPE([Transaction Currency].[Currency Key].[Currency Key].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Currency Key].[Currency Key].MEMBERS
,{{[Reporting Currency].[Currency Key].&[2147483647]}}));
THIS = IIF([Measures].[Actual – Reporting Rates]=0
, NULL
,IIF([Measures].[Actual] = 0
, NULL
, ([Measures].[Transaction Amount]/[Measures].[Actual]))
* [Measures].[Actual – Reporting Rates]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
SCOPE({[Measures].[Previous Year]}); // fact measure
SCOPE([Transaction Date].[Date Hierarchy].[Date].MEMBERS);
SCOPE([Transaction Currency].[Currency Key].[Currency Key].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Currency Key].[Currency Key].MEMBERS
,{{[Reporting Currency].[Currency Key].&[2147483647]}}));
THIS = IIF([Measures].[Prior Year – Reporting Rates]=0
, NULL
,IIF([Measures].[Prior Year] = 0
, NULL
, ([Measures].[Previous Year]/[Measures].[Prior Year]))
* [Measures].[Prior Year – Reporting Rates] );
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
SCOPE({[Measures].[Pre Year]}); // conversion on calculated member
SCOPE([Transaction Date].[Date Hierarchy].[Date].MEMBERS);
SCOPE([Transaction Currency].[Currency Key].[Currency Key].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Currency Key].[Currency Key].MEMBERS
,{{[Reporting Currency].[Currency Key].&[2147483647]}}));
THIS = IIF([Measures].[Prior Year – Reporting Rates]=0
, NULL
,IIF([Measures].[Prior Year] = 0
, NULL
, ([Measures].[Pre Year]/[Measures].[Prior Year]))
* [Measures].[Prior Year – Reporting Rates] );
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
the last conversion doesn’t work. Can you please help me out.
Cheers
Harris
So it’s the [Pre Year] calculated measure that you don’t see any values for? This is probably because, since it is a calculated measure and not a regular measure, the results of the scoped assignment do not aggregate up. You may need to do something like this: http://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/
Hi Chris
The problem was exactly the same as you mentioned. It is working like a charm.
Thank you so much.
Cheers
Harris
Hi Chris
I am facing another problem i.e. currency conversion with multiple dates. In my fact table there are two dates OrderDate and Invoicedate with amount as measure. Now the business wants to see the amount converted to reporting currency with the exchange rates of respective dates. i.e. if the order date is selected the amount should be converted using exchange rate of order date and when the invoice date is selected the amount should be converted using exchange rate of invoice date.
How can I modify your solution to accomplish this requirement?
Thanks for your help.
Regards
Harris
This is going to be difficult, because there’s no easy way of knowing which date is selected – in MDX there is always a selection. I discussed a similar problem here: http://cwebbbi.wordpress.com/2013/05/23/using-scoped-assignments-to-show-calendar-and-financial-year-to-date-values-in-the-same-calculated-measure/ but honestly, I would investigate changing your requirements so that you have some other mechanism to switch between the two methods of calculating the exchange rates.
Hi All,
Some time ago I read this article http://sqlmag.com/sql-server-analysis-services/many-many-currency-conversions-microsofts-sql-server-analysis-services
I am not sure but I think that it can be helpful.
The best option in my opinion is to combine Chris’s solution with the approach described in this artictle.
Regards,
Kris
Thanks for your reply Chris. I have decided to change my model a bit and instead of role playing dimensions I have decided to use single date dimension with date scenarios. In this way it will be easy to report on all the different dates based on scenario selected. Yes, it will increase the size of the fact table but this model is very flexible even for future. If business needs to incorporate other dates, which I am quite sure they will, it will be easy to extend the scenario dimension and same model will still be fine.
Hi Chris
We have simplified the requirements. Now All the currency conversion are based on single date i.e Create date so if user selects receipt date with the amount it should convert all the amounts based on currency rate of creation date.
If I use your code in this scenario it gives correct results when the create date dimension is selected but when I select other date dimension it gives the conversion only for the dates where create date is available.
e.g. An order was received on 2014-01-01 and was created on 2014-01-10, when receipt date is selected the result is null for 2014-01-01 where as it should return the amount converted with the conversion rate of 2014-01-10 i.e. date on which it was created.
Then I have combined the article http://sqlmag.com/sql-server-analysis-services/many-many-currency-conversions-microsofts-sql-server-analysis-services with your solution and use Exchange Create Date for the conversion it gives me results as expected. But there is another problem here it works well with the fact measures but when I apply the conversion on calculated measure it takes so much time to show the results. I have a measure Prior Year Amount which is calculated based on local currency amounts and then the currency conversion is applied on Prior Year Amount it takes couple of minutes to show the results which is not acceptable for the business.
p.s. The Prior Year Amount should not be calculated based on converted current amount because business wants to apply current year rate on prior year amount so eliminate the currency effect.
Any thoughts.
Regards
Harris
Please ignore my reply about the scenario dimension solution as it is rejected by business as they want to see the results based on two different dates i.e. Orders created in certain periods were processed in which periods. With scenario dimension and single date dimension I can not report this.
I think this problem is too complicated to be solved in the comments of a blog post, I’m afraid – I’m sure there is a solution, but it would take a couple of hours and need direct access to your cube.
HI Chris
What you think is the better way to look at this issue. Shall we arrange an interactive remote session where I can explain you what I am trying to do and what would be the expected outcome.
Regards
Harris
We could certainly schedule a remote session, but this would have to be paid for. You can find my contact details at http://www.crossjoin.co.uk – drop me an email and we can discuss rates, availability etc.
Hi Chris
I will first discuss it with my manager as there is a process to hire services.
If I get a positive reply I will certainly contact you.
Regards
Harris
HI Chris,
Trying to implement this in a real world scenario. I’ve succesfully implemented this in a demo situation..Yeahhh but now I’m trying to implement this in a real world situation and the measure is returning nothing in case i don’t use the currency and the reporting currency in my query.
Any ideas?
Greetz,
Hennie
To be clear: the measure is returning a null value if you don’t use the currency dimension and the reporting currency dimension in your query?
Don’t see nulls but just nothing. If i remove the scope script i have values.
Can’t find errors…
During testing i noticed that for certain periods, when including time dimension. Thnx.
Hennie
It’s probably a problem with your scope statement, but it’s hard to say what the problem is exactly without knowing more about your cube and seeing the code.
The code:
Scope ({[Measures].[Org Invoice Amount]});
Scope([Date].[PK_Date].[PK_Date].MEMBERS);
Scope([Currency].[Currency Description].[Currency Description].MEMBERS);
Scope(Except([Reporting Currency].[Currency Description].[Currency Description].Members,
{{[Reporting Currency].[Currency Description].&[2147483647]}}));
THIS = IIF([Measures].[Rate – Reporting Exchange Rates] = 0, NULL, IIF([Measures].[Rate] = 0, NULL, ([Measures].[Org Invoice Amount] *[Measures].[Rate]))/[Measures].[Rate – Reporting Exchange Rates]);
End Scope;
End Scope;
End Scope;
End Scope;
Thnx again…
If you’re interested I could send you the project?
My first guess was building the currencylogic into the customer solution but that doesn’t work, unfortunately. I think I’m going to work the other way around and building the cube from scratch with the Invoice fact and add the currency logic, then add the dimensions of the customer to the cube….. Hopefully that will give me a clue what the problem is……
Thanx again..
Hennie
Built the cube with the datasource, datasourceview and dimensions of the customer and included the SCOPE script and i’s working. So it must be something in the cube.
Have you set up the many-to-many relationships correctly?
Do you mean Many – to – many conversion or Many – to – many relationship?
I don’t have many to many relationship
See my blog:
http://bifuture.blogspot.nl/2014/09/ssas-multicurrency-problem-part-i.html
http://bifuture.blogspot.nl/2014/09/ssas-multicurrency-problem-part-ii.html
Currently writing part III where I’m introducing the suggestion of you described in the blogpost above.
Gr,
Hennie
Sorry, I wasn’t thinking – you don’t need m2m relationships anywhere here. What I would recommend is replacing
THIS = IIF([Measures].[Rate – Reporting Exchange Rates] = 0, NULL, IIF([Measures].[Rate] = 0, NULL, ([Measures].[Org Invoice Amount] *[Measures].[Rate]))/[Measures].[Rate – Reporting Exchange Rates]);
with
THIS = IIF([Measures].[Rate – Reporting Exchange Rates] = 0, NULL, 1);
to see whether it’s the lack of values for this measure that is causing problems. You should also test Measures.Rate in the same way.
HI Chris,
It seems there is something wrong with the Currencyrates. Reading a currencyrate tabel from Application and in 2014 the data is not entered anymore. I’m investigating this further and if I find some technical problems, I’ll let you know.
Thank you for your time….
It’s a data problem….grmbll..;-)
Greetz,
Hennie
Hi Chris,
I’m using the currency conversion in a many-to-one scenario, I have large number of measures that need converting and looking for ways to improve performance, any suggested improvements to the default scope statement generated by the wizard which is
Scope ( { Measures.[Original Order Value], Measures.[Measure2], Measures[Measure..n]} );
Scope( Leaves([Date]) ,
[Reporting Currency].[NZD],
Leaves([Currency]));
// Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Day Rate]
Scope ( { Measures.[Original Order Value], Measures.[Measure2], Measures[Measure..n]} );
This = [Reporting Currency].[Local] / Measures.[Day Rate];
End Scope;
End Scope; // Leaves of time, source currency and pivot currency
End Scope; // Measures
Thanks
Raf
There’s not much I can suggest here apart from maybe checking for division by zero (or using the DIVIDE() function if you’re on the right version http://cwebbbi.wordpress.com/2013/07/26/new-mdx-divide-function/)
Hi Chris… i want to use the respective currency conversion for the respective loadmonth in the formula of calculated member. please suggest
Thanks,
Shiva
Can you be more specific about what you want to do here?
Hi Chris,
First of all, thank you for this Article.
I am trying the same thing like schou, I want to make an “All time to Date” Measure, which is converted correctly to the reporting Currency. So far it works fine, but I don’t get a value for the [Local] currency. I am trying since hours to change Scopes, but nothing works. I am not professional in this 😉
Can you please have a look? Thank you very much
Jannes
SCOPE({[Measures].[Amount Domestic]});
SCOPE([Source Currency].[Currency].[Currency].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Reporting Currency].[Reporting Currency].MEMBERS
,{{[Reporting Currency].[Reporting Currency].[Reporting Currency].[Local]}}));
// SCOPE([Reporting Currency].[Reporting Currency].[Reporting Currency].MEMBERS);
THIS= (SUM({NULL:[Period Financial].[Financial Period].CurrentMember},[Reporting Currency].[Local])) / Measures.[Midpoint] * Measures.[Midpoint Reporting];
END SCOPE;
END SCOPE;
END SCOPE;
Maybe something like this?
SCOPE({[Measures].[Amount Domestic]});
THIS=SUM({NULL:[Period Financial].[Financial Period].CurrentMember}, [Measures].[Amount Domestic]);
SCOPE([Source Currency].[Currency].[Currency].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Reporting Currency].[Reporting Currency].MEMBERS
,{{[Reporting Currency].[Reporting Currency].[Reporting Currency].[Local]}}));
// SCOPE([Reporting Currency].[Reporting Currency].[Reporting Currency].MEMBERS);
THIS= ([Reporting Currency].[Local]) / Measures.[Midpoint] * Measures.[Midpoint Reporting];
END SCOPE;
END SCOPE;
END SCOPE;
Hi Chris, thank you very much, a variation of this did the job. I had some problems in understanding Scopes. Unfortunately it is quite slow. Do you have a tip how to enhance performance?
Best regards
Jannes
It’s hard to say what the cause of any performance problem is without seeing the cube. It could be the calculation, it could be something else. Try reading the following excerpts from a book I co-wrote on SSAS, and they’ll tell you how to go about determining what the problem is: https://cwebbbi.wordpress.com/2010/03/23/query-performance-tuning-chapter-from-%E2%80%9Cexpert-cube-development%E2%80%9D-available-online/
Hi Chris
Thank you for your solution. Performance is great in comparison to MS alternative.
However, I am wondering how to resolve an issue when you have geographical hierarchy with two levels in LOCAL mode.
Let’s say that I have Continent and Country level. On country level I have Germany (EUR) and Switzerland (CHF).
On Continent level, which is EUROPE in that case, I would like to present values in EUR instead of incorrect arithmetic aggregation of EUR and CHF.
To achieve this, I have added technical measure group where I have two measures: MinCurrency and MaxCurrency.
Then in Local mode in scope assignment I am checking with simple IF statement when MIN MAX and then correct currency in THIS statement is assigned (EUR in this case).
This works fast; however, the problem becomes more complex where we have more than 2 currencies in local mode for given continent.
Let’s say, that in our case we have Sweden (SEK) added. How to apply this logic and have robust implementation easy for configuration changes?
I have introduced currency priority technical measure, that is used by STRTOMEMBER and currency member is built dynamically.
It works and it is generic solution, but performance is degraded.
I can live with multiple IF statements where I have directly defined currency member in THIS statement instead of STRTOMEMBER but maintenance of such solution is painful.
Do you have any ideas what else can be done?
Thank you in advance.
Hmm, that’s a very difficult question to answer. I don’t think I could give you a proper solution without seeing your cube and spending a few hours trying different approaches, I’m afraid.
Hello Chris,
Thanks for the tips above. I was wondering if you knew how to return the ‘Local’ values for the measures scoped in the translation code and only perform the translation when the ‘Reporting Currency’ dimension is invoked by the user? My scenario involves many different local currency values and the requirement is to only translate the measures when the dimension is invoked, otherwise return the ‘Local’ values. Thanks a lot for your help.
You can get the local currency values by selecting the “Local” member on the Reporting Currency dimension. The code posted here already avoids doing the conversion when “Local” is selected; if you want to see these values by default you should make the “Local” member the default member on the Reporting Currency dimension.
That worked perfectly! I completely forgot about the default member. Thanks again!
Hi Chris,
I have another question for you regarding currency translation. So I’m working on setting up translation for ending balances (as a calculated measure) on a Balance Sheet, which requires some lines to translate at the Period End rate and others such as Retained earnings to translate using the Historical rate, which can be achieved by performing the calculation at the leaf of the date dimension.
The Period End lines translate using the appropriate rate and so do the Historical ones, however, the historical lines only return the correct translated ending balance at the year level and not at the quarter or month.
For example, in a balance sheet, the ending balance result should always be the same regardless if you select FY2015 or the month December of FY2015, but the results being returned for December FY2015 are the transactions that were performed during that month and not the ending balance of the accounts up until that month.
I’ve confirmed that if I perform the translation calculation in T-SQL and then simply use that measure (pre-aggregated) to translate, the ending balance result is correct across all levels in the time hierarchy, but not when performing the leaf level calculation in MDX on the ending balance calculated measure. Unfortunately this specific translation type cannot be performed in T-SQL in the ETL since it is dynamically calculating the cross and inverse rate for all currencies. It seems that I need to translate at the leaf level in MDX first then simply aggregate the results depending on which level in the hierarchy is in context.
See below for part of the translation code and the ending balance calculation which is the calculated measure that is being translated for the balance sheet. Hopefully you can help me out with this one. Thanks so much for all your help lately!!!!
–Translation code that is performing leaf level calculation
SCOPE
([Master Date].[Date].[Date].MEMBERS);
SCOPE
(Except
(
[Balance Sheet].[Balance Sheet].[Level Three].[STOCKHOLDERS’ EQUITY].Children
,{
[Balance Sheet].[Balance Sheet].[CTA].Children
,[Balance Sheet].[Balance Sheet].[Net profit/(loss)].Children
}
));
this =
IIF
(
[Measures].[Period End Trans] = 0
,NULL
,IIF
(
[Measures].[Period End] = 0
,//Inverse Rate
[Reporting Currency].[Local]
*
Round
(
Divide
(1
,[Measures].[Period Average Trans]
)
,4
)
,//Cross Rate
[Reporting Currency].[Local]
*
Round
(
Divide
(
[Measures].[Period End]
,[Measures].[Period End Trans]
)
,4
)
)
);
END SCOPE;
END SCOPE;
–Ending Balance Calculation that is being translated
CREATE MEMBER CURRENTCUBE.[Measures].[Ending Balance]
AS
IIF
(
Sum
(
PeriodsToDate
(
[Master Date].[Balance Sheet Calendar].[Year]
,[Master Date].[Balance Sheet Calendar].CurrentMember
)
,[Measures].[Net Change]
) = 0
,Null
,
Sum
(
PeriodsToDate
(
[Master Date].[Balance Sheet Calendar].[Year]
,[Master Date].[Balance Sheet Calendar].CurrentMember
)
,[Measures].[Net Change]
)
+
Sum
(
PeriodsToDate
(
[Master Date].[Balance Sheet Calendar].[Year]
,[Master Date].[Balance Sheet Calendar].CurrentMember
)
,[Measures].[Balance Brought Forward]
)
),
Is that first SCOPE statement incomplete? Should you be scoping on a measure, or measures, there somewhere?
Sorry about that, I only posted the part of the translation code that performs the leaf level translation. See below for the entire code. What seems to happen is that when the translation is performed at the leaf level, it does not aggregate up with the PeriodsToDate function of the Ending Balance calculation. I’m able to generate a PeriodsToDate balance with an additional scope assignment after the translation, but the performance degrades quite a bit. It seems like I should be able aggregate up after the translation is performed at the leaf level within the translation scope assignment. Thanks for the help.
———————————–Balance Sheet Currency Translation——————————————————
SCOPE
([Measures].[Balance Sheet Aggr]);
SCOPE
([Currency].[Currency].[Currency].MEMBERS);
//Local to USD Translation
SCOPE
([Reporting Currency].[Currency].[Currency].[USD]);
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Month End]);
THIS =
IIF
(
[Measures].[Period End] = 0
,[Reporting Currency].[Local]
,
[Reporting Currency].[Local] * [Measures].[Period End]
);
END SCOPE;
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Historical]);
SCOPE
([Master Date].[Date Key].[Date Key].MEMBERS);
this =
IIF
(
[Measures].[Period End Hist] = 0
,[Reporting Currency].[Local]
,
[Reporting Currency].[Local] * [Measures].[Period End Hist]
);
END SCOPE;
END SCOPE;
SCOPE
Descendants
(
[Account].[Translation Type].[Translation Type].[Month Average]
);
this =
IIF
(
[Measures].[Period Average] = 0
,[Reporting Currency].[Local]
,
[Reporting Currency].[Local] * Round([Measures].[Period Average],4)
);
END SCOPE;
END SCOPE;
//Many to Many Translation
SCOPE
Except
(
[Reporting Currency].[Currency].[Currency].MEMBERS
,{
[Reporting Currency].[Currency].[Currency].[Local]
,[Reporting Currency].[Currency].[Currency].[USD]
}
);
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Month End]);
THIS =
IIF
(
[Measures].[Period End Trans] = 0
,NULL
,IIF
(
[Measures].[Period End] = 0
,
[Reporting Currency].[Local]
*
Round
(
Divide
(1
,[Measures].[Period End Trans]
)
,4
)
,
[Reporting Currency].[Local]
*
Round
(
Divide
(
[Measures].[Period End]
,[Measures].[Period End Trans]
)
,4
)
)
);
END SCOPE;
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Historical]);
SCOPE
([Master Date].[Date Key].[Date Key].MEMBERS);
this =
IIF
(
[Measures].[Period End Trans Hist] = 0
,NULL
,IIF
(
[Measures].[Period End Hist] = 0
,
[Reporting Currency].[Local]
*
Round
(
Divide
(1
,[Measures].[Period End Trans Hist]
)
,4
)
,
[Reporting Currency].[Local]
*
Round
(
Divide
(
[Measures].[Period End Hist]
,[Measures].[Period End Trans Hist]
)
,4
)
)
);
END SCOPE;
END SCOPE;
SCOPE
Descendants
(
[Account].[Translation Type].[Translation Type].[Month Average]
);
this =
IIF
(
[Measures].[Period Average Trans] = 0
,NULL
,IIF
(
[Measures].[Period Average] = 0
,
[Reporting Currency].[Local]
*
Round
(
Divide
(1
,[Measures].[Period Average Trans]
)
,4
)
,
[Reporting Currency].[Local]
*
Round
(
Divide
(
[Measures].[Period Average]
,[Measures].[Period Average Trans]
)
,4
)
)
);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
———————————-Balance Sheet Aggregate Leaf calculations———————————-
SCOPE
([Measures].[Balance Sheet]);
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Month End]);
this =
[Measures].[Balance Sheet Aggr];
END SCOPE;
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Month Average]);
this =
[Measures].[Balance Sheet Aggr];
END SCOPE;
SCOPE
Descendants([Account].[Translation Type].[Translation Type].[Historical]);
SCOPE
Root([Reporting Currency]);
this =
[Measures].[Balance Sheet Aggr];
END SCOPE;
SCOPE
Except
(
[Reporting Currency].[Currency].[Currency].MEMBERS
,{[Reporting Currency].[Currency].[Currency].[Local]}
);
This =
Sum
(
PeriodsToDate
(
[Master Date].[Balance Sheet Calendar].[Year]
,[Master Date].[Balance Sheet Calendar].CurrentMember
)
,[Measures].[Balance Sheet Aggr]
);
END SCOPE;
END SCOPE;
END SCOPE;
I’m probably not going to be able to debug something this complex just by looking at the code, I’m afraid. There must be a problem with one of the scopes; have you tried using the BACK_COLOR trick I show here https://blog.crossjoin.co.uk/2013/05/23/using-scoped-assignments-to-show-calendar-and-financial-year-to-date-values-in-the-same-calculated-measure/ to find out whether you are doing the calculation at the correct level?
I’ll definitely read over that, thanks. From a purely logical perspective, if you were scoping a calculated measure that is performing a YTD calculation and within that scope assignment, you go down to the leaf of the time dimension to perform an additional calculation on that same calculated measure, would you expect the scope assignment to aggregate up the results from the leaf level up to the YTD defined in the calculated measure?
I figured it out! The issue was that the ‘Balance Sheet’ measure that I was scoping was a real, ‘dummy’, measure, which has a different aggregation behavior then what I needed for the balance sheet. I ended up scoping the ‘Ending Balance’ calculated measure for the translation logic and then simply assigned that calculated measure results to the real, dummy ‘Balance Sheet’ measure. I decided to re-watch your sql bits video ‘Fun with Scoped Assignments’ and it all clicked after the aggregation behavior differences between real and calculated measures part. Thanks again!
I have implemented this currency conversion script and it works great. But. There seems to be an issue if used for multiple measures from the same measure group.
If I use no more than one measure from every measure group and any kind of calculated members it works great, faster than MS generated code. If I however add two normal measures from one measure group, even if the measure group just has a couple of thousand records, it goes from almost instant for one measure to several seconds for 2 measures and probably minutes for 3 measures. Seems to be exponentially getting worse when you add more measures.
This problem does not occur with the MS code for some reason.
Any ideas?
Thanks,
Mike
It’s impossible to say what’s going on here, I’m afraid – I’d have to spend some time testing and looking at Profiler traces to know for sure. Are there any other calculations being used in the same query?
Nope, nothing else, just get the aggregated total of the 1,2 or 3 measures per 6 different target currencies.
I can include lots of different dimensions but it all amounts to the same thing. It takes several times as long if using more than one measure from the same measure group. 3 measures form three different measure groups are totally fine. Strangest thing to me is that if I add a calculated measure taking measure 1 – measure 2 of my measure group I can use that calculated measure together with the any of the 3 measures in my measure group and will get quick responses.
Do the measures aggregate up in different ways – for example are some Counts and others Sums? Are they in different measure groups? If so this caching behaviour could be part of the answer: https://blog.crossjoin.co.uk/2012/11/27/storage-engine-caching-measures-and-measure-groups/
All measures are sum aggegrations.
The issue is only when I add more than one measure from the same measure group.
I have 5 different measure groups, I can take 1 measure from each measure group and get good performance.
If I add 2 measures from the same measure group the performance is much worse.
Should not be the same case as you were writing about in that other post as it is quick from multiple mesure groups and all measures in my measure group use the same aggregation. Might be related to that the query engine somehow does something not so good performance wise when adding multiple measures from the same measure group and performing these currency conversion calculations.
Thanks!
Dear Chris,
Thanks for all the articles which have been a lifesaver.
I am trying to implement a similar currency conversion to the one in this article with a slight twist that the (reporting) currency conversion (many to one) should use the last date in a set of periods selected by the user. (There was a similar thread at the top but the comments were taken offline!)
The example below has periods Jan to Oct in 2018 selected, and all calculations should be based on the last date 20181031.
Period | Rate at Period | LastDate | LastDateExisting
Jan 2018 | 7.285 | 20191231 | 20180131
Feb 2018 | 7.273 | 20191231 | 20180228
Mar 2018 | 7.275 | 20191231 | 20180331
Apr 2018 | 7.201 | 20191231 | 20180430
May 2018| 7.146 | 20191231 | 20180531
Jun 2018 | 7.118 | 20191231 | 20180630
Jul 2018 | 7.116 | 20191231 | 20180731
Sep 2018 | 7.102 | 20191231 | 20180930
Oct 2018 | 7.027 | 20191231 | 20181031
I have tried using MAX with and without existing as below with a view to getting the last exchange rate.
WITH
MEMBER Measures.[LastDate] AS
MAX( [Accounting Date].[Year – Quarter – Month – Date].[Date].MEMBERS,
IIF( [Measures].[Rate at Period] = 0,
NULL,
[Accounting Date].[Year – Quarter – Month – Date].CurrentMember.Member_Key
)
)
MEMBER Measures.[LastDateExisting] AS
MAX( EXISTING [Accounting Date].[Year – Quarter – Month – Date].[Date].MEMBERS,
IIF( [Measures].[Rate at Period] = 0,
NULL,
[Accounting Date].[Year – Quarter – Month – Date].CurrentMember.Member_Key
)
)
I would like to see the last date column as all ‘20181031’ as below.
Given that the context is always used (I’m assuming as I’m using currentmember) is there any other way to achieve a result set like this?
Period | Rate at Period (overridden) | LastDate | LastDateExisting
Jan 2018 | 7.027 | 20181031
Feb 2018 | 7.027 | 20181031
Mar 2018 | 7.027 | 20181031
Apr 2018 | 7.027 | 20181031
May 2018| 7.027 | 20181031
Jun 2018 | 7.027 | 20181031
Jul 2018 | 7.027 | 20181031
Sep 2018 | 7.027 | 20181031
Oct 2018 | 7.027 | 20181031
Any help appreciated.