6 Replies Latest reply: May 16, 2014 6:11 AM by AmarnathK RSS

    ASO - MDX member formula

    994472

      Dears,

       

      In my current requirement that i cam across , I have applied the below member formula to ASO Dynamic dimension.

       

      VC_YTD -- (SUM(PeriodsToDate([Period].Generations(2), [Period].CurrentMember), [View].[VariableCost]))

       

      FC_YTD - (SUM(PeriodsToDate([Period].Generations(2), [Period].CurrentMember), [View].[FixedCost]))

       

      FixedCost - SUM({DESCENDANTS([Custom1].CURRENTMEMBER,10,LEAVES)},[FC_FIS])

      VariableCost - SUM({DESCENDANTS([Custom1].CURRENTMEMBER,10,LEAVES)},[VC_FIS])

       

      FC_FIS --- SUM({DESCENDANTS([Custom1].CURRENTMEMBER,10,LEAVES)},[VC_FIS])


      VC_FIS -

      CASE WHEN  IsLevel([Account].CurrentMember,0) THEN

      IIf(IsAncestor([A_4000000], [Account].CurrentMember),  (([MTD] * ([BegBalance],[NoLocation],[NoCostCenter],[NoProduct],[UserInput],[Budget],[Approved],[Local],[No Entity],[FY14],[MTD]))/100),0)

      else

      SUM({DESCENDANTS([Account].CURRENTMEMBER,10,LEAVES)},[FC_FIS])

      End

       

      I know thats not the right way but due to requiremnet i have to do the same.

      Now when i retriev the member in excel , my excel is dying up and it is not giving result.

       

      In the logs i am getting below errors---

      The formula for member [FixedCost] is Complex. If possible, add a non-empty directive to optimize for sparse data.

      The formula for member [VariableCost] is Complex. If possible, add a non-empty directive to optimize for sparse data.

      The formula for member [VC_FIS] is Complex. If possible, add a non-empty directive to optimize for sparse data.

       

       

      Can you suggest me on to this.

       

      Thanks.

        • 1. Re: ASO - MDX member formula
          KKT

          try to  use NONEMPTYSUBSET()

          "This function can help optimize queries that are based on a large set for which the set of nonempty combinations is known to be small. NonEmptySubset reduces the size of the set in the presense of a metric; for example, you might request the nonempty subset of descendants for specific Units.

          NonEmptySubset is used to reduce the size of a set before a subsequent analytical retrieval."

          The thing is I once had that message and used NONEMPTYSUBSET and the warning didn't go away so I wound up just ignoring it. Some problems are just complicated.

          • 2. Re: ASO - MDX member formula
            994472

            Thanks for the update , can i have some more suggestions.

             

            Thanks

            • 3. Re: ASO - MDX member formula
              TimG

              994472 wrote:

               

              I know thats not the right way but due to requiremnet i have to do the same.

              Now when i retriev the member in excel , my excel is dying up and it is not giving result.

               

               

              As KKT says, as far as the warning (it's not an 'error') goes, sometimes you just have to live with it.

               

              Why does your requirement force you to do this the 'wrong way'?

               

              As you probably know, this method for forcing roll-up of dynamic members (an IF switch on level with a SUM function at non-level-zero) is notoriously slow.  Multiple layers of complex MDX (formula members calling formula members) likewise.

               

              Assuming you're interested in changing the technique if it gives the same results, can you explain the requirement in more detail?

              • 4. Re: ASO - MDX member formula
                KKT

                Hi,

                 

                Agree with Tim can you please elaborate what you are trying to acheive as the member formula (mdx) you are trying to apply on member will slow down the performance.

                 

                can you explain some more details so all of us can help you better.

                 

                Thanks.

                • 5. Re: ASO - MDX member formula
                  994472

                  Thanks KKT and TIM.

                   

                  I tried with NONEMPTY TUPLE function with different hit and trail and formula.

                  will keep you updated.

                   

                  Thanks.

                  • 6. Re: ASO - MDX member formula
                    AmarnathK

                    I agree with TimG and others. ASO is meant for aggregations and you are making it slower by enforcing it to aggregate explicity by using a formula...

                     

                    Will 11.1.2.2, you can always use procedural calcs to calculate at base level and then leave it to aggregate at higher level..

                     

                    regards

                    Amarnath

                    ORACLE | Essbase