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?

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.

Suntour wrap up

When I woke sunday morning for the final stage of the 2014 suntour I was a little edgy to say the least. Perhaps the biggest day of my cycling career was ahead of me where I had the opportunity to fight for my first tour win as a professional. Just 8 seconds stood between my good mate Simon Clarke and the race lead with the emerging super talent in australian cycling jack Haig a mere 2 seconds adrift of me. All was set for mouth watering showdown on the queens stage of the oldest stage race in the country where we would fittingly finish atop the mythical climb of arther's seat on melbourne's mornington peninsula. The three of us all believed we had the firepower to win and also knew that we all stood a good chance of being beaten. It was a special feeling knowing I had the chance to finally be apart of such and enthralling battle where at days end there was only going to be one winner. I simply wanted the flag to drop and get stuck into it. I was as excited as the spectators to know what the final outcome was going to be. Alas just 30minutes before the start the stage was cancelled due to safety concerns surrounding potential bush fires in the area. At first when Nathan Hass told me the news I thought he was joking but quickly leant it was a fact and that was that. I am pleased the organisers made a definitive decision and stood behind it as it was without a doubt the only decision to make. At the end of the day it is just a bike race and to threaten the safety of hundreds, if not thousands of people when you consider the spectators, the simple and only decision was to cancell the final stage of the race.

So instead of doing battle atop arthers seat it merely became the site for the presentations. Sure it was an anti climax to such an exciting day of racing but I did not dwell for one second on what might have been and simply set about enjoying the podium ceremony with all the guys. I was awarded the most aggressive rider for the 2014 race which was a huge honour for me as I hold a special spot for this prize category. I love nothing more than going on the attack in bike races so to be recognised for this with the award always spurs me on to do it again and again and also is nice to feel some appreciation from the race for my contribution to spicing things up a bit! The other great thing about the award was it was sponsored by Subaru whom I am an ambassador for so was nice to hopefully show them why they made a good choice investing a little in me! Following individual trip to the stage I had the honour of getting up there for the GC podium with jack and simon. The spraying of the champagne is always a ritual for such a presentation and my good mate simon took great delight in blasting the bottle of bubble square into my eyes leaving me blinded for a few minutes, he well and truly got me a beauty there much to his amusement! There were definitely no sour grapes standing up on stage, I was very happy for simon and know he was happy for me. Knowing that your rivals respect you as much as you respect them is something I value very highly and this is definitely something simon, jack and I all shared for each other. In reality if you had told me before the start of stage 1 that I would finish 2nd on GC I would have taken it and run as quickly home as I could, I was very satisfied with my and my Cannondale boys work for the week.   

I was really honoured to be on this podium for reasons other than my personal achievement. The 3 riders I feel represent the beauty of the development and evolution of the sport in the country of the past few years. Atop the podium stood the local hero in simon clarke. 7th in the world championships last year in italy and developing rapidly and methodically from a right hand helper to the likes of simon gerrans and into a leader in his own right. When I missed a crucial split of stage 1 that greenedge created and my team mates so brilliantly shut down simon was furious with me and I was furious with myself. Simon knew had I been in it we probably would have had the chemistry to make it stick and I knew that I had put my boys in an incredibly difficult position to chase it down which I never want to do to my team mates. Simon said to me that night at dinner "don't you dare miss the next opportunity" and I said to myself I would not let my team mates down again. I knew and simon had a good inclination that the two of us would be apart of a decisive move in the race, as history will now show, that's exactly what happened on the stage from ballarat to bendigo. Simon definitely turned up to the race in the best condition of all in the race and was always going to be a factor. In the end I am pleased that the strongest rider won the race. Not only was he the strongest but also the smartest so there was no more deserving winner. Simon has progressed perfectly during his career and at 27 now looks set to really rise to being one of the best in the business. Simon's arrival at this level has come from nothing less than hard hard methodical yakka. By his own admission simon has to work extremely hard to be at his best and is a great example for work ethic and sacrifices required to make it to the top of the sport. Also what a great story for a local victorian lad to win the states most prestigious bike race on roads he rides everyday for training. Its a fairytale that makes young victorian kids riding around on those same roads believe that one day they can become professional cyclists and dream of winning the herald suntour. Without a doubt he is the perfect role model and I have to give a Big big pat on the back to my mate simon.

Jack haig is the emerging talent in the sport in this country. He is being likened in so many ways to cadel and rightly so. Its important that people remember there will never be another Cadel and that Jack is Jack but certainly I saw many reasons why australia should be pretty excited about this young fella in the future. At just 20 years of age his made me look like the grandpa on the podium and rightly so, 10 years his senior is huge so I am happy I atleast kept him one position behind me. In all reality jack was perhaps the best climber for the final stage and could very easily have finished on the top step, again we will never know but regardless 3rd was still a huge result for jack and also one he went out and made happen for himself. I was so impressed with the way he committed to the breakaway surviving with simon and I on the road to bendigo. He saw an opportunity to go tow to toe with two guys I am pretty sure he respects and was not in anyway intimidated or scared to fail and that was rewarded. He simply rode himself into the ground that day until he could do no more than simply try and survive in our slipstream for the final km's into the finish. He really emptied his young tank which simon and I appreciated and respected and consequently had no problems with giving him what could have been seen as a free ride to the finish that day. That's far from the reality of the situation and we were very happy he managed to stayed hitched onto us all the way to the finish line in his home town of bendigo, it was a great story. Jack is the whole package, he showed us in that stage he can climb with the best, is perhaps one of the better decenders in the peleton, and is not afraid to commit on the flat windy roads that many would believe don't favour his tall skinny frame. He definitely has it all there and its exciting to see what he will do in the years to come. By now I am pretty sure I've made it pretty clear that it was real honour for me to be sandwiched between these to gentlemen on the final podium of the 2014 herald suntour.

As for myself, well I feel like deserved to be there aswell. After missing the move on stage one I owed it to my team mates and all the team staff whom have put so much work into me to be in a position to fight for the title. I need to make it very clear that had Mattias, Junior matty mohoric, and george not committed on stage one I would never have even had the chance to fight for the title, simple as that!! They literally saved the tour for me chasing greenedge down on the wind swept plains running into ballarat so that's perhaps the most significant moment in the entire bike race for me. Boivin the next day laid all the foundations in the initial breakaway by driving the pace all day long to ensure we had enough of a gap at the bottom of the decisive climb so I could lay the knockout blow with simon and jack. Its the first time I really believed in myself that I could make something happen that day and it was really special feeling to have boivin who let's not forget could have won that stage aswell, sacrifice all his energy for me to have a crack at success so a huge thanks to him. It was really a special feeling to be able to look all the boys in the eye and know that I had shown them why they did all this work for me. They knew I appreciated them and I knew that they appreciated what I had done with the chance they gave me. That's one of those special moments that you get to enjoy as apart of a team sport. I definitely felt my teams and also my own contribution warranted me being on that final podium, we as a team earnt that. It was our second race together after TDU and was also fantastic to see how much we had progressed as a unit. When all was said and done with the race the results sheets saw us finish 3rd in the teams classification, 3rd in the KOM jersey with bettiol, 2nd on stage 2, 2nd on GC and also the most combative prize for the tour. Was a huge step forward for our young cannondale pro cycling team here in aus and a step forward that I was extremely proud to be apart of. Those fews days racing around melbourne were days that will definitely go down as some of the most enjoyable and significant in my cycling career.

CJW                     

Sent from my BlackBerry® wireless device