Is it possible to add a field DW_SourceTable in the default fields, so you can trace in the data were this is coming from. At a project I have database names like below:
6401 - Company 1$Appointment
6401 - Company 2$Appointment
6409 - Company 1$Appointment
For many of my records, the existing DW_Source is literal "View". I always expected that field to hold the source table... Man was I disappointed when I discovered that it holds database names. I'm curious, does anyone use the existing DW_Source?
Anyway, I agree with your suggestion, although I would have it replace the source database field. Or have some project/repo level standard where we can choose which of these to add.
DW_SourceCode actually identifies the source DB, not the source table.
We use DW_SourceCode actively when consolidating multiple of the same systems. E.g. accounting software that have a database per company (like Exact Online or Unit4 Venice), or multiple Navisions when our customers start buying each other and want to have operational reporting running quickly.
We include DW_SourceCode in the primary keys and in the lookups to make sure no cross-database lookups are performed.
This works well when doing all the heavy lifting in the Stage databases and using the DW purely for storage purposes.
DW_SourceTable field would be a welcome addition so that you indeed have:
- DW_SourceCode = source db
- DW_SourceTable = source table in that source db
Has been discussed already here as well: https://support.timextender.com/hc/en-us/community/posts/115011850126-Source-identifier-when-merging-multiple-simple-mode-tables
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
