Why oh why did SQL Server 2005 not include a "date" data type!!! Analysis services does not seem to like datetime at all. For example if you create a server time dimension and try to link it to a datetime field you can't because the time dimension has no datetime concept (this if the data contains a time, which if you are creating data using CURRENT_TIMESTAMP it will.
Instead you have to use CONVERT() and DATEPART() to create a calculated field. which you can then link to the time dimension.
Hower I have found an error when you try to link a date in a dimension table.
Fact table = order_details
Dimension = order_header
order_header contains a calculated date field (using CONVERT and DATEPART) which I am trying to join to the time dimension I have created. This results in a PCProcBinding error when you try to process the cube.