Friday 15 July 2022

I have moved! To OneStream!

 Hi All,

I just wanted to let you know that I may not have any new posts up this week as I have recently moved to the exciting world of OneStream. But who knows what this newfound enthusiasm and opportunity for exploration will bring forth! Maybe this blog will become something other than a place for people to download my Notepad++ styles!

Looking forward to my next post. In the meantime I will continue pretending like I can find the time to maintain a blog.

- Daniel

Thursday 3 September 2020

Put EPM Calc Manager / Rules / FR Web windows back in their place

I've never liked how calculation manager and various other EPM links open in pop up windows rather than tabs. Recently I hit that level of annoyance or boredom where I was actually bothered to do something about it so a quick search uncovered that it is quite easy to deal with in Chrome and the new Edge. There are options in Firefox as well but not so simple.

So next time you, for example, open Rules in PBCS and it opens up in a pop-up, simply right click in the title bar and click 'Show As Tab'. You'll then have a regular tab window that you can drag back to whatever main window you want.

You will then have the satisfaction of some nice neat tabs... which if you're anything like me will be alongside those other 100 tabs you seem to have open for a rainy day.

As mentioned above this works exactly the same in Edge, I guess since its based on Chromium. As for Firefox it is not so simple. I don't think there is a way for you to do this individually or on demand. It sounds like the only way is to permanently configure all popups to open as new tabs . This may or may not be what you want but if it is take a look at the solution in this link.


Wednesday 31 May 2017

Oh you can do that? Updating a Sub Var

Just a random small post since I haven't updated this blog in the last couple of days!

I was updating some substitution variables in EAS and noticed one of the variables properly save without me hitting Set at the bottom. Usually I'd type in the value and then go down and click the Set button.

Turns out if you type in the variable and then hit Enter once to store the value and a second time after that it will 'set' it.

Based on experience watching others set variables I'm guessing there is probably many who never realised the way that works.

Till next week..

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.