Fixing Planning's Filters

Full disclosure and source code and of course a disclaimer
Much of the content of this post comes from a ODTUG Kaleidoscope presentation I gave in June of this year. The presentation (280 – Master Essbase with MaxL automation) should be available 90 days after the conference, so I am guessing some time in September. I will update this post when it is available for those who want to see more.

This code was sort of the capstone of the presentation and used a variety of MaxL techniques. If you look at the source code you’re probably going to wonder why I used so many different approaches to, oh say, error checking, for example – you will intuit that I was trying to illustrate the different ways it can be handled. For your sanity I suggest you pick one (like %ERRORLEVEL%) and stick with it.

The source code is available here.

Take a look at the code, laugh at it, admire its genius, use it in anger – whatever. If it causes the end of the world (your Hyperion world, at least), gets you fired because it didn’t work, or any other less than optimal outcome, well, I make no guarantee, warranty, or anything else. Use it at your own risk. It is worth every penny you spent, which would be exactly zero.

Planning’s Filter Oddity
I seem to be going on and on about Planning, not Essbase, and of course the name of this blog isn’t Cameron’s Blog For Planning Hackers, so it’s fair to ask what’s going on.

Well, Planning is a wrapper application around Essbase – at the end of the day, whether it’s dimensions, data, or Business Rules (aka calc scripts on steroids) – Essbase is the foundation that Planning rests on. And Planning drives Essbase, but oddly.

Within Hyperion Planning, metadata is filtered. That is, if you're a Planning user, and security is applied to a dimension, you can only see the bits of the dimension that you have read or write access to. (If your read access is greater than your write access, you will see more members in, say, a dimension drop down, but you will only be able to write to the members you have write access to.)

This makes sense, right? Why would you want to deal with members you can't touch?

Makes sense
Here's the administrator's view of the Entity dimension from the Planning reference application:


The Planning administrator sees all – this is appropriate, as he is responsible for all data.
And here is a Planner's view of the world (literally):
See the difference? For a user that can only see Latin America, all is as it should be.

Stop making sense
And here’s that same user looking at the same dimension in SmartView:
The rows highlighted in yellow are not readable/writeable, but are visible. While the planner can select these members, he can’t retrieve their data values. This is inconsistent, and is colloquially known as Not Good.

Why does it do what it does?
Planning performs metadata filtering within the application, but when it writes the filters to the Essbase id used to query data, it uses a READ, not a METAREAD filter.

Why? This is a mystery. Okay, lots of things in life are mysteries, but the fix from the Planning side seems so simple (use METAREAD, not READ). I’m a fan of Raymond Chandler, but I am not seeing the plot to “The Big Sleep” here. Well, maybe someone was snoozing when this was implemented.

The fix (no, not the FIX..ENDFIX, the solution)
A few years ago at Solutions in the product lab I asked a Planning product manager (I think) about using MEATREAD and he said "It can't be done."

Curiouser and curiouser. Why not? Was there something in a METAREAD filter that would cause the world to stop spinning, the magnetic fields to fade, and result in a cockroach-only world? This did seem somewhat far-fetched.

There had to be a reason; I suspected I wasn’t hearing it. This was (and is) sort of the equivalent of waving a red cape in front of a bull and it made me curious -- why not? Essbase has had METAREAD since 6 (I think, it could be earlier). Why not write a METAREAD to Essbase when refreshing security?

There was an easy way to test this – roll my own refresh and force METAREAD filters as part of the Planning refresh.

Dare I? Briefly, I considered Robert Oppenheimer’s quote of the Bhagavad-Gita at Trinity: “I am become Death, the Shatterer of Worlds,” but in the true spirit of all mad scientists, I quickly dismissed such idle thoughts. Our future as cockroaches would bring many benefits, anyway.

Diagnosing the disease
What's the problem?

The filter in EAS as written by Planning:


Easy fix in EAS
If only it were this easy -- just copy the READ row and make it a METAREAD. Since METAREAD is more restrictive (both data AND metadata) it takes precedence, so the READ line doesn't have to be deleted:
And it looks just right in SmartView (or the Essbase-centric reporting tool of choice).

One tiny problem
But when Planning does a security refresh, it's going to go right back to the bad old ways of yore, and will strip the METAREAD off the Planner's filter. Bummer.

And if you have 200 users, manually editing their filters in EAS is going to cause your fingers to fall off, or your eyeballs pop out of your head, or something else equally horrible. I understand that cockroaches can regenerate limbs, so there’s another plus towards the possible end of the world due to METAREAD.

Back to some kind of solution
By hacking (okay, I am stretching this, but hey, it's the name of the blog, so bear with me) Essbase, we can make the user experience the same between Essbase and Planning without . Never say "It can't be done" to a moderately curious geek.

What would this refresh need to do?
1) Refresh Planning dimensions and security
2) Apply METAREAD access to those filters

Pretty simple, right? It's always easy when the consultant writes it on the whiteboard...

