June 3, 2014 at 2:57 am
Hi,
I have a client with an SSAS database (SQL Server 2012 multidimensional) reading from a Data Warehouse. The DW contains measures in three different currencies, i.e. "Amount Local Currency" (amount converted to local currency of each source company), "Amount Original Currency" (amount in original currency of the transaction) and "Amount SEK" (amount converted to group currency SEK). The cube currently just shows these measures as regular measures as all conversion is done before or in the DW. There are also two regular dimensions, "Local Currency" and "Original Currency", that just show the currencies of the local and original amount.
However, the client has asked for an easier way of choosing currency, as all measures are x3 due to the currency converted measures. As a test I have created calculated measures, i.e. "Amount", that choose one of the base measures based on the current member of a new dimension, "Currency Conversion" (containing the members "Original", "Local" and "SEK"). The idea is to use this dimension to choose what level of currency conversion should be used in the amounts. The default of the new dimension is set to "Local" so if the dimension is not used amounts are displayed in local currency.
The calculated measures are defined like this:
[font="Courier New"]iif([Currency Conversion].[Currency Conversion].currentmember = [Currency Conversion].[Currency Conversion].[SEK], [Measures].[Amount SEK],
iif([Currency Conversion].[Currency Conversion].currentmember = [Currency Conversion].[Currency Conversion].[Original], [Measures].[Amount Original Currency],
[Measures].[Amount Local Currency]))[/font]
The logic works well, but in some queries performance is horrible for the calculated measure. The base measures do not have any performance issues. My first thought was that this might be because if you run a query generating a big crossjoin the calculated measure is evaluated for all rows, before NON EMPTY, whereas with the base measures SSAS "knows" which are NON EMPTY (because of autoexists) without having to evaluate all rows. However I have set the "Non-empty behavior" property to one of the base measures, which I understand should deal with exactly that issue.
Any ideas about what might be the issue, and how to solve it, would be highly appreciated.
June 3, 2014 at 7:48 am
You could do this with nested Scopes. Like so:
CREATE MEMBER CURRENTCUBE.Measures.[Amount] AS NULL;
SCOPE (Measures.Amount);
SCOPE
(Measures.Amount);
IF [Currency Conversion].[Currency Conversion].currentmember IS [Currency Conversion].[Currency Conversion].[SEK]
THEN
THIS = [Measures].[Amount SEK]
END IF;
END SCOPE;
SCOPE
(Measures.Amount);
IF
[Currency Conversion].[Currency Conversion].currentmember IS [Currency Conversion].[Currency Conversion].[Original]
THEN
THIS = [Measures].[Amount Original Currency]
END IF
;
END SCOPE;
SCOPE
(Measures.Amount);
IF
[Currency Conversion].[Currency Conversion].currentmember IS [Currency Conversion].[Currency Conversion].[Local]
THEN
THIS = [Measures].[Amount Local Currency]
END IF
;
END SCOPE;
END SCOPE;
Apologies for the formatting. Hope that helps 🙂
June 3, 2014 at 8:21 am
That worked better! Thank you so much!
June 3, 2014 at 8:26 am
No worries 🙂
But I must apologize, that was needlessly messy and I forgot about using Local Currency as a default.
Have a go with this instead...
CREATE MEMBER CURRENTCUBE.Measures.[Amount] AS [Measures].[Amount Local Currency];
SCOPE
([Currency Conversion].[Currency Conversion].&[SEK], Measures.Amount);
THIS = [Measures].[Amount SEK]
END SCOPE;
SCOPE
([Currency Conversion].[Currency Conversion].&[Original], Measures.Amount);
THIS = [Measures].[Amount Original Currency];
END SCOPE;
That might be a bit better (it certainly looks nicer!).
June 3, 2014 at 8:32 am
A bit cleaner, I'll go with that! Thanks again! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply