Errors in the OLAP storage engine: The attribute key cannot be found

I'm somewhat new to using SQL server analaysis services so bear with me on this post.

I am getting the following error when trying ot process a simple cube I built:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_vwInvoice, Column: Invoice_x0020_Date, Value: 3/7/2004 10:00:00 AM.

This cube use an Invoice table as the fact table and then a "geography" table as a dimensions table. I am using the server built time dimension which seems to be the problem. It seems to not like the "Invoice Date" field which is a datetime field. This appears to happen on imported data from a legacy application where that application didn't store time so we defaulted the time to 10:00 AM. My guess is that the Invoice Date field has to be unique? If that is the case, any idea on how to get aroudn that?

Thanks for your time,

Marc

Hi,

The error means that the '3/7/2004 10:00:00 AM' value from the 'dbo_vwInvoice' fact table cannot be found in the time dimension table. You mentioned that the time dimension is the server generated one. The server time dimensions only go down to the day level (not hour, minute and second), thus the value will indeed not be found.

If you do not need to do analysis on the hour/minute/second level (in other words, if you do not use the time part of the 'Invoice Date' from from the 'dbo_vwInvoice' fact table), then you can do this:

- create a calculated column (in the DataSourceView, you don't need to alter the actual relational table) in the 'dbo_vwInvoice' fact table to only keep the date part of the 'Invoice Date' column

- then join the fact table (in the 'Dimension Usage' tab of the cube editor) with the time dimension table by that calculated column

Adrian Dumitrascu

|||

You know, I was kind of thinking that but I couldn't find any documentation stating that. Thanks for the response. I would have thought it would inheritly figure out that the field is a datetime field and to just extract the date portion. Easy enough to fix though.

Thanks again,

Marc

|||

Well I spoke too soon. I'm still getting the error. I did a convert(varchar(10),invoice date,101) as the calculated field and still came up with this errror:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_vwInvoice, Column: InvoiceDateOnly, Value: 3/29/1998. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Date of Dimension: TimeDimension from Database: WinSTIS Analysis Services Project, Cube: Winstis, Measure Group: Vw Invoice, Partition: Vw Invoice, Record: 11.

Any ideas?

Thanks,

Marc

|||

Sorry for the confusion. I figured it out. It was because I didn't have a large enough date range on the time dimension...

Marc