WP_Data Management Best Practices

34 / 96

W H I T E P A P E R
© 2017 Persistent Systems Ltd. All rights reserved. 34
www.persistent.com
5.2 Brief Summary
Dimension models are usually star schemas or snowflake schemas. These are unlike the 3NF models which are
mostly used for transactional systems. Star schemas consist of a fact table surrounded by multiple dimension
tables. The origin of the term star schemas has to do with the visual layout of the high-level schema diagram. In a
star schema dimensions are de-normalized to avoid capturing too many minuscule relationships in separate
tables. A star schema is generally recommended in the database presentation layer to satisfy performance needs
as well as to service reports/dashboards.
Contrasted with the star schema model, we have the snowflake model. This seeks to model dimensions into
separate tables, as with a 3NF model, based on redundant attributes, thereby modeling hierarchical levels within
dimensions in their own table. Snowflaking generally compromises performance and understandability. However,
under certain conditions which we discuss in section below, this approachmay be acceptable.
The other extreme is a fully de-normalized schema (or the spreadsheet model) that merges dimension attributes
with fact table attributes in a single table. Though often talked about with disdain, is also a model that could be used
in cases of databases that don’t have stable join optimizer plans or those unable to perform themby design. Indeed,
this pattern is best from performance as there are no or minimal joins or indexes lookups. However, it presents
problems when dealing with historization of dimensions with respect to attribute value changes (see sections
and below for more details).
When we look at the schema of the DW, we see tables of the following kind:
— Transaction Fact tables: These are facts where the grain is one row per transaction. These are the most basic
and common facts (There are 2 other types of facts – Periodic Snapshot Fact tables, and Accumulating
Snapshot fact tables, and we shall discuss them later).
— Dimension tables (who, what, when, where, how, why): Key questions related to a business event are
answered by means of these dimension tables using their attributes, which can be used to filter data values in
the where clause, slice/dice in the group-by clause or labeling results, in the select clause.
— Bridge tables: They help resolve m:n relationships with dimensions, while keeping the star schema intact (see
sectio below).
— Control tables: These are for the sake of recording operational information (permissions, etc.)
— Report tables: These table record summaries, and are based off facts and dimensions.
Conforming dimensions are the master data of the DW/BI environment. Common dimensional attributes for
different departmental databases are modeled, making sure that the data from these sources refer to the same
concept and hold values of the same domain; then they are then shared by multiple dimensional models to enable
enterprise integration and ensure consistency.
Conforming facts are obtained by making agreements on common business metrics across these data sources so
these numbers can be compared by formulae in the same analysis.