1

I have got a measure, getting the last value of a currency exchange rate. The fact having the exchange rates, is configured in the dimension usage with the time dimension, and is daily based. So, using MDX, I am successfully getting my converted measure in my currencies, by using a calculated measure:

with member [Measures].[Calculated Comp Money In] as 
SUM([Dim Time].[Date Key].CurrentMember,
        [Measures].[Comp Money In]/[Measures].[Last Currency Rate])

And then in the where clause, I would filter out what currency I am reporting figures on

[Dim Currency].[Currency Key].&[200]

However, what I don't like is that I have a [Measures].[Comp Money In], and a [Measures].[Comp Money In Calculated].... Can I use the SCOPE function in MDX, so that [Measures.[Comp Money In] is configured with the calculation above? I would then add this calculation in the calculations section of SSAS.

Mez
  • 4,666
  • 4
  • 29
  • 57

1 Answers1

4

If you use the Enterprise edition of SSAS, you can use the measure expression property of your measure do currency conversion. This is not very well documented in the Analysis Services documentation, but works as follows: You use only one measure (i. e. either [Measures].[Calculated Comp Money In] or [Measures].[Comp Money In], and in the "Measure expression" property of this measure, enter the expression that you have in your question above:

[Measures].[Comp Money In] / [Measures].[Last Currency Rate]

Then the Analysis Services engine will take care of the rest for you. Note that this expression may only contain the measure on which you define it, times or divided by one other measure (normally he exchange rate) and nothing else.

If you do not have The Enterprise edition of Analysis Services, you can more or less copy your definition to a calculated member:

To be able to continue using the existing measure, possibly rename the measure [Measures].[Comp Money In] to [Measures].[_Comp Money In] (and make that measure invisible when you have checked that everything is fine). Then define a calculated member with the same name as the original measure:

create member CurrentCube.[Measures].[Comp Money In] as 
       SUM([Dim Time].[Date Key].CurrentMember,
           [Measures].[_Comp Money In]/[Measures].[Last Currency Rate])

Better still, use SCOPE to calculate the measure, and avoid the aggregation for single days: Assuming the only common dimension between the measure group containing [Measures].[Comp Money In] and the measure group containing [Measures].[Last Currency Rate] is [Dim Time] and this has the key attribute [Dim Time].[Date Key], and your date hierarchy is named [Dim Time].[Date], you could just use

CREATE MEMBER CurrentCube.[Measures].[Comp Money In] as NULL;
SCOPE([Dim Time].[Date Key].[Date Key].members);
    [Measures].[Comp Money In] =
        [Measures].[_Comp Money In] / [Measures].[Last Currency Rate];
END SCOPE;
SCOPE([Dim Time].[Date].Members);
    [Measures].[Comp Money In] =
        Sum(EXISTING [Dim Time].[Date Key].[Date Key].members);
END SCOPE;
FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • Hi @FrankPI I cannot use semi-additive because I am on standard. That also means that I cannot use expressions. It would have been much easier had I had it though :) Regarding your answer, I am trying to avoid having to create [Calculated Comp Money In] ... Is it possible in the calculation section of SSAS to alter how the actual [Comp Money In] is generated? – Mez Jul 14 '14 at 08:30
  • 1
    @Mez I am not sure if it works to avoid having unconverted and converted measures for this purpose, and I find it more clear to have both unconverted and converted measures available for debugging. I added the suggestion to make the original measure invisible to my answer, as clearly, from an end user perspective, the unconverted measures should not be visible. – FrankPl Jul 14 '14 at 11:15
  • Yep that is the thing, because I would like to maintain the [Measures].[Comp Money In] name. So it is correct to say, that it is not possible, not even with the scope? I had in mind to use the scope for [Measures].[Comp Money In], and then assign this value to the value of [Measures].[Calculated Comp Money In] - so users would see [Measures].[Comp Money In] however it would be the value converted with the currency selected and exchange rate. – Mez Jul 14 '14 at 11:19
  • 1
    @Mez You could try something like `SCOPE([Dim Time].[Date Key].[Date Key].Members);[Measures].[Comp Money In] =[Measures].[Comp Money In]/[Measures].[Last Currency Rate];END SCOPE;` in the calculation script. This is a recursive assignment, and in some cases it will work, as AS detects and avoids an infinite recursion. In some cases it does not detect it and fail. When it works and when it does not work is undocumented. And maybe it works now, but a small change in the calc script in future will trigger AS not recognizing the situation. For me, this would just be too dangerous to deploy. – FrankPl Jul 15 '14 at 09:14
  • So you think best way forward is to paste the calculation in all MDX queries then.. Ouch - that is what I was afraid of. – Mez Jul 15 '14 at 13:04
  • 1
    @Mez No, you can put the calculations into the cube calculation script: Just rename the original measure `Comp Money In` in the cube to e. g. `_Comp Money In` and then add a calculated measure *in the cube calculation script*: `create member CurrentCube.[Measures].[Comp Money In] as SUM([Dim Time].[Date Key].CurrentMember, [Measures].[_Comp Money In]/[Measures].[Last Currency Rate])`. If all is tested, make the measure `_Comp Money In` invisible. So, accessing the cube from outside, you only see the correctly calculated measure `Comp Money In`´. – FrankPl Jul 15 '14 at 13:58
  • That is a very good idea @FrankPI - I will try that out and let you know. – Mez Jul 15 '14 at 14:27
  • Hi Frank, this current member is working when I put [Dim Time].[Date Key] on my rows, however when I put for instance something else, like [Dim Product Category].[ProductHierarchy].Children - and I use a filter to select the dates like for example filter([Dim Time].[Date Key].members, [Dim Time].[Date Key].CurrentMember.Member_Caption >= "20140701") - the date context is not being taken.... Any ideas? – Mez Jul 18 '14 at 05:56
  • Hi @FrankPI I am having a problem with the measures with the formulae. It is working for the daily reports, because the daily reports are grouped by day, and I am successfully getting the last currency rate. However, when I put users on the rows, and search for 1st till the 4th - it seems it is taking the rate of the 4th only. This is incorrect, as data on the 1st, should take into consideration the rate of the 1st. Is there anyway I can enforce, that the figure always takes into consideration the [Dim Time].[Date Key] - and the last currency rate of that date? – Mez Jul 22 '14 at 21:30
  • 1
    @Mez Oh yes. `[Comp Money In]` is a calculated member, hence there is no automatic aggregation. I added that at the end of my answer. – FrankPl Jul 23 '14 at 07:26
  • @FrankPI after adding the second part - the MDX is not finishing to execute. Taking ages. Also, why is it SCOPE([Dim Time].[Date].Members); and not SCOPE([Dim Time].[Date Key].Members); ? – Mez Jul 23 '14 at 17:24
  • Hi @FrankPI, when adding SCOPE([Dim Time].[Date].Members); figures are incorrect - apart from taking like 1 hour to finish for 6 days. Before adding this, they were OK but not just taking daily exchange rates. Any ideas? – Mez Jul 24 '14 at 07:30
  • @Mez I was guessing the names of the attributes and user hierarchy of your time dimension. I normally use the Enterprise Edition, not the Standard Edition. You could try to define `Comp Money In` as physical measure with `sum` as the aggregation, and thus leave the aggregation calculation to the Analysis Services internal mechanisms, which should be fast. To do that, add this measure to the physical measures on the first tab on the Cube Editor. Then remove the `CREATE MEMBER` and the second `SCOPE` statement, i. e. only keep the `SCOPE([Dim Time].[Date Key].[Date Key].members)`. – FrankPl Jul 25 '14 at 07:03
  • Hi @FrankPI - The physical attributes are renamed to _Comp Money In, so that I put these to invisible - and only display those in the calculated tab (and these are the attributes which have the currency conversion). If I leave only the SCOPE([Dim Time].[Date Key].[Date Key].members) - I wouldn't have the conversion happening. It seems I need to use the SCOPE, in order to aggregate by [Dim Time].[Date Key], so that the currency is done with the daily rate, and not just the DateTo in context when querying. – Mez Jul 25 '14 at 11:58
  • Hi @FrankPI, does doing this make sense? MEMBER [Measures].[Comp Money In2] AS SUM( [Dim Time].[Date Key].members,[measures].[comp money in]) ? It is taking over an hour to get 6 days of data though :( Which is too much. The COMP MONEY IN is the calculated measure in the calculations tab. – Mez Jul 26 '14 at 12:42
  • @Mez What I meant was: Have one invisible physical measure as discussed earlier. Then have another physical measure that is overwritten on all time dimension key level members in a `SCOPE` statement. But that second physical measure would use the aggregation as defined in its physical measure definition - which is faster that an aggregation defined in the calculation explicitly. And that should be `sum` of course.. The physical aggregation is what is done by the first statement in the calc script: `CALCULATE`. – FrankPl Jul 28 '14 at 16:59