6 Replies Latest reply: Jul 6, 2017 5:35 AM by Bala Bhaskar RSS

    No link between tables

    Thomas Ødegaard

      Hi all,

       

      I have a dataset with 3 tables: Calendar, Organisation (as a parent child hiearachy), and a fact table.

       

      "DW_SK_Date" (format=YYYYMMDD) is a primary key between calendar and fact table.

      "DW_SK_Organisation" is a primary key between the fact table and organisation hierarchy.

      The fact table only consists of the most aggregated members of the organisation hierarchy.

      All members in the organisation hierarchy have two fields "DW_ValidFrom_Organisation" and "DW_ValidTo_Organisation" (format=YYYYMMDD) indicating in what period the member is valid.


      My issue is, that my calendar only connect to the lowest aggregated member in the Organisation hierarchy as it connects to the fact table. So when I select a period (a month for instance) in the calendar, it does not responds to changes that has been made for members that are less aggregated in the organisation hierarchy.

       

      How can I make a connection between the calendar table and the Organisation hierarchy dimension?

       

      In the attached example it should catch all members that I have marked with yellow, when I choose YearMonth = 201703 as these members are valid in that March 2017

       

      My script is:

       

      Calendar:

      LOAD "DW_EK_Date" as "DW_SK_Date",

          "Year",

          "Year_StartDate",

          "Year_EndDate",

          Quarter,

          "Quarter_Key",

          "Month_Key" as "YearMonthNum",

          "Month_Char" as Month,

          "Month_Name_EN",

          "Month_StartDate",

          "Month_EndDate",

          Left("DW_EK_Date",6) as YearMonth

      FROM "My excel file"

      (ooxml, embedded labels, table is Calendar);

       

      Organisation:

      Hierarchy(DW_EK_Organisation, DW_EK_OrganisationParent,LevelName)

      LOAD

          DW_SK_Organisation,

          DW_EK_Organisation,

          LoadDate_Organisation,

          LevelID,

          LevelName,

          LevelType,

          DW_EK_OrganisationParent,

          OrganisationLevel,

          Date(DW_ValidFrom_Organisation,'YYYYMMDD') as DW_ValidFrom_Organisation,

          Date(DW_ValidTo_Organisation,'YYYYMMDD') as DW_ValidTo_Organisation,

          NewestOrganisation,

          MaxLoadDate_Organisation,

          DeletedOrganisation

      FROM "My excel file"

      (ooxml, embedded labels, table is Org);

       

      Fact:

      LOAD

          DW_SK_Organisation,

          DW_SK_Account,

          DW_SK_IncomeStatementsFTE,

          DW_SK_Date,

          LoadDate_IncomeStatements,

          TransactionDate,

          "Type",

          Amount,

          AdjustmentComment,

          MaxLoadDate_IncomeStatements

      FROM "My excel file"

      (ooxml, embedded labels, table is Fct);

       

      Please share your thoughts

       

      - Thomas