topic Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode in QlikView

Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Fri, 01 May 2015 19:31:31 GMT

I have created dynamic report based on conditional show/hide of expression and dimension (Qv 11 feature). Report is generated fine but the Qlikview in-built Export to excel is taking too long (Export to excel takes 8 mins just to export 2000 rows with 30 columns).

More details

1) Report is created with Straight table having 80 dimesions and 30 expression with paramaterized variables (conditional show or hide based on user selection) So only few will be enable based on user selections.

Ad-hoc report (Straight table)

Dimension tab:

Expression tab:

2) I have tried export to csv using macro and its just taking 1 min but we must have to use AJAX (so i believe macro is not possible.)

3) We are using QV Version 11.2 (SR 7) and 64 bit. (I read post about some patch from QV to improve export performance but its old post (around 2012) and not sure if its applicable to this version - Extremely slow Excel export)

I believe inbuilt Export to excel trying evaluate all dimensions (all 80) and expression (all 30) even though user has selected only few and could be the root cause.

Any suggestion or design change to improve the Export to excel performance?

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Mon, 04 May 2015 14:11:17 GMT

Any suggession to above approach?

What is the best approach to allow user to create ad hoc report with available selection of dimension (around 80) and expression (around 30)? Most important thing is the inbuilt (without macro) export to excel should work normally.

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Wed, 06 May 2015 17:32:42 GMT

Sharing below solution to benifit all.

Issue:

The root cause was the use of calculated dimensions (i.e. =$(vReportDimName(1) etc.). While exporting to excel it evaluates this expression and get the actual dimension name.

Solution: I have replaced =$(vReportDimName(1)) and other dimensions with actual dimension names (i.e. replaced actual column names avaible in data model like Region, Country etc). It boost the performance and now it just take few second (around 30 second) to export 80 dims and 50 expression.

Please note that i have just replaced expression with acual column/dimension names in Used dimension part. I have still kept parameterized expression for the "Enable Conditional" and "Label".

Label Expressions for each columns

=$(vReportDimDisplayName(1))

=$(vReportDimDisplayName(2))

=$(vReportDimDisplayName(3))

Underlying Variable

vReportDimDisplayName = ({<[_dimension Field Name] = {$1}>} [_dimension Field Name])

The [_dimension Field Name] is coming from xls file and used to generate avaible report demesion list.

Further Enhancement: I have created one time macro and button to execute that. The macro generates the above dynamic report straight table based on .xls file. It can be improved to run the macro post reload without clicking any button so that any new additional report columns in xls will be automaticaly added during every refresh in the straight table and to user for ad-hoc/self service report.

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

koresaket — Mon, 15 Jun 2015 08:20:58 GMT

Hi Dharm,

I'm currently facing the same issue wherein I've used over 100 calculated dimensions to create a dynamic report. The Excel exports take forever. We raised this issue with Qlik and they've recognized it as a bug.

Lets hope they fix this in the upcoming service-release!

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Fri, 19 Jun 2015 15:09:20 GMT

Hi Saket,

what SR have you had that acknowledged as a bug for? I have recently move to SR10 and finding the export slow here.

Joe

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

koresaket — Mon, 29 Jun 2015 07:13:30 GMT

Hi Joe..

The issue has not been resolved with any of their service-release till date.. They said they'll try to fix it with the next one! Hope so this gets resolved with SR13.. if there's going to be any.

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Mon, 29 Jun 2015 07:49:06 GMT

Hi Saket,

do you have the issue number that Qlik created for this if possible?

thanks

Joe

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Mon, 29 Jun 2015 20:18:27 GMT

Saket/Joe, The use of many Calculated Dimension surely causing the export to excel slow.

I have made following changes for export work around (while qlik improve calculated dimension export enhancements).

1) Try to use Actual Column names coming from data model (in below screen the Region, Country, State are my data model columns.)

2) Try to avoid use of calculated columns as much as possible (if possible move that logic to data model/scripting side).

3) Use Export to CSV (instead of Export to Excel, where ever possible.)

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

amien — Thu, 24 Sep 2015 11:42:26 GMT

@Dharm or someone else.

I have 3 questions:

* I'm running 11.2 SR9. Is there much improvemnt in SR12? Is it worth upgrading for this reason?

* Why do i need to set the label? Can't i just the expression as label? As i'm using column names from the datamodel

* Anyone got a tip for optimising this?

I have this string:

SET vGetFieldSelections = Replace(
GetFieldSelections(sel1,'|',100) & '|' &
GetFieldSelections(sel2,'|',100) & '|' &
GetFieldSelections(sel3,'|',100) & '|'

,'||','|')
;

SET vDimCustomReport = SubField($(vGetFieldSelections),'|',$1);
SET vConditionDimCustomReport = Len(SubField($(vGetFieldSelections),'|',$1));

My chart:

Dimension : =$(='[' & $(vDimCustomReport(1)))

Condition on Dimension : $(vConditionDimCustomReport(1))

Label : <Use Field Name>

Thanks in advanced

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Thu, 24 Sep 2015 20:17:52 GMT

Hi Amien,

1) I have implemented above solution and its working well in SR7 (with 200+ dimensions). I haven't tried on SR12.

2) You can keep the Label : <Use Field Name> as long as you want to keep report column name exactly same as data model column.

in my case, I have used Expression to populate Label because user wants user friendly name for column heading. Sometimes which are different than data model column name. I have created xlsx mapping file which has data model column name and related user define name.

3) I would advise you to use Actual data model column name in the Used Dimension box otherwise you will face major performance issue.

Dimension : =$(='[' & $(vDimCustomReport(1))) should be replaced by actual data model column name

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

amien — Thu, 24 Sep 2015 20:27:09 GMT

Thanks for your reply.

if $(='[' & $(vDimCustomReport(1))) is evaluated .. it will result in a column name which actually exists in the data model.

SR12 worked a bit better. I also going to try to double to server resources. It worked better on my high end laptop

I hope this will work

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

amien — Thu, 24 Sep 2015 20:44:31 GMT

What are your server specs btw? and how much records do you extract on an average usage?

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Fri, 25 Sep 2015 14:06:16 GMT

Amien, I had started with the same approach $(='[' & $(vDimCustomReport(1))) was evaluating into the column name which was already exists in data model. So it will work but the performance will not be good.

1) You can try keeping the expression and add around 100 columns. You will notice perf issue.

2) Later on you can replace that express with actual data model column name in the Used Dimension and it will be much faster.

The reason behind 2) will be better because, Qlik knows what all column (dimension) you have used in your chart while rendering the chart and it will use optimized and much faster way.

For 1) it's 2 step process for each dimension and it cause perf issue.

Re: Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

Fri, 25 Sep 2015 14:09:03 GMT

For my testing the sever used was 4 core CPU, 64 GB RAM and QVW size was more than 1 GB. Data around 1 million+