Stupid Planning queries #11 -- Where and what are my Smart Lists

Where oh where has my Smart List gone?

Smart Lists are a wonderful thing. Well, that may be slightly exaggerating their usefulness but if you want to create a drop down list in a Planning Account (or other dimension but I have never seen it) Smart Lists are the way to go. Come to think of it, they are a bit of a Hobson’s Choice (a truly fantastic movie) if you want dropdown lists in Planning forms.

Planning even gives you a great way to view the Smart Lists in your Planning application by simply going to the Administration->Manage->Smart Lists menu.

Here is a (rather short) list of Smart Lists in my sample Planning app:

Live and in person
This is a very silly and pointless form that nevertheless shows a Smart List with nothing selected.

Clicking on the down arrow:

Selected Yes


Saved to Essbase


FWIW, if I pulled the Account YesOrNo in Essbase using a Smart View adhoc analysis link, I would get a 1 in that cell as, bizarrely, Smart Lists do not resolve to Essbase Text Measures. I will try not to think about why that is the case as their functionality is the same. Different development groups is the best explanation I can think of but it is frustrating. Onwards, regardless.

So all of this is great, and more than a little basic
Yes, I know, what is there to query if you just created the Smart List? Well, in the case of this sample Planning application, there is no real reason to query much of anything as we know what the Smart List is and what member it’s tied to.

But what if you didn’t know what member the Smart List was assigned to? How would you know? As far as I can tell, there is no magic report in Planning that will give out this information.

A different story
And what if you were working on a Planning migration/modify project (ahem) and the Planning application had 31 Smart Lists, and somehow the association of Smart List to member got lost (oh, thank you accursed EPMA), and you had to go back to the original Planning app to figure out what goes where? What would you do then? Scream? Cry? Curse your bad luck? Or how about write a query that looks just like this?

The query

/*

    Purpose:     Figure out what Smart Lists are assigned to which Members
    Modified:    23 February 2013, Cameron Lackpour
    Notes:       This is a nice and easy one, isn't it?
*/
SELECT
  O.OBJECT_NAME AS 'Member',
  E.ENUMERATION_ID AS 'SL #',
  E.NAME AS 'Smart List'
FROM
  HSP_MEMBER M
INNER JOIN
  HSP_ENUMERATION E ON M.ENUMERATION_ID = E.ENUMERATION_ID
INNER JOIN
  HSP_OBJECT O ON O.OBJECT_ID = M.MEMBER_ID


And that produces a result set like this:


That hypothetical Planning application I mentioned above? Would you believe 31 Smart Lists of which 14 were actually assigned? Yup, 17 dead Smart Lists. Isn’t application maintenance a stinker? Apparently so.

Everything you ever wanted to know about a Smart List
Above I joined HSP_ENUMERATION (why wasn’t the tabled called HSP_SMARTLIST?) to HSP_MEMBER to get the link between member (in any dimension) and Smart List. But what if you just wanted a quick review of everything that ever made up a Smart List?

Query the second

/*

    Purpose:     Smart List contents by name
    Modified:    23 February 2013, Cameron Lackpour
    Notes:       Another Nice 'N Easy one.
*/
SELECT
      E.NAME,
      EE.ENTRY_ID,
      EE.NAME,
      EE.LABEL
FROM
      HSP_ENUMERATION_ENTRY EE
INNER JOIN
      HSP_ENUMERATION E ON EE.ENUMERATION_ID = E.ENUMERATION_ID
And that produces a result set like this:


And that’s it
I have to say that I wrote this blog post because I needed to get that list of Smart List associations to members and simply couldn’t find it on the series of tubes that make up the world wide web.   I’m sure it exists, somewhere, or maybe it was just so easy no one bothered to post it. Regardless, now world+dog has it.

