Controlling the use of linked objects in SQL Server Analysis Services

By: Siddharth Mehta   |   Comments   |   Related: > Analysis Services Development

Problem

Linked objects in SSAS should be used very carefully, as the object resides in the original cube and just a link is created in the target cube. The risk is that when multiple teams are working on different projects, people can take a shortcut for faster development and create links out of other cubes. Linked objects has a limitation that its definition cannot be modified in the target cube design, and this can be a big bottleneck if this is not the desired design. In an environment where multiple cubes are developed in the same solution, allowing free use of linked objects opens up a risk of creating a spider web of linked objects across cubes. In this tip we will look at how to control the use of linked objects in a cube.

Solution

Usage of linked objects in SSAS can have three different use cases:

1. Adding a linked object from the base SSAS instance to a target SSAS instance
2. Allowing to use objects from your base SSAS instance as linked objects to another SSAS instance
3. Using linked objects within the same SSAS instance

At the SSAS instance level, there are three different properties exactly for this purpose. This is shown in the below screenshot. LinkInsideInstanceEnabled is the only property that is true by default and the rest are set to false. To access this screen, connect to your SSAS instance in SSMS, right click on the instance and select properties.

Let's say we intend to implement a constraint that linked objects should not be used on a particular SSAS instance.

As an example, follow these steps:

1. Connect to the SSAS instance using Management Studio, right-click on the instance and select properties and set the value of LinkInsideInstanceEnabled property to "false", as shown in the above screenshot.
2. Open the AdventureWorks sample project that ships with SQL Server and install with two different database names, so that we have two different cubes. Our intention is to add a linked object from one cube to another cube.
3. Open one of the solutions and try to add a linked object by right-clicking on the cube and selecting "New Linked Object" as shown in the below screenshot. This option activates the Linked Object Wizard. Complete this wizard and add any object from the other cube. For the purpose of this demo, I have added the Geography dimension from the other cube.
   
   
   
   
4. Now try to deploy the cube and the deployment should fail with the error shown in the below screenshot.
   
   
   
   

So by using the above mentioned properties, you can control the use of linked objects at an instance level.

Next Steps

• Try opening a linked dimension in the dimension designer tab and check the results.
• Change the deployment server edition property of the solution to "Standard" and try deploying a linked object and check the results.
• Read these other SSAS tips.

About the author

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

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

View all my tips