Data Modelling - Dates and Periods

[Maazi]

The image is a sample of Sales data in a transaction fact table (FactSales)  in my Power BI model. I have a standard DimDate table which also includes a column "FiscalPeriod" (format is YYYYMM) that repeats for all dates within a FiscalPeriod. 
I would like to

1). Create a relationship between the SalesFact and DimDate using the FactSales(SalesPeriod) and DimDate(FiscalPeriod ) columns. This however creates a many-to-many relationship and don't want to go down that route. 

2) Have a star schema rather than snowflake

3) Be able to do time intelligences calculations, like Period over Period, Previous Period etc. 

Any ideas on how to go about will be appreciated. 

[Anonymous · Accepted solution]

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

[Maazi]

Hi @Anonymous 
Thanks, that was helpful. 

[Maazi]

 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 ? 

[MFelix]

Hi @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.