topic Re: Problem in loading table in Sense app in Qlik NPrinting

Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 07:25:38 GMT

Hi experts,

I have problem. I installed new version of NPrinting and Qlik Sense (June 2018).

Now I have a problem in loading table in application. It takes too much time. Because of that, NPrinting cannot generate report.

It gives me message:

WARN: error during report generation: Object pkvJPge of Sense app 29c327dd-cf23-4f24-9559-95950477bc23 failed to render. ERROR: Method "GetLayout" timed out

I don't understand anything, what is this.

😕

Thanks

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 07:47:30 GMT

looks like calculation time for object you are trying to export using NPrinting takes ages and it times out. GetLayout method is equivalent to creating a virtual table/model containing all values for table you need to export.

Forget NPrinitng for a moment and try to help me understand:

how long does it take to calculate this object in Qlik Sense?
how long does it take to export data from this table directly in Qlik Sense
how many rows of data does it produce?
How big is an app (no rows in fact table).
Is it possible to generate aggregated data model for NPrinitng?

To me it is optimization issue which directly relates to best practices and knowledge around performance tuning first in Qlik then in NPrinting as NPrinting performance directly relates to Qlik Sense performance you need to focus first on Qlik Sense.

regards

Lech

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 08:23:33 GMT

Hi Lech,

True is that application is very big.

Loading data is about 30 min.

In Qlik Sense app, loading table in one sheet is a few min.

Model produces about 400 million rows.

App is over 2 GB.

...

😕

After restarting server, one report that is connected to this application is generated.

But other, bigger cannot.

Again:

Failed generation of report c1f26feb-568d-4881-baaf-e17c5b0484e5 for user 530ea474-8d59-4e4d-9548-e4554a178281. ERROR: Object pkvJPge of Sense app 29c327dd-cf23-4f24-9559-95950477bc23 failed to render. ERROR: Method "GetLayout" timed out

😕

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 08:35:41 GMT

Waht about all the other questions i asked??

Nprinting produces static report so i dont think it produces milions of rows... right?

I am conviced there is a way of aggregating this data for Nprinting in qlik sense on data load level and not running it out of half bilions of rows

This is qliksense app design issue really!

Re: Problem in loading table in Sense app

Ruggero_Piccoli — Wed, 08 Aug 2018 09:02:03 GMT

Hi,

Create a Qlik Sense app only for reporting need. Upload into it only the rows and columns that are needed to generate the reports. Create only the charts that are needed for reporting and aggregate the rows where you don't need details.

Move all the calculations on the loading script otherwise you have to wait that the charts will be recalculated every time you apply a Qlik NPrinting filter.

Best Regards,

Ruggero

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 10:17:08 GMT

I did that....

I need all this table that I have in application...

about 20 tables in one report

😕

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 10:24:13 GMT

All my tables are full of measures...

Format of measure is:

fabs(Sum({$<DATE={"$(=date(rangemin(monthend(today(),-3), monthend(MAX(DATE), -3))))"}, DIM={ "23","24"}>}VALUE)/(-1000))

Every table has about 13 columns like this....(for every month, 3 last weeks)....

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 10:25:55 GMT

Jasmina.

I dont believe in this! You are saying that the fact table has roughly 400 milions of rows. If this is grain required for nprinting are you saying that your nprinting report produces the same grain (400 milion of lines?)

I ma sure not

It is aggregated!

You need to do this aggregation as well in qlik script (not in qlik sense chart)

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 10:29:28 GMT

No Lech,

Tables are aggregated....

One table about 40 rows (main table)

Other 20 tables about 15-20 rows... They are sum of values, divided on cities....

😕

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 10:29:46 GMT

Report is in 10 pages realised.

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 10:30:51 GMT

I am sorry, you dont need advice. You need experienced consultant to help you improve performance of your app on site.

In my opinion expressions you are using are not optimal, data model can be done better and since you are using this app only for nprinting you dont need to use rangemin functions etc.... it all should be done in script, using flags

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 10:31:09 GMT

How do you mean aggregation in qlik script, not in chart?

😕

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 10:32:39 GMT

Thank you

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 10:44:40 GMT

I wish i could help you more.

My advice is:

Review what dimension are you using in your tables in Qlik Sense front end tables
- If for example you are having dimensions like country, product, date, salesperson and you have sales_amount column on data load in qlik sense script apply aggregation like this:

Load

Country,

Product,

Date,

Salesperson

Sum(sales_amoun) as sales_amount

From

YourQvd.qvd (qvd)

Group by

Country,

Product,

Date,

Salesperson

;

Ideally you want this data to be aggregated in data source/ data warehouse to the required level to avoid this aggregation in load script, but if this is not possible then it will still be better than not aggregating data at all and rooling everything up from lowest grain on Qlik Sense frontend.

In general make sure you aggregate data in your data model!!

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 12:39:50 GMT

Lech, how can I write in data model this expression:

fabs(Sum({$<DATUM={"$(=date(rangemin(MONTHEND(today()-1,-13),MONTHEND(MAX(DATUM),-12))))"}, IZVESTAJ={"MIS_BSS"}>}STANJE_KM)/(1000)) as BS_12

with if?

Jasmina

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 12:51:59 GMT

I wrote:

if(IZVESTAJ='MIS_BSS' AND DATE(DATUM)=DATE(MONTHEND(today()-1,-13)), SUM(STANJE_KM) ) AS BS_12

It takes much time for load....

😕

I need maybe 100 formulas like this...

Jasmina

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 13:20:32 GMT

Lech, I think that is not good idea...this cannot be

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 13:24:02 GMT

Well - I am not sure if you understood what i meant by saying that you need to move your expression back to script. We did not say that you have to replicate your UI expressions, but all what we said is to roll it up to the level which will still allow you to use chart expressions on qlik sense front end, but your expression will be run against a lot smaller dataset.

What i meant by aggregating data in data model was to rollup to max possible aggregation in script in order to reduce your fact table. Expressions on front-end are still necessary but they can be optimized!

Out of 400 millions i am sure there is a way to preaggregate data in datamodel to for example 50 milion of rows... or maybe less

Now when i look at your expression i see that:

you are summing up Stanje_KM and returning absolute value
you are filtering date by checking min date out of MONTHEND(today()-1,-13), and MONTHEND(MAX(DATUM),-12)))
- this check possibly can be done in script with if statement and could return min date for the row or flag which you can then directly use in set analysis
You can divide STANJE_KM by 1000 in script
you can also use Sum(STANJE_KM) in script and group it by all required fields (all dimensions used in your NPrinitng report)

It is very hard to advice you on this. I would have to know how the NPrinting report needs to look like

all dimensions,
all filters used
all logics in template etc..

On Qlik Sense application side we would need to see:

load script
data model preview
all expressions from objects used for NPrinting report
all objects used in NPrinting report so we could see dimensions, as the data on backend should be aggregated up to them (grouped by ALL dimensions used in UI)
data which is getting loaded into this app
- what is the date range?
- what is the date grain (daily/date, weekly/weekend, monthly/monthend)?
  - Is the data pre-aggregated in data model to the max required for nprinting level - i say currently it is not!?
  - How much history are you loading? are you only loading data required for NPrinting report?

Like i said - i cannot guide you here as i would need to understand whole application to give you right guidence. This really can be done by having the app in front of you and going through it from requiremnts, source, script till UI and NPrinting.

regards

Lech

Re: Problem in loading table in Sense app

Lech_Miszkiewicz — Wed, 08 Aug 2018 13:34:11 GMT

No - this is not the way you need to do this. We cannot look at this expression by expression. We need to have full picture to be able to advice accordingly.

I said it before in one of my answers before, that typicaly this aggregation should be done in data warehouse....

In my last previous response i also explained that we don't want to replicate chart expressions in script, we just want to preaggregate data...

I really think you need someone who is experienced in Qlik development to help you with this. We will not be able to this via community since we don't have a full picture.

By the way - what is the source of data? Is is database or data warehouse? I would push a lot of transformation down to source if possible.

regards

Lech

Re: Problem in loading table in Sense app

jasmina_karcic — Wed, 08 Aug 2018 13:38:51 GMT

Look this Lech,

I have turned back snapshot of server, and with it I turned back previous version of Qlik Sense and NPrinting (april 2018) and now all works fine.

But I am afraid, again will stop, it is just question of moment.

P.S. Database is source (we created many views in relational database)

regards,

Jasmina