28 August 2011

More cool free stuff from Oracle -- Essbase advisors

Introduction

Can anyone picture me as Hannibal Smith, late of the A-Team? No? Why ever not? I do so love it when a plan comes together.

What’s the Plan, Stan? Unless you are a New Zealander, that link isn’t going to be terribly helpful.

Nope, the plan is to have people (you know who you are, and thanks) send me material for this blog so I don’t have to strain my brain trying to come up with interesting content. And oh, this one is interesting.

More cool free (so long as you are a customer or partner) stuff from Oracle #3

Oracle Support has always had a lot of good Essbase information buried in its vaults – knowledge base articles that: highlight the most common and popular product questions, list resources, illustrate good practices, highlight troubleshooting tips, cover the seemingly never ending installation and configuration issues that clog up OTN and Network54, review upgrade steps, figure out what release of what works with what, and generally advise on how to get the most out of Essbase. It’s all in there, but scattered amongst many documents.

The solution

Oracle has brought all of the above into a new document, Overview Advisor: Oracle Hyperion Essbase [ID 1344571.2].

This is pretty awesome stuff, as that advisor, in addition to its great information also links to:

No more complaints about not being able to find cool Essbase information in a central location, right?

Let’s sample just one of the links

There’s way too much good information going on here for me to try more than one – and get this – the content changes as new information comes in so any attempt by me to list it all would be quickly out of date.

Regardless, to give you a flavor of this, I’m going to cover the articles by topic in just the Troubleshoot Advisor.

Troubleshoot Notes

Problem Solution Documents

Reference Articles

Diagnostics

Troubleshoot Community

Oracle Hyperion Essbase Community

The above is approximately 15% of the content available in the Essbase Advisor. That yearly maintenance fee doesn’t seem so bad now, does it? :)

Conclusion

We really owe the folks at Support a big thank you for this – they’ve identified the most important and popular links and brought them all together in one spot. That means you spend more time learning and solving problems and less time searching. What’s not to love? I can hardly wait for the Planning and ODI advisors.

20 August 2011

Quand Le Monde Parlait Essbase en Francais

When the world spoke Essbase in French

Introduction

With apologies to Marc Fumaroli, did you know that French was once the world’s, ahem, lingua franqua?

French? Cameron? Mais bien sûr. Have you ever gazed upon my Gallic nose? Am I not the modern Jean Gabin of Essbase? Non? C'est juste trop mauvais.

Okay, enough with my French ancestry (all-American mutt at your service) and my love of French cinema – why am I going on and on about this?

An exciting new book

I won’t admit to how long ago I stopped my French lessons – let’s just say it was a long time ago. And that’s a pity, not just because it highlights my ignorance, but because I can’t read an exciting new book titled Oracle Hyperion Essbase: Master the world of analysis and performance management. That link takes you to the translated version, here’s the real deal. The printed copy is €51.30, the electronic copy is €43.20.

An important note – those Euros you see next to the book are not because I’ve upped sticks and moved back to Europe – this book is in French and you better be able to read it without recourse to a French/English dictionary. It’s going after the not inconsiderable Franocphone market. And remember, in the Old World, people aren’t completely monolingual like we Yanks, so the market is somewhat greater than the former French Empire.

Who wrote it?

Network54’s very own Sébastien Roux, along with coauthors Wojtex Janeczek, Antoine Dinimant, and Laetita Terlutte.

What’s it all about?

From the back cover

This book about Oracle Hyperion Essbase (addressing versions 6, 7, 9 and 11) is intended for all people in the world of analysis and BI: for IT professionals on the one hand, for business people on the other (finance, FPM, analysts, and so on), without any pre-requisite IT or accounting skill. Essbase original technology, based on multidimensional (OLAP) modeling, eliminates the gap between IT and business people and places them on a similar level. The book thus starts from a beginner level, let the reader come in where he wants, and drives him throughout the software to an advanced level.

The approach is resolutely educational and is based on examples and case studies. Each chapter, designed as a course, presents Essbase various features through examples. A dozen workshops, spread at chapter endings, gives some practice with the chapter’s key notions. All exercises have detailed answers, with not only the key but also the reasoning. Exercise files are available for download from www.editions-eni.fr.

This book walks you round the whole Essbase solution, from designing a model common to users and techies, to the most advanced features of version 11.

The four authors, who have experienced very different educations, are recognized Essbase specialists, each of them being (or having been) consultant as well as trainer; which ensures the dual technical and educational expertise necessary for such a book. The variety of their professional and educational experiences reflects the versatility of Essbase and allows them to encompass all aspects of a very rich software.

The table of contents (in English, remember, this book is in French)

Foreword
Introduction: Essbase and BI systems
Part One: BSO architecture and use

Modelize an Essbase cube (Workshop: modelize a cube from users’ reportings)
Create the Essbase cube (Workshop: create the Country dimension with alternate hierarchies and Shared Members)
Automatic dimension building (Workshop: build dimensions, alternate hierarchy and alias table)
Data Import (Workshop: load free-form, load with DLR)
View Data with the Excel Add-in (Workshop: common errors, create reports with mouse only, use Member Selector)
Other Excel tools (QD, Smart View, Visual Explorer)
Advanced Export and Reporting (Report Scripts, DATAEXPORT, etc.)

Part Two: BSO calculation

Dense and Sparse
Dynamic Calculation and Formulas (Workshop: moving periods with years outside Time dimension)
Calc scripts (Workshop: sample budget planning)
Calculation commands, operators and functions
Advanced outline features (Workshop: Varying Attributes)
Currency conversion

Part Three: ASO cubes

Create and modelize an ASO cube (Workshop: migrate our sample cube from BSO to ASO)
The MDX language in Essbase (Workshop: drills on formulas and queries)
Parameter and optimize an ASO application

Part Four: Essbase administration

Administer, copy and backup cubes
Essbase native security and security filters (Workshop: filters vs access levels, Essbase styles, Metaread)
Security with Shared Services
Partitions
Optimisations

Part Five: Program Essbase

Administration languages : MaxL and ESSCMD
Essbase and Visual Basic (Spreadsheet Toolkit, API – workshops : one button to refresh all tabs, custom VB form)
Essbase and Java (JAPI, CDF)

Appendices

Install Essbase on your computer
Essbase Studio
The Oracle Hyperion suite
Third-party tools and applications
Application Manager: Essbase 6 administration console
References

Conclusion

It’s a clever approach: write it from beginner to expert so readers can pick what they want, use examples and case studies via workshops, have exercise files available for download, and approach it from multiple levels and perspectives courtesy of the different experiences of the authors. It’s quite an accomplishment and knowing Sebastien as I do, it can only be good for those of you lucky enough to read French.

And oh yes, any book that shows you how to keep on using the beloved Application Manager has got to be awesome.

One more thing: Sebastien knew I’d give a good review when he described me as “expert Essbase et Planning et gros contributeur des forums”. :)

11 August 2011

My take on the most awesome conference, anywhere, ever, ban none

Yeah, I liked it

The one or two regular readers of this blog who aren’t my direct relation may have wondered why I’ve not written about my quaint and curious experiences at that most excellent of all Oracle conferences, KScope11.

In fact I did write about it, quite a bit actually, on the flight back from Long Beach. As I am not in the President’s or Admiral’s Club (Naval rank for an airline’s frequent travelers lounge is something I’ve never understood unless the plane has one of these Fly Navy signs on the side. Were it labeled the Air Commodore’s Club it would at least be the right Service. Such are the mysteries of marketing to a moderately-logical geek.) I was in steerage and didn’t even try to take out my laptop in the cramped confines of an airline seat. Once home, I was just too darn tired to type in what I wrote out on foolscap, other blog posts came up, and although I intended to get around to writing it up for this blog, I just procrastinated. Why try to dissemble?

But I was called to task by Mike Riley, Oracle ACE, President of ODTUG and all around nice guy when he asked for a paragraph from each member of the board re their impressions of KScope. As per usual, once started I was a perpetual motion machine. Without any further preamble, read it and weep at the ODTUG blog right here.

31 July 2011

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.

28 July 2011

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:

The purpose of the “Personal” VERSION_TYPE?
The difference between a Private and Public ACCESS_TYPE? Or even what ACCESS_TYPE might mean?
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!

Cameron's Blog For Essbase Hackers

28 August 2011

More cool free stuff from Oracle -- Essbase advisors

Introduction

More cool free (so long as you are a customer or partner) stuff from Oracle #3

The solution

Let’s sample just one of the links

Troubleshoot Notes

Problem Solution Documents

Reference Articles

Diagnostics

Popular Solution Documents

Troubleshoot Community

Conclusion

20 August 2011

Quand Le Monde Parlait Essbase en Francais

When the world spoke Essbase in French

Introduction

An exciting new book

Who wrote it?

What’s it all about?

From the back cover

The table of contents (in English, remember, this book is in French)

Conclusion

11 August 2011

My take on the most awesome conference, anywhere, ever, ban none

Yeah, I liked it

31 July 2011

More cool free stuff from Oracle, upgrade edition

Introduction

Upgrades are us

Conclusion

28 July 2011

Stupid Planning queries #4 -- Version

Introduction

Questions, questions

Some call it bragging

The fix (in SQL Server at least)

The code

The results