PBCS dynamic xref, ASO to BSO - roll your own hybrid!

Overview

PBCS has made life so easy when it comes to moving data between plan types.  Setting up a Planning to Essbase ASO reporting cube, on-premise, is a very involved process with automations to either export/import data or to move data via partitions.  With PBCS it's pretty much as easy as clicking a button in the UI or calling a data map definition in EPMAutomate.

A number of questions come up when determining how you want to architect your PBCS app.  You need to think about how data is going to be stored in the different plan types and how the data flow is going to work so that you get the results you need as quick as possible while ensuring the app is easy for users to navigate.  Features like Smart Push, which allow you to create data forms pointing to a BSO plan type and also write to an ASO plan type (or another BSO type if you wish) give you a lot of options to isolate your users from seeing what's going on behind the curtain.  I make it a selling point with my users that "you don't have to worry about how many cubes the app has, you should just see it as a single application".  This is very well received from users who have gotten used to having to enter data in one place and then run reports somewhere else.  Trying to accommodate user input requirements while not making them deal with the complexity of multiple cubes behind the scenes has been the driver behind this blog post.

I'm currently working on a project where we are moving an on-premise Hyperion Planning application to PBCS.  We recently came up against a challenge and it took a little thought and research to get it resolved.  The requirement from the users is not that complex, they want to be able to enter adjustments in a form to upper level (aggregated) members.  From an end user perspective this should be pretty easy, but for those of you who have dealt with something similar in PBCS, using both BSO and ASO plan types, I imagine the wheels are already turning in your head with some thoughts about how to make it work, and why it may be a challenge.

First please allow me to lay out some overall architecture decisions that were made for this project that will help explain why there are restricted ways to solve the requirement.  Right, wrong, or indifferent, we make certain design choices and then have to deal with them.  You hope that your decisions solve the bulk of the requirements and you minimize the work arounds.  Sometimes you have to reevaluate your overall design, but you can't change your design every time a new requirement comes up.

Design decision #1: All data will be loaded into the primary BSO plan type

Most developers I've discussed this with agree.  While you can load directly to an ASO plan type, there are a number of things to consider and see as a trade-off.  One particular issue is how data gets exported back out of the app.  Options for pulling data from ASO plan types are limited.  I'm waiting for Oracle to expose the ASO plan-type to MDX queries like they do in PCMCS; I'm told this is coming in the future, but not sure when it will be available.  In the mean time if you have a desire to get your data back out of PBCS, storing it all in ASO plan types is going to cause some pain.

Design decision #2: All data will be stored at level 0

In general this app is configured to be a bottom's up collection, but as mentioned earlier there is a requirement to enter adjustments at upper level entity members.  This is handled easily enough with a top down version for adjustments and a business rule that copies the upper level input to a leaf member on save.  So far so good!

Design decision #3: The BSO plan type will not be aggregated

This particular application has had a short lived run on-premise; it is a large BSO cube with long consolidation times due to it's sparse data set.  As users input their numbers during submission cycles, and aggregations are run, the cube fragments and consolidation times increase.  User experience is not great.  By moving the data from the BSO plan type to the ASO plan type with a data map and Smart Push users get instant aggregation.

So given these constraints, if we go back to our requirement we can see some challenges.  Again, the requirement is to be able to enter adjustments in a form to upper level (aggregated) members.  In order to be able to enter the data to an upper level member, the form has to be built on the BSO plan type; this is actually good because we do want our primary data storage to be the BSO cube.  I've already mentioned how we will use a business rule to copy the data to a leaf member after it is entered.  We can then leverage Smart Push to send the data into the ASO cube for reporting.  Overall this is not a big deal.  The problem occurs that when viewing the form, for upper level members, the users need to see aggregated values.  Remember Design decision #3, the BSO cube is not aggregated.  So if the user opens the form and they are looking at a lev1 Entity they aren't going to see any data, that lev1 member only has a value in the ASO cube where it is aggregated.  So my user's requirement is now expanded to say ability to enter adjustments in a form to upper level (aggregated) members AND see the aggregated values for the members on which the adjustments are to be based.  This is quite a bit different from the first statement and requires some work around.

