Summer of cycling done and dusted

Summer of cycling done and dusted



With the curtains closing on the 2013 tour down under, it also brought the summer of cycling in aus to an end. It turned out to a largely lackluster performance period for me and with adding injuries to insult following the crash on stage 2 at tdu and happy it's behind me and I can recover and look ahead to the rest of the season. Although luck was far from on my side in this past week, it is important to take stock now and see where I went wrong and realistically assess where I am at.



I won't lie, I was aiming very high this summer. Finishing my season at the end of august meant that I could begin my pre season a month earlier and be in great shape for January. Also with moving back to the world tour, you always feel an element of pride racing a this level and I was keen to show that it was where I want to be. I have loved training these past months. Truth be told that since October I have been going to bed every night just so excited that when I wake up the next morning I can go training. I have simply wanted to be exercising for every waking hr of every day. I decided if I was going to adopt this approach then I had to make some restrictions somewhere to ensure I don't burn out. This meant I could ride as long and as hard a terrain as I could find but under no circumstances was m heart rate to exceed 150 beats per min. This is about 70% of my maximum so I knew I could not hurt myself in any negative way at this level, I'm reality I was training at a cyclo tourist intensity!



For the first time since I began my cycling adventure I was simply riding for the sake of riding. Doing roads I would normally explore and also enjoying the opportunity to cruise along with mates that I had neglected to train with so often due to the endless intervals I bury myself in during the season. There were no power meters, simply the heart rate and speed to keep entertained and my AM/FM radio to keep me distracted when required. I was loving this period and felt it had me building up for 2013 in the perfect way. The only glitch in my little plan was to come in December when I was to travel to our training camp in Italy. The camp I knew was not focused on training. It is an important camp for all teams where riders can meet with staff to discuss the upcoming season, photos need to be taken, medical checks completed, and of course the opportunity for sponsors to need riders and discuss products that would be used in battle during the season. All these things are only possible when all riders and staff are together in one place at one time. So attendance was definitely not negotiable, besides in my eyes my personal ambitions are always secondary to the needs of the team, I pride myself on being a team man and that will never change. I was excited about the camp aswell, a great chance to see all my old team mates and meet some new fresh faces. I also saw it as a little challenge to myself, how can I use this to my advantage with regards to preparing for the summer of cycling and 2013 season??



After thinking long and hard I came up with a plan. Pretty simple really. Train as much as I possibly could on the Rd in the weeks leading into my departure and in turn use the first few days in camp while most of the team events were happening as some deserved recovery. After this initial few days I would be back on the Rd in Varese for a week before heading down to Tuscany for the 2 weeks training camp with the team. I knew this training camp from past experience would not involve the hrs or intensity my competitors for the nationals and tdu would be doing so I gave it another good nudge as far as volume goes for that week. From memory I knocked out 40hrs on the Rd which I was very satisfied with considering it was the middle of winter in Italy! I also had the company of my trusty training partner Ivan basso for a large majority of these hrs and while he was just restarting his pre season training he was a great sport in spending more time out on the Rd than he would have probably liked! Signs of a great team mate. He also treated me to a daily stop at the best cafes in the areas we were training to show me how good cappa's and Italian cakes can be! Man talk about mouth watering every time. He never let me down once in the quality of this quick little fuel stops, just another example of what a great team leader he is.



So following a great week in Varese it was back with the team in Tuscan for the riding camp. This was when most of my team mates were just resuming there pre season so was always expected to be a pretty cruisy week for me. It's a big advantage for us Aussies having our of season in the middle of summer. It does not matter how motivated you are you will simply not get the quality of training done in the depth of winter in Europe compared to summer in aus. For the international season this works fine as most of the guys objectives don't come until later in the year but for Aussies gunning for January you need the nice climate to get the quality of work done. Therefore I had a plan in this camp to at least mentally convince myself I was working toward the nationals as planned whilst in the mean time being fully apart of the training sessions with the team out on the Rd. The team organized a room for me to put my bike ergo where I set up my TT bike. The idea was. Ride as normal with the other guys then in the afternoon do the specific TT work I needed to do to prepare for the nationals. This seemed perfect and for the first week it was going perfectly, my legs felt great, power was higher than expected and all looked on track. Then 2nd week all of a sudden we had some juggling of the team schedule and requirements for me to do some promo videos for the team and it's sponsors and this free time disappeared. I was not at all concerned about this as the team stuff I love and that's why we do it December, because can and it does not effect the important objectives of the team for the season. It was however effecting my national pride and at this point I should have just done what I knew I wanted to do deep down and not race the TT. I knew I was not as prepared as I would like and not only that but the travel and events of the past month finally caught up with me when I arrived home in aus and I caught a flu which wiped me out for a few days. Bounced back quick and again was smashing out the intensity on the TT bike on the ergo and I started to think maby i Can pull it off after all. While it all seemed to be back on track at least as far as the ergo work went I completely neglected going out on the rode to test my power outside. Normally if you can do it on the ergo yo can do it on the Rd but more so with the Rd bike than TT bike. This turned out to be my biggest blunder of the whole preparation!



