Design scenario...

I am confused as to what's best design option for the following scenario.

Insurance business. I have a monthly fact table say for Premium trnasactions (Grain is at the rider level) which is connected to Policy, Product, Campaign etc. We have some fields along with Policy number to identify the riders attached to policies. Product dim has only base products and Policy dim does not have any riders. If we create rider dimension then we need to attach policy number and effective and elapsed dates with status otherwise it's just description of different riders. This makes it like a snowflake or outrigger table of Policy Dim. We can add the dates to fact and status to junk dimension as another option but if the rider lapses there won't be a premium paid on it and so we won't show it correct in the fact table. Other option is to create a factless fact for riders and then join to premium transaction fact through policy dim to know about rider status. Third option is to come up with a bridge table between policy and rider. Last option is to add Riders to Policy dimension.

What would be the best option ?

The fact table provides the relationship between policy and rider. Are you saying that the only thing you have about a rider is a text field?

What I don't understand is you are receiving transactional data, presumably with values at the rider level, but the system that allocates the premium to the rider does so without any clear identification of the rider itself? There must be something from the operational system that identifies the rider. Find it an use it as the natural key for the dimension. If the only thing you have right now is a text field, fine, at least you have established the dimension so you can receive additional information about the riders in the future.

Rider Number exists to identify a unique rider but it only make sense when it's seen with Policy otherwise it's just generic identification of a Rider.

For example...

Policy Number : P100
Rider Code : R100
Rider Sequence : 01

If I have to use this as a Natural key then I am using Policy Number again which is what I was tryingt o convey in my first message. Did I answer your question ?

Thanks for your time !

A household of husband, wife and adult kids can have multiple riders and they all can belong to one policy. If you do not have demographic information on the rider then you need a bridge table to represent this one to many relationship between a policy and the riders. Creating a bridge table is a good practice and the best option.

If you do not want to create a bridge table, then the other option you have let policy information repeat for every rider in the policy dim. And then you select the primary rider for a unique policy and tie it to the fact table.

Regardless to what option you select from above, If you are tracking when a rider pays his/her premium then you can have premium_Due_date , premium_expected_amount, premium_received_date, premium_received_amount in the fact table. If current date passes a premium_Due_date value and premium_received_amount is null then you know it has not been paid.

Is it OK If I create a Rider dimension as kind of Outrigger dimension of Policy Dim ? I will have Policy Dim key in Rider Dimension with other attributes which uniquely identifies the rider. I know this table will be bigger than Policy Dim to some extent but it's better than repeating all the Policy attributes for Rider as people always want to look at the riders pertaining to Policy. I am also thinking of adding Rider Dim Key to the fact table.

Thoughts ?

A rider dimension would be the correct approach along with a rider key in the fact.

Another thing to consider is since the rider has no real identification it may make sense to implement this as one or more junk dimensions. That is, one row for each unique combination of attributes. This can be advantageous if these attributes are often in common across policies.