Databases Reference
In-Depth Information
In an RDBMS, the snowflake schema is sometimes chosen when trying to save disk
space, as it removes duplicate values from the dimension tables. Since QlikView
automatically removes duplicates from the data model, using a snowflake schema is
generally not a preferred approach.
Creating the dimensional model
So, how do we go from an ER diagram to a dimensional model? The first thing
to understand is that an ER diagram does not directly translate into a single star
schema. A transactional system often contains data used across many different
business processes. For example, think of how many different business processes
and functions a typical ERP system supports: accounting, human resources,
manufacturing, supply chain management, and customer relationship management,
just to name a few. The data for all of these processes is usually stored in a single
ER schema.
The first step in converting from an ER schema to a dimensional schema is dividing
the ER schema into separate business processes. Each of these business processes will
be modeled into a separate star schema.
The next step is to declare the granularity of the business process (for example, a
single flight or one salary payment). We then group the measures that are used in the
business process into a single fact table.
After that, the remaining tables are flattened into dimension tables and directly
linked to the fact table using a single key. It is possible for the same dimension
table to be used in multiple star schemas. This is called
conformed dimension
. For
example, the employee dimension can be used in the context of Airline Operations as
well as in the payroll context.
In QlikView, we can use QVD files to store conformed
dimensions. For example, we can store the
Aircraft Type
dimension into a QVD file and use that file in any application
that requires information about the
Aircraft
. This way, data
consistency is ensured across applications.
Dealing with multiple fact tables
As described previously, each business process is modeled into a separate star
schema. When dealing with multiple fact tables in a single QlikView application,
loops and synthetic keys can occur. To solve this problem, fact tables can be
concatenated, or a link table can be added. The techniques for how to achieve this
are described in
Chapter 8
,
Data Modeling Best Practices
.
