Do we need keys in a QlikView Application? Would it be a good design if I can translate keys to the dimension values and then let QlikView create associations based on the Dimension Names. For Example, let us consider a DimensionTable named DIM_PRODUCT and a Fact Table named FACT_SALES. The Structure of both these tables are as follows:
DIM_PRODUCT:
PRODUCT_NAME
PRODUCT_KEY
FACT_SALES:
PRODUCT_KEY
...
...
(OTHER FACT FIELDS)
When bringing in this data to QlikView - do we need this Product Key? I was thinking if I can convert the Fact table with a join to Product Table and get it inside QlikView in the following format.
FACT_SALES:
PRODUCT_NAME (replacing Product Key - by creating a join in the data source - DIM_PRODUCT.PRODUCT_KEY = FACT_SALES.PRODUCT_KEY)
...
...
(OTHER FACT FIELDS)
I was attempting the above model as I felt - any day these keys are just going to be add up the size of my QlikView Document and if QlikView can take care of the associations, why would I need keys? Also, in my current application - I have my dimension data coming from multiple tables - issue is same product has got two different keys - one each in the dimension table.
I am trying to understand the implications in the above model. If this is an accepted model, would it be better to make the join inside QlikView or at the DB Level (fyi - my fact tables are pretty huge). Appreciate your support.
Thanks Felim for the quick response. I understand this - but can you list the implications in the earlier method (do we have a performance issue or something like this)
generating concatenated string fields uses more memory than a numeric value, and since you have the data structure readily in place there is no need to waste the memory, its like doing an extra calculation for the sake of having a calculation
Thanks Felim for the response and sorry for the delayed response.
While I appreciate your views, I am not having concatenated string fields anywhere in my model. I am just replacing the keys with the dimension names. This is something similar to the method mentioned in your earlier response, except that I am doing this at SQL level and not at QlikView level. I am interested to understand the pros and cons of both the methods. Appreciate your support.
First question I always ask prior to limit anything with SQL is what does the whole data look like.. is there anything being hidden by the SQL. Then Is there any other use for these items, should I extract and store all of both tables for future use?
Reg your first question - I am not sure I understood it when you say hidden? Can you please be little more specific here.
In your second question - I guess you are asking about the usage of QVD (your second question) - No - we get the data into QVW and then we preserve it for sometime (like 2 to 4 weeks) and then we clear them all.
There is no need to keep the key. I would map the keys to the names and bring the names into the main fact table. Do not worry about the duplicate names, as QV is very efficient in storing duplicates.
This the code I would use:
Map_Products:
Mapping LOAD DISTINCT
PRODUCT_KEY,
PRODUCT_NAME
Resident DIM_PRODUCT;
FACT_SALES:
LOAD ...
ApplyMap('Map_Products', PRODUCT_KEY) As PRODUCT_NAME,
...
The mapping table is automatically dropped when the load script completes.
Hope that helps
Jonathan
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
But Sorry, I am not sure if you understand my question. I am completely ok with ApplyMap Statement (If you look at my question - I have multiple attributes in my product table. Hence, using ApplyMap might not be the best option here).
What I need to understand is - Which is the Optimal Solution among these two.
1. Creating a FACT Table where all my dimension keys are translated to actual Dimension Data (in the above Ex. replacing Product key with the actual name of the product and the associated attributes) using SQL and bring this into QlikView. Essentially, we are joining the tables at SQL Level here.
2. Bring the FACT Tables and DIMENSION Tables Separately inside QlikView and doing the join inside QlikView (I understand we can drop the keys once the join is made, as they are going to eat up unnecesary space). Essentially, we are joining the tables at QlikView Level.
I am trying to understand which will suit best and if have a mechanism to identify / say that one of this option is best in a particularl scenario.
If you keep the Product dimension as a separate table, then the issue is a trade-off of size vs performance. The keys are not needed for the model to work, you can do the association on the name, and save a small amount of space on the key.
However, the joins performed while calculating charts on the front end will perform better using numeric keys (assuming a simple integer key). Often, this performance gain is more important than the (small) increase in the size of the model.
Hope that helps
Jonathan
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
