22 February 2014

My ODTUG webinar cup runneth over

You’d think they’d know better, wouldn’t you?


Over the next three weeks I will be participating in two webinars for ODTUG.  Whether this strikes joy, terror, or simply gives you a bad case of avoir le cafard I cannot say.  

I will share with you that actually writing the content for these sessions has been a bit of an exercise in pain but I am now all done and if I do say so myself, what I have is not half bad.

All kidding aside, I am very excited about this opportunity to share some of what I know and the subjects are two that are near and dear to my Oracle EPM heart.

The Most Awesome Planning Calculation Manager Hack the World Has Ever Seen

The most awesome? World-beating? That's mighty big talk for a webinar. Is this presentation hyperbole or fact? It's fact--cold, hard, “Just the facts, ma'am,” fact. This webinar deserves those adjectives because it will demonstrate the incredibly clever Planning Calculation Manager hack that Christian Musci and his team invented. It answers the problem the common Planning Focused Aggregation technique could never resolve--Focused Aggregations based on Planning form row and column selections. Did I mention it is a hack? And that it is awesome because it solves a problem that had no solution? And that the hackiness and the awesomeness combine and become a must-see webinar? Maybe I just did.

Join me as I explain the genesis of my understanding of the Focused Aggregation technique, a step-by-step illustration of how it works, its superiority to generic aggregations, the seemingly insolvable problem of Planning not passing row and column selections to Calculation Manager, and finally the beyond-awesome hack that solves this problem. It is an awesome hack; I may have mentioned that previously.  Maybe.

With this last bit of the Focused Aggregation puzzle, applications that simply couldn't work, or could only work in a crippled manner through administrative aggregations that robbed Planning of its real-time nature, now quite simply do work. Yes, it's that good and it's easy. All will be revealed in a step-by-step process that will allow you to make your "too big" Planning application "just right.”

Yes, I have covered this subject in this blog and (somewhat) at conferences but this is the first time where I present on the theory behind the technique, show comparisons in performance against other code techniques, and then show off a totally awesome hack that makes it all worthwhile all at once.  This is a deep, deep, deep dive into the technique.  If you are not using this technique, or not using the latest version of it, you really should block off the hour to hear about how this works.

The when and the where

25 February 2014, 2 to 3 pm, Eastern

Sign up right here.

Getting (even more) Serious about Data Quality and Governance

A few things I must mention right off:
  1. This is a webinar panel, not a traditional presentation like the above one on Focused Aggregations in Calculation Manager.
  2. I am but 1/3 of the team.  Ron Moore and Joe Caserta are the heavy hitters.  Think of me as the comic relief.

Nothing is more important than data quality. But if the steps to insure high data quality aren’t fast and easy people won’t do them – or at least they won’t do enough of them. It was always a difficult job and it consumes a lot of resources even with traditional data sources such as ERP that are relatively well behaved. Now analytics is spreading to more users and to data that’s far less well behaved. What should we be doing and how can we make it as fast and easy as possible?

In this webinar we will put those questions to our panelists and we will invite your opinions and questions. Some of our topics will include:
  • Is data quality really a problem? Where and how much?
  • Who has responsibility for data quality?
  • What techniques can we apply at the data source?
  • What techniques can we apply within Essbase and Planning?
  • Can we adopt some “simple stupid rules” for DQ?
  • What is the role of documentation?
  • What documentation is effective and worth the effort?

The when and the where

11 March 2014, 3 to 4 pm, Eastern.

Sign up right here.

That’s all for now

The topics are interesting, the content is good, and as always ODTUG provides all of this to you for free, nothing, zero, zilch, etc.  How can’t you win?

Join us, won’t you?

17 February 2014

A modest man with much to be modest about, or Yet Another Stupid Excel Trick

How do I know when I’ve spent too much time in Excel?

When I see this:
 


