Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Qlik - How To Load Azure Kusto Queries

Microsoft is launching a new export format for Azure Logging in the next weeks (https://docs.microsoft.com/en-us/azure/kusto/concepts/) and guess what, Power BI can already natively import this, so I wanted Qlik to read this, too.

The result of the Kusto POST-Call comes as JSON (see here for a sample) but the data model created by the Qlik's REST Connector "select data" wizard was sub-optimal: All values of all tables come in just one column, while you hoped to get n tables with x columns each. So I had to massage the initial data returned.

I spare you the details on how I made the script, you can check yourself in the attached .qvf file if you like. Basically, I had to

  • join tablename information from the root table down to other tables and
  • to add an auto-increment number in two tables which restarts at 1 when the foreign key changes

This is what the REST-Connector Wizard creates for you ...

2019-03-01 15_55_04-Qlik Sense Desktop.png

After my massage with Qlik Scripting I have added necessary fields:

2019-03-01 15_56_41-Qlik Sense Desktop.png

My script computes the correct ultimate load-statement itself, something like this (dont worry if you can't read it, it "pivots" rows into columns :-)

LOAD
Only(If(GoesToColumn = 1, @Value)) AS [Timestamp],
Only(If(GoesToColumn = 2, @Value)) AS [ClientRequestId],
Only(If(GoesToColumn = 3, @Value)) AS [ActivityId],
Only(If(GoesToColumn = 4, @Value)) AS [SubActivityId],
Only(If(GoesToColumn = 5, @Value)) AS [ParentActivityId],
Only(If(GoesToColumn = 6, @Value)) AS [Level],
Only(If(GoesToColumn = 7, @Value)) AS [LevelName],
Only(If(GoesToColumn = 8, @Value)) AS [StatusCode],
Only(If(GoesToColumn = 9, @Value)) AS [StatusCodeName],
Only(If(GoesToColumn = 10, @Value)) AS [EventType],
Only(If(GoesToColumn = 11, @Value)) AS [EventTypeName],
Only(If(GoesToColumn = 12, @Value)) AS [Payload]
RESIDENT Rows
WHERE Rows.TableName = '$(vTable)'
GROUP BY __KEY_Rows;

 

Finally, I drop all the tables created by the REST Connector wizard, to get this nice to read format:

2019-03-01 16_02_52-Qlik Sense Desktop.png