More cool free stuff from Oracle, upgrade edition

Introduction

I’m not totally sure the word “cool” is adequate to describe what Oracle’s giving away.  Awesome?  Stupendous?  Magnificent?   Gobsmacking?  You decide after you’ve had a read.

Is the above mere hyperbole?  I think you’ll replace the snark with praise in just a few short minutes.

Did I mention it’s free?  Read on…

Upgrades are us

No more beating around the bush on this one – if you are a paid-up  customer or partner, Oracle has created an upgrade advisor from 11.1.1.3 to 11.1.2.1.  You will have to log into Oracle Support to view this, but trust me it’s worth it.


What do I like about it so much?

• I’ve been on upgrades (As everyone who has read this blog knows by now, I am an infrastructure idiot, so I have been involved strictly from an application perspective.) that were not…well planned.  If you follow this upgrade advisor, you will have a guide.  Oracle’s guide.  Think about how that will sound to your boss/IT director/CIO/CFO/VP of Finance.  Right, thought so.
• Upgrades are often the province of consultants.  Nothing wrong with that as yr. obdnt. srvnt. is one himself.  But you, the customer, have to trust that the consultants are doing everything right.  Now you have the vendor’s recommendation and can compare and contrast that with what your consulting company offers as an approach.  This might lead to interesting conversations.  :)
• Consulting companies (if they’re smart) will spend a lot of time going through this and improve their processes.  This is A Good Thing.
• There’s big buck process consulting in here, including, in the Plan category alone:
• Learn how to work with Oracle Support
• Project Organization and Governance
• Review Architecture and Implementation Needs
• Review Potential Environment Impact
• Review Product Certifications
• Review Upgrade and Installation Guides
• Consider a Test Strategy
• Consider Training Needs
• Review Impact on Third Party Components and Interfaces
• Design Test Systems
• Constructing a Test System
• Planning for Backups and RDA/OCM Collections
• Patching Strategy for Test and Live System
• List of Milestone Deliverables
• As I wrote, that’s just the Plan section.  There’s more, much more on offer.  
• What I particularly like about this guide is it doesn’t tell you how to do something specifically (it is not going to write your test strategy for you), but it tells you why you should have a test strategy, and what the test strategy should include, and when it should be deployed in the project.  This is Good Stuff.
• Knowledge is power.  You know have Knowledge, hence your Power just increased.  
• Did I mention it is free?

Conclusion

I know of a customer who is going through an 11.1.1.3 to 11.1.2.1 upgrade right now.  I’m sending them the link right after I finish posting this blog.  How much more of a recommendation can I give?

By the way, if you feel any sense of gratitude or thanks, don’t direct it towards me as I had nothing to do with the writing of this Upgrade Advisor.  Thank the unsung heroes in Oracle Support.

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!

2011 Hyperion SIG election time

Introduction

It’s voting time again at Hyperion SIG-land, so you happy ODTUG members, this is your chance to get out there and select the representative(s) of your choice.

The candidates, in alphabetical order are:

• Kevin Cox of Lennox International
• Janice D’Aloia of Mo’mix Solutions
• Cindy Eichner of Finit Solutions
• Courtney Foster of Newland Real Estate
• Juan Porter of TopDown Consulting
• Henry Robin of ConocoPhillips
• Robb Salzmann of Accelatis
• Tony Scalese of Edgewater
• Anthony Stefanac of James & Monroe

Split ‘em by job

I don’t know if it matters to you or not:  Janice, Cindy, Juan, Tony, Robb, and Anthony all work for, or own, consulting/software companies.

I don’t know if this matters, either:  Kevin, Courtney, and Henry are customers aka clients aka industry.  That last term has never really made sense to me, but such is life.

How do you vote?

Go right here.

You must make sure you’ve logged into the ODTUG web site by clicking on the login link at the top of the page.

How many candidates can you vote for?

This isn’t Chicago or Philadelphia.  That is a Good Thing.  Or Not.

Here are the extremely simple rules.

• You must be a full ODTUG member.  If you weren’t a member, and went to KScope11, you are now a member.
• You have three votes.
• You do not have to vote three times – you can vote for one, two, or three candidates.
• You can only vote once per candidate.

Who do I recommend?

Ah, that’s a bit of a secret.  Seriously, I have my favorites, and they’re likely not all that hard to figure out, but I don’t want my insanity/stupidity/genius to influence you.

This is the Australian ballot, after all (what, you think I’d write a post like this without some completely obscure bit of trivia?), so whoever you vote for is your secret.

It isn’t compulsory, but it’s still a good idea

You may or may not live in God’s Own Country (I seem to be on an antipodean kick in this post – it is a really beautiful country and they did pioneer electoral reforms and yes, that PhD. in Imperial labor history would have been a lot of fun) and so it may not be against the law to not vote, but you should think long and hard about voting.

This is your chance to determine the direction of the EPM side of ODTUG.  Vote now.