Loading Valid Combinations

Loading Valid Combinations in bulk

Author: Mike Falconer, October 2016

 

A very useful new feature of the Simplified Interface in PBCS is the ability to define valid intersections of your dimension members. This means you can restrict your users to only enter data into specific combinations of several dimensions which you define.

This is built to plug the inevitable gap that dimension level security cannot cover. As an example let’s say that we want to restrict entry to certain employees based on the entity they work for. If a user has access to both entities, they can now enter payroll data into the wrong company, causing much confusion all around.

Valid Combinations sets up a second layer of data integrity, and saves lots of time in the long run for both the user and the administrator!

So where do I find this wonderful feature and start putting it to good use? First you need to navigate to the Simplified Interface and the [Application] -> [Setup] menu. To create a Valid Intersection, use the aptly named Create button and you should end up with a screen looking like the below:

 

From here you need to select an Anchor dimension, which is the dimension choice that drives the valid combinations. The important thing here is to choose whether 'unselected members are valid' or not. If you turn it off, then all members of your anchor dimension will be read-only for any combinations you don’t specify, so rather than specify a combination for every entity, we leave it on.

Next we need to add another dimension to have combinations of, so we use Add Dimension and define Analysis 1, where our demo Employee dimension is kept. Next we need to select the actual members we want using the member selector, and as usual we can select formulae as well.

 

 

But as with anything good, there’s a catch. It’s not yet been made easy to upload these combinations in bulk, making them fiddly and time consuming to use. Fortunately your friends at Brovanture have developed a “life-hack” to massively speed up the process of uploading them.

The first step is to export the Valid Combination rule that you just created using Migration, and download the application snapshot to your PC. Next find the XML which should be stored under the folder structure:

Export-Name\Application-Name\resource\Global Artifacts\Valid Combination Rules.

 

Before continuing, you should take a backup of this xml file, and then copy it to another location to work with. Open up your XML using a text editor like Notepad++ to see the format of the file that we will need to replicate using our hack:

 

 

This all looks absolutely awful to deal with, right? We are going to use Excel to make formatting this XML document much simpler. Opening up a fresh Excel workbook we can now enter the following information into the columns:

Column

Contents

A

<mbrselection>

B

Your data, comma delimited

C

</mbrselection>

D

 </VCSubRule>

<VCSubRule>

 

In addition the top cell of column D should be and the bottom cell should be . This gives you the following expandable structure:

 

 

Next, we need to copy the entire excel grid into a fresh Notepad++ document. In here, we need to make the final formatting changes before it can be inserted back into the XML file. Using the find and replace function (Ctrl-H) we remove the character and the TAB character. We can check that the formatting hack has worked successfully using the XML Language pack in Notepad++. Be sure to re-add the characters to the top line  

 

 

The final step is to copy our new sub-rules back into the original XML, and overwrite the original XML file in the PBCS Export. It’s important that the same names are used for all of the files. Finally, using Application management again, upload and import the XML back into PBCS. Going into the simplified interface we can see our updated rule:

 

Thanks for reading, and I hope this document helps you if you are considering Valid Combinations for your next PBCS project. More detailed instructions and information from Oracle on how to use and create Valid Intersection rules within PBCS are available here: https://docs.oracle.com/cloud/latest/pbcs_common/CSPAM/ch06.html.