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!

IMG00335-20110722-1337.jpg

Hanging out with my new training buddy Lucas Neill and now Liquigas-Cannondale fan!

Sent from my BlackBerry® wireless device

IMG00338-20110722-1343.jpg

Not very often Lucas Neill would pull on a jersey with another player's name on it!!!

Sent from my BlackBerry® wireless device

Metro-North's July Meltdown

Friday July 22^nd was the hottest day I can ever remember.  The pavement in Manhattan was 147 degrees and I could tell that my commute home was going to be awful.  Luckily, I wasn’t on the 1:34 to New Haven.

That train on time with three of its cars lacking good air conditioning, so the remaining cars were standing room only.  Just past the Westport station, an aging pantograph snared the overhead catenary (power) line, sagging in the heat, and the train lost power.  No electricity meant no AC, no radio and no PA system.

Eyewitnesses on the train tell me people started panicking as the temperature rose.  They asked a conductor to open a window or door, but he refused. Finally, two passengers opened emergency evacuation windows, pried open the doors, jumped out and walked down onto the tracks.

Realizing that they faced an emergency and with no aid or communications from the conductors, people pulled out their phones and dialed 911.  The railroad wasn’t going to rescue them, so maybe the police could.  People were crying, fainting, throwing up.  At least three pregnant women were in distress.

Stranded passengers at Greens Farms seek shade after leaving stalled train.

  Photo courtesy of WestportNow.com

When their 911 screens “lit up”, Westport Police called Metro-North headquarters asking the location of the train. At first they were told the train was empty, which delayed an EMS response.  After almost an hour in these unbearable circumstances, the train limped into the Greens Farms station where rescue workers from Fairfield and Westport tended to the sick and passed out water.  On the platform, the digital displays mocked the crisis by reading “Good Service”.

About the same time, the 12:07 pm from GCT became disabled between Stratford and Bridgeport.  The 3:27 pm from New Haven suffered the same fate nearby, also because of the pantographs snagging the drooping power lines.

At 4:45 pm I arrived at GCT, having heard of “wires down” delays from Clever Commute.  Luckily, I could grab a diesel train, the 5:10 to Danbury, which had minimal air conditioning but might bypass the delays beyond Norwalk where it would hang a left and go up the branch.

In fact, there were four Commuter Council members leaving GCT at about that hour, each on a different train.  By e-mail, we compared notes on our delays and the total lack of communications about the problems ahead.  On none of our trains was there any announcement.  I asked my conductor what he knew, hearing the Metro-North radio crackling on his hip. “They haven’t told us anything,” he said.

A commuter using Clever Commute first reported the wires problem at 3:23 pm.  It wasn’t until 4:15 pm that Metro-North’s e-mail alert system finally posted a vague message of “heat related instances” and “35 - 45 minute delays” from Stamford to New Haven. “Instances”?

Rush hour was screwed.  Dozens of trains pouring out of GCT would be delayed.  And because New Haven to NYC trains had been totally suspended, needed equipment could not arrive at GCT to take folks home.  Friday evenings are always a problem on Metro-North.  This would be one for the history books.

It’s not Metro-North’s fault that our catenary is so fragile… snapping in the bitter cold of winter and sagging in the summer’s heat.  And it’s not Metro-North’s fault that the pantographs on our 40-year-old trains can’t be adequately maintained.

Anybody who has ridden Metro-North over the years knows that “stuff” happens.

But Metro-North is responsible for its horrendous, potentially life-threatening lack of communications.  On the trains, at the stations and via e-mail, their silence and ambiguity about Friday’s crisis are just the latest in a litany of disregard for the commuter, their customer.

The Commuter Council has documented many similar incidents in the past.  Each time the railroad said “We’ll try harder”.  They have obviously failed.

Personally, I think Governor Malloy or the legislature needs to call an emergency hearing, calling Metro-North to task.  Nobody at that railroad ever seems to take the blame or responsibility.

This time, the hottest day in recent memory, thousands sweated and were delayed, but nobody was hurt.  Next time, we may not be so lucky.