3 Replies Latest reply on Nov 7, 2017 2:12 PM by USER1211

    Sparse database best aggregation strategies?

    Robert Angel

      Hi,

       

       

      Say you have a database with 2 dense dimensions with middling block size and 5 sparse dimensions with hierarchies that need aggregating, which between them will easily take Essbase into the second 2^52 group, though being a low way shy of the second limit. Data is spread very sparsely across the dimensions with less than 0.09% of the potential sparse combinations populated.

       

      Also imagine that the lions share of its data will be loaded from other systems, so form based aggregation strategies are not useful to you, and external pre-aggregated data is also not possible.

       

      Also assume dimensional ordering is optimal.

       

      Are there any useful strategies that can be employed to minimise the aggregation times in PBCS.

       

       

      thanks for your input,

       

      Robert.

        • 1. Re: Sparse database best aggregation strategies?
          USER1211

          Alot of factors which are not known(e.g like how long is the aggregation taking today....) hence as you know it still goes back to you to perform all your due diligence and detailed testing. There are several articles/blogs from folks like Cameron L etc on Essbase aggregation strategies you can Google an see if it helps.

           

          Since you are on cloud why not use an ASO cube and that way it is auto aggregated.

          • 2. Re: Sparse database best aggregation strategies?
            Robert Angel

            Thanks, I have poured over Cameron's posts, but he seems to be mostly of the school use form strategies, and some I cannot currently see as the client in their wisdom blocks blogs...

             

            ASO is okay for just getting the figures, but sometimes I will (this is future tense, still at the drawing board - my question is based on a known DB which calculates in ~ 4 mins, but with the addition of two new dimensions which will add ~ 5000 and 150 initially, but with the first of the two adding ~ 2500 new members a year) need to calculate based on aggregate members, so this will not be a universal panacea.

             

             

            thanks for your input,

             

            Robert.

            • 3. Re: Sparse database best aggregation strategies?
              USER1211

              So there is  no an initial model built, thus it could end up that the aggregation impact goes from 4 to 14 or 4 to 40...etc

               

              The most reasonable point to strategize is after you have something(i.e a model) to test test test, as folks on the board will tell you Essbase is an art not a science hence you could spend days and weeks planning and still be completely off

               

              There are a few good Oracle forum articles(the belief is you already read the Essbase DBAG) on the board [Dynamic Integration for Multiple Hyperion Planning Applications] and [The Young Person's Guide To Essbase Cube Design] that you could read through to help you and your team out.

               

              Other members will certainly chime in with advice.

               

              Good luck and let us know how you make out in the end.

              1 person found this helpful