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

Scenario	Alias	Start Year	Start Period	End Year	End Period	Exchange Rate Table	Storage	Include BegBalance	Marked for delete	Data Type	Operator
Current	Cur	FY09	Jan	FY12	Dec	Test	Store Data	FALSE	FALSE	Unspecified	~
Actual		FY09	Jan	FY12	Jun		Store Data	FALSE	FALSE	Unspecified	~
Forecast		FY09	Jul	FY12	Dec		Store Data	FALSE	FALSE	Unspecified	~
Plan		FY09	Jan	FY12	Dec		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.