2 Replies Latest reply: Aug 27, 2013 2:45 AM by Francesco Ravagnolo RSS

    How to create concatenated KEY in case of hierarchies?


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