Mondrian Roles' impact on Aggregate tables

[Bolek]

I upgraded to Mondrian 3.1.5 and am still experiencing the issue described in jira case BISERVER-1818.

Basically Mondrian keeps executing the following SQL statement 12 times for every member in my salesperson dimension (Dim_Salespeople) whenever I have Mondrian roles defined in my schema. I am not sure why it does any of these SQL statements based on each member and I can't see why it would need to do them 12 times.

Below is an excerpt from my log to show the repetitive SQL that is being issued.


Can anyone confirm if this is the issue that was supposedly fixed in the BISERVER-1818 case ??

http://jira.pentaho.com/browse/BISERVER-1818


mondrian_sql.log excerpt showing the 12 repetitive SQL statement for two members (MICHEL GREGOIRE, Miguel Branco) of the Dim_Salesperson Dimension which has 91 members overall:

2010-02-04 16:34:52,524 DEBUG [mondrian.sql] 407: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:52,526 DEBUG [mondrian.sql] 407: , exec 1 ms
2010-02-04 16:34:52,526 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 1 ms
2010-02-04 16:34:52,526 DEBUG [mondrian.sql] 407: , exec+fetch 1 ms, 1 rows
2010-02-04 16:34:52,527 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 1 ms, 1 rows
2010-02-04 16:34:52,528 DEBUG [mondrian.sql] 408: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:52,529 DEBUG [mondrian.sql] 408: , exec 1 ms
2010-02-04 16:34:52,529 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 1 ms
2010-02-04 16:34:52,530 DEBUG [mondrian.sql] 408: , exec+fetch 2 ms, 1 rows
2010-02-04 16:34:52,530 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2 ms, 1 rows
2010-02-04 16:34:52,531 DEBUG [mondrian.sql] 409: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:52,532 DEBUG [mondrian.sql] 409: , exec 1 ms
2010-02-04 16:34:52,532 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 1 ms
2010-02-04 16:34:52,533 DEBUG [mondrian.sql] 409: , exec+fetch 2 ms, 1 rows
2010-02-04 16:34:52,533 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2 ms, 1 rows
2010-02-04 16:34:52,534 DEBUG [mondrian.sql] 410: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:52,536 DEBUG [mondrian.sql] 410: , exec 1 ms
2010-02-04 16:34:52,536 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 1 ms
2010-02-04 16:34:52,537 DEBUG [mondrian.sql] 410: , exec+fetch 2 ms, 1 rows
2010-02-04 16:34:52,537 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'MICHEL GREGOIRE' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2 ms, 1 rows






2010-02-04 16:34:52,538 DEBUG [mondrian.sql] 411: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:54,962 DEBUG [mondrian.sql] 411: , exec 2424 ms
2010-02-04 16:34:54,963 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 2424 ms
2010-02-04 16:34:54,963 DEBUG [mondrian.sql] 411: , exec+fetch 2425 ms, 2 rows
2010-02-04 16:34:54,963 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2425 ms, 2 rows
2010-02-04 16:34:54,965 DEBUG [mondrian.sql] 412: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:57,208 DEBUG [mondrian.sql] 412: , exec 2243 ms
2010-02-04 16:34:57,209 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 2243 ms
2010-02-04 16:34:57,209 DEBUG [mondrian.sql] 412: , exec+fetch 2244 ms, 2 rows
2010-02-04 16:34:57,209 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2244 ms, 2 rows
2010-02-04 16:34:57,211 DEBUG [mondrian.sql] 413: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:34:59,455 DEBUG [mondrian.sql] 413: , exec 2243 ms
2010-02-04 16:34:59,456 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 2243 ms
2010-02-04 16:34:59,456 DEBUG [mondrian.sql] 413: , exec+fetch 2244 ms, 2 rows
2010-02-04 16:34:59,457 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2244 ms, 2 rows
2010-02-04 16:34:59,458 DEBUG [mondrian.sql] 414: SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC]
2010-02-04 16:35:01,937 DEBUG [mondrian.sql] 414: , exec 2479 ms
2010-02-04 16:35:01,937 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec 2479 ms
2010-02-04 16:35:01,938 DEBUG [mondrian.sql] 414: , exec+fetch 2480 ms, 2 rows
2010-02-04 16:35:01,938 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `Dim_Companies`.`CompanyName` as `c0`, `Dim_Companies`.`Company` as `c1`, `Dim_Companies`.`Company` as `c2` from `Dim_Salespeople` as `Dim_Salespeople`, `Fact_Sales` as `Fact_Sales`, `Dim_Companies` as `Dim_Companies` where `Fact_Sales`.`SalespersonKey` = `Dim_Salespeople`.`SalespersonKey` and `Dim_Salespeople`.`SalespersonName` = 'Miguel Branco' and `Fact_Sales`.`Company` = `Dim_Companies`.`Company` group by `Dim_Companies`.`CompanyName`, `Dim_Companies`.`Company` order by ISNULL(`Dim_Companies`.`Company`), `Dim_Companies`.`Company` ASC], exec+fetch 2480 ms, 2 rows