Qlik Sense Integration - Qlik Sense Repository Service API

Post Index

2023-06-19

  

Qlik Sense Integration

Qlik Sense Repository Service (QRS) API

The implementation by CURL

Qlik Sense Repository Service (QRS) API is a very useful API service which allows to perform similar actions like in the QMC console.  For example, it can import app, delete app, publish app and so on.  It can also trigger reload tasks, getting lists of app objects, managing security rules (systemrule), etc.  Basically, it is an interface to communicate with the backend Qlik Sense Repository Service and provides a full set of API calls for Qlik Sense integration and management.

There are two methods to connect to the QRS API.  One is via the proxy with port 443 and the other is directly to the API endpoints with the port 4242.  This article is focusing on the latter method to directly connect to API endpoints with the port 4242.

Pre-requisite

In order to call the QRS API, the following are required:

1. Qlik Sense Certificate

This is used for authentication.  The certificate is similar to an access card to allow to call the QRS API.

* Please keep these files in a safe location.  These files will allow the connection to the Qlik Sense QRS service and perform everything related to repository service!

2. Xrfkey

It is a string with a length of 16 arbitrary character long.  It must provide in both the request parameters and the request header to avoid web vulnerability.  The pair provided must be matched.  The characters can only be number and alphabets, i.e. 0-9, a-z and A-Z.

3. HTTP Client

CURL, postman, some browser extension are typical HTTP clients.  This article will focus on CURL because it is command-line based and can easily be used for integration.

4. Port 4242

It must make sure the port 4242 is not blocked by firewall.  Or to be more secure and specific, you should decide to just allow certain IP to connect to the QRS server with port 4242.

Preparation

Preparation - Getting Qlik Sense Certificate

1. Go to the central node of the Qlik Sense server.

2. Navigate to the path C:\programdata\Qlik\Sense\Repository\Exported Certificates\.Local Certificates

3. Copy client.pem and client_key.pem files into a safe location, say, E:\Qlik Sense API\Certificate

Preparation - Xrfkey

It is easy to have one like abcdEDFGh0123456.  Or you can use dynamic method to generate this character string to enhance the security.

Preparation - HTTP Client

The CURL can be downloaded in the link https://curl.se/download.html.

1. Download the latest version and extract the zip file. Say, E:\Qlik Sense API\CURL

2. We will need to call CURL.exe in the bin folder.  Mark down the entire path, say, E:\Qlik Sense API\CURL\bin\curl.exe.

Preparation - CURL command to call QRS API

"E:\Qlik Sense API\CURL\bin\curl.exe" -X GET --cert "E:\Qlik Sense API\Certificate\client.pem" --key "E:\Qlik Sense API\Certificate\client_key.pem" --insecure -k "https://[Server FQDN]:4242/qrs/app?xrfkey=0123456789abcdef" --header "x-qlik-xrfkey: abcdefgh0123456" --header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository "

Taking an example of the above, the CURL command contains the following elements:

1. The CURL execution file.

"E:\Qlik Sense API\CURL\bin\curl.exe"

2. The HTTP request method used by CURL.  In QRS API, it can be GET, POST, PUT, DELETE.

-X GET

2. The certificate obtained from the Qlik Sense server for authentication purpose.

--cert "E:\Qlik Sense API\Certificate\client.pem" --key "E:\Qlik Sense API\Certificate\client_key.pem" 

3.  If this is specified, it allows self-signed certificate.

--insecure

4. The QRS API endpoint, the example is calling to get a list of app

https://[Server FQDN]:4242/qrs/app

5. The XRFkey in the request parameter.

?xrfkey=0123456789abcdef 

5. The XRFkey header to match with the request parameter

--header "x-qlik-xrfkey: abcdefgh0123456" 

6. Impersonating the account to perform the action.  This account must have adequate privilege to perform the actions.   sa_repository is the Qlik Sense internal account to handle repository related matters.

--header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository"

7. [optional by API endpoint requirement] Sometimes, additional headers are required:

e.g. --header "Content-Type:application/json"

