Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MDX Query filtering on two different dimensions using 'OR' operator Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 10:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 292, Visits: 757
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!
Post #1536893
Posted Friday, January 31, 2014 11:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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?
Post #1536898
Posted Friday, January 31, 2014 11:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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.
Post #1536901
Posted Friday, January 31, 2014 11:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 292, Visits: 757
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.
Post #1536902
Posted Friday, January 31, 2014 11:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 292, Visits: 757
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?
Post #1536907
Posted Friday, January 31, 2014 11:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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.
Post #1536911
Posted Friday, January 31, 2014 11:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 292, Visits: 757
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.
Post #1536913
Posted Friday, January 31, 2014 11:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 292, Visits: 757
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.
Post #1536914
Posted Friday, January 31, 2014 12:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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]


Post #1536926
Posted Friday, January 31, 2014 4:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 292, Visits: 757
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.
Post #1537021
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse