FIXPARALLEL, DATAEXPORT, Substitution Variables and MaxL

This will be a quick and easy, I faced a dilemma where I needed to export data from one BSO application to ASO application, and the data was as sparse as the cosmos quite literally, I wanted to do the following to get that done:

1. Define my substitution variables (for moving environments, file locations etc.)
2. Export my data using DataExport
3. Clear target data using MaxL
4. Load exported data in ASO application

Very simple steps, I actually made it a bit lighter in here to show the relevant stuff only. Now before I go into more details my main issue was exporting data, so first  tried using the following code:

This works perfectly for well-defined intersections, and small databases, in my case it was taking more than 30 minutes to export the data. So I decided to use FixParallel instead and fasten the export process, so I replaced it with:

It works perfectly, and now the main reason I'm writing this post when you use DataExport with FixParallel you get an export file for every thread you have, so in my previous code I'll end up with 6 files as shown below, the first file is the one I'm getting after executing the first code. (ie no fix parallel)

I'll show how I managed to load the six files without hard coding the files names in MaxL and using substitution variables (old school I know), making this a bit more easy to handle and maintain.

So first I defined the key variables as follows (The bolded lines are the actual values:

1. &MaxLLocationDoubleQuotes  ("C:/Development/Scripts/PushData.mshs")

2. &PrivateKey  ( 12391823791823719823713,1283719283791832 ) This is used for the encrypted MaxL, I'm assuming at this stage you already know this!

3. &AdmUser (324872938472934782347923842937429) Encrypted username

4. &AdmPassword (2348729384445472983742934) Encrypted password

5. &LogLocationSingleQuote ("'C:/Development/Logs/PushData.log'") I'm Including a double quote with the single quote on purposes, will explain later

6. &ExportLocationSingleQuote  ("'C:/Development/Export/Sample_Lv0_T")  AT THE BEGINING I'M INCLUDING A DOUBLE QUOTE & SINGLE QUOTE, BUT AT THE END I'M JUST PASSING A DOUBLE QUOTE

7. &ErrorLocationSingleQuote ("'C:/Development/Logs/PushData.err'")

Now to elaborate on the SingleQuote variables, I'm planning to use RunJava MaxLFunctions to call the MaxL file, in MaxL I need the locations in single quotes, hence I'm wrapping  them with double quotes.

Now comes the sexy bit, which I found out, is the ExportLocation variable, I'll elaborate a bit more on this. My export file location is C:/Development/Export/ and I know I'm going to have 6 files in that folder for every thread in my script, so I wanted to automate this bit without actually referring to every file in my import command in MaxL, so I wrote the following:

Assuming you know how to use this CDF function, as you can see I'm passing the following to the function:
1. MaxL file location in double quotes
2. Private key for encryption
3. Admin user
4. Password
5. Log location in single quotes
6. Export file location in single quotes (Remember only the starting quote is there without the ending quote)
7. Error file location
8 "1.txt'", "2.txt'", "3.txt'", "4.txt'", "5.txt'" and "6.txt'" (For every file exported using FixParallel and I'm ENDING IT WITH A SINGLE QUOTE)

This CDF takes MaxL file location on the server, private key for reading the .mshs file, and admin user and password, now since I didn't pass the server name here any parameter passed after the mandatory ones will start as $3 and so on, this order is important.

So now what I'm going to do in my MaxL is simply add 1.txt', 2.txt'.....6.txt' to my export file location which is static.

This is my MaxL.

Yes I'm telling Essbase to load the file using two parameters, so ...:

import database Sample.Report data from data_file $4$6 using server rules_file 'LoadRule' on error write to $5;

In this case, $4 will give me 'C:/Development/Export/Sample_Lv0_T and $6 will give me 1.txt'  and will end up with the complete file location 'C:/Development/Export/Sample_Lv0_T1.txt'. I'll repeat the same for every thread I ran ($4$7, $4$8,....$4$11) and that's it.


Voila! hope this was fun :)





May the Cosmos be with you!