Forum Discussion
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 HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Super UserHi Maazi ,
For this to work has a star schema since you have the Sales granularity at Fiscal Period level your calendar table should have the same granularity, and not a dimension table.
Another option can be to introduce a date in the sales like the end or the start of the fiscal period so you can connect the date with the fact table by date allowing you to do the calculations for time intelligence.