My first Publication Agile-Data-Warehouse-Design-eBook | Page 84

Modeling Business Dimensions
63
Dimension Granularity and Business Keys
The next step is to define the granularity of the dimension so that it precisely
matches the event(s) it describes. The event stories modeled so far have used
customer and product names for readability but you must now discover the busi-
ness keys that uniquely identify these details. Business keys are the primary keys of
source system reference tables. You discover a business key by asking “What
uniquely identifies each [dimension name]?” or “How do you distinguish one
[dimension name] from another?” For example:
Ask for a business
key to uniquely
identify each
dimension member
What uniquely identifies each customer?
To which stakeholders might reply:
A customer ID
You check that this is what you need (a single, universal, reliable identifier) by
confirming with the stakeholders that:
CUSTOMER ID is mandatory. Every customer must have a value for this
business key at all times. If this were not the case other business keys would be
needed to augment this one.
A business key
must be unique and
mandatory
CUSTOMER ID is unique. There are no duplicate values. New customer are
not assigned old lapsed customer IDs.
CUSTOMER ID is stable. IDs are not changed or reassigned.
Assuming CUSTOMER ID passes these stakeholder tests (which should be con-
firmed by data profiling as soon as possible) you add this identity attribute to the
dimension table with examples to match the existing customer names as shown in
Figure 3-2. You also mark it with the column code BK to denote that it is a “Busi-
ness Key” and because it is the only business key for CUSTOMER you also mark it
as mandatory using MD. You can leave out the “has” preposition as it adds little
value. When the relationship between an attribute and the dimension subject is
more complex (and not apparent from the attribute name) you can add a preposi-
tion to help you read the dimension members as stories.
Add the identity
attribute
immediately after
the dimension
subject
Figure 3-2
Adding a business
key to a dimension