Race day for TT rolled around and at this point I was as confident as could be. I had sharpened up, the ergo numbers were spot on and I felt great, even looked great on my flash new cannondale TT bike!! (it is actually a hand me down as the team have given me Vincenso nibali's bike from 2012 as m home TT bike and I will have new one in Europe to race on) the history of this bike also meant I was super excited to be aboard a vessel with such a story and could not wait to turn the pedals in anger!



The warmup came and went and again I felt sensational. The numbers where spot on, I felt better than expected and was finally on the start line. I had the perfect starting position and behind jack bobridge and with Porte, Dennis, and the eventual winner durbridge chasing me. I was right in the thick of the action. About 5km down the Rd I realized something was wrong. I could barely feel my legs and simply did not feel the power and connection I had felt on the trainer. What i had not done was test my position on the rd and make sure the power was there in a range of conditions. It was a windy day and maby just a slight movement on the seat blocked me up and I could not get an air in, not good signs so early on! i dont know what it was specifically but i do know it is the weakest I have ever felt in a bike race. After only 10km of the 40km race all Could was role my legs around, I simply had no forse in them what so ever. It was demoralizing for me and I just wanted to pull over and cry, infact I almos did cry at one point in felt such embarrassment out there. I did not quick and can proudl sa despite the negative thoughts I fought through as best I could giving whatever I had but at the end of the day it was a demoralizing experience. I was smashed and did not have to look to far to see the reason thanks to the power meter on my handle bars. A pitterfull 370 watts was all I could manage on this day for the 50min, In the past I have done 415 for this period of time and with the training I had done I expected to be well north of that on this occasion. Alas I was not, I was way below it but atleast I had one explanation why I went so slow. On the flip side had I done 420 watts and been beaten by almost 4 minutes I would have really had to scratch my head!



Anyways what it has taught me is that at this level you don't just going a race hoping or assuming all will be ok. In the past I have prided myself on only going for it when I know I am ready. This time I convinced myself I was without all the correct evidence to back it up. On the flip side I had definitely done enough work to put in a strong performance. Sure I would have still have probably been dusted by the fastest guys but I would have been up there none the less. Anyway I know now that unless I am certain of doing best 100% I want be racing the nationals TT. I will leave December and January as a relaxed period if I am unable to commit to it like the aussies who spend the whole summer at home are able to do.



Next stop was the Rd race and with the disappointment of the TT behind me I had returned to tas for a few days and smashed myself in the surrounding hills. Infect the day after the TT I put in probably my best ever training session on m home rds. I knocked out over 3000m of climbing over 160km in 5 hrs. I felt awesome and needed this t remind myself those months of building a base had not eluded me. I was still on track for a strong season in Europe. I attended a wedding on the Saturday evening in Hobart with my partner jess, a commitment we had made long before the decision to partake i the rd race was made, before flying out and did not arrive in ballarat until midnight such was my relaxed approach to the event. A few hrs sleep and again. Was in the startle but this time relaxed and without pressure and knowing if was to do anything I would be late in the race when m endurance would kick in and the intensity was replaced by fatigue in the peleton. I hung in there all day and no surprise got stronger as the day went on and had no dramas following the late attacks and comfortably finished in the main field, 11th infect which was my best ever finish the nationals Rd race, that trailed the locomotive Luke durbridge who took out Rd race title to go along with his TT title a few days before. The funny thing was it was not until perhaps the last hundred meters or so I realized we were sprinting for 2nd. I had been largly uninterested in the race all day and was merely there to remind myself that my base fitness was good and get some valuable racing km's before the tour down under started the following week which was a major objective for me. Anyway I still notched up my best nationals performance and showed myself and team I was ready for tdu so it was job done but in retrospect I should have tested out my sprinting legs!



So while after the TT it seemed months of work was down the toilet but in reality I had to merely appreciate where I was truly at. The problem occurred when I tried to sugar coat things for the TT and I was in turn smashed back into my rightfull place at the bottom of the results sheet. I and not done the intensity required or time on the Rd as I had purposely focused solely on endurance. Testing at the team camp had shown I was the strongest I had ever been in this area so that part of my plan had gone perfectly. It should have been a warning sign to do one thing and do it properly! It was also backed up by m ride in the Rd race even considering it was m first Rd race of any kind for over 4 months so all really from that perspective was really satisfying. The thing about racing a push bike is you are so often, well I am at least, pushed beyond what you want to do, you are more often than not following a tempo that someone sets which is invariably slightl harder than you want to be riding at. Therefore race condition is crucial as in training you dont get that, more so if you train alone which I always do. Anyways the good news was all was not lost, just a little kick in the pants and a lesson in the scrapbook to learn from for next time. In the future I will be following a realistic plan considering the circumstances!



Cjw









Sent from my iPad

Tdu stage 5

A special treat for Aussie cycling fans today with arguably the nations premier race finishing atop the famed old willunga hill faking on Australia day. The day was duly won by a perfectly executed attack from Simon gerrans which made it a very Aussie victory. Aussie boy, aussie team, and great Australian victory. The crowd was incredible as it always is on this stage every year at the tdu turning the willunga climb into Australian cyclings very own collasium!



I had high hopes for today but these quickly evaporated early into the stage. The pain in my ribs was just unbearable today and I could not handle the pace. Despite the supportive work of all my team mates to keep me fresh for the climb. I was unable to handle the sharp pain when the race hit full speed on the climb and simply to do no more than sit up to avoid risking any further more serious damage. Not being able to take a deep breath or accelerate out of the seat unfortunately makes racing at the world tour level very very difficult! Anyways it has been a good exercise in dealing with such an injury and while. Hope I don't ever have to endure it in a race again I will be even better prepared should it occur in the future. It simply has not been my week here in Adelaide so I am looking forward to the final stage then getting home and getting stuck into some solid training and ensure that I am front and centre prepared next time I hit the starting grid in Europe. As a team it has also been a challenging week with all bar 1 rider crashing and nursing nasty bumps and bruises. Most importantly as a group we have not allowed it to get us down and kept a positive outlook on each day knowing that the tide will turn for the bright green jersey very very soon.



Cjw





Sent from my iPad

Tdu stage 4

Stage 4 of tdu was unfortunately another dangerous day. The stage perfectly suited to the sprinters was heavily controlled by the major teams which made it very tense in the peleton. As usual the cannondale boys rode as one in the group to protect each other but unfortunately jurai sagen got caught in a crash in the final km's but was not seriously hurt. The crashes were a deciding factor in the stage results as many sprinters and there trains came down while fighting for position. It seems the easier the stage and fresher the riders arrive at the finish the more dangerous the race becomes. All of a sudden under these circumstances every rider thinks they are a sprinter and have a shot at winning. I looked after myself and tried to give my painful ribs a rest before tomorrow's queen stage which finishes atop willing hill. So now for an Earl night as these battered and bruised ribs have not allowed me much sleep in these past few days!



Cjw



Sent from my iPad

Tdu stage 3

Hi paolo







Stage 4 was like riding in an oven all the day. The team rode the cannondale way together in the group and ensured we maintained good position and hydration all stage. As a result of this we were very present in all dangerous moves in the finale of the race. 1st Brian vanbourg put in a very dangerous attack which formed a group of the strongest riders with 20km to go. Brian was only caught inside 5km of the arrival. Thanks to the work of my team mates in the group I was kept fresh to try my hand in the arrival. I attacked hard with 2km and instantly gained a significant gap. Unfortunately following my fall yesterday and due to the major pain in my ribs I was unable to take a deep breath without excruciating pain and instantly exploded just 400m from the line.



We did gain the victory we had been chasing but we did play our cards with exciting and race shaping attacks in the serious end of the race. The boys continue to gain strength day by day which is a good sign for cannondale pro cycling team at tdu.



Ciao



Cameron



Sent from my iPad



On Jan 25, 2013, at 0:59, Paolo Barbieri <pbarbieri@brixiasport.com> wrote:



> Ciao Cameron, have you got a comment for me about last stage?

>

> Thanks

> Ciao

> Paolo

Stage 1 TDU

Stage 1 TDU



Stage 1 of tour down under is in the books for 2013 and with it the 1st official world tour race day of the cannondale pro cycling team. Without a specific sprinter we had planned for a reasonably quiet day with the main objective to avoid trouble and not lose any time ahead of the GC battle later in the week. All seemed to be going perfect. The course was a real real ripper! It had enough twists and turns and ups and downs in it to make you sea sick!! This would normally make for a stress full day but not when your riding with the cannondale boys. As usual the squad stuck together like glue in the peleton ensuring a safe and relaxed as possible ride in the bunch. With all seeming to go to plan I was happily rolling along close to the front of the bunch when out of nowhere my front wheel was clipped and while I stayed upright half of my spokes where ripped out. This happened with about 8km to go and while going downhill at about 80kmph. This presented a few little problems for me. 1st I needed to change my wheel, but first I had to get to the back of the peleton to do it. With the wheel starting to collapse it became a tricky situation of slowing down, falling out the back of the bunch, ensuring I don't bring any riders down as they pass me, and trying to do all this before my wheel completely collapses.



This all worked out fortunately and I pulled it up and assumed the neutral service car would pull up along side me and I would be underway in no time. This did not happen which meant waiting on the side of the Rd for the team car which was last inline and in turn the race was disappearing up the Rd. I tried not to panic and got underway and felt confident I. The knowledge that I had teammates waiting for me to bring me back. Alan marrangoni and then Brian vanborg rode like motor bikes to get me back on but the all seemed against us. The group kept splitting on the rolling rds and eventually a large group was dropped out of the bunch and the cars were blocked with a few kms to go. At this point the race is going full gas a the front so it was game over for me and consequently I lost a bucket load of time and failed to achieve the only objective I had for the day which was not to lose time. I certainly think the circumstances worked against me but also I was perhaps a little anxious and over zealous in my initial desperation to rejoin the race which burnt me up a little quicker than I would have liked. Having said that if I had been patient the group would have been split by the time got back through the cars so the result would most likely have been the same. It is actually the 1st I have had a mechanical in such a situation and as a worker normally it does not matter as gc is not my role. At this years tdu GC was my role so it really stings and hurts when this sort of misfortune hits but no use crying over spilt milk!!



The good news for the day is that our other climbers all stayed up front and as a result will carry the flag for GC. Stefano agostini, our little pocket rocket showed that 8 months without racing is no drama for him. He blew out the cobwebs today with a very promising 19th place which is an ominous sign for the upcoming stages which will surely be better suite to his powerful little frame. I feel terrible that 2 riders sacrificed themselves for me and in the end I ruined there day aswell as my own. I am sure I will get a chance to help them out again during the season. I am sure that as the race goes on our boys will get strong and stinger by the day so I am now looking forward to helping them out in anyway I can. As the stage went on I could see all the guys looking stronger and stronger and have no doubt that will continue thoughout the week so despite me letting the team down all is still on track for a positive start to the 2013 season for cannondale pro cycling.



The great thing about the 1st Race day of the season is that there are plenty more to come!!! So time to through caution to the wind and Onwards and upwards!



Cjw





Sent from my iPad

Flight Attendants

Any regular reader of this column knows that I hate to fly.  I’ll enjoy an overnight Amtrak sleeper ride to Chicago over a 3-hour flight, anyday (or night).

But when I do have to fly, I really like seeing grey hair in the cockpit.  The older and more experienced the pilots, the better (despite this crazy FAA rule that pilots must retire at age 65, regardless of their health).

Southwest Airlines

The other place I like to see experienced aviators is in the cabin, i.e. the flight attendants.  No longer are “stewardesses” seen as sex objects to entertain business men (remember “I’m Cathy… Fly Me!”?) or mocked in songs as nothing more than “waitresses in the sky”. 

In my mind, flight attendants are under-paid, over-worked and unappreciated safety experts.

A few years ago I was in an emergency landing of a Boeing 767.  The flight crew performed perfectly and nobody was hurt.  Since then I listen to every safety briefing and study all my options for egress.

But increasingly, flight attendants (and passengers) are being called upon to protect a flight from other passengers… like the drunk maniac on a recent Icelandair flight who had to be subdued, or the JetBlue pilot who went psychotic and had to be wrestled to the floor.  “Coffee, tea or duct tape, sir?” 

Flight attendants earn an average of $35,000 - $40,000 per year.  New hires start at $14,000 and, as a sign of our desperate times, there’s no shortage of applicants.  When American Airlines announced last November they’d be hiring 1,500 new flight attendants they got 20,000 app’s. (Question:  do those jobs automatically come with Food Stamps, or do they have to apply separately?)

Unlike most jobs, flight attendants are paid not when they show up to work, nor when they board a plane, but only when that plane departs from the gate.  The time clock ticks 65 – 90 hours a month, but there is no pay for the average 50 hours a month spent prepping flights, doing paperwork and waiting at airports.

Sure, there’s the glamour of free travel and over-nighting at airport hotels.  But as anyone who travels for work will tell you, that glamour wears thin when you’re constantly on the road.  And that’s when you’re on an expense account.  Flight attendants do this for a living and make about $10 an hour.

Is it any wonder that, being human, flight attendants occasionally snap?  Like the JetBlue male “stew”, Steven Slater, who cursed out his cranky passengers, popped the emergency exit and slid to the ground at JFK.

As I’ve written before, flying is no fun.  It is crowded, expensive, unglamorous and tedious.  And if it feels that way for the occasional passenger like me, God bless the poor flight attendants who have to do it for a living.

2013 is finally underway!!!

Today the cannondale pro cycling hit the Tarmac for it's 1st race ever as a team. It's is an extra special moment for me as it marked my return to the team I have dreamed since beginning my cycling adventure to be apart of. Despite my year away I have seemed to slot straight back into to the fold and today was a great reminder of what a special team cannondale pro cycling really is. I still have to pinch myself everytime i pull on the cannondale jersey to remind myself that being part of the team is more than just a dream. The name is new but the staff and riders that made liquigas and the bright green jersey so famous still remain and have ensured the family atmosphere has continued.



The city critirium raced tonight is not officially part of the tour down under, but serves as a great warmup for the European riders to blowout some cobwebs and that lingering jet lag following there journey from the euro winter to our scorching summer. Without a designated sprinter the objective for myself and my 6 teammates was simply to role around in the bunch and give the legs the opportunity to feel some of that all important race rhythm.



Not surprisingly the culture so instilled in the team was immediately evident with us all moving as one though out the peleton. This is somthing that comes naturally for the team and with the all important tour down under beginning on Tuesday, this team work was an important box to tick to set us up for the challenge that lays ahead next week in the Adelaide hills. It's a special feeling for me moving in the peleton with this team. Not only is it easier to move as a group compared to alone but for me it also makes me feel save. That feeling that you can trust your team mate in front of you to guide you where you need to be and knowing that there will also be a team mate behind placing equal trust in you and ensuring your safety aswell.



The race itself went to it's usual script. A small breakaway which the peleton let dangle of the front for 3/4 of the race before slowing cranking up the pace to warp speed giving the fast men a chance to flex there muscles in a bunch gallop. The big German bull Andre grieple duly won the stage he was odds on favorite to win and the rest of us were treated to 1hr of speed and agility training while his lotto team and all the other sprinting teams did all the hard yards cranking out the pace on the front. The speed was pretty impressive tonight and consequently gave me a great opportunity to test out my cannondale evo at race tempo in and out of corners and through the bunch. Having sent plenty of time on the training track I was aware of how responsive and agile the bike was under those conditions but to feel how comfortably it moves you around the peleton at speed was something else! The evo is as comfortable as sitting in a lounge chair and I quickly realized that I was saving a huge amount of energy compared to normal simply by being aboard such a well designed bicycle. In and out of corners, in and out of the saddle, on the brakes , accelerating, you name it this bike seems to just move as one with you. So I must take this opportunity to compliment the cannondale tech gurus for building such a magnificent office for us riders.



So the season is now underway. The atmosphere within the cannondale camp is one of excitement and great anticipation for what the season will bring. The name has changed but the soul well and truly remains. This team is a family and we all can't wait to get our teeth suck into the 2013 season.



Cjw





Sent from my iPad

An awesome 40

Sadly, not my age

Oh if that were still true (funny how being 40 wasn’t particularly thrilling at the time) but alas and alack, it is not.  OTOH, 40mm can be awesome, when it is in the form of a Bofors.

Why ODTUG is awesome

What on earth does a WW II era anti-aircraft gun by way of Sweden and then Chrysler (just like Merlins were by way of Rolls-Royce and Packard) have to do with Essbase?  Well, as you can see in the snap below, I am manning a Bofors quad mount on the USS North Carolina during one of the two annual ODTUG board face-to-face meetings.  You may think I just splashed a Zero whist defending my ship given my facial expression.  But no, that is not a Victory Flag, but instead an inducement to come to Kscope13, and yes, you are just looking at a geek at play.  And fun is a vital component of ODTUG.  Don’t think so?  Then you haven’t been to a Kscope, because it is fun, and exhausting, and extremely educational.  What more could anyone want in a technical conference?

Is there any point to this?  Why yes there is.

I would love to tell you that ODTUG board meetings (we have monthly telephone calls, too, oh the joy) mostly consist of climbing into, around, and over products of the Washington Naval Treaty with an eye to fully engaging my inner history geek, but alas and alack yet again, my chance to man a crew-served weapon doesn’t actually crop up very often in the course of ODTUG activities.  Nope, instead we examine, discuss, debate, and decide:  how ODTUG is doing (we have to cover costs and yes there is a Treasurer’s Report every month), what ODTUG is doing (are we meeting the needs of our members), and where ODTUG is going (we have today covered, we think, but what about the future).  It is serious stuff and it is how the board serves you, the ODTUG member.  

You would, I think, be astonished at the amount of work (and oh the time) it takes to make Kscope and the SP conferences and the webinars and the website and all of the other initiatives ODTUG performs actually come off.  And that isn’t to slight our volunteers, who are legion (I have been waiting to use that phrase forever – it could only be improved by replacing “volunteers” with “minions” but we are not Super Villains but instead a wholly benign user group) and contribute materially to ODTUG’s success.

All of the above is my tortured way of saying that ODTUG and ODTUG Kscope13 are the outcome of a lot of blood, and sweat, and tears and yes, a bit of fun.  That’s why Kscopes are, in my not entirely unbiased opinion, the best conference anyone in the EPM world can attend.  There’s nothing to touch it.  

Be seeing you in New Orleans.

The fastest way to export targeted data from BSO Essbase with NONEMPTYBLOCK

NB – The best way to experience this blog is with one of the following musical soundtracks open and playing in a loop on another tab or window.  Yes, Big Black’s RacerX gets the idea across just right.  And yes, it may be hard to believe for those of you who have met me that I was ever a fan of punk, but yup, that was my youthful musical rebellion.  For those of you who are not manly (womanly?  whatever) enough to take that can listen to the original Speed Racer theme as it ought to be heard.  All too much for you?  How about the version that most matches my current musical tastes?  Here’s a nice meld of a Quncy Jones bossa nova arrangement of “Desafinado” and of course the appropriate speed frame of mind.

Okay, with that bit of not-quite-totally irrelevant trivia out of the way, but with you most definitely in the mood for speed, speed, speed (and, if you are listening to Big Black, the desire to throw yourself into a mosh pit, yeah, this blog caters to all tastes) read on for some pretty darn exciting news about extracting data out of BSO Essbase.

The obligatory primer

When it comes to getting data out of BSO Essbase, there are a couple of ways to extract data.

Export it

From the days of long ago Esscmd’s EXPORT (believe it or not, I saw a new system last year that 100% relied on Esscmd.  Look, I thought it was a nice succinct language, but it is dead, dead, dead.  As you might imagine, the rest of the system was pants.), to MaxL’s export data command, to EAS’ export data functionality.  If you need to get ALL of the data out of the database for backup purposes, go crazy with parallel exports and have a good time.  And note, if you are working with a 92 .PAG file database as I am currently, that is NOT going to be a fast process.

Focused exports

But what happens when you need to only export a portion of a database?  The above approaches aren’t going to do the trick because they are all or nothing.  

Happily Essbase provides many ways to extract data:  report scripts, the DATAEXPORT calc script command, and, for those of us who use BSO on a regular basis, the slightly exotic looking MDX queries.  Let’s examine each in turn and yes, I have a (and I think once you see it you will agree this is sans hyperbole) technique whose performance will leave you absolutely gobsmacked – it did for me and I am not all that easily overawed.  

But just as with dinner, first the meat and veg, and then the oh-so-tasty dessert.  I am not doing this out of sheer bloody-mindedness but because I think you need to see all of the options.  And of course malva pudding tastes best after the Bobotie (mmmmm, South African food is lekker).  Enough of my culinary analogy – the other thing this review does is show how this new technique spanks every other approach.  It is a Most Awesome Hack.

Essbase report scripts

So what if we stepped back to year zero and tried this the old fashioned way with Essbase report scripts.  They have been around, literally, from the beginning of Essbase and are known to most.  

What does a report script look like that exports level zero Product and Postcode in the Forecast Scenario, Working Version, in Jan FY12 for the Account AT?  Oh, something like this:

We likely want to run this in batch (I dunno, do you like staring at a screen as you wait and wait for a process to finish?  Me neither.) via MaxL:
export database db.dbname using server report_file "Test1" to data_file "c:\\tempdir\\Test1.txt" ;

How long does it take?  1269.72 seconds for 21,788 records (one for the header).  The output looks like this (I could have suppressed the headers with the SUPHEADING keyword but chose not to):

BSO calc script DATAEXPORT

DATAEXPORT is the darling of many because it can be invoked within the context of a calc script and because it can produce nicely formatted (well, cleanly formatted) exports and even write to SQL.  

Which produces something like this (which does look a bit a ugly in WordPad):

Why oh why did I open it in WordPad?  Because DATAEXPORT only writes a Line Feed (LF) at the end of the record, not CRLF as Windows requires.  Poor old Notepad can’t handle that so you get this:

How long did all of that take?  Why a mere 1070.79 seconds for a 21,787 record export.  That’s just about 16% faster than a report script, so if speed is the purpose, then DATAEXPORT is the way to go.

So what about MDX?

Well, this wouldn’t be where I would normally turn for data extraction, mostly because MDX is so ASO-ish and I’m not writing formulas in an ASO Essbase database.  But in fact MDX has a query language with ROWs, COLUMNs, PAGEs, and as many AXISes as one can shake a stick at (actually the max is 64).  

I really got inspired to try out MDX as part of a Really Special Project (i.e., a project that I am doing for “fun” and getting $0/hour for and OMG the hours are killing me – I’d be a freaking millionaire if I were doing this for a client.  Write constructive suggestions to me care of this blog on how to conduct oneself professionally with an eye to not going broke.) and because of a recent thread on Network54.

Well, after an amazing amount of pain for really very little output (this is, sadly, my modus operandi when it comes to new-to-me technology) and boost from my buddy Dan Pressman, Mr. ASO and by extension sorta-Mr. MDX query, I came up with the following:

That’s the query with pretty colors.  To make this actually somewhat readable when it gets output, I stuck the query in the following MaxL script:
spool on to "c:\\tempdir\\MDX_Extract_Test.log" ;

login username password on servername ;

alter application ep clear logfile ;

/*    The below settings are right out of Developing Essbase Applications    */
alter session set dml_output alias off ;
alter session set dml_output numerical_display fixed_decimal ;
alter session set dml_output precision 15 ;
set column_width 80 ;
set timestamp on ;

SELECT
    {CrossJoin({[Period].[Jan]}, {[Account].[Allocation Target]})}
ON COLUMNS,
    NON EMPTY (CrossJoin([Product].Levels(0).Members, [Postcode].Levels(0).Members))
ON ROWS
FROM [EP].[ExalPlan]
WHERE ([Version].[Working], [Scenario].[Forecast], [Year].[FY12]) ;

I have to give a plug to Gary Crisci – the settings I use to turn off aliases, set decimals and precision, and the column stamp are all out of Gary’s MDX chapter in Developing Essbase Applications.  As you’ll see, formatting in MDX isn’t all that great – it all goes into MDX_Extract_Test.log along with a bunch of other stuff.  Ick, yuck, eeewwwww.

The good news is that Network54 thread I referenced above has some great suggestions for getting round all of this.  Read it through and you’ll get some ideas.

How long does all of this take?  Ah, that’s where this gets interesting -- 638.926 seconds.  Now that is interesting – almost twice as fast as report scripts and takes just 60% of the time of the DATAEXPORT calc script time.

So what do we have?

Three different ways to write out data, with an increasing performance profile:

• Essbase report scripts
• DATAEXPORT report script
• MDX queries

Looking at MDX versus Essbase report scripts, I really have to say, at least in the database I am using and for the extract I am using, MDX is by far the fastest way to do this.  Awesome, right?  We have a winner.  Or do we?

Undocumented, unrecognized, and unreal

All those times, all of those techniques I wrote about above?  Yes, they’re all going to pull the same data, and yes, I have shown you a way that is twice as fast as the most common approach, at least for this database.  But you know what?  They stink.  I have, thanks to a number of people and, I might add, Oracle, a much better way to do this.  It’s so fast the first, oh, eight or nine times I ran it I thought for sure it was simply an error.  It is no error and it is freaking awesome.  

Did I figure this out myself?  Nope.

Btw, I must tell you that I did not figure this out on my own but was told of the command by my fellow ACE Director Tim Tow.  And he discovered it whilst working with Bryan Bain, one of the original AFSG (Arbor Field Services Group) Essbase consultants, when he was trying to extract the last ounce of performance out of Essbase for his flagship tool, Dodeca.  I should also mention that I tested this out as part of that Very Special Project on one of John Booth’s test servers. I am, as always, standing on the shoulders of giants.

For the love of Mike, what is it?

It is an undocumented MDX BSO-only keyword that is hinted at in this old Network54 thread (which I was part of but, no, I did not hide it from you till this time) and mentioned in the 11.2.2 Essbase readme.  Look for defect 13037253.  And you have seen it in Planning forms.  Planning?  Yes, Planning.  Have you figured it out yet?

It is:  NONEMPTYBLOCK

How does it work?

Just like this:

How long does it take?

The query (the same as the first MDX example save the keyword change) took 1.789 seconds with NONEMPTYBLOCK.  Really.  As I wrote, no, I didn’t believe it either.  Go on, try it yourself.

Are you laughing yet?  This didn’t make my day, or my week, or my month.  I think this is going to make me laugh all the way through 2013.  And considering I am stuck in an airport for hours and hours (thank you, US Airways, for eating all of my Sunday, again) after an ODTUG board meeting (the meeting was fun, the flying (or lack thereof) not so much), I’d say that is pretty strong medicine.

To put this into percent of time relative to an Essbase report script extracting the exact same data, this technique takes 0.14% as much time.  That’s right, not 14% but (I am going to say it out loud) zero-point-one-four percent.  In other words, it’s a freaking rocket.  And of course it’s Oracle that gave us this command and then mysteriously didn’t bother documenting it.  Why?  I have no idea.  It is still freaking neat.

Where does Planning fit in?

Have you ever edited a Planning (I am not going to bring up Planning just to snapshot this – trust me, it’s there and has been for a long time) form and ticked the box that tells Planning to suppress missing blocks?  Did you know that Planning builds its forms with MDX?  If not, you do now.  In any case, when you tick that box, you are using NONEMPTYBLOCK.  Remember, there is a slower (and documented) option to suppress missing in MDX – that is the NON EMPTY keyword I showed in the first MDX query example and that corresponds to Planning’s suppress missing form setting.

Does it always work like this?

With Essbase, the answer is easy:  no.  Or at least, test it, within the context of several constraints.

As I wrote above, this only makes sense within the context of BSO Essbase.  There are no blocks in ASO so this command doesn’t make sense and consequently doesn’t work (yes I tried it, no it doesn’t work).

This command also only makes sense if you are trying to extract lots of sparse at level zero of the database.  Per that Planning documentation link I gave you, if you do not have many missing blocks: “The Suppress missing blocks setting can degrade performance if few or no rows are suppressed.  Test forms before and after using this setting to determine whether performance is improved.”

And of course if you are not suppressing data then this command makes no sense at all.  Having said that, custom exports from Essbase almost always are at level zero and almost always suppress missing data, at least in my experience.  And if that is the case, NONEMPTYBLOCK is your BFF.  Btw, I would go with definition #3 on that link.  :)

Be seeing you and enjoy the hack.