topic Re: Help with Data Modeling/Loading QVD Files in QlikView

Help with Data Modeling/Loading QVD Files

Thu, 26 Mar 2015 00:54:34 GMT

I'm very new to Qlikview and nobody in my organization really seems to understand how it works. I'm analyzing how to make our QVDs and data model better but not sure where to start. Allow me to give an overview of how we are doing this now -

We have a series of views in our SQL Server database which each contribute to a metric based on a source. For example, we have a view for getting data from Great Plains/Dynamics, we have a view which pulls data from our lead-creation software, etc. Each view is then unioned together into a single "master" view called METRICS. We then pull the data from each individual view into their own QVD_CREATOR.qvd files. Once these QVDs are populated, we then call the master view and pull the data into a master QVD file.

You can imagine the overhead with this approach - every time we add a new field to a view, we then have to copy/paste it to all of the other views because otherwise the UNION statement in the master view will break. Further, each field then has to be copy/pasted into each of the corresponding QVD files. It's a tangled mess at best and not fun to work with.

My questions:

How could this be handled better?

Can't we just pull the data from each fact table and dimension table from our data warehouse into their own QVDs and be done with this mess? Why use views for this - wouldn't a SQL query or stored procedures be just as good?

Further, we can't we just query the database tables directly instead of bringing them into QVDs? This seems redundant and ridiculous - we already have the data stored in a database, that's what it's for, so why should we be storing it in files just for our reports? Seems like too much overhead.

However, if we do need QVDs, then why can't I just load them by querying the database directly instead of loading from views, then master QVD files?

Ugh, what a mess. It's gotten out of control and hard to maintain.

Re: Help with Data Modeling/Loading QVD Files

JonnyPoole — Thu, 26 Mar 2015 01:04:32 GMT

YES - you will have to analyze what the views do, but you can replace a view with a SQL query or stored proc and copy/paste the SQL into the QLik script. If no other non-qlik application uses those views on the database,you could possibly eliminate them altogether and replace with equivalent qlik script.

YES - although the 2nd time you create a qlikview application you may find yourself reusing the qlik ETL logic or same previously run SQL query .... then it makes sense to reuse the same code and manage things in one place for all use cases

However, if we do need QVDs, then why can't I just load them by querying the database directly instead of loading from views, then master QVD files?

See above

Re: Help with Data Modeling/Loading QVD Files

Thu, 26 Mar 2015 18:15:59 GMT

I found some documents regarding data modeling in Qlikview. I think one thing the business here was trying to do is to solve proper schema formatting in view via the SQL layer. The problem is that this creates overhead when attempting to union all of the fact tables together.

So the question I have is this - is a CONCATENATE statement in Qlikview essentially the same thing as a UNION in SQL? The reason I ask is because I think I can duplicate what's being done by doing a UNION on all of the SQL views by moving this inside the Qlikview layer and CONCATENATE the facts together. This way I don't have to worry about having all fields from each fact table being present, they will just be concatenated together whether or not a fact table has the same field as another fact table.

For example:

If I have a fact table Call_Metrics and it has lead_id, phone_id, customer_id, calls_received and then I have another fact table called Sales_Metrics containing lead_id, property_id, customer_id, sale_amount, then I can concatenate the two facts together into one Qlikview table during the load. When doing this is SQL views, I have to have all fields present when I UNION each fact table together which causes overhead.

Hopefully this makes sense...i'm still learning. But on the surface, am I on the right path here?

Re: Help with Data Modeling/Loading QVD Files

JonnyPoole — Thu, 26 Mar 2015 18:34:29 GMT

They are the same. But if you can do it in one SQL statement because all the data is from one database then do that. Otherwise you need run 2 queries and concatenate locally which is likely a bit slower.

Use concatenate when the 2 pieces of data aren't in the same database because in that situation you can't use one SQL union statement against multiple databases / platforms / files.