Clear all

Virtual Fact and Dimension over computed satellites

Posts: 147
Topic starter
Estimable Member
Joined: 1 year ago

I have to developed a dimensional virtual IM layer over with calc. attributes are coming from the computed satellites. The computed satellites have complex joins with multiple satellites/hub/links and sometimes a where clause.


In IM we have 

Fact_Agreement_financial - Agreement_Key,Date_key,Measure1,Measure2,measure3...

Dim_Agreement - Agreement_key,Attr1,Attr2,....

In Vault we have below tables which daily refreshed.

Hub_Agreement- Agreement_hkey...

Csat_agreement_1 - Agreement_hkey(PK),LDTS(PK),Applied_dt, Measure1...

Csat_agreement_2 - Agreement_hkey(PK),LDTS(PK),Applied_dt, Measure2...

TPIT_Agreement - Agreement_hkey(PK),Snapshot_Dt(PK), Csat_1_hkey,Csat_1_LDTS, Csat_2_hkey,Csat_2_LDTS

typical query populating the CSATs is like

Select agreement_hkey,LDTS,Applied_Dt,RC, <SQL Expression> Measure1

From Sat_agreement_1 join .....

where (flag='N' OR Desc like '%hh').... /*SQL Expression*/

Now I have stitched all the csats using a TPIT so that i have a location of each satellite record for a snapshot_dt available in the facts.


CSATS are SCD Type 2 style so any any will result in a new record. Sometime times due the FILTER/JOINS in the CSAT populating query of due to DELETES IN SOURCE on the tables in RDV used for populating these CSATS some agreement may stop appearing in these computes satellites which for business means that agreement doesn't have the measure available for that day. The default behaviour of PIT is to take the last version of the CSAT record for the agreement available for each day's snapshot which in this case make the value appear wrong in the FACTS as the measure is not available for that day but TPIT says the last value applies. Example show below

Have I missed anything in the design? How do I deal with missing CSAT records?

12 Replies