The Compleat Idiot’s Guide to PBCS, No. 6 – Philip Hulsebosch and Valid Intersections

This is your host

Yr. Obt. Svt. may be the host but Philip Hulsebosch has again done all the work.  It is, as usual, top drawer stuff and yet another dive into the coolness that is PBCS.  

Sit back, adjust your comfy slippers (well, I’m writing this in my comfy slippers; hopefully you are in yours as well), and give this a read.  It’s well worth your time.

A note about comfy slippers – I take my sartorial hints from Fred MacMurray for which I blame a childhood of watching My Three Sons.  Check out this episode – not only are the slippers in play but so is the cardigan sweater throughout the episode.  Think about it – I largely work from home doing something that hardly anyone understands (Just what did Steve Douglas do for a living?  I couldn’t ever figure it out.), I wear slippers just like this (slate floors are cooooold), and the modern day equivalent of that cardigan.  I’m livin’ the dream.

And with that minor digression about Cameron’s complete lack of fashion sense – unless you live as I do in 1961 – out of the way, on with Philip’s considerably more relevant work.

Valid Intersections.

The functionality of “Valid Intersections“, currently available in the “Planning Budgeting Cloud Service” (PBCS), is subject worthy of review because it gives write access to a node of two or more dimensions and read access to those areas that are not defined in the set. Finally Planning can assign access to detailed member level intersections - something we we’ve been able to do in Essbase for a long time.

With Valid Intersections, we can define sections in the database as having “cross-dimensional irrelevance” and assign read-only access to them. By doing so, we prevent data being stored accidently, where they should not be and enable the relevant slices for data input.

Let me give an example: When you have an account as “Mobile Subscribers” and you have product categories “Mobile”, “Fixed Line” and “Cable”, then it is logical to have data only in the first category. One could create a Valid Intersection of the Account “Mobile Subscribers” and Product category “Mobile”. This would make the combinations with “Fixed Line” and “Cable” read-only.

Set Theory

Valid Intersections are based, like many of the functions in a multidimensional database, on Set Theory. Last, when I was working on this subject of Valid Intersections, my son entered the office. I told him that I was working again with Set Theory and how much I benefited from learning about this subject in school in a parental attempt to motivate him for subjects at school which might not be in line with his current interests. He said, “interesting”, looked at my screen and asked why I did not have a touch screen and just mark in a graphical interface the intersections I needed. That was so much easier… well, … a good question! Today, it is all about the interface.

Back to Set Theory – see, the stuff we learn in school really is relevant later in life no matter how much the 16 year old in us rebels. In Figure 1 you see a diagram with an intersection of two circles A and B. These represent Dimensions. In a Valid Intersection, you define A in a rule and B in a rule. As a result, the red area means write access, the white area means read access.

Figure 1: A Venn diagram illustrating the intersection of two sets. Red is the intersection of A and B and would result into write access.

How do I define “Valid Intersections” ?

The functionality to define “valid intersections” is only available in the “Simplified Interface”. It is the user interface of the future for both the administrator as well as the power user. In the last days, I have worked extensively with it on a PC and I really like it. The Tablet is also possible, but I love my large computer screen and keyboard. It makes me so much more efficient.

After logging into PBCS, select the Option “Console”, then select the third tab at the left side with the name “Valid Intersections” and it will open a window. Here you see existing “Valid Intersections” and at the top left a menu for adding or modifying them.

Figure 2: Overview of the Valid Intersections.

In this post, I want to show how to create “Valid Intersections” in the “Vision” application. In this example, the yellow marked areas should be write-enabled for scenario “Forecast” and “Plan”. The dark yellow areas should only have write access for “forecast”. All other areas should have read access. In the rows is the dimension “Entity” and in the columns is the dimension “Products”.

Figure 3: Example to apply Valid Intersections in the Vision application.

By pressing the button “Create” a new Intersection can be created. First, you need to think about which Dimension should be the “Anchor Dimension”. This is the….

I will take the “Entity” dimension and along this dimension, I will create various rules.

Figure 4: Selection of the Anchor Dimension.

I will give it the name “Entity-Product”, so I can see in the name which Dimensions are used in the Intersection.

Figure 5: Option “Unselected members are valid” is activated.

I have enabled the option “Unselected members are valid”. This means, all not selected members in this dimension are not part of the set and thus read only.

The next step is to select the “Product” dimension, by clicking the “Add Dimension” button.

Figure 6:  Selection of an additional dimension.

I tag this dimension to be required. So a selection is needed for all rules within this Valid Intersection.

Figure 7: Enabled the required option for this dimension.

Now, I can create the different rules. To do this, I click at the button “Add Rule” and the selection window with the two dimensions opens. At the top of the window, you can switch between the dimensions while selecting the intersection. Check Figure 8 for details.  Members can be selected by mark next to the name and functions are available at the right side of the member name.

Figure 8: Selection in the dimension “Product”.

At the right top side of the page, you can find the menu with the display options.

Figure 9: Display options.

When you are familiar with the member names, you can type them into the box. It is not necessary to select them in the member selection.

Figure 10: Editing the selection by typing.

Click the “Save and Close” button and the Valid Intersection is added in the list.

Figure 11: Valid Intersections. The top one is disabled.

Now let’s see this on an Ad-Hoc report. A refresh on an existing report shows the result. A logout and new login is not necessary. The entities “420“ and “421“ and the child products of “TP1“ were defined in a Valid Intersection. These are write-enabled, while the rest become read-only.

Figure 12: Result of the Valid Intersection definition.

Adding a new rule to the Valid Intersection. I can edit the rule on the window and I do not need to go into the member selector.

Figure 13: Adding a new combination of members as a rule.

A new query. The results become immediately visible.

Figure 14: Result of the changed Valid Intersection definition.

And now adding the last rule and hand-pick the elements of the product dimension.

Figure 15: And we add another combination into a rule.

With this, the third section of rows has been defined.

Figure 16: Result of the changed Valid Intersection definition.

And now, I combine the remaining entities “410“, “450“ and “810“ with the dimension member “Product”. This is quick and dirty, but it works, because these members will become read-only.

Figure 17: Adding the last rule.

With this, all sections are now defined. Let’s check the results.

Figure 18: Result of the changed Valid Intersection definition.

I change the account. Good, the access rights do not change on the entities and products.

Figure 19: Result after selecting a different account.

Now I change the scenario. The write enabled sections do not change.

Figure 20: Result after selecting scenario “Actual”.

Now I have to make the difference between scenario “Forecast” and “Plan”. In “Forecast” we wanted to add write access to the Entities “410”, “450” and “810”. So we have to extend the current Valid Intersections.

You can easily duplicate a Valid Intersection. Then it is really easy to modify it.

Figure 21: Duplicate a Valid Intersection.

Figure 22: Result from the duplication step.

Then I will rename the intersection into “Entity-Product - Forecast“.

To make the difference between Scenario’s, I will need to add this dimension into the Valid Intersection. I will select the member “Forecast” in each row. In the last row with the entities “410”, “450” and “810” I replace the “Product” for “Children(P_TP1)”.

Figure 23: Changed the selection in this new Valid Intersection.

Figure 24: Result after saving.

The month has been changed into “July” to see the effect on scenario Forecast, but as you can see in figure 25, the entities are not write-enabled! What is wrong?

Figure 25: Result of selecting scenario “Forecast”. Nothing has changed.

The reason is both valid intersections add up. I will select “Budget” in the scenario and I see the rule with “forecast” works.

Figure 26: Result after selecting scenario “Plan”. The intersections are adding up and scenario “Plan” is not in the intersection anymore.

Figure 27: Menu options under “Action”.  In Englisch, the menu choices are:  edit, duplicate, delete, up, and down.  German is the Esperanto of the future.  

It is simple to enable and disable Valid Intersections. Just remove the green checkmark in the column Activate. As in figure 28 shown, a green checkmark it is enabled and a grey one means it is disabled.

  

Figure 28: Enable and disable a Valid Intersection.

I disable the Valid Intersection “Entity-Product”.

Figure 29: The first Valid Intersection is disabled.

And now, entity “410” in “Forecast” is write-enabled. So that is verified to be the reason. I will need to modify the Entity-Product Valid Intersection.  

Figure 30: Result when selecting scenario “Forecast”. Also entity “410” is write-enabled.

And in scenario “Plan” it is not as can be seen in figure 31.

Figure 31: Result with scenario “Plan”.

In the Valid Intersection Entity-Product, I add also the dimension Scenario. In each row, I add “IChildren(Scenario)”. I enable this Valid Intersection after saving. Now, both Valid Intersections are active again.

These are the 4 rules now:

Figure 32: Valid Intersection 1 has been adjusted.

Refreshing the reports. Scenario “Forecast” is now correct.

Figure 33: The result in scenario “Forecast” looks correct.

Scenario “Plan” looks also OK.

Figure 34: Result in scenario “Plan”. It is correct, because entity “410” is read-only.

It is possible to reduce the number of rules to a minimum ….

Figure 35: The Valid Intersection has been reduced to the relevant part.

And it is also possible to combine both Valid Intersections into one.

Figure 36: Combination of the rules in both Valid Intersections into one Valid Intersection.

A check and the results stay correct!

The option Unselected members are valid.

To show the effect of the option “Unselected members are valid" I will extend the current report with additional entities. These entities are “815”,”830” and “840”. In the Valid Intersection, I select the anchor dimension Entity and open the menu. Here I make sure the “Unselected members are valid" is enabled.

Figure 37: Entity with the option “Unselected members are valid".

The members “815”, “830” and “840” are write-enabled in the scenario “Plan” and “Forecast”.

Figure 38: The members “815”, “830” and “840” are write-enabled.

Figure 39: Also in Forecast are members “815”, “830” and “840” write-enabled.

I will switch off the option “Unselected members are valid". This will take care that the members not selected become read-only.

Figure 40: Entity with the option “Unselected members are valid" disabled.

The result is as expected. The entities “815”, “830” and “840” who now are excluded from the selection set are read-only.

Figure 41: The entities “815”, “830” and “840” are read-only in the scenario “Forecast“.

Figure 42: Also in the scenario “Plan“ are the entities “815”, “830” and “840” are read-only.

Now we have reached our goal which is displayed in figure 43.

Figure 43: Overview of the write enabled areas for scenario “Plan” and “Forecast“.

Conclusion

Valid Intersections gives us a very nice possibility of access control. It can be defined up to the single cell level, which was not possible until now in Oracle Hyperion Planning. This functionality is available in PBCS and likely will be coming soon in the on premise version as well.

I did not check if Valid Intersections also work with data load. In the data forms it is working very well and I hope this post helps you on this subject.

Regards, Philip Hulsebosch

Yr. Obt. Svt.‘s conclusion

I’m not sure what is more amazing:  Planning finally supporting ANDs in security or Philip’s ability to clearly explain a complex concept and process in a clear way.  Okay, what’s even more amazing is his willingness to do this as a guest writer.  This was no quick note as Word tells me this is page 20 which is proof, if proof was needed, that his committment to the Oracle EPM’s community’s education is deep and sustaining.

Philip’s clients are lucky to have him.  We’re lucky to have him as a blogger (in Englisch), writer, and presenter.

Be seeing you.