Schema Design

Last Post 22 Mar 2012 05:32 PM by russellb. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
duke442games
New Member
New Member

--
22 Mar 2012 01:44 PM
I am in the process of building out a data mart that consolidates data from a large number of applications where each has their own database. While I have direct access to some of the application's DBs, I pull most of the applications from a central data warehouse. Right now we are creating separate Databases for each system, but this seems to be causing problems in locating tables and pulling tables into incorrect DBs. IE- the carSales table is in the Leasing application and in the Loan application. What DB should we bring it into Leasing, Loan, both, or a 3rd option? I am not highly confident in this schema, and looking for documentation on how to either make it work, enhance it, or documentation on why this schema should be scrapped. Thank you,
russellb
New Member
New Member

--
22 Mar 2012 05:29 PM
There isn't enough information to adequately answer the question. But a few considerations:

- what is the purpose of the data mart?
- is your data mart a single database or many?
- if many is the intent to mimic the OLTP sources?
- can you control the schema that you will import (this is the most important one)?
- If so, you define a schema, be it SQL, XML, whatever, that you will supprt. Then you create the interface.
- If not, the very first step in this type of project is to MAP THE DATA from the source structures to the destination ones. And by this, I mean create a document (Excel is perfect for this) mapping table.column to table.column being sure to handle data type transformations and schema/owner transformations.
russellb
New Member
New Member

--
22 Mar 2012 05:32 PM
This is just a bare bones description. We'd need to know a lot more about you application to be a lot more helpful.

But basically, you create the destination schema first, the mapping second. This BEFORE any importing is done. And you test it. A lot.
You are not authorized to post a reply.

Acceptable Use Policy