Databases Reference
In-Depth Information
When deciding on data model design, you should always ask yourself if the
"structural asymmetry" we talked about before is something you could accept
in your data model, or if the asymmetry is highly significant and therefore you
would prefer to live with the "additional hop" in the data model.
Choosing between approaches can also impact the application's size. Take a look at
both of the files created using each of the preceding methods and see which one is
smaller in disk size .Can you guess why the link table approach produces a smaller
file? It's because we've taken out some fields from the relatively large fact tables and
placed them into a smaller link table.
For QlikView, both of these approaches are OK. They work as they should and there
should not be calculation differences resulting from using them one over the other.
For the purpose of continuity throughout the rest of the book, we will be working
with the new data model created using the first method: Concatenation. Therefore,
make sure to integrate the
Employment Statistics
table to the original
Airline
Operations
document by concatenating both fact tables as previously shown.
The master calendar
Finally, our last set of best practices on data modeling involves dealing with dates
and times. When analyzing data, time often plays an important role. Initially, it's not
much of the individual transactions and events that users are interested in, but rather
the rolled up totals per period, or trends over multiple periods.
Source systems usually record the date at which a particular transaction or event
took place, but do not contain any further information for time grouping. This makes
sense, as transactional systems strive not to include redundant data. In our QlikView
documents, however, we strive to make the selections and aggregations as easy
as possible for our users. That is why, in addition to the original date, we include
attributes such as the month, quarter, and year components in our data model.
Rather than placing these attributes directly in our fact table, as we've done until
now, the best practice is to create a separate master calendar dimension table. The
main advantage is that it lets us use the same master calendar for multiple fact tables.
Another important benefit is that if the fact table is missing any intermediary periods,
we can still create these in our master calendar. This way, when data is missing for a
certain period, we would still be able to see that period in our document.
