Data Warehouse Subsystem
part of the ArsDigita Community System
by Philip Greenspun
Most of the real work in building a data warehouse is constructing a
dimensional data model and copying information from your online
transaction processing (OLTP) data model into the dimensional model.
This software won't help you with that. Although I hope to some day
write about this, for now I will simply refer you to Ralph Kimball's The
Data Warehouse Toolkit.
What this subsystem is designed to do is provide a reasonable user
interface to ad hoc querying of a single table. If you have multiple
tables that need to be JOINed, you could either extend this software a
bit or build a view that does the JOIN. If you have a truly large
database (gigabytes), you might find that performance isn't acceptable.
In that case, what you need to do is
Once you've gotten your data into a dimensional model (one fact table
plus a bunch of dimension tables), the data warehousing module of the
ACS can help you. It is also potentially useful if you just want to
provide ad-hoc query capabilities for a big table that happens to exist
in your production database.
- get your data into a dimensional model
- build a view that joins the fact table to the dimensions and
contains everything a user might want to group by, restrict by, or
- see if you can't browbeat your RDBMS into optimizing queries into
this view so that it doesn't mess with tables that are irrelevant to a
The assumption is that this module will keep you from having to buy,
install, and maintain Seagate Crystal Reports (a truly painful-to-use
You will almost surely want to go into /tcl/dw-defs and change
dw_table_name to return either the right table or view for
this system or something that depends on which user is logged in.