Time Dimension table join to a fact table which has null time

Question

Time Dimension table join to a fact table which has null time

george.qiao-644713

Old Hand

Points: 310
More actions
December 14, 2008 at 4:10 pm

#216580

Hi Guys,
I need a help for the following situation.
I have a time dimension called DimDate
I have a fact table which has a column called ExpiryDate
However, the ExpiryDate may contain null value.
And I plan to build a hirarchy like ExpiryYear-->ExpiryMonth-->ExpiryDate-->Products.
When I tried that, BIDS didn't give me warnings. However, when I process the cube, it throws errors.
Error 1 :"Errors in the high-level relational engine. The 'dbo_vProduct' table that is required for a join cannot be reached based on the relationships in the data source view."
Error 2:"Errors in the OLAP storage engine: An error occurred while the ExpiryDate' attribute of the 'DimProduct' dimension from the 'BI_Test' database was being processed."
How can I achieve this?
Best Regards,
George

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 1

Put a value in your date dim with the value Unknown or undefined and link the null (or invalid date) values in your fact to this

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 2

There is an "UnknownMember" property on your dimensions, you can investigate that option.

A couple of links that may also point in the right direction -

http://www.sqlservercentral.com/Forums/Topic555260-147-1.aspx

http://www.sqlservercentral.com/articles/Analysis+Services/64802/

Paul Williams SSCommitted Points: 1930 More actions · Answer 3

It's not best practice to have null values in any data mart/data warehouse.

You should provide a default time value during the ETL process in place of the null e.g. for datetime 1900/01/01 00:00:00 or similar depending upon your data, and then have this as your unknown member in the dimension.

Paul R Williams.

TangoVictor SSChampion Points: 10975 More actions · Answer 4

I do the same thing Paul said. Here's the technique I use when dealing with assigning surrogate key values to my fact records for unknown members. I set up every dimension to have an unknown member, if it's a string then I have a value like "No Value" or date like Paul mentioned. The surrogate key value for the unknown member in all my dims is -1. When using SQL to build the fact record use the coalesce statement - coalesce(KeyValue,-1) or in SSIS use a derived column to convert null to -1 , this makes it real easy to locate those unknown members when doing a health check on the data.