At Acestar Solutions we design and build star-schema data warehouses following the popular Kimball methodology. A data warehouse is essentially a database and is a combination of two sorts of database tables, ‘fact’ tables and ‘dimension’ tables.

Fact tables – a ‘fact’ table records the measures that are included in the data warehouse, for example quantity sold, revenue etc.

Dimension tables – these contain descriptive information. An example of a dimension table might be called D_Products and contain a list of product codes and descriptions.

A Star Schema

A simple star schema is shown above. There is only one table join between each table, which speeds up processing when using it for reports and analyses. Note that only the details of the actual sale are held in the sales fact table F_Sales. Product details, customer details and the date are held in separate tables.

Multi-layered design. The star schema warehouse is built in three layers, each of which is usually a database. The staging layer holds the raw data as it comes in, for example a spreadsheet containing product details would be stored in a table in the staging layer.

The enterprise layer is the place where things are added to the data, for example revenue might be calculated by multiplying quantity sold and price.

Finally, in the presentation layer the dimension and fact tables the end users have access to are created (see diagram above).

Data Marts A data warehouse usually has more than one fact table. Each fact table will then have its own star schema (but they usually overlap) and each is called a data mart. So one or more data marts go to make a single data warehouse. A large corporation may have only one data warehouse, but this will include many data marts.