Maintaining Analyto-Operative Data using MS Access

Actually, this idea should rather have startet another "BI Musings" blog. But for the sake of simplicitely ...

Any advanced data warehouse/BI landscape will necessarily develop a bunch of (mostly: dimensional) data residing in the twilight zone between operational input and analytical output.
Examples are - from our experiences - localization resources, seasonal and other analytical taggings, organisational mappings, plausibility rules, technical annotations such as versions, modes, runs, etc., etc., etc. ... See, the hunger comes with the meal.

Most often, it is too expensive and/or inconvenient to put these data and the associated maintenance processes into the (legacy) operational systems where they should have been modelled in the first place.

On the other hand, the relational techie in charge of the DWH platform is seldom willing to care about the business meaning of those things, too.

We have tried various mechanisms to construct and maintain these beasts which range from "Direct-Editing Relational Tables", "Pre-Privoting in Excel and Mounting the Sheet as an External Table" into "Replaying SQL Scripts". Neither of those solutions have been very satisfying so far.

Our customer indeed gave us a hint to look at a tool that we have abandoned long before but that is still installed at most BI users computing devices along with Excel: MS Access!

Using Access to build up and maintain a normalized part of the dimensional model (e.g., we use individual tables for each hierarchy/attribute hosting technical and business keys, labels, descriptions, ranges, localizations, ....) eventually using forms make it a pleasure for business users.

By (graphically!) defining views in Access, it is quite easy to construct, pre-pivot, debug and even pre-chart the flattened dimensional outcome of the partial model. Outer joins (caution, mostly RIGHT JOINs in a typical star-like structure) help you to initially identify integrity problems when doing batch-input. After that, using referential integrity constraints, Access will help you and your users to derive at consistent intermediate versions of the data.

Finally, you can use the more-or-less two-button ODBC export facility to dump the complete result view into the datawarehouse stage/information schema of your choice. Depending on the sophistication of your driver (believe me, SQL-Server is well-supported ;-), type conversion could sometimes aberrate, but SSAS will remap whatever junk there comes into reasonable target types most of the time.