weekend-warrior / q-risotto Goto Github PK

Qlik REST In Sense (otto) - a RESTful Engine API wrapper

Inspired by project chartcacher by Alexander Karlsson.

The intention was to provide an easy access to apps, objects and its data on a Qlik Sense server thru a REST API to integrate with other systems.

The development state of this API is at the very beginning and highly incomplete, so more or less raw and unground. But see yourself.

See also: routes.js

GET /v1/docs - all docs on server, returns getDocList().qDocList

GET /v1/doc/{docId} - app layout, returns getAppLayout().qLayout

GET /v1/doc/{docId}/objects - all objects of app, returns getAllInfos().qInfos

GET /v1/doc/{docId}/serialize - serialize an app into a JSON object, more details here: mindspank/serializeapp

GET /v1/doc/{docId}/object/{objId} - object layout, returns getLayout().qLayout

GET /v1/doc/{docId}/object/{objId}/layout - layout data, returns getLayout().qLayout depending on object type it contains qHyperCube and qDataPages

GET /v1/doc/{docId}/object/{objId}/data - object data, returns first data page of either getLayout().qHyperCube or qListObject depending on object type chart/listbox, not data for pivot tables

GET /v1/doc/{docId}/object/{objId}/pivotdata - object data, returns getLayout().qPivotDataPages for pivot tables

GET /v1/doc/{docId}/object/{objId}/layers - object data, returns getLayout().layers for maps

POST /v1/doc/{docId}/hypercube - give a column list array or HyperCubeDef JSON as payload (request body) and get back the evaluated getLayout().qHyperCube, without data page

Examples for payload to define a hypercube:

1. a list of columns as string, measures start with equal sign, all others are treated as dimensions (hint: use brackets for dimensions):

[
    "[Date.autoCalendar.Date]",
    "[Case Owner Group]",
    "=Avg([Case Duration Time])",
    "=Count({$<Status -={'Closed'} >} Distinct %CaseId )"
]

2. a list of NxDimension and NxMeasure structure objects, can be mixed with column strings like in 1.:

 [
    "[Date.autoCalendar.Date]",
    {"qDef": {"qFieldDefs": ["Case Owner Group"], "qFieldLabels": ["Group"]}},
    {"qDef": {"qDef": "=Avg([Case Duration Time])", "qLabel": "Avg Case Duration Time"}},
    {"qDef": {"qDef": "=Count({$<Status -={'Closed'} >} Distinct %CaseId )", "qLabel": "Open Cases"}}
]

3. a HyperCubeDef (see link below)

See Qlik help also:

• Columns
• NxDimension
• NxMeasure
• HyperCubeDef

POST /v1/doc/{docId}/hypercube/size - give a give a column list array or HyperCubeDef JSON as payload (request body) and get back the evaluated size of getLayout().qHyperCube as the following JSON object:

{
    "columns": 3,
    "rows": 89,
    "pages": 1
}

POST /v1/doc/{docId}/hypercube/json/{pageNo*} - give agive a column list array or HyperCubeDef JSON as payload (request body) and get back the evaluated getLayout().qHyperCube.qDataPages[0].qMatrix (the first data page or page number given as last URL param) transformed into a JSON collection of data rows (eg. easy to use with Qlik REST Connector), date and timestamps are delivered in UTC-time:

[
    {
        "Date": "2014-10-11T22:00:00.000Z",
        "Cumulative New Cases": 4,
        "Cumulative Closed Cases": 0
    },
    {
        "Date": "2014-10-18T22:00:00.000Z",
        "Cumulative New Cases": 5,
        "Cumulative Closed Cases": 3
    },
...
]

GET /wdc - Tableau WDC to connect q-risotto endpoint /v1/doc/{docId}/hypercube/json

cd sources

npm install

• Copy the files manually with admin priviliges into
  C:\Program Files\Qlik\Sense\ServiceDispatcher\Node\q-risotto\

• Then append the following configuration options to
  C:\Program Files\Qlik\Sense\ServiceDispatcher\services.conf
  This will let the Service Dispatcher know how to run the module, this step has to be re-applied in an upgrade of Qlik Sense Server.

[q-risotto]
Identity=Qlik.q-risotto
Enabled=true
DisplayName=q-risotto
ExecType=nodejs
ExePath=Node\node.exe
Script=Node\q-risotto\server.js

[q-risotto.parameters]

Adjust ./src/config/config.json to work with a Qlik Sense Server like this:

{
    "enigmaSchema": "enigma.js/schemas/12.34.11.json",
    "engineHost": "<your Qlik Sense Server hostname or IP>",
    "enginePort": 4747,
    "globalAppId": "engineData",
    "userDirectory": "Internal",
    "userId": "sa_repository",
    "certificatesPath": "C:/ProgramData/Qlik/Sense/Repository/Exported Certificates/.Local Certificates",
    "port": 3000
}

Adjust ./src/config/config.json to work with the dockerized QIX engine like this:

{
    "enigmaSchema": "enigma.js/schemas/12.34.11.json",
    "engineHost": "qix-engine",
    "enginePort": 9076,
    "globalAppId": "engineData",
    "userDirectory": null,
    "userId": null,
    "certificatesPath": null,
    "port": 3000
}

Use the docker-compose.yml file provided in the repo and start it:

docker-compose up

See config.json for configurations.

Start on Qlik Sense server with npm start or integrate into Qlik Sense ServiceDispatcher.

Navigate with browser or other tools to https://<qlik sense server name>:1338/<endpoint>

Test q-risotto API calls with Postman:

Use QlikView with the Qlik REST Connector to retrieve data from Qlik Sense:

Use q-risotto WDC with Tableau to retrieve data from Qlik Sense:

Use q-risotto in your R environment and retrieve data from Qlik Sense. Here you will find the R convenience package 'senser' to wrap q-risotto and complex Qlik structs like qHyperCubeDef:

https://github.com/ralfbecher/senser

Ralf Becher

• irregular.bi
• twitter/irregularbi
• github.com/ralfbecher

Copyright © 2016 Ralf Becher

Released under the MIT license.