Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: 3.2.0 GA (3.6.0 GA Suite Release)
    • Component/s: None
    • Labels:
      None

      Description

      I'm using the mondrian 3.2 in the 3.6 BI server.

      Here's the mdx:

      {noformat}
      select NON EMPTY {[Measures].[Count]} ON COLUMNS,
        NON EMPTY Hierarchize(Crossjoin({[Operator].[All Operators]}, Crossjoin({[Promotions].[All Promotions]}, Crossjoin({[Latest].[All Latests]}, Crossjoin({[Type].[All Types]}, Union(Crossjoin({[Transaction Outcome].[All Outcomes]}, {[Subscriber].[All Subscribers]}), Crossjoin({[Transaction Outcome].[All Outcomes]}, [Subscriber].[All Subscribers].Children))))))) ON ROWS
      from [Bundles]
      where {[Date].[2010].[SEP].[23], [Date].[2010].[SEP].[24], [Date].[2010].[SEP].[25], [Date].[2010].[SEP].[26], [Date].[2010].[SEP].[27]}
      {noformat}

      Note the 4 selected dates in the where clause - Selected via jpivot.

      Now, this runs the following Sql:

      {noformat}
      select `D_DATE`.`YEAR` as `c0`, `D_DATE`.`MONTH` as `c1`, `D_DATE`.`DAY_OF_MONTH` as `c2`, count(`F_STATUS_CHANGES_BUNDLE`.`TRANSACTION_ID`) as `m0` from `D_DATE` as `D_DATE`, `F_STATUS_CHANGES_BUNDLE` as `F_STATUS_CHANGES_BUNDLE` where `F_STATUS_CHANGES_BUNDLE`.`DATE_TK` = `D_DATE`.`DATE_TK` and `D_DATE`.`YEAR` = 2010 and `D_DATE`.`MONTH` = '9' and `D_DATE`.`DAY_OF_MONTH` in (23, 24, 25, 26, 27) group by `D_DATE`.`YEAR`, `D_DATE`.`MONTH`, `D_DATE`.`DAY_OF_MONTH`
      {noformat}

      Note how the day of month has the correct "in" clause

      then it runs this, and only this:

      {noformat}
      2010-09-28 14:45:22,203 DEBUG [mondrian.sql] 112: SqlTupleReader.readTuples [[Transaction Outcome].[(All)], [Subscriber].[Subscriber MSISDN]]: executing sql [select `D_SUBSCRIBER`.`MSISDN` as `c0` from `D_SUBSCRIBER` as `D_SUBSCRIBER`, `F_STATUS_CHANGES_BUNDLE` as `F_STATUS_CHANGES_BUNDLE`, `D_DATE` as `D_DATE` where `F_STATUS_CHANGES_BUNDLE`.`SUBSCRIBER_TK` = `D_SUBSCRIBER`.`SUBSCRIBER_TK` and `F_STATUS_CHANGES_BUNDLE`.`DATE_TK` = `D_DATE`.`DATE_TK` and `D_DATE`.`YEAR` = 2010 and `D_DATE`.`MONTH` = '9' and `D_DATE`.`DAY_OF_MONTH` = 27 group by `D_SUBSCRIBER`.`MSISDN` order by ISNULL(`D_SUBSCRIBER`.`MSISDN`), `D_SUBSCRIBER`.`MSISDN` ASC]
      {noformat}

      Notice how the day of month in clause has been lost.
      And then it runs this several times with a big list of msisdns:

      {noformat}
      2010-09-28 14:45:22,235 DEBUG [mondrian.sql] 114: Segment.load: executing sql [select `D_DATE`.`YEAR` as `c0`, `D_DATE`.`MONTH` as `c1`, `D_DATE`.`DAY_OF_MONTH` as `c2`, `D_SUBSCRIBER`.`MSISDN` as `c3`, count(`F_STATUS_CHANGES_BUNDLE`.`TRANSACTION_ID`) as `m0` from `D_DATE` as `D_DATE`, `F_STATUS_CHANGES_BUNDLE` as `F_STATUS_CHANGES_BUNDLE`, `D_SUBSCRIBER` as `D_SUBSCRIBER` where `F_STATUS_CHANGES_BUNDLE`.`DATE_TK` = `D_DATE`.`DATE_TK` and `D_DATE`.`YEAR` = 2010 and `D_DATE`.`MONTH` = '9' and `D_DATE`.`DAY_OF_MONTH` = 27 and `F_STATUS_CHANGES_BUNDLE`.`SUBSCRIBER_TK` = `D_SUBSCRIBER`.`SUBSCRIBER_TK` and `D_SUBSCRIBER`.`MSISDN` in ('' <COMMA SEPARATED LIST> '') group by `D_DATE`.`YEAR`, `D_DATE`.`MONTH`, `D_DATE`.`DAY_OF_MONTH`, `D_SUBSCRIBER`.`MSISDN`]
      {noformat}

      but obviously, because that list of msisdns came from the query above which didnt have the day of month in list, the list is incomplete. So jpivot shows 300 is the total number, but in the detail only displays about 50 rows.

      Is there a more up to date mondrian i can try? I couldnt see one on the ci server...

        Activity

        Hide
        Dan Keeley (codek) added a comment -
        Psiware support - can you provide a reproducible example? are you using the latest version of mondrian?

        I hope to be able to provide a dataset to reporduce the issue, but it's tricky finding the time.

        My workaround was simple :) use a jdbc query instead of mdx! Obviously thats far from ideal.
        Show
        Dan Keeley (codek) added a comment - Psiware support - can you provide a reproducible example? are you using the latest version of mondrian? I hope to be able to provide a dataset to reporduce the issue, but it's tricky finding the time. My workaround was simple :) use a jdbc query instead of mdx! Obviously thats far from ideal.
        Hide
        Psiware support added a comment -
        Hi dan thanks for your quick response.

        We've been able to reproduce the same behavior with foodmart example db and schema, I'm including MDX query below and resulting SQL:

        select NON EMPTY Hierarchize(Union({[Store Type].[All Store Types]}, [Store Type].[All Store Types].Children)) ON COLUMNS,
          NON EMPTY Hierarchize(Union(Union(Union(Union(Crossjoin({[Store].[All Stores]}, {[Customers].[All Customers]}), Crossjoin({[Store].[All Stores]}, [Customers].[All Customers].Children)), Crossjoin({[Store].[All Stores]}, [Customers].[USA].Children)), Crossjoin({[Store].[All Stores]}, [Customers].[USA].[CA].Children)), Crossjoin({[Store].[All Stores]}, [Customers].[USA].[CA].[Altadena].Children))) ON ROWS
        from [Sales]
        where {[Time].[1997].[Q4].[10], [Time].[1997].[Q4].[11], [Time].[1997].[Q4].[12]}

        SQL:
        select "customer"."country" as "c0" from "customer" as "customer", "sales_fact_1997" as "sales_fact_1997", "time_by_day" as "time_by_day" where "sales_fact_1997"."customer_id" = "customer"."customer_id" and "sales_fact_1997"."time_id" = "time_by_day"."time_id" and "time_by_day"."the_year" = 1997 and "time_by_day"."quarter" = 'Q4' and "time_by_day"."month_of_year" = 12 group by "customer"."country" order by "customer"."country" ASC

        Note that the month_of_year field on the where clause only includes the member "12", we think that it should be including all members on the compound slicer (10,11,12).

        Thanks for your help!
        Show
        Psiware support added a comment - Hi dan thanks for your quick response. We've been able to reproduce the same behavior with foodmart example db and schema, I'm including MDX query below and resulting SQL: select NON EMPTY Hierarchize(Union({[Store Type].[All Store Types]}, [Store Type].[All Store Types].Children)) ON COLUMNS,   NON EMPTY Hierarchize(Union(Union(Union(Union(Crossjoin({[Store].[All Stores]}, {[Customers].[All Customers]}), Crossjoin({[Store].[All Stores]}, [Customers].[All Customers].Children)), Crossjoin({[Store].[All Stores]}, [Customers].[USA].Children)), Crossjoin({[Store].[All Stores]}, [Customers].[USA].[CA].Children)), Crossjoin({[Store].[All Stores]}, [Customers].[USA].[CA].[Altadena].Children))) ON ROWS from [Sales] where {[Time].[1997].[Q4].[10], [Time].[1997].[Q4].[11], [Time].[1997].[Q4].[12]} SQL: select "customer"."country" as "c0" from "customer" as "customer", "sales_fact_1997" as "sales_fact_1997", "time_by_day" as "time_by_day" where "sales_fact_1997"."customer_id" = "customer"."customer_id" and "sales_fact_1997"."time_id" = "time_by_day"."time_id" and "time_by_day"."the_year" = 1997 and "time_by_day"."quarter" = 'Q4' and "time_by_day"."month_of_year" = 12 group by "customer"."country" order by "customer"."country" ASC Note that the month_of_year field on the where clause only includes the member "12", we think that it should be including all members on the compound slicer (10,11,12). Thanks for your help!
        Hide
        Dan Keeley (codek) added a comment -
        Re-opening because PSiWare have provided an example of how to reproduce
        Show
        Dan Keeley (codek) added a comment - Re-opening because PSiWare have provided an example of how to reproduce
        Hide
        Psiware support added a comment -
        This issue is solved from version 3.7
        Show
        Psiware support added a comment - This issue is solved from version 3.7
        Hide
        Dan Keeley (codek) added a comment -
        hurrah!
        Show
        Dan Keeley (codek) added a comment - hurrah!

          People

          • Assignee:
            Julian Hyde
            Reporter:
            Dan Keeley (codek)
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: