Forum Discussion
Linking dates - Different tables / data sources
sanderson82 how I know whcih table is which but few ideas.
- make calendar/date dimension in your model and link it with your tables. There are many posts on how to create one.
- make employee dimension table (unique employee list) and link with your tables.
in all visuals, use date and employee from above dimension and data from your transaction tables and everything will work thru.
Date and employee dimension will be one to many relationship, one on dimension side and many on transaction side.
sanderson82 can you share relationship diagram, it is not clear how these tables are related and what kind of relationship is this? One to many, many to many etc?
sanderson82 how I know whcih table is which but few ideas.
- make calendar/date dimension in your model and link it with your tables. There are many posts on how to create one.
- make employee dimension table (unique employee list) and link with your tables.
in all visuals, use date and employee from above dimension and data from your transaction tables and everything will work thru.
Date and employee dimension will be one to many relationship, one on dimension side and many on transaction side.
Thanks parry2k
I did create a date dimension table however this still did not work for me. I ensured the date column was set to date / time for all. I am guessing I perhaps had the wrong relationships, I will attempt again based on your advice
sanderson82 you need to change data type in your transaction table to date not date time. I believe in date dimesion you date column data type is date, even if it is date/time, time will always be midnight and your relationship will not work, so better to change everything to date type.