THE SQL Server Blog Spot on the Web

Welcome to - 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

Refresh of a linked dimension in SSAS

I started to evaluate the use of linked dimension across different database on the same server.

I encountered the first issue after a few minutes: when you link a dimension, VS editor create a dimension file that copies the structure of the dimension, without details on binding to data source (which are useless in this case). If I add an attribute to the original dimension, I am unable to use the linked dimension: the worst part is that I need to remove and to recreate the linked dimension on the project, losing all references to the dimension in all measure groups. At this point I need to recreate dimensions in cubes and dimension relationships for all measure groups.

It would really useful a "refresh" function that do automatically what I do to workaround the problem (I use the Customer dimension as an example):

  1. Preparation. Create the dimension Customer on database A (solution A)
  2. Deploy and process database A
  3. Create the linked dimension Customer on database B (that points to A.Customer) (solution B)
  4. Create a cube on B that use Customer
  5. Deploy and process database B
  6. Create a copy of solution B and name it database C (and solution C) - we will use it as a tool without deploying it to SSAS
  7. Change the original dimension. Add an attribute (for example Country) to the dimension Customer on database A (solution A)
  8. Deploy and process database A
  9. Make the change on linked dimension. Delete dimension Customer from database C
  10. Recreate linked dimension Customer on solution C that points to database A
  11. Open Customer.dim on solution C with the View Code function, Select All and Copy to Clipboard
  12. Open Customer.dim on solution B with the View Code function, Select All and Paste from Clipboard
  13. (as an alternative to 11 and 12, you can copy the Customer.dim file from solution C to solution B)
  14. Deploy and process database B

This workaround works if you only changed attributes that are not directly referred in the cube file: it happens when you use an attribute as a reference to another dimension or to a measure group. Most of the time dimension modifications does not touch those attributes and this workaround allows you to avoid the tedious and error-prone work of recreating dimension references to measure groups in the cube.

I would like to get feedback from people that already user linked dimension in a working (and changing!) environment.

Published Tuesday, September 12, 2006 10:37 AM by Marco Russo (SQLBI)

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



rptodd said:

I got all excited about linked dimensions and just spent several days moving all the appropriate (read as: conformed) dimensions to a separate cube in the data warehouse so I could utilize them.  I envisioned glorious days of dropping a linked dimension into a new cube without the hassle of rebuilding all the hierarchies and attribute relationships (yeah, which I can do by copy/pasting the code from the existing dimensions).  But my dream was pretty quickly quashed after I moved the Date (Time) dimension.  All the MDX Calculations I had built in the pre-existing cubes failed the first time I processed them after I had moved the Date dimension out, because the MDX Calculations almost all had references to a role-playing dimension that it could no longer find.

I'm going to spend about another hour looking into a way to work around that, and then I'm just going to use the tables in a central repository, and build the dimensions in the individual data marts (cubes) through copy/paste.

I've been surprised at how little I've seen about Linked Dimensions while trying to research this issue.  It seems to have huge potential, but doesn't live up to it.

March 20, 2008 10:59 AM

Marco Russo (SQLBI) said:

You can save your time by using cut&paste and dropping linked dimension.

The biggest issue is the work required when you modify a linked dimension, as I shown in this old post...


March 20, 2008 11:02 AM

David Hills said:

How the heck do you copy and paste dimensions between cubes?  I'm using 2005 can't find any way to do it.  I really need to move dimensions around without having to recreate them from scratch in each cube.


May 28, 2009 5:03 PM

Marco Russo (SQLBI) said:

I'm out of office now, but if I remember well, you have to put two SSAS projects into the same solution. Let me know if it works or not, and I will check it when I will come back to my office.

May 28, 2009 7:28 PM

Leave a Comment


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



Privacy Statement