Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Unanswered: Creating a Dimension Table from a 3-key table

    Hi All,

    I have a situation with a table that was created for a transactional

    system with a 3 columns key. The table is similar to the following:

    country state city description
    1 12 21 City A from country 1 and state 12
    1 13 21 City A from country 1 and state 13
    2 14 22 City B from country 2 and state 14
    2 15 22 City B from country 2 and state 15


    Now I'm trying to create a dts package that would allow me to build a

    city dimension table with unique codes (keys) for each city. What kind of

    transformation should I use to translate the old codes (based on the

    country-state-city key) into the new ones and preserving the data

    integrity?

    Thanks,

    Ignacio

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    doesn't that defeat the purpose of building a cube?

    What's it going to be for?

    How are you going to go after the data?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2004
    Posts
    9
    This cube is going to show sales history since 2002. The table where data is being pulled could contain data as the following:

    cust_id year country state city amount_cash amount_credit
    525 2002 1 12 21 8500 3200
    714 2002 1 13 21 3250 775

    Let's say I create a fact table with fk and measures only. If I would like to know city totals, it looks like cities sharing the same id will be aggregated, when in fact they shouldn't. And in the other hand, cities that are geographically shared by different states will add up correctly. Maybe I need to further analyze this, but what first puzzled me was the city table with no unique id.

    Thanks for your thoughts!

    Ignacio

Posting Permissions

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