A little more detail
We're going to combine Planning 11x's CubeRefresh.cmd, MaxL, NT Cmd scripts (I am showing my age), and VBScript (ditto) to put together a scripted approach to Planning that:
1. Refreshes Planning through CubeRefresh.cmd
2. Writes the filters to disk via MaxL
3. Reads them into memory in VBScript
4. Programmatically generates MaxL code to apply a METAREAD to each of those filters
5. Run that new MaxL script

BTW, I happen to be mildly proficient in VBScript (call me Fred) and NT Cmd scripts. You could use whatever tools you like, e.g., Powershell, or the Bourne shell, or Rexx (call yourself T. Rex), etc.

Putting it all together

This is where you’re going to want to download the source code and follow along. At least that’s what I would do, but I have been called odd (and a few other choice descriptions, but I digress).
Script components

Script name	Description
Refresh_Planning.cmd	Overall NT Cmd control script
ModCubeRefresh.cmd	Refresh Planning utility
Create_MetaRead_Filters.wsf	VBScript to read output from Write_Filters_To_Disc.mshs and write METAREAD filters
call_metaread_add_filters.mshs	MaxL shell to run METAREAD filter adds
Metaread_Add_Filters.msh	Programmatically generated METAREAD filters

A note about ModCubeRefresh.cmd
When I ran CubeRefresh from a command line (this was within the C:\Hyperion\products\Planning\bin directory) it worked just fine. However, when I called it from another batch file it stopped all execution. I tried every trick I could think of to make it work and finally just added an “EXIT” to the end of it and saved it as ModCubeRefresh.cmd.

Refresh_Planning.cmd
To get this all to work, I used the calling script to accept parameters as shown below:

Parameter	Value
Planning encrypted password file	c:\tempdir\odtug_2009\password.txt
Planning application	plansamp
Planning admin username	admin
Refresh or create	/R
Filter switches	/FS
Essbase server	%computername%
First half of private key	316108469
Second half of private key	694177571

How do I run it?
refresh_planning.cmd c:\tempdir\odtug_2009\password.txt plansamp admin /R /FS %computername% 316108469,694177571

ModCubeRefresh.cmd
The normal CubeRefresh.cmd script has seven parameters:

Parameter	Value
-f	Path to encrypted password file
/A	Planning application name
/U	Username
/C	RMI port to bind to (Extra points if you tell me who said “This is the sort of English up with which I will not put.” And why. And if he really did. Hint -- His name isn’t Gordon Broon.)
/R	Create or refresh outline
/D	Process database
/F or /FS or /FV or /FSV or /FVS	Filters, shared or not, or even validated
/L	Connect to the local bean (Is this like, "Splendid work, old bean."? Again, I digress.)
/RMIPORT	RMI port to bind to the remote Planning bean. Use with /-L option
/DEBUG	Print debug statements

The RMIPORT, Create/Refresh, Process database, Use security filters, Connect to the local bean, and Debug parameters are optional.

In my world (this is my code, you can follow or get just as explicit as you like/need/want), I chose to explicitly define: the password file, application name, username, refresh or create, filter switch (what would be the point otherwise?), and I also passed along a log file I use for error checking.

Write_Filters_To_Disc.mshs
This is simple code that writes all Essbase filters to disc. I don’t know why I did this, as the display filter command can be limited to only one database. It works in the demo world, but in a real environment it’s a distinctly lousy idea. Feel free to modify this with a MaxL parameter variable to pass only the Essbase database filters you want. Yes, this gets trickier with multiple Plan Types.

Create_Metaread_Filters.wsf
This is where the rubber meets the road – it reads the output from Write_Filters_To_Disc.mshs, converts the READ filter to METAREAD, and generates the MaxL script Metaread_Add_Filters.msh. This file will be used to set the Planner’s filters to METAREAD.

Call_metaread_add_filters.mshs
Encrypted (username and password) MaxL shell to modify filters to use METAREAD.
Metaread_Add_Filters.msh
This is the code that adds the METAREAD. It’s kind of silly to have both a READ and METAREAD in the same filter, although it works because of METAREAD’s more restrictive nature. I tried using MaxL’s replace filter command instead of alter filter but I found that it just gave the Planner read access to the entire dimension in question. It must be (I think) something to do with Shared Services and alter filter works, so I went with that. I welcome a better approach – hint – Blogspot has a comment section and I read them. I will modify this post as required.

What does it look like?
Magic – Essbase filters that come from Planning are now METAREAD filters.

Conculsion
I believe that the Earth still rotates on its axis. All is well. I do not scuttle sideways across the floor, and my trusty prismatic compass still works, so apparently TEOTWAWKI did not occur.

NB – You will have to perform all filter refreshes through this utility. Unfortunately, for ModCubeRefresh.cmd to work, this will require Planning server access, or at least remote access. There are a number of ways to do this – Google is your friend.

The future
At the conclusion of the presentation, I heard that METAREAD functionality is now in the pipeline for Planning.

However, if you’re on Planning 9x (the Planning refresh utility is a little different), or up to 11.1.2, you may want to use this code base to give your Planning users the same metadata filtering in Essbase as they get in Planning.

Another chapter in hacking Essbase under the belt.