This an update to the utility referred over here
I had to implement the solution at a client and had to use the tool I developed to import TextLists in Essbase.
Maybe this is also an after effect of the project that I recently completed.
The client(earlier) had a requirement where they needed to present the lineage (hierarchy) and some other metadata information (call it attributes, if you want) to the user in the WebForm, however they don’t want them to navigate through the entire hierarchy (I would call that as vertically traversing).
They were looking at an option of horizontally traversing. (Now that would mean creating around 23 dimensions). The next option to look at was to use Text Measure in Planning and load them against a level 0 product. Ta da they got the horizontal traversing that they are looking for.
I could get away with it as the users were not consuming the datalized (well that is not even a word ;)) metadata. This was in Planning and was easy enough to handle.
Coming back to the problem at hand, client needs to show the store address in FR report and this is a pure Essbase implementation and only option was to use TextLists.
The issue with TextLists is, prepopulating the mapping is not exposed in a load rule or MaxL.
Essbase deals with TextLists in a different way compared to Planning.
You’ve to create the mappings first (where Planning does create the mapping when you load a new text) and that method (prepopulating the mapping) is only available in API.
That’s what this tool does. You can use the jar file to encrypt the data. (I don’t believe in just encrypting just username and password)
Here is how you can encrypt the strings.
Use that encrypted text and pass that to call jar file with -D option
It’s SQL server and to create the file with ID and Address I’m using sqlcmd.
The cool thing that I could do was to remove the headers and the no:of Rows from SQLCMD using a switch and a SQL statment
sqlcmd -S SQLServer -U sqluser -P password -d SQLDatabase -h -1 -Q "set nocount on;select ROW_NUMBER() OVER (ORDER BY Address) AS RowNumber, Address from(SELECT distinct [SADDR1] + ', ' + [SCITY]+', '+ [SSTATE]+' '+[SZIPCODE] as Address FROM Tables) a where Address is not null and left(Address,1) like '%%[a-zA-Z0-9]%%';" -o %BASE_DIR%datafileAddress.txt -s "," -W
-h -1 – removes the headers
set nocount on; – removes the row count information from the end of the file.
ROW_NUMBER gives me the ID (I’m fine with random IDs)
After execution
You can download the utility from here.
