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.