I understand why Oracle did this, but with no ability to override it, it creates issues with Smart Pushes and real time reporting.
Before I continue, I want to thank Pete over at Essbase Down Under for this brilliant idea. Enter stage left, valid intersections!
What Are Valid Intersections?
Normally, valid intersections are used to link drop down menus. For example, once Honda is selected in the first menu, the second menu only shows the models related to Honda, like Accord, Civic, and Prelude. If you know what a Prelude is, you have been doing this as long as I have! Valid intersections can also limit what users have access to write too, like the rows a user has access to write to in a grid. If valid intersections are setup on account and accounts are in the rows of a Data Form, the grid will show all the accounts, but the invalid accounts are greyed out and not editable. Since this isn’t truly security, when the Smart Push is executed, the user has the access to push calculated accounts.
Why Not Cherry Pick Rows As Read Only?
Sure, rows can be set to read only. If you are thinking about going down this path, beware! There are some huge issues with this approach.
- As hierarchies change, forms will have to be manually changed. By singling out specific rows, it means administrators lose the benefit of functions, like level 0 descendants. So, rows will have to be manually maintained every time metadata changes.
- Since this is a form setting, users who run ad-hoc retrieves can still write data to these accounts.
Valid Intersections In Action
You might be thinking, yeah, but valid intersections also use level 0 descendants? They do, but just like in forms, exclusions can be applied.
In the example below, the rows/accounts with the check marks have been setup to be valid with the working scenarios.
In this example, the dimensional security is set to write for all accounts on this form. As visible below, the dimensional security is ignored. Users have the access to the accounts needed, but can’t edit those that aren’t setup as valid intersections (selected in the valid intersections setup above).
What About Ad-hoc?
Don’t worry, users can’t change data on the invalid intersections. Well, sort of. The rows for the invalid accounts are still greyed out. Here is where it gets blurry. Users can still edit the data in those cells, the ones that are grey. Although this might be confusing to a user, it is ignored on submission. Below shows the accounts in the above form, but in an ad-hoc. Notice what happens when the first two rows are changed. The first row is saved, but the second is not. It returns to its original value.
I wish there was a parameter in the Smart Pushes (Groovy or not) that allowed the form developer to set the permissions to use admin privileges. Until this gets figured out with Oracle, this is a great way to fix the problem. Everybody that uses Smart Push will run into this. Setup dimensional security to write to all accounts. Setup valid intersections for the accounts that users need to be able to edit. And wallah!