Forum Discussion
Dimdate table isn't working
You have a few options to fix it:
1. Use a truly unique key for the relationship
Instead of MonthNumber, use a column like:
- FullDate (YYYY-MM-DD)
- DateKey (e.g., 20250101)
- YearMonth (e.g., 202501)
These are unique at the date or month level and are suitable for the one-side of a relationship.
2. Create a unique Year-Month key
If your fact table is at a monthly granularity, create a new column in DimDate:
YearMonth = dimdate[Year] * 100 + dimdate[MonthNumber]
This will ensure values like 202501, 202502, etc., which are unique.
Hey IffyN ,
Dimension tables are usually on the 1 side of relationship and fact tables are on the many side connected by the relationship. To take date table for example (at day level granularity), you will have one record for each date (thus 1 side) and in the fact table you connect it to can have 0 or 1 or many entries on the same date (thus many). Right now the connection as per the error seems to be on the MonthNumber field. If you had created a date level granularity date table, then for one month you can have 30-31 repititions of the same monthnumber(assuming 1 year), and this is not allowed in the one-side(dimension) of a relationship.
Please try to delete the relationship with monthnumber and update it to use the date key or date column if applicable (lowest granularity) or model the date table such that the field that use in the date table for relationship building is non-repeating.
Hope it helps!