Ritchie's birthday party

Yesterday was Ritchie Porte's birthday. I was very honoured that he invited me to his birthday party! I wasn't actually aware it was ritchie's birthday when I met him at aroma's cafe in launceston at 6am, the only thing I was aware off was that ritchie and I were going to be spending a long time on our bikes! We had plotted this ride last week at TDU, we both love a long days training so decided a 300+km day needed to be done. On monday I received a message from ritchie upping it to 320km and thursday was locked in as the day for the adventure. I decided I would also ride up to launceston so did so on thursday afternoon, just a lazy 194km to get the system going after TDU. During the ride up I started receiving messages from ritchie with the distance of the proposed route increasing with every vibrate of my blackberry. First 330km, then 350, the finally as I passed cambelltown 365km was sent through. I could tell by the tone of ritchie's messages that he was getting excited and I simply said you choose the course I will ride it, Its your home turf, you get to make the rules! Upon receiving the message simply saying "let's make it a classic 365km" I decided to pull into the next shop I saw and start loading up on calories! I was going to be needing them the next day and promptly downed a massive mountain dew and muslie slice, I was now also going to taking the final 30km of my ride into launceston quite a lot more tranquillo!! Needed to save the pins!!

1st 100km:

We rolled out of launceston around 6:30 and headed for the quiet backroads to longford. The route ritchie choose was about as perfect a route you could choose when attempting a 360km adventure. Launceston-Longford-Epping Forest-St Mary's-St Helens-Scottsdale-Launceston. Simply put a circumnavigation of north eastern Tasmania. St Helens was exactly half way and came at a mentally perfect point where you reached the coast. Basically then you head back inland to launceston via a more picture esk route than that of which got you to the coast. Along with the more interesting terrain came many more hills however after the relatively boring flat initial 180km the change in terrain was something to look forward to.

Ritchie I had not trained together since march last year when I visited him in monaco. We speak a lot but you always chat about different things when out on the quiet country roads together. As a result of it being such a long time between training drinks I was pretty confident we would have plenty to chat about. Besides we had decided to to stop talking to each other at TDU as we knew we would have plenty of time to catch up on our big day out! As it turned out I can't remember a period of more than 30seconds when we were not chatting about something. Sure I could talk under water with a mouthful of marbles so I probably spoke or kept the conversation going a little more than ritchie but I am pretty sure it was more often than not a 2 way conversation.

Just the otherside of Longford richie broke it to me that it was his birthday. I was immediately pretty touched he choose to spend every daylight hour of his birthday on his bike with me. I said mate we don't have to do it today and ritchie said "no, this is absolutely perfect, being out here on my bike the entire day is the best present ever". Probably no surprize he is one of the best riders in the world with that attitude when the ultimate gift is a free day of riding as far and hard and wherever he wished to ride! That's real passion for the bicycle.

So back to the first 100km and all was going smooth. Pretty flat, deathly quiet country roads with nothing for company other now the odd sheep or cow. Around 50km we hit our first hurdle in the form of a ripped apart gravel road. The road was that under construction that is was a combination between strada bianchi and paris roubaix. For 5km you could do nothing but ride and slip and slide and bounce around and hope you don't puncher. Luckily we both escaped without any mechanicals and we were now at epping forest. From here It a short section of road heading south on the states main hwy before chucking a left and heading toward the coast. This left turn was the start of the most mentally challenging section of road we would face. I call this section road the hwy to nowhere as you simply feel like you are going nowhere for 100km. At the end of this section is the sea and the mood changes but until that point you are flat out not falling asleep either driving or on the bike. We put our heads down and plowed on. After 100km I was already struggling a little and needed to stop. We both slammed a coke, some water and I topped up the tank with a 600ml ice coffee. As we discover we would both have many moments of difficulty on the fuel front at different stages throughout the day.

2nd 100km:

This section of road was perhaps the toughest. Almost 60km on the hwy to nowhere and a block head wind to plow into on a false flat. When all put together it equalled a demoralising few hours. We both knew it was a big mental battle this section and made an extra effort to chat away about very important issues. It was during this period that we solved any more in depth personal issues we were facing and when riding under such torturous conditions the little issues you don't feel comfortable discussing normally all off a sudden seem not so bad to share with you mates. It always amazes me how sport can change your mood so we had a good old chin wag about whatever was bothering us to the town of st mary's. St mary's was a major land mark to pass as from there it was a decent to the coast and then a short run along to st helens and our half way point.

On the decent we struck our first mechanical. After 160km we finally hit a decent longer than a few hundred m's and we were plummeting down to the coast line when all of a sudden my front tyre blew out. I had 2 choices, crash or take my chances with on coming traffic and bee line it onto the opposite side of the road and come to a standstill in the ditch! I chose the other side of the road option and luckily no cars came around the corner. We took a deep breathe and ritchie just shook his head, we knew I was jolly lucky on that one! I repaired the tyre in the blink of an eye, infact the F1 mechanics would have been impressed and we were back on our way. Next up it was ritchie's turn to get a little hungry and after seeing me spring back to life on the back of my Iced coffee made an unscheduled stop for a similar pick me up. From there it was a short run along to st helens and half way and our lunch stop. When we pulled up I noticed my tyre was split so I went in search of a repair kit. I found one at the service station that contained enough patches to see me through my career so if anyone needs any patches for a ripped tyre send them my way! Ritchie organised lunch, a cheese and vegemite scroll while I repaired my tyre. We were certainly working well together.

Ritchie as me how I was feeling over lunch? I said awesome, I have convinced myself I have finished my first 190km training session and it was now time to start the 2nd 175km haul. Mentally its important for me not to think of it as 360km but merely ticking off check points. We had passed 2, the end of the hwy to nowhere and now the halfway point, next we making it to scottsdale, 100km away. Ritchie shrugged his shoulders in approval and we got back aboard our bikes. Everybody has there own way of dealing with these types of days and I am sure he did it differently.

3 100km:

During this section ritchie finally voiced a crazy idea we had both been thinking over. If we are going to ride 360km why not 400km?? Turth be told I was never going to let us stop before 400km but had been afraid to voice this suggestion. I had figured we make it to launceston then just keep trudging from there, first things first. Anyways ritchie is always one to speak his mind and said bugger it, we are definitely doing 400km. We will ride from dusk till dawn!! He rang his mum and informed her he would not be home for birthday dinner and that dilemma was now simply the plan. This 100km saw us enter some very hilly terrain and it was great to actually use some different muscles for a change. The trip to scottsdale seemed to fly by and before we knew it we were at 300km and slamming down yet another coke and iced coffee. This time we also threw a mars bar in the pocket as we set of on the 65km trek to launceston. Scottsdale is part of the cycling folklaw in the north of the state and ritchie probably rides through it 4 times per week. Consequently everybody at the IGA wanted to chat with the local star but were curious why he was there at 6pm?? When he said he had already done 300km they went from being star struck to simply in awe of there local star, it was also at this point I realised we still had 100km to go so was going to need to section it out very strategically!

4th 100km:

