Cementing the relationships

With attributes and PK and FK relationships established in the dimensions, we again turn to the business experts to help flesh out the fact tables and determine their grain.

Building from the conceptual model, we ensure that all relationships have a defined granularity and optionality and that the data teams understand their business context. Mistakes made in the fact table definition are the costliest to reconcile and could result in costly readjustment, so extra care should be taken. Work with domain experts to ensure that the fact tables capture the true atomic grain of the information recorded by business operations and that the optionality is correctly understood.

In the following example, the logistics team confirms that our warehouse separates parts by supplier to facilitate returns and inventory tracking. In short, the warehouse can store many parts from many suppliers. Role names are also verified and documented at this point for non-obvious...