IntroductionAs many know, sometimes to their indescribable agony, Planning uses a relational store for application metadata, whether it’s built in EPMA or in Classic Planning. Perhaps agony should be used in a past tense (agonied?) because historically Planning refreshes from that relational store to Essbase could be…problematic, but I haven’t run across that issue since the last days of Planning 9.2. May Hyperion Desktop stay in its grave forever, amen, In saecula saeculorum.
Okay, that’s the bad, but now that issue is fixed, and it turns out that the relational store has all kinds of goodies in it that you can exploit for fun and profit.
You have been warnedEVERYTHING YOU READ HENCEFORTH IS COMPLETELY AND 100% NOT SUPPORTED BY Oracle. PROCEED AT YOUR OWN RISK. Do something stupid like mass inserts into tables and go crying to Oracle Support that “Planning doesn’t work” and they’re going to ask what recreational drug you were taking when you thought mucking about with Planning tables was a good idea. And will then ask if you have a backup. Which, if you did something dumb like the above, you likely don’t. Don’t be a dope – only do SELECT queries (hey, that’s all I do) and until you’re comfortable with this, do it on a backup of the SQL data store. And don’t come whining to me either. Growl, snarl, whimper, etc.
But it’s not so badSo now that I have (hopefully) scared you into Col. Jeff Cooper’s Color Code Orange, be aware that many consulting companies, yr. obdnt. svnt. included, use queries like this all the time to get information out of Planning metadata (and data too, in a later post) repositories.
SELECTs can’t kill anything, other than the performance of your SQL server, and really, we’re talking about very small queries. Non optimized SQL code is not going to be an issue because the data sets are so small.
I am not a SQL programmer, but I play one on TVIf you have a SQL background, you may look at some of the code I write and think, “Why on earth did he code it this way, everyone knows that this (insert whatever cool SQL technique you know and I don’t) does it in a fraction of the time.” Fair enough, I am just a SQL hack. Essbase is another story (I like to flatter myself), but I agree with you in advance, my SQL is self-taught and there are definitely things I do in a cack handed way.
So post in the commentsI have no ego in this – given the above admission, if you’ve got a better way, post in the comments or email me. I will update the posts (this is going to be a series) and give full credit if you want. Or you could be the Mr/Mrs/Miss/Dr/Rev Anon SQL. You choose.
One last thingNone of this would have been possible without Dave Farnsworth’s “Planning Tables, What’s Behind the Curtain?” description of the Planning schema. Dave gave this presentation at last year’s ODTUG Kaleidoscope conference in Washington, DC and it was a SRO session. I’ve earned in billable house many times over the cost of my hotel and airfare (free admission as I presented) through Dave’s generously given away knowledge. The conference is incredible value for money. And the spirit of sharing is inspirational.
KScope11 is this year’s place to be for The Geeks That Matter. I can’t think of any other conference that provides this amount of information. My only quarrel with it is that I can’t be at multiple sessions at once – there is so much good information it is somewhat frustrating that I haven’t invented human cloning. But I digress. Sign up for this year’s conference while you still can.
Two simple queriesI’m going to start this series off with easy stuff to ease my adoring (ahem) readers into the somewhat murky world of Planning’s metadata repository. It will get much cooler/harder to read in the next post.
NB – All examples are from the Planning sample application and I am using SQL Server 2005. The code is almost identical in Oracle’s PL/SQL. And oh yes, this is against Planning 18.104.22.168. When 11.1.2.x-specific examples arise, I’ll note what version I’m on.
Give me my formsOh sure, you can go into Planning as an admin and then navigate the forms through Manage Forms but you have to go folder by sub-folder by sub-folder and it’s a right pain. And how do you print it out? Screen shots? Wouldn’t it be nice to just have a list?
Query to get list of formsSELECT O.OBJECT_NAME AS 'Form Name'
FROM HSP_OBJECT O
INNER JOIN HSP_FORM F
ON F.FORM_ID = O.OBJECT_ID
Form query output
|Plan Revenue - All Years|
|Plan Revenue - Assumptions|
|Plan Revenue - COS|
|Plan Revenue - Detail by Year|
|Plan Department Expenses|
|Plan Facilities Expenses|
|Plan Operating Expenses|
|Review G&A Expenses|
|Allocation - Expense|
|Allocation - IT|
|Allocation - Marketing|
|Allocation - Facilities Expense|
|Allocation - Segment|
Give me my Task ListsBy the same token, wouldn’t it be nice to know how many Task Lists you have and what’s in them.
Query to get Task Lists--Task list query
O2.OBJECT_NAME AS 'Task list',
O1.OBJECT_NAME AS 'Task',
WHEN 0 THEN 'Descriptive'
WHEN 1 THEN 'URL'
WHEN 2 THEN 'Web form'
WHEN 3 THEN 'HBR'
WHEN 4 THEN 'Workflow'
END AS 'Task Type',
T.DUE_DATE AS 'Due Date',
T.ALERT_DATE AS 'Alert Date',
T.INSTRUCTIONS AS 'Instructions',
T.DEPENDENCY AS 'Dependent task ID',
T.ALERT_FREQUENCY AS 'Alert Frequency',
T.OVERDUE_FREQUENCY AS 'Overdue Frequency',
O3.OBJECT_NAME AS 'Form Name',
T.STR_PROP1 AS 'URL',
O4.OBJECT_NAME AS 'Workflow Version'
FROM HSP_TASK T
--Get the task name
INNER JOIN HSP_OBJECT O1
ON T.TASK_ID = O1.OBJECT_ID
--Get the task list name
INNER JOIN HSP_OBJECT O2
ON T.TASK_LIST_ID = O2.OBJECT_ID
--Get the form name, left outer join because not all tasks have forms
LEFT OUTER JOIN HSP_OBJECT O3
ON T.INT_PROP1 = O3.OBJECT_ID
--Get the workflow Version, left outer join because not all tasks have versions
LEFT OUTER JOIN HSP_OBJECT O4
ON T.INT_PROP2 = O4.OBJECT_ID
Task List output
NB – The output from this was so big, I had to screenshot it. If you want the full output in all its glory, I have it as an Excel file here.
Hope you liked itI’ve got a veritable treasure trove of these. Anticipate (or dread) many more.
Happy Planning Hacking to you all!