This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Aug 1, 2013 4:51 PM by Daniel Willis RSS

Sending data to Essbase BSO

user3055639 Newbie
Currently Being Moderated

Good afternoon

Our group currently uses ASO essbase cubes to do their planning and forecasting.  The data bases are primarily meant to be data warehouses, users just want to be able to send their data and have it consolidated on the fly as they do their retrieves.  They do not want to have to perform database calculations (we had BSO cubes years ago, but the calc times were too much - thats why we went to ASO).  This works well, except that as you all know, ASO sends are notoriously slow - this is getting to be a bit of a problem.  Now, we recently moved to the latest version of Essbase/ Smartview, and I was told that if you partition a BSO cube to an ASO cube, you could send data to the BSO and through a transparent partition access it through ASO and the ASO would aggregate the child values in the BSO to parent values in ASO when users do their retrieves.  Is this true?  if so, this may be a game changer for us.  I tried doing this before, but always had to calc the BSO cube first.  Can someone verify this for me?

 

Thanks and sorry for being long winded

  • 1. Re: Sending data to Essbase BSO
    Evgeniy.Rasyuk Journeyer
    Currently Being Moderated

    You task is'not new. My company use this decision some years )

       If you don't believe - just search in google this words "aso bso transparent partition"

       And Oracle recommend this decision for folk        Creating Partitioned ASO Reporting Cubes in Essbase 11.1.2

     

     

    All people, whose start to work with essbase, create similar error. (and this was my way too)  I think you need stop developing and learn knowledge from best practices.

     

        1)  First of all BSO - for planning and forecasting. ASO for reporting. You can mix ASO and BSO with transparent partition.

        2)  BSO may work very slowly, but it's not problem if you know how EssBase work - you can extremally speed up calculation.

     

    p.s. You can use BSO for user data inputting. You don't need DWH for this task

    ER

  • 2. Re: Sending data to Essbase BSO
    Evgeniy.Rasyuk Journeyer
    Currently Being Moderated

    >> I tried doing this before, but always had to calc the BSO cube first.

      I think you create partition with all levels from BSO. It's wrong. You need map only LEVEL 0 members.

  • 3. Re: Sending data to Essbase BSO
    user3055639 Newbie
    Currently Being Moderated

    thanks for the reply, I know that BSO is for planning and forecasting and  ASO for reporting.  My problem is that my users will not accept calc times any greater than 5 minutes.  that is why we exclusively use ASO sends.  However, I had heard that with version 11.1.2 you could have a transparent partition that would read child members from a BSO and aggregate them on the fly in an ASO cube.  I just would like to know if this is indeed the case, or do you still have to run a calc on the BSO first

  • 4. Re: Sending data to Essbase BSO
    Evgeniy.Rasyuk Journeyer
    Currently Being Moderated

    >> I just would like to know if this is indeed the case, or do you still have to run a calc on the BSO first

    >  I think you create partition with all levels from BSO. It's wrong. You need map only LEVEL 0 members.

    NO  calculation needed.

     

     


    ER

  • 5. Re: Sending data to Essbase BSO
    user3055639 Newbie
    Currently Being Moderated

    wow, so then what you are telling me is that my users can send data to a BSO cube and can aggregate it on the fly when retrieving from an ASO cube? (that is connected through a transparent partition)  With no calculation of the BSO cube required?  Sorry, but this is a game changer for us if it works.  I was not able to get this to work in the past

  • 6. Re: Sending data to Essbase BSO
    Evgeniy.Rasyuk Journeyer
    Currently Being Moderated

       You can try it in Development ENV. )

  • 7. Re: Sending data to Essbase BSO
    CL Oracle ACE Director
    Currently Being Moderated

    If you are having performance problems sending data directly to an ASO database, why do you think that it will necessarily be faster to first send it to a BSO database and then have a transparent partition send data from that database to the target ASO database?  It may work out that way, but I wouldn't count on it -- purely from a "how many hops does this number have to make" perspective, it would seem to be a slower approach.  Maybe there is slice magic in a partition? 

     

    I would also wonder about how you are going to present the results -- users send on Sheet1 to BSO and only retrieve on Sheet2 against ASO?  How do you reflect changes to the BSO retrieve sheet on the ASO side?  Or don't you worry about it?

     

    Not that I am an expert on much of anything ASO (but the good news is that this board is full of people who are), but wouldn't it make more sense to try to tune the ASO database for better send performance?  Managing slices, changing dimensionality, handling where temporary tablespaces are stored, etc. would be where I would look first.  Have you done all that and then decided that ASO isn't for you?  Did you look into periodically merging slices as per your other thread sort of on this subject?  Smartview questions

     

    Btw, I do get the point about ASO potentially being slow if you had tons of people sending data in and ASO automatically merging the slices -- BSO simply doesn't have any of that although of course you have to deal with fragmentation of the blocks and the calculation issue.

     

    Regards,

     

    Cameron Lackpour

  • 8. Re: Sending data to Essbase BSO
    GlennS_3 Oracle ACE Director
    Currently Being Moderated

    Cameron,

    I've not stalked you in a long time.

    From a pure submission point of view, BSO is MUCH faster than ASO and I'm not sure based on the architecture you can tune that aspect much. I think It has to do with the way the slices get created and creates the offsetting amounts from the original.

     

    Original Poster,

    It is possible using a transparent partition on Level 0 data in the BSO and letting ASO aggregate it, it could give you the funcationality, but remember, depending on the size of the BSO DB (number of level 0 intersections) it could have to read a lot of blocks to be able to get the required data.  As someone said, the best thing to do is to test it in your environment

     

    ER,

    While I like your answers the two statements you make about BSO for Planning and ASO for reporting & BSO can be slow but is not a problem if you know how Essbase works I can disagree with.

    look at 11.1.2.3 with an ASO Plan type. There is a need for it becasue BSO can be slow and depending on the database may not EVER be adaquate for some DBs. This is why it is now included.

     

    I'm looking forward to a "rumored" new BSO cube with ASO upper levels.


  • 9. Re: Sending data to Essbase BSO
    CL Oracle ACE Director
    Currently Being Moderated

    Glenn,

     

    You can stalk me (intellectually only, please) any time. 

     

    I'm not arguing the point about BSO being faster than ASO wrt submits.  What I am wondering about is -- if the goal is to enter data and then report on it instantaneously (or pretty darn close to instantaneously cf. the OP's comment about performance being slow -- that isn't the hallmark of users who wait for results), I can't see how a transparent partition makes this any faster.  The BSO database is going to send data (is Essbase more efficient in its sends from BSO to ASO than when a human sends directly to ASO?) to ASO.  ASO still needs to manage slices on retrieval.  How is that faster?

     

    There are also interface questions -- how do you seamlessly manage submits to BSO and retrieves from ASO?  Or is that something that gets sold up front to the actual business users?  I've worked on a project that "magically" pushed BSO data to ASO via a CDF and a mildly convoluted MaxL script and then pulled it back into the BSO cube via an ASO to BSO transparent partition above level zero -- it didn't work very well.  But at least they tried to solve the issue of "I enter data on this sheet and I want to see the results on this sheet".  Or do you handle it in the front end -- my favorite tool, spelt D-o-d-e-c-a, could do this with a moderate amount of pain -- other tools would be, I suspect rather more code intensive.

     

    All interesting stuff btw (at least to me but I suspect just about everyone else).  I'm curious to hear if you have workarounds/Oh-Cameron-the-error-of-your-ways-are-manifold comments. 

     

     

    Regards,

     

    Cameron Lackpour

  • 10. Re: Sending data to Essbase BSO
    GlennS_3 Oracle ACE Director
    Currently Being Moderated

    Oh Cameron the error of you way is to listen to me. I was actually going to put in the comment you make about adding complexity to the equation by having to send to one database but report from another especially since prior to 11.1.2.3 you would not be able to see the ASO cube from within planning. you would have to use an Essbase connection in Smart View to get there. It certainly convolutes the environment.

     

    As I also sort of said is every environment is differenent. for the OP's case, he needs to test what his users do and see what is the faster of the two. There are some efficiencies in partitions to bring in chunks of data. The larger the amount of data the users send, the closer the two could be to being equal or one outshining the other.

  • 11. Re: Sending data to Essbase BSO
    Celvin Kattookaran Oracle ACE
    Currently Being Moderated

    Both big brothers arguing (I don't think so) on a thread. OP you should benefit from this scenario

     

    GlennS_3 hope to see that rumor getting materialized. We'll have another thread on that once it comes to Essbase.

     

    Regards

     

     

    Celvin

    http://www.orahyplabs.com

  • 12. Re: Sending data to Essbase BSO
    CL Oracle ACE Director
    Currently Being Moderated

    >>especially since prior to 11.1.2.3 you would not be able to see the ASO cube from within planning. you would have to use an Essbase connection in Smart View to get there.

    ^^^The OP doesn't use the product Planning, he just uses Essbase to do planning-with-a-lower-case-p.  Or at least that's what I got from the OP's OP.

     

    I am curious to see how, if at all, Oracle has solved the issue of sends to ASO plan types.  Also, can 11.1.2.3 ASO Plan Types use Calculation Manager to run rules?  I think the answer is "No" but I have to wonder how long that will last as it is an obvious fit.  Or maybe it's already there and I missed it?


    Regards,

     

    Cameron Lackpour

  • 13. Re: Sending data to Essbase BSO
    user3055639 Newbie
    Currently Being Moderated

    Wow

    opened up a real can of worms here didn't I?  Everyone, thanks for your input.  Just an FYI, I built 2 small BSO and ASO cubes and partitioned them using a transparent partition.  I could not get the BSO data to aggregate on the fly after I logged into the ASO cube to perform the retrieve.  I am only using essbase with a smartview connection.  But your comments raise other valuable points.  If we could get it working, would the retrieval time slow down???  I have over 150 planners, if half of them are performing sends and retrieves at the same time, any benefits with sending data to BSO could be negated by the poor retrieval times

  • 14. Re: Sending data to Essbase BSO
    GlennS_3 Oracle ACE Director
    Currently Being Moderated

    Celvin,

    We are not arguing, Cameron makes statements and I correct them. (not really, I just like to have fun at his expense).

    Cameron,

    I've not tried it yet, but I believe you can not execute a rule from a web form according to the documentation, But since ASO supports procedural calculations and allocations from Calc Manager and from MaxL, I'm gurssing you could still run those(the calc manager as part of a task list perhaps)

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points