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:


SELECT p.C_MEMBER_NAME,c.C_MEMBER_NAME FROM (
  SELECT  
    I_PARENT_MEMBER_ID, 
    I_CHILD_MEMBER_ID,
    I_LIBRARY_ID,
    I_DIMENSION_ID,
    ROWNUM AS OrderId 
  FROM (
    SELECT       
        I_PARENT_MEMBER_ID, 
        I_CHILD_MEMBER_ID, 
        r.I_LIBRARY_ID,
        d.I_DIMENSION_ID,
        d.I_TOP_MEMBER_ID
    FROM DS_RELATIONSHIP r
    INNER JOIN DS_DIMENSION d ON 
        r.I_DIMENSION_ID = d.I_DIMENSION_ID AND 
        r.I_LIBRARY_ID = d.I_LIBRARY_ID
    INNER JOIN DS_APPLICATION a ON  
        a.I_LIBRARY_ID = d.I_LIBRARY_ID AND    
        COALESCE(d.I_PARENT_APPLICATION_ID,1)=a.I_APPLICATION_ID
    WHERE
      r.I_LIBRARY_ID=1 AND
      a.C_APPLICATION_NAME='Master' AND -- Application name here. master = shared library
      d.C_DIMENSION_NAME='Period' -- Dimension name here
  ) MemebrIDs
  START WITH I_PARENT_MEMBER_ID=I_TOP_MEMBER_ID
  CONNECT BY PRIOR I_CHILD_MEMBER_ID=I_PARENT_MEMBER_ID
) Hierarchy 
INNER JOIN DS_MEMBER c ON 
  Hierarchy.I_CHILD_MEMBER_ID = c.I_MEMBER_ID AND 
  Hierarchy.I_LIBRARY_ID = c.I_LIBRARY_ID AND 
  Hierarchy.I_DIMENSION_ID=c.I_DIMENSION_ID
INNER JOIN DS_MEMBER p ON 
  Hierarchy.I_PARENT_MEMBER_ID = p.I_MEMBER_ID AND 
  Hierarchy.I_LIBRARY_ID = p.I_LIBRARY_ID AND  
  Hierarchy.I_DIMENSION_ID=p.I_DIMENSION_ID 
ORDER BY OrderId

Output:

PARENT	CHILD
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]

Notes:

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)


SELECT
   SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8) AS AppName,
   COUNT(*)
FROM JM_Job
WHERE
   e_job_status = 'Completed'
  AND
   SUBSTR(c_descr, 1,8) IN ('deploy :','redeploy')
GROUP BY
   SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8)
ORDER BY COUNT(*);
  

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.

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.

HSP_JOB_STATUS

 Columns

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.

 

 JOB_TYPE

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

 

 RUN_STATUS

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.

 

USER_ID 

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

SELECT
  jobs.ATTRIBUTE_1 AS Application,
  jobs.ATTRIBUTE_2 AS Database,
  jobs.JOB_NAME,
  CASE jobs.JOB_TYPE
    WHEN 1 THEN 'Calculation'
    WHEN 4 THEN 'Variable'
    WHEN 5 THEN 'Ruleset'
    ELSE CAST(jobs.JOB_TYPE AS VARCHAR(2))
  END AS Job_Type,
  jobs.START_TIME,
  jobs.END_TIME,
  CASE jobs.RUN_STATUS
    WHEN 1 THEN 'Running'
    WHEN 2 THEN 'Completed'
    WHEN 3 THEN 'Failed'
    WHEN 4 THEN 'Not Started'
  END As Run_Status
FROM HSP_JOB_STATUS jobs
INNER JOIN hsp_object Users ON user_id = object_id
WHERE jobs.PARENT_JOB_ID IS NULL AND users.OBJECT_NAME = 'ProcessAdminMan'
ORDER BY jobs.START_TIME

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
SomeApp		Calc1	Ruleset	17-JUN-14 07.27.04AM	17-JUN-14 07.27.31AM	Completed
SomeApp		Calc2	Ruleset	17-JUN-14 07.27.42AM	17-JUN-14 07.36.40AM	Completed
SomeApp		Calc3	Ruleset	17-JUN-14 07.36.52AM	17-JUN-14 07.37.20AM	Completed
SomeApp		Calc4	Ruleset	17-JUN-14 07.37.31AM	17-JUN-14 07.40.20AM	Completed
SomeApp		Calc5	Ruleset	17-JUN-14 07.40.31AM	17-JUN-14 07.49.44AM	Completed
SomeApp		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

SELECT
  ATTRIBUTE_1 AS "Calculation",
  JOB_NAME AS "Variable Name",
  DETAILS AS "Variable Value"
FROM HSP_JOB_STATUS
WHERE PARENT_JOB_ID = 51743;

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.

OBIEE Supports IE11! Please get EPM Supported! +Workarounds

Just read that the latest OBIEE (OBIEE 11.1.1.7.140527) 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 11.1.2.3.500 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 11.1.2.3.500 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. 11.1.2.4 is meant to properly support IE11. Check out the KB article for full details.

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.

e.g.

	Genmind Corp			Stockplus Inc.			Tescare Ltd.
	FY13	FY14	Increase %	FY13	FY14	Increase %	FY13	FY14	Increase %
Revenue	298,762	360,576	21%	698,951	737,421	6%	702,287	702,004	0%
# of Orders	113	140	24%	250	281	12%	257	286	11%

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.