Tuesday, September 2, 2008

Star Schema

If you draw a diagram of your dimensional tables, how a fact table is related (surrounded) to its dimension tables, you'll see a star schema. The fact table is the star's centre; the dimension tables, the corners.
Your schema can have multiple stars, one for each of your fact tables.
Note that a dimensional table might be related (shared) to more than one fact table.

Wednesday, August 27, 2008

Two types of tables

Dimensional data warehouse has two types of tables (I’m talking about relational database tables here). The so-called fact table contains fact data, which is WHAT you want to analyze (fact is a.k.a. measure). Fact is numeric. Any measurement can be a fact, e.g. sales amount, count (of anything), percentage (of change), duration (elapsed time), score (soccer match result), grade (as in school), and rank (as in competition).

On the other hand, dimension table contains data that provides you with the HOW you want to analyze the fact(s) in the fact table. For example, if you want to analyze the dollar sales amount by four dimensions: month, product, sales region, and customer segment, you must have Month, Product, Sales Region, Customer Segment dimension tables. Dimension data is typically textual. The Month dimension table, for example, contains “JAN 2008”, “FEB 2008”... rows of data.