We already had a small peek at the Table Viewer window in previous chapters. As this feature is very useful when analyzing our data models, let's take a more in-depth look. To illustrate, consider the data model seen in the following Table Viewer window:
The Table Viewer window can be opened by selecting File | Table Viewer from the menu bar, or by pressing Ctrl + T.
The table viewer shows the tables (boxes) and their associations (connectors). When more than two tables are associated via the same key, it will be indicated with a small blue dot in the connector line.
The layout of the tables and connection points can be changed by clicking and dragging the header and connection point, respectively. Tables can also be resized when hovering over and dragging their edges. Of course, it is not mandatory to create a clean layout for your data model, but it is recommended as it makes a diagram easier to understand.
A lot of information about our data model can be learned from the Table Viewer window.
When hovering the mouse cursor over a table header, a tool tip is shown to display the name of the table and the number of rows, fields, and key fields. This can be used to do a quick sanity check on the loaded data. Optionally, when a comment is set for the table (using the COMMENT TABLE script statement), the corresponding comment is also shown in the tool tip. For example, the following screenshot shows the tool tip for the Aircraft Types table:
When the mouse cursor hovers over a field name within a table, it gets even more interesting.
The following information is shown in the previous screenshot:
- The name of the field. Optionally, if the field is a key field, a qualifier is shown enclosed in square brackets. This qualifier indicates the following levels of key quality:
- [Perfect Key] indicates that every row contains a key value, and that all of these key values are unique. At the same time, the field's subset ratio is 100 percent. This qualifier should be seen in dimension tables, where every key should uniquely identify a single record.
- [Primary Key] indicates that all key values are unique, but not every row contains a key value or the field's subset ratio is less than 100 percent.
- [Key] indicates that the key is not unique. This qualifier is usually seen in fact tables, where the same dimension value may be associated with many different facts.
- Information density of the field, which indicates the percentage of rows that contain a non-null value.
- Subset ratio, which shows the percentage of all distinct values for a field in the table compared to all the distinct values for that field in the entire data model. It is only relevant for key fields since they are present in multiple tables and do not all share the same value. Subset ratios can be used to easily spot problems in key field associations. For example, when the combined total of subset ratios for multiple tables is 100 percent, this may indicate that there are no matching keys between these tables.
- Tags, which show the tags applied to the field. Some of these, such as tags that indicate if the field is a key field or tags indicating the data type of the field, are automatically generated. Other tags can be manually applied.
- Optionally, any comment set on the field is also shown.
While looking at ratios and such will give us some good insights into the data in our model, it is sometimes easier to just look at the raw data. By right-clicking on a table and selecting Preview, a preview of the first 1,000 table rows will be shown:
The menu of the Table Viewer dialog contains some other useful functions:
- The Export Image command lets us save a picture of the data model in PNG or BMP format.
- The structure of the QlikView document can be exported using the Export Structure button. This creates three text files: one for the tables, one for the fields, and one for the mappings between fields. Of course, these text files can be loaded back into QlikView for further analysis.
- We can also Print a picture of the data model or copy a picture of the data model to the clipboard using the Copy Image button.
- The Auto-Layout feature automatically arranges the tables. While it attempts to generate a coherent layout, in our experience it usually fails. Manually positioning the tables is still our preferred method.
- The zoom level on which the diagram is displayed can be set using the corresponding drop-down box. By default it is set to 100%.
Of special interest is the drop-down box that switches the view between Internal Table View and Source Table View.
Internal Table View is the default option and shows how the data is stored in QlikView. If synthetic keys are created, they are shown in this view. However, Source Table View shows how QlikView reads the data, and when synthetic keys are present in the model they are not shown in this view. Instead, multiple connectors between tables are displayed.
Source Table View, compare this to the Internal Table View shown at the start of this section