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

@CalcMgrDoubleFromString

I'm back to blogging, at a very slow pace though, I moved countries (continents to be specific) and the past couple of weeks have been very busy.

We all know the dynamic date members we used to create to manipulate dates (Year number, Month number etc...) like the following:

The idea here is to use this dynamic calc member with any intersection and return the year as in number for that specific combination (useful when calculating depreciation..).

This formula works perfectly without any issues what so ever, and it has served me so many times in so many implementations, there was only one thing that bugged me with it, and that is having the first year hard coded, now there is nothing wrong with this kind hard coding if you ask me, since in this case first year will never change.

I used this syntax from Essbase 6.5.7 till System 11.1.2.3, now the interesting thing is the new CDF function @CalcMgrDoubleFromString which was introduced in 11.1.2.3.500, thanks to this lovely CDF our life is a a tiny bit more simpler (not really).

So now we can have instead of the above, this one liner which returns the year number to which you can add 2000 if you want YYYY format or leave as is for YY:

That's for this evening.




May the Cosmos be with you