I have been working on QlikView since two years as a developer and have been doing extensive development and admin work. The client with whom I am working with is a Retail gaint and they are growing exponentially, hence the data size is also more. There are few KPI's which are calculated in QV from the data coming from the Transactional ERP DB. Currently, the architecture is as below:
ERP -> QVD Generators -> QVD's -> Data Model QV-> Datamodel file -> Dashboards
Now we are thinking to bring in a standard SQL server dataware house where we can have all the ETL performed and the aggregated data can be stored in SQL DB instead of QVD generators.
I wanted to understand the pro's and con's of this approach.
Please share your thoughts on this and help me choose the right approach.
Qlikview both Reporting & ETL tool .....
We can perform both the reporting and ETL work in Qlikview but the ETL in the sense not to the level of the INFORMATIC .
Qlikview have a variety of function using which you can extract , Transform your data according to our needs ...and then finally Loading the data to application that is .qvw ...
I suggest it is worth investing in separate Data warehouse for a company like you where data growth is too much.
Having separate data warehouse will help you to get aggregated data faster, this way you can make data available to your users on or before time.
If you push things to Qlik, then as data increases you may find that the entire process is taking lots of time. So data availability at right time may be difficult.
Thank you Kaushik for the quick response.
Should I do some math before I go for this approach? Or Is there a document available to stating this kind of architecture?
There is no document which will tell you this.
This is what I felt as per my experience in IT industry.
Qlikview is an ETL means when we are creating QVDs it is fetching data from one source and saved into target qvd file.
-->at the same time we are using Incremental types like Insert only ,Insert Update ,Insert Update,Delete so this also ETl
-->Transformation is nothing but if table is have Different data type of data and we are converting that data into another different type of data (Ex:-date#,Date,First Name&''&Last Name as Full Name,Max(esal)as sal group by Fields....etc)
the above all conditions we will apply in ETL process either it may be DataStage,Informatica,SSIS...etc
If you are moving Sql server data base for that it is recommended to take SSIS as your ETL tool .
For Huge amount of data Comparatively SQL server is slow with ERP any way you have a chance to Data Archiving with client approval.
Create almost all tables (Fact Tables & Dim Tables) as QVD's in Qlikview so that your application speed will also increase.
-->while you creating ODBC connection, create Windows authentication.