Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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ö
Can you try and link Lat/Long Table only to Customer Table, as currently it is also associated with your Fact table.
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.)
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ö
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.
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ö