Linking date dimension from two different cubes

4
votes

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.

enter image description here

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.

1
ssasmdxdashboardcubedimension
Why not append the fact rows from all departments into one tall fact table instead of trying to put it into two fact tables? Why do you not want a single cube? – GregGalloway
Did you manage to find a solution Merin? I have a similar problem where I my Dates have different names across different cubes (though use the same date hierarchy) and I need to link them all using a date filter on my dashboard. – TJH
I simplified it by giving the same dimension name across all cubes. – Merin Nakarmi

1 Answers

2
votes

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: VirtualCubes_1Initial

Virtual cube creation: VirtualCubes_2CreateCube

VirtualCubes_3Result

The major disadvantage of this method:

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

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

Hope this information helps.