Valid Intersections in Oracle EPM Cloud – One Feature You Need to Understand
October 4, 2017 - By: SC&H Group
Trying to implement a working cross-dimensional security model in an EPM application to align with business requirements is like standing in a security line at the airport. You don’t want to do it, but you have to in order get to your 5-day, paradise beach vacation! Luckily, the TSA PreCheck lane has come to Oracle’s EPM Cloud applications (PBCS, FCCS, etc.) in the form of a new feature called Valid Intersections.
Valid Intersections can be used in a variety of useful ways throughout an application, but most importantly, they make the lives of both end-users and administrators simpler, and simplicity is always a good thing. A well designed combination of Valid Intersection rules can avoid accidental or incorrect placement of data, restrict viewing access of dimensional intersections, and provide advanced suppression capabilities that can be used in web forms or POV selections.
While this sounds cumbersome, Oracle has done a great job of making Valid Intersections intuitive and powerful — they can be used to enhance Oracle EPM Cloud applications and say goodbye to the traveler who forgot to take their liquids out of their carry on!
Let’s start by looking at the home screen of the Oracle EPM Cloud (in this case PBCS), which nests many important administrative tools, with one of them being Valid Intersections.
The primary goal for this example will be to restrict data entry to “allowed” scenarios in the application. This is a common example that often requires several security groups which are toggled with read/write access to maintain data integrity.
To set up a new rule, select the Create button, enter a title and description, then the dimensions that need restriction.
The first step will be to choose an Anchor Dimension. This is the dimension that the rule revolves around — think of it as a FIX Statement from a business rule. We will select the ‘Version’ dimension as the Anchor and pair with ‘Scenario’ to create a simple Valid Intersection rule.
To add ‘Scenario’ click the ‘Add Dimension’ button:
We have added only one additional dimension other than the Anchor, but you may use multiple. Adding more dimensions to a Valid Intersection rule will create a more specific combination of dimensions that is valid. After all dimensions are added, the actual rule needs to be created by selecting the ‘Add Rule’ button. Next, within the rule we need to define the actual dimension member names to be included. In this example, our end goal is to specify that only the ‘Working’ version is allowed for the Plan and Forecast scenarios. We add the ‘Working’ version member first using the drop down ‘Edit’ button.
Navigate through the dimension until the appropriate member is located and hit the check mark to select it, then press the OK button. Once chosen, you will see the member populate in the ‘Selections’ column and the check mark will highlight.
Now that we have the ‘Version’ dimension member defined, we need to select the appropriate ‘Scenario’ members. We have two options: (1) Go through member selection again or (2) Type the member name to find ‘Plan, Forecast’.
BOOM! We now have our first Valid Intersection rule. Press the Save and Close button to finalize!
Now that we have our Valid Intersection setup, what does it actually look like to an end user? Below, we have pulled a Smart View query looking at some base level working data for three scenarios: Plan, Forecast and Revised Plan.
Does something look a little different than you expected? Why is the ‘Revised Plan’ read-only when we set up the rule with Plan and Forecast as the selected members? This is difficult to wrap your head around at first, but we didn’t specify ‘Revised Plan’ as being valid for the ‘Working’ version. Instead of setting up the intended, restricted intersections in the rule, you are truly setting a “valid” intersection based on the selection criteria. This means that every other ‘Scenario’ in the application not listed as valid is locked down for everybody at the ‘Working’ version.
We didn’t have to create any security groups or modify dimensional security, but we still discovered a way to control where all users have the ability to enter data at various Scenarios.
But this begs the question — what happens when requirements dictate that ‘Revised Plan’, or another scenario, needs to be manipulated? For instance, maybe ‘Revised Plan’ only should be edited at specified times throughout the planning process. There are two options to make this a possibility and open the ‘Revised Plan Scenario’:
- Edit the Valid Intersection rule to also include ‘Revised Plan’. After the revisions are made, the ‘Scenario’ can be removed from the Valid Intersection rule to lock it down again.
- Turn the Valid Intersection rule off entirely by simply clicking the Enabled option. This will open up all scenarios to be modified at the ‘Working’ version. It’s really that simple.
And what do you know, after the change we can now modify the ‘Revised Plan’.
Once all users are finished modifying, select the ‘Enabled’ checkbox and the scenario is locked down once again. The best part is no application/database refresh is needed; the Valid Intersections are applied immediately!
Whew, feeling like a Valid Intersection master ninja now? Yes? Maybe? Not Quite? Well, whatever category you fall into let’s look at another use case: Users planning for accounts at wrong departments. Wait, users actually do this? One gold standard example is users planning for revenue accounts at overhead departments. Valid Intersections can ensure this blasphemy doesn’t occur.
The query below demonstrates incorrect planning on two levels:
- Miscellaneous Revenue Data in the Purchasing Department
- Existing Depreciation in the Sales NorthEast Department
To start the Valid Intersection rule we need to think about which dimension really drives if the data is valid or invalid because that will determine the Anchor dimension. We will put the ‘Entity’ (Department) dimension as the Anchor dimension and then specify the appropriate accounts.
For the ‘Entity’ dimension column we created two lines to separate out “Sales and Finance” and “Accounting” departments because different accounts are allowable for each group.
Instead of listing out departments, we got fancy and used the ILvl0Descendants function. This specifies that any department underneath the ‘Sales’ parent will all have the same allowable accounts. The function allows the Valid Intersection rule to reflect any member changes within the ‘Sales’ hierarchy.
We used the same function for accounts paired with the departments.
In a verbal context, the first line in this Valid Intersection rule reads: “For base level Departments underneath the Sales parent, the allowable accounts are base level of Gross Profit.”
The second line is for overhead related departments underneath the Finance and Accounting node. These Departments should not have any Revenue or COGS, so we excluded them from the allowable accounts.
Now that we have the Valid Intersection built, let’s look at the beauty of all the newly defined read-only accounts identified in grey.
We have only taken a few licks from the Valid Intersections lollipop! The possibilities are endless, and it’s a must-have feature that can be applied to almost any EPM application to enhance data integrity and remove the burden of creating complex security models. Hopefully you can use Valid Intersections to get you through that security line faster and relax before your flight!
If you have any questions or are having trouble, we’d love to hear from you. You can reach out to one of our Business Architechs on our website here. We’d be more than happy to see how we can help.