BI Developer Studio does a lot of automatic changes when you change something. Each dimension is stored in a separate file, but many of the dimension informations are copied into cube files. When you modify a dimension which is already used by one or more cubes in the same project, the editor automatically updates many of these informations.
Each automatic behavior has a cost. In this case, the issue is when you want to change the Attribute Key of a dimension. In reality it shouldn’t be a common practice, but if you want to hide surrogate keys from the end users (see this previous post) then you might go in the wrong direction. After many tests, the most confortable way to do this is the following (we assume a Product dimension with an attribute key named Product):
- Rename the current Attribute Key, giving an attribute name which will be hidden to end user (i.e. ProductKey)
- Create the new Attribute with the desired application key (drag-and-drop it – suppose we have a ProductCode field).
- Rename the created Attribute (i.e. ProductCode -> Product)
- (optional) if the ProductKey attribute has a NameColumn defined, copy this definition in the new Product attribute (for the NameColumn property)
- (optional) set the NameColumn property of ProductKey attribute to None
- Move all the attribute relationships from ProductKey attribute to Product attribute, except the relationship between ProductKey and Product
- Change the Cardinality property of the Product relationship (under ProductKey attribute) to One (if your application key has the same cardinality as the surrogate key – this is not true in Slowly Changing Dimensions, for example)
- If you have user defined hierarchies, change the SourceAttribute from ProductKey to Product where necessary
At this point the attribute key is not changed – you only renamed it, but its internal key is the same and all references from the cube are still valid. If you try other routes (for example, I tried creating a new attribute for the surrogate key only, assigning to it the Attribute Key role in Usage property), related cubes might require a manual correction.
I’ll be happy to get feedback on better ways to get this job done.
Originally appeared on: http://sqlblog.com/blogs/marco_russo/archive/2007/07/16/beware-changing-the-attribute-key.aspx