Summer Getaways Without the Traffic

Watching the Friday afternoon traffic crawling up I-95, I was struck with a thought:  there has got to be a better way to get to the Cape and Islands than playing Mad Max on the bumper-to-bumper interstate for endless hours.  

Six million people visit the Cape each year, four million of them in the summer and fall.  And on most summer weekends they all seem to be directly in front of you on the roads, right?  There’s got to be a better way.

There used to be a direct Amtrak train from Washington DC to Hyannis, stopping at major stations in Connecticut. “The Cape Codder” ran from 1985 to 1996 but only carried 1200 passengers in its final season due to its 5+ hour running time from Stamford.  And once you detrained in downtown Hyannis you were still miles from the beach.

There’s still express bus service running from New Haven to Hyannis but it’s no faster (though a lot cheaper at $42 one way). (All fares quoted are based on a June Friday departure)

The newest option is high speed ferry.  The Seastreak runs from East 35^th Street in

Manhattan to Martha’s Vineyard ($165 one way) and Nantucket ($175 one way) in five and six hours respectively.  Nice boat, but still a long ride even with the flat screen TV’s, full bar and comfy seats.  .

Seastreak also operates high speed from New York to Port Jefferson LI where you can connect with the Hampton Jitney (bus) to that tony beach community in 3 ½ hours.  Or you can chug across to PJ from Bridgeport on the traditional ferry, and then hop the bus.

If money is no object, you could always fly.  JetBlue flies non-stop in an hour from JFK to Hyannis or the Vineyard for as little as $179 one way.

Or there’s Cape Airwhose Cessnas will whisk you from White Plains to the islands in just over an hour for $565 one way.  Faster and more exclusive are charters from firms like Trade Windswhose fleet includes private jets.

Really?  Are we this desperate to decamp to the sun and surf when we have perfectly lovely beaches right here in Connecticut?

State-run Sherwood Island is right off I-95 in Westport and bikeable from the Greens Farms stop on Metro-North.  And Bridgeport’s city-owned Pleasure Beach reopens in June after a two decade absence.  The free water taxi runs seven days a week.  

Another alternative is the Jersey shore.  Catch Amtrak to Newark (or Metro-North to Grand Central and switch to Penn Station) and NJ Transit’s “Coast Line” will easily get you to all the shore communities from Long Branch to Bay Head. 

So wherever you’re heading for fun and sun this summer, don’t be a slave to bumper-to-bumper traffic.  Try transit!

I don’t ordinarily write about conferences other than Kscope, but when I do…

…I write about the Rittman Mead BI Forum 2015

Tim German and I just spoke at Rittman Mead’s annual BI Forum.  Alas, we didn’t go to the one in Brighton (I attended the Comshare EMEA conference there in 1992 but I cannot find any links to it – I think it was in the hotel the IRA tried to assassinate Margaret Thatcher in although I note that RM’s conference was in a different hotel).  Instead we went to the one in Atlanta (come think of it, I believe I attended a Comshare conference there as well – maybe 1991?  1993?  I can’t remember for the life of me.) and it was absolutely brilliant.

Why?  I find technology just fascinating (erm, you would imagine that based on this blog) and the presented technology at this level is all new to me.  Fascinating.

NB – For those of you who think this is a sycophantic recounting of their conference, it isn’t.  I’m pretty hard to impress and I was just blown away by the conference.  Think of this post as enthusiasm and well deserved at that.

NB #2 – Mark went out of his way to let the two token Essbase guys feel at home.  Many thanks, Mark.

Day by day

Day 0

The conference started out with a master class on Oracle Big Data.  We’ve all heard a lot of hype and hyperbole about Big Data.  I have to say that after watching Jordan Meyer and Mark present on it, I think Big Data might just deserve it all.  I also have to admit this is the first time I’ve had a bird’s eye view of R, EDA, Oracle Big Data SQL, and I-don’t-know-how-many-other-techologies, but the kinds of data, the statistical analyses, and reporting just blew me away, as did the rest of the conference.

Day 1 & 2

Rather than try to give my impressions of each one of the sessions, which would be difficult ‘cos I was mostly completely at sea, the conference covered subjects and technologies that are not in the toolkit of at least this Essbase hacker.  I’ll list it and try to give you a feel of my overall impression of the conference.

The list

• Brian MacDonald or Chris Lynskey, Oracle Corporation : “Looking Ahead to Oracle BI 12c and Visual Analyzer”
• Robin Moffatt, Rittman Mead : “Smarter Regression Testing for OBIEE”
• Bill Creekbaum and Gabby Rubin, Oracle Corporation : “Solid Standing for Analytics in the Cloud”
• Hasso Schaap, Qualogy : “Developing strategic analytics applications on OBICS PaaS”
• Tim German / Cameron Lackpour, Qubix / CLSolve : “Hybrid Mode – An Essbase Revolution”
• Kevin McGinley, Red Pill Analytics, “Agile BI Applications: A Case Study”
• Stewart Bryson, Red Pill Analytics, “Supercharge BI Delivery with Continuous Integration”
• Ryan Stark or Chris Lynskey, Oracle Corporation : “Big Data Discovery”
• Andy Rocha & Pete Tamisin, Rittman Mead : “OBIEE Can Help You Achieve Your GOOOOOOOOOALS!”
• Christian Screen, Sierra-Cedar : “10 Tenants for Making Your Oracle BI Applications Project Succeed Like a Boss”
• Sumit Sarkar, Progress Software : “Make sense of NoSQL data using OBIEE”

All of the sessions were good (somehow I think even the session Tim and I presented on Hybrid was at least adequate – sorry Tim), but the ones that I found most fascinating were the conceptual ones that could apply to EPM as well.  Robin’s presentation in particular really hit home as it captured the kind of structure and rigor that we should be bringing to testing in EPM projects.  Stewart also almost made me believe in Agile, which is really quite an accomplishment.  Even the more BI-specific technologies and approaches were absolutely fascinating.

A data visualization bake off allowed BI geeks to take a known dataset around public school classroom projects that need funding and presented their solution at the conference.  Sorry, I can’t tell you who won as I was continuing my OMG-this-is-so-f’ing-cool bit of shock and awe, but both of the solutions were really great.

There was also a debate on self-service BI vs. the traditional IT-oriented and controlled top down BI.  Oddly, there was quite a bit of discussion about buffets vs. Michelin three star restaurants (it was a metaphor for the different kinds of BI but it all left me a bit puckish).  I have to say that I was on the side of the former – this is where Essbase got its start and where my heart with the product still is.  Empowering people to take control of their data is one of the things that makes Essbase so great.  Self-service won.  :)

In action

If you want to download the presentations have a look here.

Want to see us in action?  Check us out on Flickr.

One other thing

This sounds kind of ridiculous, but the food at this conference was just fantastic.  Perhaps that’s down to the hotel, or perhaps the small size (60-ish) which allowed a different kind of buffet

Wrapping it all up

Will I go again?  If I can afford it (remember, yr. obt. svt. funds himself) yes, I will.  It was that good.

Thanks again to RM for inviting Tim and I to present and hopefully we didn’t let the Essbase side down.

Be seeing you.

EPM documentation – The little things mean a lot

The problem

If you’ve ever needed to send a link to a particular subject within the Essbase Technical Reference, henceforth called Tech Ref ‘cos I am lazy), you may have had a frustrating experience.  Why?

Copying and pasting of said links don’t work

What do I mean?  Watch and (not) marvel.

Filter MDX function documentation

Yr. obt. svt.’s attempt to copy and paste

The copy

The paste

The url copied is http://docs.oracle.com/cd/E57185_01/epm.1112/essbase_tech_ref/frameset.htm?launch.html.  That ain’t right.

Alas and alack

We’re right back where we started from, as you can see.

Getting round this

I whined (whinged for you Commonwealth readers) about this to the EPM Information Development team and they, perhaps in an effort get me to shut up or perhaps in an effort to help the general EPM community out – I prefer to think of the latter but the former is likely the case – showed me how to get to the right place.

It’s just this little chromium switch

Instead of copying the frame url, instead look at the frame’s information.  Here are the steps in Firefox.

Here’s looking at you, kid

Grab it and go

This copy is going to result in the url http://docs.oracle.com/cd/E57185_01/epm.1112/essbase_tech_ref/mdx_filter.html.  Could this be right?

Oh yes it is

Isn’t that nice?

So is this important?

If you share links from EPM documentation, yes, absolutely.  I know it seems trivial but I think each and every one of we Essbase Hackers have been frustrated by this.  

Again, my thanks to the great people at the EPM documentation team for showing how this works.  If you’re not taking advantage of their offerings beyond the base documentation page, you really ought to.

Here they are on YouTube, Google+, Twitter, Facebook, and LinkedIn.  You can’t say they aren’t trying to reach out to us.   

Be seeing you.

Personalized Airfares

Last time we were talking about mass transit systems collecting fares on the honor system.  This time, something completely different.  But to understand it, consider this analogy:

Let’s say you’re in a store shopping for a commodity.  You and another shopper each select one of the same items at the same time and head for the cashier.  But before you can pay, the cashier asks for your name and some identification.

You’re from Darien or New Canaan and a new customer.  The other shopper is from Hartford, but a regular at the store.  The cashier plugs in that info and you’re told that your purchase will cost 10% more than the other shopper’s.

What?  Well, welcome to the world of “personalized prices”.

You may not realize it, but this happens all the time when you’re buying gasoline, thanks to “zone pricing” where gas stations charge higher prices in more affluent communities, not just in Connecticut but nationwide.

And giving discounts to “best customers” is also quite common.  Monthly pass holders on Metro-North pay only half of what their peak fares would cost purchased separately.

But never before have these concepts been combined in some secret algorithm to apply to purchasing airline tickets… until now.

IATA, the International Air Transport Authority, has petitioned the US to allow its 250 members to capture and use new kinds of personal information about would-be flyers before quoting them a fare.

Most frightening of these could be some sort of “means test”.  In other words, as in a bazaar when the salesman sizes you up and asks “how much do you want to pay?” the airline would figure out that answer itself based on your zip code and flying patterns. 

So if you live in a rich town, they’ll assume you can pay more and quote you higher fares while folks in poorer communities are offered discounts.

To his credit, our US Senator Richard Blumenthal along with others in Washington are questioning the fairness, if not the legality, of all this.  They’ve written to the US Transportation Department asking if this plan isn’t hurting more consumers than its helping.

Airline flights have never been fuller.  Because they’ve shrunk their fleets and customer demand has come back, almost 80% of all seats are full on domestic and international flights.

Gone are the glory days of my mis-spent youth when students could fly “stand-by” hoping

for an empty seat just before departure in return for a 50% discount. That only worked because planes were empty.

Today the incentive to get a cheap seat is to book early, weeks in advance, not to show up at the last minute hoping to find an empty seat.  There are none.

But to price the same seat bought at the same time at two different prices simply because of shoppers’ demographics seems unethical if not illegal. 

Airlines are not allowed to discriminate on the basis of race, gender, national origin.  So why would we allow them to, in effect, look at our credit report before quoting us a fare?

Full version of my UK Cyclist Magazine Giro Preview

The season of spring is an action packed smorgasbord of professional cycling. It's a fair argument to say that no other 3month block of the season will test the riders skills and physical condition the way the month of spring seem to do. March kick things of with the race between the seas with tirreno addriatico and the optimistically named race to the sun with Paris nice although you would be hard pressed to find to many examples of sunshine greeting the riders in nice annually! Also during march sees belgium come to life and the hard man come out to play on the cobbles, crosswinds, and what otter feels lie racing on footpaths not to mention the array of climatic conditions that are also thrown the peletons way. Perhaps fittingly in the final month of spring lies a race that has a history of testing all the skill set required to survive during this period with the giro! There is an endless archive of iconic images which encapsulate the uniqueness of the race around Italy, again seeming to serve as one final exam for a peleton that's spent the unpredictable months of racing in spring to prepare for. 3 weeks of racing that can quite literally through up a completely different climate and terrain on each and every single stage to assess how well the warriors of road racing have done the homework or hardwork in this case to be prepared for anything and everything that the giro can through up at you. Like with every great bike race it's logical that the most complete rider will rise to the top and emerge victorious but perhaps this is never more evident than at the giro.



What makes the giro special

The initial part of the season is a build to the classics for the hard men and the giro for gc chargers. To be at your best for the giro means negotiating your way through the most unpredictable and stressful period of the racing season. This therefore means that simply arriving at the start line of the giro in your absolute optimal health and best condition is a major challenge in itself, let alone worrying about the 3 weeks of torture that lies ahead. I can obviously only speak with conviction on the 2 giro's that I've competed in in 2010 and 2013. Both editions of the race were equally different and equally brutal in there own unique way. The lead up to both events is also something special. Often as early as the races in february and march you'll start to see the same faces of the races your using as preparations, another 200 guys on a similar schedule that will culminate in the giro. So before you have even made it to the startling you have already formed a bit of an extended family of fellow giro competitors in the build up, in the end it's not just the 3 weeks you'll spend with alot of all these guys but more like 3 months. Why is this important to know?? Well with the challenges that the giro throughs up with it's climate and terrain and you suffer as badly as you do at times, it makes it's far more manageable when your around a group of guys whom you know know and appreciate what your going though and that solidarity within the peleton can often be critical in helping you deal with the challenges the big lap of Italy throughs your way.





The way the race is played out-



Due to the timing of the giro it takes an extremely resilient and all rounded champion to pull on the pink jersey at the end of 3 weeks of racing. The race can be broken down into 3 stages, First week, second week and 3rd week. Week 1 is the stressful week. The sprinter or bunch sprint friendly stages result in small gaps on GC and alot of riders within striking distance of a day in the prized maglia rosa! With just one day wearing such a prize having the potential to shape the entire career of a rider it's logical that everyone is a little on edge due to the high stakes. With nervous anxious riders comes a nervous anxious peleton and that sadly tends to greatly increase the probability of nasty crashes which the consequences can be equally consequential for those that crash or suffer misfortune as those whom benefit from the crashes taking place and are the fortunate ones. It will ruin a season for some and define a career for others, such are the highs and lows of sickness, injury or a successful daring move that nets that great result. You truly start every stage in the first week of the giro knowing that one thing is guaranteed, that anything and everything is possible of that day for each and every single rider in the peleton.



While I have many wonderful memories from my days racing in the giro, one particular stage in the 2010 edition sums up perfectly my impressions of the race. Stage 11, 262 from Lucera to L'aquila was the longest stage of the 2010 event and came close to the end of what had been a brutal 2nd week. That year the race began in holland which meant that after just 3 days of racing when we returned to Italy we had taken our first rest day. This meant that a haul of 10 straight stages were rattled of before the 2nd rest day would be arrive. Stage 11 came after 7 straight of the most unpredictable and taxing days of racing I've endured. We had searing heat to contend with, almost monsoonal rain falls the next day, sub zero foggy mountain stages with zero visibility the next and to top everything off the gravel or Strada bianchi stage was held in torrential rain which would come as not surprise turned the stage into cycling equivalent of a mud run! That day in the mud was that chaotic that in the end the dismal suffering could only be replaced by the humor of the citation us riders found ourselves in as we plowed our way through the mud! Not surprisingly and fittingly the stage was won by the regarded as the most complete rider of his generation, the great Australian Cadel Evans whom was able to draw of his 20years of professional bike riding experience to cross the line first on a day where guts and determination counted as much for exceptional bike skills, two things which cadel has no shortage off! 



Anyways back to stage 11 to L'Aquila and it's a day of racing I'll never forget. The longest stage in the race is always to be feared. With 7hrs of racing ahead the entire peleton was hoping for as straightforward a day as possible. Straightforward in as much as a nice breakaway that the gc teams could easily manage and the course could be covered in the must civilized possible! Yeah right!! The stage started out in beautiful sunshine and warm condition which was one blessing as the flag dropped in Lucera, that however was the last time there would be anything civilized about the stage. Around 10pm into the stage the attacks were coming thick and fast as we entered a dark tunnel. I was at the front staying alert to control any dangerous attacks for our team leader Michele Scarponi as we entered the tunnel, well 2nd wheel to be precise. I was thinking gee we are hammering here through this tunnel and as we came out the other side and barreled down the other side I noticed we were really hammering. It was a wide open big straight road and I was full gas following the wheel ahead of me. I just assumed all the bunch was there but all of a sudden team sky and movistar stormed to the front and drilled it! At this point I looked back and noticed there were around 50 of us and daylight behind. In the group were some of the biggest gc threats for the race in Bradley Wiggins, Carlos Sastre, David Arroyo, and most excitingly for me that day fellow Tasmanian Richie Porte whom at days end became the biggest name in the international Cycling world when he pulled on the maglia rosa. There was a day that not that richie wasn't already destined for stardom but that day just sped up that process for him which was great to see. So back tot he race and the gap quickly started to rise and the big teams of there leaders drove home the advantage they were being given. I was told by my androni ds gianni savio to sit tight as many team behind were hitting the panic button and chasing hard. He said my chasing energy would be needed later in the stage. So I sat tight and comfortable in the breakaway in the luxurious position of not having to do any work. I even started dreaming of go for a big result myself as I would have a chance to arrive at the finish as fresh possible and spent many minutes studying the finish profile plotting my attack. As were rolled along the road toward L'aquila we also slowly began to rise in altitude, it was one of those days that gradually rose in elevation and with it the temperature dropped and the rain started to fall. S now the warm sunshine was gone, we were on the longest stage of the race and it was pouring rain and freezing cold. I kept checkin in with the team about going back to start pulling but was told to sit tight, all was under control. Yeah right! Sure enough with 100km to and we were 22minutes up the road savio finally called my teammate Rubens Bertagliati and myself back. The only problem was now that we were so far ahead and it was so cold that riding slowly or waiting on the side of the road as I would have got hypothermia! So did the only logical thing, I turned around and rode backwards with rubens and Brent Bookwalker of BMC who had also been called back to help his team captain Cadel Evans. We rode about 3km back over the terrain we had just covered so now turned the stage into a 270km day for us, would have looked interesting on Strava that file!! Anyways I couldn't believe what I saw when what was left of the peleton arrived. The brutality of the chase had taken it's toll and now just 20riders remained in the peleton! All the biggest names were there, all with the same desperate look of needing help on there faces as rubens, Brent and I arrived. The group contained The eventual winner Ivan basso and his team mate Vincenso Nibali, Scarponi, the man in pink Vino, Cadel, pozzato, paolini, Pablo lastras, and some other very strong dudes, put it this way at the time I recognized each and everyone of them! 



Anyways there was no time for pondering the situation it was desperate times and time to get to work. For the next 60km we pulled at pur absolute max and when I did my final pull before being catapulted out the back the gap was down to 10minutes. One conciliation was even though I was offering the chase had been solid as now only around 10 of the the strong men remaind in this peleton! It was crazy, there were 50 up the road, 10 in the peleton, and 140 guys miles back down the road!! Anyways as I was unceremoniously dumped out the back of the field I was totally spent and hadn't prepared myself for what was to hit me next. I was completely out of fuel, I was now freezing cold as my body had no energy to keep it warm, and o still had 40km to ride and I was alone! My teeth started chattering and I was starting the face the reality of not being able to keep my body moving. Then almost like a knight in shinning armor, Pablo Lastras appeared out of the mist and saw me in distress. He had seen the work I'd done and been dropped in the process, that alone will earn you respect with your peers. What he did next saved my day ensured my survival in the 2010 giro. He put his hand on my back and simply said, tranquillo Cameron, relax! He simply got me to the finish. He pushed me along and kept me talking, he even unwrapped power bars and opened gels for me, a simply thing my border lining frostbited hands hands a d teeth could not mechanically perform. He was in every sense of the word a life saver for me that day and eventually some 40minutes after the winner petrov had crossed the line and 30minutes behind the group of favorites I'd been helping some 40km's before hand! I was absolutely in peaces! 



This stage for me really signified everything the giro stand for me. We had the 4 seasons in the one day. We had a ridiculously long hard stage at a point in the race when we were already on our knees as a peleton. The stage panned out in the exact opposite way anyone in the peleton predicted before it kicked off that morning. A riders career was catapulted into super stardom when richie porte pulled on the maglia rosa. The eventual champion Ivan boss showed his class and experience and while lost major time knew that staying within his limits would mean he would have the batteries left to put all the field to the sword in the final torturous week of racing in the dolomite's. And off course that solidarity I'd talked about wigging the group of riders that tackles the giro was evident with Pablo lastras whom rode for another team ensured that I made it to the finish that stage. Oh and also my day all in all was as unpredictable as ever which a giro stage can only through your way, it still remains the only time in my racing career I've turned around and ridden the wrong way back down the race course!! All in all was just a magically racing giro day that I and I'm sure every rider whom raced the stage or watched the stage will never ever forget.





P.s. I wrote this 6 weeks ago so it's been interesting following the lead up to the big lap of Italy unfold for the big favorites! Pretty exciting that it would appear the Tasmanian richie Porte is right on track for taking home pink!!

Stupid Programming Trick No. 24 – Sub Vars to tables via Calc Scripts

Introduction

This is another in a series of good ideas that have been shamelessly stolen inspired by Martin Slack.  I’m learning an awful lot from Martin; I’m not entirely sure that he is from me.  Such are the fortunes of war.

The problem

I needed a way to get the current month value from an Essbase substitution variable so I could do all sorts of weird and wonderful data integration things with it.  How to do it?

The idea that springs to mind for most of us (or at least yr. obt. svt.) is some way of getting it out from MaxL using the display variable syntax, piping it to a log file, and then face the somewhat unpleasant task of reading in a text file, parsing it via some language (VBScript, Perl, Powershell, etc;) and then from that getting it into a table.  

Ugh.

Stolen glory

Martin said something to the effect of, “What if you could just get it from a calc script?”  A calc script?  Hmmm.  Calc scripts, even using DATAEXPORT, write data, not metadata.  Or do they?

The Stupid Trick its own self

DATAEXPORT is an eponymous command – it exports data.  But what if there was no data to export?  Even more useless, right?  Here’s the Stupid Trick bit:  this is exactly what is needed – no data is fine because we’re going after metadata.  So how does metadata come from data?  And how does it get into a relational table?  Read on Gentle Reader and all will be revealed.

It’s all in your head in four parts

Part the first – the outline

Using my VFEDITWWW, I’ve added members that, at least in this blog, can never have data.  To wit, I have added the members No Measures, No Product, and No Market.  You will likely have different requirements unless you are The Beverage Company.

Part the second – the calc script

This member combination now gets exported to disk.  Ah, you say, yet another worthless bit of programming drivel from Cameron.  You’re wrong, for once, to think that.

Looking at the code below, note the structure of the export, with the DataExportColHeader option set to  Year.  When that is combined with an export of the Members That Can Never Have Data, look at what gets exported.

Part the third – the output

Brilliant, isn’t it?  And I mention that because the brilliance is all Martin’s.

I now have an output file with just the current month and nothing else.  This is a lot easier than trying to running a MaxL file and then parsing the output.  But how to get that into a relational data store?

Part the fourth – the SQL

The last problem now facing this approach is getting the value of Oct into a relational database.  Again, there are all sorts of complex and sophisticated ways of doing this:  ODI or SSIS spring to mind but there is a significantly easier way to do this, and to even do it within a SQL query.

Say hello to your new friend BULK INSERT

In SQL Server (in Oracle there is a concept called external tables that does much the same thing), there is a command called BULK INSERT.  While it is mostly used for doing just what it sounds like – a bulk insert of lots of data – in fact it can be used to bring any amount of data into a database from a file.  And if it can do that, and there is a file that has the current month, connecting the dots is pretty straightforward.

Here’s what my code looks like:

BULK INSERT Current_Period FROM

    '\\epm11123\C$\Tempdir\Current_Period.txt'

    WITH

    (

        CODEPAGE = 'ACP',

        DATAFILETYPE = 'char',

        FIRSTROW = 1,

        LASTROW = 1,

        FIELDTERMINATOR = '\t',

        ROWTERMINATOR = '\n'

    )

One consideration is that there’s no way to map the columns in the data source to the target.  Instead BULK INSERT is positional, e.g., the first field in the source file is loaded into the first field in the target table.  This is different from INSERT INTO…SELECT FROM syntax which allows an alteration of field order.

I used UNC naming not because I had to – c:\tempdir would have worked as well – but because mapped drives *must* use UNC naming, not a shared drive so it’s best to just get in the habit.   Also, this continues the tradition of Martin + UNC so what’s not to like?

Lastly, I defined the file by specifying the character set, file type, first and last row, the tab field terminator, and lastly DATAEXPORT’s line feed row terminator.  For giggles I threw in a first and last row but really everything other than the CODEPAGE and DATAFILETYPE settings are just a belt for the braces.

What does it look like?

Pretty good, actually, but there is a fly in the soup:

Do you see the issue?  The text file contents is “Oct” and that is exactly what BULK INSERT imports.  

There’s a pretty good chance that the double quotes are superfluous; the REPLACE function ought to take care of that:

There’s only one more issue to fix

This query will be run more than once, and that’s a problem as can be seen below when I ran the query twice:

The issue is that the query as it stands doesn’t clear out the table before inserting the Current_Period.txt file.  My brilliant data integration code expects one and only one record so perhaps it is not all that brilliant and will thus go KABOOM when two records are encountered.  Even worse is that this table could have multiple values, e.g., if this were to be run in both September and October the first record would be Sep which isn’t the current month.  

The solution is to run a TRUNCATE TABLE first, then the BULK INSERT, and finally the SELECT REPLACE command as below:

Now no matter how many times I run this, I get one and only one result.  Huzzah!

So what do we have?

A combination Stupid Trick that pulls out metadata, not data, via DATAEXPORT and then painlessly imports an evaluated Essbase Substitution variable into a table with nary a hint of MaxL script output or complex data import to SQL.  Take this query and stick it into a stored procedure, an ODI variable or procedure, etc.  The sky is the limit with this and it is dead easy.

Thanks again, Martin.  You keep thinking them up, I keep stealing them, and world+dog gets the benefit.  

Be seeing you.