Planning

Get Involved. Join the Conversation.

Topic

    Greg Olma
    How to Move ASO Data to a BSO Cube
    Topic posted June 12, 2018 by Greg OlmaRed Ribbon: 250+ Points, tagged Financial Planning, Integration, PBCS, Planning, Reports 
    1321 Views, 10 Comments
    Title:
    How to Move ASO Data to a BSO Cube
    Summary:
    From an architectural perspective and best practices, BSO cubes are usually used for modeling input data using complex calculations and business logic for producing output data.
    Content:

    From an architectural perspective and best practices, BSO cubes are usually used for modeling input data using complex calculations and business logic for producing output data. From there, the data is moved to the ASO cube and aggregated for the most efficient reporting and usability possible. ASO cubes are aggregated in a bottom up manner with much more efficient aggregation times and thus are better suited to hold much larger amounts of data. Although possible, it is not recommended to model with your ASO cube as that will significantly deteriorate the performance of your cube.

    After the outputs of the BSO cube have been calculated, we usually move that data to our reporting cube and run our reporting from there. What happens if we are loading our actual data to the ASO cube and want to move that data directly to the BSO cube for modeling?

    There are several ways that we can do that of which we can explore a new cloud PBCS feature that was announced in the March update. I will start with that.

    Export All Zero Level Data from ASO Cube and Load into BSO Cube

    The first step to acquiring the data file needed is to open the calculation manager from the navigator by clicking on “Rules”.

    bso%20to%20aso_01.png

    From here navigate to your planning database and then select database properties and expand your ASO cube.

    bso%20to%20aso_02.png

    You will notice the new export and import zero level data in the options as shown below.

    bso%20to%20aso_03.png

    It is very simple as it will ask you for the file name and export it to your Inbox/Outbox directory as shown below.

    bso%20to%20aso_04.png

    To access the Inbox/ Outbox go to the navigator and then click on Overview, Actions, Inbox/Outbox Explorer.

    bso%20to%20aso_05-2.png

    bso%20to%20aso_06.png

    From here you can download that data and load through a data import option or use Data Management to complete that task.

    Calculation Manager BSO @XREF Function

    We can always leverage calculation manager from the BSO’s side to pull in data from the ASO cube. The @XREF function can be used for that task.

    @XREF (locationAlias [, mbrList])

    For example:

    After putting in the corresponding Fix statement you can pull in all January data using the following:

    Jan = @XREF(sourceDB,January);
    

    For more information about the @XREF function please refer to the following link:

    https://docs.oracle.com/cd/E57185_01/ESBTR/xref.html

    Data Sync Using Data Management

    For the fastest option to move data from one cube to another, data sync in data management is your best bet. Not only can you filter exactly what you want to move from one cube to another, but you can also map that data if there are differences in the naming of your members on both sides or if there is simply a one-to-many or many-to-one relationship that exists in your data movements. For more information regarding data sync please refer to the following blog written by Jean Luc Mosley.

    http://www.keyperformanceideas.com/how-to-easily-manage-your-data-using-data-synchronization-in-hyperion-planning-and-pbcs

    Blog post by Yousef Saead of Key Performance Ideas.

    Comment

     

    • Anthony Manfredi

      The extract from ASO in Calc manager is in Essbase format, not in column format so it will be a challenge to load it through DM.  It may work in planning import if the dimensions are exact.  Also extracting from ASO is slow because the queries span all potential intersections and not only where the data exists.  Since I wrote my blog on the extract, I have been informed that something is on the horizon that will assist with extracting from ASO.  In the meantime, I use BSO cubes to support applications that need extracts instead of using ASO as a source plan type.  Hopefully, that will change soon.

    • Gerd Pranoto

      I have had success with moving ASO to BSO using groovy script.  The concept is that you extract a subset of data from ASO to a Data Grid for reading and open another Data Grid in the BSO for writing. It's really good performance-wise.  The only issue that I'm having is the limitation of 500000 cell count, similar to Smart Push.

      • Anthony Manfredi

        Yeah, that is good stuff and really good for webform movement.  I would be hesitant to use that as a solution for complete cube moves or larger data sets.  

        • Gerd Pranoto

          That's exactly what I'm having challenges with right now.  Query performance is really good but it struggles when trying to save the grid on the target cube.  I've been using the DataGridBuilder class to write the results.  Performance improves a bit when I'm not using the Status object.  Do you know of other alternatives?

          • Anthony Manfredi

            Sorry, I usually stay away from ASO as a source because of this.  I would suggest that you try to break it up into logical parts to try to transfer the data. 

             

            • Gerd Pranoto
              Yes I found this the hard way. Data Management is the best way to go as it now supports improved ASO export. So I guess I will have to find an excuse to write Groovy scripts another day
    • Tino Tarantino

      I have a requirement to source data from an ASO cube and tried using Data Management as suggested in this thread.   I ran into a pretty severe system limitation when I tried this approach.   In my case, I have  multiple alternate hierarchies across multiple dimensions in my ASO cube.     To avoid duplicates,  shared members need to be excluded in an MDX query.  However,  the MDX generated by Data Management makes no attempt to eliminate such duplicates which makes using Data Management to extract data from an ASO source useless in my case.     This issue with MDX is long outstanding issue.   See Joe Watkins workaround at: https://www.tapatalk.com/groups/essbase/removing-shared-members-in-mdx-t15110.html for more detail.  Unfortunately,  Joe's workaround assumes you have control over the MDX that is generated, which is not the case with Data Management.

      • Tino Tarantino
        After a little more research, it turn's out there is an easy workaround to the "duplicates" issue I mentioned in my earlier post.
        Turn's you you can define additional filtering criteria in Data Management to eliminate duplicates created by shared members.
         
        For example, if you have two alternate hierarchies on the Account dimension  where the topmost parents are: Primary_Hier_1 and Alt_Hier_1, 
        use the following Account filter to eliminate duplicates: @Lvl0Descendants("Primary_Hier_1").  Apply a similar filter to other dimensions which may also have alternate hierarchies defined.
    • Kyle Goodfriend

      Using MDX via Maxl has given me great results exporting ASO.

      • Kyle Goodfriend

        There are also some new suppressions settings in the gridbuilder that greatly improve performance in many conditions.  I am moving data at the bottom of 9 dimensions at a client, two have more than 70k members, and it is about 45 seconds.