Sometimes you might have two cubes with one or more dimensions in common, but you don't necessarily have a virtual cube that joins the dimensions together. This situation might occur because the common dimensions aren't exactly the same, although most of the members match. The following MDX puzzle simulates this problem by using the FoodMart 2000 Sales cube and Warehouse cube.
Write an MDX query that returns both the Unit Sales and the Warehouse Cost for all Product Departments for both 1997 and 1998. Don't cheat and use the Warehouse and Sales virtual cube. Hint: You'll need SQL Server 2000 Analysis Services to write this query.
WITH member \[Measures\].\[Warehouse Cost\] AS 'LookupCube("\[Warehouse\]",
"(\[Measures\].\[WarehouseCost\],\[Time\].\["+Time.CurrentMember.Name+"\],
\[Product\]. \["+Product.CurrentMember.Name+"\])")'
SELECT CrossJoin(Time.Year.Members, \{\[Unit Sales\],\[Warehouse Cost\]\}) ON columns,
Product.\[Product Department\].Members ON rows
FROM Sales
This query returns Warehouse Cost from the Warehouse cube by using the LookupCube function that is new in Analysis Services. The tricky part of this query is building the names of the dimension members for the Time and Product dimensions that the LookupCube function uses. Because the CurrentMember.Name expression in MDX doesn't yield a fully qualified name (one with brackets and a dimension name), I built it by concatenating the dimension name and the brackets with CurrentMember.Name.
