Stupid Planning Queries #1 -- Forms and Task Lists

Introduction

As 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 warned

EVERYTHING 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 bad

So 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 TV

If 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 comments

I 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 thing

None 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 queries

I’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 11.1.1.3.  When 11.1.2.x-specific examples arise, I’ll note what version I’m on.

Give me my forms

Oh 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 forms

SELECT 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

Form Name

Balance Sheet

Cash Flow

Financials Summary

Income Statement

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 Lists

By 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
SELECT
    O2.OBJECT_NAME AS 'Task list',
    O1.OBJECT_NAME AS 'Task',
    CASE T.TASK_TYPE
        WHEN 0 THEN 'Descriptive'
        WHEN 1 THEN 'URL'
        WHEN 2 THEN 'Web form'
        WHEN 3 THEN 'HBR'
        WHEN 4 THEN 'Workflow'
        ELSE 'Unknown'
    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 it

I’ve got a veritable treasure trove of these.  Anticipate (or dread) many more.

Happy Planning Hacking to you all!