Comparison
Conformed dimensionvsRole-playing dimension
Conformed dimension
marketing and finance both group by the same customer dimension, so their two reports can finally be put side by side.
A dimension shared identically across several fact tables, so measures from different processes can be compared along it. It is the mechanism by which a warehouse becomes more than a pile of marts: without conformance, every team's 'region' is subtly different and cross-process comparison is guesswork. Building one is mostly a negotiation rather than an engineering task, which is why it is the piece that is usually skipped and later regretted.
Full entry →Role-playing dimension
the same date dimension is joined three times as order date, ship date and return date, and the report is unreadable until you alias them.
One physical dimension joined multiple times to the same fact under different meanings. It is the standard treatment for dates and for people who appear as both requester and approver. The practical requirement is naming: exposing three clearly named views over the one table is what keeps a BI tool's field list comprehensible and stops an analyst joining the wrong one.
Full entry →