Date field value changing to Null after loading into dimension table

  • daniness

    SSCrazy

    Points: 2890

    Hi All,

    I'm trying to investigate an issue where a date field, PolicyCancelDt, of type datetime from an extract table is changing to NULL after loading into a dimension table, where the corresponding field is of type int. There is a conversion in the query for the extract:

    SELECT  DISTINCT 
       po.sPolicyID AS PolicyAgreementNumber
                       --, 'Unknown' 
     , mv.[Market Vertical] as MarketVerticalName 
     , c.sCustomerID
     , p.sProductID
     , c.sCustomerID + p.sProductID as CombinedClientProductID
     , CONVERT(Int,Convert(varchar(8), po.PolicyIssueDt, 112)) as                                   PolicyIssueDt
     , CONVERT(Int,Convert(varchar(8), po.PolicyEfftvDt, 112)) as                                    PolicyEfftvDt
     , CONVERT(Int,Convert(varchar(8), po.PolicyExpirDt, 112)) as                      PolicyExpirDt
     , CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as                      PolicyCancelDt
     --, -1
     --, -1
    FROM dbo.ExtPRSDEPolicy po
     join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
     Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
     LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
     LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
    WHERE jw.sProductID is not null OR p.sProductID = '9095'
    ORDER BY PolicyAgreementNumber

    I'm not sure why this is happening for some records. There's a slowly changing dimension data flow as part of this package, but this date field has no part in it. Could someone please advise on why this might be happening?

  • frederico_fonseca

    SSChampion

    Points: 14685

    daniness - Wednesday, January 2, 2019 11:40 AM

    Hi All,

    I'm trying to investigate an issue where a date field, PolicyCancelDt, of type datetime from an extract table is changing to NULL after loading into a dimension table, where the corresponding field is of type int. There is a conversion in the query for the extract:

    SELECT  DISTINCT 
       po.sPolicyID AS PolicyAgreementNumber
                       --, 'Unknown' 
     , mv.[Market Vertical] as MarketVerticalName 
     , c.sCustomerID
     , p.sProductID
     , c.sCustomerID + p.sProductID as CombinedClientProductID
     , CONVERT(Int,Convert(varchar(8), po.PolicyIssueDt, 112)) as                                   PolicyIssueDt
     , CONVERT(Int,Convert(varchar(8), po.PolicyEfftvDt, 112)) as                                    PolicyEfftvDt
     , CONVERT(Int,Convert(varchar(8), po.PolicyExpirDt, 112)) as                      PolicyExpirDt
     , CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as                      PolicyCancelDt
     --, -1
     --, -1
    FROM dbo.ExtPRSDEPolicy po
     join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
     Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
     LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
     LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
    WHERE jw.sProductID is not null OR p.sProductID = '9095'
    ORDER BY PolicyAgreementNumber

    I'm not sure why this is happening for some records. There's a slowly changing dimension data flow as part of this package, but this date field has no part in it. Could someone please advise on why this might be happening?

    if this query is what is used to load onto the target dimension then the source (dbo.ExtPRSDEPolicy.PolicyCancelDt) is null on those records - those 2 converts as they are would result in an error if the data was "bad". As that is not what you are reporting then only option is that they are null already.
    you can check this by testing 
    select *
    from (
    SELECT DISTINCT
     po.sPolicyID AS PolicyAgreementNumber
    , mv.[Market Vertical] as MarketVerticalName
    , c.sCustomerID
    , p.sProductID
    , c.sCustomerID + p.sProductID as CombinedClientProductID
    , CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as        PolicyCancelDt_int
    , po.PolicyCancelDt
    FROM dbo.ExtPRSDEPolicy po
    join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
    Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
    LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
    LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
    WHERE jw.sProductID is not null OR p.sProductID = '9095'
    ) t
    where PolicyCancelDt_int is null

    Edit: Another option is that there is a trigger on that table that changes that column to null on certain cases.

    If this is not the query that is loading onto the dimension then please post the correct query.

  • kn_murthy

    Grasshopper

    Points: 12

    Have you tried casting the date fields as datetime or datetime2 ?

  • daniness

    SSCrazy

    Points: 2890

    Thank you, @frederico_fonseca. I tried your test query but it's not returning the record with the PolicyAgreementNumber in question. As for triggers, my department doesn't utilize any in the DataMart. Any other suggestions are welcome. Thanks!

  • daniness

    SSCrazy

    Points: 2890

    kn_murthy - Wednesday, January 2, 2019 11:58 AM

    Have you tried casting the date fields as datetime or datetime2 ?

    @kn_murthy, I don't think I need to do this, as the field is changing to NULL for random records.

  • ZZartin

    SSC-Dedicated

    Points: 30414

    daniness - Wednesday, January 2, 2019 12:23 PM

    kn_murthy - Wednesday, January 2, 2019 11:58 AM

    Have you tried casting the date fields as datetime or datetime2 ?

    @kn_murthy, I don't think I need to do this, as the field is changing to NULL for random records.

    Well are there any post updates that run after the initial data load that might be changing the values?

  • Greg Edwards-268690

    SSC-Insane

    Points: 20572

    Try looking at the data for the product ID for each side of the OR ctriteria.

    If they are different your distinct might be not so distinct.

  • daniness

    SSCrazy

    Points: 2890

    ZZartin - Wednesday, January 2, 2019 1:48 PM

    daniness - Wednesday, January 2, 2019 12:23 PM

    kn_murthy - Wednesday, January 2, 2019 11:58 AM

    Have you tried casting the date fields as datetime or datetime2 ?

    @kn_murthy, I don't think I need to do this, as the field is changing to NULL for random records.

    Well are there any post updates that run after the initial data load that might be changing the values?

    @ZZartin, there are only error row count and an error rows raw file data flow components...I don't think these would be causing the issue though.

Viewing 8 posts - 1 through 8 (of 8 total)

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