Qlik Rest Connector Key Generation Strategy

Posted on  by 

The Governed Metrics App (GMA) is a compliance application for Qlik Sense providing insight into master library dimensions and measures that have been deployed through the Governed Metrics Service (GMS).

Source data for the Metrics Library portion of the GMA may be loaded from any data source. Loaded data needs to conform to the specification described in the general section below.

  • General Configuration
  • Loading Metrics Metadata to the GMA

General Configuration

Import the Governed Metrics Application

Nov 12, 2017  Method needs to be GET because we only want to retrieve information from Spotify rather than push it from Qlik. The key generation strategy should be left as Sequence ID. 3 thoughts on “ Spotify – REST – Qlik. Been working with the Twitter API via the REST connector, so it is interesting to see the similarity. The Qlik REST Connector supports the following paging options. Select Sequence ID as the Key generation strategy to use for creating primary and foreign keys in the data. The order of fields is the same in every record from Facebook, so Sequence ID can be used. It is the simplest and most efficient of the key generation options.

Qlik rest api

The Governed Metrics Application is part of the GMS installation. From the installation directory, navigate to the gma folder and observe the Governed Metrics App.qvf file.

To import the GMA app to a Qlik Sense server:

  1. Launch a web browser and navigate to the QMC for the Qlik Sense site.
  2. Click on the Apps menu item on the left side of the screen.

![2](https://github.com/eapowertools/GovernedMetricsService/wiki/imgs/app/import1.png)
  1. Click the import button at the bottom of the screen.

![3](https://github.com/eapowertools/GovernedMetricsService/wiki/imgs/app/import2.png)
  1. Click the Choose File button and navigate to the gma folder inside the folder Governed Metrics Service is installed. Default path:

![4](https://github.com/eapowertools/GovernedMetricsService/wiki/imgs/app/import3.png)
  1. Select the Governed Metrics App.qvf file and click the Import Button.

![5](https://github.com/eapowertools/GovernedMetricsService/wiki/imgs/app/import4.png)

Loading Metrics Metadata to the GMA

Loading data to the Metrics Library is the same as loading data into a standard Qlik Sense app. There are some guidelines to follow depending on the data source metrics metadata will be loaded from. In general, metrics metadata will be loaded from a centralized location like a database, MS Excel, or CSV file. In the 2.0.0 release of GMS, a new method of loading data using the Qlik REST Connector makes it possible to load metrics metadata from the master library items of other apps in a Qlik Sense site.

Update the GMA Data Connection

After importing the GMA application, it may be necessary to update the lib references in the script to point to the appropriate data connection. Follow these steps to update data connections in an app.

  1. Open a new browser tab and navigate to the Qlik Sense Hub.

  1. Open the Governed Metrics Application and proceed to the data load editor.

  1. Click on the Metrics Library section of the load script.

  1. Review the MetricsLibrary table load in the script. Observe the entry for the data source location.

  1. Now review the available data connections from the list on the right. If there is a match for EAPowerToolsGMS then there is no update required. If a data connection with a different name appears in the list that points to the source of Metrics Library metadata, replace the EAPowerToolsGMS in the script with the name of the connection where the source data resides. If no connection to source data exists, proceed to create one and update the script with the new data connection name.

Field Definitions and Structure

The Governed Metrics Service relies on specific field names to distribute metrics. While the source data field names do not have to match, the field names Qlik Sense will use need to be the following:

  • ID - (REQUIRED) The ID field is a numeric primary key field for metrics distributed by the GMS natively.

Usage:
A. The ID field must be filled with the Qlik Sense Engine object id when importing metrics via MDI or adopting manually created Master Library items into GMS.

B. Use a unique numeric value when a new metric is delivered to apps exclusively through GMS.


  • UID - (REQUIRED) The UID is the field the Governed Metrics Service applies to new master library items to be created or uses to detect existing master library items to make changes. The UID can be one of two items:

Usage:
1. A hardcoded value representing an existing master library item id. For MDI and adopted master library items, this value is the same as the ID.

2. If the UID is left blank at the source, GMS will create a UID with a modified 128 bit hash composite of the MetricSubject field and ID field. The hash has non-alphanumeric characters stripped out for support as object ids in Qlik Sense objects. Uses the expression if(isnull(UID),KeepChar(Hash128(MetricSubject,ID),'$(chars)'),UID) as UID.

  • MetricSubject - (REQUIRED) The MetricSubject MUST equal the ManagedMasterItems custom property value. When the Governed Metrics Service reads the Metrics Library app tables, this field is used to identify which apps will receive the dimension or measure.

  • MetricType - (REQUIRED) This field identifies if the metric is a dimension or measure.

  • MetricName - (REQUIRED) The MetricName field contains the friendly name displayed in the Master Library for the dimension or measure.

  • MetricDescription - This field contains a description for the dimension or measure. When a user clicks on a Master Library item, the description will appear in the pop user interface.

  • MetricFormula - (REQUIRED) The MetricFormula contains the dimension field name(s) or the expression to be used for the dimension or measure.

Usage:
A. For measures, the field is a string literal of the expression without the initial equal sign.

B. For single dimensions, enter the field name the dimension will reference. Do not square bracket field names with multiple words and spaces.

C. For calculated dimensions, the field is a string literal including the initial equal sign. If using Excel as the data source, add an apostrophe before the equal sign so that Excel will treat the expression as a string literal.

D. For drilldown dimensions, enter the field names to group together as comma separated values. Do not include square brackets surrounding field names.


  • MetricOwner - This field identifies who owns the metric for informational purposes.

  • MetricTags - (REQUIRED) The MetricTags field is a semicolon delimited list of descriptive tags to be added to the dimension or measure Master Library item to aid search. The field must be present, but does not require any data.

  • MetricGrouping - (REQUIRED) The MetricGrouping field identifies if dimensions are single (N), or drilldown (H). Measures are only ever single (N).

  • MetricColor - (REQUIRED)Available in Qlik Sense 3.2 and higher, the MetricColor is applicable only to measure master items. A hex color code value (e.g. #ffffff) may be supplied in this field to set a color for a measure. If no hex value is supplied, no color will be set. The field must be present, but does not require any data.

Connector

Sample Source Data Table

Free Key Generation Software

Below is an example of the resulting table of information expected to be loaded from the data source.

IDUIDMetricSubjectMetricTypeMetricNameMetricDescriptionMetricFormulaMetricOwnerMetricTagsMetricGroupingMetricColor
1EDSMNFH8FCustomer ServiceMeasure% Resolved in SLAPercentage of Tickets handled within SLASum({< [Call Ctr Days to Resolve] = {'0', '1', '2', '3', '4', '5', '6'} > } [Call Ctr Call #])/sum([Call Ctr Call #])Linda LeeKey KPI;CallN
2P29OSdkESalesDimensionCountryCustomer CountryCustomer CountryChad JohnsonCustomerN
3L2SDNCHDJEL4ritdsRFinanceMeasureCostsCost Amountsum([Sales Costs])Gordon WyseCostN
ABCDABCDSalesDimensionTimeDrillFiscal time drill downFiscalYear, FiscalQuarter, FiscalMonthSalestime;drillH
sales_5SalesMeasureSumCalcA fake calculationsum(1)some personfake;metricN#46c646

Loading Data from a Folder or Database Data Connection

Here is an example of a load statement for loading metrics from an MS Excel file.

While all the fields are important and required, the UID field is most critical because it is the field used to identify existing metrics deployed to applications through GMS, and the value of this field will be used to generate the engine object id of new dimensions and measures added by GMS. It may be necessary to alter this template script to identify the source field names, but it is mandatory that the destination field names reflect the values described in the field definitions section on this page.

Loading Data using the MDI REST Endpoint

Beginning in GMS 2.0.0, a REST endpoint is available to reach into other Qlik Sense applications and extract the master library dimension and measure definitions. The definitions are formatted and loaded into the Metrics Library as if they were stored in the central data store for metrics metadata. The advantages of this capability are reuse of existing metrics stored in apps and a way to categorize master items by using an app as the storage container for definitions. This capability is useful when no central metrics data source exists.

This is a completely optional section of the guide. If Qlik Sense apps with master library dimensions and measures will be used as a source of metric metadata, please follow the steps below to set up a connection.

In order for the MDI capability to work, the Governed Metrics Service must be running.

To access the MDI REST endpoint, use the Qlik REST Connector and follow the directions below to configure a connection and identify a MasterLibrarySource application.

Creating the MDI REST Connection in the Data Load Editor

Qlik Sense Connectors

  1. Click on New Connection and choose Qlik REST Connector.

  2. Enter the URL for the MDI endpoint. The endpoint url is https://%senseServerName%:%gmsPort%/masterlib/getallMDI.

  3. For Data options make sure the auto detect checkbox is checked and the key generation strategy is sequential.

  4. In the authentication section, set windows authentication to No and leave the userid and password fields blank.

  5. Authentication to the REST api uses the certificates GMS is set up to use during installation. In the Use certificate dropdown, choose installed to use a certificate installed on the windows server through certificate management, or choose From file to enter the paths for the certificate and private key files for authentication.

  6. In the Pagination section, set the Pagination type to None.

  7. For the getAllMDI path, there is no need for query parameters.

  8. Provide a name for the connection, test the connection and click Save.

  9. Open the data preview window. Click the checkbox next to root. This will show the data fields in the preview window.

  10. Click the Insert script button to add the load script to the data load editor.

// Twitter account detail

/* Version 1.0A Qlik Sense app that is using Qlik REST Connector to load data from Twitter. Is has some limitation of rows loaded from Twitter.

Qlik Rest Connector Key Generation Strategy

To use this app in Qlik Sense you need to:

Qlik Rest Connector Key Generation Strategy Pdf

Download

  1. Download Qlik REST API Connector http://market.qlik.com/rest-connector.html

  2. Download extension WordCloud https://github.com/cleveranjos/br.com.clever.wordcloud/ or https://community.qlik.com/docs/DOC-7794

Twitter

  1. Have or Create an Account on Twitter (for authentication)

Apigee

  1. Go to https://apigee.com/console/twitter

  2. Choose service https://api.twitter.com/1.1

  3. Choose authentication OAuth 1 and give access/autorize app to your twitter account

  4. In left menu 'Select an API method' select Timelines - user_timeline

  5. Then in Query fill in the User ID - you can find User ID on http://gettwitterid.com/ by typing twitter username (screen name)

  6. Fill in screen_name (username for twitter)

  7. Fill in Since ID - the max date from which will be loaded data in some interval to history

  8. Click on send button

  9. The data for REST connector will appear in the Request URL and on the left side in Request

  10. Dont close the website, you will need it for next steps.

Now you have all information needed for Qlik Rest Connector, to create new Qlik Sense connection you need to:

  1. Open data load editor and in the right corner click create new connection

  2. Choose Qlik REST Connector and name the connection to Twitter

  3. URL - copy the 'Request URL' from Apigee website

  4. Timeout - 30

  5. Check autodetect response type and Key generation stragegy - Sequence ID

  6. Windows anthentication - NO

  7. Your twitter mail and password

  8. Use certificatie - NO

  9. Pagination type - NO

  10. In query headers create 3 rows:a. Authorizationb. Hostc. X-Target-URI

  11. Copy the information from Apigee (you can find it by clicking on Send button in apigee and after the server response,they will appear on the left side in Request.

  12. Test conection

  13. In the sheet Twitter_user you have load script

  14. Now you can Load data or preview the data.

  15. ThatB4s all!

Qlik Web Connectors

Enyoj!

Qlik Connector Download

Maros twitter: @MarosVongrej Linkedin: Maroš Vongrej https://cz.linkedin.com/in/maroš-vongrej-927a7153

Qlik Sense Salesforce Connector

*/

Coments are closed