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,

Ignaciodoesn'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?|||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