topic Re: Huge Data Load in QlikView

Huge Data Load

anushree1 — Thu, 18 May 2017 09:42:43 GMT

Hi,

I am trying to load a table of 206 Columns having 16 million records from SAS through Qlikview Desktop.

Its seen that the first 3 Million records get loaded within the 3 Mins but next 1 million does not load even after 2 hrs and qlikview gets shut down , apart from the RAM and disk space could there be any issue causing this trouble

Re: Huge Data Load

avinashelite — Thu, 18 May 2017 09:49:23 GMT

all the 206 columns from the same table? if yes then its taking long time to load the records and QV is getting freeze . Its the issue with the RAM only .

Try like this try to load the data Year wise or by applying the filter and create QVD;s for the same ..so that you could load the data easily

Re: Huge Data Load

anushree1 — Thu, 18 May 2017 09:53:36 GMT

yes all the 206 columns are from same table

Loading them to different qvds would not serve the purpose as i must concatenate them in stage 2 anyways

Re: Huge Data Load

Sergey_Shuklin — Thu, 18 May 2017 09:55:22 GMT

Hi, Anushree!

Is there any way to divide this query on the several smalest? I mean, can you use a filter or something which will separate your huge data on parts? Because a 3 billion cells is a real task even for a QV!

Re: Huge Data Load

anushree1 — Thu, 18 May 2017 09:58:07 GMT

Hi Sergey,

Its in million not billion I did read that Qv easily loads millions and millions of data

Re: Huge Data Load

avinashelite — Thu, 18 May 2017 10:12:50 GMT

Yes it will , how much RAM your System has ? and are you loading any other tables along with this table? any joins ?

Re: Huge Data Load

anushree1 — Thu, 18 May 2017 11:11:14 GMT

Hi Avinash,

I did run through the Server it took around 30 Mins, the size of qvd generated is 12 GB.

But for making changes in application like creating new objects I will have to work on my local which has 8 GB RAM , so I doubt that the application would work in case Load the 12 GB qvd into it.

So do you have any suggestions or workaround on this?

Re: Huge Data Load

avinashelite — Thu, 18 May 2017 11:16:45 GMT

OK so issue is with your local RAM .

Try like this

Load first , only 1 or 10 K records to your app and add all the objects and UI elements to the app then finally push the app to Server and reload the complete data so this way you could open the app in local machine and once the data load is complete in server you will get the complete data too

Hope this helps you to resolve the issue

Re: Huge Data Load

Peter_Cammaert — Thu, 18 May 2017 11:41:42 GMT

Development on your server would be a workaround (every Windows Server includes two RDP connections) when resources are tight everywhere else. However, you may now run the risk of consuming all server resources (RAM & CPU) when you make a mistake in your load script. Your end-users and AccessPoint visitors may not like that.

An alternative is to upgrade your developoment machine to 16GB of RAM. Most laptops/desktops can be upgraded to that amount (more RAM may pose a problem in older laptops)

A third approach is to eliminate all columns from your huge data files that aren't used anyway, or to aggregate everything that will be used in aggregated form only.

Re: Huge Data Load

anushree1 — Fri, 19 May 2017 06:41:18 GMT

Hi Peter,

could you please state what should be the RAM size ideally to enable smooth working of Dashboard having Millions data

Re: Huge Data Load

Peter_Cammaert — Fri, 19 May 2017 07:32:46 GMT

IMHO development machines (PCs) should have as much RAM as possible. Otherwise you'll have to make compromises or implement reductions as Avinash amply explained.

It's a different story for a server where you'll usually have enough RAM but you'll need to find a way to share it with other stakeholders.

[Edit] Ok, that depends. In my region, cost of RAM is way lower than developer time. YMMV.

Re: Huge Data Load

vardhancse — Fri, 19 May 2017 08:23:12 GMT

Hi Anushree,

In real time not required to develop the dashboard with complete volume of data.

As Avinash suggested, best practice is to load some limited set of data and complete the presentation layer.

Post completion of build you can remove the filter in script and place the file in server and load complete data.

Re: Huge Data Load

anushree1 — Fri, 19 May 2017 08:44:13 GMT

I very much agree to it but then the requirements have not freezed yet.I keep getting changes and enhancements to be worked upon and moreover validation is also going on the other end so they need the complete data set with all the requirements as and when its worked upon.

Re: Huge Data Load

avinashelite — Fri, 19 May 2017 09:47:18 GMT

As suggested earlier , either you increase your RAM or follow the approach which I specified . You could add all the UI components with limited load and then push the file to Server and reload ...I am not getting whats the issues with this approach

Re: Huge Data Load

vardhancse — Sun, 21 May 2017 05:46:32 GMT

Hi Anushree,

Business/domain team will not be aware of the technical aspects they will just expect the output.

as a developer its our responsibility to make them understand the technical feasibility.

Moreover never its suggested to load whole set of data and do the build and then place in dashboard.

In that case we need to work in server, as it will have huge RAM and now we can able to load huge volume of data