13 Replies Latest reply: Sep 13, 2013 12:26 PM by DanPressman RSS

    Help with ASO formula calculated values aggregation to Parent members

    Srinivas Madderla

      Hi All,

       

      I am trying to perform currency USD conversions in ASO cube.

      I am loading NetSales and FX Rate to Local currency and calculating the USD values using ASO MDX outline member formula.

      First I tried without specifying the condition level0 members and the calculations are correct at lev0 but the parent member conversions are incorrect as all the FX rates are rolled to Parent Entity which are loaded at company code level in Entity Dimension. I modified the MDX member formula to do conversions at lev0 only, but the level 0 conversions are not rolling to Parent levels resulting #Missing values at parent members.

       

      USD = LocalCurr\FX Rate only for level0 members.

       

      I want the level 0 calculated values to be consolidated to parent without any conversions to parent.

      Is there a way to aggregate the level0 calculated values to parent members using MDX Outline member formula?

       

      Thanks in advance.

      Srini.

        • 1. Re: Help with ASO formula calculated values aggregation to Parent members
          TimG

          In ASO stored hierarchies are always consolidated prior to formula execution.  You can not execute a formula at level zero and then have the result rolled up by a stored hierarchy.

           

          This is a classic ASO problem.  Check out (just for example): Re: Currency Conversion in ASO Member Formula using MDX or ASO Solve Order

           

          I'd also suggest you Google 'ASO Currency Conversion' - this will throw up a lot of forum questions and blog posts on the topic.

          • 2. Re: Help with ASO formula calculated values aggregation to Parent members
            AmarnathK

            Hi Srinivas

            In general, when you restrict your calculation to work using a CASE/IIF in a member formula (As you did in level0) you have to mention what it should calculate if it is not level0 (For Example, Sum the children) if not it will just calculate level0 members and ignore the rest.

             

            One Question

            FX rates are rolled to Parent Entity which are loaded at company code level in Entity Dimension

            Do you have a scenario where a company can make transaction in multiple curencies? Is that the reason why you have loaded the FX rate by company?

             

            Amarnath

            http://amarnath-essbase-bog.blogspot.com

            • 3. Re: Help with ASO formula calculated values aggregation to Parent members
              Srinivas Madderla

              Hi Amarnath,

               

              As we are using just LocalCurr and USD in currency dimension, we are trying to load FX Rates at different Company codes with different FX rates.

              Is there a way to explicitly mention in the MDX formula to roll the level 0 members to parent member if it is not a level 0 member on all dimensions?

              The calculation is working on lev0 members so need nto roll the level0 calculated numbers to it parent members in all dimensions.

               

              Thanks

              Srinivas

              • 4. Re: Help with ASO formula calculated values aggregation to Parent members
                TimG

                There's an example posted by Gary Crisci in the second link I posted.

                 

                You don't really need to worry about all dimensions, do you?  Allowing the local currency numbers to roll up in the other dimensions before FX conversion is not a problem (except time, probably), is it?

                • 5. Re: Help with ASO formula calculated values aggregation to Parent members
                  Srinivas Madderla

                  Hi Tim,

                   

                  Thanks for the reply.

                   

                  I need aggregations for all the dimensions as users will run their reports at different levels in different dimensions. I tried your suggestion but not able to achieve the results.

                   

                  CASE

                  WHEN IS([Scenario].CurrentMember,[Actual]) AND IS([Period].CurrentMember,[&FlshCYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Timeview].CurrentMember,[Flash View]) AND IS([Account].CurrentMember,[EST_TotNS]) AND IS([DataType].CurrentMember,[Flash_Inp]) AND IS([Dataview].CurrentMember,[Amount]) AND ISLEVEL([Segmnt].CurrentMember, 0) AND ISLEVEL([Brand].CurrentMember, 0) AND ISLEVEL([Area].CurrentMember, 0) AND ISLEVEL([Entity].CurrentMember, 0) AND ISLEVEL([BusType].CurrentMember, 0) AND ISLEVEL([CostCenter].CurrentMember, 0)

                  THEN ([HE_NS_OTHER],[LocalCur])/([Actual],[&FlshCYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                   

                   

                  WHEN IS([Scenario].CurrentMember,[&FRSPE]) AND IS([Period].CurrentMember,[&FlshCYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL([Segmnt].CurrentMember, 0) AND ISLEVEL([Brand].CurrentMember, 0) AND ISLEVEL([Area].CurrentMember, 0) AND ISLEVEL([Entity].CurrentMember, 0) AND ISLEVEL([BusType].CurrentMember, 0) AND ISLEVEL([CostCenter].CurrentMember, 0)

                  THEN [LocalCur]/([&FRSPE],[&FlshCYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                   

                   

                  WHEN IS([Scenario].CurrentMember,[Plan.Final]) AND IS([Period].CurrentMember,[&FlshCYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL([Segmnt].CurrentMember, 0) AND ISLEVEL([Brand].CurrentMember, 0) AND ISLEVEL([Area].CurrentMember, 0) AND ISLEVEL([Entity].CurrentMember, 0) AND ISLEVEL([BusType].CurrentMember, 0) AND ISLEVEL([CostCenter].CurrentMember, 0)

                  THEN [LocalCur]/([Plan.Final],[&FlshCYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                   

                   

                  WHEN IS([Scenario].CurrentMember,[Actual]) AND IS([Period].CurrentMember,[&FlshPYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL([Segmnt].CurrentMember, 0) AND ISLEVEL([Brand].CurrentMember, 0) AND ISLEVEL([Area].CurrentMember, 0) AND ISLEVEL([Entity].CurrentMember, 0) AND ISLEVEL([BusType].CurrentMember, 0) AND ISLEVEL([CostCenter].CurrentMember, 0)

                  THEN [LocalCur]/([Actual],[&FlshPYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                   

                  END

                   

                  The above formula does level 0 calculations but not able to aggregate using sum and crossjoin in else part.

                   

                  Thanks

                  Srinivas

                  • 6. Re: Help with ASO formula calculated values aggregation to Parent members
                    TimG

                    Yes, I understand that users will retrieve at all levels in those dimensions.  But your formula probably doesn't have to include BusType, Segmnt, Brand, Area etc - because there is no reason you can't let Essbase sum up the local currency in those dimensions 'naturally' (with the outline) before performing the currency calculation.  No need to write code to do what ASO already wants to do for you!

                     

                    Did you look at the sample code Gary posted?  You don't seem to have actually tried it - you mention 'not able to aggregate using sum and crossjoin in else part' but you don't have an ELSE or a SUM or a CROSSJOIN in the code you've posted.

                    • 7. Re: Help with ASO formula calculated values aggregation to Parent members
                      Srinivas Madderla

                      Tim,

                       

                      Sorry, I just pasted the formula which was working and currently working on aggregation part.

                       

                      Here is the else part which I am testing which is throwing error.

                       

                      ELSE

                       

                      SUM(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({Descendants([Entity].CurrentMember, 10, LEAVES)},{Descendants([Area].CurrentMember, 10, LEAVES)}),{Descendants([CostCenter].CurrentMember, 10, LEAVES)}),{Descendants([Brand].CurrentMember, 10, LEAVES)}0,{Descendants([Segmnt].CurrentMember, 10, LEAVES)}0,{Descendants([BusType].CurrentMember, 10, LEAVES)})))))

                       

                      error:

                      Error(1260052) - Syntax error in input MDX query on line 16 at token ',' USDConvYTD

                      which is in else part.

                       

                      I

                      • 8. Re: Help with ASO formula calculated values aggregation to Parent members
                        DanPressman

                        You method of using sum*crossjoin(crossjoin ... is not ASO - it is programming.  Your cube will be enormously slow.

                         

                        You need to listen to the advice given above.

                         

                        REMOVE ALL CROSSJOINS

                         

                        Remove all code with "WHEN IS([Scenario].CurrentMember,[Actual]) AND IS([Period].CurrentMember,[&FlshPYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL"

                         

                        If your fx rate is constant for all months of the year your life is very simple: you can multiply after all rollups.

                         

                        Add a currency member called Converted_to_USD = fx rate for Curr(x) * Curr(x)

                         

                        Make sure the solve order for this member is LOWER than all other solve orders.

                         

                        If you have a classic (unrecommended) formula for YTD then the above will work even if you differing rates per month.

                         


                        • 9. Re: Help with ASO formula calculated values aggregation to Parent members
                          TimG

                          Dan - thought this question would be up your street.

                           

                          The only dimension (besides Time) that really poses is a problem is Entity, assuming that all 'local' currency values are loaded to the same input member and the converted-to-USD total must be summed.

                           

                          Although that could be fixed by having one input member per local currency vs using an MDX SUM.  This may be the solution you're already proposing; I'm not sure.

                          • 10. Re: Help with ASO formula calculated values aggregation to Parent members
                            DanPressman

                            Right - I forgot about the typical BSO/planning design of having all currencies in one column.  That is really a leftover from BSO where it was important to keep dimensionality down (ie potential blocks) in order to make performance fast.

                             

                            In ASO having a lot of extra members and having dimensional irrelevance does not hurt performance very much it does raise the length of the bitmap identifying each data point but it does not increase the number of data points or as it did in BSO the number of blocks.

                             

                            Therefore what I do is I build a currency dimension more or les as follows:

                             

                            Currency (label Only) (Dynamic)

                                 Local-As-Entered-Currencies (label Only)

                                      curr1

                                      curr2

                                      curr3

                                      ...

                                 Converted Currencies(label Only)

                                      ConvToCurr1 Solve order=10

                                           curr1 No formula needed) Solve order=10

                                           curr2_as_Curr1   = Curr2 * curr2_to_curr1_rate Solve order=10

                                           curr3_as_Curr1   = Curr3 * curr3_to_curr1_rate Solve order=10

                                           ..._as_Curr1        = Curr?? * curr??_to_curr1_rate Solve order=10

                                      ConvToCurr2 Solve order=10

                                           curr2 No formula needed) Solve order=10

                                           curr1_as_Curr2   = Curr1 * curr1_to_curr2_rate Solve order=10

                                           curr3_as_Curr1   = Curr3 * curr3_to_curr2_rate Solve order=10

                                           ..._as_Curr1        = Curr?? * curr??_to_curr2_rate Solve order=10

                                      ConvToCurr3 ... Solve order=10

                             

                            So now all stored hierarchies for the other dimensions roll up in thei local currencies first  Then when all of that is done there is just a small amount of mdx to convert the rolled up all other dimensions - no crossjoins needed.

                             

                            If you have rates that are constant for the entire year you can even use my "stacked" YTD stored hierarchy system.  If not Be sure that the YTD has a HIGHER solve order than the formulas and rollups in the above  (ie greater than 10)

                             

                             

                            Srinivas - I really hope you take this advice because you will eventually get your solution to work but it will be terribly slow.  It does mean you are going to have to change how you load the data to your cube.

                            • 11. Re: Help with ASO formula calculated values aggregation to Parent members
                              AmarnathK

                              DanPressman has already gave you the solution. But one think i still don't understand is

                               

                              As we are using just LocalCurr and USD in currency dimension, we are trying to load FX Rates at different Company codes with different FX rates.

                              Can you put a sample set of records of how you are loading FX Rates.

                               

                              Amarnath

                              http://amarnath-essbase-blog.blogspot.com

                              • 12. Re: Help with ASO formula calculated values aggregation to Parent members
                                Srinivas Madderla

                                Hi All,

                                 

                                Thanks to all for your valuable suggestions.

                                 

                                The below formula seems to be working for me. I am still testing the data.

                                USDConvYTD=

                                 

                                CASE

                                WHEN IS([Scenario].CurrentMember,[Actual]) AND IS([Period].CurrentMember,[&FlshCYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Timeview].CurrentMember,[Flash View]) AND IS([Account].CurrentMember,[EST_TotNS]) AND IS([DataType].CurrentMember,[Flash_Inp]) AND IS([Dataview].CurrentMember,[Amount]) AND ISLEVEL([Entity].CurrentMember, 0)

                                THEN ([HE_NS_OTHER],[LocalCur])/([Actual],[&FlshCYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                                 

                                 

                                WHEN IS([Scenario].CurrentMember,[&FRSPE]) AND IS([Period].CurrentMember,[&FlshCYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL([Entity].CurrentMember, 0)

                                THEN [LocalCur]/([&FRSPE],[&FlshCYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                                 

                                 

                                WHEN IS([Scenario].CurrentMember,[Plan.Final]) AND IS([Period].CurrentMember,[&FlshCYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL([Entity].CurrentMember, 0)

                                THEN [LocalCur]/([Plan.Final],[&FlshCYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                                 

                                 

                                WHEN IS([Scenario].CurrentMember,[Actual]) AND IS([Period].CurrentMember,[&FlshPYMTH]) AND IS([Currency].CurrentMember,[LocalCur]) AND IS([Dataview].CurrentMember,[Amount]) AND IS([Timeview].CurrentMember,[YTD As Of]) AND ISLEVEL([Entity].CurrentMember, 0)

                                THEN [LocalCur]/([Actual],[&FlshPYMTH],[LocalCur],[Flash_Inp],[Amount],[Flash View],[CC Unspecified],[DC Unspecified],[SE Unspecified],[BR Unspecified],[AR Unspecified],[Flsh_Rate]).value

                                 

                                ELSE

                                SUM(CHILDREN([Entity].CurrentMember),[USDConvYTD])

                                END

                                 

                                FX Rate load member combination.

                                [month]

                                Level 0 EntityBR UnspecifiedAR UnspecifiedDC UnspecifiedSE UnspecifiedCC UnspecifiedActualAmountFlash View     Flash_InpLocalCurFlsh_Rate

                                 

                                Thanks

                                Srinivas

                                • 13. Re: Help with ASO formula calculated values aggregation to Parent members
                                  DanPressman

                                  At least change the SUM(CHILDREN([Entity].CurrentMember),[USDConvYTD]) to just   USDConvYTD

                                   

                                  But I do not think that this will work at upper levels of entity or other dimensions only at ISLEVEL([Entity].CurrentMember, 0) which was your original problem