Linking date dimension from two different cubes

Question

I have two cubes

1. Computer Engineering
2. Environmental Science.

Both cubes have a same Date dimension.

I have two scorecards, each for Computer Engineering and Environmental Science as shown in following figure. I would like to have a result as shown in the figure.

I have a drop down filter which uses Date dimension from Computer Engineering cube. This filter works perfectly for filtering KPIs of Computer Engineering Scorecard since Date dimension and measures are from same cube. But it is not able to filter KPIs from Environmental Science cube because the Date dimension in filter and measures in KPIs are from two different cubes.

I have tried following ideas.

1. LinkMember - LinkMember MDX function can get equivalent member from different dimension but within the same cube. It is limited to only one cube.
2. LookupCube - By using a LookupCube MDX function on first cube, I am able to get the measures of second cube on it. But these measures don't pivot over Date dimension from first cube.
3. Linked Objects - I added measures from second cube to the first cube as linked objects, but it did not create dimension usage relationship with Date dimension from first cube. Thus unviable.

I don't want to create a gigantic single cube.

Please help me suggest a solution or work around. Thanks in advance.

Answer 1

I'm wondering why Linked Objects didn't help. Let's create everything step by step.

Here is structures of two cubes, which I used:

Virtual cube creation:

The major disadvantage of this method:

You can't just add a measure to one of cubes, which are used in a Virtual cube.

You have to re-link the cube to avoid this error.

Hope this information helps.