Design One to Many Relationship from Fact

Hi All,
There is Agreement Transaction Fact that captures business events such as renewal, cancellation, reinstatement etc., of insurance agreements. Dimension tables surrounding this are Agreement, Member, Txn Date, coverage etc., and measures such as Premium Amts are widely used for reporting. Now there comes a new business need to capture all activities for an Agreement Transaction and 1 transaction could have multiple activities. This is essentially a 1:M relationship from data perspective. From dimensional modeling stand point, it isn't a recommended approach to create a child table (Activity Fact) directly relating to another fact but instead go via a dimension table. Since I already don't have an Agreement Transaction Dimension in my existing design, is there a better way to approach this instead of introducing a new dimension just to avoid Fact to Fact join? Creating a Dimension at this juncture seems redundant to me.

It depends.

If there are measures at the activity level then it should be another fact table at the higher grain. The new fact should include the dimensions of the original fact. If the activity is simply additional context, then a bridge between the fact and activity dimension is appropriate.

There are two approaches to implementing a bridge: you can group unique combinations of activities and store the group key in the fact and bridge, with the other key referencing the dimension, or you can have a unique key for the fact and use that key in the bridge instead of a group key. The former method results in a much smaller bridge if similar activities are common as there would be fewer groups than transactions, but is a bit more complex to implement.

ngalemmo wrote:It depends.

If there are measures at the activity level then it should be another fact table at the higher grain.  The new fact should include the dimensions of the original fact.  If the activity is simply additional context, then a bridge between the fact and activity dimension is appropriate.

There are two approaches to implementing a bridge: you can group unique combinations of activities and store the group key in the fact and bridge, with the other key referencing the dimension, or you can have a unique key for the fact and use that key in the bridge instead of a group key.  The former method results in a much smaller bridge if similar activities are common as there would be fewer groups than transactions, but is a bit more complex to implement.

Hello ngalemmo,
A new fact could be a viable option for me here as I got Coverage Location, User, Activity Date etc., to be captured as a part of Activity. Kind of Factless Fact at this moment. In your approach for new fact, you said all dimensions of original fact (ie., txn fact) should be included. I got a degenerate dim (transaction id) in Transaction Fact table, if I bring all dims into the new fact then isn't I create parent/child relationships between these two fact (txn and activity) tables? Is it okay? I'm worried BI layer will start joining these two facts as there will be common fields between them. For instance, if txn id, txn amt (from txn fact) and all activities (from new activity fact) need to be present in a single report then the join between these tables will become obvious and it could rollup duplicate txn amt for the same txn but with multiple activities. If I had a transaction dim in between then BI will create two pass sqls and join them by the common Txn id at BI level to show the correct result. Any thoughts on this?

No. Creating a parent/child relationship between fact tables violates basic dimensional design principles. A dimensional model is not an entity-relationship model. A star schema has a strict form which should be followed.

Besides, storage is cheap, time is not. Forcing a join between facts only adds complexity and slows down queries.

As far as the BI layer is concerned, most have metadata where you define what may be joined to what. A good one is also aware of the dimensional form and would properly combine facts when such operations are needed.