SQL Analysis Services OLAP TIME dimension

Question

Hi i'm struggling with adding time dimension to OLAP cube. I can get everything in cube to work except date.

In my source data view I have datetime column.

I go by using Dimensions->New Dimension->Generate time dimension on the server. I end up with a nice hierachical time dimension (Date-Month-Quarter-Year).

Later I add this dimension to cube and define regular relationship with datetime column from source data view (same table which has fact data).

When I try to deploy the cube, I get error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing:Table: 'table_name', Column: 'registration_date', Value: '3/29/2007 3:00:00 PM'. The attribute is 'Date'

Maybe I don't get something? Every manual I can find talks about calendar table already created in the source database. There are plenty of script which will create calendar table for you. But why should I ? Isn't Generate time dimension on the server meant for it?

Answer 1

I would guess that your date field in your fact table needs to be present in the time dimension. Perhaps remove the time or create a calculated field in the SSAS designer. More experience people may have better answers, I've only made one cube.