I have the need, the need for speed. This applies to so many things. In my case, I needed to build a number of text lists in Essbase 19c. I did not feel like adding hundreds of entries through the UI. I looked at the REST API and figured I could do it that way, but I wanted something I could automate against the relational source that contained the lists. In playing with the new index based load rules, I remembered the list of items was pretty extensive. So I did the next logical step and went to look at the documentation. As an added bonus to the post, here is an easy link to the documentation for 19c. https://docs.oracle.com/en/database/other-databases/essbase/19.3/books.html . I have a hard time finding it so maybe it will help you.
Anyway. As it turns out, it is not documented how to build a text list (they call it a smart list in the rule) in a load rule. Being the inquisitive sort that I am, I thought I could figure it out. In doing so, maybe, I'll save you all a bunch of time and agony (I crashed the application multiple times figuring this out).
I will say, I am sure I am doing extra things that don't need to be done. but this is what I got to work for me.
First, you have to make sure Typed measures is turned on. To do so, Edit the outline in the UI and click on the properties button. In the dialog that appears make sure Typed Measures is set to TRUE
WARNING Once you do this you can not turn it off. I don;t think it is a big deal, but it might want you to back up the application or do this on a test version first.
Once Typed measures are turned on, click on the Textual Measures Tab (I like that they use different names in different parts of the application for the same thing. The documentation says Text Members, This Textual Members and the load Rule, Smart Lists). At any rate, I digress. Once on the tab, Enter and name for the list and click on Add
It will bring you an initial list
You can change the names for the Missing and out of range members. If you want to manually add entries you can click on the +. This is fine for small lists, but if you have a lot to add it is inefficient.
Once the list is created, Click on Apply (and close unless you have more lists to create).
Once the list(s) is created, you can add text members to the accounts dimension.
I'm adding a District member that will be loaded at the individual Market level. In the Measures dimension (which is tagged as accounts) I added a member called District. Then I inspected the member just added and changed the type to "Text: Districts" If you did not create the list first, the option would not appear. I then applied and closed the inspect dialog. You will notice the District member was changed to never aggregate (^). I then saved the outline.
Now the fun begins. Depending on if you are using a SQL source or a text file the next may or may not apply to you. In may case I was using a SQL source to get my data. so I created a Connection and Datasource in the Source card.
I walked though the tabs and saved the results.
Next I created a index based dimension build rule.
I gave it a nice long name and selected the datasource I had created and clicked Proceed.
(Of course if you are using a file, sel
In the load rule, I first went to Dimensions and added the dimension. Note, I switched cubes and accounts ins now the accounts dimension.
Once I added the dimension I clicked on it and on the advanced tab, changed it to allow property changes. and clicked on OK, to close the dialog.
Next I wend into Source and changed the source to datacource. It populated the SQL statement. If you are using a file you don;t need to do this. Also, I found at times if I reopened the rule, the setting would sometimes revert back to SQL. I closed this dialog after making the changes.
Next I click on the properties button. I expanded out Smart Lists and Click on the Plus (+) sign.
I added the name of the ALREADY EXISTING Text list. I set the start number to 1 and it to increment the list by 1. I had originally wanted to supply the list IDs, but could not get it to work. Since I already had the Missing and out of range members defined, I ignored these.
Here is where I had to play around a bit to get the rule to work. You will notice I have a parent and child column in the rule. My SQL statement hard coded these, just I just needed to get something there. I set the first column to Account ->Parent, the second to Account-> Child The third column has my actual list. I set it to "Smart List Text" and as child (This is why I needed the first two columns without them, I could not set the column to child.
Once I had set the columns up, I went into each one of them and changed the Smart list to my text list name. (you have to type it in). 
This may sound counter intuitive and it is, but next I ignored the first two columns (Parent and Child) If I did not do this when I ran the build process I got the following error
I saved the rule and ran it as a regular dimension build rule and it ran without issue. This after my trials to get it to work after many failed attempts including crashing the application multiple times.
As I have said, there are probably things I did that I did not need to and other ways to accomplish this, but this is what worked for me. If you find a better way, let me know and I'll try it out and post an update.
Posts
