Oracle Advisors for every purse and purpose

Introduction

You may have gathered that Oracle Support are doing some really exciting things around gathering their vast number of technical documents and organizing them into constantly updated guides into what Oracle calls their Advisors series.  I’ve highlighted the Essbase Advisor here and it is truly awesome stuff.  If you haven’t read that post, check it out and take a gander at the depth and scope of the articles.  I guarantee you there’s valuable information in there.

Want proof?  Check out Doug Burke of the ODTUG Hyperion SIG’s comment:

Thank you for sharing this valuable information. Very helpful!
Can you now find me the time to read it all?

Yep, it’s a little overwhelming, but in oh such a good way.  What?  You don’t like tested, quality, high value information?  Right, thought you did.

Be prepared for more overwhelming goodness

The Essbase Overview Advisor was good.  No, it wasn’t good, it was great.  Yes, I rather liked it.

What would you say (oh, I do love how I get to write leading questions) if I told you there was an Overview Advisor for:
Essbase (yes, I know, I already wrote about it, this is a bonus mention):  Overview Advisor: Oracle Hyperion Essbase [ID 1344571.2]
Planning:  Overview Advisor: Oracle Hyperion Planning [ID 1349181.2]
HFM:  Overview Advisor: Oracle Hyperion Financial Management [ID 1344975.2]
FDM:  Overview Advisor: Oracle Hyperion Financial Data Quality Management [ID 1347925.2]
DRM:  Overview Advisor: Oracle Hyperion Data Relationship Management [ID 1354621.2]
Crystal Ball:  Overview Advisor: Oracle Crystal Ball [ID 1356689.2]
Strategic Finance:  Overview Advisor: Oracle Hyperion Strategic Finance [ID 1357224.2]

“Thanks, Oracle Support,” comes to mind.  :)

Let the numbers do the talking

The large count of articles within these Advisors precludes highlighting individual articles.  Instead, let me give you a feeling for the scope of information the Advisors offer and see if that piques your interest:

Advisor	Article count
Essbase	128
Planning	64
HFM	94
FDM	69
HSF	65
Crystal Ball	57
Total	526

Over 500 articles full of tested and proven information from the vendor.  Yes, some of them are repeats across products, such as Internet Explorer (IE7, IE8 and IE9) Recommended Settings for Oracle Hyperion Products, because it relates to so many of the product suites.  Regardless, there is lots and lots and lots of good stuff in there.  

NB – I left Upgrade Advisor: Enterprise Performance Management (EPM) System Upgrade from 11.1.1.3 to 11.1.2.1 [ID 324.1] off of the above list because it isn’t so much a collection of articles but more a good practices guide for the upgrade of 11.1.1.3 to 11.1.2.1.

Where Oracle Support is going

Warning:  This is Cameron’s Speculation – I have no inside line as to what Support is going to do tomorrow, next week, or for the rest of time, or why they did or will do it, whatever “it” is.  The best way to find out what Oracle’s really thinking is to, well, contact Oracle.  If you’re interested in a semi-educated series of guesses and speculations, then read on, otherwise jump to the error handler and exit (oooh, a programming joke, scary).

My speculation hat is set firmly on my head

The problem

Support knew that they had lots of good information but customers and partners couldn’t find said information easily.  This meant that questions that had already been answered were getting asked again and again.  From an Oracle and customer/partner perspective this was silly and expensive.  Oracle has to pay Support people to answer the question, the customers/partners have to wait for a Service Request to make its way through the system, and all the time the answer is in a published Support article.  Why?

The solution(s)

Oracle then came up with a three-pronged approach to getting this information out to us:  Master Note Indexes, Advisors, and putting a human face to Support.

Master Note Indexes

Master Notes Indexes (Indicies?  Indexes?  I never get this one right) are collections of deep technical troubleshooting guides.  I wrote about them here.  They are Good Stuff.

Advisors

Advisors you’re reading about right now and are more organized around good practices, how-to, troubleshooting, installation and configuration, upgrade, certification, and performance guides.  There is some overlap with the Master Note Indices (Hah! I used the alternate word!), but not much.

Outreach

And then Support is really reaching out to the Oracle community through conferences such as ODTUG KScope and Oracle Open World.  This isn’t as direct as the above two initiatives, but in some ways is more interesting.  What I think Support is doing is trying to find out what customers and partners aren’t getting out of Support, why that’s happening, and what Support should offer.  They were at KScope11 in a big way with a Support panel session (nervously moderated by yr. obdnt. srvnt.), a Support genius bar where anyone could just walk up and ask some of the big Support guns any technical question under the sun, and participated in a couple of sessions (I understand that when My Man in California, Glenn Schwartzberg, saw Carol Crider of Essbase Support fame in the audience for his presentation, he just about had a heart attack.  Star Power at work as I know it takes quite a bit for Glenn to do more than his left-eyebrow-raised-when-oh-my-God-you-said-something-really-stupid-Cameron look.).

What does it all mean?

Support has moved in a new direction.  Where before Support was content (okay, I don’t know about contentment as that speaks to motive, but let me say that it looked like they were content) to let customers and partners find answers in the vast universe of support articles if they could, Support has now moved to a proactive approach where you are guided to the right answer.

What’s not to like?  That guidance means you find out as quickly as possible the right way to do something, fix a problem, or figure out the next steps that maybe means you really and truly do need to open a Service Request.

You may have noticed that Oracle’s EPM products aren’t becoming simpler over time.  Support has realized this and also figured out that they were sitting on a treasure trove of useful technical information that can help alleviate the complexity of the EPM platform.  We all benefit from their new approach.  

Don’t forget to let Oracle Support know that you’re happy with what they’re doing.  :)  I will.

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.