This specifies the request body to be JSON format.

e.g. --header "Content-Type:application/vnd.qlik.sense.app"

This specifies the request body is a binary QVF file.

e.g. --header "Content-Length:0"

This specifies the request body is with no content.

e.g. --data-binary "@[Path_of_upload_file/content]"

It specifies the path of the upload file/content.

Examples to call the QRS API

We are now ready to call the QRS API.  For instance:

1. Getting a list of app

"E:\Qlik Sense API\CURL\bin\curl.exe" -X GET --cert "E:\Qlik Sense API\Certificate\client.pem" --key "E:\Qlik Sense API\Certificate\client_key.pem" --insecure -k "https://[Server FQDN]:4242/qrs/app?xrfkey=0123456789abcdef" --header "x-qlik-xrfkey: abcdefgh0123456" --header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository"

2. Uploading an app

"E:\Qlik Sense API\CURL\bin\curl.exe" - T "E:\Qlik Sense\App\App Name.qvf" -X POST --cert "E:\Qlik Sense API\Certificate\client.pem" --key "E:\Qlik Sense API\Certificate\client_key.pem" --insecure -k "https://[Server FQDN]:4242/qrs/app/upload?xrfkey=0123456789abcdef&name=App%20Name&keepdata=true&excludeconnections=true" --header "x-qlik-xrfkey: abcdefgh0123456" --header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository" --header "Content-Type:application/vnd.qlik.sense.app" 

Note:

• -T is to specify to do multipart upload.
• "E:\Qlik Sense\App\App Name.qvf" is the QVF file location.
• keepdata=true is to retain the data.
• excludeconntions=true is to avoid creating the data connection.

3. Change App Owner and Unapprove

"E:\Qlik Sense API\CURL\bin\curl.exe" -X PUT --cert "E:\Qlik Sense API\Certificate\client.pem" --key "E:\Qlik Sense API\Certificate\client_key.pem" --insecure -k "https://[Server FQDN]:4242/qrs/app/[app_id]/object?xrfkey=0123456789abcdef "--header "x-qlik-xrfkey: abcdefgh0123456" --header "X-Qlik-User: UserDirectory=internal;UserId=sa_repository" --header "Content-Type:application/json" --data-binary "@E:\Qlik Sense API\json\appobject_[id].json"

Note:

• The change owner and unapprove details are inside the json file.  This can be obtained and modified by getting the app/[id]/object/full to get the json and then to change the required details.

Tips

1. the endpoint to be called needs to be URL encoded, e.g. " " (a space) to %20.

2. Calling in batch file.  % needs to be escaped by %%.

3. Majority of JSON required, you can refer to the /full to get an idea of how to build the json content.

4. Refer the API call in the method sections 

https://help.qlik.com/en-US/sense-developer/May2023/APIs/RepositoryServiceAPI/index.html?page=0#Methods

5. Refer the API required JSON (model) in the model section.

https://help.qlik.com/en-US/sense-developer/May2023/APIs/RepositoryServiceAPI/index.html?page=0#Models

6.  If you exporting the details from Qlik Server, it always comes with a GUID and this GUID is an unique identifier to classify the uniqueness of the object.  This GUID, in fact, can be retained when you are using the API to post it back to the server.  Excepting App, App Object, the GUID must be changed. Other objects in the Qlik Sense server, in fact, can be maintained if the JSON has specified it in the API call

API call is very useful for system integration.  For example, to trigger a reload task once the data is ready.  The upstream system can send a QRS API to trigger the reload.  Other useful example would be server migration.  All the details can be obtained by the QRS API and these details can simply post back to the new server.  This can also enhance durability of the server since the redundant content can be scanned and ignored during the processes.

I hope you find it useful!  See you in the next post :-) !

Analytical Calendar - 009 - Analytical Calendar Implementation

Post Index

 Analytical Calendar The Story | The Index Page

2023-05-16

Analytical Calendar

Analytical Calendar Implementation

The implementation of Analytical Calendar in Qlik

