MDX - 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
innocent1973Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Tim HumphriesConnect With a Mentor DirectorCommented:
Hi,

I see you have the same dimension references in both statements - why do you have separate cubes? You could simply have multiple measure groups in the same cube and your problem goes away.
There aren't any good ways to cross link two cubes in the same query - linked measure groups and the LookupCubeFunction should be avoided.

Tim
0
 
innocent1973Author Commented:
thank you very much for your reply. I have added a new measure within the same cube at SSAS. And I saw this new measure on the SSRS platform.
I know that this may be a stupid question but I am really very new at BI area.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.