Friday, 15 August 2014

EPMA - SQL Query to get Hierarchy in Parent Child format (+Bonus query)

There was some discussion on the Essbase discussion board about how to build a query that would return the EPMA hierarchy in parent/child format.

Not one to refuse a challenge (I actually can't) I had a go and produced the following using Oracle SQL:


    ROWNUM AS OrderId 
  FROM (



        a.I_LIBRARY_ID = d.I_LIBRARY_ID AND    
a.C_APPLICATION_NAME='Master' AND -- Application name here. master = shared library
d.C_DIMENSION_NAME='Period' -- Dimension name here
) MemebrIDs

) Hierarchy 

Period D-T-D
Period W-T-D
Period M-T-D
Period Q-T-D
Period P-T-D
Period S-T-D
Period Y-T-D
Period H-T-D
Period BegBalance
Period YearTotal
YearTotal Q1
Q1 Jul
Q1 Aug
Q1 Sep
YearTotal Q2
Q2 Oct
Q2 Nov
Q2 Dec
YearTotal Q3
Q3 Jan
Q3 Feb
Q3 Mar
YearTotal Q4
Q4 Apr
Q4 May
Q4 Jun
Period [Year]


This query uses the CONNECT BY clause to build a 'parent, child' resultset based on the top level member and some input parameters. Once I've built the parent/child hierarchy based on the member IDs I then go to the member tables to look up the actual member names. There are joins on the dimension and application tables to ensure we get the correct results as a member can exist in multiple dimensions and a dimension can exist in multiple applications.

The input parameters are  the following (i don't think there is a need to change library_id)
  • C_APPLICATION_NAME - The name of the application you want to use. Put 'Master' here to use the Shared Library
  • C_DIMENSION_NAME - The name of the dimension you want to use

Bonus Query - List all EPMA apps and how many times they have been deployed

 This code works in Oracle. I might update the post with some MSSQL code later (or something that works in both)

SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8) AS AppName,
e_job_status = 'Completed'
SUBSTR(c_descr, 1,8) IN ('deploy :','redeploy')
SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8)

Post in the comments if you have any feedback or problems with the queries as it is possible there are strange EPMA circumstances that require special logic that I didn't encounter testing the above.

Update: Fixed/updated the bonus query and prettied the SQL slightly.

Wednesday, 18 June 2014

Reporting on the Planning Job Console

I have recently been exploring various back end databases and tables to expose logging and processing details to an OBIEE dashboard.
I hope to blog on these various areas one at a time with the first being the Job Console for a planning application. 

There isn't a whole lot of interesting information or complexity around the job console data. There is a single table in the planning schema's database called HSP_JOB_STATUS. Note that this is the specific Planning application's schema and not the Planning schema itself that the product is installed into.



The columns in HSP_JOB_STATUS are as follows (details including comments taken from Oracle schema, not mine):
  • "JOB_ID" NUMBER(*,0) - (PK) ID for the job'
  • "PARENT_JOB_ID" NUMBER(*,0) - This is the id for the parent job. If the rule is launched as a part of a rule set, this will have the ID for the rule set job. RTPs will have the ID for the rule to which they belong.
  • "JOB_NAME" VARCHAR2(80 BYTE) - Name for the job
  • "JOB_TYPE" NUMBER(*,0) - This is the type of the job being executed.
  • "USER_ID" NUMBER(*,0) - FK(HSP_USERS). This is the id for the user who executed the job.
  • "START_TIME" TIMESTAMP (6) - Job execution start time.
  • "END_TIME" TIMESTAMP (6) - Job execution end time.
  • "RUN_STATUS" NUMBER(*,0) - Current status for the job
  • "DETAILS" CLOB - Job details
  • "ATTRIBUTE_1" VARCHAR2(255 BYTE) - The information stored in this property varies by job type. In case of rule, this stores the application against which the rule is being run. In case of RTPs, this stores the parent rule/rule set name.
  • "ATTRIBUTE_2" VARCHAR2(255 BYTE) - The information stored in this property varies by job type. In case of rule, this stores the plan type against which the rule is being run. In case of RTPs, this property stores the RTP prompt text.
  • "SERVER_ID" NUMBER(*,0) - ID of server who created job, use to track orphan jobs
  • "PARAMETERS" VARCHAR2(2000 BYTE) - Parameters used in job
Unfortunately there isn't documentation on the possible values of JOB_TYPE or RUN_STATUS however we can make some observations.



I don't think I have obtained all possible values for this column yet however the following lists those that I have:

1 = Business Rule
4 = Variable
5 = Business Rule Ruleset



1 = Job is running
2 = Job completed successfuly
3 = Job failed
4 = Job did not start -

I have made an assumption on RUN_STATUS=4 based on 2 entries in an application I used for sample data. Whatever the exact meaning of this value, it appears to be related to the total failure of the execution of a job  but not relating to something such as a syntax error or input member error which the front-end would stop a job from getting to this stage.



You can use this field to join the HSP_OBJECT table (OBJECT_ID) and obtain the username of the user who executed the job.

Sample Queries

Sample Query 1: Show all executed jobs and their details

  jobs.ATTRIBUTE_1 AS Application,
  jobs.ATTRIBUTE_2 AS Database,
    WHEN 1 THEN 'Calculation'
    WHEN 4 THEN 'Variable'
    WHEN 5 THEN 'Ruleset'
  END AS Job_Type,
    WHEN 1 THEN 'Running'
    WHEN 2 THEN 'Completed'
    WHEN 3 THEN 'Failed'
    WHEN 4 THEN 'Not Started'
  END As Run_Status
INNER JOIN hsp_object Users ON user_id = object_id

Here is some sample data from a system with names removed. Note that I filtered it to an admin (ProcessAdminMan) used for daily processing and that the database is blank for this job type.

Application Database Name Type Start Date End Date Status
Calc1 Ruleset 17-JUN-14 07.27.04AM 17-JUN-14 07.27.31AM Completed
Calc2 Ruleset 17-JUN-14 07.27.42AM 17-JUN-14 07.36.40AM Completed
Calc3 Ruleset 17-JUN-14 07.36.52AM 17-JUN-14 07.37.20AM Completed
Calc4 Ruleset 17-JUN-14 07.37.31AM 17-JUN-14 07.40.20AM Completed
Calc5 Ruleset 17-JUN-14 07.40.31AM 17-JUN-14 07.49.44AM Completed
Calc6 Ruleset 17-JUN-14 07.49.56AM 17-JUN-14 08.06.21AM Completed

Here is this report on an OBIEE Dashboard (minus a couple of columns). Again I've whited out a few names so there are a few gaps. I'd also apply conditional formatting to add some colour based on the run status.

Sample Query 2: Show the variables used for a calc execution

  ATTRIBUTE_1 AS "Calculation",
  JOB_NAME AS "Variable Name",
  DETAILS AS "Variable Value"

Output would look like:

Calculation Variable Name Variable Value
Calc1 StartDate d20140616
Calc1 EndDate d20140611
Calc1 SomeVar SomeValue
Calc1 AnotherVar AnotherValue

PARENT_JOB_ID is the ID of the calculation script.

And there you have it. Hopefully that helps provide some basic understanding of behind the scenes of the Planning Job Console.

Saturday, 31 May 2014

OBIEE Supports IE11! Please get EPM Supported! +Workarounds