As we rolled out of scottsdale ritchie I have certainly never done the feared sidling climb with 10hrs in the pins before. We decided to cruise up but of course we didn't. While we went steady we knew once at the top we were pretty much home and hosed to launceston with predominantly down hill roads. As we crested the top ritchie simply said, "well I have certainly gone up there a lot slower than that". We dropped down the other side like stones and could smell launceston now less than 40km to go. Ritchie looked at me and said let's drop the hammer and we gave it some revs. We were both had a big spring in our step with the final climb behind us and the finish line insight. Ritchie even commented that all was going well and we were humming along. I simply said I am fixated on launceston and until I am there I don't want to think about how surprizingly fresh I felt. I had 11hrs on the clock, 330km and had averaged 230watts so we were really flicking along on. With that being said I did not want to relax and explained to ritchie it feels like I am having a good game of golf and I know but don't want to count my scorecard, that can wait until then end, don't count your chickens before they hatch!

With all the enthusiasm we barrelled toward launceston. We kept dropping the hammer and before we knew it we were in launceston and on the other side of in search of the final 40km to make up the magical 400km. First we had some logistics to organise and we both turned to our parents. I called my dad and asked him to bring an energy hit and please follow us to ensure we are safe on the road. It was now 8:30pm and we would definately be finishing this adventure in the dark. Dad arrived and we loaded up on coke for the final time and a few jelly snakes. The clock was at 12hrs 45min so we simply had 38km and 1hr of riding to hit our target. Since the 200km mark we had been dreaming of hamburgers from burgers got soul. These burgers are an absolut must eat if you visit launceston. Anyways our problem was it would be closed when we return so ritch now had to call his mum. We put our order in and now officially after being such big brave boys all day we had turned to our parents to nurse us and motivate us home. Dad led with the safety car and we rode with the fun and confidence and security that we would in a race.

In the final 10km we started to count our chickens! We could smell the finish line and knew we would make it. It was now pitch black but we were having an absolute ball. We could not believe we had pulled of this crazy challenge and felt a huge amount of satisfaction. We sprinted into launceston and straight for ritchies house and the burgers! Dad got sent via the bottle shop to pick a cascade for me and a boags for ritchie, the north v southern tas stable beers. Our diet during the day was certainly your usual race or hard training diet plan but hey it was ritchie's birthday! We had to have the party food and I must admit the biggest regret I have is that we didn't go the party pies and sausage rolls for lunch in st helens!

We had survived the journey. The funny thing was we could now admit to each other that all along we both wanted to hit 400km but were both scared to think out allowed and actually lay that down to early! The key to the day seemed to be the mind games with spacing the ride out into objective check points which were upon reflection pretty simple. Make it to the road to nowhere, stop at st helens for lunch, role along to scottsdale, charge into launceston, and finally an and back spin along the tamar river. I had convinced myself the final 40km were free km's as we would be so close to home that no matter what the body will keep going. And you know what, it really wasn't that hard a day. I did not feel anymore drained than I would after an actual hard training day. I am sure that seems strange but the power of the mind is pretty powerfull and I certainly realise that now as upon reflection if you told me to do what we did yesterday before we started I would have said impossible! Not because of capability but because it was so unknown. But like I said to ritchie on the sidling climb "if we always do what we have always done we will always get what we have always gotten"!

Looking forward to ritchie's birthday next year!  

Ride data

Time: 13hrs 30min

Distance: 403km

elevation gain: 4500m     

Ave Power: 230watts

Ave Heart rate: 115

Kcal Burnt: 11400

Cjw

Sent from my BlackBerry® wireless device

The Commuter Manifesto

Today we start a new venture on behalf of my fellow Metro-North commuters:  The Commuter Action Group

To capture the mood of the moment and explain our expectations as riders of Metro-North, I have written this Commuter Manifesto:

We, the riders of commuter railroads in Connecticut, are tired of deteriorating service, rising fares and indifference and ineptitude from Metro-North.  As customers and taxpayers we deserve better and expect change.  Our expectations are few, and simple:

SAFETY FIRST

              We expect a clean, safe, on-time, seated ride on trains with heat / AC and lights.  Don’t treat us like cattle making us ride on railcars you wouldn’t ride on yourself.

We want to know that you make our safety your top priority.  Make every employee understand that responsibility.  If they do anything that jeopardizes safety, discipline them or fire them.  There is no excuse for stupid mistakes.

FAST, ACCURATE AND HONEST COMMUNICATIONS

              When things go wrong, immediately tell us what’s happened, why and when it will be fixed.  When you make a mistake, admit it.  Stop making apologies; get things fixed and don’t repeat the same errors over and over again. 

RESPONSIVE CUSTOMER SERVICE

              When we see a problem, give us an easy way to report it to you.  Then get it fixed and follow up with us to tell us it’s been resolved. Our complaints shouldn’t fall into a black hole.

              Train your employees to be courteous and efficient, treating us like valued customers.  When they don’t meet those standards, train them again.  There should be zero tolerance for rude behavior by employees … or commuters.

OPEN & TRANSPARENT OPERATIONS

              Let us know how you make decisions that affect us by opening all of your meetings to the public and media.  Share your goals and self-evaluations and ask our opinions as well.  The way you run the railroad affects our lives and we should have input.

LEADERSHIP THAT LISTENS

              Meet with commuters on a regular basis at times and locations convenient to us.  Hear our complaints and suggestions and answer our questions.  We will listen to you if you will listen to us:  we’re in this together.

www.CommuterActionGroup.org      @CTRailCommuters     CommuterActionGroup@gmail.com

Going back to the beginning

Is time travel possible?

Alas and alack, no.

Although I have searched for just such a device, to my knowledge there is no WayBack Machine that can take me back to 1992.  Yes, that long distant time from the 20th century before the Global War on Terror, before the Internet (at the time I had an AppleLink account which gave me email  to someone actually outside of my Fortune 50 employer as email used to stop at the company network’s end – it was a different time), a time when I did OLAP on an IBM mainframe using Comshare’s System W, and a time when all of the hair on my head was 100% devoid of grey.  

22 years is a fair clip ago when it comes to personal history and it is an eternity in the technology field.

And yet there has been in my professional life a constant since that time (for me it started in 1993 or 1994 but give me artistic license).

What oh what oh what could it be?

Note to young people (like, oh, under the age of 30) – once upon a time, people (honestly, even the women had to wear ties of a sort)  in corporate America were expected to wear a suit and tie (all the time – to the bathroom, to lunch in the cafeteria, on the way out the door – always), took notes on paper (actually, I still do), had 3270 terminals at their desks (nope, don’t have one of those, thankfully), etc.  In other words, “The past is a foreign country:  they do things differently there.”

With that preamble, what might be the one constant from early in the Clinton administration?

No, not suits – those are gone except for weddings and funerals.

No, not ties – see the above.

No, not rolodexes – they were obsoleted by smart phones.

No, not mainframes – they are certainly around but are no longer the primary target of corporate IT.

It’s Essbase.  Yes, Essbase is that old – over 22 years.  If Essbase were human, it could vote, marry, take a loan out for a house, buy a gun, and serve in the armed services.  In other words, Essbase is a mature product.

That it is still around, largely in its original form is testament to its genius.  Yes, there have been many improvements and additions, but at its core, Essbase still does what Essbase did when it was first invented by Bob Earle and Jim Dorrian back in 1992.  Don’t believe me?  Take a look at the patent for BSO Essbase.  Yes, of course ASO Essbase is internally quite different, but read that application and see if what was described is all that functionally different than what it does today.

