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

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

Change the original dimension. Add an attribute (for example Country) to the dimension Customer on database A (solution A)

Deploy and process database A

Make the change on linked dimension. Delete dimension Customer from database C

Recreate linked dimension Customer on solution C that points to database A

Open Customer.dim on solution C with the View Code function, Select All and Copy to Clipboard

Open Customer.dim on solution B with the View Code function, Select All and Paste from Clipboard

(as an alternative to 11 and 12, you can copy the Customer.dim file from solution C to solution B)

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.

Comment Notification

Comments

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.

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.

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.