QVD's from XML

[#1]

Hey all!

So, currently we have created a QVW from around 25 000 XML's.

We have 50 locations which send 1 XML every hour, so that's 1200 XML's a day.

Considering the load is extremely slow i want to create a QVD.

What is the best practice for this? i have read all the QVD manuals on the community as several on different websites, but I cannot seem to find the clear cut explanation i would like to see to create this.

The issue isn't so much the creating of the QVD, but the management of my XML's.

Normally a database / datawarehouse is sacred, but we would like to delete the XML's at a given point.

So say I load all the XML's in my QVW Database, then create a QVD, and a reporting QVW.

The next day i have another 1200 XML's, so i would have to do a full reload over the 35000 XML's to find the new 1200, then open my QVW report, reload the QVD, then get the new data out of my QVW database, and create a new QVD.

That's normal practice i reckon.

However, after 6 months i would have close to 220 000 XML's, the reload time on my QVW database would be insane, for the 25 000 it already takes over 1 hour.

So if i let my system delete all XML's older then say 7 days, would that cause an issue?

My QVW database would only contain 8400 XML's, so the reload would take around 15/20 minutes

My QVD would still hold all historical data which would be filled up with the new data, and i can still report over it right?

I understand this would sound extremely logical or simple for most of you, but i am new to Qlikview and would like to be sure of it before i delete a months worth of data

Also, yes deleting data is a dead sin among programmers and analysts, however considering most of our reports are for a 5 year period it would mean a total of 2.19 Million XML's, this would take up way to much space and memory for the backups.

So could someone answer this for me please

Kind Regards,

Mike

[jonathandienst]

Hi

I would use two qvw files - one a QVD generator that loads the XML file and saves QVDs. This QVD generator would create one XML file per day with the 1200 XML files.The generation process would involve moving the XML files to a temporary location, loading them and saving the XML with a date stamp in the XML file name. Then once the QVD has been created, the XMLs can be moved from the temp folder to an archive location.

This two step process ensures that XML files are not being written to the same location as the QVD generator is using and you cannot accidentally move something to archive that has not been loaded.

The main model then loads the QVDs, either a wildcard load to load them all:

     LOAD .... From Data_*.qvd (qvd);

Or using a For Each loop with a file list which will allow you to selectively load QVDs - for example for the last 30 day:

     For Each vFile in FileList(QvdFiles\*.qvd)

          // parse file name to determine if file should be loaded

          If somecondition = true Then

               LOAD * FROM [$(vFile)] (qvd);

          End If

     Next

The XML archive location can be purged in accordance with some sort of data retention policy.

HTH

Jonathan

[#3 · Accepted solution]

I like the idea, that way i do keep an actual archive in case the QVD crashes.

However i have no clue how to start doing this.

I do however want all the data instead of the last 30 days, simply because we analyse trends over periods of years.