Hi,

I have 12 measures according to the months in a year:

Period 01
.....
....
Period 12

I want to create hierarchy in the measure dimension, like Q1...Q4 and Total Year. The structure should have the months at the bottomlevel. Is this a problem in Analysis Services or could I solve it differently. "Parent member of" is disabled in the Measure dimension. And if it was enabeled it shouldn't solve my problem since Qx is a parent instead of a child of Period xx.

The background of my problem is actually a currency conversion approach where I can't us the months, quarters and total year in a dimension. I have tested this situation but the roll up of the period dimension will always prevail. Conversion is executed at second phase. I also have heard that this is the basic rule of MSAS, first aggregating then executing calculated members.

However, the conversion should first calculated at month level (by calculated members) and then roll-up. Custom roll-up with CalculationPassValue set to pass 1 and Currency conversion set to pass 0 doesn't work. Maybe this is because I'm using the standard edition instead of Enterprise edition? Solve order doesn't help either. The rates are -of course- taken from a LookupCube function and works fine.

The measures dimension is flat by definition. Not sure what date/measures
hierarchy has to do with currency conversion though -- this doesn't seem
like an effective approach to the problem.

I seem to recall that calculated cells requires Enterprise Edition. It is
possible to do currency conversion this without calc cells though. How many
measures do you have? How many of them are currency?

public @ the domain below
www.tomchester.net

"J. Nathalia" <anonymous (AT) discussions (DOT) microsoft.com> wrote

a problem in Analysis Services or could I solve it differently. "Parent
member of" is disabled in the Measure dimension. And if it was enabeled it
shouldn't solve my problem since Qx is a parent instead of a child of Period
xx.
tested this situation but the roll up of the period dimension will always
prevail. Conversion is executed at second phase. I also have heard that this
is the basic rule of MSAS, first aggregating then executing calculated
members.
CalculationPassValue set to pass 1 and Currency conversion set to pass 0
doesn't work. Maybe this is because I'm using the standard edition instead
of Enterprise edition? Solve order doesn't help either. The rates are -of
course- taken from a LookupCube function and works fine.

Hi Tom

If the Measure dimension is flat by definition I think this topic will move forward to a currency conversion problem

I will give more information what I have been doing

In a datawarehouse financial information is stored in local currencies. A DTS is filling a FACT table where all local data is converted to a base currency (in case GBP). The cube(s) will basically store financial data in GBP (base currency). The cube should also report in different currencies (so called reporting currencies).

However it's not important in this case, I would also like to mention that two conversion rates are applicable. End-of-month rates for balance sheet items and Average month rates for profit & loss. This is captured in the logic by evaluating the member property of the financial account

1st approach
Financial Cube
Account dimension consiting of P&L and B/S item
Period dimension consisting of Total Year, Q1..Q4, M01...M12 (drill down hierarchy
Year dimension consisting of fiscal year
Measure dimension consisting of measures/members Actual, Budget and Forecas

Exchangerate Cube
The Financial cube is using xchange rates via LookupCube function to Exchangerate cube

The conversion logic has been implemented (without giving details of the MDX statements). The problem I'm facing is an order of calculation. Since the aggregation of dimensions is performed first and secondly the execution of calculated members, the conversion is correct at month level but not at Quarter level. It's converting the quarter value of the financial account against the rate at quarter level. However the latter part can be solved by taking the last quarter of the month, principally this is not correct because the order of calculation should be first the conversion of the financial account at month level (pass n), following by aggregating the result to quarter level (pass n+1). This will cause into a aggregated figure at quarter level instead of converted value at quarter level which is wrong

2nd approach
Probably this approach feels strange, but the Measure dimension contains now the months, quarter and total year. The Actual, Budget and Forecast members are now moved to a Scenario dimension. The reporting currencies are now calculated by a calculated member with one simple statement. Conversion of the months are again perfectly executed. Quarters and total year can also be perfectly calculated by a calculated member since the solve_order or order of the individual calculated members (Mxx, Qx, Total Year) implies already that Qx and Total Year are calculated correctly. The only disadvantage thing is the lack of a hierarchy

Finally, I think the 1st approach is preferrable since a hierarchy is available. The problem is actually how can I perform a "Custom Rollup Formula" after the execution of a calculated member. The Custom Rollup Formula is just an SUM of the children within a quarter etc. Is applying CalculationPassValue appropriate or is this problem simply not possible?

If not possible, the alternative is multiplying the fact tables by the amount of reporting currencies and do a pre-calculation before reading into the cube. This is absolutely not preferrable since the cube data will be blown up unnecessarily by the amount of reporting currencies

Regards
Jeroen Nathali
OLAP consultan
Hyperion Essbase & MS Analysis Service
The Netherlands

Jeroen, this type of problem is difficult to solve with database in hand,
and impossible without. Here's about all I can offer:

- scenario dimension is a good idea; don't like to see it put in measures
- don't assume that a custom member formula needs to be part of solution;
you can use calc cells to aggregate the data that was converted at month
level
- putting year/months in measures is a poor design

Good luck.

public @ the domain below
www.tomchester.net

"J. Nathalia" <anonymous (AT) discussions (DOT) microsoft.com> wrote

currency (in case GBP). The cube(s) will basically store financial data in
GBP (base currency). The cube should also report in different currencies (so
called reporting currencies).
items and Average month rates for profit & loss. This is captured in the
logic by evaluating the member property of the financial account.
the aggregation of dimensions is performed first and secondly the execution
of calculated members, the conversion is correct at month level but not at
Quarter level. It's converting the quarter value of the financial account
against the rate at quarter level. However the latter part can be solved by
taking the last quarter of the month, principally this is not correct
because the order of calculation should be first the conversion of the
financial account at month level (pass n), following by aggregating the
result to quarter level (pass n+1). This will cause into a aggregated figure
at quarter level instead of converted value at quarter level which is wrong.
members are now moved to a Scenario dimension. The reporting currencies are
now calculated by a calculated member with one simple statement. Conversion
of the months are again perfectly executed. Quarters and total year can also
be perfectly calculated by a calculated member since the solve_order or
order of the individual calculated members (Mxx, Qx, Total Year) implies
already that Qx and Total Year are calculated correctly. The only
disadvantage thing is the lack of a hierarchy.
Formula" after the execution of a calculated member. The Custom Rollup
Formula is just an SUM of the children within a quarter etc. Is applying
CalculationPassValue appropriate or is this problem simply not possible?
the cube. This is absolutely not preferrable since the cube data will be
blown up unnecessarily by the amount of reporting currencies.

Incidentally, you can even do it with a calc member, made easier by having
just one measure. Assume this psuedo-calc is named Measures.X:

IIF( Time.CurrentMember.Level IS [Month],
<currency conversion formula here> ,
SUM(Descendants(Time.CurrentMember,,[Month]), Measures.X)
)

As you know, you also need conditional logic so that non-currency accounts
aren't converted.

public @ the domain below
www.tomchester.net

Correction, there was an extra comma:

IIF( Time.CurrentMember.Level IS [Month],
<currency conversion formula here> ,
SUM(Descendants(Time.CurrentMember,[Month]), Measures.X)
)

public @ the domain below
www.tomchester.net

This is really an interesting discussion....

Does this formula sum up itself ? Does it mean below that It will make a sum
of the currency converted amount ?

\Michael Vardinghus

Tom Chester <publicNOSPAM (AT) tomchester (DOT) net> wrote

Yes and Yes.

public @ the domain below
www.tomchester.net

"Michael Vardinghus" <mivar (AT) wmdata (DOT) dk> wrote

Tom

Thanks for you recommendation. The approach works fine!!

1) Before closing this issue I would like to know the arguments why you do not prefer Scenario (Actual, Budget, Forecast) in the measures dimension but as a separate dimension. I do prefer them as measures since they are the only members that do not have a hierarchy. Besides my measures/accounts are taken into a separate dimension because it's large and it has a hierarchy

2) Is it better to disable the Period dimension by a custom rollup formula equal to "0" for performances reasons? Or is a ~ as unary operator better

3) If I apply a SOLVE_ORDER of < -5119 to my original calculated member, why is still my calculated member executed after the roll-up. I have read in a book (George Spofford) that if you apply a solve order smaller than -5119 the calculated member is calculated first instead of the aggregation

4) What are the consequences in terms of performance of having your approach against multiplying the fact table by the amount of reporting currencies

Thanks in advance, I do appreciate you recommendation very much

Regards
Jeroen

1) The same reason I don't like to see ANY separable dimensions baked into
measures -- the measures dim is uniquely inflexible. Adding a new member
requires schema change and reprocess of cube, to cite one example.
2) I suspect the difference would be immeasurable. If unaries are already
defined then I'd tend to use the unary; I certainly wouldn't make the dim
unary for this reason. (I'd use NULL rather than zero though.)
3) Too hard to diagnose without database in hand
4) You're going to have to remind me what my approach is! <g> I don't
understand what "multiplying the fact table by the amount of reporting
currencies" means.

public @ the domain below
www.tomchester.net

"J. Nathalia" <anonymous (AT) discussions (DOT) microsoft.com> wrote

as a separate dimension. I do prefer them as measures since they are the
only members that do not have a hierarchy. Besides my measures/accounts are
taken into a separate dimension because it's large and it has a hierarchy.
a book (George Spofford) that if you apply a solve order smaller than -5119
the calculated member is calculated first instead of the aggregation.
currencies.