I will note again what an incredbily helpful thing it is to write these queries – I cannot imagine going through each one of the Accounts in the application I am talking about (over three thousand across multiple Plan Types) and try to find the silly things – I’d have completely gone off my rocker (although I will admit it might be hard to spot when that happens) and I would have spent a *long* time trying to figure out where the non-assigned 17 Smart Lists should have been. Which was nowhere, thanks to the query. SQL saves the day yet again.

Be seeing you.

I cover the Antipodes

Okay, technically they’re only the Antipodes if you live in England. And, if you look at a globe, it’s easy to tell that this is just a figure of speech, not a direction for a Journey To The Center Of The Earth. In fact, near as I can tell, China fits for the States if I were to transfer the analogy to where I live. And that makes sense because that movie about Three Mile Island was called The China Syndrome and not The New Zealand Syndrome. (True story – I can remember as a kid my parents sitting at the kitchen table trying to figure out where to bug out if York, PA became a radioactive wasteland. Fun times, fun times. This stuff is safe, right? Riiiiight.) Have I lost everyone? Hopefully not, because there is good stuff to come.

Anyway, I am not in the nuclear power industry (and that is a good thing given my sometimes decided lack of attention and focus) nor am I going to China, but yr. obdnt. srvnt. is going to both New Zealand and Australia for two conferences. Yes, I am a glutton for punishment but I was asked and I said “Yes” before anyone could change his mind.

New Zealand
The New Zealand Oracle Users Group has its conference every 18 months. NZOUG 2013 is from 18 to 19 March 2013 in Te Papa, Wellington, NZ. In theory, I was the content chairman for the BI and EPM track at this conference but I have to admit that this really meant that I bugged, bothered, and pestered Erica Harris, Richard Philipson, and what seems like most of Oracle Australia/New Zealand (thanks to Kay Galbraith and Daniel O’Brien) with trying to figure out what would be appropriate content for NZ and who oh who would present. They did a great job identifying people to speak. NZOUG does quality work and their agenda is very strong. I plan on checking out the other tracks (something I never seem to be able to do at Kscope) while I am there as well as presenting two sessions, one on ODI and data quality (hey, come to NZ or buy my book and read my chapter on this) and the other, excitingly, on Dodeca. Now I just have to finish writing it.

Check out the agenda here.

Here’s what’s planned for BI and EPM:

• Exploring Oracle BI Apps: How does it work and what do I get ? - Richard Philipson
  James & Monroe
• The spreadsheet management system known as Dodeca - Cameron Lackpour
  CL Solve
• Oracle Business Intelligence: Model First, Build Later - Daniel O'Brien
  Oracle Corporation
• Slay the evil of bad data in Essbase with ODI - Cameron Lackpour
  CL Solve 
• Infrastructure Preparations for Hyperion EPM 11.1.2.2 - Charles Naslund
  Indigo New Zealand
• Oracle BI Apps - Recommendations for Effective Implementation - John Hooker
  Asparona
• EPM Case Study: Rank Group - Richard Philipson
  James & Monroe
• Closing BI/EPM Panel - Cameron Lackpour
  CL Solve

The important bits are: NZOUG 2013 is from 18 to 19 March in Te Papa, Wellington and costs a mere 795 +GST NZD if you are a member and register under the early bird scheme. Read the full agenda – there’s amazing value for money.

Australia
Ah, another country, and a slightly different group of people to exasperate, although in this case it’s fellow board member and Oracle ACE Bambi Price that I think I annoyed the most and of course Oracle Australia (hi, Kay, and yeah, I owe you). Again, I helped out with the agenda and yes, I have written about this before for the ODTUG blog where you can read all about it.

This is an ODTUG Seriously Practical conference (NZOUG is their own full Oracle product line show show, I am just there to present and help with the BI and EPM content selection) and as such will focus a deep dive into the technical end of the BI and EPM tools. Yes, I am presenting the same two sessions at this conference and no, there will not be many NZers (I just made that word up as “Kiwis” is a bit twee) in Melbourne so I don’t view this approach as a rerun. More like a keep-Cameron-on-the-ragged-edge-of-sanity-because-he-takes-too-much-on approach.

