Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MDX - Multiple Cubes and One Report

Posted on 2010-09-06
2
Medium Priority
?
756 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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