Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Using QVD Files

Hi All,

I am building a dashboard for a warehouse so the management can view what is going on in their warehouses - which are big.  The problem for them is that they are in the midst of a migration project from an old Warehouse Management System (WMS) to a new one.  I solve that problem as QV sits over both databases and allows a consolidated view.

I have just got to the stage (with many thanks to this wonderfully helpful community) where I now have to interrogate a transaction history table - and I have to do this twice.  Once in the older system and once in the new system.  Old system = old slow hardware with more users and more transactions currently.  New system = new super fast system (with fewer WMS records).

The transaction history folder basically holds every transaction to every SKU in the warehouse.  Even though I am only interested in this year's data and restrict the SQL select to older than '01/01/2011', I am coming up against a problem.  I retrieve around 75,000 records from the new system in less than 5 seconds (roughly) and then I retrieve 900,000+ records from the old system in 2.5 hours.

My thoughts were that if I only have to interrogate the live systems for the last month's data, could I read in and save the year's data apart from the last 30 days into a QVD file.  I would still have to open and read in these records whenever the application was refreshed, but as the data now resides in QVD files, I should avoid waiting for the DBMS to service my request and there will be less load on the DB and certainly no contention for the older data.  Right?  The data never changes once it is older than about a week.

Is this the right approach to this problem?  Then once a month I re-run the separate QV application to refresh the data in the QVD file(s) and update my script in the main application to only read in "live records" newer than the latest QVD file creation date from the DBMS.

Hope this makes sense.  And if anyone can send a link to QVD best practises - if that sort of thing exists - much appreciated.  Is there any way to schedule and automate the process of saving the data every week for data older than 30 days?

Regards

Ian

2 Replies
cesaraccardi
Not applicable

Using QVD Files

Hi Ian,
I think you can make this control for the time using variables in the script, like storing the last loaded month and testing during the load and still using the QVD files as you mentioned.

Regards
Cesar

pat_agen
Not applicable

Using QVD Files

hi Ian,

here is a link to Rob Wunderlich's web site. he has a blog with a webinar describing qvd and giving best practices.

http://qlikviewnotes.blogspot.com/2011/02/qvd-questions-and-answers.html

there are also qs & as in his blog pots.

interesting reading.