Simpler data loads with Visual Basic

In almost any implementation, at some point in the early development stages you need to load data files directly in Essbase using the lovely rule files before you even think of automating this process.

In some cases the data file provided may need a bit of messaging that is a bit too much to handle in Essbase rule files(replacing null values, multiple text replacements in the same column...), I will show a simple example in this post. (I'll be using an old version of Essbase, my 11.1.2.4 cloud based subscription has expired and I need to configure my own).

Take this very simple outline, I have three dimensions in my model.

And my data file looks like this.

I need to insert No wherever there is a blank cell in my data file, why not use Essbase load rules? well you just can't.

You cannot replace a null value in load rules, in this version you cannot even insert a replacement line unlike versions 9.x and later where you can define and add a replacement line but still it won't work.

So you have to do a little bit of massaging in your data file before it is ready for loading. Now if you are likely going to load the file number of times, and there is a good chance the content of the file may increase or decrease, in this case you want to have a VB script to format the file for you and after that you'll simply load the file.

In my source file, I'm assuming column A will always have data, so I can use it in my script to figure out my last row, and then accordingly replace all NULL values for column C from C1 to my last cell.

Lets get on with it, first figure out the last record in the sheet.

Insert a dummy value in the last row for column C (You'll need to repeat this step say you have two columns you want to format and so...)

To make things more visual, this is how your file will look after that code is executed:

Now I'll replace null values with "No".

And my file looks like:

Finally delete the last line inserted.

Now this script will replace any file in the same format regardless if it is has 10 or 100,000 rows, pretty convenient if you ask me.

Now I'll save the formatted file in the folder:

And if you are into nitty-gritties like my self, you can make your life easier by calling a batch file (to run your MaxL, OutlineLoad utility....) and automate the the process from start to end.

Voilà!

May the Cosmos be with you