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.