I am learning how to use and create QVD files.
Besides and quicker speed and reduced database use, we want to use them to create a way to do the following:
We have a production management dashboard that I created. This includes workstation load and capacity, worker efficiency, job information, and employees currently working. We want to be able to refresh the data linked to the currently working portion every 5 minutes, but have the rest of the data refreshed twice a day.
The current script contains roughly 32 tables, out of which 5 of those tables are what we need refreshed every 5 minutes.
We thought that creating QVD files for the tables we want to refresh twice daily and replacing them in the script would be a solution. The QVD creating would then happen twice a day, and the tables we want refreshed every 5 minutes would remain on the script and the entire document would have this refresh added to it effectively pulling only the needed tables, and the data from the QVD's.
I have looked into the partial reload, but it seems like it would not work well from the server side with scheduling the reload.
My questions are these:
What would be the best way to create these QVD files? IE: adding the needed tables to a new document and creating all QVD's there (Worried about linking), or just doing qualify loads for all the tables in one QVD and worrying about doing the linking and data reduction in the main document?
To make it a little harder we would also be doing an Incremental Insert, Update, and Delete, so I would need to use the following for each table?
Let ThisExecTime = Now( ); QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(ThisExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERE NOT EXISTS(PrimaryKey); Inner Join SQL SELECT PrimaryKey FROM DB_TABLE; If ScriptErrorCount = 0 then STORE QV_Table INTO File.QVD; Let LastExecTime = ThisExecTime; End If
Other Question: What does this mean by PrimaryKey?
Also attached is a picture of the current table linkage. The circled tables are the tables we want refreshed every 5 minutes.
Any ideas on the best way to proceed?
Untitled.png 135.6 K