Use Valid Intersections To Bypass Smart Push Security Errors

If you have used Smart Pushes, you have undoubtedly run into security issues.  The PBCS development team is working on a way to bypass this, but there is no release date.  If you haven’t run into this, you probably have and didn’t know it.  Here is the issue.  Dimensional security is typically setup so that users only have access to write to specific accounts.  For example, they have access to update units and cost per unit, but not revenue because revenue is calculated by multiplying units and cost per unit.  The problem is that a Smart Push won’t push revenue because the user doesn’t have access to write to that account.  Said another way, when users update the components of revenue, the revenue itself doesn’t get pushed to the reporting cube because the user doesn’t have write access.  Here in lies the problem, as revenue won’t get updated until an administrator runs a Data Map.

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.

1. 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.
2. 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).

When the form is saved, and the Smart Push executes, the Smart Push uses dimensional security.  All the data pushes since the users have write access to all accounts!

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.

Finishing Up

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!