While a few years ago we did not work with datasets containing more than a few millions of records very often, nowadays we regularly work with datasets with tens of millions records or larger. A few years ago we would load the whole dataset with all details into a single Qlik app. But with growing datasets and growing number of users we need to consider more carefully if every single user needs access to each and every single piece of detail, all the time. In fact, most of the time users are OK with some level of aggregation and only need to analyze details in specific cases. Including all those details would not bring value, on the contrary, it would negatively impact performance, calculation times and thus user experience.
To prevent these negative impacts you can create one or more aggregated apps (depending on your data) for regular use and link these apps with a simple detailed app containing more granular data (or even a full dataset) by App Integration API, which is a Qlik Sense term for "Document Chaining" we new from QlikView.
How does App Integration API work
The magic of App Integration API is hidden in creating a link between to apps. However, the link is nothing more then a URL created in a specific – and frankly quite simple – way which will allow you to copy specific selections from an aggregated app to a detailed app. Users start their analysis in the aggregated app and when they need to analyze details for specific selections, they can click on a button which will open the detailed app and apply the same selections. So there are basically three things we need to handle:
- Create a navigation button which can open a configurable URL
- Know which app to open and how
- Know which selections to carry to that app
Create a navigation button
Qlik Sense does ship with out of the box navigation buttons, so we need to dig into Qlik Branch. I prefer Sheet Navigation + Actions extension by Stefan Walther, which is now also certified by Qlik. You can find it here: http://branch.qlik.com/#!/project/56728f52d1e497241ae698a0. This extension allows you to create a simple button with configurable URL address.
Which app to open and how
Here’s where the real “magic“ starts. The main building block of App Integration API is the Base URL, and URL to a Qlik app. When you open a couple of Qlik Sense apps and look at the address bar of your browser you will notice that all URLs share a common structure:
This URL says which app is currently opened (defined by appid parameter) and which sheet you are looking at (defined by sheetid parameter). You can build this URL in several ways, the easiest is to open the target app on a selected sheet and copy that URL from the address bar of your browser (or you can look for appid and sheetid in the QMC or Dev-Hub).
Which selections to carry over
We aim at fast calculation times, therefore we want to limit the dataset considered for calculating object. This means making selections. But users already made their selections in the aggregated app and we do not want to force them to repeat all those steps again. Luckily, we can carry over selections by expanding the Base URL with select parameter by which we can define fields and respective values for selections:
Here’s an example of a final URL:
This URL will open an app with ID 096a73ca-48de-4d0e-be30-6bf6b1c90fce, it will navigate to sheet with ID bEmvU and select values “California“ and “Colorado“ in a field State. Of course, we want to define values for selections dynamically, so our calculation would contain something like this:
='select/State/['&Concat(Distinct State, '];[')&']'
You can repeat the select parameter if you need to apply selections in multiple fields. Also, it is a good idea to generate a valid URL only after users select a few values (remember, we need to limit dataset considered for calculating objets in the detailed app). The resulting formula for generating an URL thus can look somewhat like this:
=If(GetSelectedCount(State)>0 AND GetSelectedCount(State)<3, 'https://sense.server.com/sense/app/096a73ca-48de-4d0e-be30-6bf6b1c90fce/sheet/bEmvU/state/analysis/select/State/['&Concat(Distinct State, '];[')&']', 'http://about:blank' )
There. Users would use smaller and more performant aggregated app for most of their analytic needs, saving resources and getting faster response times. In specific cases when they need to analyze more granular data they have an option to access the detailed app. The detailed app will be large and performance heavy, but thanks to carrying over filters they will again have reasonable response times. Plus, they should use this app only occasionally, so slower performance should not be such a pain.
You can find more info on the Qlik Help page: http://help.qlik.com/en-US/sense-developer/November2017/Subsystems/APIs/Content/app-integration-api.htm