Data Modelling - Dates and Periods

Hi Maazi ,
Based on your description, instead of creating a relationship directly between FactSales(SalesPeriod) and DimDate(FiscalPeriod), first create a bridged table containing unique FiscalPeriods. This bridged table will act as an intermediary between FactSales and DimDate, thus avoiding many-to-many relationships. Then, make sure that your FactSales table is at the center of the schema and that the DimDate and other dimension tables are directly associated with it. The above bridging table will help maintain this star schema structure. Finally, create a one-to-many relationship between DimDate[FiscalPeriod] and BridgeFiscalPeriod[FiscalPeriod].
Create a one-to-many relationship between FactSales[SalesPeriod] and BridgeFiscalPeriod[FiscalPeriod].

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly