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?

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.