Have you ever thought it might be interesting to store a Qlik data model into a single QVD? This can be useful in a number of cases such as:
- Archiving (and retrieving) data models.
- Overcoming the “single binary load” restriction.
QlikView Components (QVC) Version 11 introduced two new routines to do just that:
Qvc.ExportModel — Exports all tables of the current model into a single QVD.
Qvc.ImportModel — Import a data model created by Qvc.ExportModel.
Even if you don’t have QVC V11 installed, you can try Qvc.ExportModel right now using http include. Add these lines to any QlikView script (instructions for Qlik Sense further on down in this post).
$(Must_Include=https://github.com/RobWunderlich/Qlikview-Components/releases/download/v11.1/Qvc.qvs); CALL Qvc.ExportModel CALL Qvc.Cleanup
Mind the wrap. The Must_Include should be on one line. Using QVC requires the Qvc.qvs library be included (usually at the beginning of script), CALLing Qvc routines, and CALLing a Cleanup routine at the end of your script.
Assuming this script is included in “Sales Dash.qvw”, the default exported model QVD will be named “Sales Dash.qvd” in the same directory.
Now, to import this QVD model into another qvw, replace the CALL to ExportModel in the above sample with:
CALL Qvc.ImportModel('Sales Dash.qvd')
The original model will be reconstructed as individual tables.
Qvc.ExportModel has three optional parameters:
CALL Qvc.ExportModel(['qvddir'],['qvdname'],['addTimestamp']);
| Parameter Number | Parameter Description |
|---|---|
| 1 | String. Optional. Relative or absolute directory where the model QVD will be stored. If relative, it follows the same rules as the STORE script statement for relative directory. |
| 2 | String, Optional. Name for the model QVD. If omitted, the name of the QVW will be used. For example, if QVW is “Sales.qvw”, then QVD will be “Sales.qvd”. |
| 3 | String, Optional. 1/0 True/False. If True, a timestamp of the form _YYYYMMDDhhmmss will be appended to the QVD name. Default if omitted is False. |
Qlik Sense has no default path so parameter #1, a lib:// for the QVD should be specified. Alternatively, if a lib has been established with a DIRECTORY statement, parameter 1 can be omitted.
Qlik Sense will require a web file Connection for the http Must_Include.
After defining the web connection and having an appropriate folder connection to store the QVD in, Qlik Sense script would look like this:
$(Must_Include=lib://QvcWeb);
Call Qvc.ExportModel('lib://QVData')
CALL Qvc.Cleanup;
That’s all there is to it! If you are already using QVC, I hope you’ll find these routines a welcome addition to the library. If you are new to QC, explore more at QlikviewComponents.org.
-Rob
Thanks to Jörgen Peerik for raising the single-QVD export idea during a QVC class.
Nice one Rob! I haven’t tried this out yet, but are there any metadata added by Qvc.ExportModel to the resulting QVD? For example somehow appending table names to field names so that the single QVD could be blown back out into individual logical tables on reload.
Slightly related question — is there (or will there be) a Qvc.ImportModel routine?
Hi Bill,
Yes, there is a Qvc.ImportModel routine. It’s mentioned in the post — although I guess it doesn’t stand out enough.
Metadata is added by ExportModel to allow ImportModel to reconstruct the individual tables. The metadata is two additional fields added to each row:
Qvc.ExportModel.TableName
Qvc.ExportModel.FieldNames
very interesting, Rob. Can you elaborate on its usage for “Overcoming the “single binary load” restriction.”?
Also did you test this on larger data sets? I assume it would take a while to import/export if the source data model has a 100 million rows?
Hi Boris,
I have not tested the performance on large data sets. I imagine it may be a bit slow, but nevertheless functional.
Only one BINARY load is allowed in a script. I sometimes see people wishing they could do more than one binary load. If the models have been exported, then using multiple “Qvc.ImportMode(‘xyz’) in a script a workaround that lets you achieve something like a “multiple binary load”.
Thanks Rob – will have to try this out. The restriction of a single Binary Load has been an issue in the past.
Thanks so much!
Hi Rob,
that is a nice feature.
What is the speed compared to storing + loading (optimized) of set of qvds?
This would be much slower than storing and loading individual QVDs.
Excelente post. Es de suma utilidad para nosostros. Gracias.
Rob, I tested this and it is another great addition to the QVC library. I see a lot of potential for using this in my work. Thanks!!
Hi Rod. It’s very usefull. It’s simple and pretty efficient
Do you know witch one is faster to run: Store each table in a QVD file, or Use the QVC ExportModel?
thanks!
It’s much faster to store into individual QVDs.
Hi friends,
i want to know can we use where condition with Call Qvc.ExportModel or not and how can i reduce the size of the model.
There is currently no provision for a where clause or other filtering in Qvc.ExportModel.
You could reduce the overall size of the model before calling Qvc.ExportModel. Or are you trying to keep the full model but store a smaller version of it?
Hi Rob,
Only me I have errors with QlikView 12.20 and QlikviewComponents-11.2 ? Semantic error
I’m not sure what causes the error, but you can workaround by adding this as the first line of your script:
///$bnf off
Thanks Rob , I wrote on github.
https://github.com/RobWunderlich/Qlikview-Components/issues/71
Hi Rob,
It is working fine , also using Binary load.
I have requirement to pass Binary load with Variables, this Binary load pass variables are one is .QVW file location and .QVW file name, when i need to pass variables to Binary load then it will not create .QVD file from above .QVW file.
So Can you please help me to resolve this.
Regards,
Bakshu
I’m not sure where your problem may be. Can you post the script you are using or ask this question over on QlikCommunity where it is easier to post examples?