Hello,
I'm totally beginner and after some hours of try...I'm giving up (the answser to your).
I do have 5 Dimensions TABLE (all hierarchies) with the following structure:
- DIVISION = ParentDivisionID; DivisionID; DivisionNAME
- PRODUCT= ParentProductID; ProductID; ProductNAME
- GEOGRAPHY= ParentGeographyID; GeographyID; GeographyNAME
- ORGANIZATION= ParentOrganizationID; OrganizationID; OrganizationNAME
- ACTIVITY= ParentActivityID; ActivityID; ActivityNAME
The FACT Table is containing the ID of all the Dimensions (DivisionID, ProductID, GeographyID, OrganizationID, ActivityID) plus a DATE and the AMOUNT.
Furthermore I do have other Tables (let's call them MAP) who are also based o nthe same set of IDs:
- HotSubjects: = DivisionID, ProductID, GeographyID, OrganizationID, ActivityID plsu SubjectName
- 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?