That’s a Classic Essbase Excel add-in message and proof that I was doing waaaaay too much analysis of a particularly knotty data issue.  In almost 20 years of Essbase work (most of it with the add-in) I have never managed to get that error message.  I took it as a note that it was time for me to stop and take a coffee break.


But that’s not what this blog post is about.  What this blog post is about Yet Another Stupid Excel Trick because of Yet Another Manual Process.


I must give mention of Mabel Van Stone who tried to warn me of this issue.  As usual, I heard it, but didn’t hear it.  Now I am going to write it.  Maybe this time it will actually sink in.

The hell that is linked workbooks

If you read my last blog post on this subject, you’ll know that I am not exactly a terrific fan of linked Excel workbooks because of the potential of completely mucking up the data.


I also am not exactly a terrific fan of:  mean people, stupid people (although on reading this post you may be convinced that I am part of that group), injustice, bad coffee, and a whole host of other things.  Me not liking them matters not a jot as they exist with or without my approval.  What does matter is knowing what defines “bad” and then firmly following one of two paths:  avoidance of things I cannot change (and grudging acceptance therof) and the complete eradication and expiration of the things I can change.  Sometimes run and hide is the answer, other times violence is the answer.  Yes, I am a simple man.  What I am about to illustrate falls into the former category.  


Source and linked target open at the same time

Let’s begin with a simple (hah!) example of linked workbooks, this time with both workbooks open.

Source

Do the products in column A look familiar?  No?  They are Cameron’s detail skus to MVFEDITWW (pronounced mmmfeditwwwuuua) aka Sample.Basic.

Target

And here is that linked target in MVEDITWW’s send workbook:


Note that the total Excel formula value in the send sheet’s row 6 is linked to the target sheet’s row 4.

Updates

A change in the source:


Is reflected in the target immediately as both workbooks are in memory:

Sheet renames

If the source sheet is renamed from “Source sheet” to “Source sheet 1”:


It is reflected immediately in the target workbook formula:


All very slick and goof proof.  Maybe.


Closing the files

That’s great if you have both workbooks open, but it’s often common practice, aka some form of sanity to keep just one workbook open at a single time so what’s being dealt with is obvious.  


To keep an even greater degree of sanity, it is also common to disable the automatic updates of linked workbooks.  Maybe you want the target workbook to be updated, maybe you don’t.  The only way to control this is to:
  1. Go to the Data ribbon
  2. Click on Edit Links
  3. Click on Startup Prompt
  4. Click on Don’t dipslay the alert and don’t update automatic links
 


This then puts you in control of updating or not.  


I like this because I in general like control of my data and also because it makes this example easier to demonstrate.  NB – the functionality is the same if automatically updated so that is not a free ride to getting away from the Excel Stupid Trick.

Closed source workbook

So what happens when data gets changed in the source and then saved and closed?


It’s not updated until it is explicitly updated by you, oh Excel god.


Updating is easy, simply go to Data->Edit Links->Update Values and the new numbers are reflected in the workbook.  Simple and again you are in control.

Renames

What happens if you rename the old sheet and then save and close?


On update, Excel knows that the sheet formerly named “Source sheet” is now called “Source sheet 1”.  It is not tied to the number of worksheets in case you were thinking it went off of the index of sheets in a book.   Somewhere in the depths of Excel, there’s a code associated with the sheet name.  We see it as “Source sheet 1” or whatever; Excel has its own name that allows this kind of renaming whilst retaining the links.

 


Note that before the update, the sheet name is the old “Source sheet”.  After the update it is “Source sheet 1”.

 


This is all pretty awesome, isn’t it?  What oh what oh what could possibly go wrong?

New sheet with the same name

If I create a backup of the old sheet (hey, I may want to go back to the old data) and create a new sheet with new data, what happens?


In the example above, Excel used (I think) some sort of internal sheet code to keep the link to the renamed sheet, ignoring the displayed sheet name of “Source sheet 1”.


