none
One table for two dimensions

    Question

  • Hello everyone,

    First of all, sorry if this question is already answered in this forum, I browsed around and didn't find any solution to my problem.

    I am quite new to data warehouse modeling with SSAS and I would like to design a basic cube about transportation informations.

    What I have in my data source is a collection of flights information (passengers, schedule, ...) and each flight is linked to an Origin Airport and a Destination Airport. I then created a cube with a fact table for flight information and two dimension Departure and Origin for the Airports.

    My question is : How can I retrieve the total number of passengers arriving and departing from a specific airport?

    I have found a way to express it in MDX but it is a weird query. What is the typical MDX solution for those kind of queries? Does this value have to be computed during the ETL phase? In the fact table?

    Thank you,

    Orlando

    Monday, July 15, 2013 8:45 AM

Answers

  • Hi Orlando,

    Thanks for your post.

    Could you please share you MDX query in this case? The ETL process focus on "Extact", "Transform" and "Load". The OLAP Engine Server components provide process calculations functionality. For more information, please see:
    http://technet.microsoft.com/en-us/library/ms174776.aspx

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Monday, July 22, 2013 3:34 AM
    Moderator
  • Ok I found the solution to my problem on this thread : http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6014c375-31ce-4cf4-bc61-466a6c73552d/join-2-different-dimensions-in-mdx

    The solution is to use the LinkMember() and is given by Deepak.

    Thank you very much for your help.

    Here is the query that gives exactly what I want :

    WITH
    	MEMBER [Measures].[Incoming Passengers] AS
    		( [Measures].[Passengers], [Origin].[Airport].[All],
    			LinkMember( [Origin].[Airport].CurrentMember, [Destination].[Airport]
    			)
    		)
    	MEMBER [Measures].[Outgoing Passengers] AS
    		( [Origin].[Airport].CurrentMember, [Measures].[Passengers])
    	MEMBER [Measures].[Total Passengers] AS
    		( [Measures].[Incoming Passengers]
    			 +
    		 [Measures].[Outgoing Passengers] )
    SELECT
    NON EMPTY { ([Origin].[Airport].Children) } ON ROWS,
    { [Measures].[Outgoing Passengers],[Measures].[Incoming Passengers],[Measures].[Total Passengers] } ON COLUMNS
    FROM [Tran Stat V3 - Flights]

    Best regards,

    Orlando

    Monday, August 05, 2013 9:42 AM

All replies

  • Hi Orlando,

    Thanks for your post.

    Could you please share you MDX query in this case? The ETL process focus on "Extact", "Transform" and "Load". The OLAP Engine Server components provide process calculations functionality. For more information, please see:
    http://technet.microsoft.com/en-us/library/ms174776.aspx

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Monday, July 22, 2013 3:34 AM
    Moderator
  • Hi Elvis,

    Thank you for your answer.

    Here is my MDX query :

    WITH
         MEMBER [Measures].[Total Passengers] AS
         ( [Origin].[Airport].CurrentMember,[Destination].[Airport].[All],[Measures].[Passengers] ) -- IN
              +
         ( [Origin].[Airport].[All],[Destination].[Airport].CurrentMember,[Measures].[Passengers] ) -- OUT
    SELECT
    {
    [Measures].[Total Passengers]
    } ON COLUMNS,
    {
    Filter(
            {
              ( [Origin].[Airport].Children,[Destination].[Airport].Children) }
              ,[Destination].[Airport].CurrentMember.Name=[Origin].[Airport].CurrentMember.Name
            )
    } ON ROWS
    FROM [Tran Stat V3 - Flights]

    I think that what I need is a way to merge those two dimensions (Origin and Destination) in order to avoid this Filter from the name of the member. Am I right?

    Regards,

    Orlando

    Monday, August 05, 2013 9:31 AM
  • Ok I found the solution to my problem on this thread : http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6014c375-31ce-4cf4-bc61-466a6c73552d/join-2-different-dimensions-in-mdx

    The solution is to use the LinkMember() and is given by Deepak.

    Thank you very much for your help.

    Here is the query that gives exactly what I want :

    WITH
    	MEMBER [Measures].[Incoming Passengers] AS
    		( [Measures].[Passengers], [Origin].[Airport].[All],
    			LinkMember( [Origin].[Airport].CurrentMember, [Destination].[Airport]
    			)
    		)
    	MEMBER [Measures].[Outgoing Passengers] AS
    		( [Origin].[Airport].CurrentMember, [Measures].[Passengers])
    	MEMBER [Measures].[Total Passengers] AS
    		( [Measures].[Incoming Passengers]
    			 +
    		 [Measures].[Outgoing Passengers] )
    SELECT
    NON EMPTY { ([Origin].[Airport].Children) } ON ROWS,
    { [Measures].[Outgoing Passengers],[Measures].[Incoming Passengers],[Measures].[Total Passengers] } ON COLUMNS
    FROM [Tran Stat V3 - Flights]

    Best regards,

    Orlando

    Monday, August 05, 2013 9:42 AM
  • Ok I found the solution to my problem on this thread : http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6014c375-31ce-4cf4-bc61-466a6c73552d/join-2-different-dimensions-in-mdx

    The solution is to use the LinkMember() and is given by Deepak.

    Thank you very much for your help.

    Here is the query that gives exactly what I want :

    WITH
    	MEMBER [Measures].[Incoming Passengers] AS
    		( [Measures].[Passengers], [Origin].[Airport].[All],
    			LinkMember( [Origin].[Airport].CurrentMember, [Destination].[Airport]
    			)
    		)
    	MEMBER [Measures].[Outgoing Passengers] AS
    		( [Origin].[Airport].CurrentMember, [Measures].[Passengers])
    	MEMBER [Measures].[Total Passengers] AS
    		( [Measures].[Incoming Passengers]
    			 +
    		 [Measures].[Outgoing Passengers] )
    SELECT
    NON EMPTY { ([Origin].[Airport].Children) } ON ROWS,
    { [Measures].[Outgoing Passengers],[Measures].[Incoming Passengers],[Measures].[Total Passengers] } ON COLUMNS
    FROM [Tran Stat V3 - Flights]

    Best regards,

    Orlando

    Hi Orlando,

    Glad to hear that you issue have been solved. Have a nice day!

    Best Regards,


    Elvis Long
    TechNet Community Support

    Monday, August 05, 2013 10:06 AM
    Moderator