MDX Query filtering on two different dimensions using 'OR' operator

  • I need help with a simple MDX query.

    Suppose I have a measure group linked to a fact table called Flights, which records air traffic information.

    There are some attributes in that fact table:

    FactFlights

    DestinationCity

    ArrivalCity

    FlightTime (how many minutes to get from Destination to Arrival)

    ...

    Then I have two cube dimensions, one linked to DestinationCity, and one linked to ArrivalCity.

    DimDestinationCity

    DimArrivalCity

    Those dimensions have a couple of attributes, including City, with memebers like 'Seattle', 'New York', 'Los Angeles', etc.

    I need to write an MDX query that returns data from the FactFlights measure gropu where a city appears in EITHER the DestinationCity OR the ArrivalCity.

    In TSQL this would be trivial.

    Something like:

    SELECT

    DestinationCity,

    ArrivalCity,

    FlightTime

    FROM Flights

    WHERE

    DestinationCity = 'Seattle'

    OR

    ArrivalCity = 'Seattle'

    Any help/guidance would be greatly appreciated.

    Thanks!

  • Do you need to display one sum of flight time for the whole set of 2 members collectively; or, do you need to group the data in a different way?

  • Off topic a bit:

    Also, from a design perspective, I would have only one PHYSICAL dimension for the airport and use role playing dimensions.....one for the departure city and one for the arrival city. I cannot tell if that's what you did here on not.

  • sneumersky (1/31/2014)


    Do you need to display one sum of flight time for the whole set of 2 members collectively; or, do you need to group the data in a different way?

    Thanks very much for the reply.

    It's a detail/line report, so I'm not too concerned about the grouping for now.

  • sneumersky (1/31/2014)


    Off topic a bit:

    Also, from a design perspective, I would have only one PHYSICAL dimension for the airport and use role playing dimensions.....one for the departure city and one for the arrival city. I cannot tell if that's what you did here on not.

    As far as I can tell, there is only one physical dimension (DimCity).

    How can I tell if these are 'role playing' dimensions?

  • If you just wanted one cell to display as an answer, I would put the destination city member of Seattle and the departure city member of Seattle in the WHERE clause and just ask for the FlightTime aggregate on columns.

    It all depends on what you want as row and column headers.

  • sneumersky (1/31/2014)


    If you just wanted one cell to display as an answer, I would put the destination city member of Seattle and the departure city member of Seattle in the WHERE clause and just ask for the FlightTime aggregate on columns.

    It all depends on what you want as row and column headers.

    Good point.

    Assume I want the following, as column headers:

    Date

    Departure City

    Arrival City

    Time In Flight

    As I mentioned, it's sort of a line/detail report - there's not much analysis going on.

    Thanks for the continued help.

  • sneumersky (1/31/2014)


    If you just wanted one cell to display as an answer, I would put the destination city member of Seattle and the departure city member of Seattle in the WHERE clause and just ask for the FlightTime aggregate on columns.

    It all depends on what you want as row and column headers.

    Good point.

    Assume I want the following, as column headers:

    Date

    Departure City

    Arrival City

    Time In Flight

    As I mentioned, it's sort of a line/detail report - there's not much analysis going on.

    Thanks for the continued help.

  • Defeating the purpose of SSAS IMO.....but try this:

    In pseudocode...

    NonEmpty( (Day Level of Date Dimension) * (Seattle Member of Departure City) * (Seattle Member Of Arrival City)

    )

    ON ROWS

    FlightTime

    ON COLUMNS

    FROM [YOUR CUBE]

  • sneumersky (1/31/2014)


    Defeating the purpose of SSAS IMO.....but try this:

    In pseudocode...

    NonEmpty( (Day Level of Date Dimension) * (Seattle Member of Departure City) * (Seattle Member Of Arrival City)

    )

    ON ROWS

    FlightTime

    ON COLUMNS

    FROM [YOUR CUBE]

    Thanks very much. I will give this a try.

  • In case anyone has a similar problem: the solution that ended up working was a combination of the UNION and CROSSJOIN functions in the FROM clause of my MDX. Importantly, I had to include the AllMembers member for both of my role-playing dimensions (my 'To' and 'From' dimensions).

    The following references were helpful:

    MSDN Forum (similar question):

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/00068834-c11b-4d3a-9cb7-498655c51cdc/how-to-union-two-mdx-queries-with-two-different-dimension?forum=sqlanalysisservices

    Technet Reference: MDX UNION:

    http://technet.microsoft.com/en-us/library/ms146030.aspx

    Technet Reference: MDX CROSSJOIN:

    http://technet.microsoft.com/en-us/library/ms144816.aspx

    The pseudo-code looks like this:

    SELECT NON EMPTY

    {

    [Measures].[Value1],

    [Measures].[Value2],

    ...

    } ON COLUMNS,

    NON EMPTY

    {

    [Dim1].[Dim1Attribute].AllMembers *

    [Dim2].[Dim2Attribute].AllMembers *

    ...

    } ON ROWS

    FROM

    (

    SELECT UNION

    (

    CROSSJOIN(

    [DimFromCity].[City].&[Seattle],

    [DimToCity].[City].AllMembers

    ),

    CROSSJOIN (

    [DimFromCity].[City].AllMembers,

    [DimToCity].[City].&[Seattle]

    )

    )

    ON COLUMNS

    FROM [MyCube])

  • One thing I forgot to mention (because I didn't want to force you into that path).....a drillthrough action can get you the most detailed cube data. Are you familiar with actions and drillthrough?

  • sneumersky (2/3/2014)


    One thing I forgot to mention (because I didn't want to force you into that path).....a drillthrough action can get you the most detailed cube data. Are you familiar with actions and drillthrough?

    I know 'of' them, but I've never implemented. My experience with SSAS is limited.

    Thanks for the tip, though - I will look into them.

  • Solid Quality Mentors has a great illustration on how to do it in pure MDX code (as well as doing it other ways too)....

    http://www.solidq.com/sqj/Pages/Business_Intelligence/SSAS-Cube-Exploration-Digging-Through-the-Details-with-Drillthrough.aspx

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply