On-demand App Generation(ODAG) in QlikSense

pawwy1415

On-demand apps enable you to load and analyze big data sources in Qlik Sense. On-demand app generation service is available in Qlik Sense default services from September 2017 version.

To work with On Demand App Generation User should need Qliksense Server. ODAG won't work on QlikSense Desktop Version. On-demand selection and template apps require special load scripting.

Enable On-Demand App Service in QMC:
Before starting ODAG scripting we need to enable On-Demand App Service in QMC like below.

Enable On Demand Service on QMC under On-demand app Service Tab.
By default it was disabled we need to enable by clicking on checkbox.

On-Demand App Scripting:

On-Demand functionality contains Two apps called 'Selection App' and 'Template App'.
On-demand selection apps must load data with a modest level of dimension granularity.
On-demand template apps contain load scripts with data binding expressions used to formulate the queries made on the data sources.
A selection app can be linked to multiple template apps, and a single template app can be linked to by multiple selection apps. But the template app's data binding expressions must correspond to fields in the selection apps that link to it.

1. Selection App Script:
On-demand selection apps is nothing but our Final Project application it must load data with a modest level of dimension granularity.

ODBC Connect to ...

FactTable:

Load
PURCHASEDATE as [Purchase Date]
PRODUCTID as Product,
STORENAME as [Store Name],
STOREDESCRIPTION as [Store Description],
PRICE as Price,
TAX as Tax,
1 as TOTAL_LINES;

sql select
to_chat(pp.purchasedate,'DD/MM/YYYY') as purchasedate,
pp.productid,
s.storename,
s.storedescription,
Sum(pp.price) as price,
Sum(pp.tax) as tax
from product_Price pp
inner join store s on s.storeid = pp.storeid
Group by pp.productid,s.storename,s.storedescription;

Left Keep(FactTable)

DimensionTable:

Load
PRODUCTID as Product,
PRODUCTYTPE as [Product Type],
PRODUCTDESCRIPTION as [Product Description],
CUSTOMERNAME as [Customer Name],
CUSTOMERADDRESS as [Customer Address];

sql select
p.productid,
p.producttype,
c.productdescription,
ct.customername,
ct.customeraddress
from product_account p
inner join product_company c on c.productcompanyid = a.productcompanyid
inner join product_customer_type ct on ct.productcustomertypeid = a.productcustomertypeid;

2. Template App Script:

//Created subroutine for fields in DimensionTable
SUB ExtendWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART) > 0 THEN
LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ( $(Values) )';
ELSE
LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
ENDIF
ENDIF
END SUB;

SET PRODUCTYTPE = ;
SET PRODUCTYTPE = $(ods_Product Type); // Right hand side field should match with Load -UI field
SET PRODUCTYTPE_COLNAME='p.producttype'; // Right hand side field should match with database feild

SET CUSTOMERNAME = ;
SET CUSTOMERNAME = $(ods_Customer Name); // Right hand side field should match with Load -UI field
SET CUSTOMERNAME_COLNAME='ct.customername'; // Right hand side field should match with database feild

SET WHERE_PART = '';

FOR EACH fldname IN 'PRODUCTYTPE', 'CUSTOMERNAME'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','vallist');
ENDIF
NEXT fldname

TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);

// Created Custom Sub Routine for Date Field
SUB ExtendWhere1(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART1) > 0 THEN
LET WHERE_PART1 = '$(WHERE_PART1) AND ( $(Values) )';
ELSE
LET WHERE_PART1 = ' $(Values) ';
ENDIF
ENDIF
END SUB;

SET PURCHASEDATE = ; //In the Load Left hand side PURCHASEDATE mention at first,Second and Third Set variable names
SET PURCHASEDATE = $(ods_Purchase Date]); // The field inside the $() on the right-hand-side of the SET statement must match with the field from //selection app
SET PURCHASEDATE_COLNAME='PURCHASEDATE';

SET WHERE_PART1 = '';

//First Set Statement Variable field PURCHASEDATE need to mention at For Loop
FOR EACH fldname IN 'PURCHASEDATE'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere1('$(fldname)','vallist');
ENDIF
NEXT fldname

TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART1);

//Created subroutine for other fields in FactTable
SUB ExtendWhere2(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART2) > 0 THEN
LET WHERE_PART2 = '$(WHERE_PART2) AND $(ColName) IN ( $(Values) )';
ELSE
LET WHERE_PART2 = ' WHERE $(ColName) IN ( $(Values) )';
ENDIF
ENDIF
END SUB;

SET PRODUCTID = ;
SET PRODUCTID= $(ods_Product); // Right hand side field should match with Load -UI field
SET PRODUCTID_COLNAME='pp.productid'; // Right hand side field should match with database feild

SET STORENAME = ;
SET STORENAME = $(ods_Store Name); // Right hand side field should match with Load -UI field
SET STORENAME _COLNAME='s.storename'; // Right hand side field should match with database feild

SET PRICE= ;
SET PRICE = $(ods_Price); // Right hand side field should match with Load -UI field
SET PRICE_COLNAME='pp.price'; // Right hand side field should match with database feild

SET WHERE_PART2 = '';

FOR EACH fldname IN 'PRODUCTID ', 'STORENAME ', 'PRICE'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere2('$(fldname)','vallist');
ENDIF
NEXT fldname

TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART2);

ODBC Connect to ...

FactTable:

Load
PURCHASEDATE as [Purchase Date]
PRODUCTID as Product,
STORENAME as [Store Name],
STOREDESCRIPTION as [Store Description],
PRICE as Price,
TAX as Tax

1 as TOTAL_LINES;

sql select
to_chat(pp.purchasedate,'DD/MM/YYYY') as purchasedate,
pp.productid,
s.storename,
s.storedescription,
Sum(pp.price) as price,
Sum(pp.tax) as tax
from product_Price pp
inner join store s on s.storeid = pp.storeid
$(WHERE_PART2) and pp.purchasedate <= to_Date($(WHERE_PART1),'DD/MM/YYYY') ;

Left Keep(FactTable)

DimensionTable:

Load
PRODUCTID as Product,
PRODUCTYTPE as [Product Type],
PRODUCTDESCRIPTION as [Product Description],
CUSTOMERNAME as [Customer Name],
CUSTOMERADDRESS as [Customer Address];

sql select
p.productid,
p.producttype,
c.productdescription,
ct.customername,
ct.customeraddress
from product_account p
inner join product_company c on c.productcompanyid = a.productcompanyid
inner join product_customer_type ct on ct.productcustomertypeid = a.productcustomertypeid
$(WHERE_PART);

Note: No Need to reload the Template app just model it. Then from selection app, we can reload our template app for the selected values by generating new application.

Create App Navigation Link In between Selection App and Template App

Once we have completed the Selection App and Template App building need to create App Navigation Link in Between these two apps like below.

Open an on-demand selection app(ProductSelectionapp.qvf) and select Edit.
Select App navigation links from the panel on the left side.
Click the Create new button to open the Create new On-demand app navigation link dialog.
Name the new on-demand app navigation link as per your wish
Select an On-demand template app(ProductTemplateapp.qvf) from the drop down
Write an Expression that computes the total number of detail records i.e., SUM(TOTAL_LINES) which we have created in the above script
Specify the Maximum row count.Note: The Maximum row count should be less than or equal to SUM(TOTAL_LINES) count
Specify the Maximum number of generated apps.
In the drop-down menu to the right of the Retention time of generated app menu, select the unit of time for the retention period.The options for retention time are hours, days, or Never expires.
In the Default view when opening generated apps menu, select the sheet to display first when the apps generated from the navigation link are opened.
You can select App overview or one of the sheets in the selection app from which the navigation link is created.
Select a stream from the Publish to drop-down menu where apps generated from the navigation link will be published.

12. Then do selections on ODAG Filters. When the ODAG2 button turns to green, then user able to generate the app.
13. Click on On-Demand Button >then click on ”i” icon>it will show you the fetching lines under status>click on generated app button>again click on “i” icon to close the window

14. Once the app generated click on open app.

kkkumar82 · ‎2018-07-07

Thanks for the good post kumar, just a simple question, What is the real use case of ODAG in real world ?

Is it for BigData related applications? can you just elaborate ?

kuczynska · ‎2018-07-13

Hi there - for us it was driven by Big Data challenges (and the fact that analyzing daily transactions was extremely difficult due to performance issues caused by data volumes), but I am sure you can use this in different situations too. I am sure some of your users would like to see the links between one app and another, and you can achieve this using ODAG as well. Hope this helps,

Good luck!

Micha

kkkumar82 · ‎2018-07-16

Thanks for the answering

kuczynska · ‎2018-08-01

Hi All,

One other thing we are currently working on is max number of apps that can be generated at the same time - as per below:

We are currently waiting on some confirmation from Qlik if there is any way to overcome this issue (without the need to increase the infrastructure by additional load balancing nodes and upgrade to the most recent release) as this is now vital to the success of our project. We have been told that the value of 10 is a pure limit of the service, but we are still hoping there hardcoded limit of 10 could be removed.

Anyone here maybe managed to find any workarounds?

Thanks,

Michalina

jonvitale · ‎2018-08-01

kuczynska‌

Do you know if there is any way to avoid hard coding the ods_variables in the template app (e.g., ods_Product Type). We want to, for example, load in the fields that users can make a selection on from an excel file.

I posed that question here, but got no reply, which makes me thing it is not possible.

Variable Field Names in On-Demand App Generation (ODAG)

catalystmichael · ‎2018-10-08

I've written a short blog post on ODAG with a script you can download. Hope it's useful:

https://adventuresinqlik.com/2018/10/08/on-demand-app-generation-odag/

jonvitale · ‎2018-10-08

Can you double-check this link?

Ricardo_Gerhard · ‎2018-11-07

Another way to easily build your own ODAG

https://github.com/QlikPreSalesDACH/odag_wizard

sri_c003 · ‎2019-01-11

Is there any limit on the number of apps that can be created this way?

MartinMasMontiel · ‎2019-04-03

For those having issues running this, when ExtendWhere is being called in the for loop you have to call '$(vallist)', not 'vallist' because you want to reference the variable.