IntroductionI 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!
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
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',
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',
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS 'Include BegBalance',
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS 'Marked for delete',
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'
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.
WHEN 0 THEN '+'
WHEN 1 THEN '-'
WHEN 2 THEN '*'
WHEN 3 THEN '\'
WHEN 4 THEN '%'
WHEN 5 THEN '~'
WHEN 6 THEN '^'
END AS 'Operator'
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
|Scenario||Alias||Start Year||Start Period||End Year||End Period||Exchange Rate Table||Storage||Include BegBalance||Marked for delete||Data Type||Operator|
ConclusionThe Scenario dimension looks simple, but there is a fair amount of detail in there.
Happy Planning table hacking and keep reading and correcting.