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.
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.
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:
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).
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:
|
1 2 3 4 5 6 7 8 9 10 |
select chr(39)|| n.object_name || chr(39) || ',''f' || n.object_name || chr(39) filterName from hsp_users u, hsp_object n where u.user_id = n.object_id and u.role in (1,2,4) order by 1; |
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.
This yields the following results:
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.
