Aug 12, 2016 Hello my fellow coders.At my company we have an installation of a BI-solution called QlikSense (next generation of Qlikview if anyonw knows about it). In Qliksense i have several tasks that extract data from varius sources and display them as graphs. The GMA (Governed Metrics Application) 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. The Qlik REST Connector enables Qlik Sense and QlikView to efficiently load data into Qlik apps from a REST data source. Many web-based data sources expose data through a REST API. The Qlik REST Connector is a generic connector, that is, it is not tailored to a specific REST data source.
You can only connect autonumber keys that have been generated in the same data load, as the integer is generated according to the order the table is read. If you need to use keys that are persistent between data loads, independent of source data sorting, you should use the hash128, hash160 or hash256 functions. Improved productivity by reusing SAP components. Qlik Sense's many SAP connectors enable organizations to reuse business logic and leverage investments they have already made in SAP components such as HANA views & tables, BEx queries, DSO / ODS objects, ABAP reports, SAP queries, and SAP extractors.
// 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.
To use this app in Qlik Sense you need to:
Download
Download Qlik REST API Connector http://market.qlik.com/rest-connector.html
Download extension WordCloud https://github.com/cleveranjos/br.com.clever.wordcloud/ or https://community.qlik.com/docs/DOC-7794
Generate column based on primary key column mysql 3109 download. You use the KEY when you want to create an index for a column or a set of columns that is not the part of a primary key or unique key. A UNIQUE index ensures that values in a column must be unique. Unlike the PRIMARY index, MySQL allows NULL values in the UNIQUE index. If you define a compound key, the order of columns in the primary key matches the order of columns as shown in the table. However, you can change the order of columns after the primary key is created. For more information, see Modify Primary Keys. Using Transact-SQL To create a primary key in an existing table. MySQL provides two types of generated columns: stored and virtual. The virtual columns are calculated on the fly each time data is read whereas the stored column are calculated and stored physically when the data is updated. Based on this definition, the fullname column that in the example above is a virtual column. MySQL generated column’s. How can I make three columns my primary key. Ask Question Asked 6 years, 8 months ago. Making statements based on opinion; back them up with references or personal experience. To learn more. How to properly create composite primary keys - MYSQL.
- Have or Create an Account on Twitter (for authentication)
Apigee
Go to https://apigee.com/console/twitter
Choose service https://api.twitter.com/1.1
Choose authentication OAuth 1 and give access/autorize app to your twitter account
In left menu 'Select an API method' select Timelines - user_timeline
Then in Query fill in the User ID - you can find User ID on http://gettwitterid.com/ by typing twitter username (screen name)
Fill in screen_name (username for twitter)
Fill in Since ID - the max date from which will be loaded data in some interval to history
Click on send button
The data for REST connector will appear in the Request URL and on the left side in Request
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:
Open data load editor and in the right corner click create new connection
Choose Qlik REST Connector and name the connection to Twitter
URL - copy the 'Request URL' from Apigee website
Timeout - 30
Check autodetect response type and Key generation stragegy - Sequence ID
Windows anthentication - NO
Your twitter mail and password
Use certificatie - NO
Pagination type - NO
In query headers create 3 rows:a. Authorizationb. Hostc. X-Target-URI
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.
Test conection
In the sheet Twitter_user you have load script
Now you can Load data or preview the data.
ThatB4s all!
Enyoj!
Maros twitter: @MarosVongrej Linkedin: Maroš Vongrej https://cz.linkedin.com/in/maroš-vongrej-927a7153
*/
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
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:
- Launch a web browser and navigate to the QMC for the Qlik Sense site.
- Click on the Apps menu item on the left side of the screen.
- Click the import button at the bottom of the screen.
- Click the Choose File button and navigate to the gma folder inside the folder Governed Metrics Service is installed. Default path:
- Select the Governed Metrics App.qvf file and click the Import Button.
Loading Metrics Metadata to the GMA
Qlik Rest Connector Key Generation Strategy Chart
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.
- Open a new browser tab and navigate to the Qlik Sense Hub.
- Open the Governed Metrics Application and proceed to the data load editor.
- Click on the Metrics Library section of the load script.
- Review the MetricsLibrary table load in the script. Observe the entry for the data source location.
- 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.
Sample Source Data Table
Below is an example of the resulting table of information expected to be loaded from the data source.
| ID | UID | MetricSubject | MetricType | MetricName | MetricDescription | MetricFormula | MetricOwner | MetricTags | MetricGrouping | MetricColor |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | EDSMNFH8F | Customer Service | Measure | % Resolved in SLA | Percentage of Tickets handled within SLA | Sum({< [Call Ctr Days to Resolve] = {'0', '1', '2', '3', '4', '5', '6'} > } [Call Ctr Call #])/sum([Call Ctr Call #]) | Linda Lee | Key KPI;Call | N | |
| 2 | P29OSdkE | Sales | Dimension | Country | Customer Country | Customer Country | Chad Johnson | Customer | N | |
| 3 | L2SDNCHDJEL4ritdsR | Finance | Measure | Costs | Cost Amount | sum([Sales Costs]) | Gordon Wyse | Cost | N | |
| ABCD | ABCD | Sales | Dimension | TimeDrill | Fiscal time drill down | FiscalYear, FiscalQuarter, FiscalMonth | Sales | time;drill | H | |
| sales_5 | Sales | Measure | SumCalc | A fake calculation | sum(1) | some person | fake;metric | N | #46c646 |
Qlik Sense Connectors
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
Click on New Connection and choose Qlik REST Connector.
$ sudo nano /.ssh/configHost.Hostname github.comUser gitIdentityFile /.ssh/customidrsaIf you chose a different name, make sure to change the file name in the IdentifyFile line.Save your file – you should not have to restart your SSH client for the changes to be applied.Now that your files are ready, you can skip the next section dedicated to Windows hosts and start importing your keys to GitHub.Create SSH keys on Windows using ssh-keygenIn order to use ssh-keygen on Windows, you need to have the OpenSSH client enabled.
$ cat /.ssh/customidrsa.pubssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQC+HvRnxwPJyUiUO/UCPKrW6mFPgJF8LxsC2lbBePtn+UDv4Xy+eMJRgG5fbaqy2i0tvP+7T7bjVWCXJGIYunPbH978H4jrebF6Ts+dsgel4+ALf3wkT6nAJkmPDSk3qHleBbjKP1UtO9AAIlclkIVeu5LmV7RaE8H78VXxGVQLcWXvlS0SGlwIxXXd9hBeGh6qPmrya63Ezrt/J1fNy6Ro9s5+ndLogBG2G0JKdAoytbCIBgPmm6sK9nvv3kHrjSK4S0rRz0nb9oaxCQF6V+T75hPgYp+JMOl8yZZMGLN3GPadE2ye2/lskJXzYjlHyjAE6a0g+vrHmMjOULPUrO+aHEA84fNote: you can also use the “ cat” command in PowershellPaste the content of your public key to the dedicated key text area on GitHub. This is the key you are going to copy to Github in order to perform operations on your repos.Configure your SSH keysIf you chose to create the GitHub public key in a separate file, named “ customidrsa” for example, you need to configure your SSH client in order to take into account this separate file.Create a new file named “ config” in your.ssh directory and paste the following content in it. Enabling OpenSSH client on Windows 10In order to enable the OpenSSH client, you essentially have two options: by using Powershell or by using the graphical interface.To enable the OpenSSH client via Powershell, use the “ Add-WindowsCapability” option and specify the OpenSSH Client. When clicking on “ New SSH key“, you will be asked to choose a name for your key and to paste the public key you saved before.To get the content of your public key on Linux, use the cat command on your public key file.Enter the URL for the MDI endpoint. The endpoint url is https://%senseServerName%:%gmsPort%/masterlib/getallMDI.
For Data options make sure the auto detect checkbox is checked and the key generation strategy is sequential.
In the authentication section, set windows authentication to No and leave the userid and password fields blank.
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.
In the Pagination section, set the Pagination type to None.
For the getAllMDI path, there is no need for query parameters.
Provide a name for the connection, test the connection and click Save.
Open the data preview window. Click the checkbox next to root. This will show the data fields in the preview window.
Click the Insert script button to add the load script to the data load editor.