New to Data Warehouse - Setup data warehouse environment

  • Hi,

    I have asked to setup a data warehouse environment for two databases reside on two database servers.

    I am new to data warehouse and looking for a help.

    Sourse Systems : System 1_DB1, System 2_DB2

    I need to setup a replication for both the servers and feed the data from both the servers to a new server. In each database I have 20 identical tables, but the data source for these tables are different. I need to create a new data Warehouse where I can pull the data from these two servers for the reporting purpose.

    Also I need to create a data mart from the data warehouse system for the drill down reports.

    any sugesions and help is highly Appreciable.

    Thanks in advance..

    Reddy

  • Hello Reddy,

    I need to setup a replication for both the servers and feed the data from both the servers to a new server

    If I understand correctly, as you intend to use replication then your DW Tables would pretty well have the same structure as the Source DBs? I am guessing that the Source Servers host OLTP applications? Generally speaking DW DBs require a different design to OLTP ones, especially to improve query efficiency. May be worthwhile taking a look at the following MSDN page:-

    http://msdn.microsoft.com/en-us/isv/bb190492.aspx

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • [font="Verdana"]John's quite correct. Data warehouses are designed quite differently to an OLTP database.

    If all you need to do is consolidate two databases (in the same structure) into one so you can report across them, then your approach will be fine. Replicate the two systems onto the one server, and run some consolidation code. That will give you a reporting database (which is quite distinct from a data warehouse.)

    If you actually want to construct a true "data warehouse", I would have to ask: why? What is the business requirement that your database warehouse needs to fulfill? Always start with business requirements for data warehousing.

    If that's still what you want to do, I recommend you take a look at the Kimball data warehousing approach. There are several excellent books available by Ralph Kimball on the topic. They are very much business oriented, which I think is one key to an excellent data warehouse.

    [/font]

  • Thank you for your help!!!!

    Even I was thinking the same but requirement from client is that I need to setup a dataware house and the desgn should be as attached.

    Regards,

    Reddy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply