Solved

MDX - Multiple Cubes and One Report

Posted on 2010-09-06
2
745 Views
Last Modified: 2016-02-15
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
0
Comment
Question by:innocent1973
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 500 total points
ID: 33615812
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
 

Author Comment

by:innocent1973
ID: 33615849
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

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question