Not a time machine, but something almost as good

As I wrote, I haven’t found a time machine.  But I recently did get to spend a fascinating afternoon with one of the two fathers of Essbase – Bob Earle.  I must hasten to add that this is because of the generosity and contacts of my good buddy Tim Tow who seemingly knows everyone.

Fellow ODTUG board members Tim, Natalie Delemar, and I met Bob for a few drinks at a hotel in Seattle.  We were all there because of the ODTUG board face to face meeting (we have two a year, one right before Kscope14, one six months earlier, usually in the host city).  This was our chance to meet one of the progenitors of Essbase and we were all quite keen to meet him.

Here we are.  

Natalie, Yr. Obdnt. Srvnt., Bob Earle, and Tim Tow

So what was it like?

Bob is an incredibly self-effacing guy.  He was astonished that Essbase was still around, amazed that people cared about it so much, and was, I think, sort of gobsmacked that we were so interested in his story.  He was also very clear that he was only one half of the team that invented Essbase.

Essbase was an idea, an opportunity, and (I think) a really fascinating and stressful chapter in Bob’s life.

I will also note that Bob is a very private person, and asked that what we discussed remains confidential.  I will say that I think he found the genesis of Essbase to be quite the roller coaster and he has completely divorced himself from the high technology world.  Take from that what you will.

It was a true honor to meet the man that set so many of our careers on the path they now are.  I’ve often thought of what I might be doing if my former boss, Mike Rose, hadn’t thrown Comshare’s resold version of Essbase on my desk and said (I am paraphrasing, but this is pretty much as it happened), “My mainframe costs are killing me, see what you can do with this product.”  

The rest, as the saying goes, is history.

Here I am with the guy that unwittingly landed me right here at this blog.  

Sometimes you can go home again.

The Old Willunga Hill stage 5 TDU

The stage to the top of old willunga hill is the most anxiously anticipated race in australian cycling. The climb has become synonymous with the TDU over the years and everyone as a result gets very excited about stage 5 and the saturday of the event.

This years race has been absolutely spoilt with excitement and world class racing. Not that every other year has been world class but this year the standard level of the racing and the riders creating the action is simply amazing to see on aussie soil. No one could have imagined our sport would grow to what it is today in australia and off course a lot of that is down to 5 letters, CADEL! Well fittingly the aussie sporting icon began the day in the leaders jersey but was set to face an all out assault from fellow aussie simon gerrans one way or another during the stage. The only question was which tactic would gerro employ to take on cadel??

Most believed like yesterday gerro's green edge team would again chase the 2 intermediate bonus sprints on offer in the first part of the stage. Here he could gobble up 6 seconds and be just 1 second behind or would he chase the bigger bonus's on offer at the finish and go for the stage win and race lead?? Well gerro is a fighter and always backs himself so I guess it should have come as no surprize that he chose to chase the stage win and overall glory in one hit. It was a decision that made the clear statement the he truly believed he was the best rider in the bike race. By days end gerro would indeed be back in the leaders jersey. He didn't win the stage but neither would cadel. Simon was 3rd and distanced cadel enough to overhaul him by a single second. It was a bold move by simon particularly seen as though cadel had dropped him on the corkscrew climb so he really turned the tables tables today. So hats off to gerro, he is leading the race but all along he has earnt it in the most exciting possible way so congrats to him and his team.

To the stage. Jensie attacked from the gun and quickly disappeared into the distance with 3 others. We wouldn't see them again until to top of old willunga hill the first time up. The stage does 3 laps around willunga town and the beach and the a lap of the climb and next time up finish's at the top. The crowd as always was absolutely incredible. It gets larger and larger every year and this year on the willunga 3km climb it was simply a wall of noise, like a big cauldron. There must have been a few hundred thousand people out there today, it was absolutely amazing to be apart of. So with the break gone the wait and watch game happened to see who would do what next.

The first to commit to the chase and setting up the stage was the sky team of fellow tassie boy richie porte. I immediately was a little edgy about this as the magician bernie eisal was on the front. Bernie has this amazing ability to chase at the exact perfect tempo to save the legs of his boys and make life hell for all those behind. One moment he has the group at warp speed and strung out in a line and the next at snails pace and riders are all bunched up and frustrated they can't get to the front. He just has this amazing instinct to ride at the perfect pace to basically zap the life out of you and your team mates and in the process keep the race completely under his command! So with one lap up the hill out of the way the race was back together. Bernie had paced it perfectly and the final escapee was brough back as we crested the top of the mountain. From here greenedge took control and drilled it in the gutter. I was actually remarkably calm at this time as although I was a little out of position I saw it was just impy and hayman on the front. I was pretty sure gerro would be saving his boys as long as possible so doubted they would completely open the throttle that far out. Sure enough it did split the group a little but was fortunately possible to stay in the main field. So down the hill and onto the final few kms of flat before we hit old willunga hill for the 3km hilltop run to line. All the big guns were there so the stage was set for what all the aussie fans craved, a battle between australias biggest stars.

As we swung left and hit the lower slopes I was feeling pretty good. You always feel good when the pace is not on, you are just waiting for how much pain you can suffer though when the big guns hit the throttle! I was a little sleepy at the bottom and got a few to many wheels back. In the end it certainly did not cost me a high stage placing but again I would have loved to be a little more apart of the action. Anyways again I wasn't up there when I needed to be and when cadel hit the gas much like on the corkscrew I got left behind! To say I was a little tired after yesterday is an understatement, I have gone beyond being tired now, I simply can't feel anything, only problem is I simply don't have any power left either! Anyways still that's all apart of the game, can't keep making withdrawals from your system and not expect your balance to go down! Still I survived the day closer to the front than the back so happy to still be amongst the action and looking forward to keeping the condition building from here.

Back to the race and with cadels attack the strongest men where quickly leading us a merry dance. It was a block headwind on the climb so for any rider to make a real difference and break free they needed to be incredibly brave and super super strong. Cadel unsurprisingly believed he had these characteristics and was the first to show his hand. Then cometh the hour cometh the man, I saw ritchie porte step out into the wind as we passed the 2km barrier and in a wink of an eye the pint sized tasmanian was riding away with stage win and well and trully hammering the into my Tassie Cup aspirations. With the wind like it was it was only going to take a gutsy phenomenal effort to ride away from the best in the world and on willunga today ritchie showed everyone exactly why he is our next big hope for grand tour success. As I said before its the biggest day on the aussie cycling calendar and we all won't apart of the action. Ritchie was still able to show a clean pair of heals finishing of the great work from his team and particularly bernie the magician eisal. Big good stuff to Mr Porte today.

So that was the race. Sadly I couldn't be as crazy as yesterday and get amongst it but was happy I was atleast apart of it. I know where I am missing the mark and what I need to work on so can't wait to get home tomorrow to hobart and start working on that straight away. First however we have the final street circuit around adelaide tomorrow. It will be a bunch sprint so our last chance to set Elia up for a battle royale with the fastmen. After the disappointment of letting him down yesterday I am champing at the bit to get out there tomorrow and give him all I have to give him the best shot at glory. Been an awesome TDU so looking forward to finishing it off tomorrow in style.

CJW   

     

Sent from my BlackBerry® wireless device

Breaking away Race Data

Forgot to put race data in blog

Distance: 150km

Time: 3hrs 32min

Ave speed: 42kmph

Ave power: 314 watts (without 0's 341watts normalized new record!!)

Ave power for first 3hrs: 330watts (in breakaway)

Max power: 1100watts

Ave Heart rate: 164BPM

Max Heart rate: 191BPM (was a bit soft today)

Kcal burnt: 4011kcal

Sent from my BlackBerry® wireless device

Breaking away stage 4 TDU

Well today was a touch on the more interesting side for me! There was a very clear script for today stage, almost a blue print infact. 2 sprints with bonus seconds on offer, one close enough to the start to control the field for and one close enough to finish to control the field for a bunch sprint afterwood. Through in the fact that it was the only real sprinter friendly looking stage meaning half the field was interested in ensuring it was a bunch sprint. Well I decided to not accept the blue print and see if I could do somthing a little crazy for the day and go on the attack!!!

The stage started with an 8km climb just to soften the legs a little. I was certainly suffering so was not surprized to here the group was split in 2. It would regroup over the top as greenedge took control for the first sprint but I knew it was a tired peleton. There was no point attacking before the sprint but I knew as soon as we passed through and the dust settled it was time to open the throttle. Jensie was the 1st to launch and this only confirmed my desire to get up the road and when his move was brought back I set of in search of some time in the sun. Finally I was again in a breakaway again. Much like in the giro it seemed like a suicide but I felt with the wind blowing and a tired peleton anything and everything could happen over the next 120km.

5 of us skipped out to a 5minute lead and I immediately thought this is a good situation. The coolest thing was the composition of our escape group which included the obligatory frenchmen from ag2r, arishiro the japanese national champ from europcar, a saxo dude from I have no idea and last but not least my great mate in the red outfit of drapac from tasmania Wez sulzberger. We worked well together and everyone was committed, particularly when we hit a 5minute advantage, the motivation swelled amongst us escapees! Well all was good until the team cars arrived and the saxo director told his rider not to work with me as I was to high on GC. Fair enough, each team is entitled to there tactics but I doubt he was expecting what happened next.

I simply attacked almost full gas in the cross winds and drew away wez with me. We went almost full gas but the saxo dude, as expected burried himself to pull us back. Perfect, he fell for that one so just as he got back on I hit it full full gas again this time and I was away. I have to thank the other guys who said to him again if you want to play games you have to chase again and this time he surrendered. The ag2r guy then jumped away and I knew I could not let up to much to ensure I kept going away but I just rolled over a pace that the frenchey could slowly catch me but in the meantime maintain our lead. Once he got across he was a little annoyed I had not immediately waited for him so I told him to sit on my wheel for a while and catch a breather. If my plan of riding away with the bike race was to succeed he was a crucial cog in the chain!

So we were away and consolidated our 4'30" lead. At this point I started to do some calculations and set some targets. First was getting to the 100km mark with as bigger gap as possible. There was a chance we would have a tail wind for the final 35km but unfortunately I would quickly realise it was the total opposite! Oops. Still 100km was the first target the reassess. I figured I needed to set a tempo that I knew was uncomfortable for a team to close which was between 350-380 watts or my medium zone. I can hold this for a long period of time and know that behind they need to ride close to threshold to close the gap. Sure enough the gap started closing slowly as I knew greenedge would be working to close us down before the final sprint at 25km from the finish. I knew now with a head wind instead of tail wind staying away was almost impossible but I was going to make it as hard as possible for those chasing to catch me. We worked well together, I certainly did a majority of the work but that's fine, I felt great and wanted to work today. I was annoyed with myself yesterday and wanted to suffer out there. I wanted to get my fight and hunger back into my system so while I was basically on the limit for 3hrs I was relishing the challenge. I need to make special mention to matty junior morhic who showed me his decending technique he used to great effect when he won the U23 worlds last year. It involves sitting on your top tube and pedalling and its amazing how much free speed you get. This was definitely a saviour for me today.

So past the 100km mark and onto the climb. The gap was falling so I knew we were getting caught but being up the road had been usefull in a few ways for the team. The wind which we knew would be decisive was blowing and I was able to warn my team mates what direction to expect the wind to come from and to position accordingly. I would radio the info and biagio would relay it to the guys from the team car. Back to the race and onto the climb and here I set a pace I knew was one that the peleton would have to stay below to keep the sprinters happy, it was around 450-470 watts and sure enough at the top our gap had gone out a bit. To me though now it was inevitable we would be caught before the final intermediate sprint line 25km from the finish so I started backing it off and charging the batteries for the next phase of the race. I knew how hard we had been riding and how quickly the group was coming and as a result knew very well and good that the entire peleton would not be catching us. Sure enough 5km before the sprint the greenedge led bunch of no more than 60 riders gobbled us up and the escape was finished. I didn't really care about that I was just happy that they had had to work hard to catch us and that elia was safely in the front of the field. Next job was to do a little head count which didn't take long, it was just elia and I in this selection so then I looked for what sprinters were not there. The kittel group, one of the big favourites for the day was merely 50 seconds behind and chasing hard. I didn't bother worrying about this for the moment as greenedge were going full blast for the sprint. Once the sprint was done it was time to get back to work.

I now had a chat with the lotto boys and said I will ride if you do to stretch out the gap to kittel and the other sprinters with him. We rode hard for 5km or so and all of a sudden we had 3minutes. At this point I finally logged off and went back to the car to get some water and food for Elia and my attention turned to looking after him. We simply rolled along in the bunch and despite my 100+km on the attack, my legs actually felt pretty snappy. The pace was good so no need to panic, just stay in the wheels and look out for elia. At 5km to I made the stupidest decision I have made for a long time!!

As we rolled past the 5km to go banner I had elia positioned perfectly on greipels wheel. Would have been perfect had he still been the 4.5km later! Oh how much can wrong in that time. At 4km to go the pace slackened again and again like yesterday I had a brain melt, a touch of redline fever and made a big mistake. I attacked again! I went of the front in a desperate bid to unsettle the sprint trains. I did not back myself to help elia in the sprint so figured the best assistance I could be to him was to tire out the sprinters teams by having to chase me. Off course with this theory I assumed elia would still be glued to Greipel's wheel so my plan was perfect. Well 2 things went wrong. Firstly there was a small rise a little longer than I anticipated that really slowed me down and I was caught way quicker than I wanted to be! And secondly In the scurry to chase after me riders were all over the place and wheels in different spot and Elia was no longer glued to the lotto train. By the time I realised all this the group was engulfing me and we were now rocketing into the final kms and I was nowhere near elia and with no chance of getting back to him. I really messed up there, I should never have left his side as now when he really needed me I wasn't within cooeee of bailing him out. Good slap in the face to remind me not to come up with my on brilliant ideas in sprint finishes.

So we all saw what happened next. Elia started the sprint way out of position and charged at the line so fast he sucked the helmets clean of the head of the 20 guys he past en route to an incredible 3rd place on the stage. He came so fast that there was simply a green flash on the screen and as often is the case we were only left to ponder what if the line had been 100m further down the road. The reality is had I have been there to even just help him with 5 of those spots he had to come past I would have probably made the difference. We will never know now but what is certain was I made a big mistake and it potentially cost elia the win. I am very very sorry and like yesterday I can't change my misdemeanour from today but will definitely remember and be champing at the bit to ensure next time I get the chance to help elia I execute whatever role he wants to play as close to perfection as I can.

So today was definitely 2 very distinctive parts. The first I felt like I could somehow blow the race apart with a seemingly fruitless attack. Unrealistically ambitious I know when racing against a peleton of this level but hey, what not give it a crack. Upon assessment I think I did succeed to some extent. I think green edge had to chase very hard and that blew the field apart. It blew off a lot of sprinters and there teams and gave elia an even greater chance of victory. Sadly in the end I probably had an equal hand in ruining elia's chances at that victory with my stupid assessment of the situation and subsequent attack in the final km's. The day was almost perfect and for the large part I felt awesome. I made my first trip to the podium for a very long time and had an amazing reception from the crowd, it capped of a memorable day on the bike. As great as that self celebrating felt it cannot mask the pain I am feeling for letting down my team leader and good friend Elia in those final km's. That is a feeling that will stay with me from today and that's good. It will drive me to keep pushing to really turn into a bike rider. Just like Jensie said to me yesterday "your never too old to learn" well I had a good old fashioned slap in the face reminder of that today!! Anyways onwards and upwards, todays today and tomorrow presents a new opportunity.

CJW

Sent from my BlackBerry® wireless device

Corkscrewed stage 3 TDU

Well today was always going to implode the peleton and that's exactly what happened! The corkscrew climb situated on the outskirts of adelaide has become quite synonymous and a pivotal point in the TDU over the past 2 years. The organisers do an amazing job of turning climbs that would normally not seem so daunting into ones that have the potential to give you nightmares! Much like Willunga Hill on saturday, todays short but steep climb of corkscrew hill was billed as a potential game changer and that's exactly what happened. The legend of aussie cycling, Mr Cadel Evans fittingly stole the show with a display of power that's we have become accustomed too over the past decade. What made his attack so impressive today was the simple fact that everybody knew exactly what he was going to do and nobody could do a thing about it! Well done Cadel.     

For us Cannondale boys we put all our eggs into a good performance from George and myself on todays final climb. Like the past days the plan was for everybody to keep us fed, hydrated and in a good position in the bunch and that's exactly what the boys did. Krizek I am pretty sure will have wind burn for all the hours he kept himself in the wind to keep us up the front which made a huge difference. Again Boivin and Elia used the super duper sprint positioning skills to position us when things got tricky, again made life so much easier. Although today did not suit them its always such a nice feeling having the sprinters sacrifice themselves for you, it just fuels the fire for you to repay them next time we find ourselves in a bunch gallop stage finish.

So the stage went a little more smoothly than expected for the large part. A small break went and it was kept on a very tight leash. Jensie was up in it so everybody knew that was dangerous so the escapees were never going to get to far down the road. Todays stage was characterised by 2 things. The corkscrew, and the 20km downhill through the george to get to the bottom. Everybody knew they had to be right at the front for the decisive left hand turn 2km from the start of the climb so you can imagine the pace and chaos of the 20km run in!! With that being said the boys had done an incredible job saving george's and my energy all day and in the 20km of frenetic positioning they were at there scintillating best! I unfortunately had a few nervy corners and a brain melt and forgot how to think and lost them in final few km's before the decisive turn and consequently ended up way out of position. Very very stupid! George however had the perfect possi to start the climb so I just swallowed my poison and had to cross my fingers that through some miracle I would have a good ride up the climb. The pace never let up, I was way to far back, and when Cadel hit the throttle the group split to pieces and I was instantly blow well and truly out the arse end of the peleton. I gave chase as best I could trying to close the gaps that had opened up but to no avail. My body was at the limit, much like it has been over the past couple of days and again my heartrate was over 200. I dragged myself up the final 1500m of the climb. At a few points I was going that slow that I should have been given a parking ticket! I was on suffer street to say the very least and on the corkscrew climb this year I was definitely the cork getting screwed and not the other way around!  

The TDU every year seems to be an absolute nail bitter. The course design and structure with the bonus seconds really gives the aussie fans something to get on the edge of there seats for. Take todays stage for example. You stick a left hand corner 1.5km before the start of a nose bleeding 2km climb and after 20km which we cover at 60-80kmph and you get a real bike race. In that space of 25km you test your teams ability to position its rider, your riders ability to handle there bicycles under the most stressfull of circumstances, and of course your leaders ability to ensure he utilises all these skills to be in the right position when he hits that decisive corner. Once around the corner your pretty much set in your position and then you need to climb with some of the best mountain men in the world. But then the climb is only 2km long so of course there are more people capable of going hard for 2km than 10km so to make a difference in the race you have to be incredibly strong and incredibly courage's. Oh and add again to that the fact so many of the best riders in the world have assembled upon adelaide for the TDU and you get an incredibly exciting bike race. Not only for the fans but also for the riders as on a day such as today we are on the edge of our seats wondering how it will pan out. Might I also though in was relatively short by world tour standards at just of three and a half hours which adds to the freshness and motivation of the bunch throughout the stage and with it the battle for position. So I guess what I am really trying to point out is the fact that to make a difference on a stage like today makes what cadel did really really impressive.  

As for jensie talk today, well it was very short lived. We did have a little chat this morning in the neutral zone about what I had found out about those sad looking grapes we gawked during our nature break on stage one. I have since found out that they are funnily the sultana variety of grape and won't be picked for a few months. Upon sharing this info with jensie naturally had an intelligent response! He said "you know cameron, your never to old to learn something new". A few minutes later I saw a black flash rip passed in my peripheral vision which could only mean one thing, jensie was on the attack. He went so hard that he was half a km up the road before anyone could think about following let alone chasing him. What a LEGEND!

   

So on my part I unfortunately had a bit of barry crocker shocker. All I can say is that  I am no where near the level of the big guns in the race which comes as no surprise as I know I have not done the work that I know I need to do to be where I feel I can be competitive. I had arrived here HOPING I would go well as I have been feeling pretty good but in reality HOPE has never had me perform at my best. Due to my extended off season, which I desperately needed, I have never started the season having done so little training. While arrogance had me believing I could perform well this week, reality has been very humbling over the past few days and so far there have been no fairy tales. I aint an athlete that can get by on talent sadly, I have to work very hard to perform at my best and I still have not worked anywhere near hard enough. I have literally arrived at the finish on my knees these first few stages which is a sensation I have not experienced for an extremely long time. I don't know why this is, letlag from california, change in climate, bad condition, probably a mix of them all, it doesn't really matter, it is was it is for now. I simply need to go home, work harder, smarter and keep building on the work I have already done and arrive at the next race better prepared. Then I need to continue this process until I finally get myself in tip top shape.

Tomorrow presents a new opportunity for the cannondale boys with an expected sprint finish. After the boys doing such great work over the past few days for us, I am really excited to repay the favour and help Elia and the Canadian Cannonball into position for the bunch gallop. New day, new opportunity!

Race data

Diatance: 143km

Ave speed: 40.5kmph

Ave Power: 237watts

Max power: 1000watts

Ave Heart rate: 139BPM

Max Heart rate: 202BPM

Kcal Burnt: 3100

CJW

    

Sent from my BlackBerry® wireless device

Stage 2 TDU

The stirling stage of TDU always destroys the bunch! Its one of those strange stages that seems pretty cruisey until the final 800m which regardless of how the previous 4hrs has panned out leaves you on your knees!! Today was no exception! In fact today hit me so hard in the final m's that once I finally rolled to a stand still I simply got of my bike and walked for 100m or so, like yesterday the thought of pedalling any further than I had to made me feel physically sick! Anyways fortunately I survived the day one way or another and following my massage am fired up for another round of pain cave tomorrow.

Today the plan for the cannondale boys was again pretty simple. George and I try to avoid losing any time and should it come down to a sprint then ensure Elia and the cannadian cannonball are front and centre to duke it out with the fast men. The boys did a perfect job all day and I don't remember ever feeling out of position the entire day, well that was until the final 1km that is! Junior moharic and krisek did a great job all day and we needed to be at the front for the potentially decisive final 15km george, elia, cannadian cannonball and I were right there. All looked exciting for us leading into the finish until a big crash in the final km split the group and we all got caught behind. To be honest, even had it not have held me I don't think I was feeling as spritely as last year when I hit out in search of glory but none the less I did not get a chance to find out this year so at the end of the day was happy not to lose anymore time on GC to the main field. The same applied for george so atleast that part of the plan was executed one way or another!

My conversations with jensie are becoming a highlight of my day in the bunch. Each day there is somthing different for us to have a yarn about and today was no different. At the start we were chatting away about this that and the while in the neutral zone and I said to jens, gee my legs are sore I think we should attack at km 0!! No said jens as I don't have an aero jersey and I would waste to much energy pointing to his baggy black and white jersey. Fair enough I said but I attacked anyways to no avail! All I wanted to do was wake my pins up and i succeeded, I felt much better after that!

Anyways back to fun with jensie. Later in the day I waited until we had a good little audience around of people I felt we could have a little fun with. My cannondale boys were there of course, the lotto boys, the sky boys and a few of jensie's team mates. At this point I said "hey jensie, why does your jersey look like a T shirt? Which got a good laugh out of all the boys as I had hoped. Then as things calmed down a bit Bernie Eisel, another absolute legend and gentleman of the bunch said "jensie ordered them a few sizes to big as he is planning for his retirement! Knows its his last year of getting free cycling kit!" That response had us in stitches, nice to have a bit of fun in the bunch, it is the first race of a long season afterall and we have to ensure we enjoy as much jensie moments as possible!

So back to the race and basically was a non event for us cannondale boys in the end. We had done everything right all day and seemed to have ourselves positioned perfectly until the crash in the final km. I again had my heart rate north of 200 with 203BPM being my max today. When I saw that obviously it was at a moment we were going full gas but I remember thinking gee I am suffering what's my heartrate? I had a look and saw 203 and said to myself "Beauty I still have 3 beats to go so the tank aint empty yet!" So I decided to calculate how much time I have been north of 200 in the first 2 stages, the answer? 27minutes! Tomorrow we face the corkscrew, the decent that ruined my TDU last year. So I guess my plan will be pretty simple, firstly get to the top as far toward the front as possible and then not crash on the plunge down the other side!      

Todays race data

Time 4hrs

Ave Power: 240 watts

Ave Heart rate: 140

Max Heart rate: 203

Kcal burnt: 3700

Max power: 1100 watts

Cjw

Sent from my BlackBerry® wireless device

Shared Services Stupid Trick No. 4

Challenges

I like them.  No, not impossible ones, like cleaning out the Augean Stables.  I’m no Hercules.  Those who have seen my manly physique know I am more like the before than the after in this famous-if-you-ever-read-comic-books ad.  And before you get any ideas, know that yr. obdnt. srvnt. does not habitually kick chairs when frustrated, seek out “dance floor hogs” for a damn good whacking, or practice “dynamic tension”.  I do like hiking, however.  And isometrics are not a bad way of building anaerobic strength.

What I really enjoy is being told that something is “impossible” when I have a sneaking suspicion that maybe it isn’t.  And then trying to see who’s right – Mr. Know-All or me.  Sometimes I win, sometimes I lose but no matter the outcome I’m the wiser for trying.  This is one of those posts.

The genesis

Over on Network54’s Essbase board, there was a rather long thread about Things That We Wish Oracle EPM Did.  And in that thread, there was a comment or at least an implication that SQL queries couldn’t join Shared Services provisioning to Planning access but that XML processing could.  On the face of it, this didn’t make sense – why would one technology be better than another when it came to matching up data?  There was also the admission that the LCM->XML->MS Access code took over an hour.  Surely I could beat that performance with good old SQL.   Maybe.

The challenge

It seems pretty straightforward.  Based on that thread post, using SQL, could I:

1. Match Shared Services provisioning and roles with Planning application security
2. Do so in less than an hour

Of course this is a challenge that I set for myself and if I ended up not being able to do it, well, I tried, failed, and maybe would have to rethink the power of SQL.  And if I solved it, hey presto – an excuse for a blog post.  As you’re reading a blog post, I reckon not many of you are sitting on the proverbial edge.

Okay, I cheated

There’s cheating and then there’s cheating.  I was pretty sure that I could do this because I had already written a four part series on Planning security and a three part series on Shared Services security.  I think I know the basics of querying Oracle EPM security.  So not cheating, but at least an idea that this maybe it wouldn’t be all that difficult to do.

And for once I was right

In fact, this query was simply the mashup of the direct Shared Services and implicit Planning queries.  Joining across databases (this is SQL Server – Oracle would just be joining across schemas and works the same way) is easy:  just specify the database name before the table and Bob’s Your Uncle.  It took me all of three minutes to do this – it is nice when one has the code already done although when I wrote it I never thought I would join the results.

The code

USE HYP_SharedServices

GO

--    Declare the anchor member as a variable as it will be referenced more than once

DECLARE @TopCat AS nvarchar(4000)

SET @TopCat = 'PLN_SampApp1' ;

/**********************************************************************************/

/*    NOTE NOTE NOTE NOTE -- The below is direct Shared Services assignment.        */

/**********************************************************************************/

WITH TopCat AS

(

   --    Set up the anchor member

   SELECT

       G.LOWER_IDENTITY_ID AS 'Parent_ID'

       , G.LOWER_IDENTITY_ID AS 'Child_ID'

       --    Need to CAST as VARCHAR(200) to allow UNION ALL

        , G.NAME AS 'Parent'

       , CAST('' AS VARCHAR(200)) AS 'Child'

       , 1 AS 'Gen'

   FROM CSS_GROUPS G

   -- This is where you put in the topmost member of the tree

   WHERE G.NAME = @TopCat

),

Related AS

(

   --    Almost Parent/Child, but not quite

   --    I made this a separate CTE because it's cleaner down in the true Parent/Child query

   SELECT

       GM.LOWER_GROUP_IDENTITY AS 'Parent_ID'

       , GM.LOWER_MEMBER_IDENTITY AS 'Child_ID'

       , G1.NAME AS 'Parent'

       -- 1 = user, 2 = group; Cast both as VARCHAR(200) to allow UNION ALL

       , CASE

           --    Subqueries for Users and Groups to get the names

               WHEN

                   GM.MEMBER_TYPE = 1

               THEN

                   (SELECT

                       CAST(U.NAME AS VARCHAR(200))

                   FROM CSS_USERS U

                   WHERE U.LOWER_IDENTITY_ID = GM.LOWER_MEMBER_IDENTITY)

               WHEN

                   GM.MEMBER_TYPE = 2

               THEN

                   (SELECT

                       CAST(G2.NAME AS VARCHAR(200))

                   FROM CSS_GROUPS G2

                   WHERE G2.LOWER_IDENTITY_ID = GM.LOWER_MEMBER_IDENTITY)

           END

       AS 'Child'

   FROM CSS_GROUP_MEMBERS GM

   INNER JOIN CSS_GROUPS G1 ON

       GM.LOWER_GROUP_IDENTITY = G1.LOWER_IDENTITY_ID

),

ParentChild AS

(

   -- Set the anchor

   SELECT

       T.*

   FROM TopCat T

   UNION ALL

   -- Oooh, fancy, we're going recursive here

   SELECT

       R.Parent_ID

       , R.Child_ID

       , R.Parent

       , R.Child

       , PC.Gen + 1

   FROM Related R     

   --    This is the recursive JOIN that ties child to parent

   INNER JOIN ParentChild PC

       ON R.Parent_ID = PC.Child_ID

   ),

--    Figure out all of the possible roles in each project

RolesByProduct AS

(

   SELECT

       R.LOWER_IDENTITY_ID

       , R.ROLE_ID

       , R.PRODUCTCODE

       , R.PRE_CONFIG_STATUS

       , L.VERSION

       , L.NAME

       , L.DESCRIPTION

   FROM CSS_ROLES R

   INNER JOIN CSS_ROLE_LOCALES L

       ON R.LOWER_IDENTITY_ID = L.LOWER_IDENTITY_ID

   WHERE

       LOWER(L.LOCALE) IN (LOWER('en_US'))  

       AND L.LOWER_NAME LIKE LOWER(N'%')

       AND LOWER(L.VERSION) = LOWER (N'11.1.2.0')

),   

--    Figure out direct security assignments for all levels of the hieararchy.

--    Use the undocumented view VW_APPLICATIONS to get a pretty descriptions of the

--    products and projects.

DirectSharedServicesSecurity AS

   (

   SELECT

       --PC.*

       --, I.LOWER_ROLE_IDENTITY

       --, I.LOWER_APPLICATION_ID

       PC.Parent

       , PC.Child

       , PC.Gen

       , VA.PROJECT_NAME

       , VA.APP_DISPLAY_NAME

       , R.NAME

       , R.DESCRIPTION

       , VA.PRODUCT_NAME

   FROM ParentChild PC

   LEFT OUTER JOIN CSS_PROVISIONING_INFO I

       ON PC.Child_ID = I.LOWER_MEMBER_IDENTITY

   LEFT OUTER JOIN VW_APPLICATIONS VA

       ON I.LOWER_APPLICATION_ID = LOWER(VA.APPLICATION_ID)       

   LEFT OUTER JOIN RolesByProduct R

       ON I.LOWER_ROLE_IDENTITY = R.LOWER_IDENTITY_ID    ),

/**********************************************************************************/

/*    NOTE NOTE NOTE NOTE -- The below is Planning implicit security assignment.    */

/**********************************************************************************/

 -- Dimensions and all of their members

    Dimensions AS

    (

    SELECT DISTINCT

        O.OBJECT_ID,

        O.OBJECT_NAME AS "Member",

         (SELECT OB.OBJECT_NAME

            FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT OB

            WHERE OB.OBJECT_ID = M.DIM_ID) AS "Dimension"

    FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT O

    INNER JOIN

        HYP_PLN_RestoreApp.dbo.HSP_MEMBER M

        ON M.MEMBER_ID = O.OBJECT_ID

    --ORDER BY O.OBJECT_ID ASC

    ),

 -- All of the other object types, including the ones that aren't documented or in HYP_PLN_RestoreApp.dbo.HSP_OBJECT_TYPE

 ObjType AS

    (

     /*

       1 to 50 defined in HYP_PLN_RestoreApp.dbo.HSP_OBJECT_TYPE

       103 = Menus

       107 = Composite forms

       115 = Deployed Business Rule

       116 = Looks like Business Rules, but don't exist in CM?  So orphaned?

       117 = Calculation Manager variables

       118 = Business Rule Folder

       119 = CalcMgrRulesets -- that's actually the OBJECT_NAME, so defined by system?

       120 = There are four valies in a three Plan Type Planning app:

             CalcMgrVariables

             CalcMgrVariablesPTName1

             CalcMgrVariablesPTName2

             CalcMgrVariablesPTName3        

     */

     SELECT DISTINCT

       OBJECT_TYPE AS "OBJECT_TYPE",

       TYPE_NAME AS "TYPE_NAME"

     FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT_TYPE

     UNION

     SELECT

       CONVERT(INT, '103') AS "OBJECT_TYPE",

       'Menu' AS "TYPE_NAME"

     UNION

     SELECT

       CONVERT(INT, '107') AS "OBJECT_TYPE",

       'Composite' AS "TYPE_NAME"

     UNION

     SELECT

       CONVERT(INT, '115') AS "OBJECT_TYPE",

       'Business Rule' AS "TYPE_NAME"

     UNION

     SELECT

       CONVERT(INT, '118') AS "OBJECT_TYPE",

       'Business Rule Folder' AS "TYPE_NAME"

     --ORDER BY OBJECT_TYPE, TYPE_NAME    

    ),

 --  Get every object in the application

 ObjectID AS

   (

     SELECT

       OBJECT_ID,

       OBJECT_NAME,

       OBJECT_TYPE,

       SECCLASS_ID

     FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT

   ),

 -- This is almost the end of the road, but it doesn't take into account implicit security

 -- Stop here if that isn't important

 FinalCTE AS

   (

     SELECT --DISTINCT

       --OT.TYPE_NAME AS "Type",

       --  If the OBJECT_TYPE = 50 then it is a user-defined or custom dimension

       --  so do a subquery to pull the dimension name

       CASE

         WHEN O_ID.OBJECT_TYPE != 50 THEN OT.TYPE_NAME

         ELSE (SELECT D."Dimension"

                 FROM Dimensions D

                 WHERE O_ID.OBJECT_ID = D.OBJECT_ID)

       END AS "Type",

       O_ID.OBJECT_NAME AS "Object",

       CASE

       -- Subquery to get user or group type

           (SELECT OA.OBJECT_TYPE

               FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT OA

           WHERE OA.OBJECT_ID = AC.USER_ID)

           WHEN 5 THEN 'User'

           WHEN 6 THEN 'Group'

       END AS "Security Type",

      (SELECT OA.OBJECT_NAME

           FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT OA

           WHERE OA.OBJECT_ID = AC.USER_ID) AS "User/Group Name",

       CASE AC.ACCESS_MODE

           WHEN 1 THEN 'Read'

           WHEN 2 THEN 'Write'

           WHEN 3 THEN 'Write'

           WHEN 4 THEN 'Launch'

           WHEN -1 THEN 'Deny'

       END AS "Read/Write",

       CASE AC.FLAGS

         WHEN 0 THEN '"' + O_ID.OBJECT_NAME + '"'

         WHEN 5 THEN '@CHI("' + O_ID.OBJECT_NAME + '")'

         WHEN 6 THEN '@ICHI("' + O_ID.OBJECT_NAME + '")'

         WHEN 8 THEN '@DES("' + O_ID.OBJECT_NAME + '")'

         WHEN 9 THEN '@IDES("' + O_ID.OBJECT_NAME + '")'

        END AS "Hierarchy function"      

     FROM ObjectID O_ID

     INNER JOIN

       ObjType OT ON OT.OBJECT_TYPE = O_ID.OBJECT_TYPE

     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_ACCESS_CONTROL AC

       ON O_ID.OBJECT_ID = AC.OBJECT_ID

   ),

 --  Figure out what the relationship is between users and groups

 --    All users in all groups

 SuperSet AS

   (

     SELECT

       O1.OBJECT_NAME AS "Group",

       O2.OBJECT_NAME AS "User"

     FROM HYP_PLN_RestoreApp.dbo.HSP_USERSINGROUP G

     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O1

       ON G.GROUP_ID = O1.OBJECT_ID

     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O2

       ON G.USER_ID = O2.OBJECT_ID  

   ),

 --  Just the users in the specified group(s)

 --  NB -- This could be expanded to multiple groups if need be

 SpecificGroup AS

   (

     SELECT

       O1.OBJECT_NAME AS "Group",

       O2.OBJECT_NAME AS "User"

     FROM HYP_PLN_RestoreApp.dbo.HSP_USERSINGROUP G

     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O1

       ON G.GROUP_ID = O1.OBJECT_ID

     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O2

       ON G.USER_ID = O2.OBJECT_ID  

     --WHERE

     --  O1.OBJECT_NAME LIKE '%EMEA'

   ),

 -- Use an IN statement with a subquery to limit the users from the SuperSet CTE to the users in the

 -- SpecificGroup CTE

 UsersInGroups AS

   (

     SELECT

       S.*

     FROM SuperSet S

     WHERE S."User" IN (SELECT "User" FROM SpecificGroup)      

   ),

 -- Use an IN statement with a subquery against a DISTINCT to limit the groups from the SuperSet

 -- CTE.  This will return all of the inherited groups.    

 GroupsInGroups AS

    (

        SELECT DISTINCT

            S."Group"

        FROM SuperSet S

        WHERE S."User" IN (SELECT "USER" FROM SpecificGroup)

    ),

 -- Get the security that is specifically assigned to users

 -- Use an IN statement with a subquery to limit the users from the FinalCTE CTE to the users in the

 -- SpecificGroup CTE

 UserDefinedSecurity AS

 (

   SELECT

       F."Type",

       F."Object",

       F."Security Type",

       F."User/Group Name",

       --'User-assigned' AS "Parent Group",

       F."Read/Write",

       F."Hierarchy function"

   FROM FinalCTE F

   WHERE

        "Security Type" = 'User'

        AND F."User/Group Name" IN (SELECT "USER" FROM SpecificGroup)

 ),

 --  Get the security that is specifically assigned to groups

 --  The join between the CTE UsersInGroups and FinalCTE is the key to implicit security

 GroupDefinedSecurity AS

 (

   SELECT

       F."Type",

       F."Object",

       F."Security Type",

       G."Group" AS "User/Group Name",

       --F."User/Group Name" AS "Parent Group",

       F."Read/Write",

       F."Hierarchy function"

   FROM FinalCTE F

   INNER JOIN GroupsInGroups G

     ON G."Group" = F."User/Group Name"

 ),

 --  UNION the explicit to the user and the implicit via a group security

 UserAndGroupDefinedSecurity AS

 (

   SELECT

     *

   FROM UserDefinedSecurity  

   UNION

   SELECT * FROM GroupDefinedSecurity

 ),

 PlanningSecurity AS  

 (--  Now report out however you like

 SELECT

   "User/Group Name",

   "Security Type",

   --"Parent Group",

   "Type",

   "Object",

   "Read/Write",

   "Hierarchy function"

 FROM UserAndGroupDefinedSecurity

 )

--  Join the two query threads on user/group

SELECT

    DS.Parent AS 'SS Parent'

    , DS.Child AS 'SS Child'

    , DS.PROJECT_NAME AS 'SS Project'

    , DS.APP_DISPLAY_NAME AS 'SS App'

    , DS.NAME AS 'SS Role'

    , DS.DESCRIPTION AS 'SS Role Desc'

    , DS.PRODUCT_NAME AS 'SS Product'

    , PS.[Security Type] AS 'PLN Security Type'

    , PS.Type AS 'PLN Object'

    , PS.[Read/Write] AS 'PLN R/W'

    , PS.[Hierarchy function] AS 'PLN Hierarchy'

FROM DirectSharedServicesSecurity DS

LEFT OUTER JOIN PlanningSecurity PS

    ON DS.Child = PS."User/Group Name"

That’s a fair bit of code, about 350 lines.  In the real world, I would probably stick the Shared Services and Planning sides of the query into views and then join across the views to get the results to make things a bit more readable but that is an exercise for you, Gentle Reader, if you are so inclined.

Note that I used a LEFT OUTER JOIN to ensure that a lack of Planning security didn’t suppress Shared Services provisioning/roles.

The time

I suspect I will never know enough SQL to actually tune my queries.  The good news is that the set of what gets queried out of the EPM repositories is so small that efficiency is not much of an issue.  As an example, this query took exactly zero seconds to return the result:

I took a look at SQL Profiler to see if I could get more detail.  Instantaneous isn’t totally correct, but pretty darn close:
 

Based on the time, I think that 13 milliseconds beat 60 minutes quite handily.  :)

