topic Re: Google Map calculates extremely slowly in QlikView

Google Map calculates extremely slowly

kavicsgym — Mon, 02 Feb 2015 10:56:37 GMT

Hi,

It is part of a SIB where we have:

1. invoice / sales data from SAP in a Fact table (not more than 1 million rows)

2. Customer Dimension table with Customer ID and name (1000 customers)

3. Geographic Dimension table with Customer ID and Long / Lat data (I only have Long/Lat data for 200 customers)

The 3 tables are associated with a composed key (SAP Client/Mandant number + Customer ID).

The data volume is rather low, a couple of months data only. Now, if I select a single month, the map/scatter chart calculates in 3 minutes. If I select 1 month /1 customer the chart calculates in 30 sec. But if I select several months, it starts calculating and ends in “not responding”.

Any idea why it is so slow? Keys are OK. In the end we will have 50 million records …..

Thanks and cheers, Enikö

Re: Google Map calculates extremely slowly

arthur_dom — Mon, 02 Feb 2015 18:06:19 GMT

hi Enikö .

What kind of Google map call are you using? Qlik Sense API , GeoQlik ou as background Image?

Re: Google Map calculates extremely slowly

Colin-Albert — Mon, 02 Feb 2015 21:32:17 GMT

If your data is shown with the same expressions & dimensions in a straight table, do the calculation times change?

If not, then look at optimising your expressions & dimensions, the problem is not Google maps.

Re: Google Map calculates extremely slowly

kavicsgym — Tue, 03 Feb 2015 09:40:10 GMT

Hi, I use Google MAP API in QlikView, not QlikSense, not GeoQlik. The 'usual' Scatter plot with Latitude, Longitude and Sales Value expressions and dynamic image in the background of the scatter chart.

Any hint?

Re: Google Map calculates extremely slowly

kavicsgym — Tue, 03 Feb 2015 09:50:47 GMT

Hello Colin, thanks for your reply.

I changed the scatter chart into a normal Straight Table, cleared the slection (1 month, 1 customer) and it is not respondig for 2 hours now 🙂

In the same document, I have 10 other tabs with plenty of objects and they all work fine with same expressions (sum of sales, etc.).

This has sg to do with Lat/Long calculations in my honest oppinion.

I even disabled the 3rd expression (sum of sales) but same result

Do you think it matters that I only have Long/Lat data for a minority of the customers?

My other idea is that data is in 3 tables now, 1 fact and 2 dimensions (customers, lat/long data). - but it is a clear model.

Which could be more responsible for such a bad performance? Enikö

Re: Google Map calculates extremely slowly

jpenuliar — Tue, 03 Feb 2015 09:54:41 GMT

Hi Enikö,

Can you post a sample file, codes or table view to look at.

Re: Google Map calculates extremely slowly

Colin-Albert — Tue, 03 Feb 2015 11:38:52 GMT

Can you push the lat / long calculations into the script rather than in the chart.

How granular do you need the lat / long calculations for each location?

Can you map a town or area to last long rather than mapping each individual site?

You could then link a lat/long table to the town or area field rather than calculating it for every customer.

Re: Google Map calculates extremely slowly

kavicsgym — Tue, 03 Feb 2015 11:43:41 GMT

Hey, I can only attach a snipet from the Data Model which I have described above.

Also the 'empty' map chart.

This is the very standard Google Map API implementation that is published all over the forums. ('Step by step ...)

Script is from a working app.

And it works here as well, just with very limited selection.

Issue is when slightly more data is slected...

Appreciate every feedback. Enikö

Re: Google Map calculates extremely slowly

arthur_dom — Tue, 03 Feb 2015 11:46:46 GMT

You can make some points to make it better, like pre-filter points, or eliminate Clients without Lat/Long using set analysys.

I think the better way is to reduce calculation

Re: Google Map calculates extremely slowly

kavicsgym — Tue, 03 Feb 2015 11:54:59 GMT

Well, we have to show the map by customer, not by town or area.

What do you mean by: " push the lat / long calculations into the script rather than in the chart"

The chart expressions (below) should be calculated in the script as 2 fields and just sum them up in the scatter chart?

Longitude:

=sum( round (256*pow(2,($(var_zoom)-1)))+( Longitude *((256*pow(2,$(var_zoom)))/360)))

Latitude:

=sum(((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((Latitude)*pi()/180)))/(1-(sin((Latitude)*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))))

This is a good idea, but still does not explain the performance issue I am having...

I attached below some samples.

Thanks a lot, Enikö

Re: Google Map calculates extremely slowly

jpenuliar — Tue, 03 Feb 2015 11:59:39 GMT

Can you try and link Lat/Long Table only to Customer Table, as currently it is also associated with your Fact table.

Re: Google Map calculates extremely slowly

kavicsgym — Tue, 03 Feb 2015 20:03:34 GMT

I will try to apply map lat/long into Customers - otherwise I need the same key for all 3 tables.

(Key is SAP mandant number + Customer ID and it might happen that same Cust ID occures in different SAP mandandt so we need this composed key.)

Re: Google Map calculates extremely slowly

kavicsgym — Tue, 03 Feb 2015 21:47:41 GMT

Hi Arthur,

you gave me a good idea!!

To eliminate Clients without long/lat I used Caluclated Dimension: + Supress when Value is Null to exclude any calculations where Lat or Long is null()

=IF(len(Latitude)>0 or len(Longitude)>0, KNA1_Key, null() )

Now the chart calculated way faster, after Clear all in 1-2 seconds!!!!!

Now if also optimize the tables (merge Customers and Geodata into one) that might make it even better.

About Set Analysis, any ideas how to eliminate Clients this way? I tried sg- see below - but my Lat/Long values not simply 0 but they are not existing in the data modell, so how to filter these out with set anaylssis? I am just curious 🙂

{$<Longitude = {">0<0"}>}

{$<Longitude = {">0", "<0"}>}

Thanks a lot! Enikö

Re: Google Map calculates extremely slowly

Colin-Albert — Tue, 03 Feb 2015 22:37:44 GMT

You can test if len(longitude) >0 in the load script.

If(len(longitude) > 0 and len(latitude) > 0, 1 ) AS LatLongOK

Then you can include LatLongOK={1} in your set expression to exclude data with invalid values.

Re: Google Map calculates extremely slowly

kavicsgym — Thu, 05 Feb 2015 09:54:16 GMT

Hi Colin.

Thank you very much for your replies. In the end, both solutions worked fine,

- one with flag on the script and excluding values in the front-end (actually, we set an Action to the map page OnActivateSheet and to Select In LatLongOK Field only good values)

- and the other one with Caluclated Dimension: + Supress when Value is Null

It is a good learning that QV calculates rather slow in such case when Long/Lat data is present only for a minority of values.

Thanks again to everyone, who repled. Enikö