Re: Announcing the "Instant Data Warehouse" Product

Hi,

    great to see some interesting comments on fact table creation and maintenance...I'd love to see more people discuss fact table creation since it seems to be a well kept secret on how they actually work. (We can find plently of people advertising in them, not much meat and potatoes.)

Firstly, yes, placing multiple levels of summaries in a single fact table does require that the aggregate level be specified for all dimenstions that can contain aggregates in the query. Simplistically, this can be catered for by using views over the fact table. For example, product_by_week, product_by_month etc. Sort of turns out to look the same to the end user as having separate tables. If the tools are more intelligent then they can constrain in SQL rather than in views. I once visited a customer with over 50 summary tables, and end users who were confused as to what tables contained what. At the time we created 'multi-level summary tables' to overcome this problem, and later I found out about being able to contain multi-level summary tables in a fact table. This is very powerful. And yes, sometimes the user needs to be trained.

Secondly, yes, you can end up querying a much larger fact table than is necessary, which is why I usually make it possible to have as many fact tables as the customer wants, though we do not insist on it. Usually we can get down to only a few fact tables for each transaction based system. We especially recommend that all details go into one table, all summaries go into another table, and the 'most used' summaries are propagated into a third table. Since one generally does not know what summaries will be popular when one starts it helps if summaries are table driven and require no visit back to the IS shop for more coding and more database objects to be created. I'm sure you get that the idea is to reduce the time/effort in the database build of summaries.

Thirdly, not all columns need to be strictly additive for such a schema to work. For example, in a banking environment I can have account balance on the detailed records and on the summary records and if your account balance is anything like mine, it does not get added to by every transaction (quite the opposite)....yet, I can still carry account balances on my aggregate tables, no sweat....

I look forward to more discussion on what actually goes into a fact table....

-- 
Peter Nolan
Principal Consultant
Nolan Consultants Pty. Ltd.
http://www.ozemail.com.au/~pnolan