Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joydipp1988
Creator
Creator

Code maintainability and QVD generator

Hello all,

I'm using 3 layer architecture in my project development.

1st Layer:
Generating all QVD's from source data and saving it under Source QVD folder.

2nd Layer:
Using Source QVD's and apply appropriate transformation and generating Transformed QVD's.

3rd Layer:
Building data model using Transformed QVD's and UI and Dashboards.


Situation:

I have more than 50 tables in the database. On which 5 tables are FACT tables.

My question:

How to generate the Source QVD's from the source data?

should I fetch each table one by one or should I use one single QVD generator script using FOR-NEXT loop?

If I use one QVD generator script then how can I apply Incremental load for FACT tables ?


I know how to generate Source QVD's and how to write QVD generator but I need guidence regarding
how to maintain the code and what are the best practices to follow here?

Thanks in advance,

Joy

1 Solution

Accepted Solutions
marcus_sommer

Personally I would tend to use 50 qvd-generator applications - maybe some of the dimension-tables could be grouped into one qvd-generator and even matched to a complete dimension-table (qlik datamodels should be going in the direction of a star-scheme and therefore the data-structures should be rather de-normalized).

Why? The more generators you used the better could they be parallelized and/or running independent of eachother and it will be easier to apply incremental approaches and keep the whole process clear and neat.

Further you should only load fields which you really need within your reports - quite often the databases contain a lot of fields which are only there important (index-fields, flags to control certain routines and probably some more) and some others will be outdated.

This meant it's rather not useful to load the table-fields with a wildcard of "*" else to specify your needed fields for each table. Of course you could put these informations within one or several tables and loop through them like indicated from Anton above and creating load-statements on the fly. But then you would add a lot of complexity and deteriorate the maintainability. I have done this before and use it actually to some parts of my tasks but I couldn't really recommend it and I will change it with the next major-release of our reporting. Nevertheless here an example of them: Re: Entire load statement in a Variable.

Beside them it's often useful to put some transformations within the qvd-generators, for example creating unique keys to be able to apply incremental loadings, cleanings of fields, replacing NULL's, formatting fields or splitting fields - a good example for this is the split of a timestamp into a date- and time-field - you will save a lot of space within the qvd with an appropriate splitting and formatting and therefore it will be useful to apply it within the very first step instead of transferring it to later transformings. This seems against the pure doctrine but it's very practical and you would need some more architecture-layer (and efforts and resorces for them) to keep it pure.

- Marcus

View solution in original post

5 Replies
Not applicable

Hi Joy,

It would be better if you fetch each table and load into QVD.

You cannot use incremental load in a single script in for loop.


--Ashok

asgardd2
Creator III
Creator III

I think you may load  no fact tables in loop and fact tables one by one(if you use incremental load)

For no fact tables you can pre-creating a inline table with the names of tables in the database, and pass it on each line, then xtractor is reloading (in "for  ... next")

marcus_sommer

Personally I would tend to use 50 qvd-generator applications - maybe some of the dimension-tables could be grouped into one qvd-generator and even matched to a complete dimension-table (qlik datamodels should be going in the direction of a star-scheme and therefore the data-structures should be rather de-normalized).

Why? The more generators you used the better could they be parallelized and/or running independent of eachother and it will be easier to apply incremental approaches and keep the whole process clear and neat.

Further you should only load fields which you really need within your reports - quite often the databases contain a lot of fields which are only there important (index-fields, flags to control certain routines and probably some more) and some others will be outdated.

This meant it's rather not useful to load the table-fields with a wildcard of "*" else to specify your needed fields for each table. Of course you could put these informations within one or several tables and loop through them like indicated from Anton above and creating load-statements on the fly. But then you would add a lot of complexity and deteriorate the maintainability. I have done this before and use it actually to some parts of my tasks but I couldn't really recommend it and I will change it with the next major-release of our reporting. Nevertheless here an example of them: Re: Entire load statement in a Variable.

Beside them it's often useful to put some transformations within the qvd-generators, for example creating unique keys to be able to apply incremental loadings, cleanings of fields, replacing NULL's, formatting fields or splitting fields - a good example for this is the split of a timestamp into a date- and time-field - you will save a lot of space within the qvd with an appropriate splitting and formatting and therefore it will be useful to apply it within the very first step instead of transferring it to later transformings. This seems against the pure doctrine but it's very practical and you would need some more architecture-layer (and efforts and resorces for them) to keep it pure.

- Marcus

joydipp1988
Creator
Creator
Author

Hi marcus_sommer‌,

Superb explanation. I'll apply this procedures in my current and future development.

Thanks,

Joy 

rahulpawarb
Specialist III
Specialist III

I agree with Marcus.

If we have independent dimensions or facts then we can use parallel approach; else we have to use serial one.

Further to loading selective fields we have load only required records (if table has archived/old version of master data).

Also use the suggested structure to maintain the generated QVDs (as well as other objects) for better maintainability as given below (this is just sample; you can customize it as per your need).

Folder_Structure.jpg