Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
- Qlik Community
- :
- Discover
- :
- Blogs
- :
- Product
- :
- Design
- :
- Let's Dissect the Qlik Engine API - Part 4: Hyperc...
Michael_Tarallo
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
My friend, Øystein Kolsrud - Software Architect at Qlik, is back with part 4 of the Qlik Engine API fundamentals: Hypercubes
Previous articles:
- Part 1: Let's Dissect the Qlik Engine API - Part 1: RPC Basics
- Part 2: Let's Dissect the Qlik Engine API - Part 2: Handles
- Part 3: Let's Dissect the Qlik Engine API - Part 3: Generic Objects
Introduction
The topic of this post is the concept of "hypercubes", which can sound rather intimidating at first, but which is simply a very flexible and convenient way of extracting data from Qlik Sense.
Background
We have in previous posts seen some basic examples of how to perform evaluations in Qlik Sense. It is theoretically possible to use the approach described in Part 3 (where a generic object is used to group evaluations) to extract any information we want from the system. In fact, it would be possible to rely solely on the "Evaluate" method described in Part 2 along with selections to produce any imaginable set of data. But the obvious downside to this approach is performance. A visualizations would typically have to call that method an enormous amount of times, maybe even millions, in order to fully display a chart. Doing so is of course not feasible, and this is where the hypercube comes into play.
The term "hypercube" is a concept taken from mathematics and is a geometric concept denoting a cube of a generic number of dimensions. The good old cube as we know it is a hypercube of 3 dimensions. The square is a hypercube of 2 dimensions. But in mathematical terms, there is no reason why it would not be possible to express higher order cubes, using any number of dimensions. For more reading on this (and some fancy animations), Wikipedia provides a good starting point:
https://en.wikipedia.org/wiki/Hypercube
But that's all I'll say about mathematics for now. In Qlik Sense, a hypercube is simply an interface for defining a set of data to extract. And as far as this post goes, it is sufficient to simply think of a hypercube as a table.
Defining a hypercube
I will start with an extension of the example used in the previous post where we used the "StringExpression" construct to evaluate the sum of sales. In that example we created a generic object with the following set of properties:
{
"qInfo": {
"qId": "81bac84a-cb6a-48ac-8139-298b6e4913c5",
"qType": "myKpi"
},
"myProp0": { "qStringExpression": "=Sum(Sales)" }
"myProp1": { "qStringExpression": "=Sum(Sales)/Count(Month)" },
}
The resulting object gave us an interface for performing computations, and we could use selections together with "GetLayout" to evaluate the sum of sales for a particular employee. This approach would be fine for a simple KPI, but for a more complex visualization we would typically like to do something more interesting like for instance listing the sales for all employees at once. In that case, what we need is to be able to construct a table with the employee as one column and the expressions to evaluate as the other columns. That way we could tell the engine to fill in the cells of the table and extract all the information in one go. This is exactly the purpose of a hypercube.
To use a hypercube, we need to add a definition of our hypercube to the properties of a generic object. And just like an evaluation can be added by using the reserved "qStringExpression" property, a hypercube can be added using the reserved property "qHyperCubeDef". The hypercube is, as I have already mentioned, a very flexible tool which means that there is a huge amount of settings available for it. I will only cover a very small portion of what you can do with hypercubes in this blog series, but you will find the full definition of the structure for defining them here:
The two most important settings for a hypercube are "qDimensions" and "qMeasures". These are the properties that define the dimensions that the hypercube should consider, along with the measures that should be computed based on the values of those dimensions. In our case we will have one dimension (the field "SalesRep") and two measures (the sum of sales, and sum of sales per month) which means our call to "CreateSessionObject" will like like this:
{
"jsonrpc": "2.0",
"id": 17,
"method": "CreateSessionObject",
"handle": 1,
"params": [
{
"qInfo": { "qType": "myCube" },
"qHyperCubeDef": {
"qDimensions":
[ { "qDef": { "qFieldDefs": [ "SalesRep" ] } } ],
"qMeasures":
[ { "qDef": { "qDef": "Sum(Sales)" } },
{ "qDef": { "qDef": "Sum(Sales)/Count(Month)" } }
]
}
}
]
}
The engine replies with the following message giving us a handle for interacting with the new object:
{
"jsonrpc": "2.0",
"id": 17,
"result": {
"qReturn": {
"qType": "GenericObject",
"qHandle": 4,
"qGenericType": "myCube",
"qGenericId": "3cb898dc-b7dc-44ed-ba83-c27fae5d0658"
}
},
"change": [ 4 ]
}
Getting data
Now that we have an object containing a valid hypercube definition, we can extract data from it using the method "GetHyperCubeData":
This method takes two arguments, of which the first is a path to where in the properties structure of the object the hypercube is defined. In our case the definition is found at the root level, so our path will be "/qHyperCubeDef" just like the example in the reference documentation. The second argument contains a definition for what data to retrieve from the cube. This is defined as a set of pages each defining a rectangle of the data of the table defined by the hypercube. The definition of the "NxPage" structure can be found here:
https://help.qlik.com/en-US/sense-developer/November2020/APIs/EngineAPI/definitions-NxPage.html
In my very simple example, the table contains three columns (one for the dimension and two for the expressions) and only two rows as my data contains only two sales reps. So to get all data for this hypercube, I will use a page starting at the top left corner, with width set to 3 and height set to at least 2 (I somewhat arbitrarily choose 20 in this case):
{
{
"jsonrpc": "2.0",
"id": 21,
"method": "GetHyperCubeData",
"handle": 4,
"params": [
"/qHyperCubeDef",
[ { "qLeft": 0,
"qTop": 0,
"qWidth": 3,
"qHeight": 20
}
]
]
}
The response I get is, as the reference documentation states, an array of instances of the structure "NxDataPage":
https://help.qlik.com/en-US/sense-developer/November2020/APIs/EngineAPI/definitions-NxDataPage.html
The full response to the call to "GetHyperCubeData" does in our case look like this:
{
"jsonrpc": "2.0",
"id": 20,
"result": {
"qDataPages": [
{
"qMatrix": [
[ { "qText": "Amalia Craig", "qNum": "NaN",
"qElemNumber": 0, "qState": "O"
},
{ "qText": "261142", "qNum": 261142,
"qElemNumber": 0, "qState": "L"
},
{ "qText": "2720.2291666667", "qNum": 2720.2291666666665,
"qElemNumber": 0, "qState": "L"
}
],
[ { "qText": "Amanda Honda", "qNum": "NaN",
"qElemNumber": 1, "qState": "O"
},
{ "qText": "253689", "qNum": 253689,
"qElemNumber": 0, "qState": "L"
},
{
"qText": "2698.8191489362", "qNum": 2698.81914893617,
"qElemNumber": 0, "qState": "L"
}
]
],
"qTails": [ { "qUp": 0, "qDown": 0 } ],
"qArea": { "qLeft": 0, "qTop": 0, "qWidth": 3, "qHeight": 2 }
}
]
}
}
The most important part of the returned data is what we find in the property "qMatrix". That is where all the data is found. The matrix consists of an array of rows (two in this case), and each row contains an array of cells. The cells contain values in the same order as the dimensions and measures are defined for the hypercube, so cell position number 0 contains the dimension value and position 1 and 2 contain the measures computed for that dimension.
More to learn
I will stop here for now, but I'll come back to hypercubes in later posts as this is a very important concept in Qlik Sense. Pretty much every visualization will be based on an object containing a hypercube definition of some form. In fact, some visualizations will contain multiple hypercubes. An example of this is the map visualization which contains one hypercube for each map layer. Another example is the box plot visualization which contains one main hypercube which is based on the dimensions and measures the user defines, and a set of auxiliary hypercubes constructed under the hood that are derived from the main hypercube but used to compute the whiskers and outliers.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.