Welll, I have not figured out what to call Essbase now when it is generically in either on Oracle Analytics Cloud (AOC) or in Oracle Cloud infrastructure(OCI), so for the simplicity in this article, I'm referring to it as Cloud Essbase. If you have a better suggestion, please let me know.
Now that I have the disclaimer out of the way, let me describe what I'm trying to accomplish. I have given presentations with on-premises Essbase on how you can use substitution variables either as the header or as a column in data load rules to make them more dynamic. Today, I was asked by a colleague how she could do that in "The cloud".
If you want to do it as a value for the entire load rule as a header it is pretty easy.
Open the load rule and select the source button. Then in the "Header" box, enter in your substitution variable. That is all there is to it.
Now if you want it as a column header, it is a bit more complicated. In EAS we could type in the column headers. But in the cloud, it is a drop down selector that we can't type into. BUT, the cloud gives us something that on-premises never had. We can export the load rule into a JSON format, edit it and reload it. So how do we do this amazing thing?
First I created the load rule to be what I wanted. I selected the dimension/member I wanted where I want to use a substitution variable. and saved the load rule. In may case, I want to replace the "Jan" column with the substitution variable &curmonth
Once I've saved the load rule, tested it to make sure it worked without issues with the hard coded value.
Next from the script tab for the database I selected Rules and the load rule I just created. From the hamburger at the right I selected Export. It is important that you do it from this screen and not the files screen. From here you will get the JSON format, from the files, we get the binary.
When I selected to export, I got the choice of opening with a program or saving the file. I chose to open it with Notepad ++ but could easily have saved it as well.
In the JSON file I did a search for Jan and got
It is important to note that JAN is enclosed in "\" This is to make sure the member name is in quotes by escaping the inner quote. (the \") we will not need these, so I replaced \"Jan\" with my substitution variable &CurMonth. I still left the outer quotes
I saved the file. In doing so, I renamed it so I could keep the original load rule. I then uploaded it FROM the scripts tab. Again if you do it from the files tab, bad things happen as the files tab does not know what to do with a JSON file.
Once uploaded. we can open the rule and see the it is now using the substitution variable.
For a complete test, I cleared the data in the cube and loaded the original rule that had Jan hard coded I got what I expected when I retrieved in Smart Vie. Just the Jan data
Next I set the Substitution variable to May and ran my new rule , note I did not clear the cube first.
As you can see the May has now been populated and it match Jan since I used the same file for both months
There you have it an easy way to use substitution variables to control loading data into the cube. I have to say, the ability to get at the JSON file opens a lot of options for us in making changes to load rules. I am looking forward to exploring other uses.
Posts
