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

226
Chapter 7
Create separate
hot swappable
dimensions for
each reporting
language
When and Where
With this approach standard reports can be developed once and run unaltered (as
long as they do not filter on translated descriptions) in multiple offices with local-
ized results. For example, a CO 2 footprint report in the London office that catego-
rizes travel reasons as “Conference,” “Consulting,” and “Return Home”, would
display “Congresso,” “Consulto,” and “Casa di Ritorno” when in Rome.
Using separate hot swappable dimensions for national languages means that you
can add new languages at any time without affecting the existing schemas and
reports. This allows you to deliver an agile solution with a single language initially
and then go global, without incurring technical debt.
Consequences
When translating dimensional attributes, care must be taken to preserve their
cardinality; for example, 50 distinct product descriptions in English must remain
50 distinct product descriptions in French and Italian—so that reports contain the
same number of rows with the same level of aggregation when translated.
Preserve sort order
and cardinality
National language versions of a dimension sort differently. Cryptic business keys
(BK) are often stripped from dimensions if they are never required for display
purposes. However, they can be used (without being displayed) to provide consis-
tent sort order when standard reports are delivered in multiple languages.
Summary
Time is modeled dimensionally by separating date and time of day into CALENDAR and
CLOCK dimensions which should contain all the descriptive time attributes BI users need.
Period Calendars, such as MONTH are built as rollups of the standard CALENDAR. They are
used to explicitly define the time granularity of higher level fact tables.
Fact-specific calendars, built using ETL fact state information, are used to ensure valid YTD
comparisons.
International facts should be overloaded with additional time keys to support standard and local
time analysis.
Location-specific date descriptions and day-specific time descriptions can be handled by using
the time surrogate keys DATE KEY and TIME KEY to represent versions of a date or minute.
Journey analysis can be enhanced by overloading movement facts with additional location keys
and why and how dimensions based on the first and last locations in a meaningful sequence.
Separate hot swappable language-specific dimensions are used to support national language.