The following Cubes are defined within the schema:
[MSR] Contract Value
[MSR] Qty Events
And one virtual cube:
Events vs Contracts
[DIM] [from Contract] Contract
[DIM] [from Events] Event
[MSR] [from Contract] Contract Value
[MSR] [from Events] Qty Events
[CME] [Valid Contract Value] as ValidMeasure( [Measures].[Contract Value] )
There's no dimension table. For this scenario they've built two fact tables only - with local dimensions.
The issue happens when using the virtual cube and following these steps:
1. Drag Dim Contract - shows contracts;
2. Drag Msr [Valid Contract Value] - shows contract values;
3. Drag Msr [Qty Events] - shows the quantity of events for each contract;
4. Drag Dim Event - shows all the events for each contract and shows the Contract Value over the toplevel of the Dim Event;
5. take [Qty Events] out of the report canvas - it simply gives back an empty canvas stating there is not data for this report.
The resulting MDX shows: the usage of a nonemptycrossjoin
SET [*NATIVE_CJ_SET] AS 'NONEMPTYCROSSJOIN([*BASE_MEMBERS__Contract_],[*BASE_MEMBERS__Event_])'
SET [*BASE_MEMBERS__Event_] AS '[Event].[Event].MEMBERS'
SET [*BASE_MEMBERS__Measures_] AS '
SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET],
SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],[Contract].CURRENTMEMBER.ORDERKEY,BASC,[Event].CURRENTMEMBER.ORDERKEY,BASC)'
SET [*BASE_MEMBERS__Contract_] AS '[Contract].[Contract].MEMBERS'
MEMBER [Measures].[*FORMATTED_MEASURE_0] AS '[Measures].[Valid Contract Value]', FORMAT_STRING = '#,##0.00', SOLVE_ORDER=500
[*BASE_MEMBERS__Measures_] ON COLUMNS
[*SORTED_ROW_AXIS] ON ROWS
FROM [Events vs Contracts]
The resulting SQL shows a bad constructed query:
SqlTupleReader.readTuples [[Contract].[Contract], [Event].[Event]]: done executing sql [select 0 as "c0" from "case_contract" as "case_contract" where 1 = 0],
No columns selected plus a conditional that will never be true.
If we take the MDX and change the nonemptycrossjoin to crossjoin we get the expected results using Analyzer's MDX utility.