Post reply

New to Data Warehouse - Setup data warehouse environment

  • June 1, 2009 at 9:02 pm

    #204960

    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

  • June 1, 2009 at 11:48 pm

    #1003182

    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

  • June 2, 2009 at 12:04 am

    #1003187

    [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]

  • June 2, 2009 at 12:32 am

    #1003195

    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