Select Page

In my last blog I went over some of the basics on what QVD files are and how they serve the Qlik Sense engine. This time I’m covering some of the more technical aspects surrounding actually generating QVD files.

One of my observations when trying to implement a load strategy was the lack of information available on the internet, most answers were limited to “do a loop”… Well that’s great, but there is a whole lot more to think about..

Below is the full set of initial code that I wrote for generating QVD files. The strategy is pretty straightforward, go into the target SQL Server database information schema, create a subfolder for the source, and grab all the tables, then loop through those and build a QVD for each where there is data within the source table.

SET ThousandSep=’,’;
SET DecimalSep=’.’;
SET MoneyThousandSep=’,’;
SET MoneyDecimalSep=’.’;
SET MoneyFormat=’$#,##0.00;-$#,##0.00′;
SET TimeFormat=’h:mm:ss TT’;
SET DateFormat=’M/D/YYYY’;
SET TimestampFormat=’M/D/YYYY h:mm:ss[.fff] TT’;
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale=’en-US’;
SET CreateSearchIndexOnReload=1;
SET MonthNames=’Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec’;
SET LongMonthNames=’January;February;March;April;May;June;July;August;September;October;November;December’;
SET DayNames=’Mon;Tue;Wed;Thu;Fri;Sat;Sun’;
SET LongDayNames=’Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday’;

//QVD load variables below:

SET vCount = 0;
SET vTotalTables = 0;
SET vTableName = ”;
SET vTableCatalog = ”;
SET vTableSchema = ”;
SET vTableFullName = ”;
SET vRecords = 0;

//Enter the source database connection name:

SET vSourceConnection = ‘msbd’;

//Enter the target folder path location where the QVD files will be kept:

SET vTargetConnection = ‘lib://QVD’;

//Continue to the end even if there are errors, there doesn’t seem to be any useful error handling:

SET ErrorMode = 0;

//Turn off security modes so we can run EXECUTE statements (note that these statements are added to the
//C:\ProgramData\Qlik\Sense\Engine\settings.ini file and under engines in QMC standard mode is unticked:

SET StandardReload=0;
SET OverrideScriptSecurity=1;

//The actual path of the target file connection – I haven’t figured out how to strip this from the connection:

SET vTargetPath = ‘C:\Qlik\’;

//Figure out if there is a subfolder contained for this particular database already:

SUB DoDir (Root)
FOR EACH Ext IN ‘qvd’
FOR EACH FoundFile IN FILELIST (Root&’/*.’ &Ext)

FileList:
LOAD
‘$(FoundFile)’ as FullPathName, // could be useful for removing QVD files based on size
FileSize(‘$(FoundFile)’) as Size, // could be useful for removing QVD files based on size
RIGHT(‘$(Dir)’,INDEX(‘$(Dir)’, ‘/’)-1) as FileDir
AUTOGENERATE 1;
NEXT FoundFile
NEXT Ext
FOR EACH Dir IN DIRLIST (Root&’/*’ )
CALL DoDir (Dir)
NEXT Dir
END SUB
CALL DoDir (vTargetConnection);

LIB CONNECT TO $(vSourceConnection);

table_list:
SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES;

LET vTotalTables = NoOfRows(‘table_list’)-1;
LET vTableCatalog = PEEK(‘TABLE_CATALOG’);

//Create a folder for the data source on the qlik server, if it aready exist nothing will happen

EXECUTE cmd.exe /C mkdir $(vTargetPath)$(vTableCatalog);

//Loop through the source information schema for each table

DO WHILE vCount < vTotalTables

LET vTableName = PEEK(‘TABLE_NAME’,$(vCount));
LET vTableCatalog = PEEK(‘TABLE_CATALOG’,$(vCount));
LET vTableSchema = PEEK(‘TABLE_SCHEMA’,$(vCount));
LET vTableFullName = PEEK(‘TABLE_CATALOG’)&.&PEEK(‘TABLE_SCHEMA’)&.&PEEK(‘TABLE_NAME’,$(vCount));

$(vTableFullName):
SELECT
*
FROM
$(vTableName);

//Figure out if the source table has any data within and if not don’t create a QVD:

LET vRecords = NoOfRows(‘$(vTableFullName)’);

IF vRecords > 0 THEN

STORE $(vTableFullName) INTO $(vTargetConnection)/$(vTableCatalog)/$(vTableFullName).qvd (qvd);

//Purge the temporary table from memory:

DROP TABLE $(vTableFullName);
LET vCount = vCount + 1;

ELSEIF vRecords = 0 THEN

DROP TABLE $(vTableFullName);
LET vCount = vCount + 1;

END IF
LOOP

As I mentioned above this is some initial code which I have written to generate QVD files, and I will likely update this blog as I flesh it out more. My thoughts are that a configuration table of sorts should be generated based on stakeholder input, this would perhaps: exclude columns which are not required, rename columns inline with business names and most importantly implement a load strategy based on business knowledge rather than doing what is essentially a full reload each time. I’m also a little uneasy about switching the security to legacy mode, however I haven’t been able to find much information on full ramifications of doing this, and the only other way to I can think of to run shell scripts would be via some sort of extension.

Till next time, happy Qliking!

Thomas – MacGyver of code

Thomas blogs about reporting platforms, data warehousing and the systems behind them.

 

You can read all of our Qlik Sense blog posts here.

%d bloggers like this: