Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    60

    Unanswered: Avoid Cross Database Views in Data Warehouse

    We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –

    1) Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –

    a. The size of tw will grow tremendously.
    b. We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.

    2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.

    As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.

    Thank you all in advance,

    sajmera

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sajmera
    We have a relational database (rd) and a data warehouse (dw).
    No you don't. You have a datamart. If you had a real data warehouse schema you would not have these issues.

    Quote Originally Posted by sajmera
    The DBA wants me to remove the look up views.
    Why?

    Quote Originally Posted by sajmera
    2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
    A data warehouse (or datamart) should be self-contained, and not rely upon pulling dynamic data from other systems. Maintenance and support are necessary requirements of a data stores.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •