Introduction
I said I would get all of Planning’s dimension queries out in a month, and I aim to meet that goal.Without further ado, here’s the code to pull out the Account dimension from the sample Planning application in all of its glory.
Yes, I posted this on Network54, but here it is in my blog for completeness’ sake.
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 query Accounts
-- Purpose: Rewrite of Dave Farnsworth's sample Account query using-- explicit INNER JOINS because I am a control freak. Yes, it
-- really isn't any better, I just like it that way.
-- Modified: 30 June 2011, first write Cameron Lackpour, cameron@clsolve.com
-- Notes: Really written by Dave Farnsworth --
-- http://www.solutionsexperts.com
-- Go to http://www.odtug.com, then Technical Resources, then go
-- find his white paper on hacking Planning tables, then have
-- fun. :)
SELECT
O.OBJECT_ID,
O.OBJECT_NAME AS 'Member name',
-- Use SQL Subquery to get aliases.
-- 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 = O.OBJECT_ID AND
OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias',
PO.OBJECT_NAME AS 'Parent',
-- There are up to five Plan Types and operators
-- can vary across Plan Types, so you will need to repeat
-- this block if > 1 Plan Type.
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',
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 M.TWOPASS_CALC
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
END AS 'Twopass',
-- The parent object type is either 2, which is the Account
-- dimension or 32, which is a Account member
-- Comment this out as it doesn't really add anything to the-- query.
-- CASE PO.OBJECT_TYPE,CASE AA.USE_445
WHEN 0 THEN 'None'
WHEN 1 THEN '445'
WHEN 2 THEN '454'
WHEN 3 THEN '544'
ELSE ''
END AS 'Spread Type',
CASE AA.TIME_BALANCE
WHEN 0 THEN 'None'
WHEN 1 THEN 'First'
WHEN 2 THEN 'Last'
WHEN 3 THEN 'Average'
ELSE ''
END AS 'Time Balance',
CASE AA.SKIP_VALUE
WHEN 0 THEN 'None'
WHEN 1 THEN 'Skip missing'
WHEN 2 THEN 'Skip zeroes'
WHEN 3 THEN 'skip missing and zeroes'
ELSE ''
END AS 'Skip Value',
CASE AA.ACCOUNT_TYPE
WHEN 1 THEN 'Expense'
WHEN 2 THEN 'Revenue'
WHEN 3 THEN 'Asset'
WHEN 4 THEN 'Liability'
WHEN 5 THEN 'Equity'
WHEN 6 THEN 'Statistical'
WHEN 7 THEN 'Saved Assumption'
ELSE ''
END AS 'Account Type',
CASE AA.VARIANCE_REP
WHEN 1 THEN 'Expense'
WHEN 0 THEN 'Non Expense'
ELSE ''
END 'Variance Reporting',
CASE AA.CURRENCY_RATE
WHEN 0 THEN 'None'
WHEN 1 THEN 'Average'
WHEN 2 THEN 'Ending'
WHEN 3 THEN 'Historical'
ELSE ''
END AS 'Currency Rate',
-- Bitmask indicating the cubes that use the account
-- 1=revenue
-- 2=net income
-- 4=balance sheet
CASE AA.USED_IN
WHEN 1 THEN 'Revenue'
WHEN 2 THEN 'Net Income'
WHEN 3 THEN 'Balance Sheet'
END AS 'Where used',
CASE M.DATA_TYPE
WHEN 1 THEN 'Currency'
WHEN 2 THEN 'Non Currency'
WHEN 3 THEN 'Percentage'
ELSE ''
END AS 'Data Type',
CASE AA.SRC_PLAN_TYPE
WHEN 0 THEN 'NA'
WHEN 1 THEN 'Revenue'
WHEN 2 THEN 'Net Income'
WHEN 4 THEN 'Balance Sheet'
End AS 'Plan Type'
FROM HSP_OBJECT O
INNER JOIN HSP_OBJECT PO ON
PO.OBJECT_ID = O.PARENT_ID
INNER JOIN HSP_MEMBER M ON
M.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_ACCOUNT AA ON
AA.ACCOUNT_ID = O.OBJECT_ID
WHERE O.OBJECT_TYPE = 32
