Query results from 2 cubes in one report

I have 2 cubes, one relating to sales facts with an employee dimension, and another relating to sales goals facts with an employee dimension.  How do I go about creating a query and/or report where I can show sales by employee with both the sales quantity and their target goal?  Thanks for all help.

I'm not exactly the master of all things MDX, but you could try using the LookUpCube Function. Here's a couple links that you may find useful:

MDXpert.com is a site I've found useful.

And here's the MSDN link to the LookupCube function.

In the following example, I'm using the Adventure Works and Mined Customers cubes.

WITH MEMBER [Measures].[Lookup Internet Order Count] AS

LOOKUPCUBE("Mined Customers", 
"([Measures].[Internet Order Count],[Customer].[Customer Geography].[Country].&[" + [Customer].[Customer Geography].CurrentMember.Name + "])")


SELECT [Customer].[Customer Geography].[Country] on COLUMNS,

{[Measures].[Internet Sales Amount], [Measures].[Lookup Internet Order Count]} ON ROWS

FROM [ADVENTURE WORKS]

The important thing to remember when using the LOOKUPCUBE function is that you have to specify the context of the query. So for the second item of the LOOKUPCUBE function, we have to construct a string that specifies exactly the context of the query. In this case, the string must look exactly like:

"([Measures].[Internet Order Count],[Customer].[Customer Geography].[Country].&[" + the current country name + "])"

To get the country name, I use the expression: [Customer].[Customer Geography].CurrentMember.Name

In this way I am able to view measures in seperate cubes in the results of a single MDX query. This example was done using the Adventure Works cube and the Mined Customers cube that is included with Adventure Works. Please let me know if you have any other questions.

Dustin,

Thanks for the info and the links.  I will check them out.

Daniel

Did this resolve your issue or were you able to solve it another way? Also, I wrote a blog post that covers use the LOOKUPCUBE function that includes a little bit more detail than in my forum post on this topic: http://www.bidn.com/blogs/DustinRyan/ssis/798/retrieving-data-from-multiple-cubes-in-an-mdx-query-using-the-lookupcube-function