I’ve renamed the original sheet with an “_old” suffix and created a new sheet with the same layout but with a the original sheet name.


Excel is smart enough to see this and gives you a choice of sheets – original one with the “_old” suffix or the new sheet with the original “Source sheet 1” name.  Saved from certain disaster I bow down again before the genius of Microsoft.  Or should I?

A modest man with much to be modest about

We have now reached the inspiration for the title of this blog, and alas and alack, there is no false modesty here.  What do I mean by this?  I simply mean that as you can see from the above, Microsoft made linked workbooks, at least from the source sheet perspective, goof proof.  Believe me, I tried very, very, very hard to break Excel in the course of building the samples for this blog post and I just could not make it happen.  


And yet I managed to make a mess of linked workbooks even after being warned that just such an error could occur.  How did I manage to do this?  Read on so my humiliation can be complete.

Screwing up, step by step

1 – Prerequisites

Environment

I have thus far shown you a very simple set of workbooks and sheets.


Imagine a very complicated set of linked workbooks, with the source being the user set of workbooks that define base budget information and the target being a series of linked worksheets that send data to Essbase.  When I say complex, I mean there might be 20 to 70 (yes, you read that correctly) sheets in the source workbook and at least that many in the target.  Within each sheet is a link range (think of it as a mapping of source layout to target layout) and then there is another range of linked (this time only within the sheet) formula cells that are selected and sent to Essbase.  And before you start tsk-tsking, remember that this is not my process – I am just a caretaker and yes I hate it.

A birth and a death

The business created a new entity.  At the same time an old one was closed.  This is important.

2 – Getting the request

On the source side, Mabel (hello, Mabel, and yes we are almost to the “I warned you, but you didn’t listen” bit) reused and renamed an existing source sheet for the new entity.  This makes sense from her perspective as the formatting, formulas, etc. were already defined.  She simply put in a new description, new base numbers, and the existing source worksheet did its magic.  Thanks very much Microsoft.


Over on the target side, because there were so many sheets, because I do not really know the entity structure, because this is a manual process, and mainly because I am a dope, I did not follow Mabel’s lead.  This was A Bad Thing as we shall see.

3 – Adding the sheet

As the layout of all of the sheets are the same I copied an existing target send sheet – note that this was not the dead entity target sheet but another one.  Yes, that was another Bad Idea.


Being the super ultra-clever chap that I am (ahem), I knew that I would have to change the link formulas in the new target sheet and duly did so.


But I also did not delete the old send sheet for the closed entity.  This was not a super ultra-clever act and is really the key to the error.

4 – Doubling the data

So what happened?  As I showed above, Excel keeps an internal name for all of the source worksheets.  A simple sheet rename doesn’t break the links.  This is good, right?  Right?  Wrong.


Not deleting the dead sheet meant that Excel now linked twice to the renamed source sheet, once for the old dead entity and again for the new entity.  As there was supposed to be no new data in the dead entity (remember the Cameron is a dope factor) I then managed to double the data in Essbase.  Oops.


And this is, btw, precisely what Mabel warned me of.  

Ameliorating the problem in the future

Putting aside the issue of me as a user of anything but the most basic of Excel workbooks, how can something like this be avoided?

Excel is not the answer

Excel has auditing features, but it does not (at least as far as I can see) have a simple way to show what the linked worksheets are except by examining each sheet (not terribly useful when there are 70 sheets in play) or by writing a custom VBA macro to try to report the information.  Both of these are difficult-ish and time consuming.  There has to be a better way.

Power Utility Pak v7

And there is in the form of an Excel utility called Power Utility Pak (PUP).  I have to thank Dave Farnsworth (who has “swallowed the anchor” but continues to teach me) yet again for telling me about this product years ago.  If only I had it installed on my client machine…


The famous John Walkenbach wrote this utility and it is, in a word, awesome.  


Without going into all of the many features of this tool, and there are many, I will focus on the bit that gives you an audit report of linked worksheets.



