Copy SQL Server Analysis Services Dimensions

By: Daniel Calbimonte   |   Updated: 2012-12-17   |   Comments (5)   |   Related: > Analysis Services Dimensions

Problem

How can you copy dimensions from one SQL Server Analysis Services (SSAS) Project to another?  Are there any options to do so?  This tip is for SQL Server BI Professionals who need to copy dimensions from one SSAS project to another. I will outline three approaches to address this need in this tip.

Solution

There are three solutions to copy dimensions from one SQL Server Analysis Services (SSAS) Project to another SSAS Project. Those options are:

1. Linked dimensions
2. XMLA (trust me, is not that hard and I promise to explain this for beginners)
3. Copy the dimensions file and add the item in the destination project

Requirements

In this tip, I am going to use SQL Server 2012, but the solutions are applicable to SQL Server 2008 or 2005.  As a point of reference, here are the required items:

• SQL Server
• The AdventureWorks Database

In this example I am going to copy the dimension date from a project named AdventureWorks (i.e. Project A) to a solution named Multidimensional6 (i.e. Project B) as shown below:

Sometimes we have nice dimensions with structures, attributes already tested in other projects and we want to use them again as shown below.

Option 1 - SQL Server Analysis Services Linked Dimensions

The first method to use existing dimensions is to use the linked dimensions functionality.  In order to add the dimension in Project B from Project A, go to Project B and in the dimension folder right click and select "New Linked Dimension" as shown below.

You will have a Wizard to connect to the other project. If you do not have a connection, press the "New" button as shown below.

Now select the dimensions that you want to add as shown below.

Finish the wizard.

The new dimension will now be available, but you will not be able to change the structure, since it is a linked dimension. The new dimension was not physically added.

You can however see the translation section as shown below.

If you have the default configuration, you will see the following error message in the browser tab of the linked dimension:

Here is the error message "Errors in the OLE DB provider. The server returned the following error: (Errors related to feature availability and configuration: The 'Linking to other instance.' feature is disabled by the 'ConfiguationSettings\feature\LinkformOtherInstanceEnabled' property.)."

OK, it seems that we have to enable a property. The question is where do I do that?

The answer is in the SQL Server Management Studio. Log on to Microsoft Analysis Server,  right click on the server name, and select "Properties".

Check the box for "Show Advanced (All) Properties" as shown below..

Set the LinkFromOtherInstances property to "true" as shown below.

Alternatively, we can configure the property in the msmdsrv file. This file contains the SSAS properties.  This file is in the Microsoft SQL Server\MSAS\OLAP\Config folder as shown below.

In the msdmsrv file to enable the property, by setting it to 1 as shown below.

Once you have configured the property, restart the SSAS Service as shown below.

Once enabled, go to SSDT or BIDS and process the dimension as shown below. Now you will be able to browse the dimension.

You may wonder why is the LinkedFromOtherInstances is disabled by default?  There are some disadvantages of using linked dimensions. The first problem is the dependency on the other project. If someone changes the other dimension, it may affect all of the projects using it. There is a pretty nice A href="/sqlservertip/2351/controlling-the-use-of-linked-objects-in-sql-server-analysis-services/">article about this written by Siddharth Mehta if you want to read more about it.

Now, what is the next solution?  Let's see how we can do this with XMLA.

Copy SQL Server Analysis Services Dimensions with XMLA

What we are going to do in this solution is:

1. Generate a XMLA script from the dimension in Project A.
2. Modify it the XMLA script and run it in Project B.

Here are the detailed steps:

IIn Project A (the Adventureworks solution) go to SQL Server Management Studio (SSMS) and connect to Analysis Services.  Go to the dimensions and select the "Date" dimension. In the date dimension, select "Script Dimension as" > "Create To" > "New Query Editor Window". These actions generate the script to create the dimension.

The XMLA script is from the AdventureWorksDW2012Multidimensional-EE database. We need to change the XMLA code to create the dimension in the other project, so me will change the DatabaseID.

Our Project B is named "MultidimensionalProject6", but sometimes the name and ID are not the same. For example, if your database was originally named "DatabaseA" and then you change the name to "DatabaseB".  Although, the name is changed, the ID remains the same. In order to verify the ID, in the SSMS go to the MultidimensionalProject6, right click and select the "Properties" option.

Verify the ID value. In this example the "Name" and "ID" are the same.

Now, change the DatabaseID from "AdventureWorksDW2012Multidimensional-EE" to "MultidimensionalProject6". Since I added a linked dimension, I am also changing the "Dimension Name" and "ID", but if you did not try the linked dimension option, then this step is not necessary. Press F5 to create the dimension by executing the code.

You may have the following error after pressing F5 to execute the code.

The problem occurs when the Data Source View ID from Project A and Project B are different. In order to see the Data Source View ID, right click in the Data Source View folder and select "Properties" as shown below.

Verify the ID.

Now in the XMLA script, change the DatasourceViewID from "AdventureWorks" to the name of the "Project B" and press F5 again to execute the code.

If you press F5, the new dimension will be displayed successfully.

Add an existing item to a SQL Server Analysis Server Solution

The last solution is to go to the Solution in SSDT or BIDS and add an existing item and select the dimension. I am not going to explain in detail this last solution because it is a well- known solution and it was already explained by other people here.

Next Steps

• In this tip we outlined three different methods to add an existing dimension in another SSAS solution.  Work through the examples in your environment and see which one makes most sense in your environment.
• For more information, refer to these links:
  • http://msdn.microsoft.com/en-us/library/ms174890(v=sql.110).aspx
  • http://www.dimodelo.com/blog/2011/how-to-add-an-existing-dimension-to-a-ssas-project/
  • http://msdn.microsoft.com/en-us/library/ms187152(v=sql.110).aspx
  • Check out all of the SQL Server Analysis Services Tips

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips