It's survey time

Introduction

The prelude to summer must be survey time in Oracleland as there seems to be a bumper crop of them at the moment.  

Instead of reading my usual caffeine-fueled hallucinatory gibberish, instead spend some of your precious free  time with three worthy surveys that are striving to make the tools we work with better.  How can we not take them?  Unless you like products that don’t get better.  Your choice.

Be quick though, these surveys have expiration dates, some of them are coming up quite soon.

The BI Survey 10:  The Customer Verdict

I hope that BARC (Business Applications Research Center) doesn’t have a problem with me cribbing from their survey launch page – hey guys, I’m trying to drive traffic to you.  <grin>  

What is the purpose of the survey?

BARC's annual survey gathers input from a large number of organizations to better understand their buying decisions, their implementation cycles and the business benefits achieved.

Who is BARC?

BARC (Business Application Research Center) is a leading independent software industry analyst specializing in Data Management and Business Intelligence. For more information on BARC please visit the BARC Homepageand www.BI-Verdict.com.

And they are vendor-neutral

The BI Survey has always adopted a vendor-independent stance. While vendors assist by inviting users to participate in the Survey, Business Application Research Center (BARC) - the publisher - does not accept vendor sponsorship of the Survey, and the results are analyzed and published without any vendor involvement.

Who should take the survey?

Business and technical users, as well as vendors and consultants, are all welcome to participate. If you are answering as a consultant, please answer the questions (including the demographic questions) from your client's perspective; we will ask you separately about your own firm.

Why should you take the survey?

As a participant, you will:

• Receive a summary of the results from the full survey
• Be entered into a draw to win one of ten $50 Amazon vouchers
• Ensure that your experiences are included in the final analyses

Also, I am told that the survey is short on Essbase, BI Enterprise Edition Plus, and BI Standard Edition.  BI?  What’s that?  Essbase?  That I (and we) know.  Go get ‘em.

When does the survey end?

This survey ends 18 June 2011.

Finally, how do you take the survey?

Go right to the ODTUG launch page here or directly to the BARC survey.

Oracle BI User Survey

Again, I will steal in order to drive business.

What is the purpose of the survey?

The Oracle Business Intelligence User Experience (BIUX) Team is committed to using customer feedback to continuously improve our BI products.

We would like to invite you to participate in our first large-scale BI user survey to inform us about your main job responsibilities, technical background, tool usage, and work environment.

Who should take the survey?

Our goal is to reach as many users as possible, so we would also like to ask you to help us spread the word by forwarding the survey link to other BI users in your company/institution. They can range from developers to business end users.

When does the survey end?

Soon, really soon – 31 May 2011.  There’s still time, but not much.
^^^This has been extended till 8 June 2011. 

How do you take the survey?

To access the survey, please click on the unique URL below.

http://www.oraclesurveys.com/se.ashx?s=705E3EFC3D7B563A

Or you can access this OTN post to read the original request.

And one I already talked about

You can read about it right here:  What the heck is the Q?  

I see that they fixed the typo and actually name the survey.  Bummer.  I really enjoyed the mystery but I can appreciate that it’s probably better to list out the name of the survey.  

So get going

This is your chance to have your voice heard.  Unless you are an International Man of Mystery and want everyone to be in the dark as to your true EPM desires and needs, you must participate in the surveys to have your voice heard. 

What the heck is the Q?

Introduction

Well, what is it?

It’s not the name of an avant garde Broadway show.  

It isn’t used for playing snooker.  Or pretending you’re Paul Newman.  Actually, I’ve hung around with a lot of Essbase people – we’re more built like Jackie Gleason.

But I digress, yet again.  The Q in question is at the top of the Essbase forum, pinned to very tippy-top.

So what is it, anyway?

What can be seen

That seems mysterious.   And intriguing.  What do you suppose it does?  Will I ever end with the leading questions?  Yes, indeed, and I just gave you a clue.  


It’s a little different over on the Planning and Budgeting board.
What!?!  Those no-good Planning guys get the whole question!  Hmmph, bunch of second raters, if you ask me.  The cool guys got the puzzle but it had to be spelled out for Planning people.  As is typical.

NB – I have been implementing Planning since 2002.  Or was it 2001?  See, I told you Planning consultants were slow.

Essbase guys are way cooler – we (Did I mention I’ve worked with Essbase since 1993?  Or was it 1994?) just get a single letter and have to fall back on our innate intelligence, good looks, and better breeding to figure this out.  

Alternatively, you could skip all of this drivel and just click on the “Q” link.  I did.

First a tirade of praise

It’s that great big company asking you for help again.  I’m apologizing in advance of my mini-rant to all of the fine people who used to work at Hyperion and now work for Oracle.  Okay, apology over, and so the frothing at the mouth can begin.  Can anyone remember Hyperion Solutions asking us, the users and implementers of their products, for help?  Ever?  Publicly?  

Nope, thought not.

It continues to amaze me that a company Oracle’s size gets it.  But they do, and this is further proof of it.  And no, I don’t work for them nor do I own any stock.  But I do make a living off of their products and their success is my success.  I want, no, I need Oracle to succeed.  If you’re reading this blog, the same is true for you as well.

So what did I find?

Oracle’s asking us for help.  How can we not answer?

Click on the link

And you will find a five minute survey on documentation.  Fill it out, won’t you?  This is one of those moments where your self-interest and Oracle’s coincide.  Unless you like bad documentation.

How to go beyond the survey

Some of you may be familiar with this little conference I have been talking about of late:

Did you know that Oracle Support will have a lounge at KScope?

And that Oracle will be presenting at the conference?

And that this is your opportunity to engage in conversation with Oracle management?  

Yessir, it’s all true.

So what on earth are you waiting for?  

Take the survey and then show up at KScope – for the Geeks Who Matter.

See you there.

Stupid Planning Queries #1 -- Forms and Task Lists

Introduction

As many know, sometimes to their indescribable agony, Planning uses a relational store for application metadata, whether it’s built in EPMA or in Classic Planning.  Perhaps agony should be used in a past tense (agonied?) because historically Planning refreshes from that relational store to Essbase could be…problematic, but I haven’t run across that issue since the last days of Planning 9.2.  May Hyperion Desktop stay in its grave forever, amen, In saecula saeculorum.  

Okay, that’s the bad, but now that issue is fixed, and it turns out that the relational store has all kinds of goodies in it that you can exploit for fun and profit.

You have been warned

EVERYTHING YOU READ HENCEFORTH IS COMPLETELY AND 100% NOT SUPPORTED BY Oracle.  PROCEED AT YOUR OWN RISK.  Do something stupid like mass inserts into tables and go crying to Oracle Support that “Planning doesn’t work” and they’re going to ask what recreational drug you were taking when you thought mucking about with Planning tables was a good idea.  And will then ask if you have a backup.  Which, if you did something dumb like the above, you likely don’t.  Don’t be a dope – only do SELECT queries (hey, that’s all I do) and until you’re comfortable with this, do it on a backup of the SQL data store.  And don’t come whining to me either.  Growl, snarl, whimper, etc.

But it’s not so bad

So now that I have (hopefully) scared you into Col. Jeff Cooper’s Color Code Orange, be aware that many consulting companies, yr. obdnt. svnt. included, use queries like this all the time to get information out of  Planning metadata (and data too, in a later post) repositories.

SELECTs can’t kill anything, other than the performance of your SQL server, and really, we’re talking about very small queries.  Non optimized SQL code is not going to be an issue because the data sets are so small.

I am not a SQL programmer, but I play one on TV

If you have a SQL background, you may look at some of the code I write and think, “Why on earth did he code it this way, everyone knows that this (insert whatever cool SQL technique you know and I don’t) does it in a fraction of the time.”  Fair enough, I am just a SQL hack.  Essbase is another story (I like to flatter myself), but I agree with you in advance, my SQL is self-taught and there are definitely things I do in a cack handed way.  

So post in the comments

I have no ego in this – given the above admission, if you’ve got a better way, post in the comments or email me.  I will update the posts (this is going to be a series) and give full credit if you want.  Or you could be the Mr/Mrs/Miss/Dr/Rev Anon SQL.  You choose.

One last thing

None of this would have been possible without Dave Farnsworth’s “Planning Tables, What’s Behind the Curtain?” description of the Planning schema.  Dave gave this presentation at last year’s ODTUG Kaleidoscope conference in Washington, DC and it was a SRO session.  I’ve earned in billable house many times over the cost of my hotel and airfare (free admission as I presented) through Dave’s generously given away knowledge.  The conference is incredible value for money.  And the spirit of sharing is inspirational.

KScope11 is this year’s place to be for The Geeks That Matter.  I can’t think of any other conference that provides this amount of information.  My only quarrel with it is that I can’t be at multiple sessions at once – there is so much good information it is somewhat frustrating that I haven’t invented human cloning.  But I digress.  Sign up for this year’s conference while you still can.

Two simple queries

I’m going to start this series off with easy stuff to ease my adoring (ahem) readers into the somewhat murky world of Planning’s metadata repository.  It will get much cooler/harder to read in the next post.

NB – All examples are from the Planning sample application and I am using SQL Server 2005.  The code is almost identical in Oracle’s PL/SQL.  And oh yes, this is against Planning 11.1.1.3.  When 11.1.2.x-specific examples arise, I’ll note what version I’m on.

Give me my forms

Oh sure, you can go into Planning as an admin and then navigate the forms through Manage Forms but you have to go folder by sub-folder by sub-folder and it’s a right pain.  And how do you print it out?  Screen shots?  Wouldn’t it be nice to just have a list?  

Query to get list of forms

SELECT O.OBJECT_NAME AS 'Form Name'
FROM HSP_OBJECT O
INNER JOIN HSP_FORM F
   ON F.FORM_ID = O.OBJECT_ID

Form query output

Form Name

Balance Sheet

Cash Flow

Financials Summary

Income Statement

Plan Revenue - All Years

Plan Revenue - Assumptions

Plan Revenue - COS

Plan Revenue - Detail by Year

Plan Department Expenses

Plan Facilities Expenses

Plan Operating Expenses

Review G&A Expenses

Allocation - Expense

Allocation - IT

Allocation - Marketing

Allocation - Facilities Expense

Allocation - Segment

Give me my Task Lists

By the same token, wouldn’t it be nice to know how many Task Lists you have and what’s in them.

Query to get Task Lists

--Task list query
SELECT
    O2.OBJECT_NAME AS 'Task list',
    O1.OBJECT_NAME AS 'Task',
    CASE T.TASK_TYPE
        WHEN 0 THEN 'Descriptive'
        WHEN 1 THEN 'URL'
        WHEN 2 THEN 'Web form'
        WHEN 3 THEN 'HBR'
        WHEN 4 THEN 'Workflow'
        ELSE 'Unknown'
    END AS 'Task Type',
    T.DUE_DATE AS 'Due Date',
    T.ALERT_DATE AS 'Alert Date',
    T.INSTRUCTIONS AS 'Instructions',
    T.DEPENDENCY AS 'Dependent task ID',
    T.ALERT_FREQUENCY AS 'Alert Frequency',
    T.OVERDUE_FREQUENCY AS 'Overdue Frequency',
    O3.OBJECT_NAME AS 'Form Name',
    T.STR_PROP1 AS 'URL',
    O4.OBJECT_NAME AS 'Workflow Version'
FROM HSP_TASK T
--Get the task name
INNER JOIN HSP_OBJECT O1
    ON T.TASK_ID = O1.OBJECT_ID
--Get the task list name
INNER JOIN HSP_OBJECT O2
    ON T.TASK_LIST_ID = O2.OBJECT_ID
--Get the form name, left outer join because not all tasks have forms
LEFT OUTER JOIN HSP_OBJECT O3
    ON T.INT_PROP1 = O3.OBJECT_ID
--Get the workflow Version, left outer join because not all tasks have versions
LEFT OUTER JOIN HSP_OBJECT O4
    ON T.INT_PROP2 = O4.OBJECT_ID

Task List output

NB – The output from this was so big, I had to screenshot it.  If you want the full output in all its glory, I have it as an Excel file here.

Hope you liked it

I’ve got a veritable treasure trove of these.  Anticipate (or dread) many more.

Happy Planning Hacking to you all!

Cool free stuff from Oracle and how to get more of it

Introduction

Do ginormous multinationals just give away valuable information?  Apparently, if their name is Oracle, and the department is Oracle Global Support, the answer is, “Yes.”  Pretty amazing as you’ll see in a moment, and the quality is good.

What is the free stuff?

How about every new version installation note, release note, mandatory patch list, supported platform matrix, troubleshooting, general knowledge, and tool tip Oracle Support can come up with, by product?  

As an example, the Essbase (my very favorite database in the whole wide world) Master Note Index [ID 1105886.1] covers, in the very first Troubleshooting article (there are seven articles in total as listed below this table):

Article	Sub-topic	Note
Document 1291202.1 Oracle Hyperion Essbase and Aggregate Storage (ASO)	How to archives	Doc ID 584534.1 How To Create an Aggregate Storage (ASO) Database
		Doc ID 584913.1 How To Find The Size Of An Aggregate Storage (ASO) Database in Essbase?
		Doc ID 584585.1 How To Set The Aggregate Storage (ASO) Pending Cache Size Limit?
		Doc ID 980315.1 How To Automate The Aggregation Process On An Aggregate Storage (ASO) Database?
		Doc ID 980377.1 How To Assign Expense Reporting Tag in an Aggregate Storage (ASO) Database?
	Unable to Compact ASO Outline	Doc ID 1291128.1 Unable to Compact an Aggregate Storage (ASO) Outline
	Cannot Load Outline into Memory	Doc ID 947934.1 Error "1250053 Cannot load outline into memory" When Editing an ASO Outline After Upgrading to Essbase 9.3.1.4
	Sort Operation Ran Out of Memory	Doc ID 596566.1 Error: "Sort operation ran out of memory. Please increase the size of aggregate storage cache."
	Error Updating ASO Outline	Doc ID 800076.1 Error Updating ASO Outline "Hierarchy [DimensionName] is too complex: maximal width member number exceeds 64 bits. See application log for details."
	Data Load Takes Longer	Doc ID 816703.1 ASO Data Load Takes Longer in Essbase 9.3.1 Compared To Essbase 9.2
	Failed to Open File	Doc ID 888642.1 When Creating Aggregate Storage (ASO) Database Error "1270076 Failed to open file [%s]: a system file error occurred. Please see application log for details"
	Migrating ASO Outline Crashes EAS	Doc ID 1223357.1 Migrating an Aggregate Storage (ASO) Outline Crashes the Essbase Administration Services Server (EAS) When Using 64 bit Installation
	Outline Restructure Takes Longer	Doc ID 755871.1 Restructure of an Aggregate Storage (ASO) Database Takes a Long Time When Outline Change Logging is Enabled

There are also these articles:

Article

Document 1089114.1 Oracle Hyperion Essbase and ODI

Document 1089108.1 Oracle Hyperion Essbase and Shared Services

Document 1090635.1 Oracle Hyperion Essbase and 64 Bit

Document 1105799.1 Oracle Hyperion Essbase and API Scripting

Document 1082973.1 Oracle Hyperion Essbase and SmartView

Document 1105876.1 Oracle Hyperion Essbase and EIS

Document 1090680.1 Oracle Hyperion Essbase and Partitioning

Document 1105830.1 Oracle Hyperion Essbase and SQL Interface

Document 1105831.1 Oracle Hyperion Essbase and Unicode

Document 1105894.1 Oracle Hyperion Essbase and Product/Technical Issues

Each one of those articles has individual notes.  Amazing stuff, eh?  This is just for Essbase.  There’s a lot more out there.

Let’s define “free”

Okay, a qualification – this really cool stuff is free if you are a customer who has a valid maintenance contract or an up to date Gold partner (I’m pretty sure below that level it’s no soap, radio, although if a Silver or below partner logs in a gets this stuff, let me know and I’ll amend this post ) or above.  Meet those requirements, and all this is yours.

But wait, there’s more

You say you love an Oracle EPM product other than Essbase?  Hard to believe, but true.  Apparently Oracle shares the love, because there are guides for all of the products (okay, I didn't search for Close Management or Profitability but I'm willing to bet they're out there -- first one to comment on this post with the link wins a mention and I'll revise accordingly) in the form of pdf files.  That means there are many, many notes chock full of good information.  More than you can imagine and if you can log onto http://support.oracle.com, you can have them for the asking.  

Planning

Did you know there was also a Planning version of this, at
Oracle Hyperion Planning Master Notes Index [ID 1083483.1]

Essbase

Yes, I covered this before, but for the sake of completeness:
Oracle Hyperion Essbase Master Note Index [ID 1105886.1]

Financial Data Quality Management

And of course one for FDM:
Oracle Hyperion Financial Data Quality Management Master Note [ID 1126261.1]

Hyperion Financial Management

I can’t imagine there are too many green-shaded geeks at Cameron’s Blog For Essbase Hackers, but in case you care:
Oracle Hyperion Financial Management Master Note Index [ID 1083469.1]

But wait, there’s even more than there’s more

Did you know about the Notes Indexes?  As Alfie Elkins would say, “Not a lot of people know that”.

Do you want to know how to more effectively use Oracle Global Support?  If so, ODTUG KScope11 is the place to be.  

Is this an idle promise?  Nope.  On Wednesday, 28 June 2011, at 3:00 pm (that would be getting towards the end of the Greatest Oracle EPM Conference Ever, aka ODTUG KScope11), I’m hosting the Tech Support panel session where we will focus on good support practices – these Master Notes Index articles are but one example.

I used the word “we”.  I exaggerated -- pay no attention to me as I’m just the idiot kicking off the session and running around the room with a microphone taking your questions.  It’s Oracle’s support organization that is up on the dais and they’re the ones to whom you should ask all of your support process questions.  

More stars than there are in the heavens

No, this isn’t MGM circa 1939, but the fantastic group of Oracle Support staff that will be at this symposium.

Name	Title	Technical area
Carol Crider	Sr. Principal Tech Support Engineer	Essbase
Larry Geraghty	Principal Tech Support Engineer	HFM
Matthew Hendricks	Principal Tech Support Engineer	Support/development relationship
Kamila Pavlaskova	Principal Tech Support Engineer	Foundation/infrastructure
John Kellogg	Sr. Mgr. Customer Support - EPM/BI	Kibitzing from the crowd
David Valociek	Sr. Dir. Customer Support - EPM/BI	Kibitzing from the crowd

These are some of the most best support people Oracle has – I know one fellow blogger (Hi, Glenn) who had this to say about Carol, “I am thrilled that Carol Criter will be there, I really admire her.  She has been around forever [and] is like a super support tech. She works with people on really big problems.  I worked with her on [Client X’s] issues”.    Dear readers, you may not believe this, but Glenn really did IM the above to me this evening, completely unprompted.  If Glenn’s excited, I’m excited, and you should be as well.

Would you believe even more than even more than there’s more?

Last one, I swear – did you know that KScope11 is going to have a Support area at the conference itself so you can go directly to Support and interact directly with Global Support superstars?

Tim Tow is owed a big round of applause for this – he’s championed it from the beginning and Oracle, in the forms of Al Marciante and David Valociek, more than stepped up to the plate with resources and people.

I’m happy just to bring you the news.

See you in Long Beach.