topic Re: General question on data loading by Qlik in QlikView

General question on data loading by Qlik

Tue, 05 Apr 2016 13:25:54 GMT

Hi,

We have developed around 5 dashboards. Each dashboard has it's own data model now. There is a suggestion to have a single qvd that will cater to all the dashboards. That single qvd will have -

1. More than 150 columns

2. 700k records

3. Will be the result of joining around 10 critical tables in the database

4. Would take a long time (> 45 min) to refresh.

The idea behind this suggestion is -

1. We can one centralized data/qvd which can be used as a source for all the dashboards

2. This will eliminate some duplicate fields being drawn into the dashboards. Ex: Dahboard1 and Dashboard2 both might bring in a Column A. So, if it's done through a single qvd, this duplication can be avoided.

Personally, I feel, this approach will add more complexity in terms of maintainability. Also, there are not many columns that are common among the dashboards. It will be less than 10 columns that are common across and so, I think, keeping individual data model for each qlikview is probably the best.

Please suggest.

Re: General question on data loading by Qlik

marcus_sommer — Tue, 05 Apr 2016 13:38:41 GMT

In my opinien that's not a good idea. Recommended are data-architectures which goes in the direction of 3-tier architecture. The main-point here is to create a division of work which is easier to handle, on which could several people work on the same time and where tasks could run parallel. These concepts are very common and are not restricted to qlikview else to all kinds of IT business.

Re: General question on data loading by Qlik

Tue, 05 Apr 2016 13:42:07 GMT

Thanks Marcus! Could you please elaborate on how 3-tier architecture applies here?

Thanks,

Sudha.

Re: General question on data loading by Qlik

marcus_sommer — Tue, 05 Apr 2016 13:57:15 GMT

Not really better then it is explained within the links from the above mentioned link. I suggest to take some time and read carefully and think even more carefully about your reading and understanding and how it could be adjusted to your concrete environment and requirements. It's not a matter of a few hours of time then on this will be base your future work of the next years.

- Marcus

Re: General question on data loading by Qlik

Tue, 05 Apr 2016 13:58:41 GMT

Sure. Thank you!

Re: General question on data loading by Qlik

HirisH_V7 — Tue, 05 Apr 2016 14:19:19 GMT

Hi,

This may help you,

Merging Data from Multiple Sources

Get started with developing qlik datamodels

-Hirish

Re: General question on data loading by Qlik

Tue, 05 Apr 2016 14:21:03 GMT

Thanks Hirish.

Re: General question on data loading by Qlik

syukyo_zhu — Tue, 05 Apr 2016 14:27:05 GMT

Hi,

You can also create severals qvd and create an application qvw used for datamodel which take all of qvds.

Then, you can use Binary to charge the datas from this application datamodel.

I think that is easier to apply rather than a single qvd.

hope helpful

Re: General question on data loading by Qlik

Anonymous — Tue, 05 Apr 2016 14:27:20 GMT

I would think about having a seperate QVD generator for each of your 10 database tables. If any of the tables are large transactional tables then you could consider incremental loads for them.

So you would now have 10 QVD's. These could be loaded into your Dashboard qvw's directly with optimized QVD loads.

Re: General question on data loading by Qlik

Tue, 05 Apr 2016 14:42:08 GMT

Should we necessarily use a QVD for a dashboard?. Right now, I have 5 QVWs/dashboards and each one has it's own data model with 3-4 tables. Can we leave them as is or should I think about having QVDs for each dashboard?

Re: General question on data loading by Qlik

robert_mika — Tue, 05 Apr 2016 14:45:11 GMT

1 Tier - is your data source loaded into first QVD's (no changes)

2 Tier - Data Loaded from Tier 1 and cleaned,normalized, model concept created

3 Tier - GUI (Design Layout) Data from Tier 2 - no changes to the Fields.

You can have 4 Tier - where you can binary load from Tier 3.

But I belive 3 Tier architecture is the common one.

Re: General question on data loading by Qlik

