Little known ESSCMDQ feature – FILLDB

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 11.1.2.4 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 11.1.2.4 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.

startEsscmdq Script

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 11.1.2.4 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.

FILLDB command

Just to prove that it actually works, this cube was blank when I started and here are the statistics:

FILLDB block density

  FILLDB PAG size

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 11.1.2.4 as well as talk about the road map beyond 11.1.2.4.

 

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.

Advertisements

2 comments

  1. Hey Robert, stumbled upon your blog. Saw in your bio that you might know a good bit about Smart View automation with VBA….exactly what I am looking to do. If you have time, I would love it if you could guide me to some good resources!

    Thanks!
    Justin N

    1. Justin,

      If you are an ODTUG member, even the Associate membership that’s free, you can log into their site and take a look at past presentations from the KScope conference. I did a presentation on Smart View automation with VBA back in 2010. In the audience of that presentation was a guy named Gary Adashek who works at GE now. Gary has done presentations on his Smart View automations the last two or three years and he has taken the automations much farther than I ever did. I still dabble here and there as clients request help with converting, but I think you can probably find everything you need on the ODTUG site.

      Cheers,
      Robert

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s