Just read that the latest OBIEE (OBIEE now supports IE11 at last! Now please follow it up with EPM Oracle!

Windows 8.1 has made life really painful for supporting Oracle BI products. If you did not know, Windows 8 comes with IE10 and Windows 8.1 with IE11. You cannot roll back or install an earlier version than what it comes with. This makes it very tricky when various web apps such as EPM Workspace do not support or work at all in these versions (OBIEE 11 has supported IE10 for a while now and EPM supports IE10 if you're lucky enough to be there already!).

If you are stuck in this predicament (OBIEE or EPM) these are the workarounds I know about:
  1. Probably the least simple- Use citrix to offer your users IE8 through a published app. It is pretty seamless however the user will need to remember to go into IE8 whenever they want to connect to EPM. The published app cannot be made the default browser as far as I know.
  2. Probably the least practical- You can switch IE10/11 to emulate IE8 in the developer options. I say least practical though because you have to do it every time you visit the website. Developer options can be accessed through the Tools menu or by pressing F12. You need to find the emulation tab (IE11 has little icons down the left to change tabs/sections.. I think CTRL-8 also goes there).  You can then change document mode to '8'.
  3. IE11 has an Enterprise Mode which enables users to permanently set the browser to use IE8 emulation for a specific page. This requires the Windows 8.1 Service Pack to be installed and this is why I haven't been able to try it out yet although I should be able to next week. Apparently there are two options for administrators to allow this setting. They can allow users to turn on and off Enterprise Mode via the tools menu or they can maintain a whitelist on the users computer of sites that should use Enterprise Mode by default. I'm assuming this should work like the emulation in option 2 and therefor assume (hope) it works.
There was also an article on The Art of BI blog recently detailing some steps to alter the code on the server-side to work with IE10. Click here to go check that out.

Thanks to the Oracle Proactive Support blog for the heads up on OBIEE support.

Anyway, I just got excited that IE11 was finally supported in OBIEE and somehow ended up writing all of the above. Have a good weekend!

- Update (15-09-2014): Oracle have specified the following in regards to running EPM in Enterprise Mode:

Oracle has not certified EPM System on Internet Explorer 11 in Enterprise Mode and Oracle guidance is to use a certified release of Internet Explorer.  However, Oracle Support will assist customers running EPM System products on Internet Explorer 11 in Enterprise Mode by providing support for issues that are known to occur in a certified browser release.

There are a couple of minor exceptions and other notes. is meant to properly support IE11. Check out the KB article for full details.

Monday, 19 May 2014

OBIEE - Formatting Calculated Items in a Pivot Table

I have been spending a lot of time with OBIEE and Essbase lately (quite often much more time than should have been necessary!) and so my first blog in much too long is about my latest hurdle. It seems that OBIEE is full of hurdles and sneaky workarounds (and also some amazing customisation/extension opportunities using third party visualisations). Using Essbase and OBIEE together probably exponentially increases the amount of hurdles but I am still enjoying the product.

I recently wanted to create a table with measures in the rows and years and organisations in the columns. I also wanted to have a increase % column added.


Genmind CorpStockplus Inc.Tescare Ltd.
FY13FY14Increase %FY13FY14Increase %FY13FY14Increase %
# of Orders11314024%25028112%25728611%

This seemed easy enough to do using a calculated item on the Fiscal Year column in the table.

It quickly became apparent however that the formatting options are very limited for this new column. Basically you only get style formatting and cannot change the number format and therefore you cannot choose to display the output as a percentage.

A quick thinker might instantly just try to concatenate a percentage sign onto the end of the number but would be even more quickly disappointed when the editor only allows numerical calculations/output.

The solution is not particularly convoluted thankfully. All you need to do is add a conditional format onto each of your measure fields with the condition that when the column ('Per Name Year') is null, then apply the percentage data format. So the steps are:
  1. Go back to the criteria tab
  2. Edit the column properties for the 'Revenue' column and click on the Conditional Formatting tab.
  3. Click Add Condition and select 'Per Name Year'
  4. Select Operator "is null' and press OK
  5. Select your formatting options. I have selected the 'Data Format' tab and selected Override, Percentage, Parantheses (red).
  6. Select OK and you should see something like the following screenshot.
  7. Navigate to the Results tab and you will see the result.
Notice that the second measure is still not formatted (# of Orders). You will need to repeat the conditional formatting on every measure.

The final result will be:

It should be noted that this would only work if you only had a single calculated column or you wanted all your calculated columns to have a single format.

There you have it. Hopefully that gets someone out of a bind. I would be keen to hear any other options people use, especially if they are better! I'm sure another option would be to modify the XML which would work for different formatting on multiple calculated columns.

Wednesday, 18 December 2013

Interesting EPM / IE10 Tidbit

While looking through Oracle Support I came across this newly updated piece of information.

Wednesday, 12 December 2012

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 servers to an 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)


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 servers. One difference when I did it on the server though was that I joined the fields two at a time instead of all 3). This leaves us with 1 field again


6. Create a New field with text 0

23 0
964 0

7. Join the two fields

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!

Tuesday, 28 August 2012

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).