Hi, I have about 360 fields in a table and all but a handfull are using SCD Type II. The business users want to see only the fields that have changed (plus effective dates) and none of the other fields (for past 30 days). So maybe 6 of the 360 fields in a record would have changed for a certain clientID and these are the fields I need to Isolate. The Kimball tool is working well, however not sure how we might bring this in. Is there a way to specifically isolate and bring in changed field values of a record through the tool (or otherwise) and not the entire record. The Row Change reason field is very close and helpful, but it doesn't offer the changed value... I look forward to any ideas. Thanks, Pete

You probably wouldn't be able to do anything with SCD type 1 changes unless you capture the row change reason output column and store it somewhere. As for the SCD 2 changes... (your real issue...) I'll describe my thought process on what would work. Select all records with an effective date in the past 30 days. All those records have something changed on them (or they're brand new). You'd probably want to select the last record for each business key - because the same entity may have had several changes during the 30 days. (That'll take a subselect/CTE for sure.) You'd then want to get a rowset of how those rows looked prior to 30 days ago. So using the set of BKs of rows that have effective dates under 30 days old, select the oldest rows that are more than 30 days old. That will eliminate the "new" BKs - since they won't have anything to compare to that's more than 30 days old... maybe you want that, maybe you don't. Now compare those two rowsets - JOIN by BK - and compare each of the columns. You could do the above all in T-SQL, or in SSIS. But it's really unrelated to the DMSCD (note the new name - you might want to get the latest version), and should work with any SCD processor. Talk to me now on

Todd, Thanks very much for your feedback. I thought that using a CTE might be my best option. I know that the issue is not related to DMSCD, but the tool comes so extremely close to providing everything I need, escpecially since it has a RowChangeReason output. I think the solution you offer is well laid out. I'm just wondering if I can simplify it because I don't think I will need to bring in the last record for each business day. Most changes are not that frequent...maybe once a month...So I'm going to read this again and reflect a bit more...Thanks again. Pete

Good. I didn't mean to bring in the last record for each business day. I mean that you need the last record per BK, period. For each of those records that has an effective date later than a month ago, you'll also need the record for that BK that was effective 30 days ago. So just two records per BK. Talk to me now on

Good. I didn't mean to bring in the last record for each business day. I mean that you need the last record per BK, period. For each of those records that has an effective date later than a month ago, you'll also need the record for that BK that was effective 30 days ago. So just two records per BK. Talk to me now on

Oh I see. I think I may have misread the original post. That makes sense. It seems much more straightforward now. Thanks again!