Stupid Planning queries #5 -- dimension list and Custom dimension

Introduction

Has 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 query

Have 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
--                queries
--
SELECT DISTINCT
    M.DIM_ID,
    O.OBJECT_NAME
FROM HSP_MEMBER M
INNER JOIN HSP_OBJECT O ON
    M.DIM_ID = O.OBJECT_ID

First output

Second query

Now 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:

UDAs

Attributes

The code

--    Purpose:    Illustrate the querying of a custom dimension
--    Modified:   3 August 2011, first write
--    Notes:      Now with formulas, UDAs, and Attributes!
--               
SELECT
    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
    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',
    --    Remember, there can be up to five plan types per Planning
    --    application, so CONSOL_OP1 through CONSOL_OP5 are valid.
    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 '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.
    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?
    CASE
        WHEN M.USED_FOR_CONSOL = 0 THEN 'False'
        WHEN M.USED_FOR_CONSOL = 1 THEN 'True'
        ELSE 'Undefined'
    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 output

This is too big again, so please go here to download it and see it in all of its glory.

Conclusion

I 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:
Scenario
Account
Version
Segments (custom dimension)

That leaves:
Period
Year
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.