SQL Analysis Services OLAP TIME dimension

5
votes

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?

1
sql-serverolapcubedimensioncube-dimension
The '3:00:00 PM' is a major red flag. Are you storing times in your time dimension? They're typically only for dates without time, and the dates in your fact tables need to have the time part stripped off. Time, when required, is typically a separate attribute or, in rare cases, possibly its own dimension. – mattmc3

1 Answers

1
votes

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.