The focus of this blog will be to deal with some real world examples that will help to better understand Dimensional Modelling. Procurement is a critical activity for most of the organizations. For retailers and distributors, effective procurement of products at the correct price is very important. Organizations could have analytical requirements like what products are frequently purchased, at what prices from various vendors, firms receiving negotiated pricing or not, vendor performance and fill rate etc.

The procurement transaction type dimension is used for grouping or filtering transaction types like purchase orders. The degenerate dimension contract number is used to determine the volume of business conducted under each negotiated contract.

In the above example many dimension are applicable to some transaction types but not all, leading to separate fact tables. So we use the bus matrix for high level of abstraction and visionary planning on data warehouse architectural level. It provides a framework and master plan for agile development identifying the reusable common descriptive dimensions that provide both data consistency and reduced time-to-market delivery.

The decision to have separate fact tables for purchase requisitions, purchase orders, shipping notices, warehouse receipts, and vendor payments was made from a user perspective as they see these activities as separate and distinct business processes. Different systems provide data and there is unique dimensionality for various transaction types. Stay tuned for more updates !

References:

The Data Warehouse Toolkit 3rd Edition - Ralph Kimball, Margy Ross