There are 2 columns in my fact table, order_date and ship_date. Each row has an order_date, but ship_date is NULL, possibly for orders that are yet to be shipped, or were cancelled and never shipped. Pulling it in a cube kept generating the error message
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘table_name’, Column: ‘Column_name’, Value: ‘0’. The attribute is ‘attribute_date’.

This was happening when trying to lookup the key values for NULL/missing ship_date against time dimension

To resolve this, we need to let the Fact table ignore NULLs or zero’s in the data

In BIDS, open the Cube Structure Tab
In the Measurements pane, select the Fact table the error is related to
Change ErrorConfiguration property to (custom)

Expand the ErrorConfiguration property,
 set KetNotFound property to IgnoreError
 set NullKeyNotAllowed to IgnoreError

This will pass-through all NULL or 0 key values to the dimension as “Unknown Member”, leaving all error handling for referential integrity issues for the Dimension to handle.

Open the related Dimension
In the Dimension Structure pane, select the Dimension name (at root level)
Change UnknownMember to Hidden

Process the Dimension, and then process the Cube

Advertisements