Handle SSIS lookup failure on Date Dimension Date column NULL value

Handle Lookup failure when look up null value in Date dimension.

Problem : 

Lookup fail when look up null value in Date dimension.

I have date Dimension table with one row for Null value. In my OLTP Order transaction data some of the date data type column has null value.

I am getting the lookup key not found error and those rows are redirected into reject table. Actually both null was not matched up and not able to found the NULL value dimension key.

SOLUTION :

So in this blog I have explained the customized date look up script with the following screen shots..

Note(Alternate solution):

I don’t want to replace the OLTP date column NULL value with some default date (like 1/1/1900). If I do so in the reporting layer I have to replace the default date (1/1/1900) into NULL. Otherwise this will be misleading in the report also I feel this approach is overhead.

Database & SSIS : SQL Server 2008

Table        ColumnName                            DataType
Date_D    Date(lkp column)                        Date      
Order       ShippingDate(Source column)     Date      

 
select date_Key, date from [dbo].[Date_D]
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 
select * from (select date_Key, isnull(date,'1/1/1900') as date from [dbo].[Date_D]) [refTable]

where [refTable].[date] = ISNULL(?,'1/1/1900')
 
 
 
 
 
 
 
 
 
 
   

I am not sure about the performamce of Date dimenison look up. I am working on this...Since its a date dimension volume is not the consideration.

 

I hope this would be helpful to all.

Wednesday, September 29, 2010

Create Measure for DateTime DataType in SSAS

*** Self reference entry by Liyasker **
There is a requirement to create the Measure for User InTime Hrs, OutTime Hrs, CheckInTime, CheckOutTime, AVGCheckInTime with HH:MM AM/PM format.

But SSAS measure will support only for Integer and Numeric. Our case I have to expose the cube measure in to MS EXCEL 2007. There we can’t do any conversion.

I got the right help from the following blog of John (Thanks John :)). Please go through this, this will help you.

http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx

I create a Facttable view and did the float conversion like below
,CONVERT(FLOAT,CAST(InTime_InHrs AS DateTime)) + 2 AS OfficeInTime
,CONVERT(FLOAT,CAST(OutTime_InHrs AS DateTime)) + 2 AS OfficeOutTime
,CONVERT(FLOAT,CheckInDateTime) + 2 AS InDateTime
,CONVERT(FLOAT,CheckOutDateTime) + 2 AS OutDateTime

Then I apply the Time format in SSAS Measure Properties.

I hope this will help to calculate DateTime Measure in SSAS.