topic Re: Data Modelling in QlikView

Data Modelling

surajap123 — Wed, 05 Feb 2014 15:49:42 GMT

Hi Experts,

I have to pull data from OLTP Database and need to build a Qlikview app.

1. I believe that, I can pull the tables into Qlikview and keep almost same normalized data model. (doing some changes like resolving association issues like removing synthetic keys, doing some data cleansing, modifying tables as per UI requirement etc..).

I hope this is the way we build data model in Qlikview?

2. Suppose, If I want to create a proper dimensional model (ie having all measures and dimension keys in a fact table and Dimension tables are linked to fact table with the dimension keys). How can I achieve this using Qlikview Scripting?

I mean, once after loading the Normalized OLTP tables into Qlikview, I want to group all the measures into single table(fact table) and all dimensions into various tables(dimension tables).

To achieve this, what I have to do in Qlikview Script??

Regards,

Suraj

Re: Data Modelling

agilos_mla — Wed, 05 Feb 2014 16:01:22 GMT

Just have in mind:

A QlikView data model is the representation of the data you have loaded and the underlying data sources will have to be manipulated within the script to deliver the data model that best suits your data for both usability and performance.

QlikView data model is persistent and reacts as a whole to user “queries”, a selection in the user interface affects the entire schema.

Some Guidelines:

There Is No One Best Architecture.
Don’t kill yourself with technical linktables if you don’t have to !
Don’t put everything in a single model. Monolithic model introduces unnecessary complexity. Use document chaining.
Architecture Is Entirely Dependent on Requirements (Systems, Skill Sets, Security, Functionality, Flexibility, Time, and above all… Business Requirements!

Hope it helps,

Michael

Re: Data Modelling

Josh_Good — Wed, 05 Feb 2014 16:46:50 GMT

Yes you can keep the tables in the current form. The most important thing is you must join all the tables together in some sort of logical schema without any circular references. You may want to check out this posting Creating a Data Model

Re: Data Modelling

surajap123 — Sat, 08 Feb 2014 11:33:37 GMT

Thanks for all the valuable information..

Re: Data Modelling

ngulliver — Sat, 08 Feb 2014 15:01:14 GMT

Hi, Suraj.

I thought it was worth pointing you in the direction of Steve Dark's blog. He has a few thoughts on data model design.

http://www.quickintelligence.co.uk/perfect-your-qlikview-data-model/

Regards,

Neil

Re: Data Modelling

surajap123 — Mon, 24 Mar 2014 12:23:00 GMT

Hi Josh,

As per your suggestion, I am pulling the necessary tables from transaction database and keeping the same links as in source db while buiding data model in Qlikview. I am achiving this by using Qualify*; and Unqualifty <keyfield>; above each table in qlikview script, so that I can avoid synthetic keys and circular references. I am doing this to keep the same connections as in source db. Anyway later I will be changing the model(using calcuated fields, lookups, left joins, concatenation etc) based on the requirement.

Is my approach correct?

Does the data model I create using above method, bring the same result in qlikview, when compared with source database?

Thanks !!!

Re: Data Modelling

Josh_Good — Mon, 24 Mar 2014 14:25:13 GMT

Yes, it sounds like you are on the right track. Using Qulaify is a great way to get started and to better understand what the data model needs to look like. I would be careful about using Qualify beyond for just quickly figuring out your data model. If you build a UI on a data model that uses qualify then it will not be very business friendly. For example business users will not want customers to be call Customer.Name, it would be much better to alias it to [Customer Name] in the data model so you don't need to keep renaming it in the UI.

If you have a data dictionary already establish then this video might be helpful. Aliasing Fields using Data Definitions

Re: Data Modelling

surajap123 — Mon, 24 Mar 2014 15:48:16 GMT

Thanks a lot !!