In an analytic workspace dimension, each member must be unique across all levels. Because each level may be mapped to a different relational column, this uniqueness is seldom enforced in the source data.
For example, a
geography dimension table might have a value of New York in the CITIES column and a value of New York in the STATES column. Unless you take steps to assure uniqueness, the second value for New York will overwrite the first.
To ensure uniqueness among levels, you can create surrogate keys by adding a level prefix to the members while loading them into the dimension. For the previous example, you would create two dimension members named CITIES_New_York and STATES_New_York instead of a single member named New_York.
However, surrogate keys will not ensure uniqueness within a level. For example, if the CITIES column has several values of Springfield, then surrogate keys would change all these values to CITIES_Springfield. Again, each value of CITIES_SPRINGFIELD would overwrite the one loaded previously into the analytic workspace. To provide unique values, you must create a view in SQL that concatenates STATES and CITIES for the city level. The view provides values for cities such as MA_Springfield, NJ_Springfield, and VA_Springfield.
Then you can use Analytic Workspace Manager to map the geography dimension to the new view.
Note: If you change this setting after loading dimension members, the change will affect new members, but it will not change existing members.
Generate Surrogate Keys in the Analytic Workspace
Select this option unless you know that every dimension member is unique. During the load, the level name is added as a prefix to each value.
You must define the dimension with at least two levels.
Use Keys from Data Source
If you are sure that dimension members are unique across levels, then you can use the exact same names in the analytic workspace as the source. For example, if your relational schema uses numeric surrogate keys to assure uniqueness, then there is no need to create new surrogate keys for the dimension.
You can use the source keys to define either level-based or value-based hierarchies. You must use source keys if you plan to map summary fact tables.
Copyright © 2003, 2007, Oracle. All rights reserved.