Innovation: = DivisionID, ProductID, GeographyID, OrganizationID, ActivityID plus InnovationName
For this reason I thought it would be good to:
create a FACT table with a %MasterKey plus Date and Amount
a MasterLink table containing all the concatenations of DivisionID, ProductID, GeographyID, OrganizationID, ActivityID pls uthe ID itselfs
a link between the Dimensions and the MasterLink via the ID (no need to add the <ANY> as there is a TOP Hierarchy in any of them; e.g. TOT_PRODUCTS, TOT_Geographies,...)
a link between the MAP tables and the MasterLink via the %MasterKey
The issue is that I'm facing the 2 following problems:
I cannot achieve to build the MasterLink via the JOIN function and than concatenate them -> I achieved to do it via a distinct load from the FACT table, but than I realized that I was missing some unused keys...that ware needed for the sélections and the hierarchy
I'm not sure that at the end this woul work out....especially with the hierarchies.....
let's say that within the Geographies I'm selecting "Europe" (child of "World" and parent of "Germany", "France",...)...will the selection of all the KEY with the European country work?
Re: How to create concatenated KEY in case of hierarchies?
I think you can use the Hierarchy and/or HierarchyBelongsTo function to create hierarchy tables for your five dimension tables. Those tables can be linked directly to your MasterLink table.
You can use the Qlikview Components library to quickly create link tables. I'd try using this library to create your MasterLink table. See the LinkTable.qvw document in the Examples folder after you've extracted the files from the qvc-7.zip download.