SQLChicken.com

SQL Server DBA Tips & Tricks

By

BIxPress 3.0: DBAs Welcome!

Much like the USA Network here in the States welcomes characters, I’d like to formally let the world know that BIxPress also welcomes folks, and this time it’s looking at you DBAs out there!

You may be thinking, “But Jorge, the product is called BIxpress, why as a DBA would I give a flip about it?!?” Glad you asked! I’ve recently made the transition from a DBA to a BI consultant and as part of my learning process for learning the BI stack I decided to take a crack at creating an SSIS package that would take a bunch of video files from a conference, compare the file names to the actual session titles (files came down named with their session codes, not names) and rename the files according to their formal session titles. If you’re interested in that, I’ll be posting another blog post soon detailing how I did it as well as you’ll be able to download the package yourself and try it out!

This past week Pragmatic Works (full disclosure: I work for them) released the latest version of BIxPress, version 3. Now, as a DBA I had known about the product but I always thought that it was more of a BI developer tool and it wouldn’t really be all that useful for me. With this latest release of BIxPress, Pragmatic Works has decided to give DBAs a hand in helping corral their own ETL environmental issues and to help understand what’s going on a little better than “yeah that package/job failed last night”. The way they’ve done this is they included a bunch of reports that DBAs would actually care about (I know, revolutionary, isn’t it?).

Okay, reports…woo hoo? C’mon now guys, it’s more than that! One of the coolest things about BIxPress, that I really didn’t appreciate until I started diving into SSIS development myself, is that you can actually view a package as it is running live. No, seriously, you can. As I discovered this was pretty damn cool especially as I was trying to troubleshoot that package I was developing. In BIDS to dig through errors you get a view that looks like this:

With BIxPress you can get a friendlier view of any package errors that look like this:

The second way is much easier to parse through and figure out exactly what’s going on within a package. And that’s just for troubleshooting. Let’s get to the fun part with the reports! Earlier I mentioned you can see a package while it’s running, which is something you haven’t been able to do before. This becomes quite handy if someone asks the status of where the package is at, or maybe it’s taking longer than expected and you can see exactly at which point the package is currently on. This live view gives you tons of great stuff like any changes to variables, the current value of a given variable or even the change history of all the variables in a package! This is great stuff especially if you’re expecting certain variables to be/not be changing and you can see the exact values being passed around.

Live view of your package in progress in BIxPress

If you’re in an enterprise environment you’re more than likely having to monitor multiple packages. Another sweet thing with BIxPress is that you can setup a view that allows you to see that live dashboard view for up to 16 packages at once! Somewhere there’s an operations person thanking their lucky stars that they get to see something other than green screens for once.

Okay, that’s fine but what about the DBA stuff? Well in this release they’ve included reports they think DBAs would like to know about including run times, averages, trends and whatnot. What I really liked about these reports is the detail given. You can actually see the individual data sources and destinations for given packages, as well as how much data is getting moved to and from them!

This dashboard view also easily lets you see which packages run fastest and slowest in your environment so you have a razor sharp idea of what’s going on and are able to quickly dive in and take a look at any potential performance problems. As a DBA I’m pretty grateful for any and all insight into my environment that I can get and this tool lets you clearly see in to the black box (or so many view it as) of the ETL infrastructure within your environment. What do you think? Have you tried it out? Go download a trial copy and give us some feedback!

Share