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

Dimensional Design Patterns for Time and Location
225
National Language Dimensions
Data warehouses that have to deal with international locations and time zones will
also have to provide national language support (NLS). Stakeholders will want to
ask business questions in their own language and have the results translated.
International data
warehouses need
to be multilingual
National Language Calendars
Multilingual calendar presentation styles can often be handled by the localization
features built into database management systems and/or BI tools; for example, you
can configure language and default date presentation format (MM/DD/YYYY for
USA and DD-MM-YYYY for Europe) at the database schema level or in the BI tool
metadata layer to reformat dates into the appropriate local language for presenta-
tion. Changing the presentation format at the database or BI tool level preserves
the correct date sort order of the underlying queries.
Use the localization
features of your BI
tools and DBMS to
support local date
formats and month
name translation
If BI users and developers require national language support for reporting element
names while constructing ad-hoc queries (for example, Italian users want to select
“Mese Fiscal” and “Motivo per il Volo” rather than “Fiscal Month” and “Flight
Reason”), attribute name translation should be handled by the BI tool semantic
layer rather than database views. This keeps the SQL or OLAP query definitions
portable across boarders.
Swappable National Language Dimensions Pattern
Problem/Requirements
Pomegranate has BI users in the UK, U.S., France, and Italy who want their reports
to use the local language for descriptive labels—such as full product descriptions or
flight reasons. One possible design is to create additional dimension columns for
each of the required languages (for example, FRENCH FLIGHT REASON and
ITALIAN FLIGHT REASON). But this approach overcomplicates the dimensions,
especially if many attributes need localization, and many languages have to be
supported. It also requires reports to be rewritten to use each new language column
as other countries come on line.
Stakeholders want
reports in English,
French and Italian
Solution
Instead, a more scalable design is to create separate hot swappable dimensions
(SD) for each language. Each language version would be identical in structure
(identical table name, identical column names, and identical surrogate key values)
but with its descriptive column contents translated as required. These language-
specific dimensions would then be selected based on the schema the BI user logs
into. For example, Italian user IDs would default to the schema with Italian ver-
sions of the PRODUCT and FLIGHT REASON dimensions.
Use hot swappable
dimensions