to stage or not to stage?

[tracysmart]

I have worked on quite a few QlikView projects to date and most of them involve using a staging DW database between the live production system and the first layer of QlikView.

I am currently working on a project where the customer does not have a staging database and therefore all of the ETL and Business Logic is being coded inside the QlikView QVD layers, which in my opinion, is far more cumbersome than coding in say SSIS using SQL. It also locks the logic inside QlikView so should another product want to report on the data, it too has to go to source and define the logic again.

My question to you, my peers, is this...do you try and implement a staging database first if you are able to and what would be considered as best practice?

Is it just down to personal preference and where your skillset lies?

[Miguel_Angel_Baeyens]

Hi Tracy,

This is a very good question indeed, and one I have to face every time I show Qlik to a new customer. There are several points to take into consideration, in no particular order:

• QlikView is not an ETL tool. Yes, it has the scripting, yes you can do transformations and aggregations but that is not the core of the QlikView in-memory association engine (QIX)
• It is not QlikView which needs the DW, is the business. As you mention, what if you want to reuse the logic? Then you have to replicate it or dump QVD into CSV files that you can use in a different tool.
• Performance: from the source to the analysis, how many steps the information needs to take in order to make sense? If the source table is an analytical table, that could be it, and likely your ETL requirements are very few. However, if your source table is an operational or transactional table, there will be loads of information the user will never analyze (indexes, timestamps, calculated columns, binary values or blobs) and therefore you need to remove them from the end data set.

My personal suggestion after some time using QlikView? Rely on a staging environment as much as possible if your source is not purely analytical. But do not wait for the staging environment to be set and ready to start doing things with QlikView, since a single table box will provide you with enough power as to see (and avoid) potential data quality issues, and changing a data source string in QlikView is trivial.

Miguel

[Peter_Cammaert]

I usually work on projects that do the staging entirely in QlikView, especially when dealing with SAP. I don't think it's cumbersome at all, when compared to SAP

It depends on how wide you define the required skillset. To me norrowing the skillset to just QlikView seems like a big advantage for improving overall project efficiency. But I admit that this is from a viewpoint where QV is your primary data client.

Peter

[RedSky001]

I think every situaiton / project is different.

If the project is fast paced and tactical the business may not be willing to wait for a database to be modified.  It also depends if anyone else wants to use the data.  It can also make a huge different who is doing the coding in Qlikview.  I've walked into projects where the scripts took months to unpick. 

[giakoum]

Having a staging environment is really useful and takes away all that cumbersome coding from QlikView, letting it fulfill its primary objective : QlikView is a front end tool, not an ETL tool. But having a staging database like Oracle for example, requires an expert and can be a bottleneck. That is why we at my current assignment, are looking into tools like Lavastorm, which are agile and visual enough for everybody to use.