The above illustrates the data model for Analytical Calendar. Technically, there are 3 tables in the design including “Analytical Calendar”, “Date Range Association” and “Custom Date”.

1.        Analytical Calendar

It is a combination of the analysis calendar and the comparison calendar. A calendar type is used to distinguish which calendar.

Analysis calendar supports all the date range selection with the date range type like Actual, YTD, MTD, YTW, Rolling, etc. The date period is directly picked by users for analysis.

Comparison calendar mainly is for date comparison. In addition to the selection related to the analysis calendar, i.e. date range type, unique date and rolling range, further selection on comparison unit and comparison range to define the comparison period.

Comparison calendar also integrates the custom date. Whatever it is selected in the analytical calendar, i.e. date range type, unique date and rolling range, a custom date is available to be selected. Once custom date is selected, the comparison unit becomes “Custom”.

User is free to select all these calendar fields while in the backend, there is a control to make sure which calendar to take effect.

2.       Custom Date

This is a table containing all the dates available in the dataset. The main function is to allow the custom date range selection.

3.       Date Range Association

It consists of all the date associations that links up to the analytical calendar to the fact table by using the association of Date Key. It uses the compression method discussed in previous section.

Data Model Working Principles

The working principle of analytical calendar is simple because it only requires picking which calendar to use, i.e. analysis calendar and comparison calendar. To be more precise, comparison calendar can be separated into comparison calendar and comparison calendar – custom.

In the analytical calendar, there are three calendar datasets including analysis calendar, comparison calendar and comparison calendar – custom.

The fields in the analytical calendar include:

·         Key fields:

o    %CUSTOM_KEY

It is used to link up the custom calendar. No matter which fields are selected in the analytical calendar, it can allow any custom dates to be selected.

 

o    %DATE_RANGE_KEY

It is the key to associate the relevant dates based on the analytical calendar design. For analysis calendar, once a combination of date range type, unique date and rolling range are selected, it will associate with meaningful period for analysis. For comparison calendar, in additional the fields required in analysis calendar, additionally, it requires comparison unit and comparison range. If comparison unit is custom, it needs the custom date to be selected in the custom date table.

 

·         For user selection to control the calendars:

o    Date Range Type

To select which date granularity and also the date range representation.

 

o    Unique Date

To select the unique date, i.e. 2022 (Year), 2022-01 (Month), 2022-01-01 (Date), etc.

 

o    Rolling Range

To select when Date Range Type is rolling. It ranges from 1 to the maximum number of rolling. If date range type is not rolling, it shows N/A.

 

o    Comparison Unit

To select the comparison unit for comparison period. It is either one of the date granularities including Y, Q, M, W, D.  The integrity is already controlled when building up the comparison calendar. In other words, it would not allow Y-Actual 2022 to have a comparison unit of M, for example.

 

o    Comparison Range

To select the comparison range for the comparison period. It starts from 1 to the maximal allowed period.

 

·         Sequence fields:

o    %UNIQUE_DATE_SEQ

The unique date sequence is to sort the order of the unique date.

 

o    %DATE_RANGE_TYPE_SEQ

The date range type sequence is to sort the order of the date range type. So, all with same date granularity can be grouped together and based on the user preference to show the preferred order of the date ranges.

 

·         Calendar selection:

o    %CALENDAR_TYPE

It has two values including ANALYSIS to indicate analysis calendar and COMPARISON to indicate comparison calendar.

Analysis calendar data sample is as illustrated below. It takes effect on the fields of:

1.        Date Range Type

2.       Unique Date

3.       Rolling Range

4.      %CALENDAR_TYPE = ‘ANALYSIS’

Comparison calendar data sample is illustrated below. It takes effect on the fields of:

1.        Date Range Type

2.       Unique Date

3.       Rolling Range

4.      Comparison Unit

5.       Comparison Range

6.       %CALENDAR_TYPE = ‘COMPARISON’

Comparison Calendar – Custom data sample is illustrated below. It takes effect on the fields of:

1.        Date Range Type

2.       Unique Date

3.       Rolling Range

4.      Comparison Unit = ‘CUSTOM’

5.       %CALENDAR_TYPE = ‘COMPARISON’

6.       Custom Date

Dashboard Development Principles

The Qlik associative data model already pre-calculates all the associations required for the calendar usage. For front-end dashboard development, it is required to control which calendar, i.e. analysis calendar or comparison calendar, takes the effect. In order to do that, there are two variables created:

·         vSetAnalysisAnalysisCalendar

The set analysis below helps to control the usage of the analysis calendar.

 

[%CALENDAR_TYPE] = {'ANALYSIS'},

[Comparison Range] =,

[Comparison Unit] =

 

·         vSetAnalysisComparisonCalendar

The set analysis below helps to control the usage of the comparison calendar.

 

[%CALENDAR_TYPE] = {'COMPARISON'}

$(=if(GetSelectedCount([Custom Date])>0, ',[Unique Date]={' & Concat(chr(39) & [Custom Date] & chr(39), ',') & '}', ''))

 

 

For a chart requires to show the analysis period, it will have an expression similar to below.

Sum({< $(vSetAnalysisAnalysisCalendar) >} Exp1)

 

For a chart requires to show the comparison period, it will have an expression similar to below.

Sum( {<$(vSetAnalysisComparisonCalendar)>} Exp1)

 

For instance, if the below fields are selected,

The corresponding results will be like below.

And the corresponding period textbox will show as below.

Dashboard Design Advantages

Using the analytical calendar, it gains the below benefits:

User Perspective

·         The dashboard is easier to understand with respect to analysis period and comparison period. It follows 100% of green-white-grey design.

·         Reduce the ambiguity in date selection and chart presentation.

·         The concept is new to the user, but it is easy to pick up and understand. Less than 10 selection attempts, user is able to pick how to use.

Developer Perspective

·         It is a generic design and able to be re-used in many dashboards.

·         It reduces the complexity of the dashboard development to write very complicated set analysis and expression.

·         No need to consider conditions of selection against the chart behavior. It follows the design to show analysis period or comparison period.

·         The design allows customization to add in more date range type.

Previous Section <== 008 - Analytical Calendar - Analytical Calendar Design

Analytical Calendar The Story | The Index Page

Analytical Calendar - 008 - Analytical Calendar Design

 Analytical Calendar The Story | The Index Page

2023-05-15

Analytical Calendar

Analytical Calendar Design

The Design of the First Version of Analytical Calendar

      

Analytical calendar consists of three calendars including Analysis Calendar, Comparison Calendar and Custom Calendar. Analysis Calendar is responsible for reflecting the date selection for analysis. Comparison Calendar is responsible for reflecting the comparison selection for date comparison. Custom Calendar is responsible for custom date range selection.

Analytical Calendar can deal with actual dates like year, quarter, month, week, date, and also allow standard date range like YTD, MTD, YTM, etc, and even more for rolling in different date levels like year, month, week, date. These date range representations are simply converted into the range bounded by start date and end date.

Comparison Calendar references the Analytical Calendar and, on top of it, apply comparison unit and range for date comparison. Integrity is enforced for the comparison unit and comparison range.

The Custom Calendar can accommodate any selected date range and allow comparison selection of any date range.

Basic Elements

There are a few basic elements in the calendars.

1.        Date Range Type

It has two components including the date granularity and the date range type. For example, D-Actual, D-YTD, M-Actual, M-YTM, etc. The date granularity is to control which unique date to show, and the date range type is to control which date range it means for the unique date selected.

2.       Unique Date

It is a combination of all the unique dates including year, year quarter, year month, week year week and date. Once the date range type (i.e. the granularity) is selected, it will show the relevant unique date of the same granularity.

3.       Rolling Range

It works with the date range type with rolling such Y-Rolling, Q-Rolling, M-Rolling, W-Rolling and D-Rolling. It specifies the rolling range required starting from 1 to the maximum allowed rolling period. If non-rolling date range type is selected, it shows N/A.

4.      Comparison Unit

The comparison unit includes Y, Q, M, W and D. It means the date unit for the comparison, i.e. year before, quarter before, month before and so on.

5.       Comparison Range

The comparison range means how many date units for the comparison period. It starts from 1 to a maximum allowed comparison range.

 6.       Custom Date

It is a list of dates for handy picking up the range for comparison.

Analysis Calendar

Analysis calendar consists of the below elements:

1.        Date Range Type

It specifies the date granularity and also the date range type, e.g. D-Actual, D-YTD, M-Actual, etc. There are plenty of cases can be defined based on the need.

2.       Unique Date

It is specifying the date using a unique date format by the date granularity.

·         Date Level => Year-Month-Day, e.g. 2022-02-02.

·         Quarter Level => Year-Quarter, e.g. 2022-Q1

·         Month => Year-Month, e.g. 2022-02.

·         Week => Year-Week, e.g. 2022-W20.

·         Year => Year, e.g. 2022.

It is a unique date representation based on its date granularity. In other words, the Date Range Type controls the corresponding unique dates in the same date granularity level, e.g. Y-Actual, it shows 2022, 2021, etc. while M-Actual, it shows 2022-01, 2022-02, etc. As a result, there is no ambiguity on what date granularity you are selecting.  For instance, M-Actual for 2022-02 is selecting a month, D-Actual for 2022-02-01 to 2022-02-28 is selecting 28 dates.

3.       Rolling Range

It is specifying the number for rolling period with respect to the date granularity, e.g. N/A (it is not rolling related), 1, 2, 3, etc. If date granularity is Y, it means rolling by year, if M, it means rolling by month, so far and so on.

As a result, based on selecting these three attributes, it is allowed to select from one particular date to a range of dates. Date range includes standard date range like YTD, MTD, YTM, etc and rolling N years, N months, N weeks or N days.

Comparison Calendar

Comparison calendar consists of the below elements:

1.        Date Range Type

It is the same as the date range type in the Analysis Calendar as the comparison reference.

2.       Unique Date

It is the same as the Unique Date in the Analysis Calendar as the comparison reference.

3.       Rolling Range

It is the same as the Rolling Range in the Analysis Calendar as the comparison reference.

4.      Comparison Unit

It specifies which date level comparison is required. It has a special comparison unit called custom. This means to pick up custom dates.

·         Year Level, i.e. Y

·         Quarter Level, i.e. Q

·         Month Level, i.e. M

·         Week Level, i.e. W

·         Date Level, i.e. D

·         Custom Level, i.e. Custom

The available comparison unit is enforced with integrity. Once the date range type (date granularity) is selected, only relevant comparison unit is shown. For example, 2022, it is unable to compare in month level. Instead, it only allows Year level comparison.

The two comparison methods, i.e. comparison unit and custom date range are mutually exclusive to each other. In other words, if comparison unit method is used, custom date range comparison is deactivated while using custom date range comparison, comparison unit is deactivated.

5.       Comparison Range

It specifies the date range for comparison regarding to the available comparison units. Custom level does not have a comparison range.

Custom Calendar

There is an additional calendar called the custom calendar. It is the same as a master calendar. It allows to select exactly which dates as the comparison period.

Integrity Control

Integrity control is one of the core features in the analytical calendar because a calendar is normally very flexible for users to do selection. However, if all the date integrity is manipulated by users, it is instead not user friendly.

Analytical Calendar, thus, pre-builds all the integrity control. In other words, the user would not be possible to select something that will be confused. Technically, it reduces the effort for the developer to “fight” against the combinations and scenarios for the usage. Complicated calculations (like the date conversion) would not be required anymore in the dashboard development. Therefore, developer can purely focus on the front-end visualization.

Below shows some examples of considerations of the data integrity about date. When the corresponding date range type is selected, it only allows the corresponding comparison unit. 

Comparison Date Range Type	Comparison Unit
D-Actual	D
D-Actual	M
D-Actual	W
D-Actual	Y
D-MTD	M
D-MTD	Y
D-Rolling	D
D-Rolling	M
D-Rolling	W
D-Rolling	Y
D-YTD	Y
M-Actual	M
M-Actual	Y
M-Rolling	M
M-Rolling	Y
M-YTM	Y
Y-Actual	Y
Y-Rolling	Y
Q-Actual	Y
Q-Actual	Q
Q-Rolling	Y
Q-Rolling	Q
Q-YTQ	Y
W-Actual	W
W-Actual	Y
W-Rolling	W
W-Rolling	Y
W-YTW	Y

Taking an example YTD, it makes sense only to compare last year instead of yesterday. As a result, if YTD is picked, the only comparison unit allowed is year. Another example like rolling 1 year, it makes more sense to compare by year instead of month, week or day. As a result, year is the only selection in the comparison unit for rolling year.

It will be good to support all kinds of rolling and comparison. However, it also generates a lot of data when building up the calendars. Therefore, certain limits are set to avoid overwhelming usage.

For the rolling range, by default, it is configured as below.

Date Range Type	Rolling Range	Reason
D-Rolling	30	Allow 30 days rolling, which is less than the number of days in a month. If 1 month rolling is required, then select 1 month instead.
M-Rolling	11	Allow 11 months rolling, which is less than number of months in a year. If 12 months rolling is required, then select 1 year using ‘Y-Actual’ in Unique Date instead.
W-Rolling	26	Allow 26 weeks rolling, which is less than the number of weeks in half year. If half year rolling is required, then select 6 months using ‘M-Actual’ in Unique Date instead.
Q-Rolling	3	Allow 3 quarters rolling, which is less than number of quarters in a year. If 4 quarters rolling is required, then select 1 year using ‘Y-Actual’ in Unique Date instead.
Y-Rolling	3	Depends on data size

For the comparison range, by default, it is configured as below.

Comparison Unit	Comparison Range	Reason
Y	3	Depends on data size.
Q	3	Allow 3 quarters comparison, which is less than number of quarters in a year. If 4 quarters comparison is required, then select 1 year using ‘Y-Actual’ in Unique Date instead.
M	11	Allow 11 months comparison, which is less than number of months in a year. If 12 months comparison is required, then select 1 year using ‘Y-Actual’ in Unique Date instead.
W	26	Allow 26 weeks comparison, which is less than the number of weeks in half year. If half year comparison is required, then select 6 months using ‘M-Actual’ in Unique Date instead.
D	30	Allow 30 days comparison, which is less than the number of days in a month. If 1 month comparison is required, then select 1 month instead.

Date Association

The date is now represented using Date Range Type, Unique Date, Rolling Range, Comparison Unit, Comparison Range and Custom Date. Consider the following example:

Date Range Type = D-YTD

Unique Date= 2022-02-05

Rolling Range = N/A

Comparison Unit = Y

Comparison Range = 1

 

The analysis calendar should reflect date from 2022-01-01 to 2022-02-05 and comparison calendar should reflect date from 2021-01-01 to 2021-02-05. There is an association relationship of the representation to the actual date selection. It can be imagined that this will generate a huge number of records. To minimize the number of records, a new representation is introduced.

 

If it is represented using date, the analysis calendar requires 36 date values for this association and the comparison calendar also requires 36 date values for the association. In total 72 records are required. However, a better representation would be 2022-02-05|35. It means 0 to 35 days before 2022-02-05, i.e. 2022-01-01 to 2022-02-05. In this representation it only requires 2 records. It greatly reduces the number of records.

 

And more importantly this representation can be used. For example,

 

Date Range Type = D-Rolling

Unique Date= 2022-02-05

Rolling Range = 36

Comparison Unit = Y

Comparison Range = 1

 

It is the same to have 2022-02-05|35 and 2021-02-05|35.

Previous Section <== 007 - Analytical Calendar - Calendar Consideration and Standardization 

Next Section ==> 009 - Analytical Calendar - Analytical Calendar Implementation

 

Analytical Calendar The Story | The Index Page