Multiple Cubes and One Report

Hello Experts,

I have two SSAS cubes SalesFact and InventoryFact. I would like to create a report that lists both sales and inventory values within the same report.

I think I should make some modifications on my MDX statement. But I am very new at SSAS and SSRS platform.

For example my InventortyFact MDX is like below:

 SELECT NON EMPTY { [Measures].[Quantity] } ON COLUMNS, NON EMPTY { ([DimWarehousesMAX].[Store ID].[Store ID].ALLMEMBERS * [DimProductMAX].[Season ID].[Season ID].ALLMEMBERS * [DimProductMAX].[Group ID].[Group ID].ALLMEMBERS * [DimProductMAX].[Department ID].[Department ID].ALLMEMBERS * [DimProductMAX].[Class ID].[Class ID].ALLMEMBERS * [DimProductMAX].[Style Code].[Style Code].ALLMEMBERS * [DimDate].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HCInventory_MAX] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

and my salesFact MDX is like this one:

 SELECT NON EMPTY { [Measures].[Sale Unit], [Measures].[Sales Value] } ON COLUMNS, NON EMPTY { ([Dim Warehouses MAX].[Store ID].[Store ID].ALLMEMBERS * [Dim Product MAX].[Season ID].[Season ID].ALLMEMBERS * [Dim Product MAX].[Group ID].[Group ID].ALLMEMBERS * [Dim Product MAX].[Department ID].[Department ID].ALLMEMBERS * [Dim Product MAX].[Class ID].[Class ID].ALLMEMBERS * [Dim Product MAX].[Style Code].[Style Code].ALLMEMBERS * [Dim Date].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HCSALES_MAX] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

How can I combine these two cube resultset as one?

Any idea?

King Regards

You can only query one cube in an MDX statement. But if you are showing these results in SSRS then you can place two tables on the report, one for each query.

I had a similar situation.  Dustin responded and suggested Lookup Cube as the answer.

Here is the forum

http://www.bidn.com/forums/microsoft-business-intelligence/reporting-services/211/query-results-from-2-cubes-in-one-report

Thank you very much for your great comments.

I have solved this issue by using Dimension Usage tab of the BIDS. I have added the measures which I will use in reporting service within a same cube. And it works :)

I have also tried to apply your MDX statement on my application. I think there is something wrong with query performance? Am I right?

You really want to avoid using the LookupCube function if at all possible. Adding the measure which you need to your cube is the best option.