THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Beware changing the Attribute Key

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):

  1. Rename the current Attribute Key, giving an attribute name which will be hidden to end user (i.e. ProductKey)
  2. Create the new Attribute with the desired application key (drag-and-drop it - suppose we have a ProductCode field).
  3. Rename the created Attribute (i.e. ProductCode -> Product)
  4. (optional) if the ProductKey attribute has a NameColumn defined, copy this definition in the new Product attribute (for the NameColumn property)
  5. (optional) set the NameColumn property of ProductKey attribute to None
  6. Move all the attribute relationships from ProductKey attribute to Product attribute, except the relationship between ProductKey and Product
  7. 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)
  8. 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.

Published Monday, July 16, 2007 3:58 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement