Time dimension not working in SQL Server Analysis Services

Question

My fact table has a "Date" column that I have used as a foreign key to the Time Dimension table I used SSAS to create for me in the datasource. However when I deploy the cube and browse it, adding the time dimension attributes or any of its hierarchies do not filter anything on the measures. Its like there is a disconnect between the time information in my fact table and the time dimension table SSAS created for me.

Am I missing something here ? How do I link them such that I can use the generated time dimension to apply slice and dice analytics on my cube based on the date values in my fact table ?

Answer 1

Is the date column the primary key column of the dimension table? Is that referenced by a foreign key from the fact table? Are the primary key and the foreign key relationship shown in the Analysis Services DSV? In the Cube editor, do you see a filled white area in the intersection of the measure group and the time dimension showing the correct key name, and not an empty gray area?

Does it really contain dates or datetimes?

A pattern used often is for a time dimension in OLAP applications when you have a granularity of days is to have an integer primary key of the structure yyyymmdd (i. e. year * 10000 + month * 100 + day_of_month. This makes the key human readable, but still is an integer which is slightly more efficient for Analysis Services.