Stupid Planning queries #4 -- Version

Introduction

Today we consider the simple Version dimension in the seemingly-endless-but-honestly-not-that-many-more-please-God-make-it-stop-but-oh-yes-soon-soon-soon series of Planning dimension queries.  Simple?   Really?  Sure, how complicated could such a small dimension (Working, Final, etc.) be?  

Do I ask leading questions?  Why, as a matter of fact, yes I do.

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.

Questions, questions

I love poking around in the odd corners of Planning – there’s always interesting stuff to be found.  

Does anyone know:

1. The purpose of the “Personal” VERSION_TYPE?
2. The difference between a Private and Public ACCESS_TYPE?  Or even what ACCESS_TYPE might mean?
3. The definition of the IN_USE field?

Send all answers care of this blog.  The world will thank you.

Some call it bragging

I call it the joy of figuring out something that really, really bugged me.  This one had me stumped for a fair bit so it made my day to figure it out.

What happens when a subquery returns more than one row?  Wait, wait, I can tell you –KABOOM!  

Unless you grab the results and concatenate it into a string.  Nah, there's no way I figured this one out on my own, but I am at least capable of stealing the idea from the interwebs.
The setup
Here's the Version's (Same old Saturday night) three UDAs:

These are the only three UDAs in the entire application (hey, it’s the Sample Planning app, no actual reflection of reality required), so this code:
SELECT * FROM HSP_UDA

Returns:

UDA_ID	DIM_ID	UDA_VALUE
1	35	Test UDA
2	35	Test UDA #2
3	35	Test UDA #3

The fix (in SQL Server at least)

The code:
SELECT STUFF((SELECT ',' + UDA_VALUE FROM HSP_UDA FOR XML PATH ('')),1,1,'')

Returns:

Test UDA,Test UDA #2,Test UDA #3

Ooh, pretty.  And think about the way ODI returns UDAs.  Yup, it’s the same thing.

I will admit to being all kinds of excited about this and sharing my geeky, gawky delight in this with two of my more SQL-oriented colleagues.  They were…unimpressed.  Sigh.  Oh well, such is the lot of the SQL FNG.

The code

So, putting the above into the ever more standard pull-the-dimension-from-Planning query:
--    Purpose:    Query the Version dimension
--    Modified:    23 July 2011, first write Cameron Lackpour
--    Notes:        There's some interesting stuff in Version,
--            much of it not visible through the UI.
--            I threw in UDAs and Formulas.
SELECT
    O1.OBJECT_NAME AS 'Member',
    CASE V.VERSION_TYPE
        --    I have no idea what "Personal" means, but it's in
        --    Dave Farnsworth's and Oracle's schema guide.
        WHEN 0 THEN 'Personal'
        WHEN 1 THEN 'Standard Bottom Up'
        WHEN 2 THEN 'Standard Target'
    END AS 'Type',
    --    I don't know what Access means, either.  Something to do
    --    with EPMA?  This is what comes from hacking mostly undocumented
    --    schemas.
    CASE V.ACCESS_TYPE
        WHEN 0 THEN 'Private'
        WHEN 1 THEN 'Public'
    END AS 'Access',
    --    Oh, this is a broken record.  I can't tell what IN_USE
    --    actually does.  Anyone with a clue please write care of
    --    this blog.
    CASE V.IN_USE
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
    END AS 'In use',
    -- 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',
    --    Hah!  Finally, one that i can figure out.
    --    This is the date/time that the Version was created.
    V.DATE_IN_USE AS 'Date created',
    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 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',
    CASE M.TWOPASS_CALC
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
    END AS 'Two pass',
    CASE M.ENABLED_FOR_PM
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
    END AS 'PM?',
    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.MEMBER_ID = O1.OBJECT_ID AND
            MU.UDA_ID = U.UDA_ID FOR XML PATH ('')),1,1,'')), '') AS 'UDAs'
FROM HSP_VERSION V
INNER JOIN HSP_OBJECT O1 ON
    V.VERSION_ID = O1.OBJECT_ID
INNER JOIN HSP_MEMBER M ON
    M.MEMBER_ID = O1.OBJECT_ID
LEFT OUTER JOIN HSP_MEMBER_FORMULA F ON
    V.VERSION_ID = F.MEMBER_ID

The results

It’s way too wide for this blog.  Click here to download the Excel file with the results or squint like crazy and look at the graphic below.

Regardless of where you look, the cool concatenation is way over on the right side of the table.

One day I will be the SQL ou manne and I suppose I won’t be impressed with this sort of thing.  In the meantime, I think that’s a pretty cool hack.

Happy Planning hacking!