Check out the agenda here.

Here’s what’s planned for BI and EPM:

• Haleem Jan Babar, Oracle – What’s coming in BI and EPM
• Debra Lilley, Fujitsu – BI and EPM in Fusion
• Richard Philipson, James & Monroe – Exploring BI apps
• Christine Aird M-Power Solutions – HFM good practices
• Maneesh Disawal, James & Monroe – OBIEE dashboards
• Eman Seif M-Power Solutions – ASO Essbase
• Paul Anderson, Oracle – Proactive Support
• Yr. Obdnt. Svnt., CL Solve – ODI/Essbase/data quality and Dodeca

The important bits are: the ODTUG SP Australia is from 21 to 22 March in Melbourne and costs a mere 599 AUD. Read the full agenda – there’s amazing value for money. Again. 

This is pretty exciting stuff
Okay, the flight in economy from home to NZ to Aus to NZ to LA to home is not exciting. At all. But helping out with BI and EPM geeks on the other side of the world is exciting. Yes, they have odd sounding accents (of course to them I’m the one with the weird way of pronouncing things and the incomprehensible slang) but their passion and commitment to technical knowledge, sharing, and evangelism is just like what you see here in the States with ODTUG’s events. I’m beyond happy and proud to help out and I’m hoping that both events will be a great success.

Thanks to the magic of Google Analytics, I know that both NZ and Australia read this blog. Australasians, if you have ever wondered what kind of idiot I am in person, now’s your chance. :) Seriously, they’re both good presentations and you can always go get a cup of tea (ah, real tea, I wonder what the NZ/Aus. version of Typhoo or PG Tips is) if I prattle on too much. I hope that you’ll be able to come to the conference that is closest – as you can see from the above there’s really some great content on offer.

Be seeing you.

One Of These Things (is Not Like The Others)

A different kind of blog
I’m sort of a boring guy (go on, just ask those who know me personally) and I’m definitely a busy geek (you will have to decide if that is because I do a lot, or try to do too much, or simply have lousy time management skills, I tend to think the last) and thus most of the blogs I read are 100% technical and informative -- how do I do this, why doesn’t this work, what’s the workaround for this, what are the concepts behind that.  You get the idea – boring + busy + always scrambling for ideas = technical blogs, not blogs that espouse philosophy, or a particular Weltanschauung.

But I think maybe I’m doing myself a disservice with this kind of focus.  Simply being technically proficient (you decide to what extent I fit that description) is not enough to make me a well-rounded consultant or even person.  I don’t write philosophical (opinionated yes, philosophical barely) blogs on the state of EPM, but is there someone who does?  Why yes there is.

It’s a secret

Well, I’m not 100% sure it is secret, but at least it isn’t a well-publicized blog.  What am I talking about?  The “hidden” counterpoint to The Travelling Consultant.  Take a gander at the url for that blog:  http://thetravelingconsultant2.wordpress.com/  Btw, I like this blog, a lot, strictly on technical grounds.  But moving on…

Do you see the “2”?  The number sort of implies that a predecessor exists (Thanks, John and no, he isn't the author, just more observant than I).  Try this url:
http://thetravelingconsultant.wordpress.com/

Ah, now we have something very different.  A personal blog but from a (mostly) EPM consulting perspective.  There is some very inside baseball, or industry specific stuff here (all names, places, and dates quite rightfully redacted but very interesting) and if you ever wanted to know what it’s like to be an EPM consultant, or what consultants are like, or the nature of EPM consultancy, I can’t think of another place to find it.  I would also recommend this for customers who want an insight into how a consulting practice works and what you might (for good or for ill) expect to see in EPM consultants.

Yes, these are told from a particular point of view (one that I mostly agree with).  You may not agree with everything written (that is sort of the point of a personal blog) but I think the articles ring very true.  I applaud the Travelling Consultant, whoever that is, on his honesty and candor.  And sense of humor.  :)  And no, I am not the writer.

