Discussion Board for collaboration related to QlikView App Development.
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).
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...
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.
At first glance everything looks OK, but when I look closer I can see a couple of problems.
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.
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...