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
Then you need to get creative. You can create a new key by concatenating multiple fields and then the tables can associate with this key if you need multiple fields. If you don't need multiple fields then make sure that the other fields do not have the same name by changing one of them.