HirisH_V7 — Tue, 05 Apr 2016 15:02:24 GMT

Hi,

======================

QVD Files

A QVD (QlikView Data) file is a file containing a table of data exported from QlikView. QVD is a native QlikView format and can only be written to and read by QlikView. The file format is optimized for speed when reading data from a QlikView script but it is still very compact. Reading data from a QVD file is typically 10-100 times faster than reading from other data sources.

QVD files can be read in two modes, standard (fast) and super-fast. The selected mode is determined automatically by the QlikView script engine. Super-fast mode can be utilized only when all fields or a subset thereof are read without any transformations (formulas acting upon the fields), though the renaming of fields is allowed.

A QVD file holds exactly one data table and consists of three parts:

A well formed XML header (in UTF-8 char set) describing the fields in the table, the layout of the subsequent information and some other meta-data.
Symbol tables in a byte stuffed format.
Actual table data in a bit-stuffed format.

Purpose of QVD Files

QVD files can be used for many purposes. At least four major uses can be easily identified. More than one may apply in any given situation:

Increasing Load Speed

By buffering non-changing or slowly changing blocks of input data in QVD files, script execution becomes considerably faster for large data sets.

Decreasing Load on Database Servers

The amount of data fetched from external data sources can also be greatly reduced. This reduces work load on external databases and network traffic. Furthermore, when several QlikView scripts share the same data it is only necessary to load it once from the source database. The other applications can make use of the same data via a QVD file.

Consolidating Data from Multiple QlikView Applications

With the Binary script statement it is limited to loading data from a single QlikView application into another one, but with QVD files a QlikView script can combine data from any number of QlikView applications. This opens up possibilities e.g. for applications consolidating similar data from different business units etc.

Incremental Load

In many common cases the QVD functionality can be used for facilitating incremental load, i.e. exclusively loading new records from a growing database.To learn more about the use of QVD files and Incremental Load, follow the link below:Using QVD Files for Incremental Load

Create QVD Files

A QVD file can be created by one of three different methods:

Explicit creation and naming from script by means of the Store command. Simply state in the script that a previously read table or part thereof is to be exported to an explicitly named file at a location of your choice.
Automatic creation and maintenance from script. By preceding a load or select statement with theBufferprefix, QlikView will automatically create a QVD file which under certain conditions can be used instead of the original data source when reloading data.
Explicit, manual creation and naming from layout or via Internal Macro Interpreter. Data can be exported from the QlikView layout via GUI commands or Automation macros. In the GUI you will find QVD as one of the possible export formats under the Export... command, found on the object menu of most sheet objects.

There is no difference between the resulting QVD files, e.g. with regard to reading speed etc.

Reading Data from QVD Files

A QVD file can be read into or accessed by QlikView by the following methods:

Loading a QVD file as an explicit data source. QVD files can be referenced by a load statement in the QlikView script just like any other type of text files (csv, fix, dif, biff etc). The File Wizard: Type handles QVD files by the same principles.
Examples:
load * from xyz.qvd (qvd);load Name, RegNo from xyz.qvd (qvd);load Name as a, RegNo as b from xyz.qvd (qvd);
Automatic loading of buffered QVD files. When using the Buffer prefix on load or select statements, no explicit statements for reading are necessary. QlikView will determine the extent to which it will use data from the QVD file as opposed to acquiring data via the original load or select statement.
Accessing QVD files via the script. A number of script functions (all beginning with qvd) can be used for retrieving various information on the data found in the XML header of a QVD file. These functions are presented under the File Functions in Script functions.

=======

**Source: Copy from help

HTH,

Hirish

Re: General question on data loading by Qlik

Anonymous — Tue, 05 Apr 2016 15:05:30 GMT

I like to go for communal QVD files. These then can be loaded into as many Dashboard qvw's as one wishes. [This is what is called 3 tier architecture]

But I also like to only have as many Dashboard qvw's as are needed and when they are suitable combine them into fewer Dashboard qvw's.