Want to hire me? For availability and scheduling please email info@arcepm.com

01 July 2011

Stupid Planning Queries #2 --- Scenario dimension

Introduction

I am pretty sure I am insane.  There are many that would I agree, I think.

Oh, I am insane -- this blog was WRONG Are Aliases in the Scenario dimension?  Yup.  Why oh why did I think there weren't?  See point #1.  So, Godoy (see the comments for this post), whoever and wherever you are, thanks for reading and pointing out the error of my ways.  The code is corrected below.

Why am I nuts?  Well, maybe a better description is I am just suffering from too much enthusiasm.  An exhausting but exhilarating KScope11 (I will have a post on that but code first) just finished yesterday and when I woke up this morning after finally getting more than three or four hours of sleep (it’s a good thing the conference ended when it did – I couldn’t have taken too much more fun) all I could think about was writing SQL to export Planning dimensions.  See, ODTUG is inspirational that way.

Without further ado, let’s extract dimensions from Planning, starting with the Scenario dimension, which is harder than you think.  All examples are from the Planning Sample App and the inspiration comes from Dave Farnsworth’s excellent hacking Planning tables white paper he presented at last year’s Kaleidoscope in Washington, D.C.  You can get the white paper by going to http://www.odtug.com, then Tech Resources, then search for “Planning Tables”.

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

Code to pull Scenario

--    Purpose:    Query the Scenario dimension

--    Modified:   30 June 2011, first write Cameron Lackpour

--    Notes:      Now with Aliases!

SELECT

      O1.OBJECT_NAME AS 'Scenario',

      -- NB -- The overall SELECT from HSP_MEMBER ensures that members

      --       with and without an alias are selected.

      -- ISNULL puts in zero length string in place of NULL

      ISNULL((SELECT OA.OBJECT_NAME

      FROM HSP_ALIAS A

            INNER JOIN HSP_OBJECT OA

                  ON A.MEMBER_ID = O1.OBJECT_ID AND

                  OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias',

      O2.OBJECT_NAME AS 'Start Year',

      O4.OBJECT_NAME AS 'Start Period',

      O3.OBJECT_NAME AS 'End Year',

      O5.OBJECT_NAME AS 'End Period',

      --    ISNULL to get rid of ugly Null when there is no fx table.

      ISNULL(O6.OBJECT_NAME, '') AS 'Exchange Rate Table',

      CASE M.DATA_STORAGE

            WHEN 0 THEN 'Store Data'

            WHEN 1 THEN 'Never Share'

            WHEN 2 THEN 'Label Only'

            WHEN 3 THEN 'Shared Member'

            WHEN 4 THEN 'Dynamic Calc and Store'

            WHEN 5 THEN 'Dynamic'

      END AS 'Storage',

      CASE S.USEBEGBAL

            WHEN 0 THEN 'False'

            WHEN 1 THEN 'True'

      END AS 'Include BegBalance',

      CASE O1.MARKED_FOR_DELETE

            WHEN 0 THEN 'False'

            WHEN 1 THEN 'True'

      END AS 'Marked for delete',

      CASE M.DATA_TYPE

            WHEN 1 THEN 'Currency'

            WHEN 2 THEN 'Non Currency'

            WHEN 3 THEN 'Percentage'

            WHEN 4 THEN 'Smartlist'

            WHEN 5 THEN 'Date'

            WHEN 6 THEN 'Text'

            ELSE 'Unspecified'

      END AS 'Data Type',

      --    No way to show the Plan Type.  Remember, a given Scenario is

      --    in ALL Plan Types.

      --    This could vary by Plan Type, so you may need to repeat this CASE.

      CASE M.CONSOL_OP1

            WHEN 0 THEN '+'

            WHEN 1 THEN '-'

            WHEN 2 THEN '*'

            WHEN 3 THEN '\'

            WHEN 4 THEN '%'

            WHEN 5 THEN '~'

            WHEN 6 THEN '^'

      END AS 'Operator'

FROM

      HSP_SCENARIO S

INNER JOIN HSP_OBJECT O1 ON

      S.SCENARIO_ID = O1.OBJECT_ID

INNER JOIN HSP_OBJECT O2 ON

      S.START_YR_ID = O2.OBJECT_ID

INNER JOIN HSP_OBJECT O3 ON

      S.END_YR_ID = O3.OBJECT_ID

INNER JOIN HSP_OBJECT O4 ON

      S.START_TP_ID = O4.OBJECT_ID

INNER JOIN HSP_OBJECT O5 ON

      S.END_TP_ID = O5.OBJECT_ID

INNER JOIN HSP_MEMBER M ON

      M.MEMBER_ID = O1.OBJECT_ID

LEFT OUTER JOIN HSP_OBJECT O6 ON

      S.FX_TBL = O6.OBJECT_ID



Output



ScenarioAlias
Start YearStart PeriodEnd YearEnd PeriodExchange Rate TableStorageInclude BegBalanceMarked for deleteData TypeOperator
CurrentCur
FY09JanFY12DecTestStore Data

FALSE

FALSE

Unspecified~
Actual
FY09JanFY12Jun
Store Data

FALSE

FALSE

Unspecified~
Forecast
FY09JulFY12Dec
Store Data

FALSE

FALSE

Unspecified~
Plan
FY09JanFY12Dec
Store Data

TRUE

FALSE

Unspecified~

Conclusion

The Scenario dimension looks simple, but there is a fair amount of detail in there.  And who knew that Scenarios can’t have aliases?  It even has aliases.

Happy Planning table hacking and keep reading and correcting.


2 comments:

Godoy said...

I don´t know wich version you are using, but I´m on 11.1.1.3 and there is, indeed, alias for the scenarios.

Cameron Lackpour said...

Oh, pooh, I got that wrong. In my defence, I will state that the Alias field is way down in the dialog box.

I will revise my SQL code to pull the Scenario.

And I'm glad to see that someone other than my mum or Glenn reads this thing and finds errors (that would mostly be Glenn).

Regards,

Cameron Lackpour