Fact sheet data models combine facts from various perspectives. The customer fact sheet data model combines information from our sales, marketing, working capital, and operations perspectives. However, we don't necessarily include all the facts that are measured in each perspective. In this example, we store the following events in our data model's fact table:

There are two principal ways to combine all of these events into one data model in QlikView. The first option is to combine all these events into one fact table, while the second option is to create a link table between various fact tables. The link table contains existing key combinations of every fact table, and serves as a bridge between the separate fact tables and a common set of dimensions.

On one hand, the link table creates an additional layer of links in the QlikView data model that often slows the performance of our analysis and data visualization. On the other hand, combining all these separate fact tables into one all-inclusive fact table may drastically increase the application's use of RAM memory if the tables contain a large number of columns.

For this example, we choose to combine all these fact tables into one table. As this consolidated table is directly linked to the dimension tables, it is more likely to have better performance, unless, in the extreme case, it creates an extremely wide fact table with numerous columns. If performance becomes an issue, we test this fact table against the option to create a link table.

Whether we use one fact table or a link table, we may confront a situation where the data volume is too much to include each detailed transaction. In this case, we only add fields and the level of detail that we know that we are going to use. In our following example, we explore the ideal case of when we can add all customer-related data at the most detailed level.

We've already used most of this data model's tables in previous perspectives. We add a few tables called island tables that have no relationship to this data model. These tables store data that helps us create certain elements of our user experience. For example, we are going to allow business users to choose the currency and language of the QlikView application. We also allow business users to choose from a list of metrics and dimensions in order to create their own reports on the fly:

We have to be careful when performing analysis over a data model that mixes transactional facts with periodic and accumulating snapshots. For example, the pitfalls that we can avoid here are: while we can sum transactional facts over various months or years, we cannot sum periodic snapshots over time. The sum of several months' balances does not serve any analytical purpose. We can prevent any incorrect summation using set analysis to select the latest month's balance even when the user selects more than one month.

In the case of the accumulating snapshot, the challenge is to determine which date we need to use for our analysis. In the customer fact sheet, we expect the user to select a certain period using fields from the common calendar. In an expression that requires that we analyze the average time delivery for a certain month, we use set analysis to clear the common calendar selection and transfer this selection to the corresponding delivery calendar fields.

In addition to the 7Ws table, we create the following table to clarify how each event is recorded in the fact table. The manner in which we've classified most of the facts should be obvious from the way we've used them in their corresponding perspectives. The one event that is not so clearly defined is a sales opportunity. In other data models, we may handle the sales opportunities like a traditional accumulating snapshot that is similar to the sales operations process. However, in Chapter 4, Marketing Perspective, we recorded each stage in our sales pipeline as a separate row instead of a separate column. This treatment is similar to that of a slowly changing dimension, but, instead of a dimension, this is a long-lived event.

Even though each stage is stored by row and not by columns, we treat it the same as any other accumulating snapshot. For example, we cannot sum the amounts between different stages; however, we may want to analyze how the amount changes as we progress through the sales pipeline process:

Finally, when we mix several events together, as we did for our working capital perspective, we tend to have a fact table with mixed granularity. We use the following table to visualize at what level of granularity we can analyze each metric:

In our example data model, the A/R Invoice Balances event is the least detailed and cannot be viewed by the Date, Sales Person, or Item filters. Also, we cannot analyze events, such as activities and sales opportunities by Item.

Now that we've reviewed our customer fact sheet data model, let's design how we want to visualize our customer fact sheet.