Evidence of Qlik Layered into the Fossil Record.
Proper QlikView architecture is one of the pillars of a successful implementation. When us Qlik developers start out, we feel accomplished merely loading data into an app and displaying a few sexy charts on the screen. But obviously, as we scale up within our environment, this one stop approach does not work anymore. There are many variations of a layered Qlik architecture with associated pros and cons. So I thought it would be a good exercise to lay out all the pieces so you can decide which layers you will want in your implementation. This article can be applied to both QlikView and Qlik Sense architecture.
A Note About Data Retention in the Levels
Generally for apps that serve to stage data into a QVD (Extraction and Transformation), there is no need to retain that data in the QVW. This means that at the end of these scripts, you should drop the tables out of the app. This will make the saving of your apps very fast and will also speed up the opening of the app for the next reload.
Usually I do collect meta-data and display it on the main page as explained here, but generally there are no other visualizations in these apps. End-Users should never see them anyway.
The idea of an extraction layer is to have a layer of QVWs with the sole purpose of loading raw data from our databases and storing that data to QVDs. There are no transformations at all, not even renaming of fields.
This is typically the first layer I create because it has the wonderful of advantage of making development more efficient. Since QVDs load much faster than data from a database, I can create the QVDs once and then rely on the QVDs for my primary development, turning the connections back to the database only before promoting the app to production. Typically, I use 2 sets of data source script lines for each load and simply comment and uncomment as needed.
The extraction layer is also the primary way in which we scale Qlik. The extraction layer provides us a duplicate of the database tables which we can then load as many times as we want into subsequent Qlik apps without having to go back to the original database. Adding incremental load logic to this makes the extraction layer even more efficient, limiting the database pull to only the most recent or changed records. This is your primary weapon in the scalability war. I believe all implementations should have this layer.
Input: Original Data Sources
Output: Extracted QVDs
Pros: Speeds development, scalability mechanism, isolates db connections
Cons: Very Minor – Up-front time to set-up apps and tasks, Multi-Step data refresh process, Lineage confusion
The transformation layer is a way to massage the data for straight-forward analysis. This is where you will rename fields, apply business logic, do some dimensional joins, and possibly aggregate fact tables to differing levels of granularity. Generally, we will load data from our extraction QVDs, complete our transformation steps and store the resulting tables into Transformed QVDs.
In this Qlik Sense architecture layer, I usually add in any non-database data sources rather than including them in the extract process. It serves little purpose, for example, to extract the data from a spreadsheet into a QVD. There is no reason why we can’t start with these kinds of sources in the Transformation layer.
We should also talk a little about granularity. Mostly when it comes to transactional data, we load all the rows into QlikView because we want to be able to drive from summary all the way down to the smallest detail. But many dashboards are better served with a summary level of data. Executive Multi-Subject Dashboards might fall into this category. So if fact tables should have versions that are aggregated to one level or another, this should also be done in the transformation layer.
The major advantage of the Transformation layer is that we can use one set of business logic and conventions to drive the entire implementation. It is better to apply logic once, than to have to repeat the development in eight different Qlik scripts. And because all renaming is done in one location, we can help eliminate confusion over naming conventions that would naturally differ between apps.
Input: Extracted QVDs
Output: Transformed QVDs, Aggregated QVDs
Pros: Maintenance efficiency, scalability mechanism, universal business definitions
Cons: Up-front time to set-up apps and tasks, Multi-Step data refresh process, Lineage confusion
Traditionally, the UI has been the final layer. This is where we load the transformed QVDs and create our UI Dashboards with the charts and graphs the client has requested. The concept of the Data Mart layer is simply separating the data model piece from the UI. We will load the Transformed QVDs and create any needed concatenated keys, apply autonumber if desired, add our calendar, and any other unassociated tables needed. This creates a completed data model for the app as evidenced in the Table Viewer. But no User Interface will be added. Please note that in this layer, we will retain the data in the QVW.
Instead, we create yet another QVW that Binary Loads the Data Mart app and then create the user interface, complete with all the necessary charts and graphs and the professional appearance we are all used to experiencing in Qlik.
There are a case-specific advantages to this layer. Firstly, it serves to isolate development from designer activities. Using this method, we do not need to give QVD read or database connection permissions to Qlik designers. Secondly, we can reuse common Qlik data models in multiple applications. One data model can support an executive view and an analyst view.
My view on the DataMart layer is that some organizations can make good use of this, while others may not see an advantage.
Input: Transformed QVDs
Output: QVW Data Model
Pros: Isolates data model development from dashboard design, data model reusability
Cons: Might outweigh Pros? – Up-front time to set-up apps and tasks, Multi-Step data refresh process, Lineage confusion, extraneous data potential
QlikView Architecture: Putting it All Together
Below are some figures to help you visualize the different layers within the entire architecture. If you want to remove a level, you will simply combine two QVW rows into one, eliminating any QVD rows in between if there are any.
I encourage you to spend time designing your Qlik architecture appropriately, thinking about the future of the tool in your business. It is well worth the up-front effort, and also a little more of a hassle to implement if you have already gone down a seemingly simpler road. You might also enjoy an article on Living Qlik highlighting techniques for looping through files in your load script. Do you have thoughts on this topic? Would love to hear them.