What have we learnt?

A couple of things, in order of increasing importance:

1. SQL is really cool.
2. Simplicity (yes, the query is long, but this is simpler than LCM, XML, and MS Access) trumps complexity.
3. There are few absolutes when it comes to code.  Or perhaps what I mean is that it is dangerous to state (or to be fair, imply) that something is impossible, or even that one approach is “better” than another.  I for one have been shown many times that my impossible is someone else’s common practice.  Sometimes I laugh at my ignorance, other times I wince, but I always come away from the experience that little bit wiser.  Heaven knows I need the help.
4. I better not really believe that what I did is “the best”, cf. the above point.  At best it is an approach that works quickly and was easy to write.  Pride goeth before the fall and all that.

So with those points and caveats, did I meet the challenge I set for myself?  Yes, I was able to use publicly available (albeit unsupported) code (yeah, my own, but still it is public) and join together security information from multiple EPM products with really very little effort.  Huzzah for me, etc., and I do enjoy that all too rare pat on the back even if I gave it to myself.

But that’s mere ego and thus not important.  What is really important is that you too, Gentle Reader, can combine the queries and code that I and others (Celvin, I am looking at you) are putting out there.  I really was very pleasantly surprised that it was actually so easy.  If you’re not taking advantage of all of these great resources…well, you need to interview your brains.

Be seeing you.