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

Dimensional Design Patterns for Cause and Effect
275
WHERE Option2 = “Y” and Option3 = “Y” and Option14 = “Y” and
Option4 = “N” and Option5 = “N” and Option190 = “N”
The example data in Figure 9-14 shows that option pack the users are looking for is
OPTION PACK KEY 1. This is the same value as the more complex set based
queries would eventually find in the bridge table because the pivoted dimension
and the bridge table use the same surrogate key—they are swappable versions of
each other. This means that the fact table does not need to be altered to add the
pivoted dimension if the bridge table key is already present. There is value in
having both tables because the bridge table and OPTION dimension combination
is GROUP BY friendly and single value WHERE clause friendly while the pivoted
dimension is combination WHERE clause friendly. To make the pivoted dimension
user-friendly as well it should be built with meaningful names for each option
column; for example, MEMORY UPGRADE, CPU UPGRADE, RAID
CONFIGURATION etc.
A bridge table and
pivoted dimension
are swappable
dimensions that can
be used together
Figure 9-14
OPTION PACK
pivoted dimension
In Figure 9-14, the pivoted dimension has an additional OPTION PACK attribute
containing comma separated lists of option codes. This can be used in a query
GROUP BY clause or displayed in a report header/footer to describe the filters that
have been applied. In the user-friendly version of the pivoted dimension this would
be a long text column containing a list of descriptive option names (sorted in
alphabetic order). It can be useful to provide both versions; e.g., an OPTION
PACK NUMBER list of codes and an OPTION PACK list of descriptions.
Add comma
separated list
attributes to flag
dimensions to make
them more report
display-friendly
If you need to build a column flag dimension, create the multi-valued bridge table
version first. Maintaining this type of table is easier with standard ETL routines
and simple SQL. After the bridge table is in place you can then create more
elaborate ETL routines that pivot its rows to create and maintain the column-
orientated version with meaningful column names generated for the descriptive
row values using SQL generated SQL.
While bridge tables and pivoted dimensions often go together, the need for a
pivoted table is not limited to multi-valued dimensions. For example, if the granu-
larity of ORDERS FACT was one record per product option order line item (the
product plus each of its custom options as fact rows), this would avoid the multi-
valued bridge but the pivoted dimension would still be needed to easily answer the
combination questions. It would just be more work to build the pivoted dimension
Even without a
bridge table, a
pivoted dimension is
needed to cope with
complex ad-hoc
combination queries