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:
    • 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.
    • 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: