Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Aggregate CopyLink in Mondrian4 doesn't work

  1. #1
    Join Date
    May 2013
    Posts
    24

    Default Aggregate CopyLink in Mondrian4 doesn't work

    Hi,

    I've been trying to use the Aggregate CopyLink option in Mondrian 4.0 with the Foodmart schema and it just doesn't work. (Saiku with mondrian4 for testing).

    The aggregate table just doesn't get hit, it does with a ForeignKeyLink but not here. The xml definition I am using is as follows:

    <MeasureGroup table='agg_c_special_sales_fact_1997' type='aggregate'>
    <Measures>
    <Measure aggregator='sum' name='Fact Count' column='fact_count'/>
    <MeasureRef name='Unit Sales' aggColumn='unit_sales_sum'/>
    <MeasureRef name='Store Cost' aggColumn='store_cost_sum'/>
    <MeasureRef name='Store Sales' aggColumn='store_sales_sum'/>
    </Measures>
    <DimensionLinks>
    <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/>
    <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/>
    <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/>
    <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/>
    <CopyLink dimension='Time' attribute='Month'>
    <Column aggColumn='time_year' table='time_by_day' name='the_year'/>
    <Column aggColumn='time_quarter' table='time_by_day' name='quarter'/>
    <Column aggColumn='time_month' table='time_by_day' name='month_of_year'/>
    </CopyLink>
    </DimensionLinks>
    </MeasureGroup>
    Using the fact_count measure with any of the foreignkeylink dimensions works, with copylink it does not. Does anyone have any ideas?

  2. #2
    Join Date
    Jan 2013
    Posts
    796

    Default

    Hmmm. I copied your MeasureGroup def into my FM and I *do* see agg_c_special.. get used. Can you try a nice simple query that should be satisfied by the agg? E.g. "select [Time].[Quarter].members on 0 from sales"

  3. #3
    Join Date
    May 2013
    Posts
    24

    Default

    Thanks

    I've run the following query, taken from the logs:

    Code:
    2014-02-20 11:52:53,404 DEBUG [mondrian.mdx] 1065: select [Time].[Time].[Quarter].Members ON COLUMNS
    from [Sales]
    
    2014-02-20 11:52:54,186 DEBUG [mondrian.mdx] 1065: exec: 781 ms
    Which then runs the following SQL, taken from the logs:

    Code:
    2014-02-20 11:52:53,415 DEBUG [mondrian.sql] 34: SqlTupleReader.readTuples [[Time].[Time].[Quarter]]: executing sql [select `time_by_day`.`the_year` as `c0`, `time_by_day`.`quarter` as `c1` from `time_by_day` as `time_by_day` group by `time_by_day`.`the_year`, `time_by_day`.`quarter` order by ISNULL(`time_by_day`.`the_year`) ASC, `time_by_day`.`the_year` ASC, ISNULL(`time_by_day`.`quarter`) ASC, `time_by_day`.`quarter` ASC]
    2014-02-20 11:52:53,420 DEBUG [mondrian.sql] 34: , exec 2 ms
    2014-02-20 11:52:53,421 DEBUG [mondrian.sql] 34: , exec+fetch 5 ms, 8 rows
    2014-02-20 11:52:53,689 DEBUG [mondrian.sql] 35: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct `the_year`) from `time_by_day`]
    2014-02-20 11:52:53,690 DEBUG [mondrian.sql] 35: , exec 0 ms
    2014-02-20 11:52:53,690 DEBUG [mondrian.sql] 35: , exec+fetch 1 ms, 1 rows
    2014-02-20 11:52:53,691 DEBUG [mondrian.sql] 36: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct `quarter`) from `time_by_day`]
    2014-02-20 11:52:53,692 DEBUG [mondrian.sql] 36: , exec 1 ms
    2014-02-20 11:52:53,693 DEBUG [mondrian.sql] 36: , exec+fetch 1 ms, 1 rows
    2014-02-20 11:52:53,832 DEBUG [mondrian.sql] 37: Segment.load: executing sql [select `time_by_day`.`the_year` as `c0`, `time_by_day`.`quarter` as `c1`, sum(`sales_fact_1997`.`unit_sales`) as `m0` from `sales_fact_1997` as `sales_fact_1997`, `time_by_day` as `time_by_day` where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` group by `time_by_day`.`the_year`, `time_by_day`.`quarter`]
    2014-02-20 11:52:54,070 DEBUG [mondrian.sql] 37: , exec 237 ms
    2014-02-20 11:52:54,111 DEBUG [mondrian.sql] 37: , exec+fetch 279 ms, 4 rows

    There is no sign in there that the aggregate is getting used. My mondrian properties file is attached. The mondrian log is also attached.

    Looking at the source, I noticed the following comment around the Aggregate section (on the lagunitas GitHub branch - I take it this is Mondrian-4, looking at Jira), is this what is causing it - The log output matches the section after the aggStar is null:
    Taken from:
    https://github.com/pentaho/mondrian/...ager.java#L246

    Code:
         // Find an aggregate table. (There aren't any registered anymore, so
         // this will never find anything.)
         AggStar aggStar = findAgg(star, levelBitKey, measureBitKey, rollup);
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2013
    Posts
    796

    Default

    Are aggregates enabled? I.e. the following props set?

    mondrian.rolap.aggregates.Use=true
    mondrian.rolap.aggregates.Read=true


    The code snippet you included is from the older method of handling aggeregates. If you're interested in stepping through the Mondrian 4 code, a good place to break is in RolapGalaxy.findAgg().

  5. #5
    Join Date
    May 2013
    Posts
    24

    Default

    They are indeed, as shown in the mondrian.txt attachment of my properties in use.
    They work when I use ForeignKeyLink, just not CopyLink.

    Ok, it maybe an older method but it looks like somehow I'm hitting that due to the mondrian_log output that is attached. The only match for 'Aggs=[' in the code is in that linked file.

    I've had a quick dig through the code starting at the RolapGalaxy and AggregationManager, looking at the call hierarchy. It looks like the only overlap is at https://github.com/pentaho/mondrian/...ager.java#L932


    Without looking deeper in the codebase and gaining some understanding of it, I don't think there is a great deal more I can add.

    The build of Mondrian4 and base FoodMart.xml I'm using is the one bundled with 17/02 release of Saiku-Mondrian 4:
    http://ci.analytical-labs.com/job/saiku-mondrian4/

    If there is any other information you need let me know. Would it be worth me trying with your FoodMart schema, if you're willing to attach it.

    Thanks again.

  6. #6
    Join Date
    Jan 2013
    Posts
    796

    Default

    I'm using the Foodmart xml off of the HEAD of the lagunitas branch, which is the main Mondrian 4 branch: https://github.com/pentaho/mondrian/...t.mondrian.xml.

    When I get a chance I'll try grabbing the saiku release to see if I'm able to reproduce what you're seeing. I'm not sure exactly what version of mondrian 4 they are using...

  7. #7
    Join Date
    Jan 2013
    Posts
    796

    Default

    Sorry, still unable to reproduce what you're seeing. I grabbed the saiku dist just now (http://ci.analytical-labs.com/job/sa...4-SNAPSHOT.zip, build #435). I did the following:

    1) modified the mondrian.properties to set aggregates.Use and aggregates.Read to true, configured log4j.xml to output SQL
    2) started it up and dragged [Unit Sales] and [Quarter] onto the report, since [Quarter] is defined as a copy link for the agg tables agg_g_ms...

    In the SQL I see the table agg_g_ms_pcat_sales_fact_1997 was used for segment load.

    Can you try to reproduce your problem with that same saiku dist so we can make sure we're looking at the same thing? Thanks.

  8. #8
    Join Date
    May 2013
    Posts
    24

    Default

    I've taken that Saiku and followed your instructions. The aggregate table gets correctly hit.

    I then modified the mondrian.properties file to set the following:
    mondrian.rolap.aggregates.ChooseByVolume=true

    With this set, the aggregate did not get hit. I would have expected this to work in the same way as having this set to false.
    Code:
    agg_g_ms_pcat_sales_fact_1997
    Column Count: 13
    Row Count: 2637
    Volume: 34281
    Code:
    sales_fact_1997:
    Column Count: 16
    Row Count: 86837
    Volume: 1389392
    On my original example (using agg_c_special_sales_fact_1997), setting ChooseByVolume to false allows the aggregate to be hit. So I think there is an issue in this area, judging by the description of this property:

    Code:
    # Boolean property that controls whether aggregate tables
    # are ordered by their volume or row count.
    #
    # If true, Mondrian uses the aggregate table with the smallest volume
    # (number of rows multiplied by number of columns); if false, Mondrian
    # uses the aggregate table with the fewest rows.
    Thanks again.

  9. #9
    Join Date
    Jan 2013
    Posts
    796

    Default

    Hi Adam. I think that's a bug. I've logged MONDRIAN-1920.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.