Model Design and Dates

[Dev-13]

Hi I have a data model that has a date table and fact tables. I am having issues with the model design. The date table needs to relate to the fact table by year and month, only this is not possible due to only allowing one relationship. Using one relationship will not work. Does anyone have advice on how to link the tables together? The fact table has year and month number and I wanted to link this into the date table so I can get the data by financial year.  Thanks

[jdbuchanan71]

I would add a table to your model that you could use as a lookup.  It would contain the YMW values and the date you want associted to it.  You would link it to your fact table that has the YMW and pull over the date field then link the date field to the Dates table.

YMW	Date
2022M01W01	1/1/2022
2022M01W02	1/2/2022
2022M01W03	1/9/2022
2022M01W04	1/16/2022
2022M01W05	1/23/2022
2022M01W06	1/30/2022
2022M02W06	2/1/2022
2022M02W07	2/6/2022
2022M02W08	2/13/2022
2022M02W09	2/20/2022
2022M02W10	2/27/2022
2022M03W10	3/1/2022
2022M03W11	3/6/2022
2022M03W12	3/13/2022
2022M03W13	3/20/2022
2022M03W14	3/27/2022
2022M04W14	4/1/2022
2022M04W15	4/3/2022
2022M04W16	4/10/2022
2022M04W17	4/17/2022
2022M04W18	4/24/2022
2022M05W19	5/1/2022
2022M05W20	5/8/2022
2022M05W21	5/15/2022
2022M05W22	5/22/2022
2022M05W23	5/29/2022
2022M06W23	6/1/2022
2022M06W24	6/5/2022
2022M06W25	6/12/2022
2022M06W26	6/19/2022
2022M06W27	6/26/2022
2022M07W27	7/1/2022
2022M07W28	7/3/2022
2022M07W29	7/10/2022
2022M07W30	7/17/2022
2022M07W31	7/24/2022
2022M07W32	7/31/2022
2022M08W32	8/1/2022
2022M08W33	8/7/2022
2022M08W34	8/14/2022
2022M08W35	8/21/2022
2022M08W36	8/28/2022
2022M09W36	9/1/2022
2022M09W37	9/4/2022
2022M09W38	9/11/2022
2022M09W39	9/18/2022
2022M09W40	9/25/2022
2022M10W40	10/1/2022
2022M10W41	10/2/2022
2022M10W42	10/9/2022
2022M10W43	10/16/2022
2022M10W44	10/23/2022
2022M10W45	10/30/2022
2022M11W45	11/1/2022
2022M11W46	11/6/2022
2022M11W47	11/13/2022
2022M11W48	11/20/2022
2022M11W49	11/27/2022
2022M12W49	12/1/2022
2022M12W50	12/4/2022
2022M12W51	12/11/2022
2022M12W52	12/18/2022
2022M12W53	12/25/2022

[CoreyP]

I agree with @jdbuchanan71 . However, I think you could also just do this with one date table. You could create a calculated column in your date dimension to reproduce the format of your period key in your fact table by using combinations of functions like YEAR, MONTH, WEEKNUM, and FORMAT. Then join the two on that key.

[jdbuchanan71]

@CoreyP 

If you did that, add the YMW column to your date table, your join to your fact table would be many to many unless I am not understanding your post.

[CoreyP]

Oh jeez. You are 100% correct. I completely overlooked that. Good call! Thank you 🙂

[jdbuchanan71]

So do you have a W52 or is it just the weeks in the month (W01 - W05)?

[Dev-13]

Yes WK1-W52 and M1-M12

[jdbuchanan71]

You would add a date field to your fact table that is the first day of the month for the year / month.  For example, if your year month is 2023 October, add a date field to the fact table that is 10/1/2023.  Then you can link that to your date table.

[Dev-13]

Thank you - yes this would work.

I do have one issue that would cause a problem with this though.

The data is set up in date periods in the same column so it contains both week and month data. The date period key is 201801W01 or 201901M01. W01 is week 1 of the year and M01 is first month of the year. I can do the calculation easily with the month, but I don't know how I would achieve this with the week number. I have split the month data and week data into different tables in the model so the month ones are working fine now and linking to the date table. I don't know how to achieve the same for the week column