SQuirreL away that data

The most useful language any programmer should know is T-SQL - structured query language.  When you have a solid understanding of relational database architecture and the tools to manipulate it, extracting handy information from your business data becomes almost effortless.

Relational DB systems abound: Access, SQL Server, Oracle, MySQL and PostgreSQL are a few examples.  You can even use SQL to intelligently compile data right out of your Excel worksheets.

As macro developers, we often rely on canned reports produced by corporate systems to feed us data.  This is usually in the form of .csv text files, web scrapings, or stacks of steaming, reeking workbooks that take hours to recalculate.  Sometimes, especially if data volumes are large, moving everything into Access and pulling it back into Excel using SQL Select statements can be the best way to carve out the specific information required.

In a recent project, a client was mining several years worth of member profile data to compile statistics and spot geographic trends.  The prototype system, using a small subset of data and a few pivot tables, appeared to get the job done.  But the workbook was already topping 20MB in size - a real pain to shuttle around.  Then the full data set accumulated to over 65,000 records - more than the number of rows in an Excel 03 (client's spec) worksheet... time to step up to MDB.

I massaged the data in pieces in Excel, imported it into an Access database, and was stunned to find the .mdb file to be only 3.2 MB in size!  That's sweet compression.  Smaller than my prototype workbook was, zipped, but holding 20 times the data.

Next, do away with pivot tables.  Nothing personal. I actually think pt's are a pretty good idea, but they can be unwieldy, and many end users fear them.  Programming them takes you into the outer wastelands of VBA - sketchy knowledge for most of us, (but examples are out there,) and getting exactly the result you need might not be possible - this has been my experience with most of Excel's special power features.

I think SQL is considerably more powerful, and a lot less complicated.  With a simple* piece of code I can build a mini pivot table that doesn't pivot, because it's already pivoted to what the user wants to see.  Here's an example:

SELECT TOP 10 ZipCode, AVG(Revenue) 
FROM MemberView GROUP BY ZipCode ORDER BY 2 DESC

This gives us a nice little Answer, suitable for publication in this years Annual Report - a breakdown of the top 10 zip codes by average revenue.

The key to this is setting up a View, which is another more complex SELECT query that joins data from different tables.  This View can also pre-compute some answers, convert ugly binary 1's and 0's to pleasant things like "Yes" and "No", and limit the data set as needed.  It sits in the DB and looks just like another table.  This brings all the data we need to a single point of light.

I wanted to let users pick the field to zip-rank.  For this I linked a drop-down control to a list of cells containing the fields in my View, and the report macro simply subs the user choice into the Select statement.  With this dead-simple, pre-pivoted table maker, users can't mess up.  Instead, they grind out report after report, all day long, grinning like madmen.

Doing some of the heavy lifting in the database itself means the macros and formulas can be much simpler, and the JET database engine can crunch the numbers a lot faster than Excel anyway.  I love that my Business Reporting System workbook is only 100Kb.  Not having data to lug around means macro updates and bug fixes don't impact the client, which helps keep me off-site.  When the data set scales up even further, I'm well positioned to migrate to an enterprise system like SQL server, with minimal impact on the Excel code.

In a Previous Post I showed you some VBA code to support Access DB connectivity and  pulling data using SQL Select statements.  This should be enough to get you started.  I'd love to hear about any nifty tricks or nasty pitfalls you encounter.

Excel in Front, part 1

This post is long overdue - I've been flat-out, coding a very interesting application for a manufacturing client, using Excel as the front end on a shared Access database.  The goal is to give a workgroup the ability to perform simultaneous edits to their work-in-progress tracking data, without any of the hassles, bugs, hideous file growth, sharing violations, lock-outs, etc. that you get with the built-in workbook Sharing mechanism.  There's a host of business logic involved, and they want the ability to design their own reports at will.

Again, I find myself reinventing the wheel.  "Why not develop your application in Access?" one might ask. My reasoning: Customer didn't have it, doesn't want it, can't support it, and more importantly, developing in Access is painful.  I prefer Excel.  The advantages weigh in: custom business logic is more easily coded in Excel.  It is.  And we needed in-grid editing, flexible data presentation, filtering, sorting, graphing... and whatever else we can dream up for versions 2 through N.  That's all in my Excel quiver.

"Oh, right. Excel lets you Link to a data source and populate tables..." yeah, I thought about it, but the users don't want to fart around with Data Connections, and my experience tells me it's a long road coding for those APIs. (if you know better, please tell me.)  And I wanted this to be an always-disconnected setup, where the workbook simply grabs data or sends updates to the db in quick bursts - no live links to network files that might be unavailable, renamed or changed.

The db was designed using an SQL Script to create the tables, populate the lookup data, and set up primary keys, defaults, and so on.  I ran the script to create the database in SQL Server, and then imported the tables into my .mdb file using Access.

I did not use the Access database design tools, though you certainly can.  I prefer to use a build script whenever designing a relational database; you drop and recreate the db often as you rework your design - that's Agile.  I also find that Access is a bit too user-friendly, in other words, not powerful enough.  I guess I can type SQL faster than I can ponder through menus.

In Excel, I set up a file-select control, and some text boxes for username and password.  The users on the network will each have a local copy of the application workbook - It's not linked, welded, cross-referenced or otherwise umbilical'd to any other file.  Can't you smell the freshness?  No pop-ups... ever.  As updates come out, they will be emailed to the users.  They will enter their credentials, file-pick the .mdb file, and work. That's Data Separation.

Here, again, I'm not tying into any built-in user management.  In a secure enterprise situation you probably have to set up database user accounts linked to network id's, but my client is looking for (and I strive to offer) the simplest solution wherever possible (SSWP), so we won't be contacting network support for domain credentials and such - the .mdb file is on a password-secured network drive.  Our application will maintain it's own user list... VBA code does the rest.

The project is still underway - next post I'll explain how I set up my object classes, control cell-editing, filter and sort, and conclusions: will it be another home run, or a teetering, reeking, house-of-cards? Stay tuned.