MDX query not working

Question

I am trying to perform the between two dates (inclusive) in mdx. I have two fact tables and one mapping table.

Fact_TableA 
  TableAId,
  ValueA,
  Date_FK

Fact_TableB
  TableBId,
  ValueB,
  Date_FK

Fact_MappingTable
 TableAId,
 TableBId

Fact_MappingTable has many to many relationship with Fact_TableA and Fact_TableB. I have written below query

 SELECT 
 NON EMPTY 
 { 
    [Measures].[ValueA], 
    [Measures].[ValueB] 
 } ON COLUMNS, 
 NON EMPTY 
 { 
    (
     [Fact Table A].[Column AID].[Column AID].ALLMEMBERS * 
     [Fact Table B].[Date FK].[Date FK].ALLMEMBERS 
     ) 
 } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
 (
    select ([Fact Mapping Table].[Date FK].&[2014-12-10T00:00:00] : [Fact Mapping Table].[Date FK].&[2015-01-01T00:00:00]) ON COLUMNS FROM [DW Testing] 
 )

This query is performing the

Select 
   Fact_TableA.ValueA
  ,Fact_TableB.ValueB 
from 
   Fact_TableA 
   left outer join Fact_MappingTable on 
     Fact_TableA.TableAId = Fact_MappingTable.TableAId 
   left outer join Fact_TableB on 
     Fact_TableB.TableBId = Fact_MappingTable.TableBId
where Date_FK between '2015-01-01' and '2015-01-01'

But my requirement is

 Select 
   Fact_TableA.ValueA
  ,Fact_TableB.ValueB 
 from 
    Fact_TableA 
    left outer join Fact_MappingTable on 
      Fact_TableA.TableAId = Fact_MappingTable.TableAId and 
      Fact_MappingTable.DateM_FK between '2015-01-01' and '2015-01-10'
    left outer join Fact_TableB on 
    Fact_TableB.TableBId = Fact_MappingTable.TableBId
  where Date_FK between '2015-01-01' and '2015-01-10'

I have tried performing the Subselect and Where slicer but is not working correctly. Any suggestion how this could be done in MDX? How dimension usage should have relationships among dimensions and fact table to perform the filtering. Attached the screenshots

I think you may have setup the many-to-many dimension incorrectly in the Dimension Usage tab. Can you provide a screenshot of the tab and of the many-to-many relationship dialog? — GregGalloway, Sep 19 '15 at 16:15

whytheq · Answer 1 · 2015-09-23T22:29:44.410

Maybe this:

 SELECT 
 NON EMPTY 
 { 
    [Measures].[ValueA], 
    [Measures].[ValueB] 
 } ON COLUMNS, 
 NON EMPTY 
 { 
     EXISTS(
        [Fact Table A].[Column AID].[Column AID].ALLMEMBERS
       ,{[Fact Mapping Table].[Date FK].&[2014-12-10T00:00:00] 
          : 
         [Fact Mapping Table].[Date FK].&[2015-01-01T00:00:00])}
     ) 
     * [Fact Table B].[Date FK].[Date FK].ALLMEMBERS 
 } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
 (
    select ([Fact Mapping Table].[Date FK].&[2014-12-10T00:00:00] : [Fact Mapping Table].[Date FK].&[2015-01-01T00:00:00]) ON COLUMNS FROM [DW Testing] 
 )

Try swapping the Exists function for the NonEmpty function:

SELECT 
 NON EMPTY 
 { 
    [Measures].[ValueA], 
    [Measures].[ValueB] 
 } ON COLUMNS, 
 NON EMPTY 
 { 
     NonEmpty(
        [Fact Table A].[Column AID].[Column AID].ALLMEMBERS
       ,{[Fact Mapping Table].[Date FK].&[2014-12-10T00:00:00] 
          : 
         [Fact Mapping Table].[Date FK].&[2015-01-01T00:00:00])}
     ) 
     * [Fact Table B].[Date FK].[Date FK].ALLMEMBERS 
 } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
 (
    select ([Fact Mapping Table].[Date FK].&[2014-12-10T00:00:00] : [Fact Mapping Table].[Date FK].&[2015-01-01T00:00:00]) ON COLUMNS FROM [DW Testing] 
 )

this query is not filtering the values for [Fact Mapping Table].[Date FK].&[2014-12-10T00:00:00] : [Fact Mapping Table].[Date FK].&[2015-01-01T00:00:00] date range — 107, Sep 21 '15 at 14:08

MDX query not working

1 Answers1