IntroductionHas anyone been reading this series of Planning dimension extracts? Anyone? Other than Godoy (oh so close to Godot, and maybe I’m waiting for him)?
Hmm, well, no matter, I think I do this as much for myself as anyone else. So today I’m going to share two (oh, be still my trembling heart) queries today.
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.
First queryHave you ever wondered how the code I’ve posted knows that HSP_OBJECT’s OBJECT_ID 32 is Account? Or that 34 is Period? After some of what you’ve seen, this should be easy peasy lemon squeezy.
-- Purpose: Query to figure out dimension ids
-- Modified: 29 July 2011, first write Cameron Lackpour
-- Notes: The DIM_ID values will be used in all kinds of dimension
FROM HSP_MEMBER M
INNER JOIN HSP_OBJECT O ON
M.DIM_ID = O.OBJECT_ID
Second queryNow that we know what the dimension id for Segments (remember, this is the Planning sample application and Scenario, Account, Entity, Period, Version, and Year are required dimensions with HSP_Rates and Currency thrown in for good measure in the Hyperion-built multiple currency application) the query to bring out all kinds of custom dimension goodness should be pretty easy.
If you are as sad as I am, you might find this a special treat: both UDAs and Attributes get pulled out as shown below:
The code-- Purpose: Illustrate the querying of a custom dimension
-- Modified: 3 August 2011, first write
-- Notes: Now with formulas, UDAs, and Attributes!
PO.OBJECT_NAME AS 'Parent',
O.OBJECT_NAME AS 'Child',
-- 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
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',
-- Remember, there can be up to five plan types per Planning
-- application, so CONSOL_OP1 through CONSOL_OP5 are valid.
WHEN 0 THEN '+'
WHEN 1 THEN '-'
WHEN 2 THEN '*'
WHEN 3 THEN '/'
WHEN 4 THEN '%'
WHEN 5 THEN '~'
WHEN 6 THEN '^'
END AS 'Operator',
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 'No'
WHEN 1 THEN 'Yes'
END AS 'Two Pass',
-- Change the syntax of the CASE statement to support testing for NULL
-- Dave's Unofficial Guide has DATA_TYPE = 0 as
-- Unspecified, but in 11.1.2 that isn't the case.
WHEN M.DATA_TYPE IS NULL THEN 'Unspecified'
WHEN M.DATA_TYPE = 1 THEN 'Currency'
WHEN M.DATA_TYPE = 2 THEN 'Non Currency'
WHEN M.DATA_TYPE = 3 THEN 'Percentage'
WHEN M.DATA_TYPE = 4 THEN 'SmartList'
WHEN M.DATA_TYPE = 5 THEN 'Date'
WHEN M.DATA_TYPE = 6 THEN 'Text'
END AS 'Data Type',
-- As far as I can tell, this isn't used in Planning, but is still
-- there. Why?
WHEN M.USED_FOR_CONSOL = 0 THEN 'False'
WHEN M.USED_FOR_CONSOL = 1 THEN 'True'
END AS 'Used for consol',
ISNULL(F.FORMULA, '') AS 'Formula',
-- The subquery will puke if it returns more than one
-- UDA, so do string concateenation using FOR XML PATH
ISNULL((STUFF((SELECT ',' + U.UDA_VALUE
FROM HSP_MEMBER_TO_UDA MU
INNER JOIN HSP_UDA U ON
MU.UDA_ID = U.UDA_ID AND
-- INNER JOIN HSP_OBJECT O
MU.MEMBER_ID = O.OBJECT_ID
FOR XML PATH ('')),1,1,'')), '') AS 'UDAs',
ISNULL((STUFF((SELECT '; ' +
OAN.OBJECT_NAME + ': ' + OAV.OBJECT_NAME
FROM HSP_MEMBER_TO_ATTRIBUTE M1
INNER JOIN HSP_OBJECT OAN
ON M1.ATTR_ID = OAN.OBJECT_ID AND
M1.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_OBJECT OAV
ON M1.ATTR_MEM_ID = OAV.OBJECT_ID
INNER JOIN HSP_MEMBER MB
ON O.OBJECT_ID = MB.MEMBER_ID
FOR XML PATH ('')),1,1,'')), '') AS 'Attribute assignments'
FROM HSP_MEMBER M
INNER JOIN HSP_OBJECT O
ON M.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_OBJECT PO
ON O.PARENT_ID = PO.OBJECT_ID
LEFT OUTER JOIN HSP_MEMBER_FORMULA F ON
M.MEMBER_ID = F.MEMBER_ID
WHERE M.DIM_ID = 52305
Custom dimension outputThis is too big again, so please go here to download it and see it in all of its glory.
ConclusionI said I would get the Planning dimensions out in a month and this has just taken longer than I anticipated. I do try to have a semblance of a life that includes not always sitting in front of a computer doing unbillable work.
Having said that, we have made pretty good progress on the dimension front with:
Segments (custom dimension)
Entity (the big one)
I will try to get those out as soon as possible and then I’m going to switch gears for a while, and focus on EPM and the cloud and some Amazon Web Services Stupid Tricks.