Data Model

[deepakqlikview_123]

Hello All,

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

I have attached my test QVW file for this.

thanks

[iliyansomlev]

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

[maleksafa]

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...

[Anonymous]

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.

[hic]

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

1. 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.
2. 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

[deepakqlikview_123]

Henric,

u men shall i create new table for date.

Thanks

[petter]

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.

[petter]

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...