Occasionally, we have a need to load up a database with some fake data. This could be a copy of a customer application or perhaps a sample application that we really want to load up with some random data for calculation testing.
As most people know, Oracle has a special version of ESSCMD known as ESSCMDQ downloadable on their site here. Oracle even has an 220.127.116.11 version posted for those of us playing with the latest version. As of right now, the only documentation available on ESSCMDQ relates to the ASO compaction process blogged about by several of my colleagues in the EPM space.
Edward Roske mentioned this command to me a couple of years ago when we were doing some load testing of a customer’s application, but the only thing I could find about it on Google was a single Network54 post from 2004. Thank you Ray Dhiilon, wherever you are for recording the only existing documentation on FILLDB until now. I recently saw that Jason Jones created a utility called Cube Data that fills random data into Essbase and it jogged my memory on this command. While it was fresh in my mind, I figured that I would share it.
Setting up ESSCMDQ
I downloaded the zip file for 64-bit ESSCMDQ version 18.104.22.168 from Oracle’s site. I copied the ESSCMDQ.exe file into my Essbase Server directory at C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin. In the C:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin directory, I copied the startEsscmd.bat file to startEsscmdq.bat and edited the script to call ESSCMDQ.exe instead of ESSCMD.exe.
I launched ESSCMDQ by double-clicking on my .bat file. I am not a huge fan of ESSCMD, so I don’t know the login steps very well. I figured there are probably a lot of people like me that have invested time into learning MaxL, so don’t really use ESSCMD very frequently or ever. The commands are pretty easy to make this work and I show how to run this in my command line output below.
FILLDB loads random data into level0 data blocks. There are three parameters, if no parameters are specified, ESSCMDQ will fill all cells of the database.
FILLDB [size density distance]
- size – the desired combined size of the essn.pag files in Megabytes (MB)
- density – how full your blocks will be as a percent
- distance – distance between blocks that get filled (determines the sparsity of the database)
FILLDB 1024 5 3 = Fill the database up to 1GB, 5% block density, fill every third block.
FILLDB = Fill the entire database, 100% block density, fill every block.
The distance parameter is interesting. The way it appears to work is that it takes the level0 list of your first sparse dimension and will fill the top level0 member, then skip the desired number of level0 members and fill in the next one.
I logged into my 22.214.171.124 sandbox environment and selected a Hybrid Aggregation database just to see if it would work. I am pleased to report that it does. That’s not terribly surprising since Hybrid Aggregation databases still use blocks and index and page files. Combine that with the fact that FILLDB works on level0 cells and it’s really a no-brainer; nevertheless, I wanted to make sure.
Just to prove that it actually works, this cube was blank when I started and here are the statistics:
Obviously, this is a BSO command, as we are talking about filling blocks. You can run it against an ASO application, but the prompt just goes away while ESSCMDQ tries to figure it out. I let it run for a few minutes and then it came back with a 1042017 Essbase error. So, BSO only for this command.
Also, while I’m blogging, I just wanted to put in a quick plug for the OAUG Hyperion SIG meeting this Sunday, April 12. If you are going to be at COLLABORATE, please join me at the SIG meeting. We have some great speakers lined up to cover what’s new in 126.96.36.199 as well as talk about the road map beyond 188.8.131.52.
EDIT: It’s probably worth mentioning that this is undocumented and unsupported, so don’t call Oracle Support if you are having issues. You are on your own, and of course this may be removed at any time as well. So use it if you can, if not, take a look at Jason Jones’s utilities.