Our Qlik Ambassador talks about Going Live(Query) with Qlik Sense

Background

Qlik provides an excellent platform for modern analytics, but it has been centered around the main core feature of the Qlik platform – loading everything into memory. This has been and still is an excellent solution.

But with the current change in the data landscape, where data is moving from on-premise to the cloud and with the ever increasing data volumes, there is a need and a desire from our customers to keep the data where it is in the data warehouses or source systems.

This is especially true for our customers and companies that are moving towards cloud data warehouses like Snowflake, Google BigQuery, Microsoft offerings, Amazon’s offerings, or other products on the market.

These data warehouse solutions provide a convenient and efficient way of collecting, storing, and scaling datasets to provide data across organizations. They allow the creation of cohesive models that scale across the organizations. With the data residing in the cloud data warehouse or large central system, it drives the need and desire to be able to reuse these models and investments in the Qlik platform.

Current state

To support analytics on live data, Qlik has a few different options:

The On-demand application generation (ODAG) solution – where the user can scope the data and then generate an app dynamically with that specific data. Qlik will then load that data into memory and everything in Qlik works as normal.
The Dynamic views – is a solution where you basically create an application with visualizations that are driven by a subset of the data and can be reused across multiple applications.
Direct Query – the last solution we also need to mention is the Direct Query functionality. It has been used and is used by customers for specific purposes. Although, it’s not that widely used because it has some limitations in which visualizations and aggregations are supported and how it interacts with the rest of the model and the Qlik dashboard. It’s a good solution for doing direct linking of the model for those special cases.
and now… LiveQuery! We will go into details about this new option below.

Qlik Sense LiveQuery – Our tool for the toolbox

As a consulting company, Stretch Qonnect is constantly in close dialog with our customer regarding their needs and requirements for their business. Where they are now and where they want to be in the future.

A common theme in relation to the Qlik platform is the desire and/or requirement to be able to reuse the investments in cloud data warehouses as the companies are moving their data there. Customers want to be sure that they can leverage their investment in Qlik together with these investments in the new cloud data warehouses.

This insight has led us at Stretch Qonnect to investigate what is possible with the Qlik platform and how we could support this use case. The goal is to “expand the toolbox” for the Qlik platform to encompass these cloud data warehouses and integrate them into the data landscape.

Therefore, Stretch Qonnect has developed the product LiveQuery for Qlik Sense. This is a service for Qlik that allows the user to directly execute SQL queries from the frontend and run those against the data warehouse in real-time, getting the answers back into the dashboard in real-time. This way you can drive a Qlik dashboard with Qlik Sense LiveQuery.

The Key Innovations

Automatically data model link

Automatically linking between the in-memory data model and the in-place data model allows us to support the visualization interactions where you can click on everything, and the dashboard responds, just like the users are expecting from their usual experience with Qlik Sense. The dashboard selections are reflected in the queries against the data warehouse.

Built-in memory cache

Another feature is the built-in memory cache. This enables caching of queries and allows the BI-department and IT-department to balance the need for live data against how often to query your underlying data source. This enables enhanced performance, as repeated queries hit the cache, but is also a tool for balancing costs, performance, and how fresh the data is.

Combine Qlik Sense LiveQuery by Stretch Qonnect with in-Memory calculation

The combination of calculations between Qlik Sense LiveQuery by Stretch Qonnect and in-Memory in the same calculation is supported. As an example, let’s say there is a requirement for a calculation of the “Total Revenue” from all the customers across every line in the ERP system divided by the numbers of customers. The ERP data is in a Snowflake data warehouse and the customers are loaded in-memory in Qlik Sense. This calculation can be implemented by a Qlik Sense LiveQuery to Snowflake where the data warehouse calculates the total revenue and divide it by a simple in-memory count of customers. Traditionally to support this calculation, you either must load all the data on the lowest level or produce an aggregated table with the relevant data. With LiveQuery combining data allows the users to have leaner data models on a higher level but still supporting the application with analysis on the lowest level.

Combine Qlik Sense LiveQuery across multiple data warehouses

Another feature is the option to connect to multiple warehouses and create measures combining calculations across them. For instance, we have a customer ranking KPI that is a composite of data, defined like:

“Total Revenue” / “Years Of Being a Customer” * “Retention Probability”

Total revenue is calculated from the ERP data from the snowflake data warehouse.
The customer “Retention Probability” is calculated from a Google BigQuery (or other) data warehouse driving machine learning.
Lastly, the measure “Years Of Being a Customer” is calculated from the in-memory data modeling in Qlik Sense.

This way of combining these data in Qlik makes it possible to do new things and enables the users to support dashboards that earlier were impossible or possible but might not be practical and not feasible in a traditional Qlik environment.

Use-case examples

Use-case: Real-time dashboard

Qlik Sense LiveQuery, by Stretch Qonnect, enables the use of actual real-time updated data. An example could be a call center or the sales support team that needs to access the customer’s newly create order but also needs to know the purchasing history of this customer, combined with a real-time calculated retention score based on the last purchase.

Use-case: Reduce the Qlik in-Memory data-model size

A key use case that we are seeing often is that an organization must limit the data scope (in time or for a sub-set of the organization) of their large application to make them perform well. This is especially relevant for applications containing fine data models like order lines, transactions, or events. Usually, the detailed level is only used when looking at a specific customer, or a small subset of the data model. For Qlik to support this all data needs to be in the Qlik data model. This leads to very large data models and the requirement for large Qlik Servers (high TCO).

What you can do with Qlik Sense LiveQuery, by Stretch Qonnect is not to load those detailed lines but instead just fetch them from the underlying data source at the time the user needs them. This way we can dramatically reduce the size of the Qlik data model from the lowest level to the next lowest level (or even better). A reduction of the data model size with a factor of 10 or better is often achievable, which gives a smaller application footprint, leading to faster load times and faster response in the application. This also enables the application developers to use more elaborate visualizations, increasing the insight presented to the user. This is achievable because the Qlik engine will respond faster on the same server.

Qlik Sense LiveQuery by Stretch Qonnect, enables you to archive these use-cases by directly querying against the data without having to move all the data into Qlik.

My Take

By Michael Faursby Ahlers, Qlik Partner Ambassador & Senior Consultant at Stretch Qonnect

I’m really excited about this. This technology and this solution enable Qlik to support new use-cases and increase performance and user experiences in many different use-cases.

To be clear this is not a replacement for in-memory data. The different solutions have their own use-case. This is another tool, but combining in-memory and in-place real-time data opens up Qlik to new insights and use-cases, complementing each other to create solutions that are better than “pure” real-time or in-memory solution.

Qlik Sense LiveQuery by Stretch Qonnect gives Qlik and the Qlik-community as a whole, the “green checkmark” regarding support and possibilities with Qlik Sense LiveQuery. This is useful when talking to customers, comparing to other products, selling Qlik or discussing the future IT-architecture.

The exciting question is; what new added value can you create with this for your organization?