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

Mondrian does not use agg tables when cubes used by a virtual cube have agg tables defined

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 3.3.0 GA (4.1.0 GA Suite Release)
    • Component/s: None
    • Labels:
      None
    • Customer Case:
    • QA Validation Status:
      Not Yet Validated

      Description

      When you have a Virt Cube covering multiple aggregate tables ( 2 cubes, each with 2 aggs), when it determines dimensionality - it goes to the fact vs. the aggs
      1. custom.mondrian.xml
        5 kB
        Luc Boudreau
      1. Capture.PNG
        124 kB

        Activity

        Hide
        Li Deng added a comment -
        Followed the steps and able to create report using the data. Here is the log after create the report from step 8 above:

        // Request ID: 8e4f8c85-399e-11e2-9d75-5712d0bc0b4f - RUN_REPORT
        With
        Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Time],[*BASE_MEMBERS_Store])'
        Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Time].CurrentMember.OrderKey,BASC,Ancestor([Time].CurrentMember,[Time].[Quarter]).OrderKey,BASC,[Store].CurrentMember.OrderKey,BASC)'
        Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
        Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Time].currentMember,[Store].currentMember)})'
        Set [*BASE_MEMBERS_Time] as '[Time].[Month].Members'
        Set [*BASE_MEMBERS_Store] as '[Store].[Store Country].Members'
        Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
        Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400
        Select
        [*BASE_MEMBERS_Measures] on columns,
        Non Empty [*SORTED_ROW_AXIS] on rows
        From [SuperSales]
        Show
        Li Deng added a comment - Followed the steps and able to create report using the data. Here is the log after create the report from step 8 above: // Request ID: 8e4f8c85-399e-11e2-9d75-5712d0bc0b4f - RUN_REPORT With Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Time],[*BASE_MEMBERS_Store])' Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Time].CurrentMember.OrderKey,BASC,Ancestor([Time].CurrentMember,[Time].[Quarter]).OrderKey,BASC,[Store].CurrentMember.OrderKey,BASC)' Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}' Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Time].currentMember,[Store].currentMember)})' Set [*BASE_MEMBERS_Time] as '[Time].[Month].Members' Set [*BASE_MEMBERS_Store] as '[Store].[Store Country].Members' Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]' Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400 Select [*BASE_MEMBERS_Measures] on columns, Non Empty [*SORTED_ROW_AXIS] on rows From [SuperSales]
        Hide
        Brandon Bruce added a comment -
        tested in 4.8 and it appears to be using the agg tables. I ran the following MDX and then checked the logs:

        select {NonEmptyCrossJoin([Time].[Month].Members, [Store].[Store Country].Members)} on rows,{[Measures].[Unit Sales]} on columns from [SuperSales]

        The mondrian.log shows the following referencing the agg tables:

        2012-12-03 10:34:46,052 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Time].[Month], [Store].[Store Country]]: executing sql [select "agg_c_14_sales_fact_1997"."the_year" as "c0", "agg_c_14_sales_fact_1997"."quarter" as "c1", "agg_c_14_sales_fact_1997"."month_of_year" as "c2", "store"."store_country" as "c3" from "agg_c_14_sales_fact_1997" as "agg_c_14_sales_fact_1997", "store" as "store" where "agg_c_14_sales_fact_1997"."store_id" = "store"."store_id" group by "agg_c_14_sales_fact_1997"."the_year", "agg_c_14_sales_fact_1997"."quarter", "agg_c_14_sales_fact_1997"."month_of_year", "store"."store_country" order by "agg_c_14_sales_fact_1997"."the_year" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."quarter" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."month_of_year" ASC NULLS LAST, "store"."store_country" ASC NULLS LAST], exec 194 ms
        2012-12-03 10:34:46,072 DEBUG [mondrian.sql] 24: , exec+fetch 228 ms, 12 rows
        2012-12-03 10:34:46,072 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Time].[Month], [Store].[Store Country]]: done executing sql [select "agg_c_14_sales_fact_1997"."the_year" as "c0", "agg_c_14_sales_fact_1997"."quarter" as "c1", "agg_c_14_sales_fact_1997"."month_of_year" as "c2", "store"."store_country" as "c3" from "agg_c_14_sales_fact_1997" as "agg_c_14_sales_fact_1997", "store" as "store" where "agg_c_14_sales_fact_1997"."store_id" = "store"."store_id" group by "agg_c_14_sales_fact_1997"."the_year", "agg_c_14_sales_fact_1997"."quarter", "agg_c_14_sales_fact_1997"."month_of_year", "store"."store_country" order by "agg_c_14_sales_fact_1997"."the_year" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."quarter" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."month_of_year" ASC NULLS LAST, "store"."store_country" ASC NULLS LAST], exec+fetch 228 ms, 12 rows, ex=25, close=25, open=[]
        Show
        Brandon Bruce added a comment - tested in 4.8 and it appears to be using the agg tables. I ran the following MDX and then checked the logs: select {NonEmptyCrossJoin([Time].[Month].Members, [Store].[Store Country].Members)} on rows,{[Measures].[Unit Sales]} on columns from [SuperSales] The mondrian.log shows the following referencing the agg tables: 2012-12-03 10:34:46,052 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Time].[Month], [Store].[Store Country]]: executing sql [select "agg_c_14_sales_fact_1997"."the_year" as "c0", "agg_c_14_sales_fact_1997"."quarter" as "c1", "agg_c_14_sales_fact_1997"."month_of_year" as "c2", "store"."store_country" as "c3" from "agg_c_14_sales_fact_1997" as "agg_c_14_sales_fact_1997", "store" as "store" where "agg_c_14_sales_fact_1997"."store_id" = "store"."store_id" group by "agg_c_14_sales_fact_1997"."the_year", "agg_c_14_sales_fact_1997"."quarter", "agg_c_14_sales_fact_1997"."month_of_year", "store"."store_country" order by "agg_c_14_sales_fact_1997"."the_year" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."quarter" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."month_of_year" ASC NULLS LAST, "store"."store_country" ASC NULLS LAST], exec 194 ms 2012-12-03 10:34:46,072 DEBUG [mondrian.sql] 24: , exec+fetch 228 ms, 12 rows 2012-12-03 10:34:46,072 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Time].[Month], [Store].[Store Country]]: done executing sql [select "agg_c_14_sales_fact_1997"."the_year" as "c0", "agg_c_14_sales_fact_1997"."quarter" as "c1", "agg_c_14_sales_fact_1997"."month_of_year" as "c2", "store"."store_country" as "c3" from "agg_c_14_sales_fact_1997" as "agg_c_14_sales_fact_1997", "store" as "store" where "agg_c_14_sales_fact_1997"."store_id" = "store"."store_id" group by "agg_c_14_sales_fact_1997"."the_year", "agg_c_14_sales_fact_1997"."quarter", "agg_c_14_sales_fact_1997"."month_of_year", "store"."store_country" order by "agg_c_14_sales_fact_1997"."the_year" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."quarter" ASC NULLS LAST, "agg_c_14_sales_fact_1997"."month_of_year" ASC NULLS LAST, "store"."store_country" ASC NULLS LAST], exec+fetch 228 ms, 12 rows, ex=25, close=25, open=[]
        Hide
        Slawomir Chodnicki added a comment -
        Hey Brandon, can we close this one?
        Show
        Slawomir Chodnicki added a comment - Hey Brandon, can we close this one?
        Hide
        Brandon Bruce added a comment -
        This needs to be tested as part of 5.0.0 M1
        Show
        Brandon Bruce added a comment - This needs to be tested as part of 5.0.0 M1
        Hide
        Li Deng added a comment -
        Verified in 5.0 .0, see the same result as in Brandon's notes above. see attached screenshot.
        Show
        Li Deng added a comment - Verified in 5.0 .0, see the same result as in Brandon's notes above. see attached screenshot.

          People

          • Assignee:
            Li Deng
            Reporter:
            Brian Hagan (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: