Creative Load Rule #1 - Left Pad a Field with 0

OK I really wanted to call this Stupid Load Rules but that would be stealing someone else's catch phrase,
so just as we like to refer to bugs as features, I'll label this craziness as creativity.

Why do I call this crazy creative? Because I would never ever do this in a load rule, I'd do it in the data source or during the extraction. But another thing I'll never* do is pass up a challenge!

* Almost never. Can't seem to make this fine print any smaller!

So the challenge? The below was posted on the Essbase OTN forum:

I need to add a 0 to the left if the data is less than 3 digits. Is this possible to do it in a data load rule.

e.g. 99 , change it to 099. Some values will have 3 digits , there should be no change to those.

Believe it or not I actually killed two EAS servers with this load rule and I had barely done anything. Seems like an easily repeatable bug in EAS. Once I moved from 11.1.2.1 servers to an 11.1.2.2 server however I was able to proceed. It only took about 15 field edits and wasn't that much effort once the theory was devised.

So here was my theory:

1. Start with a text file with two rows (or more if you like)

32

469

2. Split the first character

3 2
4 69

3. Split the first character on field2

3 2 _
4 6 9

4. Reverse the order of the fields

_ 2 3
9 6 4

5. Join the fields (this is the step that crashed 2 EAS 11.1.2.1 servers. One difference when I did it on the 11.1.2.2 server though was that I joined the fields two at a time instead of all 3). This leaves us with 1 field again

23
964

6. Create a New field with text 0

23 0
964 0

7. Join the two fields
230
9640

8. Split the first 3 characters. We will now ignore the final field which is possible in a load rule

230 _
964 0

9. Split the fields again like in steps 1 and 2
2 3 0 _
9 6 4 0

10. Reverse the order of the fields (hold together EAS!)

0 3 2 _
4 6 9 0

11. Join them back together!

032 _
469 0

We now have what we wanted. Numbers with a left pad of 0. I didn't really consider single digit numbers but then the poster didn't specifically mention that and I know it could be done if I've done it with 2.

Here is the proof (may not match above steps exactly but it should look similar to this).

It certainly doesn't beat the 5 seconds it would take to do in SQL but it was definately more fun! Unfortunately I don't have access to restart those two EAS servers until the morning.

Happy Essbase-ing!

OSDOTW #3 - Defects Fixed Finder

Yes it's that time again, time for my odd-monthly Oracle Support Document of the Week column.

A quick update today to point people to the '"Bugs Fixed Finder". Not the most creative name but it certainly highlights what it does.. and what better cause for a name. Perhaps I can apply the same logic to my first born, I'm sure many parents would be all too keen to share their suggestions.

From the horse's mouth:

This new tool provides an efficient way to review the defects fixed in patch set updates, patch set exceptions, and patch sets for major releases, starting with Release 11.1.1. The tool helps you plan patch implementations by providing concise descriptions of defects fixed after your current release. The Defects Fixed Finder enables you to easily find information about defects fixed for each EPM System product.

Essentially it works like the Cumulative Feature Overview Tool; you select the product(s) you are interested in and then you select your current release. The tool will create a new page detailing all bugs that have been fixed including the release & patch number since your current installation.

One of the best things about this is it makes it easy to find out what bugs exist before running into them yourself. It is oh so painful when you spend half a day trying to make something work that was never  ever going to work in the first place (even worse when you find out it is an unpublished bug though).

So go on, check it out at the Oracle Support page for the EPM System Defects Fixed Finder (doc ID 1292603.1).

OSDOTW #2 - RIP Classic Essbase Excel Add-in

I noticed this on Oracle Support today.

Statement of Direction

Oracle Essbase Spreadsheet Add-in is an option to Oracle Essbase and is an Excel Add-in that allows end users to perform ad hoc analysis against Essbase data sources.
After of January 1, 2013, Oracle Essbase Spreadsheet Add-in will no longer be available for general distribution with new releases of Oracle Essbase.  Releases prior January 1, 2013 should be considered a terminal release for Essbase Spreadsheet Add-in and customers should make preparations to migrate to Oracle Hyperion Smart View for Office.  Existing licensed customers of Oracle’s Essbase Spreadsheet Add-in will continue to be supported per the terms of their maintenance agreements with Oracle. 

The Oracle Hyperion Smart View for Office (Smart View) is the strategic Oracle direction for office add-in for EPM and BI.  Oracle Smart View has a flexible open architecture that enables it to support all of the EPM and BI offerings from Oracle. This provides the basis to extend the feature set and richness of analytics against Essbase when compared with Oracle Essbase Spreadsheet Add-in and to offer compatibility with later versions of the Microsoft Office Suite.
Release 11.1.2.1.102 of Smart View added significant features for users consuming Oracle Essbase data sources. Changes were also made to Essbase and Anayltic Provider Services (APS).  As such to get the benefit of the new Smart View features Essbase and APS need to be upgraded as well. Oracle encourages customers of the existing Oracle Essbase Spreadsheet Add-in to adopt Smart View release 11.1.2.1.102 or above, along with Essbase and APS upgrade to a corresponding version, for advanced analytics with a richer experience.

Source:  Oracle Essbase Spreadsheet Add-in Statement of Direction [ID 1466700.1]

I, like many others, have many fond memories of the speed and power of the classic add-in, especially pre Office 2007. It is great to see Smart View now on par feature-wise with the classic version though and there is no doubt a more feature rich, new-user friendly add-in was the way forward.

I'll be moving out of a consulting role to an internal position very shortly working with 11.1.2 onwards so for me.. goodbye classic add-in!

OSDOTW #1 - EPM Configuration Information

As promised, here I am after my brief hiatus in Korea to bring you the first of my regular blog postings on hidden treasures from the Oracle support vaults.

Oracle Support is huge and typically as a user you will go there when you have a need for specific information. My goal in this series ('Oracle Support Document of the Week') is to highlight interesting and informative articles in the Oracle Support vaults that readers would otherwise probably not come across. It may or may not be weekly, I may just have liked the name, but it will certainly be a regular piece so stay tuned!

If you haven't already you can subscribe or follow my blog using some of the RSS feed and follow links at the bottom of the page.

As a consultant, troubleshooting is an everyday necessity. Whether it be recent implementation or something someone from some other consulting company did quite some time ago, one of the keys for both ourselves and the customer, is in identifying the issue and determining what it is going to take to resolve. The first few of my postings will most likely be around basic troubleshooting and configuration.

So without further ado here is my first posting which is actually a collection of three articles ('buy 1 get 2 free' or as I learnt after a very confusing conversation in Korea, "1 plus 2"!).

Locations of Major Configuration Information

• Locations of Major Configuration Information in Oracle Hyperion System 9.3.3 [ID 1313155.1]
• Locations of Major Configuration Information in EPM 11.1.1.3 [ID 1449061.1]
• Locations of Major Configuration Information in EPM 11.1.2.1 [ID 1449103.1]

These documents provide quite comprehensive listings of configuration details as well as a decent description of what each item actually is. One of the great things about Oracle Support is that there seems to be a renewed focus on meaningful and useful documentation and these articles are no exception.

Enjoy!

- Daniel

OSDOTW - Oracle Support Document of the Week!

 I'm often snooping around Oracle Support and there are certainly a good number of articles worth-reading buried in there. Problem is, finding things is often about getting lucky. By that I don't just mean that things are necessarily hard to find, but every now and then you come across a gem of an article that you would never had thought existed or intentionally tried to find.

I don't aim to necessarily provide a significant amount of commentary on these documents but rather provide a medium to make some of the more interesting stuff visible.

My goal is obviously to keep this as a regular posting and believe there should be enough 'good finds' to do so. Of course I'm writing this post 2 weeks before I go AWOL for almost 4 weeks but I promise to get right back into it! I think I also just like abbreviations. Stay tuned for OSDOTW!

Oracle Webcast - Hyperion Planning Performance Pitfalls

Oracle have an upcoming webcast titled ''Hyperion Planning Performance Pitfalls". It is scheduled for Thursday the 4th of April (or early morning on the 5th for us 'down-under') and will go for an hour.

Updated Notepad++ Styles (plus MDX style)

I have made slight tweaks to my Notepad++ styles over time and wanted to share the latest version. In addition I have also created an MDX style but have not tested it significantly yet as I don't write much complex MDX.

I have found the CalcScript user style extremely useful of late as I had to debug some (over) complicated IF statements and ended up finding that they were not doing what was expected due to things like missing ENDIFs and IFs instead of ELSEIF statements which Essbase doesn't necessarily pick up (at compile time or run time). Since Notepad++ allows you to set up collapsible sections (e.g. between IF and ENDIF) it makes it much easier to navigate.

As before, the easiest way to install them is to do the following:

1. Open the View menu
2. Select User Defined Dialogue
3. Click Import button
4. Browse to and open the downloaded xml file (do this for each, one at a time)

Click below to download:

CalcScript User Defined Style: CalcScript.xml
MAXL User Defined Style: MAXL.xml
MDX User Defined Style: MDX.xml