Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-980

MDX subquery (also known as "sub-select" and "subcube")

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Medium Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Not Planned
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      davidmet writes:
      > Hi everybody.... I have problems with a nested query after of a From MDX sentence . For instance:
      > select ....... from [db] .....
      > works fine, but:
      > select ....... from (select ...... from ..... where .......) ...............
      > don't works. JPivot says that there is a syntaxis error in "(".

      Mondrian does not support MDX subqueries. It should. See e.g. http://blogs.technet.com/b/paolt/archive/2005/02/14/372374.aspx

        Issue Links

          Activity

          Hide
          Julian Hyde added a comment -
          The email thread http://lists.pentaho.org/pipermail/mondrian/2012-October/004153.html makes a case that sub-selects are a more elegant way of providing "visual totals" than the VisualTotals function. I agree.

          However, sub-selects are quite a lot of work. They are definitely more than syntactic sugar. We're not likely to implement this feature by mondrian-4.
          Show
          Julian Hyde added a comment - The email thread http://lists.pentaho.org/pipermail/mondrian/2012-October/004153.html makes a case that sub-selects are a more elegant way of providing "visual totals" than the VisualTotals function. I agree. However, sub-selects are quite a lot of work. They are definitely more than syntactic sugar. We're not likely to implement this feature by mondrian-4.
          Hide
          Julian Hyde added a comment -
          Microsoft's documentation:

          Using Cube and Subcube Expressions
          http://msdn.microsoft.com/en-us/library/ms145564.aspx
           
          CREATE SUBCUBE Statement (MDX)
          http://msdn.microsoft.com/en-us/library/ms144916.aspx
          Show
          Julian Hyde added a comment - Microsoft's documentation: Using Cube and Subcube Expressions http://msdn.microsoft.com/en-us/library/ms145564.aspx   CREATE SUBCUBE Statement (MDX) http://msdn.microsoft.com/en-us/library/ms144916.aspx
          Hide
          Maxime Caron added a comment -
          For a use case for this feature :

            If want to create a barchart with a bar for each month but also need to filter on a date range which might not completly overlap some of the month.

           When that happen I would expect the aggregate count for those month to only aggregaet over the days that fall in the date range instead of the aggregate for the whole month.

           I dont know if this is possible using VisualTotals but if it is this seem like that would not be fast.

          please see: http://stackoverflow.com/questions/12681096/how-to-groupby-and-filter-on-the-same-dimension-in-mdx
          Show
          Maxime Caron added a comment - For a use case for this feature :   If want to create a barchart with a bar for each month but also need to filter on a date range which might not completly overlap some of the month.  When that happen I would expect the aggregate count for those month to only aggregaet over the days that fall in the date range instead of the aggregate for the whole month.  I dont know if this is possible using VisualTotals but if it is this seem like that would not be fast. please see: http://stackoverflow.com/questions/12681096/how-to-groupby-and-filter-on-the-same-dimension-in-mdx
          Hide
          Luc Boudreau added a comment -
          Maxime,

          Can you use something like this:

          with member [Measures].[Member] as 'Aggregate( Filter( [Dim].Members, {whatever contition} ) )
          select ....
          Show
          Luc Boudreau added a comment - Maxime, Can you use something like this: with member [Measures].[Member] as 'Aggregate( Filter( [Dim].Members, {whatever contition} ) ) select ....

            People

            • Assignee:
              Unassigned User
              Reporter:
              Julian Hyde
            • Votes:
              7 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

              • Created:
                Updated: