January 31, 2014 at 10:52 am
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!
January 31, 2014 at 11:06 am
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?
January 31, 2014 at 11:08 am
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.
January 31, 2014 at 11:09 am
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.
January 31, 2014 at 11:11 am
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?
January 31, 2014 at 11:15 am
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.
January 31, 2014 at 11:19 am
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.
January 31, 2014 at 11:23 am
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.
January 31, 2014 at 12:09 pm
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]
January 31, 2014 at 4:04 pm
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.
February 1, 2014 at 1:21 pm
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):
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])
February 3, 2014 at 8:16 am
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?
February 3, 2014 at 8:32 am
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.
February 3, 2014 at 9:43 am
Solid Quality Mentors has a great illustration on how to do it in pure MDX code (as well as doing it other ways too)....
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply