mssqltips logo

Controlling the use of linked objects in SQL Server Analysis Services

By:   |   Updated: 2011-04-05   |   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.

connect your ssas instance in ssms, 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.

    set the value of LinkInsideInstanceEnabled property to false

  4. Now try to deploy the cube and the deployment should fail with the error shown in the below screenshot.

    when you deploy the cube, the deployment should fail

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.


Last Updated: 2011-04-05


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools