Saturday, January 10, 2009

Archive Data

We sometimes use QlikView as a method for capturing and saving data for archive purposes. These reports aren’t normally used unless someone needs to review historic data or research data to answer a question. For example, if we have a project where we might need to check on what the product master looked like six weeks ago (most companies only care about current master data and they don’t keep track of historic master data), then we might set up a daily job that loads a QlikView document with the product master and any associated data. Then, the document is simply saved in case it is needed. We usually give the document file a filename containing the date to help organize the files.

This advantage of this method over some kind of data dump file is that the method for reviewing the data (QlikView) is automatically part of the process. Anyone who needs to review the data need only click on one of the files in the archive folder. The data in QlikView qvw files is stored in compressed form so no further zipping is needed to conserve disk space.


Anonymous said...

an example pls to show how to do it..thnx - Arun Mittal

-TB said...

Hi Arun,
This posting is not so much about a specific technical solution as about a project management or architecture choice.

Here's the key thought:
If your project has a requirement to periodically save data simply so that it can be examined some time in the future if it is needed; then consider saving the data in a QlikView document instead of some special backup or technical archive technique such as database dump files.

Ramunas Balukonis said...

Hi, Tim

Sometimes you need to save QV document without data (to save disk space). As QV does don't have metada management nor version control, I created miniscript to load, reduce QV document and put new "empty" QV document into new folder. I scheduled this script before everyday QV docs reload. All my QV document takes 30 GB, and "empty" QV document takes 0.5 GB for 3 months. There is the script:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolders = objFSO.GetFolder("z:\vitrinos\")

FindFileR objFolders

Sub FindFileR(ByRef objFolders)

Set objSubFolders = objFolders.SubFolders
Set objFiles = objFolders.Files

for each File in objFiles
If Right(File.Name, 4) = ".qvw" Then
ReduceData File.Path, File.Name
End If

for each Folder in objSubFolders
FindFileR Folder

Set objSubFolders = Nothing
Set objFiles = Nothing

end Sub

Sub ReduceData (FilePath, FileName)

Set qlik = CreateObject("QlikTech.QlikView")
Dim NewFileName
NewFileName = "C:\sql_scripts\QV Daily\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "_" & Replace(FileName, ".qvw", "") & "_" & Replace(Replace(Replace (FilePath, FileName, ""), "\", "_"), ":", "_") & ".qvw"

If objFSO.FileExists (NewFileName) Then
objFSO.DeleteFile (NewFileName)
End If

Set Doc = qlik.OpenDoc(FilePath)
Doc.SaveAs NewFileName

Set qlik = Nothing

End Sub