Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    29

    Unanswered: Multiple Sources, One Dimension

    Hi

    I have several DB's, and only one datawarehouse. I need to make sure that I'm not replicating dimensions. And then i need to map the references in the sources, to the references from the fact tables to the dimension table.

    I use SQLServer2K

    Thanks
    Joćo Marto

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Not sure on your question, I got two possible answers.

    1) if you are trying to use multiple facts or dim tables from multiple data sources you can create a cube based on a standard SQL view.

    2) You typically use a surrogate key and a natural key for each dimension, the fact table should be able to reference your dim table by its natural key.

    If neither addresses what you are asking, let me know.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Aug 2003
    Posts
    29

    Multiple Sources, One Dimension

    Table Worker on source1
    ID name
    ----------- --------------------------------------------------
    1 John
    2 Paul
    3 George

    Table Worker on source2
    ID name
    ----------- --------------------------------------------------
    1 Paul
    2 John
    3 Micheal

    what sort of primary key should I use in de Dimension table?

    And having a table with sales referencing the worker by it's ID, how can I get the dimension table primary key?

    Thanks
    Joćo Marto

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You typically use a natural and surrogate key, surrogate key would be arbitrary identity field while your natural key is what your OLTP system uses to reference the row. DO you mean how to get the natural key in your fact table? This you do by just specifying to pull the primary key of the transaction for all your dims in your ETL process (process used to pull your data from the OLTP store)

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Aug 2003
    Posts
    29

    Multiple Sources, One Dimension

    I'm making a tool to do the ETL process... and my problem is that the same member of one dimension may by present in multiple OLPT's, naturally with different natural keys.

    What key sould I use in the DW as natural key?

    When loading the fact table, what sould how can I get the surrogate key?

    Thanks
    Joćo Marto

  6. #6
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    a Surrogate key could be just a sequential identity column, some people don't use them but they can be helpful in case there are changes in your natural key among other things.

    I see what you are saying in multiple natural keys, would like an example of what you are seeing but I'll try to take a stab at what you mean.

    Let's say I have 4 applications each with customer addresses. Each of thsoe applications would typically have their own data mart consisting of subject oriented cubes. You could either have multiple dimensions for each customer address (one for each application) or you could store within your dimension table all natural keys for that row, Something like this:

    Dim_cust_address
    Columns - sur_key (only one if you are storing all natural keys) - nat_key1 - nat_key2 - nat_key3 - nat_key4 - any other dimension properties (street, house_nbr, prefix,suffix,etc)

    Now for your subject oriented data marts you would only need to store one natural key per fact table. (nat_key1, nat_key2, etc.)

    Then when building your cubes you can either use a view to link the dims to the different facts or make shared dimensions and access that that way. I'll admit I haven't done something like this but it should work OK.

    HTH

    BTW: The best book for these type of questions and much more is Ralph Kimball's Data Warehouse Lifecycle toolkit.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

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