Making banking fun... secretly

I'm afraid I can't tell you right now how I'm making banking fun, because we're probably going to patent it, and my limited understanding of such things tells me that I shouldn't tell you what it is until we've at least filed for the patent. Or, if we take a page from Apple, I shouldn't tell you what it is until it's totally obvious because we released a product with it. Or, if we take a page from Google, I should never tell you what it is, because we might only use it to make a product, not actually make it into a product itself.

I have, however, made banking fun. I call my new plan to take over the world "The Grand Schema." You will have to use your imagination.

Interesting database reporting

Not speaking of which...

StarQuery for Excel has its problems (notably the screwy non-Firefox-friendly web site), but they have a very interesting concept: the idea of creating a database map to make it easier for end users to generate their own reports.

Basically, the database admin/programmer can use their tool to create "map" diagrams. The map file specifies a particular database connection type (say, an ODBC connection), a list of tables/views that are relevant to this map, a list of table "join" relationships (inner, left, right), a list of "measures" (columns that can reasonably be summed, averaged, etc), and several "dimensions", which are groups of columns you can use to subcategorize table rows (for things like filters, "group by", "order by", etc).

Dimensions are an interesting concept by themselves. Really, it's just a two-level hierarchy of columns, purely for human-helping organizational purposes. For example, I might take the Region, Country, Province, City, and PostalCode columns (perhaps all from different tables) and dump them all into a "Place" dimension, the LastName, FirstName, Sex, and Birthdate columns and dump them into a "Personal Information" dimension, and so on. Some columns might even belong to more than one dimension.

This is where it gets neat. After specifying all this stuff - which is essentially static information about your database - you save it in a map file, which you then import into the report designer. A different person, maybe an admin assistant or Ozzy or whoever, now doesn't have to care about the database schema; they just get the map, a convenient representation of the parts of the database you figured would be relevant to them, all pre-joined and pre-filtered and pre-categorized. They can create a sum of "revenue" (a "Measurement") grouped by "Place" (ie. the location identified by concatenating all the fields from "Place" as a primary key), or break apart "Place" into its component columns if they want; in the latter case, the Dimension box really is just an organizer to help humans find things.

The magic here is that the admin can just think about columns and sums, not tables, indexes, joins, data types, and UniqueIDs. And if your application is obscenely huge (ours certainly is), you can even create different "map" files with different overlapping subsets of the database, and use those for different types of reports.

The results of your query gets dumped into Excel, where you can format it, draw charts, and so on. It's all surprisingly elegant, and it makes it easy for non-database-experts to explore your data.

Presumably this is what "data mining" and "cube views" applications are trying to do, but this is the first one whose user interface didn't make my head explode.