Help: Datamodel with multiple Intervalls and Facts

Fragstuff — Thu, 30 Sep 2021 08:33:34 GMT

Hello together,

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

For Example i prepared my Facttable like this:

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

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

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,"
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

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

Every help would be appreciated 🙂 🙂 🙂

Greetings

Fragstuff

topic Help: Datamodel with multiple Intervalls and Facts in Connectivity & Data Prep