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

172
Chapter 6
Who and What
Swappable Dimension Patterns
Problem
Mixed business
model customer and
product dimensions
can contain many
sparsely populated
exclusive attributes
For organizations with mixed business models, selling heterogeneous products and
services to both consumers and businesses, customer and product dimensions can
quickly become complex and unwieldy. Each different type of customer or product
can have its own set of exclusive attributes (X) that are not valid/relevant for the
other types. This can lead to wide, sparsely populated dimension records. For very
large customer populations this can lead to performance and usability issues.
Although product populations rarely approach the size of customer populations,
for heterogeneous products and services, the number of product type specific
attributes can be very large indeed, causing similar problems.
Solution
Dimensions that contain large groups of exclusive attributes (based on one or more
defining characteristic (DC) attributes) can be modeled as sets of swappable dimen-
sions to improve usability and performance. Swappable dimensions are so named
because they can be swapped into a query in place of (or in addition to) another
swappable dimension that shares the same surrogate key. Figure 6-4 shows swap-
pable sets of customer and product dimensions that would be useful for a mixed
business model data warehouse. The main CUSTOMER dimension contains
attributes that are common across the entire customer population, this includes the
defining characteristic CUSTOMER TYPE [DC1,2] which identifies which of two
exclusive groups of attributes are relevant: X1 consumer attributes or X2 business
attributes. The swappable CONSUMER and BUSINESS CUSTOMER dimensions
contain these common attributes and the exclusive attributes relevant to just their
customer type.
More efficient
swappable subset
dimensions can
be created based
on defining
characteristics
Hot swappable
dimensions can be
used in place of
each other without
rewriting queries
Swappable subset dimensions are easier for many BI users to navigate because they
contain only the rows and columns that are relevant to them. For example, BI users
working in corporate sales would use the BUSINESS CUSTOMER version of
CUSTOMER—using database synonyms, it can be renamed to be their default
CUSTOMER dimension. Because BUSINESS CUSTOMER only contains corpo-
rate customers they would see only the business attributes they want, and would
not have to add where Customer_Type=‘Business’ to every query. If
businesses made up only 10% of the customer base, the corporate sales analysts
would see a significant performance boost too.
Swappable dimensions that have identical column names are referred to as hot
swappable, because they can be used in place of each other without rewriting any
queries. Hot swappable dimensions can be used to implement restricted access
(row-level security), study groups, sample populations, national language transla-
tion and alternative CV/HV reporting views (See the Hybrid SCD pattern covered
later in this chapter).