We at IPC Global think that Qlik is the best Data Warehousing platform in the world. The market has been convinced that Qlik is one of the finest visual analytics and reporting platforms in the world. We happen to agree with that too. However the secret sauce, the tenderloin of Qlik, is its associative data model. The associative data model is built on data files called QVDs. The data files, when implemented, managed and governed properly constitute our claim that Qlik is the best Data Warehouse platform in the world. Let’s see how.
In part one, we’ll cover the ETL process (Extract, Transform, Load). This is something that most IT folks are familiar with, but the way Qlik accomplishes this sets it apart.
Let’s start by saying something that is obvious to us in the Qlik ecosystem, but we get asked about all the time: Yes, Qlik can connect to your data. Whether you’re using SAP, Oracle, Excel files, industry-specific ERP systems, etc, Qlik can connect to it.
Qlik can connect to virtually ANY structured or semi-structured data source: tables, csv files, excel files, pdfs, web service data, websites, text files, etc. No, you can’t interrogate an x-ray or a picture, but give Qlik something with some text and you’re off running. For one client, IPC worked on a time and expense system that looped through text files to find instances where people had mentioned certain key words (ex. Per diem, allowable) and created structured data sources that counted these instances, allowing these to be pulled into their internal auditor dashboards.
Qlik is the fastest extract engine in the world. Now you might be thinking that’s a bold statement, how can I say that? Well, we do the same thing everybody else does: we connect to data via ODBC, OLAP, web service call, rest API, etc. But the key here is that Qlik does not require the source data to be flattened out or pre-aggregated. When we get to connect to the data source directly, we can turn on the hose and let it run as fast as it can. The database server can throw tens or even hundreds of thousands of records per second at Qlik.
How is Qlik’s process different from other data warehouses? Sequel-based, structured data warehouses require flattening and pre-aggregation. Yes, database engines like Sequel Server, Oracle, and DB2 are designed to accomplish this flattening, but when we talk about moving all the data from the source to the data warehouse, that’s a lot to ask of a database engine. The database engine is intended to control the daily operations of the organization like taking orders, shipping products, doing inventory control, productions, and human resources, and now you’re over tasking it with these advanced extract functions. That’s a burden on your database engine, and worse, that burden is something the organization will feel. By skipping the pre-aggregation phase, Qlik builds its data warehouse faster, decreasing the burden on the organization’s systems and personnel.
Transform & Load
In this phase, we take the data and systematically engineer it into a single, consistent source of truth. Most think of this as a data model, but at IPC we think of this as a QVD or QVW, a Qlik data file that has many tables engineered to be a single, reusable source of truth. For example, an order entry business would have a file called Customer Master, Order, Shipment, etc. In the source system, these might be 5, 7, or even 12 tables joined together. In Qlik, each is an individual flattened data object ready to be reused in any application. This data can be taken and stored in one data model multiple times, and can be used in multiple data models.
This approach to a single source of truth facilitates true self-service. Our competitors will tell you that self-service means you can click on a dashboard and add a new chart. That’s something almost all BI tools can do. True self-service is to be able to grant users access to your data store, so if I go in and want to create a new application that uses that customer master and order objects, I can be confident that the data is blessed by IT. The difference between Qlik and our competitors is we build simple objects with easily-identified names so users can go shopping in the data store and get the objects they need to create their own applications.
In part two, we’ll cover storage, partitioning data, and how modeling in Qlik supports a multitude of users’ analytics and reporting needs.