Dimensional Modeling event Hierarchy

Question

In my current world, An employer Can Grant Stocks to an Employee under a stock plan. Not only stocks, other types like restricted stock units etc.. can be granted too. Each Grant record has its own attributes (Qty granted, fair market value when granted, etc..). Every Grant record has multiple vesting information (eg: out of 100 shares granted, 50 can vest in 2021, 30 in 2022, 20 in 2023) . Finally each vesting record can have multiple "planned distribution" records (ie out of 50 vested ones in 2021, 20 can get exercised in Dec 2021 , 20 in Jan 2022 and remaining in Feb 2022). So the hierarchy looks like this: Employee -> n Grants -> n Vesting -> n Planned Distribution

Wondering what is the prescribed way of Dimensionally modeling this?

Option#1: Treat Grant, Vesting, Planned Distribution as separate Dimension and have a separate factless fact that relates all of these and the employee (However question here is, can they be treated independent dimensions, as child cannot exist/meaningless without the parent)

Option#2: Only have Planned Distribution Fact and collapse the Grant and Vesting into this fact (like kimball order-orderline concept). Thus employee and employer will be the only Dimensions (Drawback- What if Grants and Vesting are required on its own in other facts?)

Option#3: Treat Grant, Vesting, Planned Distribution as separate Dimension, relate them by using natural keys from parent to the child, but also have separate factless fact to relate the Dim Keys of each of this for point in time analysis

Database: snowflake cloud

Thanks in advance Sunil

Answer 1

You are approaching this from the wrong direction. A dimensional model is based on the business questions you want to be able to answer, not on the data and its structures that you happen to have in your source system.

So you need to define the measures that you want to report on and their grain (which will give you your facts) and the entities that you want to use to filter and aggregate your fact (which will give you your dimensions).

Once you have this information it will become easier (though not necessarily easy!) to design your model and the answers to your questions will either become much more obvious or, possibly, irrelevant.