0

i have a question related to the structure of dimension-keys. I am building classical Star Schema. Therefore i am creating dimension keys by using a sequence so that every entry in my dimension table has its own unique key. So far so good. Now i have seen a key structure by a project which is created by the oracle Warehousebuilder. This Software defines additionally to the dimension keys dedicated keys on every level of the hierarchy of a dimension. That looks like the following example:

- Dimension Key
--------------------------------------
- country ID
- country name
--------------------------------------
- state ID
- state name
- state short
--------------------------------------
- city ID
- city name
- city code

Is that really necessary? If not what are the benefits or the chain of thought of this approach?

user3364656
  • 279
  • 2
  • 4
  • 9
  • If I understand you correct and it's a single dimension table - then the reason is so you use the individual countries, states and cities independently of each other and/or you can map them to existing data in ETL processes. As for "is it necessary"- then as almost always with databases and model then "depends" based on what you need. – Allan S. Hansen Jan 29 '15 at 11:28
  • does the operational system has country, state and city tables? If it does then those id's are used to map the information to the operational system. If it is necessary? it is used for the user can locate that data in the operational system (it can be used for auditing and to quickly find -- in the operational system -- something strange which might show up). – SQL.injection Jan 29 '15 at 15:24

1 Answers1

0

There's no need for it, but it does sometimes make the record matching easier.

For example, if your source system already assigns individual keys to Paris, Texas and Paris, France, you only need to lookup the keys, not the city names, ensuring there are no mismatches. Beware that there may be different cities with the same name even in the same country (e.g., on different administrative regions). Having unique keys assigned on all levels prevents these mismatches. It may be over the top for most common uses, but there's nothing wrong with being extra cautious.

nsousa
  • 4,448
  • 1
  • 10
  • 15