Summary: I demonstrate my latest “Data Browser” sheet for use in Qlik data modeling. Download here.
Today I want to share the latest version of my “Data Browser” sheet.
What’s a “Data Browser”? It’s my name for something you may already have. The ubiquitous “System Sheet” or sometimes “Profiler” where you may have listboxes or charts utilizing the system fields “$Field”, “$Table”, “$Rows”. These fields are part of the shadow data model that hold useful metadata about the data loaded in your app.
Some very useful example profile sheets have been shared in the community over the years.
You can download the latest QlikView and Qlik Sense versions of my Data Browser here.
For QV, I just copy the objects into my new app in one go. For QS, I either copy the charts one-by-one or if I’m lucky they are already in my template (who’s going to build the sheet copy extension?) .
I’ll do this walk through with the QV version, the QS version is similar.
When I make selections in $Field I can see values and frequency counts in a listbox. After selecting for example the “Sales” field, the values and frequency counts are shown in a listbox. I also have a histogram and some descriptive statistics about the field values.
I can select field values and drill into data using this sheet or my application sheets. For example, I might want to select the outlier high Sales value, then select Product in $Field to find out what Product is associated with this Sale, clear the Sales field to see what other Sales look like for this product and so on.
There are several properties such as Null Count (Information Density), field alpha/num content ($tags) that I can get from the built in Table Model Viewer. The Viewer requires me to examine one field at a time. In the chart below I get an overview of those properties of interest. Because it’s a chart, I can use sorting and selecting to focus.
Something I don’t get from the Table Viewer is the difference between numeric count and text count for a field. I like to surface this problem (highlighted in yellow) early in my modeling.
One of my favorite features is the “Value Association” chart below. It’s likely a favorite because it took me a long time to work out the expression!
In Table Viewer we use “Subset Ratio” to understand where we have connected and un-connected data in our model. Subset Ratio is limited to reporting the relationship between key fields. It can’t tell me how data field values in this table associate to data field values in other tables. Subset ratio, like other stats in the Viewer, does not respect selections. For example, if I select a specific Customer, how many SalesReps are linked to the Customer?
The chart below (highlights added) covers all these use cases and can also surface problems in the model.
I’ll start by selecting a field central to my model, in this case “OrderID”. I then sort by the “Pct” column. This column represents the percentage of field values associated to the selected field,
“OrderID”.
In the green highlight, I’ve called out a Table “Sales” that has zero association with Orders. Something to look into.
In the orange callout, I can see that only 19% of my Employees are associated with Orders. That might be a candidate for trimming the Employees table when loading.
In the red callout, I see something puzzling. Only 47% of my OrderDates are linked to Orders. That may be ok, I would need to review the data. What looks not ok is that WeekDay Pct is also 47%. I would have expected something like 5/7 or 6/7. No fraction of weekdays would equal “47%”. And there are 1092 values for WeekDay…something is off.
I’ll select WeekDay from the chart and examine the values in the listbox. Aha, the Weekday values were created incorrectly. They were created with a Date() function instead of the correct WeekDay() function.
I used to hide the sheet before production but now I generally leave it in as I and others find it useful in production as well.
Besides the Table Viewer, another Qlik modeling tool I really like is Catwalk. If you haven’t used Catwalk I encourage you to check it out. I won’t go in to explaining Catwalk as it has an excellent tutorial built in.
Do you have any favorite profiling or modeling tools to use with Qlik?
-Rob
