Data Warehousing and Business Intelligence

21 September 2010

Renaming Attribute and Dimension

Filed under: Analysis Services — Vincent Rainardi @ 12:35 pm
Tags:

Say the attributes and dimensions in the enterprise data warehouse are renamed and your cubes are impacted. Apart from renaming the attribute & dimension themselves, when estimated the effort we also need to consider:

  1. Attribute relationships
    After changing the attribute names, the attribute relationship will show a yellow circle with exclamation mark. You will need to delete and recreate them. No, editing them won’t work.
  2. Hierarchies
    Many of the renamed attributes are used in hierarchies. The levels on these hierarchies should also be renamed, to match the attribute names. The hierarchy name itself should also be renamed.
  3. MDX Scripts/Calculations
    This looks like a trivial “replace all”, but be careful when doing replace all. Sometimes it makes a few things stop working, for example:
    a) Same dim name, attribute & level, i.e. [Branch].[Branch].[Branch]. You meant to replace the Dim Name only, but the “replace all” replaced all three.
    b) That word is part of Hierarchy Name, i.e. [Electronic Product Hierarchy]. You meant to replace [Product] dim name to [Service] but the above Hierarchy Name in the calculation would also be replaced.
  4. Default Values
    If an attribute has default value, it needs to be changed too. For example: dim = account, attribute = account type, current default value: [Account].[Account Type].[&Bond]. If Account dim is renamed to Product and Account Type attribute is renamed to Product Type, then the default value becomes: [Product].[Product Type].[&Bond].
  5. Dimension Usage
    If you change a name of a dimension, the dimension name on the Dimension Usage tab should also be change. Dimension Usage is the tab that contains the relationship between dimensions and MGs.
  6. Translations
    Both on the Cube Translations (the dim names are here) and the Dimension Translation (the attribute names are here). For example, if Account Type is changed to Product Type, then you don’t want the French translation to stay as Type De Compte. You would want it changed to Type De Produit. And the Spanish: from Tipo De Cuenta to Tipo De Producto.
  7. Analytics Reports
    Remember that the attribute names, dimension names, and measure names on all analytics reports Strategy Companion, Tableau, ProClarity, SSRS, Excel/Vivid would need to be changed too. This is why once a lot of reports are built, practically speaking it’s impossible to change the attribute/dimension name.

I would definitely discourage the “shortcut” practice of: “export to XMLA, search & replace all, deploy to AS server, recreate BIDS project from the server”. True that this is quick, but it won’t work, apart for the simplest cubes. But if it’s a very simple cube, then renaming it on BIDS is quick & simple too. So definitely do it in BIDS.

If you are changing measure names as well, things that are impacted are:

  1. Calculations / MDX Scripts
  2. Default Measure of the cube

Hope this helps your impact analysis/estimation. As usual I welcome discussion and questions at vrainardi@gmail.com

3 Comments »

  1. We are about to undergo a mini-project of renaming our dimensions from a pattern of “DimProduct” to “Product”, since our front end is PerformancePoint, which displays the literal dimension names with no ability to substitute a friendly name alias.

    These are the steps that I found worked best for doing the rename – and it takes a max of 5 minutes per dimension:

    1. Rename Dimension file to the friendly name in BIDS.
    2. Close and save the solution. (This updates the SSAS Project file.)
    3. Open the Dimension file in Notepad, change the value to the friendly name.
    4. Open the Cube file in Notepad, and change all the ID and Name references for the cube dimensions. (Don’t just do Replace All, though in case your DSV has tables with the same names!)

    Hope that helps…

    Comment by Jeff Brumley — 5 May 2011 @ 6:54 pm | Reply

  2. I have a question on the Renaming a Cube Dimension.

    I have Cube dimension–“xx CurrentFY” I need to rename to “xxFY14~FY13” , If I reame it I need to change 48 MDX scripts in my reportdatasets.Is there any way where I can just rename cube dimension but still MDX should point the old Name “xxCurrentFy” but in the Cube browser user can see the new Name “xxFY14~FY13”

    I saw below ,in the cube dimension Properties “ID” and “Name”
    ID–>OldName (i.e) xx CurrentFY
    Name–> NewName (i.e,) “xxFY14~FY13”

    Any thoughts one above one where I can Use this ID in my MDX and for display User can see the new Name while browsing the cube..

    Any Ideas would be helpful

    Thanks
    ~PM

    Comment by PM — 17 June 2013 @ 10:50 pm | Reply

    • Hi Kumar, As far as I’m aware by default SSAS MDX uses the cube dimension name. I don’t know how to use the cube dimension ID instead of cube dimension name in the MDX. Sorry. Usually people talk about the member ID and caption in MDX, not the cube dimension ID and name 🙂

      Comment by Vincent Rainardi — 18 June 2013 @ 4:50 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: