Help: Datamodel with multiple Intervalls and Facts

[Fragstuff]

Hello together,

i would like to know if someone could please advise me in following problem:

For Example i prepared my Facttable like this:

ID	Start_ID	End_ID	Dim_1	Start_Dim1	End_Dim1	Dim_2	Start_Dim2	End_Dim2
1	2021-01-01	2021-12-31	A	2021-01-01	2021-06-31	A	2021-01-01	2021-02-28
1	2021-01-01	2021-12-31	A	2021-01-01	2021-06-31	B	2021-03-01	2021-06-31
1	2021-01-01	2021-12-31	B	2021-07-01	2021-12-31	C	2021-07-01	2021-10-31
1	2021-01-01	2021-12-31	B	2021-07-01	2021-12-31	D	2021-11-01	2021-12-31

What would be the best method so that i can do following analysis:

• The Status of the ID on a specific date (which Dim_1 | Dim_2 was active)
• Showing every Dim on one Date_X_Axis
• Assoziative filtering on Dim and ID should still be possible

Normally the Timeline has a range of 5 years and we deal with round about X Million Rows

1. I created a Date_Link_Key (Start_ID & End_ID)  from one possible Date_Interval (1 out of eight )
   
   1. "num(Start_Date) & num(Ende_Date) as Date_Link,"
2. Resident Load this Link in separate Table and extracted the Start & End Date again to intervallmatch my Mastercalender to create a DateBridge

   1. DateLink1:
      Load Distinct
      Date_Link,
      date(left(Date_Link, 5),'DD.MM.YYYY') as Date_Link_Start,
      date(right(Date_Link,5),'DD.MM.YYYY') as Date_Link_Ende

      Resident Faktentabelle;

   2. BridgeTable:
      Intervalmatch(Datum) //Datum from my Mastercalender
      Load Distinct
      Date_Link_Start,
      Date_Link_Ende
      Resident DateLink1
      ;

The Actual Data Model with a Date_Bridge on ONE Start|End _DIM (1 out of eight )

But how should i connect the Other (7) Start|End_Dim Intervalls without Circular Reference 😞

I already read about

• As of Calender
• Canonical Date
• Reference Date
• Mastercalender
• Intervalmatch()

but can't get a perfect DataModel out of this 😞

Every help would be appreciated 🙂 🙂 🙂

Greetings

Fragstuff