Copy Planning Security to Essbase II

As we’ve seen in the last post, we have a list of users and their filter names as they exist in Planning. Continuing on, let’s take a look at what the documentation tells us about filters.

15_1_Filter_Doc1

It’s not evident at first glance, the importance of the section, “as FILTER-NAME”. If you dig one step deeper, we find that the filter-name object needs to be follow the syntax,  application_name.database_name.filter_name”.

15_2_Filter_Doc2

The part above, is what helps us in copying filters from one application to another. The pseudo-code MaxL required to do this might look like:

The next step is to extract the list above to a file by calling SQLPlus (yes, this one’s on an Oracle database, not SQL Server).

The contents of “Ext_Filters.sql” looks like:

The code above extracts the filters into a usable format.

15_3_FilterList

Now, let’s set up some MaxL scripts to use the output above in an efficient manner. I chose to go with 2 individual scripts, one for copying filters and one for granting the user with the filter. The former might look like (ignore the fact that I am using encryption, this will work either way):

And the latter might look like:

Finally, all we need is a little batch script to put all the pieces together.

The “FOR” loop above will go through each item on the “FilterList.txt” file and process them individually. And that’s it, all I had to do was run the script and my filters were copied over. Though this does provide a quick way to copy filters from 1 app to another, you do still lose the controls you might get if these users were in groups. As I mentioned earlier, this is just a little POC.

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 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 *