There are several methodologies for implementing a data warehouse or data mart that might be useful to consider when implementing QlikView in an organization. However, for me, the best approach is dimensional modeling—often called Kimball dimensional modeling—as proposed by Ralph Kimball and Margy Ross in the book The Data Warehouse Toolkit, John Wiley & Sons, now available in its third edition.

Some other methodologies, most noticeably that proposed by Bill Inmon, offer a "top-down" approach to data warehousing whereby a normalized data model is built that spans the entire enterprise, then data marts are built off this to support lines of business or specific business processes. Now, QlikView can sit very readily in this model as the data mart tool, feeding off the Enterprise Data Warehouse (EDW). However, QlikView cannot implement the normalized EDW.

In my opinion, Kimball dimensional modeling, on the other hand, is right up QlikView...

For many dimensions, we are not usually worried about changes being made in the underlying system. If a salesperson gets married and their surname changes from "Smith" to "Jones," we just reload the QlikView document and the new surname will appear in the selectors. However, if the same person changes from the inside sales team to the northwest sales team, just updating the data means that sales attributed to that salesperson will no longer get attributed to the correct team.

These changes to the dimensions do not happen very frequently and are called slowly changing dimensions (SCDs). Kimball defines eight different methods of handling SCDs, from Type 0 to Type 7. The first example discussed previously, the change of surname, is an example of Type 1—simply update the value (Type 0 says to use the original value). The second change, where the sales team is updated, should be handled by Type 2—add a new row to the dimension...

In data models designed around business processes, we will often have just one source fact table. If we have additional fact tables, they tend to be at a similar grain to the main fact table, which is easier to deal with. Line-of-business documents may have fact tables from lots of different sources that are not at the same grain level at all, but we are still asked to deal with creating the associations. There are, of course, several methods to deal with this scenario.

Fact:
Load * Inline [
Date, Store, Product, Sales Value
2014-01-01, 1, 1, 100
2014-01-01, 2, 1, 99
2014-01-01, 1, 2, 111
2014-01-01, 2, 2, 97
2014-01-02, 1, 1, 101
2014-01-02, 2, 1, 98
2014-01-02, 1, 2, 112
2014-01-02, 2, 2, 95
];

Join (Fact)
Load * Inline...

One of the basics of dimensional modeling is the ability to drill between models to answer questions. There are a few situations in QlikView that make this an important consideration, for example:

QlikView handles this quite well with the document chaining function. As with any other system where you need to drill across, the ability to do so is entirely dependent on the use of conformed dimensions.

To enable...

This chapter has had a lot of really important information. We started by reviewing what you should already know about associating data. You learned important information about keys and autonumbering and the level of calculations used in QlikView. We also reviewed the different methods of stitching data together—join, concatenate, and mapping.

We then moved on to talk about dimensional data modeling, fact and dimension tables, and best practices from Ralph Kimball. You learned how to handle SCDs and multiple fact tables and how to drill across tables.

The previous chapter dealt with loading data for performance. The next chapter will help us continue our learning of how best to load data, building QVD layers, to support a dimensional modeling approach.