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
Hi MFelix
Many thanks for your reply. The Sales Fact needs to be at the Period level. I have had a workaround to this by doing the following;
1.) Create a table (DimPeriod) of distinct list of all the Periods from the DimDate table
2) I have created a one-to- many relationship between DimPeriod(Period) to SalesFact(SalesPeriod)
3) In other to use time intelligence, I have also created a one-to-many relationship between DimPeriod (Period) and DimDate(Period)
I have attached a graphical representation of this design. Problem is it's a a snowflake schema rather than a star schema although it seems to be working as expected. Is there any way to design this into a Star schema ?