Want to know The Truth About CPM?

26 October 2010

Stupid Programming Tricks #4


This is number four in my series of short tricks and tips. 


I debated about the utility of this until I spent an hour trying to figure out how to search MaxL scripts with Windows Explorer’s Search function.


The fix is easy and oh so useful.

What am I trying to do?

Let’s say I want to find all of the MaxL scripts that use the “spool” command.


If I searched all of the .txt files in a given folder, Explorer’s Search function would give me a nice list of files that contain the string “spool” in the file body. 


To prove this, I renamed a MaxL file so that it has an extension of .txt.


Here are the results:


But if I search the same folder for the same file with a .msh extension I get this:


Of course, I could:

1)    Find every MaxL script (in my world, they end with a .msh, and yes, that is important) in a given folder, harddrive, computer, etc.

2)    Open up every one of those MaxL scripts and search within for “spool”.

3)    Do an Oedipus Rex and take out my eyes with knitting needles in a completely non-Freudian way because of the despair, ennui, and avoir le cafard that steps one and two engender.


But there is a Better Way.  The cockroaches of Sidi-Bel-Abbes will thank me for that one.

Don’t fear the Registry

Go to the Start->Run menu and type regedit.


A key is required

If you’ve already changed .msh files in Explorer to open with Notepad or my personal favorite TextPad, this step isn’t necessary as  the main key will exist in the registry.


Add a new Key:


The Registry Editor will stick this new key at the bottom of the list – don’t worry about the order.


Rename the selected text to .msh and hit Enter.


Tell Search to look inside .msh files

Add another new key to the .msh key – I guess you could call this a subkey – by right clicking on .msh.


Here’s what it looks like:


Now give that key a default String value:


It is not going to be set by default.


Right click on (Default) and select Modify.


Now we need to give the Default key a very specific value.  Just copy and paste this into Regedit:




After you click on OK, you should see the following:


Log off and log back on; rebooting shouldn’t be necessary.


Search now lets you look inside .msh files.


Not a big deal, or the world’s best hack, but it sure is a nice way to search for a string within a non-standard file type.  Of course you can extend this technique to .csc, .rep, and .whatever files you need to scan.  Useless 99% of the time, but when you need it, you need it.


Happy hacking till next time.

21 October 2010

Who will rid me of this turbulent bug

It’s déjà vu all over again

Yep, life is sometimes like a Yogi Berra saying.  That’s scary.

I just rolled off a Planning migration from h-e-double-toothpicks.  I am reminded, again, that I am an applications, not an infrastructure consultant.  For some strange reason, I seem to enjoy parading my serial infrastructure incompetence to all and sundry via this blogDirty Harry said it best.  I am embracing my limitations with renewed fervor.

My pain=your gain

In an effort to ensure that this particular problem doesn’t bite you, oh applications consultant/administrator reader, in the unmentionables, think back, far back to the long-ago days of Planning 2.2.  Was there a release with that number?  Oh yes, and even before that.  I have been around Planning a long time.  So why have I learnt so little?

Moving past questions that cannot be answered (or at least questions that have answers I do not want to hear), there was a problem in older releases of Planning – ephemeral port consumption.  No, that is not a Victorian-era disease that involves sanitariums and bloody coughs.

Why do you care and what are they?

The issue is that when Planning refreshes filters, it consumes ephemeral ports during its communication with Essbase.  When the OS runs out of ports, Planning filter refreshes fail.

What does it look like?

The symptoms

What should have tipped me as to the error was that with 100 users in the app (I got pretty darn good with the Planning importsecurity.cmd/exportsecurity.cmd utilities) the refresh would work.  The fact that the command line syntax for invoking the import and export utilities is completely different was just a dollop of Hyperion icing on the misery cake.

Getting back to what worked and didn’t, the filter refresh would work with 300 users in the app.

As the number of usernames increased (I was slowly adding known good MSAD usernames) to just over 600, at some point (and no, I never did get to the actual count that just tripped failure as I was adding in groups of 50) Planning would fail on the refresh.

I (and quite a few others) spent a lot of time trying to figure out if the MSAD ids were bad (some were and “bad” in MSAD means a bunch of different things, e.g., corrupted, locked out, etc.).  But that wasn’t the issue.

Should have paid attention, but didn’t

What really threw me is that as I did the refresh, I’d get a pretty consistent list of failed usernames.  However, when I selected those usernames individually, their refresh would work.  Huh?  Also, these same ids worked in other Planning apps.  Huh, again.

And the answer(s) are

I would love to tell you that I came up with the diagnosis and the cure to this filter refresh failure, especially because I suffered through this in 2002, but I must give credit where it is due – say hello to Jason D’Onofrio who went into Metalink and started searching for an answer.  Why would anyone want to search the help?  If you don’t fancy my preferred diagnostic method of blindly poking around you too can search Metalink for knowledge base article 826673.1.

And the thing of it is, Tim Tow has documented this error and its fix for, oh, forever, maybe?  A long time certainly.

If you can’t be bothered to read any of the explanations, here’s the quick and dirty Windows fix (the same issue affects *nix, but not very much and while the concept applies to that OS, the mechanics below do not):
1)    Go into Windows Registry editor on the Essbase server.
2)    Navigate to the following key:  HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
3)    Right click and select New or Edit->New and then select DWORD Value.
4)    The name should be MaxUserPort.
5)    Right click on the new “MaxUserPort” and edit the DWORD value. Enter a decimal value of 65534.  You have just increased the number of ephemeral ports to their maximum value.
6)    Again create a new DWORD Value.  Call it TcpTimedWaitDelay.  Set it to a decimal value of 30.  You have now decreased to the minimum the time Windows will take to release a port.
Your registry settings should look like this when you’re done.
7)    Reboot the Essbase box after stopping your various services – you know the boot order.
8)    After starting the Oracle EPM services back up, try doing a refresh.  You should have bottled magic at this point.

NB – The Metalink instructions go on about adding MaxFreeTcbs and setting that the decimal value to 6250.  That wasn’t necessary in my case.

Why might you not see an error?

Maybe the registry settings are already there and you don’t know it. 

Maybe you have small user communities and you never blow through Windows ephemeral ports.

Maybe you just can’t believe that this issue exists almost a decade after Hyperion Planning 1.0 was released on an unsuspecting world.

Maybe you’re on 11.1.2 and are using Windows 2008 which has a larger ephemeral port range.  Yes, despite Essbase.sec’s almost complete emasculation in this release, filters are still stored in good old Essbase.sec.

Maybe you’re running some version of *nix.

Maybe you’re just lucky.  :)

Phew, this is a problem I never want to revisit.  Thanks again, Justin, for finding the answer.

09 October 2010

Stupid programmng tricks #3


Number three in my series of short tricks and tips.

This is a case of misleading documentation.  Or a stupid programming trick.  You decide.

The Most eXcellent Awesome Language (MaxL) maybe isn’t

display variable all ; will display all variables on your Essbase server.

display variable on database Sample.Basic ; will display all of the variables in My Very Favorite Essbase Database In The Whole Wide World (MVFEDITWWW).

But what if you have a variable called Test defined in MVFEDITWWW? 

Here's the syntax from the Tech Ref:

You might think that these commands would work:

display variable test on database sample.basic;

display variable 'test' on database sample.basic;

They don't and will kick out an error near 'on'. 

The actual syntax is:

display variable sample.basic.test ; 

Do so and you will get back:

 application    database    variable    value


 Sample            Basic        Test        "Jan"


You could also type display variable othertest ; and get back the results if the variable was at the server level. I think otherwise you have to start using the appname.dbname.varname nomenclature.  I believe (and I haven't tested this, but it seems reasonable -- hey, this gives you something to do if in case you are casting about for something to play around with) display variable appname.varname ; also works.


I’ve named these short posts “Stupid programming tricks”.  Surely this post fits the description.  Until you can’t figure it out.

Happy MaxL hacking till next time.