Copy Planning Security to Essbase I

Today, I thought we’d look at a little proof of concept, in this part 1 of a 2-part series of posts. One of my co-authors and I were trying to figure out how to replicate security from a Planning application to an Essbase cube. Imagine the scenario, where we might have a planning application, for inputs only, and an Essbase cube, for reporting only. They have similar dimensionality. We could create groups and filters to enable security on the Essbase cube, but why not try something a little different.

Our POC is a combination of SQL, MaxL and DOS (or ODI or Shell scripting or P(J)ython or Perl or…). We know that Planning creates individual filters on the Essbase when security is refreshed from Planning to Essbase. For instance, a user named “Clem Fandango”, with the username “cfandango” will have a filter created, called “fcfandango”. We can view these filters from EAS, by right-clicking on the database and selecting Edit > Filters.

14_1_Filter

So, theoretically, all we have to do is copy these filters to our reporting cube. That should do the trick, so long as they have similar hierarchies and in this case, they do. It doesn’t matter if there are analytic dimensions as seen below as we are not planning on applying security to those dimensions anyways.

14_2_Otl

I am sure there is an API-ey way of doing this, but I wanted to keep this simple. I couldn’t figure out a way to copy all the filters en-masse using just MaxL, so I thought of using the Planning repository to give me a helping hand. All we need is a way to extract a list of users who access the application. This is easy enough to do as this information is stored in 2 tables, HSP_OBJECT and HSP_USERS. The latter looks like this:

14_3_Users

To get the user name tied to those IDs, all we need to do, is link back to the HSP_OBJECT table (this is the grand daddy table within the Planning repository).

14_4_Object

The join can be done by linking USER_ID from the user table to OBJECT_ID in the object table. So the query I need to get a comma-separated list of users and their filter names might look like this:

I am excluding certain roles, because I do not want to include certain roles (such as administrators or interactive users). How can we easily filter these? Just check the column comments, the Oracle developers have been kind enough to expose these to us.

14_6_Column_Descriptions

This yields the following results:

14_5_Filter_List

This seems like a good stopping point. Part II will focus on using these results to feed some Maxl and DOS scripts.

EDIT: Part II can be found here.

Avatar

About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He is, surprisingly, an Oracle ACE Associate. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

Leave a Reply

Your email address will not be published. Required fields are marked *