I have two fact tables one depend on date date dimension (Day,month,year). and the other depend on month and year only. So my question do i need to create two dimension one has (day month year) and another dimension that only has year and month ?
Thank you .
A touch late here; sorry about that. Yes, you should build two dimension tables. I'd also recommend a relationship between them (i.e. each month has multiple days). Finally, and some consider this controversial, you might want to do more of a snowflake approach here and have the day level tables contain no information about months (eg month name, month number, etc.) beyond a link to the month table. The downside is that you'll almost always have to join the month table to the day table when you use the day table. Some feel this join is cheap and worth it for the benefit in reduced data redundancy. Others feel that any unnecessary join in a star is to be avoided.