Some of the more interesting posts

This is pretty straight stuff with little controversy:
Consulting 101: Project Roles
Consulting 101: Project Phases

I got a lot of pleasure out of these Profiles In Consultancy.  And yes, I have met every one of these types.  I try not to think too closely about which one I most nearly resemble.
Consultant Profiles: the “Great Guy/Gal”
Consultant Profiles: the “Flake”
Consultant Profiles: the “Diva”
Consultant Profiles: the “Talker”
Consultant Profiles: the “Over Biller”

Want to be a consultant?  How does it all work?  Read on.  I think every new hire to a consulting firm ought to get the links to these stories as part of their welcome package, especially if they are new to consulting.
Consultant Policies

These are good but I’ll bet the really good ones are Too Dangerous To Print.  
War Stories

So wrote it?

That’s a secret, too.  I think if you read it, you’ll understand why.  The posts aren’t negative in any way but some of the truths they speak are…uncomfortable.  In the interest of self-preservation aka continued employment the writer is anonymous.  Such is life.

Go on, broaden your horizons

Given the non-technical nature of the blog, the great thing is that you don’t need to rush through it.  I wish there were more EPM blogs like this.  I do try to bring at least a little philosophy (some call it idiocy) to my posts but I fall far short of the other The Traveling Consultant.  I hope you enjoy it as much as I did.

Be seeing you over on the non-technical side.

Patching 11.1.2.x the wizard way

Introduction

Yes, the thought of me writing much of anything on infrastructure is slightly (completely?) laughable given my fully documented and freely admitted serial incompetence in this area.  However, not everyone has the luxury of saying, “Damn it, Jim, I’m an application consultant, not an infrastructure geek” and truth be told I get pulled into these situations, at least tangentially, from time to time.  And of course because I’m in a roll-your-own one man consulting band, I have to occasionally patch my development system as well.

You are likely not in that spot (once upon a time the EPM market was full of we hardy independent souls – we now seem to be a vanishing breed of which only the fittest/most stubborn remain but I digress) and need to do the patching, or at least manage, or maybe just have an appreciation for what is entailed in the process of patching and maintaining your company’s not-architected-for-scaredy-cats EPM system.

If this is a task that strikes fear into your heart (actually, if it doesn’t strike fear into your heart, you’re not paying nearly enough attention) then you are in luck for Oracle Support (thanks, T.) have pulled out all the stops and have produced their very own Patching & Maintenance Advisor: Enterprise Performance Management (EPM) 11.1.2.x(1517258.1).

What’s it all about, Cameron?

It is a wizard-based (you wondered about the title of this post, I’ll reckon) approach to:

1. Why you should patch (this isn’t too hard to fathom)
2. How you patch (ah, the details, where Old Scratch lives)

The goal is to get you to carefully consider the potential  impact, possible considerations, and concrete actions you need to take, step by step, from a 11.1.2.NotEverPatchedNoWayNoHow release to the very latest version of 11.1.2.WowEvenOracleDoesn’tKnowAboutAllOfThesePatches.

My story of woe, agony, and defeat, that you can now avoid

I wish this had existed, oh, about two years ago when I was on my very first 11.1.2.0 Planning implementation.  The installing consulting company (whom I shall not name ‘cause getting sued isn’t in my list of things to do) insisted that patching was not necessary.  I thought this was likely one of the dumber things I had heard from a consultant’s mouth (and yeah, I’m a consultant and I say some pretty dumb things from time to time) but I had nothing to fight this with other than, “Why on earth would you not want to head off known issues?”  As you might imagine, he was Infrastructure, and I was Applications, and that battle was lost before it began.  It was fun a few weeks down the road whist watching the client put them through the wringer when things didn’t work.  That patching I wanted up front eventually happened but oh what a waste of time and effort.

If I had had a time machine to get this patch advisor (cf. Mr. Peabody’s Improbable History and yes, I look disturbingly like his boy Sherman) from the then future, I could have gone to each one of the sections of Support’s step by step guide and rebutted their every claim with the icing on the cake of, “Well, that might be what you say, but Oracle Support (you know, the vendor) says…”  Yes, I have revenge dreams and whoever gets to do this really ought to have pity on me and post their success to this blog’s comment section for my Schadenfreude moment.  :)  

What oh what does it look like?

Just like this:

This is in line with other advisors I have written about before.  It is a wizard with subsections off to the left to show what needs to be completed in each section.

Wizard steps

Each overall phase of the patching process has individual step by step guides.  Every one of the guides takes you to a new document that, at the very least, provides food for thought.  And maybe ammunition against someone who maybe shouldn’t be in the installation game.  Ahem.

Evaluate

• Business Plan Value
• Increase Supportability
• Overview of EPM Patching
• Business Plan Considerations
• Glossary Of Terms

Plan

• Define Proactive & Reactive Patch Plans
• Define Patch Test Plan
• Define Patch Implementation Plan
• Identify Patches / Patchsets to Apply
• Read Patch Documentation
• Assess Impact
• Milestone Checklist and Feedback
• Glossary Of Terms

Test

• Apply Patch Test Plan
• Verify Patch Install Has Been Successful
• Verify That Backup / Recovery Works
• Document Lessons Learned
• Milestone Checklist and Feedback
• Glossary Of Terms

Implement

• Verify That Backup / Recovery Works
• Apply Patch Implementation Plan
• Verify Patch Install Has Been Successful
• Milestone Checklist and Feedback
• Glossary Of Terms

Pretty comprehensive, eh?  The advisor really spoon feeds the whole patching process.  Thanks, I need all the help I can get and I suspect I am not alone.

But wait, there’s more

Would you believe there are more goodies to be had?  As they might say at my current client (bonus points if you can place this by US state), you betcha.

What oh what oh what are the patches for each one of our beloved EPM products?  There’s a very nice and concise list of links to all of the patches (you can sort of see part of it in the screen shot above).  Of course it is now a pretty rare thing that only one product is being used at a time (I would say almost impossible given how Shared Services and sometimes EPMA are part and parcel of practically every implementation).  Would these have been handy during my losing argument with the installer, mighten it?  You betcha again.

Description	Document ID
Available Patch Sets and Patch Set Updates for Oracle Hyperion Shared Services	Document 1481942.1
Available Patch Sets and Patch Set Updates for Oracle Hyperion Financial Management	Document 1321453.1
Available Patch Sets and Patch Set Updates for Oracle Hyperion Reporting and Analysis, Financial Reporting and Interactive Reporting	Document 1360962.1
Available Patch Sets and Patch Set Updates for Oracle Hyperion Planning	Document 1395593.1
Available Patch Sets and Patch Set Updates for Oracle Hyperion Essbase	Document 1396084.1
Available Patch Sets and Patch Set Updates for Enterprise Performance Management Architect and Calculation Manager	Document 1400076.1
Available Patch Sets and Patch Set Updates for Hyperion Financial Data Quality Management and FDM ERPI Integrator	Document 1400561.1

Did you know there was a Hyperion Patch Reviews Community?  Nope, neither did I.  But now we both do.

May this bring an end to these questions on OTN

I am not totally sure why OTN (and to a lesser extent, Network54) has become the home of infrastructure-related questions when there are so many good resources in Oracle Support.  The two Johns (John Goodwin and John Booth) seem to answer most of these but I have to wonder if the original posters even bothered to look on Support.  Remember, if your company has an EPM product (actually, any Oracle product) you at least have read access to Support.  The answers Support come up with are the official word (this stuff gets vetted through a process ever so slightly more rigorous than OTN or Network54 or this blog) and ought to be at the very least your starting point for all things infrastructure.  With this patch advisor, I hope to never see (and never post myself) another patch question on OTN.

Be seeing you.