Dim table showing NULL for date field, but source query to load table showing an actual date
-
Hi All,
I'm hoping I can get some insight into this issue I've been trying to investigate. There's a dim(ension) table which is showing a NULL as its PolicyCancelDate field value for a particular policy, but this same policy shows an actual date when the query which loads this table is used. I've followed this policy in the SSIS package via data viewers and its cancel date flows all the way through to the dim table. However, when I query the dim table after it's been loaded, the cancel date shows as NULL, while the source query does show the date. I then noticed the package has the usual "Pre-delete rows" sql task disabled, which is usually enabled in other packages. So just out of curiosity, I deleted/recreated/reloaded the table and then when querying against it, the said PolicyCancelDate actually populated the expected value. Initially, this issue was noticed in our upper environments, but it was not occurring in Dev and I tried the delete/recreate/reload of the table in the Int environment...not sure if this is the reason? I've included the queries and result records below. Any insight would be greatly appreciated. Thanks!
-
This does us no good. You're showing a table missing something and a query that isn't. What you need to show us is the actual code that does the table population.
As a bit of a sidebar, you're wasting 20% of the table space associated with your dates by converting them to VARCHAR(8). VARCHAR() carries an additional 2 bytes of overhead. If you must store them as character based, store them as CHAR(8). While we're at it, you have the dates originally stored as an INT. which is useless for any type of date time calculations without a conversion and then you more than double the number of bytes used by converting them to 8 character strings and still can't do much with date calculations. The DATE datatype would store the date in only 3 bytes (70% savings compared to VARCHAR(8)) and open up a world of temporal calculation abilities.
I guess I'll never understand why people do what the do to data in the name of data warehouses and other things similar.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Jeff. I'll keep this valuable feedback in mind. Meanwhile, I think I've found the issue causing the null dates...it has to do with the date fields not being checked for updates in a slowly changing dimension within the SSIS package. I'm hoping by including the date fields in this slowly changing dimension, it will correct them being changed to nulls. Thanks again for your input!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply