Binary load and reduce data after reload

[SanderHovestad]

Hi,

We have a single Qlikview document which holds data of all of our companies, customers and years of orders and invoices. This DWH-like QVD holds a Link Table joining all facttables and dimensions.

On top of this QVD we have built several dashboards using a binary load. In these dashboards we don't need all the data (e.g. just data for last 2 years or just data of 1 customer). Also the weight of all this data slows nprinting down a lot (it takes 3 hours to refresh metadata). So to keep the dashboards "lean and mean" I'd like to minimize data after the binary load, like this:

final_Orders:
NOCONCATENATE LOAD * Resident f_Orders where year(OrderDatum)>=2020;
DROP TABLE f_Orders;

I did this for the Orders and Invoices facttables and the customer dimension.

This does minimize the # of rows in 3 tables, however as all is joined together in the Link Tables this still has an enormous amount of rows. So it's not reducing in the way I need it to?

Next, I tried to include a macro that after reload sets a few selections and then does the reduce data. This works in the Desktop client but not when using a scheduled refresh from command line (QVD is not saved after reload, even not when adding the Save command to the Macro).

Any one with some best practices here who can help?

[edwin]

My understanding is that you build multiple data models with specific business rules such as Last two years for all customers, Customer = 'Customer1', etc..

i would build this not through script but through QMC using QMC reduce capabilities.  i believe this generation of the QVWs would be faster that having to reduce the individual tables by script, then reducing the link table via inner joins.

this however will introduce multiple middle data models to be used in you dashboard binary loads.

if this strategy does not work and you still want to reduce through script, then you need to reduce your fact, get the unique keys from your fact table that is also a foreign key in your link table and then inner join it to your link table - to also reduce your link table

you want want to test both strategies and weigh the pros and cons.  hope that helps

[stevejoyce]

You can also Left Keep all your tables after you reduce your f_orders table.  Setup an inline table with your from table, to table, key(s), and loop through all tables in the inline table with a Left keep (<to table>). 

Would that help reduce your size down?