ROLAP not working - how to design the cube/DSV for ROLAP?

Question

I'm trying to configure storage mode ROLAP for a partition in an existing SSAS cube. The cube is a little messy in that the measure group is defined by a named query (as opposed to a table) and the dimensions are defined in several different data source views (DSV).

This is the error message I get when querying the cube with mdx:

Executing the query ...
Server: The operation has been cancelled.
Errors in the high-level relational engine. The 'dbo_dim_account' table that is required for a join cannot be reached based on the relationships in the data source view.
Execution complete

Note that MOLAP storage mode with proactive caching works fine. This problem occurs only when storage mode is ROLAP or HOLAP.

Also, I have tried to add the tables of all dimensions to the DSV of the cube in question but that doesn't seem to help.

Any ideas?

Answer 1

Not an expert here, but you could try importing the AS DB in Visual Studio.NET - "Import Analysis Services database" in the New Project... dialog.

Once in there, you can see the table schemas for the Data Source View (which is where the relational tables are defined that the cubes are extracted from). Next, look to make sure the "dbo_dim_account" table is there and that your fact table is related to it.

It may be that a dimension and fact have to be in the same DSV for the relation to work?

Also, maybe the SSAS flight recorder or Application log would have more issues?