SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Building Database to Data Warehouse Database Maps
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/26/2012 :  23:54:04  Show Profile  Reply with Quote
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.

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 09/27/2012 :  12:19:35  Show Profile  Reply with Quote
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.

My personal feeling aside about the Kimball Group, they did put together a spreadsheet that you might be able to make use of:
http://www.kimballgroup.com/wp-content/uploads/2012/07/Ch02_MDWToolkit_Datamodel_Spreadsheet_4.0_2008R2.zip
Go to Top of Page

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/27/2012 :  13:43:08  Show Profile  Reply with Quote
thanks I took a look at your examples and really appreciate the feedback and help..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000