Copy SQL Server Analysis Services Dimensions

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

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:

SQL Server Analysis Services Dimensions

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

Existing SQL Server Analysis Services Dimension Structures

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.

Create a new SQL Server Analysis Services Linked Dimension

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.

SQL Server Data Source Wizard for a Linked Dimension

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

Select Objects in the wizard

Finish the wizard.

Linked Object Wizard - Completing 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.

 
Dimension structure in SQL Server Data Tools

You can however see the translation section as shown below.

Translation section for the dimension

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

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

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".

SSAS Properties

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

SQL Server Analysis Services - Show advanced properties

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

SQL Server Analysis Services a listing of all Advanced Properties

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.

Configuration path of Microsoft SQL Server\MSAS\OLAP\Config for the msmdsrv file

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

msmdsrv configuration to enable the  LinkFromOtherInstances property

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

Restart the SQL Server Analysis Services service

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

Process a SQL Server Analysis Services 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.

Generate XMLA code in SQL Server Management Studio

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.

Change the xmla code to have the correct 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.

SQL Server Analysis Services Project Properties

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

Determine the DatabaseID in the database properties

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.

Update the dimension name in the XLMA code

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

Errors in the metadata manager

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.

SQL Server Analysis Services Data Source Views properties

Verify the ID.

Data source view properties to determine 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.

xmla code for the Data Source View ID

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

Verify the dimension is added in the SQL Server Analysis Services Project

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



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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



Comments For This Article




Wednesday, September 30, 2020 - 8:25:42 AM - kevin Back To Top (86559)
Why not copy the cube?

Wednesday, December 19, 2012 - 11:21:57 PM - Daniel Calbimonte Back To Top (21040)

It looks like a problem with the SQL Server edition. The edition used for the demo is the enterprise edition


Tuesday, December 18, 2012 - 2:16:09 AM - Rakesh Sharma Back To Top (20994)

Hi Daniel,

I did same steps as guided, i have changed "LinkFromOtherInstances property to "true"" as well by doing this i am able to process the Date dimension but getting below error while processing the cube.

Please help.


 
   
      SSASTESTCUBE
      Date
   
    ProcessFull
    UseExisting
 

 Processing Dimension 'Date' completed.
  Start time: 12/18/2012 12:36:55 PM; End time: 12/18/2012 12:36:55 PM; Duration: 0:00:00
  Processing Dimension Attribute '(All)' completed.
   Start time: 12/18/2012 12:36:55 PM; End time: 12/18/2012 12:36:55 PM; Duration: 0:00:00
  Processing Dimension Attribute 'Calendar Due Date Calculations' completed.
   Start time: 12/18/2012 12:36:55 PM; End time: 12/18/2012 12:36:55 PM; Duration: 0:00:00
  Processing Dimension Attribute 'Calendar Year' completed. 6 rows have been read.
   Start time: 12/18/2012 12:36:55 PM; End time: 12/18/2012 12:36:55 PM; Duration: 0:00:00
   SQL queries 1
    SELECT
  DISTINCT
 [dbo_DimDate].[CalendarYear] AS [dbo_DimDateCalendarYear0_0]
  FROM [dbo].[DimDate] AS [dbo_DimDate]
  Processing Dimension Attribute 'Fiscal Year' completed. 6 rows have been read.
   Start time: 12/18/2012 12:36:55 PM; End time: 12/18/2012 12:36:55 PM; Duration: 0:00:00
   SQL queries 1
    SELECT
  DISTINCT
 [dbo_DimDate].[FiscalYear] AS [dbo_DimDateFiscalYear0_0]
  FROM [dbo].[DimDate] AS [dbo_DimDate]
Errors and Warnings from Response
 Errors in the high-level relational engine. The data source view does not contain a definition for the 'Calendar_x0020_Due_x0020_Date_x0020_Calculations' column in the 'dbo_DimDate' table or view.
 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Date', Name of 'Date' was being processed.
 Errors in the OLAP storage engine: An error occurred while the 'Calendar Due Date Calculations' attribute of the 'Date' dimension from the 'SSASTESTCUBE' database was being processed.
 Internal error: The operation terminated unsuccessfully.
 Server: The operation has been cancelled.

Error 4 Errors related to feature availability and configuration: The 'Linking inside same server instance.' feature is not included in the 'Standard Edition' SKU.  0 0 

Thanks in Advance,

Rakesh Sharma


Monday, December 17, 2012 - 8:49:57 PM - Daniel Calbimonte Back To Top (20991)

Sure, I will


Monday, December 17, 2012 - 11:01:39 AM - RL Back To Top (20986)

This is pretty interesting.  You need to have more tips about XMLA and really explain the code.















get free sql tips
agree to terms