Hi Tech Gurus,
I have a star model where I have DIM_CLAIM as SCD2 (new record add whenever a claim attribute change) and it is linked with FACT_PAYMENTS (Snapshot Fact).
Now, a claim can have multiple payments,a claim can have multiple snapshots and a payment can have multiple snapshots.
I have connected DIM_CLAIM to FACT_PAYMENTS on natural key of Claim Number because users want to see claim attributes and payment measures at same point in time. They do not want to see claim information as current/as at time when payment record was created.
Now, FACT_PAYMENTS is connected to DIM_CALENDAR where DIM_CALENDAR.DATE is between fact_payments.fr_date and fact_payments.to_date.
When user selects a date from snapshot calendar, it perfectly picks up the right SS of payment data but because DIM_CLAIM has no connection with snapshot calendar, I get multiple SS rows of same claim.
User doent want to use two separate Date filters.
I tried creating a filter as :as_at_date: =calendar_date and :as_at_date: between dim_claim.fr_date and dim_claim_to_date.
However the issue is that even if user doesnt use anything from claim table, this filters forces the join as dim claim is part of filter.
What is the best way to allow users to select a date that filters fact payments and dim claim's version valid on that date(only if user has used data from dim_claim)?
Example business questions - Provide list of finalised property claims and their payments as at 30/1/2019.
Attached is context diagram.
use Case in next comment