locked
Cross Reference Tables RRS feed

  • Question

  • Hi everyone.

    Please I need an urge assistant here.

    I am working in a BI production environment that I get data from difference sources that has some common tables that are not arranged accordingly.

    Example:

    In source A, I have a table called Product as

    ID ProductName

    1  Laptop

    2  Printer

    3 Scanner

    In Source B, there is a Product table as

    ID ProductName

    1 Scanner

    2 Laptop

    3 Printer

    And source C, there is a Product table

    ID ProductName

    1 Printer

    2 Scanner

    3 Laptop.

    ......

    In each of these tables, the ID column are primary keys which are referenced in other tables(Fact tables) from their respective data source.

    However, I want to have a consolidated data warehouse where I will only have one Product table that would be used to reference the fact tables from the different data sources without having any conflicts in the referenced foreign key.

    Can someone help on clues on how to achieve this.

    Thanks


    me

    • Moved by Tom Phillips Monday, December 10, 2012 2:26 PM Query question (From:SQL Server Database Engine)
    Monday, December 10, 2012 11:21 AM

Answers

All replies

  • Something like that

    CREATE VIEW my_view

    AS

    SELECT 'A' AS Source,ProductName FROM SourceA.dbo.Product

    UNION ALL

    SELECT 'B' AS Source,ProductName FROM SourceB.dbo.Product

    UNION ALL

    SELECT 'C' AS Source,ProductName FROM SourceC.dbo.Product

    ----Usage

    SELECT * FROM my_view WHERE Source='A'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Uwe RickenMVP Monday, December 10, 2012 12:13 PM
    • Marked as answer by Iric Wen Tuesday, December 18, 2012 9:51 AM
    Monday, December 10, 2012 11:29 AM
    Answerer
  • Cool, but my challenge now is that I want to build an OLAP cube with this single reference table, how do I go about the relationship with the different facts tables from the different sources while building the cube.

    me

    Monday, December 10, 2012 11:41 AM
  • I am sorry I have no experience how to build cubs,  you will be better of asking that question in OLAP forum..

    SELECT * FROM my_view JOIN SourceA on my_view.PK=SourceA.PK WHERE Source='A'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Iric Wen Tuesday, December 18, 2012 9:51 AM
    Monday, December 10, 2012 11:43 AM
    Answerer
  • ok. Thanks for the effort

    me

    Monday, December 10, 2012 12:03 PM
  • Ideally you need to have a staging area (A different Database) where you would do data cleanup/resolution/de-duplication etc. After that the data would move to your DataWarehouse/Mart. this data mart will have only one primary key for each unique product. This is the "Golden" data fully resovled for data integrity issues. This is the one that you would use for creating cube. How to build a cube would be a different topic.

    • Proposed as answer by Dattatray Nale Monday, December 10, 2012 12:55 PM
    Monday, December 10, 2012 12:52 PM