topic Re: Data Model in QlikView

Data Model

deepakqlikview_123 — Fri, 13 Mar 2015 09:54:37 GMT

Hello All,

Please find my attached data model, please suggest on this.

I have attached my test QVW file for this.

thanks

Re: Data Model

iliyansomlev — Fri, 13 Mar 2015 12:04:06 GMT

Your model looks great - you have a central fact table and many dimension tables attached to it - it is a star schema, and your keys are all Perfect keys on the dimension table side (meaning they have unique values and contain all values found in the fact table).

Cheers,

Iliyan

Re: Data Model

maleksafa — Fri, 13 Mar 2015 12:07:55 GMT

seems good, it is a star schema which is good, one thing that i would recommend is to use the document analyzer to identify unused fields; it will tell you what are the fields loaded in the schema but not used later on in the charts/expressions/variables etc...

Re: Data Model

Anonymous — Fri, 13 Mar 2015 12:15:14 GMT

As already said, it all looks fine.

I would apply the principle that if the data model looks ok visually and all dashboard interactions are sub 1 second [which they are], then no need to worry about improving it.

Re: Data Model

hic — Fri, 13 Mar 2015 16:31:09 GMT

At first glance everything looks OK, but when I look closer I can see a couple of problems.

The fields LOCATION_ID, SECTOR_ID, COUNTRY_ID and REGION_ID have different domains. Meaning if you click on a value in one of them, all values in the other three are excluded. In other words - there are no records in your fact table that have more than one of these keys. Surely, this is not what you want? The normal situation is that every record in the fact table has all or most of the keys present.
You have a field AS_OF_DATE. If this field really is an as-of-date, it should not be in the fact table. One single value in the fact table should link to several as-of-dates, so if you move your as-of-date into the fact table, you need to duplicate the single row and then QlikView will calculate the numbers incorrectly.

HIC

Re: Data Model

deepakqlikview_123 — Sun, 15 Mar 2015 05:25:36 GMT

Henric,

u men shall i create new table for date.

Thanks

Re: Data Model

petter — Sun, 15 Mar 2015 10:33:03 GMT

Hi Deepak - you are going in the right direction but you still have work to be done.

The datamodel is not a good candidate for a clean and well-formed dimensional model yet.

In my humble opinion at least the following points needs to be addressed to progress from what

you have achieved:

1) Remove all the fields in your fact table that are duplicates of fields in your dimension table.

2) Make sure why you want to concatenate four different fact tables into one. Look at your grain

and see if it is appropriate and be sure that you don't run into any problems with mixed granularity.

3) Create a date dimension as Henric Cronström suggests.

4) Read Henric Cronströms article about CANONICAL DATES

5) Clean up the other dimensions to see if there are functional dependencies between them

as that will indicate that they should be restructured/reorganised/joined/merged ... possibly having some

hierarchical relationship within one unified dimension like for instance COUNTRY -> REGION -> LOCATION

6) Do you really want to keep the LOAD_DATE_* columns in all the dimension tables and the LOAD_DATE in the

fact table? They can't possibly serve any purpose as they stand in your preliminary data model.

7) Make AS_OF_DATE into a real QlikView date by using the Date() function - tip: you don't need two fields for

that - one is sufficient - as Date() is a "dual" function which stores the date-number ANDkey-fields to each dimension and possibly only one fact field INDICATOR_VALUE unless you decide to enrich your fact table further.

😎 AS_OF_DATE1 is a mix of many country names and a lot of numerical dates - that can't possibly be a good

solution - at best it is convoluted and dangerous for many reasons.

9) Do a data profiling of your data sources - the source tables/files. Either in advance of bringing them into QlikView

or use QlikView to do the data profiling - it is an excellent tool for that.

At the end you should be able to end up with a clean and well designed dimensional model:

* one fact table with one measure/metric: INDICATOR_VALUE

the fact table contains also keys to each dimensional table

* multiple dimensions that are independent of each other

Again when you need to have multiple dates the article here on Qlik Community named CANONICAL DATES will help you a long way. A completely similar approach could be used to handle ALIAS dimensions / ROLE PLAYING dimensions as well.

Good luck Deepak.

Re: Data Model

petter — Wed, 29 Apr 2015 14:02:34 GMT

I recommend to read Stephen Redmonds book "Mastering QlikView" it is truly worth it's title and 100x it's price. It covers data modeling with QlikView brilliantly.

Packt Publishing has it also as an e-book...