Friday, October 1, 2010

What is the most overlooked task on Hyperion Planning projects? Security.

Building security.

In the throws of a Hyperion Planning project, everyone is quick to want to get into the system to see and touch it. Sometimes this leads to the admin id / password getting out the door. Or almost as bad, all the folks on the team, including business users, getting provisioned the administrator security role.

This might seem like an ok idea on day one but no one ever comes full circle to test security from an end user perspective until that fateful day, UAT (user acceptance testing).

Here's why security cannot be slapped together in an hour or 2.

-reports that were developed without using a "secured" test user id WILL NOT work when using a "secured" id. In Hyperion Planning, any id other than admin cannot see data at the top of Entity, Scenario, or Version. Reports developers usually do not know this as their background might include writing reports directly against Essbase (not an issue here) and not Hyperion Planning. All too often when the army of report writers (IMO the customer should write the reports with consultants assistance) are brought in, they somehow acquire the admin id or privileges to get their report development moving. They get their 50 reports done and roll off the project. Then during uat (if security is actually tested), none of the reports works. Now you're left scrambling to "fix" these reports with the original developers long gone.

-properly setting security so a web form enables write access is not a small feat. Each of the "delivered" dimensions entity, scenario, and version first have to have members opened up (through "meta data" security assignment). This applies to administrators as well as users. Allowing administrator access for testing is only a crutch that will get you in the end.

-sometimes when looking at the security matrix it's determined that the entities dimension does not allow for the proper security to be applied and changes must be made to the entities dimension. This is not an easy thing to do at the end of a project. All business rules need to take into account any changes in the entity dimension as well as reports that have been developed.

-web forms that used to work under the admin id don't work with a secured id because the business rule security has not been assigned. Business rules have to have their security assigned for secured id's as well. Since business rules are usually set to run on save without interaction from the user, it can be a difficult issue to resolve when users report that a form is "busted" and give no further detail.

Security can be a very complicated matter. Not enough thought is given to the design of the security matrix and not enough time is dedicated in the project plan. Every project has to address building security in Hyperion Planning.

I will outline how I address security in a later post or on my website.

Like most everything, proper time needs to be taken in order to match the security requirements with especially the makeup of the Entities dimension.


Wednesday, March 31, 2010

Essbase licensing: no fee for additional Essbase servers, really?

This is the greatest news I can remember about licensing for Hyperion since Oracle started giving away Integration Server and the Essbase API. It turns out that one of my current customers was able to add another production Essbase server without paying any additional licensing fees to Oracle. So it finally looks like there is a benefit to the licensing model that years ago took away concurrent user pricing. It looks like Oracle is satisfied with charging a per user fee so they no longer will charge for additional production servers and development servers. Do you remember the day's in which a development Essbase server costed in excess of 10k? Now we are just waiting for Financial Reporting studio to output directly to Excel workbooks.....

Check with your Oracle sales rep. The details / paperwork is a little fuzzy as to which customers can add as many Essbase servers as they want for free. Chances are if you've recently purchased / upgraded your Essbase / Hyperion, this is now an option. So if you're currently running version 11 this is likely to be the case.

Rumor has it that Oracle EPM 11.1.2 is coming out very soon....


Tuesday, March 2, 2010

As a Hyperion administrator what skills should I be learning....

In working with Hyperion for the past 15+ years, I've seen a lot of clients and a lot of system administrators. I've even spent significant time trying to backfill my administrator role at various clients as well. You might be surprised at how many folks are out there that say they can run Essbase but they have never written a calc script. There is a great difference between maintaining a Hyperion system and developing a Hyperion system from scratch. In order to make yourself more valuable you need to really understand what's going on under the covers. I've even advised a few business analysts that focusing their efforts on really understanding and learning Essbase would result in a bigger bang for the buck than starting the process of gaining an MBA. In both cases they were able to double their salary in the course of 3 years by learning Hyperion and switching employers without incurring +10,000 in student debt. Usually folks involved in Hyperion at a client don't have any time to goto night school anyway. The right move I believe is for a company to figure out a way to keep this talent. So much business knowledge is learned from creating these systems that it is a real shame for it to go out the door.

Anyway, some things that you should do to make yourself more valuable in the Hyperion arena include:
-fully document your Hyperion Systems and identify / train your backup resource. Go download the outline extractor and export all your outlines to audit all the member formulas in your databases. Many administrators out there have a really tough time taking vacation and an even tougher time taking time off for training as the system seems to stop running when "Bob" is not here.
-devise an issues log to help track all the fires you are putting out. When it comes time for yourself appraisal, you need to have proof of the amount of work your are performing. Saying you are just solving the needs ("keeping them happy") of finance is not tangible. Despite your best efforts it's not possible to keep users happy. By keeping a log you can better explain the amount of work you are performing and this can also give you credibility when you come to the realization that you need to get another head hired to help you out. Logs also help you prioritize and keep track of all the issues. "Prioritize" - the secret that allows IT folks to go home at 5:00pm every night when business folks are always getting stuck working late. It seems on the IT side that resources are always limited while on the business side there is no cap on how hard people work to complete deadlines.....
-learn Essbase VBA to figure out how to automate bulk loading of spreadsheets into Essbase and to streamline other business admin tasks
-see what else VBA can help out with. You can easily create a menu based interface in Excel for report generation.
-understand when web based reports should be used instead of Excel for reporting creation and distribution
-recreating your applications from scratch in development. In so doing, understand the decisions that were made for your application and challenge yourself on how to improve the model.
-reviewing your cube update process and automating every step possible (stop touching the outline by hand when there are more than 1 to update, make a dim build file and run it through batch dimension builds)
-review your data loading process and try to load your data from a relational data store (instead of loading from files, get these files loaded to some kind of sql data store) and perform your etl (scrubbing of your file, adding prefixes, concatenations, etc) in the sql and not the load rule (load rules are disposable and sometimes get corrupted when upgrading. These also are not self documenting).
-learn what esscommand and maxl are (command line based scripting used for automating Essbase administration)
-learn how to perform allocations with calc scripts (spread a value stored at the top of 3 dimensions down to level 0 based on a populated account)
-if you have any calc scripts running, make sure you have validation workbooks to audit every calc that takes place in your models
-review your scripts (might be Essbase only, not sure about HFM) and review the benefits of inserting substitution variables to make your scripts more dynamic
-if you have not created an aggregate storage database do so. If you don't know what an aggregate storage database is then read up on it.
-if using Hyperion Planning review your business rules and web forms to ensure the user experience is as streamlined as possible (not a list of 12 steps of business rules that must be run in order). A user should not need to know how to run more than 1 or 2 business rules. These should all be attached to web forms that run upon save without requiring the end users launching these in the tools->business rules menu.
-also, if you're using Hyperion Planning, go through the effort of configurating and using the application copy utility in System 9 or learn how to use the life cycle management tool of system 11.
-get 2 virtual machines setup, one for windows xp (easy one) and another for windows server 2003. A virtual machine allows you to run different versions of software on your desktop. These are like mini machines that run on your desktop. You can now switch between running Essbase 6.5, 7.x, 9.3.3, and 11.1.3 on your pc. You'll actually need to "fire up" (a vm takes up about 10gig of space on your hard drive and takes a few minutes to start) your vm and probably not run them concurrently. But, VM's can be very valuable in testing compatibility of Hyperion with different versions of IE and Excel.
-install hyperion software on these vm's. There is plenty of help out there on the internet how to install system 9 and more recently system 11. You can install Essbase System 9 on a Windows XP client vm (shared services, essbase, bi+ (workspace).
-learn ODI as this is the replacement for HAL. While this will be a necessity for Planning, ODI can also come in handy for working with Essbase.

ps. If you think you're ready to branch out into the traveling consultant role let me know. I know some pretty good consulting firms to work for. If you think you would rather go independent drop me a line and I'll try to talk you out of it. Some folks have a hard time saving their acorns for the winter months.


Saturday, January 23, 2010

Hyperion - lets focus on infrastructure client story 3

This is one of my favorite clients. It's perhaps one of the typical Hyperion Planning environments. The business folks are running the Hyperion Planning environment. IT at this client site is there to help. The don't put up roadblocks or resent having Hyperion in the building. Well, I last visited this client about 2 years ago. Since that time they have restored using my directions more than a handful of times. It turns out that in the quest to be super responsive to the business environment, a couple of "oops" happened. The planning app ended up getting stuck so they made a copy of the Essbase app and have been running that for a few months. I recieved a call from their IT asking how to get this new Essbase app to show up on the planning web login screen.

Well, I thought that I preached too much when I'm at a site about what Hyperion Planning means to Essbase, but apparently not in this case. Hyperion Planning maintains the outline structure of Essbase. Virtually all outline changes must run through planning first then get pushed to Essbase. In this case they restored the Essbase outline not the entire Hyperion Planning app. The answer to their question is "no. This Essbase app cannot be made available through the planning log in screen". The resolution is that the planning app needs to be rebuilt from the ground up. It turns out this is acceptable to the client as they were ready to rework some dimensions materially. They were also able to function because the extensive business logic in the business rules can be run against native Essbase as well as Hyperion Planning.
Lessons learned:
-development environments are very important. Even if you don't have a development server, at least make a copy of your production app for testing different business requirements.
-always make changes to the outline structure in Hyperion Planning then push them to Essbase via a refresh.


Hyperion - lets focus on infrastructure client story 2

This client is one of the more controlled IT environments in which I've worked. There is proper change control. The business folks have had until recently only read only access to the server. Essbase is running on Unix. There is a full time DBA (contrator 3 years). Sounds pretty good. Well the day came where I needed to restore data from 9 days ago. My typical environment provides the Essbase admins to recover the last 7 days of data and Essbase objects. This set of backups was working properly but I just needed a little bit older data restored. Since we had been in production for a few week, I assumed everything was being backed up. My Essbase admin also believed this. When the call came to restore from tape, it came to light that somehow there was an exclude on the data directory we were using for my Essbase server. So, the good news is that this came to light before a really critical restore was needed. It just caused me 2 days of headaches that resulted in having to assist in validation of data that would not have been required had the restore been functioning.

Thursday, January 21, 2010

Hyperion - lets focus on infrastructure, client stories....

I've just had 3 clients experience extremely painful outtages due to the ignoring of their server environment.

Client 1: When I last left this client, microsoft mom alerts were running so anytime the server was bounced we (I) would receive an email. This is one way to keep your IT honest. I can't tell you the number of times that IT bounces and touches the Essbase server when they shouldn't be. All produciton outtages should be scheduled, no exceptions. In addition, alerts were running that would notify the business admins of Essbase (business side of the fence) when the hard drives approached 80% capacity. The Essbase hard drive filled up and there was a crash. I got contacted a day later. 3 of 15 apps would not start. I arranged to remotely restore the files from tape if they could place the tape backups in a folder. Guess what? Before this series of outtages from 3 clients at the same time, I would have guestimated a 60% success rate of recovering from tape backup. Now my educated guess has dropped down to 40%. The files they placed in the folder were missing some important files (app, .ind, .pag to name a few). After 8 weeks, I am still battling with this client to ensure backups will be usable. The problem is that the business users had their original data loads scripted and were able to quickly recover. No lesson has been learned yet. I just attempted another restore and their IT still could not replace all the necessary files. This is in an environment where I am giving IT lights out of Essbase for 5 hours a night.