SSAS: How to handle date dimension when date is null

Question

I'm trying to add a new column to my SSAS cube. The column is a date field, and links to my DimDate table (a Date dimension). This date represents the project completion date.

However.... not all of the projects have a project completion date due to old projects not ever being assigned this value. And this is expected. We don't want to put bogus dates into the field just to get SSAS to work.

When processing the cube, it crashes with:

Errors in the OLAP storage engine: The attribute key cannot be found when 
processing: Table: 'dbo_FactMyTable', Column: 'MyDate_id', Value: '0'. 
The attribute is 'Date Id'.

I can't disable "missing values" for the entire project because in most cases, this really is an error. How can I disable missing values for this dimension?

Or is there a better way to handle missing dates/values like this?

Ferdipux Ferdipux · Accepted Answer · 2016-06-24T06:33:52

Small correction - based on your question, you need to change Processing error handling for special Measure Group, not Dimension. You can do it for all dimensions linked to some measure group, but not to specific dimension.
You can process individual measure group with _Table: 'dbo_FactMyTable'_ first with necessary missing value settings, and then - process rest of your cube with default settings.
Main problem here - how to process rest of the cube. You might have sophisticated system which creates processing XMLA scripts dynamically based on data update knowledge (I do it with SSIS); in this case you would not ask this question. Suppose your environment is simpler - you update cube and would like to process it as a whole completely. In such scenario I would sudgest the following workflow:

Process Default all Dimensions (will do initial processing or in structure changes)
Process Update all Dimensions
Process Cube with Unprocess - invalidating it
Process your special measure group
Process Cube with Process Default

This will first update Dimensions, then - clear processing status flag from all measure groups in the Cube. After that you process your measure group with special flags; this set processing status for this MG. And then during Process Default on Cube - only unprocessed MGs will be covered, which excludes your special MG from processing scope.

SSAS: How to handle date dimension when date is null

2 Answers