Linked dimension performance issue

Question

I am working with 2 star schema data warehouses, each data warehouse contains a fact table and the dimensions tables are located in separate databases (one database used by both data warehouses).

I created a multidimensional analysis project for each data warehouse:

• In the first project, I defined the dimensions and deployed the Analysis database and I am able to browse the cube with no problems from Management Studio.
• In the Second project, I defined Linked dimensions and used the deployed dimensions from the first analysis database.

When trying to browse the second cube everything is working fine, but when I tried to browse the dimension or to add a filter when browsing the cube the management studio is not responding. After many hours it returns the following error message:

Error occurred retrieving child nodes: The Messages element at line, (namespace urn:schemas-microsoft-com: xml-analysis: exception) cannot appear under Envelope/Body/ExecuteResponse/return/SubCube)

When searching for this issue, i found some article mentioning that using Linked dimensions are not recommended when analysis databases are located on different servers. But in my case the data warehouses and the analysis databases are on the same server.

Also i tried to run the same filter logic using MDX query using FILTER() with no luck. The MDX query syntax is similar to:

SELECT ([Dimension2].[---].[---], [MeasureGroup].[Measure]) ON COLUMNS,
        FILTER([Dimension1].[---].[---],[Dimension1].[---].[---].CurrentMember.Name = "FilterValue") ON ROWS
FROM [AnalysisCube]

Note that: The dimensions contains more than 4 GB and CompatibilityLevel is set to 1100.

Any suggestions?

Balanchine

Why not just put both cubes in the same database? Then they can share dimensions without needing Linked Dimensions. – Ulster

@DavidBrowne-Microsoft Did you mean to add a new measure group for the second warehouse – Balanchine

@DavidBrowne-Microsoft the number of warehouses may increase with time, and for management purposes we decided to build separate analysis databases. – Balanchine

On the flip side, if each database has its own copy of the dimension, they don't have to use all the same attribute hierarchies. – Ulster

@DavidBrowne-Microsoft please check the question update – Balanchine

I don’t know. It’s been a while since I’ve built a multidimensional model, and I’ve never used the Linked Dimension feature. You may need to open a support case. – Ulster

@DavidBrowne-Microsoft i found something interesting, check the answer below – Balanchine

Answer 1

Based on the following Microsoft reference:

• Exam Ref 70-767 Implementing a SQL Data Warehouse

SSAS allows you to add a linked dimension to that other multidimensional database so that you only have one dimension to build and maintain. However, the use of linked dimensions is not considered to be best practice in SSAS development because it can produce performance problems.

Another way to think about building once and reusing your development work is to save the .dim files in source control. You can then require new multidimensional database projects to add .dim files from source control rather than build a new dimension directly. That way, you can maintain the design in a central location and benefit from reusability without introducing potential performance issues.

From the information above, it looks like using Linked dimensions is not recommended from performance perspective.

Balanchine