Drillbridge 1.3.3 hot off the press

Well, here we are again. Birds are singing, the sun is shining, bytes are compiling… and a new Drillbridge point release is out the door. Don’t let the small version bump (from 1.3.2) fool you. This version has seen some significant engineering in the area of performance.

All versions of Drillbridge up to this point (anything 1.3.2 or less) have used a straightforward strategy for getting rows of data into a webpage for the user. In particular, this strategy worked kinda of like this:

Drillbridge: “Hey Mr. Database, can I have these rows that my user wants?”

Database: “Why sure Mr. Drillbridge! Here’s a metric crap ton of rows for you!”

Drillbridge: “Golly, thanks for all the rows of data Mr. Database! I’m going to process through ALL of those and create a formatted version of all of those rows, and then I’m going to hand those off to an HTML template that needs to iterate over all of them and insert them one at a time into the DOM and then pray that the node insertion routine has good performance and…”

Database: “Look kid, you go and play, this bottle of tequila isn’t going to drink itself.”

So, anyway, single page report results have been reworked with performance in mind. In particular, results are processed AS they stream in from the database and are formatted on the fly, then outputted right to the web browser. No more handing off giant table of data (in cases where there were lots of results). Generally speaking, I am seeing page render times that are just slightly above the time it takes to pull the results from the database in the first place.

Drillbridge didn’t really have any problems with smallish result sets but now it pretty easily handles tens of thousands and more.

Additionally, this version of Drillbridge introduces an optional DataTables formatting option as well as server-side paged results. Do read the documentation for implementing results that are paged on the server as there are a couple of steps to set it up. I would consider this feature to be early, but useable. Please let me know if you bump into any issues.

As with before, subsequent releases of Drillbridge will focus on polish, bugfixing, and stability. Future releases will likely slow down as the product matures.

Drillbridge can be found as always in the Saxifrage Systems LLC downloads section. Don’t forget about the support forums, the shiny new Drillbridge wiki, and my upcoming ODTUG Drillbridge webinar on October 28th. It’s more than likely that Drillbridge 1.3.3 will be the version I use for the webinar!

I have tested this version as well as I reasonably can but if you encounter show stopping issues with the database upgrade or usage please let me know and I’ll address that in a quick followup release. So until further notice consider this beta-ish quality.

Happy drilling!

Performance nuances with MaxL data imports with local and server

Some time ago, I reviewed and revamped the MaxL automation for a client. One of the major performance gains I got was actually pretty simple to implement but resulted in a huge performance improvement.

Did you know that the MaxL import data command can can be told whether the file to load is a local data file or a server data file? Check out the MaxL reference here for a quick refresher. See that bold “local” after from? That’s the default, meaning if we omit the keyword altogether, then the MaxL interpreter just assumes it’s a local file.

Imagine that you have an Essbase server, and then a separate machine with the MaxL interpreter. This could be your local workstation or a dedicated automation server. Let’s say that there is a text file on your workstation at C:/Essbase/data.txt. You would craft a MaxL import command to import the local data file named C:/Essbase/data.txt. That’s because the file is local to the MaxL interpreter.

Now imagine that the file we want to load is actually on the server itself and we have a drive mapped (such as the Y: drive) from our workstation to the server. We can still import the data file as a local file, but this time it’s Y:/data.txt (Assume that the drive is mapped directly to the folder containing the file).

In this scenario, MaxL reads the file over the network from the server to the client, then uploads that data back to the server. This data flow is represented in the figure in the left of this diagram:

MaxL data loads: server vs. local

You might be thinking, “But wait, the file is on the server, shouldn’t it be faster?” Well, no. But there’s hope. Now consider server file loading. In this case we use the server keyword on the import statement and we specify the name of the file to load. Note that the file location is based on the database being loaded to. If you’re loading to Sample Basic, then Essbase will look in the ../app/Sample/Basic folder for the file. If you don’t want to put files in the database folder, you can actually cheat a little bit and specify a path such as ..\..\data.txt and load the file from a relative path. In this case by specifying the ..\..\, Essbase will go up two folders (to the \app folder) and look for the file there. You can fudge the paths a little, but the key is this: Essbase will load the file from itself, without the MaxL client incurring the performance penalty of two full trips of the data. This is depicted in the right figure in the diagram: the MaxL client issues a data load command to the server, which then loads the file directly, and we don’t incur the time needed to load the file.

In my case the automation the written to load a file that was already on the server (in the \app folder), so I just changed the import to be a server style import, and immediately cut the data import time dramatically.

I wouldn’t be surprised if this “anti-pattern” is being used in other places – so take a look at your automation. Let me know if you find this in your environment and are able to get a performance boost!

 

Drillbridge Wiki now up

It has been quite a busy week owing to helping a client deal with some major production issues. The fires are put out for the moment so I am finding myself with a minute or two to move forward a wee bit on Drillbridge.

Several of you indicated a willingness or desire to help out with a wiki for Drillbridge content, so that’s exactly what I have done. The Drillbridge Wiki is now live. For now I have imported the content from the Drillbridge manual to start things off. It looks pretty nice. I haven’t done much to modify or validate the formatting but things look okay. I might say that right now there are a handful of big pages rather than a lot of moderately sized pages, but I am sure over time it will morph into something more Wiki-like.

You can create an account in order to make edits. Please have it if you are interested. Feel free to post tips, tricks, examples, clarifications, links to blog articles written about Drillbridge, and more. You won’t hurt my feelings if you decide to move things around or edit them. Moving forward the wiki will be the primary form of documentation rather than trying to keep the PDF manual in sync with it.

In other Drillbridge news, I haven’t had a lot of time to work on it, but I am still working on 1.3.3. It contains some fixes and some pretty awesome performance improvements. Just sit tight and wait for the awesomeness to get completed.