I've been asked to build a Table/Column map view that would show column mappings between 2 databases for those tables we've referenced from the core database while we've built out our Data Warehouse tables. Our data warehouse of course isn't an exact dup of the tables and columns from the core tables of the initial database which is why it would be nice to have a mapping of this available for anyone to run as a view to see what's going on...sort if a nice documentation but very helpful view/tool.
I figure this must be a common thing to do but I'm not a DBA. We will leave this view mapping with the client as well as QA can use it to help them understand how our Data Warehouse Database Tables/Columns maps back to the core database tables/columns of the client....like an overall map view of the fields and how they map between the databases and our core data warehouse tables.
does this make sense? I'm lost at how to even start that and how it should look.
I probably have a bunch of repeating sentences there but need some help as to how to do this.
This can be a very painful process as what ends up in a data warehouse can be/is transformed and/or aggregated or massaged in some way from the original source data. So, in general, if you wanted to create such a mapping you'll have some columns that map directly back to a source and others that are derived in some fashion.
That having been said, the real values is in defining what the data in the data warehouse means, not necessarily where it came from.