We will now provide some useful tips for dealing with more complex data models, specifically those used for dealing with multiple fact tables in a single QlikView document.
We will take a hands-on approach and we will continue using the Airline Operations document that we have previously used. The design challenge we will describe in this section will be aimed at integrating a new fact table into our data model, one that contains Airline Employment Statistics.
The Airline Employment statistics table
The table we will be adding to the data model contains monthly information about the number of employees per airline, separated by full time and part time, and also showing the equivalent total of full time employees.
The table contains the following fields:
As you can see, all of the dimension fields are already part of the Airline Operations data model and the only new fields are those related to the actual measurements of employment.
Integrating multiple fact tables into one data model is one of the main challenges we can come across when designing a data model. This is, in fact, a very common scenario and we will present two ways of solving the task at hand:
Let's see how each of these methods work and how the integration of this table will take place.
Concatenating fact tables
The first approach suggests that we should combine the two fact tables into one.
This is a valid approach as all of the dimension fields of the new table are also present in the initial table.
We have already used the concatenate function in the previous example, and we will use it again to combine both fact tables.
This method will keep our data model simplified because there will be virtually no additional tables in it. However, we should keep in mind one important consideration: structural asymmetry.
Although it is true that all of the dimension fields contained in the Employment table are present in the Main Data table, the opposite is not true: not all of the dimension fields in the Main Data table are present in the Employment table. There is an asymmetric structure between them.
This structural asymmetry needs to be kept in mind when creating the frontend of the document, simply because there will be analyses across certain dimensions that will be impossible to make. For example, we will not be able to create a chart that shows the number of employees by airport, as that dimension (airport) is not present on the Employment table, and there is no way to get that data into our document. However, we will be able to create, for instance, a chart that shows the number of employees by airline or by month or by year.
In hand with this difference in table structure, there is another point we should address: the Main Data table already contains Year, Month, and Quarter dimensions, and those dimensions are included as listboxes in the user interface to allow the user to filter through the data. However, the Employment data only contains the Year and Month fields, but not the Quarter field.
There is something we can do about this: simply add a calculated field to the Employment table before concatenating it to the Main Data table. In the end, both table segments contain the Quarter field. If we do not add the Quarter field to the Employment table, any user selection on this field will automatically exclude all of the employment data.
Natural and forced concatenation
Moving on to concatenating tables, as we saw in the previous chapter, there are two ways in which this operation can take place in QlikView:
Let's take a moment to revisit this subject and see how we can apply it to our fact tables.
Natural concatenation happens when two tables are loaded with exactly the same structure, that is, they contain the exact same fields (in both the number of fields and field names).
When this condition is met, QlikView automatically combines all tables that are similar and treats them as one logical table.
An example of this is shown in the next script:
As you can see, with the preceding script we are loading two tables into the data model and both tables are loaded from a different QVD file. The first table contains data for the year 2011, while the second table contains data for 2010. As they both have the same structure (field names), they will automatically be merged into one logical table in the QlikView data model. Also note that the order in which the fields are defined in the load script is not relevant for natural concatenation.
In case we want to avoid the default behavior whenever this circumstance is present in the script, we can add the No Concatenate keyword as a prefix to the Load statement of the second table so that QlikView continues treating them as separate tables in the data model.
Forced concatenation happens when we explicitly define that two tables should be combined into one logical table in the data model, even if they don't have the same structure or field names.
This is the method we have used earlier in this chapter, where we used the Concatenate prefix to add the 'unknown' Aircrafts to the Aircrafts dimension table. It will be useful in this case as well as there are only a few shared fields between both tables.
As we also saw earlier, with the Concatenate prefix we can specify to which of the previously loaded tables the new table should be appended. This is done by adding the name assigned to the target table, enclosed by parentheses.
The following script shows how to explicitly concatenate two tables that do not have the same structure:Sales:LoadRegion,Month,Year,[Total Amount]From Sales2 011.qvd (qvd);Concatenate (Sales)LoadRegion,[Total Budget Amount],Month,YearFrom Budget2011.qvd (qvd);
If we don't add the name of the original table (Sales, in the preceding example) to the Concatenate prefix, the new table will be concatenated to the table loaded immediately before it, no matter what that table is.
We recommend, as a best practice, to always explicitly define concatenation by adding the name of the target table to the Concatenate prefix, even if both tables have the same structure and would naturally be combined. This is mainly to avoid confusion and makes it easier for other developers, and yourself, to understand the script.
Concatenating the Employment Statistics table
Now that we've revisited the subject of table concatenation and described the considerations that we must keep in mind, let's put it into practice. We will be integrating the Employment table into the already designed data model for the Airline Operations document.
Follow these steps:
As no actual tables have been added to the data model, the resulting model will look identical to the one we had before adding the script. The only difference will be the new fields that are included at the end of the Main Data table that correspond to the employment measures. Use the Table Viewer dialog to verify that the new fields have been added.
We have described the first approach for dealing with multiple fact tables in a data model. In the next section, we will present yet another option along with its pros and cons, so that you, as a Developer, can better decide which one will suit your needs best.
To continue, save and close the Chapter (Data Modeling Best Practices) _ Concatenated tables.qvw document.
Working with link tables
When we include two or more fact tables in a single QlikView document, it's very likely they all are somehow related and will, therefore, have some common dimension fields among them. However, as we've outlined before, in a QlikView data model, two tables should not be associated through two or more fields because it would generate a synthetic key.
So how do we incorporate two or more fact tables into one data model and treat them as two separate logical tables while, at the same time, avoiding the synthetic- key issue? At first sight, it can seem like both options are mutually exclusive, but there is a workaround which is to create a Link Table.
As its name implies, a link table essentially "links" two or more fact tables by taking all common fields out of the original tables and placing them into a new one (the link table).The new link table contains all possible combination of values for that set of fields and, through a unique key, is associated to the original tables.
A link table example
Take, for example, the following scenario:
If we let QlikView make the default associations, we would get the following data model:
From the preceding image, we can observe that a synthetic key has been created because two fields (Employee id and Department id) are shared between the Operations table and the Payroll table.
To solve this challenge, we will remove the synthetic key by using a link table. As was mentioned earlier, the new link table will hold all combinations of the key fields that are common for both tables. We should also create a new compound key to connect the three tables.
Essentially, the link table replaces the synthetic key table, and in some cases, the result in both performance and design is exactly the same. However, it's a good practice to always "clean" the data model and remove all synthetic keys.
Our re-designed data model will look as follows, after applying the appropriate changes:
As you can see, the synthetic key has been removed and the Payroll and Operations tables are now connected to the Department and Employees dimension tables indirectly and through the link table.
When designing a data model using this method, we should always consider the following:
Creating a link table in the Airline Operations document
Now that we've described the creation of link tables and its uses, let's put it into practice in our Airline Operations data model.
Remember that, in the previous section, we already added the Employment Statistics table to the Airline Operations data model by using the Concatenation method. We'll now do the same, but this time using the link table method. You can compare each of them and see for yourself their pros and cons.
Follow these steps:
1. Assign a name to the table: Link Table.
2. Create a list of distinct combinations of all shared fields from the previously loaded Main Data table, including the new %Key Field, by performing a Resident Load (more on Resident Loads in Chapter Advanced Data Transformation).
3. Create a duplicate of the %Key Field attribute and name it %temp Key Field.
4. Concatenate a new list of distinct combinations of all shared fields from the previously created Employment Statistics table, including the new %Key Field attribute and adding a calculated field Quarter. From this new list we exclude all combinations that already exist on the first list earlier, using a Where clause.
5. Remove the %temp Key Field field from the data model, as it was only to be used in the Where clause.
6. Remove the shared fields from each fact table, except the %Key Field attribute, as they will now be stored in the link table.
13. Save the changes we made and reload the script. The new data model will be created and look as follows:
Two fundamental recommendations can be made regarding the creation of link tables:
Proceed to save and close the document.
Finding a balance
We have outlined two ways for dealing with multiple fact tables in a data model. If we were to ask which of these methods is better, we would need to say, again, "it depends." There is no definite best, and the decision about which to use will depend entirely on the specific scenario where it has to be implemented.
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.
Qlik View Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|PL/SQL Interview Questions||MSBI Interview Questions|
|VBA For Excel Interview Questions||SAP BO Interview Questions|
|SQL Database Interview Questions||Qlik View Interview Questions|
|R Programming language Interview Questions||Pentaho Interview Questions|
|Advanced SAS Interview Questions|
Qlik View Tutorial
Seeing Is Believing
Data Modeling Best Practices
Basic Data Transformation
Set Analysis And Point In Time Reporting
Advanced Data Transformation
More On Visual Design And User Experience
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.