Business Case -
I am currently modelling a Invoice Fact table in my information mart built over a Vault. The business use can is to report all the invoices for an agreement and also invoices per business unit. When i went through the data I found cases where there are invoices which linked to an agreement and a business unit BUT there are also cases where an invoice is linked only to a business unit and doesn't have an agreement linkage.
I have done my agreement_dim and business_unit_dim via PIT and now I am creating a Invoice_fact having keys from agreement and business Unit dimensions. For Fact I built a snapshot style bridge table with data attributes coming from LINK_AGREEMENT_BUSINESS_UNIT_INV and measures from Sat_Agreement_Invoice.
Now in the BRIDGE table i initially did a inner join between LINK_AGREEMENT_BUSINES_UNIT_INV and Sat_Agreement_Invoice but this filtered out all the Invoices not linked to an agreement. So i did a LEFT join for Sat_Agreement_Invoice and made Agreement_hkey as MD5_BINARY(nvl(Agreement_hkey,'?')) [ where '?' points to my ghost record]. But problem comes in the PIT_AGREEMENT which doesn't have a GHOST record for every snapshot_date, how do i join the fact with dimension in such a scenario?
Have I missed something in the design? Please suggest.