Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

3.1. The Base Schema

Often, the initial implementation of a data mart does not include aggregates. This omission can help an inexperienced project team get their feet wet by reducing project scope. Aggregates will be introduced later, and will probably be incorporated into the initial implementation of subsequent data marts. These project approaches are examined in Chapter 7.

Whether or not aggregates will be included, documentation of the base schema should include information that will aid in the future development of aggregate tables. These include grain statements, the dimensional conformance bus, and the documentation of aggregation points or hierarchies within dimensions.

When building aggregates, the base schema serves as the source system for the aggregate schema. Knowing this, you can include some housekeeping columns in your fact and dimension tables, which will aid the ETL process for building the aggregates.

3.1.1. Identification of Grain

Explicit identification of fact table grain is an essential part of any dimensional design. It is usually one of the first steps in the schema design process. Failure to properly fix the grain leads to problems. It will be difficult to get valid reports out of the schema. Confusion over what a row in the fact table represents will make the task of identifying aggregates impossible.

3.1.1.1. When Grain Is Forgotten

Inattention to grain leads to failures in design that compromise the value of the schema. Fuzzy grain manifests itself ...