Business processes are the operational activities performed by your organization. Select a business process:
⚠️ Process is too high level
This process is too generic. A good business process should be a specific business event or transaction that happens at a specific point in time. Try selecting a more specific process.".
Why "Coffee shipments" is a good business process to model
A business process is the operational activities performed by your organization, such as taking an order or processing a shipment.
"Coffee shipments" describes an actual operational activity that occurs in the business, rather than a general business area like "Sales" or "Subscriptions".
2
Declare the Grain
The grain establishes exactly what a single fact table row represents. Select a grain level:
⚠️ Grain is too high level
This grain is at an aggregated level. For best analysis, consider using the lowest level of detail. This allows for more flexible analysis and drill-down.
⚠️ Grain is too high level
This grain is at an aggregated level. For best analysis, consider using the lowest level of detail. This allows for more flexible analysis and drill-down.
Why "One row per coffee product shipment per subscriber" is a good grain
The grain statement gives the precise meaning of a single row in the fact table. This atomic grain captures each individual shipment line item, providing the most dimensional flexibility. Detailed atomic data can be constrained and rolled up in every possible way, but aggregated data cannot be disaggregated.
3
Choose the Dimensions
What is a Dimension?
Dimensions provide the context surrounding a business process event. They answer the who, what, where, when, why, and how questions. Dimensions contain descriptive attributes that enable filtering, grouping, and labeling of fact table rows.
Based on your process and grain, here are the recommended dimensions following Kimball techniques:
4
Choose the Facts
What is a Fact?
Facts are the measurements from the business process event. They are typically numeric and represent the metrics that the business analyzes. Facts can be additive (summarizable across all dimensions), semi-additive (summarizable across some dimensions), or non-additive (not summarizable).
Based on your grain, here are the recommended facts (measures) - starting with the simplest facts:
5
Detailed Entity-Relationship Diagram
This detailed ER diagram shows all attributes, primary keys (PK), and foreign keys (FK) for each table in the star schema.
Alternative Modeling Techniques
There are alternative data modeling techniques that do not use a star schema.
Conceptual Model
Other techniques like Data Vault start with a conceptual model.A conceptual data model represents the business concepts and their relationships at a high level, independent of how the data will eventually be stored.
📝 Note
A conceptual data model isn't "normalized" or "denormalized" in the technical sense because it doesn't represent actual database tables yet. It represents the business concepts and their relationships at a high level, independent of how the data will eventually be stored. Data Vault uses conceptual modeling as its starting point.