Run while you can

It’s that time of year again

ODTUG is looking for nominations for its board of directors but you only have until 28 September 2011.  

As one of the members not up for election this year (see you next year), I was asked to write up why I thought you might want to run for this august group of geeks.  Take a look at the ODTUG blog for my guest post.  You may also want to check out Mike Riley’s slightly more sane post on why he did it and why it might be for you.

If my (not Mike’s) kind of drivel isn’t your cup of tea (But then why oh why are you even reading this?  I will leave that to you to ponder.), at least check out the call for nominations and the nomination guidelines.

If you’re interested, the nomination deadline is next Wednesday, so there isn’t much time.  Don’t let the deadline pass you by if you’re interested.

Hate Smart View? Love the add-in?

Don’t be a hater

It looks like it’s finally time to embrace Smart View.

Why?  

Remember all of those things that the add-in has done since the year dot but Smart View has not?  (Ooooh, a rhyme, and not even intentional.)

Yes, you remember.  How could you possibly forget?

The answer to all of our Smart View prayers is here:  full add-in parity has (apparently, see below) been reached.

Has wild cheering ensued on your end?  It did on mine when I heard about it.  

See the Essbase Labs post on the functionality right here.

And MMIC's even more detailed take on it here.

Fall in love all over again on 29 September 2011

If you want to see this in action, go to Oracle Support and search for document id 1356368.1.  This will sign you up for the webinar.  Remember all of those Support Advisors I’ve blogged about?  This webinar is part of that initiative.  Have you had good thoughts for Support lately?  You should.

See you there.

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.