The Workbook Link Finder is exactly what I should have used.


PUP even gives you a way to display the links:


Given a target workbook with a copied target sheet (just what I did in real life), the following report comes out of PUP:


Looking at the above report, I can easily see that Target sheet and Target sheet (2) both link to the same source sheet.  In my example, this is precisely what I did not want to happen, but did.

Check it out for free

J-Walk has a free trial download available here.  I encourage you to try it out.  I am going to buy another copy and install it (if I am allowed) on my client laptop.

What have we learnt?

I think this can be summarized into a few key points:
  1. Cameron the Essbase hacker is not Cameron the Excel hacker.
  2. Yet Another Manual Process (YAMP) equals Yet Another Chance For Failure which begets Yet Another Stupid Excel Trick
  3. Tools like PuP can make life a little less painful
  4. Linked workbooks are evil
Okay, the last bit is just my opinion, but my goodness a simple Excel error led to a big Essbase data problem.


Be seeing you.

09 February 2014

Two fantastic regional user conferences

There is more to BI/EPM conference life than Kscope.  I know, hard to believe, but it’s true, I promise you.  Don’t believe me?  Read on, Gentle Reader, and all will be revealed.

Sometimes a chance remark makes things happen

Over on the Network54 Essbase page, there was a thread about regional user conferences in the days of Hyperion and how there really isn’t anything today to take their place.  Never one to pass up stealing other people’s ideas miss an inspiring thought, I took this comment from the mysterious “RIM” to ODTUG, ODTUG did their usual hard work, and the result of their effort is this regional Chicago ramp-up to Kscope14.

The speakers below are all Kscope veterans – think of this as a preview to Kscope and all the more reason why you should be in Seattle as well.

When, where, what, and how to sign up

When

21 February 2014, 12:45 to 5 pm

Where

Oracle’s regional Chicago office, 233 South Wacker Drive, 45th floor, rooms 45014/45015/45016

What

  • Shared Services API – Oracle ACE Celvin Kattookaran
  • Exalytics Performance – Oracle ACE John Booth and Tim German
  • BI Solutions with Oracle Analytics – Dave Collins
  • Financial Close Management – Jim Springer

These are fantastic speakers and content and the cost is absolutely zero, zilch, nada, you get the idea – FREE.  It is rather difficult to argue with that price.

How

If you are interested, and I cannot imagine how any Chicago area BI/EPM practitioner would not be, sign up here.  

Thanks to all of the speakers for taking time to do this, ODTUG for organizing it, and of course Oracle for providing the meeting venue.

I wish, wish, wish I could be at the meeting but alas I cannot.  It will be that good and it is a regional user conference not to be missed.

What’s Round on the Ends and High in the Middle?

O-hi-o.  

Sorry, that joke has been bouncing around my head since I saw the film Third Finger, Left Hand and heard Melvyn Douglas sing that particular ditty.  Lest you roll your eyes at yet another old movie reference, take a good look at Myrna Loy.  Phwoar.  It's also a pretty funny movie.

Why the reference to the Buckeye State?  That’s where the Northeast Ohio Oracle Users Group (NEOOUG) is holding their annual Great Lakes Oracle Conference (GLOC).  If you are an Oracle practitioner in the Midwest, this conference is right up your alley, as is the user group itself.

When, where, what, and how to sign up

When

The Great Lakes Oracle User Conference is 13 to 14 May, 2014.  The pre-conference seminars (of which more anon) are 12 May.

Where

Student Center 3rd floor ballroom foyer at Cleveland State University

See
here for hotel and more detailed information.

What

GLOC is not primarily a BI/EPM user conference.  Yes, for Essbase hackers aka the Best and Brightest aka whoever reads this blog, it is sometimes difficult to realize how small a part of Oracle’s universe we are but it is The Awful Truth.  Don’t view that as a bad thing, but instead see it as an opportunity to broaden your horizons.  You may have noted that this blog actually covers a whole bunch of technologies, Essbase included.  I do that not because I am sort of wonderful renaissance man, but because that is simply what it takes to succeed in IT.  I expect you do the same.

With that broad technology interest in mind, take a look at what NEOOUG have on offer.

Keynote speakers

GLOC has not one but two keynote speakers:
  • Tom Kyte – Sharpening your Memory: A look at the Oracle in Memory Database
  • Steven Feuerstein – Coding Therapy for Software Developers aka How Does This Code Make You Feel?

I saw Tom present at NZOUG 2013 and he was not half bad.  :)  All kidding aside, Tom is a bit legendary as is Steve.  Both sessions will be beyond interesting.

Session Tracks

As I wrote, this conference (just like Kscope) is all things to all men, or at least tries very hard to speak to much of the world that defines Oracle:
  • Application Development
  • DBA
  • Data Warehousing/BI (see, I lied, there is BI/EPM content here)
  • System Architecture/Administration

That’s 30 sessions in total over two days and these look like really geeky sessions based on their titles.  Click here to Read The Whole Thing.

Still accepting abstracts

Despite the above agenda, the deadline for abstracts is still open, and will be till 16 February 2014.  

Workshops

GLOC kicks off with a full day of preconference workshops.

So far, the workshops consist of:
  • Oracle Performance Tuning 101 – Carlos Sierra and Mauro Pagano
  • From Relational to Hadoop – Migrating Your Data Pipeline – Alex Gorbachev
  • APEX Hands-On Crash Course – Scott Spendolini
Speaking of which…
GLOC’s workshops will also have (assuming of course that NEOOUG accepts my summary and abstract, so maybe yes, maybe no) a three hour Introduction to Essbase seminar given by yr. obdnt. srvnt. to be given on 12 May 2014.

You say that you don’t see the workshop on the link (at least at the time of the writing of this post)?  That is because I have (ahem) yet to submit the summary and abstract.  All I can plead is that I am a tad over committed.  Sometimes I even get to sleep – really, if I could just remove that trifle of biological function, I’d be set from a do-all-the-cool-stuff-I-do-outside-of-work.  Thomas Edison claimed that sleep was a waste of time.   A man can dream.  Or not.

Putting aside unrealistic dreams (ahem yet again), I did just suffer a two day power outage complete with downed power lines trapping me on my street.  I viewed this as a gift as it allowed me time away from an awful lot of distractions (you know, like paying work) and I have now written, on paper (hey, no power = no typing it up) a seven page outline of what I want to cover.  Ambitious?  Yes, but I think it will be a killer intro to Essbase.   
Here’s the summary
To relational people, Essbase is a strange creature.  It’s not a relational database, but it’s owned by Oracle.  Essbase can speak SQL, but you cannot run a SQL query against it.  Financial users love it, IT departments are puzzled by it.  What is Essbase and why should you care?  This three hour seminar will give you a firm grounding on what Essbase is, how it works, and what it’s like to develop in Essbase.

I've got fingers crossed on the abstract getting accepted.

How

Signing up couldn’t be easier.  Simply click right here and sign up.  You’ll (hopefully) soon see my workshop there.  I’ll be submitting the abstract just after this blog post goes up, I promise you.

Wrapping it up

So yes, there is quite a bit more to user group life beyond Kscope.

The first regional Kickoff to Kscope user group meeting (may it be one of many that ODTUG will put on in future) is in Chicago and is focused on BI/EPM.  If you are in the area, it is a do-not-miss event.  It’s coming up soon, 21 February 2104, and you can sign up right here.

The second, rather larger, Great Lakes Oracle Conference is also chock full of awesomeness.  Maybe it will even include me and my ever so slightly awkward love of all things Essbase.  Don’t forget that it isn’t too late for you to submit your own abstract for a session and have the conference fee waived.

Be seeing you.