Star Schema Redux ...

I received a blizzard of E-mail above my comments on Star Schema.

Just as I responed to those who wrote, I really can't go into too much detail without giving away the store. We are certainly not the only consultants who use this design nor did we invent it. However, it is an important part of our practice.We did, however, discover our own version of it through trial and error, and perhaps this discussion will allow you to converge on your own solution more rapidly.

The whole point of star schema is to provide a VERY FAST way to get the data out. It solves a narrow band of problems in the relational world. It is to be used only for OLAP-type applications, like a data warehouse. In fact, there is a burning disagreement among certain noted industry analysts about whether it makes sense to keep multi-dimensional in a RDBMS at all, as opposed to a MDDB like, say, essBase or Gentium. The star schema have no OLTP capabilities. It requires a strategy for update and refreshing which is not implied by the design - this is a separate issue. We have not yet figured out a way to distribute a star scheme across multiple servers. With those caveats in mind, here is a more detailed description of the design.

The heart of a star schema is dimensional modeling. A classic multi-dimensional model is the product model, where the dimensions are time,
product, customer, geography, etc. The *facts* are usually numeric, additive
values, like dollars, shipments, pounds, etc. The star schema take advantage
of these relationships by allowing one to create structures that will resolve many queries without ever going to the fact table (pick lists, for example, do not need to
do a SELECT DISTINCT on the huge table). Therefore, you can handle constraints and control breaks in the smaller, dimension tables and only hit
the fact table when you are ready. And the more denormalized and complex the
dimensional tables, the fewer tables required in the join path, therefor, faster resolution of complex queries. Clear as mud?

Neil Raden
Decision Support/Data Warehouse Consulting

Envirometrics, Inc.                            805.564.8672
133 E. De La Guerra St.                    805.962.3895 (fax)
Santa Barbara, California 93101         E-Mail:  NRaden_at_aol.com