Potential solutions for a work around that were discussed

1. Aggregate the data in BSO.  This is not a viable option, the whole point of going to PBCS, as I mentioned above, was to leverage the ASO plan type for this sparse data set.  Pre-aggregating the cube is not an option.
2. I briefly considered a composite form where the top was pointing to the ASO cube for aggregated values and the bottom was pointing to the BSO cube for input.  Again, kind of sloppy and I could see users getting frustrated if the form got large enough to scroll up/down or left/right.  While it might have been an option I considered some years ago, I knew we could do better with PBCS.
3. Enter the adjustments in the ASO plan type.  The problem here is the ASO cube will only allow the adjustment to be entered to a lev0 member, remember the requirement was an upper level member.  We discussed creating _input members for the users to enter the adjustments into; we actually have _input members as the target of the data copy after they enter the data (using a [MemberName]_input is a cleaner way to do this opposed to writing to the first level 0 descendant as I've seen done in some other apps).  The dissent on this approach is the form would have two lines for each record; a line with the aggregated (parent) value and another line for the lev0 _input member.  I was tempted to push back on the users for this one, it seemed like the easier way for us [IT] to deal with it and it would also eliminate the need to have to copy the member from the parent to the lev0 member.  However, if I was being honest with myself, it was sloppy to say the least.  It also introduced another problem; if we allowed users to enter adjustments in the ASO cube we would be violating Design decision #1, we want the BSO cube to be the source for data storage.  We can work around that by using data management to move the data back into BSO but that's more complex than the business rule approach I had before.  Another options is we could bring the data back into BSO using a business rule with an xref.....this got me thinking; if I could bring the adjustment back into BSO with a rule, why couldn't I just bring the aggregated value into BSO for the upper level members so I could keep the form pointing to the BSO cube?  I quickly realized I could not predict before the user opened the form what POV they were going to select and even if I could, they would likely change the POV to do various adjustments.  So pre-seeding the cube with stored aggregated values via a batch business rule wasn't going to work.  So what about bringing the values into the BSO cube dynamically?  Basically a transparent partition.

This third idea seemed to have some meat to it.  It would allow the user to enter in a single form while being able to see aggregated values, and they would be able to enter adjustments to those values on the same row.  They could navigate to other POVs and get the results they need with good performance and when the user hit save the upper level adjustment would be copied to the lev0 _input member and Smart Push'd to ASO so the reporting cube was instantly accurate.

Ok, I had the approach, now all I had to do was figure out how to set it up.  Turns out there aren't a lot of blog posts or documentation on how to do this; hence why you are getting this blog post - you're welcome!

Solution

Before bringing this to the development team to implement I wanted to test it out as a POC, so I spun up a small PBCS app and went to work testing.

I created a standard PBCS application.  I did not add any custom dimensions for my testing; the plan was to use the Entity dimension as the one that needed the upper level adjustments.  The application has the two default plan types (one BSO and one ASO).  My Account dimension has just one member [Measure 1].  The Period, Scenario, Version, and Years are all out of the box.  I set up the Entity dimension as follows in figure 1.

Fig. 1

My next step was to create a data entry form.  This form is to simulate the automated data load process in the real application so I can get some data in the app to work with.

Fig. 2

Note that DataEntry1 is set up for Smart Push as seen in Figure 3

Fig. 3

Tip

For reference, the data map was set up with defaults since both cubes have the same dimensions

Fig. 4

I then have a Reporting form based on the ASO cube that shows me my starting data set.

Fig. 5

• Hear I can see that my data is loaded to base level 0, and my parent values are aggregated
• I have a [What If] member in my Version dimension where the users can enter inputs
• I have a calculated [Final] Version member to see the results of the Adjustments against the Base member
• I have the _input members to store the adjustments once they are entered

To solve my user requirement, I have to create a Data input form where the user can select the Parent entity members [P1] or [P2] and enter a [What If] adjustment.  That form is depicted here in Figure 6

Fig. 6

Figure 6 clearly demonstrates our problem.  This is the data entry form on top of the BSO cube, and while a user can certainly enter an adjustment, without seeing the Base number, it would be difficult to determine what the adjustment should be.  Unfortunately, as I have already explained, the BSO cube is not aggregated, therefore the value they need to see on the form does not exist; at least it doesn't exist in BSO, but as we saw in Figure 5 above, the ([Measure 1], [P1]) value of 450 does exist in the ASO plan type.

So here comes the magic.  We are going to create a new dynamic calc member in the version dimension called [Base_x].

Fig. 7

*Note that [What If] is a Standard Target version; that is necessary to do our upper level adjustment input.

The member [Base_x] has a member formula with an @XREF to the [Base] member in the ASO plan type.


@XREF(_ASO1Cube_, "Base");

Tip

@XREF requires a location alias to work; in this case _ASO1Cube_.  I am not clever enough to come up with such an alluring naming standard and even if I could, PBCS does not provide a mechanism for creating location aliases.  Fortunately, PBCS creates them for you automatically behind the scenes.  To find the location alias, you need to know where to look since it is not easy to find.  In order to find the location aliases that have been set up for your PBCS app follow these steps

1. Open calc manager by going to Navigator -> Create and Manage -> Rules
2. Select Database properties from the Action drop down
3. Expand the Planning node and continue expanding to the BSO database
4. Right click on the BSO database and select Location Aliases
5. Write down the Alias for use in your member formula

*Depending on how many plan types you have, you may have more than one location alias.

Fig. 8

I can now modify my DataEntry2 form and replace [Base] with [Base_x]

Fig. 9

Voila!  The user is now able to see in the form the aggregated value for [P1] despite the fact the form is pointing to a BSO plan type that has not been aggregated.

If the user changes the POV to [P2] they see the correct value of 900.

Success!!  Boil-In-Bag!!!

Fig. 10

From here the user can enter a value in the What If column to input their adjustment

Fig. 11

Some things to be aware of

1. The column "Final" is actually a Planning form formula column and not the [Final] member in the outline.  Using a basic formula column ([A] + [B]) we can show the user the result of the adjustment without having to create more @XREF members than absolutely necessary. 
2. The form is set to run a business rule on save that copies the value from the [P2] parent member to [P2_input] lev0 member.  See Figure 12
3. The form is set to run a Smart Push on save; PBCS will run the Smart Push after the business rule completes.
1. Note in Figure 13 that it is imperative you deselect the Entity dimension in the 'Use Form Context' column.  If you do not uncheck the Entity dimension, the scope of the Smart Push will be limited to the members on the form, which in this case is the [P2] parent.  Since we ran a business rule to copy [P2] to [P2_input] it is the _input member we actually want to move over to the ASO cube (ASO cube can also only take a lev0 member).  By removing the check mark for Entity we are telling PBCS to run the Smart Push for all Entity members.

Fig. 12

Fig. 13

Tip 

Note that the business rule (Figure 12) is set to use a RunTime prompt for the Entity based on the form POV.  I then use a concatenate function to derive the correct _input member for a target.  Figure 14 shows the business rule properties on DataEntry2 form.  Don't forget to 'Hide Prompt'.

Fig. 14

Now that the process is completed, any user can open a form/report from the ASO plan type and immediately see the results of the adjustment.

Fig. 15

As a final check we can run another report against the BSO plan type to see what we would get if we ran a Lev0 export, Figure 16

Fig. 16

Closing

So there you have a it, a somewhat long winded post I know, but there are a few intricacies here that were worth taking time to call out.  Leveraging this method you can increase the possibilities with PBCS and hopefully increase user productivity.


Please post any comments or questions you may have.


See you next post!!!!