I’ve been working with Hyperion Planning since Version 1, and have seen it grow and mature over the years. Beginning with the CapEx and Workforce Planning extensions, Oracle has created application “frameworks” for specific use cases of Planning.
Hyperion Public Sector Planning and Budgeting (PSPB) is one such framework for government agencies. It adds specific public sector functionality for position and employee budgeting models, and customized Financial Reports.
My experience with PSPB has surfaced several key considerations for implementing this Hyperion Planning module:
##Smart Lists PSPB comes with about 25 pre-defined smart lists. Some of them are of the typical Planning/Workforce Planning variety (yes/no, employee type, employee status), but others are much larger (entity segment, job class, union code, etc). Before implementing them out of the box, consider whether they will be used in a reporting or analytic capacity.
For example: Is reporting by union code with subtotals required? This generally means you’ll end up using the FR Planning connection and some kind of custom FR formula. Do you have to allocate or spead costs to job classes? That task is much tougher in a business rule or calc script when you’re working with smart list index values stored in an Essbase account. In cases like these, you may be better served with a stored or attribute dimension.
You’ll get better reporting flexibility and faster calcs that are easier to write and to maintain.
##Reporting Cubes The module isn’t built for aggregation, so assume you’ll be working with an ASO reporting cube for reporting. If you’re doing a straight export/import style load to your reporting cube, this is a pretty easy undertaking. As with smart lists, however, you will need to consider your reporting requirements up front. You can map a reporting application and push data from a Planning cube right to a reporting cube with minimal effort, but be ready for the following limitations:
- Smart list to dimension conversion: Works well, but assumes your smart list index values are at the same intersection as your core data. This isn’t the case out of the box. Most smart list values are set up at plug or default intersections (e.g. No Year->Unspecified element->unspecified budget item) so if you set up a straight smart list to dimension conversion for something like job class, you’ll wind up with the default values. You’ll have to line up the index values with the data by either setting up a account with a member formula (=Job->No Year->Unspecified element->unspecified budget item) or doing a data copy before you push the data.
- Translations: You can’t do them, so if you need to add a prefix or a suffix, you won’t be able to do it.
- Loading Data: You won’t be able to see the intermediate translation files that are loaded to the reporting cube. By default, all you’ll get is a success or failure message. During development, you’ll probably want to see the joined file if you’re doing smart list to dimension translations. To get that, put Planning in debug mode and the files will be left in the temp directory.
##Calculations Like Workforce Planning or CapEx, there are several pre-written calcs out of the box. Expect to modify them to some degree. They’re pretty detailed scripts, so plan on having an Essbase resource on your project.
You’ll definitely have to modify the calculations if you have more than 4 segments coming in from your GL or if you want to rename those segments. In addition, if you want to allocate anything other than expense dollars (e.g. FTEs or headcount), you will have to modify the calculations.
##Tuning Like all other Planning apps, there is a BSO cube behind the scenes. You can do all of the standard cache, outline and calc tuning, but be careful of dimension order. Changing the order of dimensions may break the out-of-the-box calculations. For e.g. when I made an order change, I broke the AllocDistCost business rule - and got a generic Essbase error with no detail in the log files.
##Stage Data in a Relational Database In my opinion, this is a best practice and should be implemented universally. The benefits far outweigh the setup time:
- No problems with data translations
- More control over the data
- Integration with ETL tools
- OBIEE and BI tool ready
- Integration with monitoring and automation software such as STAR Analytics
- More control over extract and load operations
- Serves as a de facto backup of your data
- RDBMS skills are readily available in every organization
If you are seeing issues implementing PSPB, leave a comment below and I will respond if it’s something I have seen.