Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • 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

      Mondrian generates invalid SQL when a dimension table
      is accessed multiple times in different roles - for
      example when the time dimension table is used twice as
      "Time of Order" and "Time of Delivery". In this case
      both tables are selected, but there is no join
      condition for the second one.

      To reproduce with the Sales cube make a copy of the
      Dimension "Yearly Income",
      rename it to "Yearly Income2" and change the
      foreignKey="product_id".

      <Dimension name="Yearly Income2" foreignKey="product_id">
      <Hierarchy hasAll="true" primaryKey="customer_id">
      <Table name="customer"/>
      <Level name="Yearly Income"
      column="yearly_income" uniqueMembers="true"/>
      </Hierarchy>
      </Dimension>

      Then submit this MDX:

      select NON EMPTY

      {[Measures].[Unit Sales]}

      ON COLUMNS,
      NON EMPTY Crossjoin(

      {[Yearly Income].[All Yearly Incomes]}

      , [Yearly Income2].[All Yearly
      Income2s].Children) ON ROWS
      from [Sales]

      Mondrian generates this SQL, where the customer table
      is selected twice (correct) but only joined once (error):

      select
      `customer`.`yearly_income` as `c0`
      from
      `customer` as `customer`,
      `customer` as `customer_1`,
      `sales_fact_1997` as `sales_fact_1997`,
      `time_by_day` as `time_by_day`
      where
      `sales_fact_1997`.`product_id` =
      `customer_1`.`customer_id` and
      `sales_fact_1997`.`time_id` =
      `time_by_day`.`time_id` and
      `time_by_day`.`the_year` = 1997
      group by
      `customer`.`yearly_income`
      order by
      `customer`.`yearly_income` ASC

        Activity

        Hide
        Mondrian Importer User added a comment -
        {avix}

        , 10/27/2006: priority, 5 |

        {jhyde}, 10/31/2006: resolution_id, 100 | {jhyde}

        , 10/31/2006: assigned_to, 100 |

        {jhyde}, 10/31/2006: status_id, 1 | {jhyde}

        , 10/31/2006: close_date, 0

        Show
        Mondrian Importer User added a comment - {avix} , 10/27/2006: priority, 5 | {jhyde}, 10/31/2006: resolution_id, 100 | {jhyde} , 10/31/2006: assigned_to, 100 | {jhyde}, 10/31/2006: status_id, 1 | {jhyde} , 10/31/2006: close_date, 0
        Hide
        Mondrian Importer User added a comment -
        {jhyde}

        , 10/28/2006: Logged In: YES
        user_id=312935

        Working on this...

        Show
        Mondrian Importer User added a comment - {jhyde} , 10/28/2006: Logged In: YES user_id=312935 Working on this...
        Hide
        Mondrian Importer User added a comment -
        {jhyde}

        , 10/31/2006: Logged In: YES
        user_id=312935

        This one is kind of user error... although not an obvious
        user error, so I've added an error message. (Fixed in change
        8028.)

        The problem is that the "customer" table appears twice in
        your "Sales" cube, via different join paths. (Multiple uses
        via the same join path are fine, always have been.)

        Now, mondrian ought to be smart enough to notice that the
        join paths are different, but it isn't. I could fix mondrian
        to deduce the correct aliases, but it would be a lot of
        work. In the next release or two, I want to change the way
        mondrian star schemas are represented (backwards compatible
        of course), and this problem should be fixed by that change.

        For now, the solution is to supply an alias in the <Table>
        element:

        <Dimension name="Yearly Income2" foreignKey="product_id">
        <Hierarchy hasAll="true" primaryKey="customer_id">
        <Table name="customer" alias="customer2" />
        <Level name="Yearly Income" column="yearly_income"
        uniqueMembers="true"/>
        </Hierarchy>
        </Dimension>

        I've also added validation to detect usages of the same
        table with different paths. If you run the above schema
        without the alias, you'll get the error "Duplicate table
        alias 'customer' in cube 'Sales'".

        Show
        Mondrian Importer User added a comment - {jhyde} , 10/31/2006: Logged In: YES user_id=312935 This one is kind of user error... although not an obvious user error, so I've added an error message. (Fixed in change 8028.) The problem is that the "customer" table appears twice in your "Sales" cube, via different join paths. (Multiple uses via the same join path are fine, always have been.) Now, mondrian ought to be smart enough to notice that the join paths are different, but it isn't. I could fix mondrian to deduce the correct aliases, but it would be a lot of work. In the next release or two, I want to change the way mondrian star schemas are represented (backwards compatible of course), and this problem should be fixed by that change. For now, the solution is to supply an alias in the <Table> element: <Dimension name="Yearly Income2" foreignKey="product_id"> <Hierarchy hasAll="true" primaryKey="customer_id"> <Table name="customer" alias="customer2" /> <Level name="Yearly Income" column="yearly_income" uniqueMembers="true"/> </Hierarchy> </Dimension> I've also added validation to detect usages of the same table with different paths. If you run the above schema without the alias, you'll get the error "Duplicate table alias 'customer' in cube 'Sales'".

          People

          • Assignee:
            Julian Hyde
            Reporter:
            avix
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: