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.