11 Replies Latest reply: Nov 3, 2013 8:33 PM by Anand N. RSS

    TB First not fetching correct values

    908557

      Hi All,

       

      I am encountering a problem while using a account member with TB First property. For your reference i have the data set below:-

       

       

      Unit1Unit1Unit1Unit1Unit1Unit1Unit1Unit1Unit1Unit1
      DC1DC1DC1DC1DC1DC1DC1DC1DC1DC1
      BU1BU1BU1BU1BU1BU1BU1BU1BU1BU1
      Scenario1Scenario1Scenario1Scenario1Scenario1Scenario1Scenario1Scenario1Scenario1Scenario1
      Act1Act1Act1Act1Act1Act1Act1Act1Act1Act1
      123456789Y-T-D(09)
      Product1Custom1134115580000000#Missing
      Product2Custom1242607934260793426079342607934307951417138241713824274650#Missing
      Product3Custom134877877648855924490715452695019231509395153636301538558417773170#Missing
      Product4Custom14432231.3432231.3432231.300000#Missing
      Product5Custom154.27E+084.27E+084.23E+0817082742162419021734719647537774753537#Missing
      Product6Custom164.55E+084.55E+083.9E+084.19E+084.16E+083.4E+083.43E+083.42E+08#Missing
      Product7Custom177.9E+087.9E+087.91E+088.23E+088.22E+088.16E+087.81E+087.79E+08#Missing
      Product8Custom183.36E+083.36E+083.3E+083.31E+083.22E+082.89E+082.91E+082.9E+08#Missing
      Product9Custom196.71E+086.69E+086.71E+086.64E+086.61E+086.55E+086.44E+086.41E+08#Missing
      Product10Custom1106.08E+086.08E+086.08E+086.4E+086.39E+086.34E+086.41E+086.38E+08#Missing
      Product11Custom1114.93E+084.93E+084.94E+085.16E+085.16E+085.13E+085.18E+085.16E+08#Missing
      Product12Custom1126.04E+086.04E+086.05E+086.34E+086.36E+086.3E+086.36E+086.34E+08#Missing
      Product13Custom1134.01E+084.01E+084.02E+084.07E+084.06E+084.03E+084.05E+084.08E+08#Missing
      Product14Custom1#Missing04.02E+084.01E+084.08E+084.06E+084.02E+084.03E+084.03E+08#Missing
      Product15Custom1#Missing#Missing#Missing#Missing#Missing#Missing4.98E+085.03E+085.01E+08#Missing

       

       

       

      The last column as you can see has #missing. The account "Account1" has property set to TB First and Skip None,Addition and Never Share. The account does not have any child members.

      The cube i am working on is BSO.

       

      Thanks and Regards,

      Kartik Gupta

        • 1. Re: TB First not fetching correct values
          955124

          You are able to see Data for the Account mentioned , so its not an issue with the TB property you mentioned . As I see , in the last column you are trying to fetch YTD data.

          Just make sure if your cube is set for YTD aggregation. For that , Open outline go to period property and check its properties. Also if there are any formulas concerned with these retrievals , make sure they work for YTD . Else no way you can expect the data to roll up for Y-T-D.

          • 2. Re: TB First not fetching correct values
            SreekumarHariharan

            Can you get more info about Time balance First in the below url

            Oracle Essbase Database Administrator's Guide & go to example provided for Time Balance First

             

            Thanks,

            Sreekumar Hariharan

            • 3. Re: TB First not fetching correct values
              Evgeniy.Rasyuk

              If work with Dynamic Calc members try this solution

                 -> use TwoPass calculation option  on member properties.

              • 4. Re: TB First not fetching correct values
                908557

                Hi Evgeniy,

                 

                Had already tried the Two pass calc. Its not working.

                 

                Thanks

                • 5. Re: TB First not fetching correct values
                  SreekumarHariharan

                  You can use the MDX formula using Openingperiod () function to achieve the tbfirst value for account "nf400210020".The OpeningPeriod() function fetches a FirstDescendant and it can be applied and works on any dimension.

                   

                  For E.G

                   

                  WHEN IsGeneration ( [Account].CurrentMember,3 ) THEN

                  (["nf400210020"],

                  OpeningPeriod (

                  [Account].Levels(0),

                  [Account].CurrentMember

                  )

                  )

                   

                  Thanks,

                  Sreekumar Hariharan

                  • 6. Re: TB First not fetching correct values
                    CL

                    I took a look at Sample.Basic and see that TBFirst works just fine with Dynamic Time Series:

                        Market    Actual
                       Product   
                    Opening Inventory          Jan    117405   
                    Opening Inventory          Feb    116434   
                    Opening Inventory          Mar    115558   
                    Opening Inventory     Qtr1    117405   
                    Opening Inventory     Qtr2    119143   
                    Opening Inventory          Jul    143458   
                    Opening Inventory          Aug    142587   
                    Opening Inventory          Sep    142215   
                    Opening Inventory     Qtr3    143458   
                    Opening Inventory     Qtr4    141850   

                    Opening Inventory    Year    117405   

                    Opening Inventory    H-T-D(Jan)    117405   

                    Opening Inventory    H-T-D(Feb)    117405   

                    Opening Inventory    H-T-D(Mar)    117405   

                    Opening Inventory    H-T-D(Apr)    117405   

                    Opening Inventory    H-T-D(Sep)    117405   

                    Opening Inventory    H-T-D(Dec)    117405   

                     

                     

                    If you go to that application, can you get the same results?

                     

                    What happens with a non-TBFirst member in your database?  Does it add up?

                     

                    I'm wondering if your Y-T-D DTS is maybe not pointing to the right generation.

                     

                    Regards,


                    Cameron Lackpour

                    • 7. Re: TB First not fetching correct values
                      Evgeniy.Rasyuk

                      Can you show doxumentation when aso stat support dts.?

                      • 8. Re: TB First not fetching correct values
                        CL

                        Evgeniy,

                         

                        Who's talking about ASO?

                         

                        Per the OP:

                        >>The cube i am working on is BSO.

                         

                        Regards,

                         

                        Cameron Lackpour

                        • 9. Re: TB First not fetching correct values
                          Anand N.

                          Is your "Y-T-D(09)" a dyn-calc member with a member formula by any chance?

                          • 10. Re: TB First not fetching correct values
                            CL

                            >>Is your "Y-T-D(09)" a dyn-calc member with a member formula by any chance?

                            ^^^Pretty sure any time you see a Y-T-D(periodname) you're looking at Dynamic Time Series.  And yes, that is a dynamic calc, but one that is supported natively in BSO Essbase and handles time balance automatically.


                            Regards,


                            Cameron Lackpour

                            • 11. Re: TB First not fetching correct values
                              Anand N.

                              I don't recall why it was setup this way originally, but we actually do not use the native Dynamic Time Series functionality in our BSO cubes. We use a member formula instead ("@PTD (Jan:Dec)" for YTD-Dec, etc.). If that's how the OP has his YTD implemented, then he might want to check his member formula for the YTD members..