Planning security: the wrong way and right way – which way is yours?

Introduction

Yes, again, not an Essbase subject, or more precisely, not strictly an Essbase subject.

Perhaps I ought to rename this blog to, “Cameron’s Blog For Planning Hackers,” as I seem to spend a lot of time talking about Planning. So, yes, this is a big hint that Planning is on the menu.

The subject of how to best define security in a Shared Services/Planning/Essbase world keeps on coming up with clients and was suggested to me by my much missed, ex-colleague (sob) Josie Manzano. If Ms. Josie suggests something, who am I to argue? It seems like the plurality of opinion is for it, and besides, I can tell my clients to, “Read my blog,” instead of having a conversation about security and hence drive traffic to this site. :)

How not to assign security

I can think of a few ways, all bad...

Assign security directly to a user name

Pro

• It works.

Cons

• Maintenance intensive – what happens when the user gets promoted (surely all who use Oracle EMP get promotions), quits, gets hit by a SEPTA (Southeastern Pennsylvania Torture Transit Authority) bus, etc.?

Assign security to groups without inheritance

Pro

• At least you’re not assigning to usernames.

Con

• Still maintenance-intensive, and you’ll have just as much many manual assignments. What were you thinking? Oh wait, you’re a Victim of Planning 4.x and before, so it isn’t your fault. Remember, all you have to lose are your chains.

Assign security to groups with inheritance

Pro

• Ah, you’ve reached Oracle EPM security nirvana.

• Low maintenance through inherited security.

• Inheritance design allows atomic security assignments.

Cons

• More than four levels of inheritance can bring poor performance, so don’t.

• You’ve had to read my drivel (346 words, thus far) to get to this point.

How does it all work?

1. Assign an upper level group in Shared Services to your Planning application and provision the group to access the application. In Planning; this typically means the Planners role.
   
2. Create one or more subgroups that are members of the overall group you just created. Typically, this is used to assign access to the Planning Plan Type. Note that the subgroups are provisioned to the application through security inheritance – there is no need to provision access to the application at this level.
3. Create yet a third (and in this example, the final) level of groups. There could potentially be many groups here (you may define many, it could be 20, it could be 50, dependent on how focused you define security). This third level is a member of the second group. Again, no need to assign provisioning to these groups.
4. Assign Planners to the third generation groups.
   

Three layers of groups seems a bit much just to provision access to an application. Surely there must be another reason to create this many layers, yes?

Indeed there is – now these multiple security groups are going to be applied to different slices of a Planning (or Essbase) application.

A mythical Planning application

The Planning application TotPlan has two Plan Types (Planning uses strange names for concepts that have been around since the year dot in Essbase) that correspond to databases in Essbase: Consol and Workforce.

The planners in these two Plan Types are mostly mutually exclusive; there are some users active in both, just to be difficult.

In this mythical application there are seven dimensions (note the modified hourglass order with the non-aggregating sparse dimensions at the bottom -- even examples should be optimized):

1. Account
2. Period
3. Entity
4. Employee
5. Year
6. Scenario
7. Version

Please ignore this message

Some dimensions don’t have security, so we can ignore them: Year and Period (Period has access to open and closed months, but this isn't done through security, so the tile of King Pedant remains safely on my brow).

But start paying attention here

Let’s look at the two simplest required dimensions: Scenario and Version.

Within Scenario, Actual will be read-only, Budget will be read/write.

Scenario

To give all of your provisioned planners (remember the first group and and inheritance) access to Actual and Budget, assign the topmost group to Actual, and give it read access. Do the same with Budget but make the access write.

Version

In Version, there are two members: Final and Working. Final gets the read-only setting and Working is set to write.  Again, use the topmost, first generation group to assign access.

That’s it, you’ll never have to worry about base dimensions again.  Note that security was done at a high level group (the highest, really) as access is the same for all Planners.

The big guns

Two required dimensions remain: Account and Entity

Chop the Accounts

When I build a Planning application with more than one Plan Type, I like to create upper level Account parents that segregate by Plan Type. This makes security and dimension builds as straightforward as possible. Yes, this does require extra dynamic calcs in the target (really, it’s the master) Plan Type to pull the XREF’d data from the source Plan Type(s), but I think it’s a small performance penalty to pay for clarity.  I reserve the right to bin the above approach if it doesn’t work for a particular application, dear client(s), so please don’t consider the above set in stone.

Mythical application example -- Account

To do this, name and order the Accounts like the below to split security by Plan Type:

Accounts

|--Wrkforce Accounts

|--Consol Accounts

NB -- Wrkforce, the source of employee expenses, is ordered before target Income Plan Type so that there are no forward dynamic calcs.

I can assign second generation groups to both Plan-Type-by-Account-parent assuming that all Workforce planners can see all Workfoce accounts and the same holds true for the Consol Plan Type.  Do you see the matchup between the upper level Accounts and the groups?
 

How do exceptions get handled?

More restrictive 
If a Workforce Planner did not have access to a single WorkForce Account, or range of accounts, apply his third generation group and assign None access.  This assignment on top of the second generation group Write access works because lower level, more restrictive, security will take precedence.

Access to both Plan Types 

If a Planner spanned Workforce Accounts and Consol Accounts, make his third generation group a member of both the Consol and the WorkForce second level groups. No need to create a special group just for that purpose – they grow like Topsy and quickly veer out of control.

Mythical application example -- Entity

Entity is the last of the required dimensions and it too must have security. Remembering that the default access is None, this is where the third level groups come into play as there (likely) is no general access to cost centers/projects/accounting units/etc.  Assign read or write access to Entity parents or, less optimally, to individual Entity members by third generation group.

Mythical application example -- Employee
This leaves Employee, a custom dimension in Planning-speak. Is this dimension to have security?  Probably. Can you get away with the same groups as used in Entity?  Almost certainly, as Employee dimensions have a habit of mimicking Entity dimension hierarchies.

The other alternative is to not turn on security in Employee, and let the Entity dimension access drive security. This is simpler and my recommendation.

Suggested naming conventions

Mnemonic names
Name the groups something that make a little sense.  You don't have to use my naming convention, but I've not found anything that makes more sense.

Names by generation

• Top level group that provisions access to the application and is used in the Scenario and Version dimensions: appname.
• Second level group for Plan Type access via the Account dimension: appname.PTname.
• Third level group for Entity access: appname.PTname.Entityname.
• BTW, the Entity in question would (could, but ain’t necessarily so) likely not be a level 0 Entity but some upper level parent. Take it easy on yourself and just tell your business owner it can’t be done. You’ll be thanked later when security management doesn’t take over the administrator’s life.

Real (as real as a sample in a blog) world example

Let us examine planner John Q. Public. A friendly sort of chap, with few vices (chiefly an excessive predilection for coffee), and several good points (he likes this brand), John is a planner in Entity 12345 in the Income Plan Type. 12345 is (yes, I said it was a bad idea, but it’s my example, so I do as I please) a level zero Entity.

How, oh how, does this mythical man of government forms get his security? (For non-North Americans – Canada, please note your inclusion by this Yankee because of the awesomeness of Tim Hortons, but actually I have no idea if John Q. Public means anything in the land of Timbits – John Q. Public is sort of the name of the man in the street.)

Steps in Shared Services

1. Create a Shared Services group (native, because my laptop doesn’t have MSAD) named PlnLCM.
2. Create two Shared Services groups: PlnLCM.Consol and PlnLCM.Workforce. Make both groups members of PlnLCM.Consol. NB – In the absence of any security overrides at this level, these groups inherit PlnLCM’s security.
3. Create a Shared Services group named PlnLCM.Consol.12345. Make this group a member of PlnLCM.Consol.
4. Make John Q. Public a user member of PlnLCM.Consol.12345.

Steps in Planning

As application administrator:

1. Migrate identities to ensure that the new groups are pulled from Shared Services if that hasn’t automatically happened already.
2. Perform dimension security as described above (PlnLCM.Consol is assigned to the parent Account "Consol Accounts", etc.).
3. Log out of Planning, and log in as John. Remember, this is a test id you created to prove that it works; real world users will be authenticated through LDAP or MSAD, and you are unlikely to know their password.
4. Note his restricted access – this is the payoff.

It gets better
When Jane Doe is to be added to Planning, you need only create an additional third level Shared Services group, make it a member of PlnLCM.Consol (if she is a Finance Planner), and assign her user name to PlnLCM.Consol.Entityname. As Jane (and John) are inexorably climbing the ladder of corproate success because of their Planning prowess (it could happen, ya just gotta believe), you, humble Planning administrator, need only move their usernames out of the third generation groups and move in new, soon-to-be-similarly-lucky Planners who are kicking down the door to ride the Planning elevator of professional success.

Wrapping it up 
As with most things in life, a little (and really, in the Planning world, the above has to fall into the e category of little) pain and planning deliver big results.

All of the above techniques would true for Essbase (had to bring it ‘round to the name of this blog sooner or later) and filters, except that instead of the Plan Types you will deal with databases.

So, not exactly a hack, and unfortunately, not exactly brief, but definitely a technique worth pursuing.  So maybe that kind of, sort of is a hack.

See you next time.

New Rail Cars Delayed, Fare Hike Promise Broken

I have bad news and worse news. The bad news is our new M8 rail cars are late in delivery. The worse news is that we’ll still be hit with a fare increase to pay for them despite promises to the contrary.

Back in February of 2005, Governor Rell told the legislature she wanted to invest in 300 new rail cars for Metro-North. To help pay for the cars she proposed a $1 per ticket fare surcharge -- to take effect after the cars were in service. Her promise was that (commuters) “should not be asked to pay for improvements until they actually see them, sit in them or park in them.” Those are her actual words. Remember that.

While the surcharge seemed fair, it wasn’t. A $1 surcharge on a $2 ticket would cost much more than on an $18.50 ticket. So the surcharge proposed was replaced with a series of fare hikes to take effect starting January 1st 2010… 1.25% that date and an additional 1% each January first until 2015.

The fare hike schedule assumed that the new cars would be in service by January 2010. But they won’t be.

While CDOT turned over the design and engineering of the new M8 cars to Metro-North, builder Kawasaki continued on its time-line. The first six “pilot cars” were supposed to be delivered August 2009. And a few M8’s were to be in service carrying passengers by December.

Now we hear that those prototype cars won’t arrive until November. Testing for the new cars will take four to six months, with the cars being put through their paces (mostly at night so anxious commuters won’t see them and wonder why they’re not on board).

Assuming the testing goes well (and that’s a big assumption with a new design such as this) it will not be until March, April or May of 2010 that the cars will be officially accepted by CDOT and Metro-North.

Then and only then will production cars be put into service. That’s three to five months after the fare hike has gone into effect. And while the new cars will arrive at the rate of 10 per month, it won’t be until August 2012 that the last of them arrive… again, assuming no production or engineering problems.

But what about the Governor’s promise that fares would not go up until commuters could “see or sit” in the new cars? There’s the rub.

Does seeing the test train running on our tracks fulfill the promise? Not to commuters who are riding in old unreliable cars often older than they are.

It may have seemed reasonable for the Governor to make such a promise in 2005 when the new cars were thought to be achievable by 2008. But that was an impossible dream given that Metro-North’s M7 cars for Westchester service took five and a half years to place in service. (In February 2005 I predicted this is exactly what would happen.)

Governor Rell didn’t break her promise. The legislature did. When they replaced her $1 per ticket surcharge with a fare increase, it became a matter of law, written into the 2007 budget. But now they seem unwilling to bear any responsibility for the Governor or CDOT’s over-optimism.

I asked one lawmaker who worked on the fare compromise if he could rescind the fare hike and keep the Governor’s promise. He laughed and said “no way”, blaming an over-zealous CDOT for being unable to deliver the project on time. “We have a $9 billion deficit to deal with,” he said. “This is the least of our problems!”

There will be public hearings this fall on the January fare hike, moot as they may be given the hikes are already written into law. And I’d expect that more than a few commuters will turn out to vent about politicians long on transportation promises but short on keeping them.

It should be good political drama and fodder for a few editorials, but nothing will change. The fares will go up and if we’re very lucky we might be riding in the new M8 cars by next summer. Maybe.

Woodstock on The Tappan Zee

This summer marks the 40th anniversary of the grand-daddy of all rock festivals… Woodstock. I was in my teens the summer of 1969, but couldn’t get off from my job to join the swarms of rock fans. But I did see most of them.

My job that summer was as a “temp seasonal” toll collector on the Tappan Zee Bridge, joining Westchester and Rockland counties across the mighty Hudson River.

There were two things I learned in that job: how to roll quarters and how to listen to the radio. The tiny booths lacked air conditioning, but I could bring a fan or a radio. My portable FM entertained me eight hours a day as I listened to both the music and the FM DJ’s… a job I eventually earned at WLIR after college graduation.

The FM stations were buzzing about Woodstock for weeks, and that Friday and much of Saturday, every kid in the tri-state area was heading for Yasgur’s Farm. Most weekends were pretty crazy in that job, because in those days tolls were collected in both directions… fifty cents north-bound and fifty cents coming home. (Today the toll is $5 roundtrip).

Busy as it was on summer weekends on that bridge, nobody expected a half-million kids would show up heading to Woodstock, especially not the folks at the Thruway. But after the rock fest was well underway, the Thruway brass realized the mobs would eventually be heading home, clogging the bridge. Because the music was expected to end late on Sunday, many of us temp-collectors worked overtime into the wee hours of Monday morning.

Late into the night we had five lanes open southbound, most of us enjoying some handsome overtime. But traffic was so light, they sent us home by about 1 am. I was due back in the booth five hours later.

Of course, the music didn’t end until early Monday, meaning that the usual morning rush hour carried as many burned-out hippies as it did business commuters. I remember one station wagon that pulled in to my lane, caked in mud up to the windows and stuffed with a dozen zonked-out kids. “Hey man,” said the driver with eyes that struggled to focus. “We don’t have any money” (to pay the 50 cent toll). “How about these instead?” That day, the Tappan Zee toll was an orange and a warm Coke.

Most days life as a toll collector on the Tappan Zee was a delight, as I was usually assigned the outside lane, also known as “the country club” because of its green vistas and views of the mighty Hudson River.

That far outside lane was also the site of experiments pre-dating the EZPass system, and I was a witness to many failed attempts at automating toll collection.

One such experiment involved fastening special permit plates to the underside of trucks, then running them through my toll lane at 30+ mph while an automatic camera mounted in the road snapped pictures of their permits. The system didn’t work.

After being transferred to the New Rochelle toll barrier on the New England Thruway, I learned about the “exact change” lanes. As folks threw their change into the basket, the coins went into a machine with rotating discs and holes the size of nickels, dimes and quarters. As the coins fell though the holes, their value was totaled and the driver could pull away.

What I didn’t know was the people threw more than coins into those baskets.

One day, while inside the booth removing change buckets, I heard a car stop in the lane outside followed by an ominous thump. Not the clinking of change, but a thump.

Imagine my horror as I watched an entire orange work its way down the change chute, hitting the rotating discs like a food processor, spewing orange juice and peel everywhere over the machinery, the buckets of coins and me.

Oh, for those days in “the country club lane” back on the Tappan Zee!

ParaTransit for more than just the disabled

Quick. What’s the most expensive ride in public transit? No, not rush-hour peak service on Metro-North. It’s ParaTransit… the door-to-door service for the disabled.

Transit districts are legally obliged to offer ParaTransit even though it’s extremely expensive and often draws complaints about poor service. Here’s the story.

In the 1980’s when planners from the American Public Transportation Assoc. would gather for meetings, there would be a swarm of demonstrators. Wheelchair activists would block their way, demanding access to mass transit. And why not?

In 1990 the Americans with Disabilities Act law gave them what they wanted… sort of. Buses would have to be equipped with wheelchair lifts. Key Metro-North stations were made “ADA compliant”. Even some subway stations in New York City saw elevators installed.

But some transit systems didn’t want to retrofit to carry the disabled. And even if they did, what about the blind or those who can’t easily get to the bus stop? That’s where ParaTransit came in. The ADA law mandates that door-to-door service must be available anywhere within three-quarters of a mile of a fixed route transit line.

The cost to the transit providers? Upwards of $25 - $30 per ride, with fares covering less than 10% of those expenses. But riders must book a day in advance and often share their ride on the “short bus” with others, hence the complaints. The disabled may be blind or unable to walk, but they’re far from silent.

While many felt they’d become second class citizens in the transit arena, Mayor Ed Koch complained that it would be cheaper to pay for cab fare for the disabled than pay for ParaTransit. And, in fact, that’s what one Connecticut town is doing.

Affluent Darien is already served by “Gallivant”, a door-to-door mini-van for both the disabled and the elderly. Passengers pay a suggested $5 per ride and must book a day in advance.

But in the town of almost 20,000 with 3,200 residents aged 60 or more, the Gallivant service is vastly under-utilized, carrying just 407 one-way riders in the last quarter. That’s only about seven rides a day because of limited hours and just one van.

Administered by the town’s Social Services Department, “Gallivant” is popular with many for rides to the doctors, for shopping or the Senior Center. But for others in town the pre-booking and stigma of riding the “short bus” keeps them house-bound.

So, using a new $15,000 state grant, the town is experimenting with offering half-price taxi vouchers for anyone aged 60 or older. Following a similar program in neighboring Stamford, the Darien plan is simple: just show up at Darien Town Hall, prove you’re a resident over age 60 and buy your half-price taxi vouchers. A book of five $5 vouchers (worth $25) costs $12.50.

When you’re ready to go, call Eveready Taxi (hopes are that other cab companies will join the program) and they’ll pick you up and take you where you want to go. Pay with vouchers and tip with cash.

The initial response to the program has been slow, but those who’ve tried it swear by it, not at it. The cab company gets more business, residents who can’t or shouldn’t drive get mobility. What’s not to like? Well, it seems some folks may be trying to scam the system.

The taxi voucher program is designed to give seniors and the disabled independence and spontaneity. Trips to the doctor, shopping maybe even the movies seem fine… and help local merchants.

But now the program is getting requests for half-price taxi rides to the airports… a $120 one-way trip! It’s one thing to give a senior mobility around town, but it’s a whole different matter to subsidize their summer vacation.

The Social Services Department is considering the request. But I hope people in need don’t get left at the curb when the funds run out because others gamed the system.

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.

Fix My Station... Revisited

Three years ago, the Commuter Council launched the “Fix My Station” campaign, calling on Governor Rell to fix the crumbling, dilapidated and often-times dangerous conditions at CDOT-owned rail stations from New Haven to Greenwich.

Commuters sent in dozens of photos of their stations showing exposed wires, mold and graffiti which we posted on our website. Months later, CDOT finished a careful station by station engineering analysis recommending not just repairs but improvements. Special funds were allocated by the legislature for the needed work. Then… nothing happened.

Once again, we’d identified a problem, studied it and issued reports… and assumed the job was done. Few, if any, repairs were ever made to stations. If we were to revisit the same stations today we’d find things little improved.

But then, along came the Federal government this year with its “stimulus money” to create jobs with “shovel ready” public works projects.

Regional planners moaned, caught in a classic “Catch 22”. Because they had not been allowed for decades to plan for work that wasn’t already funded, there was little work that was truly “shovel ready”… except, in Connecticut’s case, at the train stations.

Here’s what happened at one station… Noroton Heights in Darien… but is doubtless being replicated across the country with similar public works projects.

One of the problems indentified at Noroton Hts as far back as 2004 was two sets of crumbling concrete steps leading from the west end of each platform up to Hollow Tree Ridge Road. Each set of stairs contains 28 steps.

Town officials initially estimated the repair work would cost $225,000, and the repairs were ordered, pending accumulation of enough money from parking revenues from commuters to pay for them.

Then the CDOT got involved. Because the steps were close to the track and overhead catenary power lines, CDOT said that Metro-North “flag men” would be required to oversee the repair work. That would add $80,000 to the job.

Because of the delays and since building supplies were then in such demand, prices escalated and the final bid for the work topped $400,000.

Then, along came Uncle Sam. When the feds dumped billions onto the states, somehow Hartford decided that $1.6 million should be spent at the Noroton Heights rail station. This would mean that in addition to fixing the steps, platform canopies could be extended and the platforms themselves could be resurfaced… projects long dreamt of but never put to paper by planners.

But be careful what you wish for.

Because Federal funds were now involved, CDOT had to revisit the stair rebuilding to be sure the work met Washington’s standards, not just Darien’s or the state’s.
Now, $30,000 will go to CDOT just to administer the project. But because CDOT is now under-staffed thanks to recent layoffs and early retirements, they can’t administer the job. The work is delayed again and the stairs probably won’t be repaired until 2010, six years after they were first identified as needing the work.

Had the Federal stimulus money gone directly to the towns, work would probably be underway by now. Heck… with a crew of Boy Scouts, a few sledge hammers and some local contractors, the steps could have been fixed in one week summers ago!
Instead, the steps are still crumbling. Federal funds are not being spent. Jobs have not been created. And another summer construction season will probably be wasted.

The “Fix My Station” campaign seemed like such a great idea three years ago, but those were simpler times and I was probably naïve to think anything so important could ever be done so quickly and easily. After all, this is Connecticut.

Getting To The Airport

They used to say that “getting there is half the fun”. Whoever “they” were, they haven’t endured the challenges and indignities of air travel post-9/11.
Even getting to the airport can sap your strength, if not your wallet. Consider the alternatives.

A car service is certainly convenient. But at $160 one way to LaGuardia’ $170 to JFK and $200+ to Newark, getting to the airport can cost more than your air fare. (Mind you, these are the advertised rates, so I wouldn’t be shy about asking for promotions and discounts when you call to book.)

But car services aren’t just expensive, they’re also wasteful. Couldn’t solo travelers share a car with others in a “limo-pool”? Is one passenger in a Lincoln Town Car an efficient use of limited space on I-95?

How about Connecticut Limousine? Now there’s a misnomer! Since when is a bus or cramped van a limo? And try explaining that name on the receipt on your expenses to your company’s accountant. “Really boss… it was just a bus!”

On a few occasions I’ve actually rented a car at the airport, driven home and then dropped the car the next day in Stamford. A day’s car rental is about half the cost of a car service. OK… so call me cheap.

Some regular fliers hire neighborhood teens to drive their own car to the airport, drop them off and drive home, repeating the process on their return. That’s cheaper than a car service, but puts double the miles on your car.

My preferred airport transfer is in my own car. Airport parking is $33 a day. Not cheap, but certainly convenient. And nobody complains about my cigar smoking enroute to the airport.

Another alternative, believe it or not, is Metro-North. Get off at 125th Street and catch a cab or livery and you’re at LaGuardia in about 15 minutes. Future plans call for some Metro-North trains to travel over the Hell’s Gate bridge, through Queens and into Penn Station. That could be a great chance to add a LaGuardia station with shuttle bus service to the terminals. But it’s a rail link our kids might see in their lifetimes, not ours.

If you’re heading to Newark, definitely consider Amtrak. Most Northeast corridor trains stop at Newark Airport where a convenient connection to the airport monorail has you at the terminals in just minutes. The train sure beats the Cross-Bronx and GWB any day. And fares are as low as $23 one way.

The proponents of ferry service on Long Island sound keep tempting us with talk about direct water-borne service to LaGuardia, but I’ll believe it when I see it. The old Pan Am Water Shuttle (a high speed ferry) couldn’t make a go of it carrying expense-account business fliers from the Marine Air Terminal to midtown, so I’m skeptical that operators could fill ferry boats to Stamford and Norwalk. And do you really want a sea cruise in the winter?

Mind you, New York’s three airports aren’t the only choices. Westchester County airport offers non-stop jet service to many cities and offers a variety of major carriers including JetBlue. Bridgeport’s Sikorsky airport used to get you to such cities as Philadelphia and Newark, but service now is very limited and expansion of both of these airports is challenged by local residents. Hartford’s Bradley Airport offers another alternative, including low fare carriers like Southwest… if you don’t mind a two-hour drive to get to the airport, north of Hartford.

One reader extols the virtues of New Haven’s Tweed Airport where US Air flies to Philly where you can connect to most anywhere.

Clearly, the trip to and from the airport can start and end a trip on a very sour, and expensive, note. But with a little imagination, this summer’s vacation can end on a thriftier note.