Architecture of Qlikview Application according to best practices

[#1]

I would like to start a discussion about the architecture of Qlikview Application according to best practices.

While developing Qlikview application I came across many situation where

I need to write a complex query including multiple joins and filter conditions on database to fetch the data and put it into QVD to satisfy a requirement. This I had done because the dataset was very huge about (~20 millions of records) and database can perform joins and apply filters between multiple tables better than Qlikview. That time I could able to do this because I had Sales & Activity database and I am using common concatenated GIANT fact table approach in Qlikview with a column specifying the records type to create metrics upon them. This GIANT was surrounded by different dimension table.

In this way whenever I get a new requirement, I find out if I cannot fulfill it by existing GIANT fact, I create a query and then its QVD and add this QVD data to the GIANT fact table with identifying column giving a new value for these records, so that if necessary I can identify it at UI level.

I am referring GIANT fact table because it is really very big in terms of columns as well as records. And it is getting huge and huge day by day as requirements are coming. So far I am able to satisfy the requirement, but my question is that – Is it a good approach to fetch raw data from database and create QVDs and then put Transformation layer in between of Extraction and Datamodel layer every time whenever we start with Qlikview project? Or just put the transformation logic in sql query and create a new QVD everytime to satisfy the requirement?

Whatever I have done in SQL query, I know that I can do it in Qlikview in transformation layer but is it a good approach in terms of performance, flexibility and maintenance with the fact that database can perform complex operation such as joins, aggregations, filters, selections better than qlikview?

Here the Extraction script is the one which create raw QVDs from database. It may have some logic but that is going to be required for all Qlikview applications that are going to be build using these QVDs.

The Transformation script is the one which creates necessay tranformed QVDs for a datamodel to consume. This may have the complex logic, aggregation, filters as per the requirements.

The Datamodel script is the one which load the transformed QVDs and creates necessary relationship in memory so that UI can be build upon it.

Please correct me if I am wrong anywhere.

[hic]

The main reason to create a qvd layer is if you want to use the same data several times, e.g. in several qvw files or in the same qvw but at different points in time. Another reason could be that you do not want your users to access the DB, but instead pick and choose from a set of prepared files. A third reason could be that you want to “clean” and transform the data before the users should be allowed to consume the information.

So, yes, in many cases it is a good practice to have a qvd layer; a layer that contains some files with raw, untransformed data, other files with transformed data.

On the other hand, the above reasons are exactly the same reasons why you should have a data warehouse created with a proper relational database (RDBMS). The two steps you mention – Extraction and Transformation – can easily be made in a data warehouse. So, to me, the question boils down to “Why should I use qvd files instead of a RDBMS?”

The answer is that a solution with qvd files is simpler and cheaper. And you will have it up and running faster than a RDBMS. But, on the other hand – you can’t do as much as you can with a RDBM system. So if you have advanced requirements, you should probably put most of your ETL stuff in a RDBMS instead.

HIC

[#3]

Hi Henric,

Thanks for your suggestions. They are really good.

Still I have some more doubts. How can we classify the advance requirements? As per my knowledge all things which can be done through queries, can also be done using Qlikview script. The only problem which we can face is that, the perfomance in Qlikview may not be good.

Also lets say till now we are following the cleaner approach of creating Extraction, Transformation and Datamodel. Suddenly we cannot jump to the approach where we start writing the COMPLEX business logic in sql Query and put it into Extraction and created QVDs out of it. This way we are diverting from our cleaner approach by removing Transformation layer for some COMPLEX Business logic and for SIMPLE business logic we are using it to tranform data.

After some point of time we would end up having mixed approach which is very hard to understand for a new commer in project and it is also not very clean and flixible.

I would also like to know that, is there any best practices document which Qlikview provides for architecturing Qlikview application at enterprise level?

Thanks & Regards,

Anosh Nathaniel

[#4]

Hi,

I found some usefull links on Qlikview community for the same issue:

http://community.qlik.com/message/312097#312097

http://community.qlik.com/message/76251#76251

http://community.qlik.com/message/284328#284328

But my questions on the above post are still open for discussion.

Thanks & Regards,

Anosh Nathaniel

[#5]

Hi Anosh,

This is quite an interesting topic. I always wanted to make a doc but i couldnt come to a conclusion what is the best practices or the best architecture for QV.

I would say a 3-tier architecture is better if you take data from multiple sources.

First Layer:

The first layer would do raw data extraction to QVD.

If you have a DW, like BW, BO, MS SQL, do as much as you can in those DW.

Because when it comes to UAT, it helps the user in checking and comparing data between the source and QV.

Second Layer:

The second layer is basically consolidating all the data from QVDs and form the STAR.

This layer is important, because we can use this to track the source of each columns.

Sometimes when you have too many data sources, you really want to build something to be able to tell the source quickly. So in each load, you can make a new fileds says - ORIGINE, and store only the name of the source.

Because we have the STAR here, then you can start to populate common dimension in list box to see if the data is good and standardise, if it doesnt, you can do some clean up in thi s layer.

Just to clarify that the STAR i make, i have my dimension (fact-table) in one single table and linked via a key with all the other tables which store only key and their figures.

Here is an example, for each data source, i'd do...

LOAD KEY,

          Figure1

          Figure2

FROM ABC.QVD

Load KEY,  << for second sources onward, use concatenate()

         ZONE,

         COUNTRY,

          'ABC' as ORIGINE

FROM ABC.QVD

Of course you can scipt it any way you like, but i find this method is easy to maintain. of course it has it own limitation. One thing to take note, you only need one KEY column, if you create a KEY for each data sources, over time, the performance will be impacted and also the size of your app.

I have also seen to do the architecture the other way, meaning, to have all the different figures stored into one single table, to reduce the path, since QV works faster in flat table, i have tested this architecture, but it failed due to the number of data i have. so, if anyone has some other scripting method, feel free to share.

Dashboard Layer:

Here in the script part i do load * from QVD.

and then, the wow factor.

I find scripting like this is efficient compared to other method i have tried.

It's not neccessary to use STAR schema, i have done some projects without STAR and later i regretted, the only reason is it's difficult to maintain or to expand the app in future.

Above is solely my own opinion, and for reference purpose only

I wont say this is the best practices and would like to hear from others.