[Mondrian] RE: Eigenbase perforce change 9695 for review

Julian Hyde julianhyde at speakeasy.net
Mon Aug 6 04:25:04 EDT 2007


Chuck,

I think I now understand the problem you were seeing with axis ordering.

There are a couple of problems with this fix, though. You assume that
members' natural order is the same as their name (not the case for a time
dimension [Time].[1997].[January], [Time].[1997].[February], ...); and you
use a nested loops algorithm to sort the members, which I think is O(n ^ 2)
in the size of the axis - unacceptable if an axis has say 1,000 members.

Can you rewrite your fix to use FunUtil.sortMembers() or something similar.
This uses a java.util.Comparator object which compares members using their
natural order, and it uses the efficient sort methods provided by the JDK.

Richard,

Since you wrote much of the code in RolapResult, can you please also review
Chuck's code.

Julian

> -----Original Message-----
> From: Chuck Reidmiller [mailto:creidmiller at cincom.com] 
> Sent: Friday, August 03, 2007 7:18 AM
> To: Andreas Voss; Bart Pappyn; Julian Hyde; John V. Sichi; 
> Matt Campbell; Sam Birney; Zelaine Fong
> Subject: Eigenbase perforce change 9695 for review
> 
> http://p4web.eigenbase.org/@md=d&c=6PU@//9695?ac=10
> 
> Change 9695 by creidmiller at creidmiller on 2007/08/03 07:16:26
> 
> 	MONDRIAN: sort members into default order when merging axes
> 	
> 	This is a follow up to change 9630.  The hasAll 
> parameter should not affect default output row order.  This 
> test query:
> 	
> 	select {[Promotion Name].[Price Winners], [Promotion 
> Name].[Sale Winners]} * {Tail([Time].[Year].Members,3)} ON COLUMNS,
> 	 NON EMPTY Crossjoin({[Store].CurrentMember.Children},  {[Store 
> 	 Type].[All Store Types].Children}) ON ROWS from [Sales]
> 	
> 	produces rows in the order:
> 	
> 	...[All Store Types].[Mid-Size Grocery]
> 	...[All Store Types].[Small Grocery]
> 	...[All Store Types].[Supermarket]
> 	
> 	with hasAll=true for Promotions, and the order:
> 	
> 	...[All Store Types].[Small Grocery]
> 	...[All Store Types].[Supermarket]
> 	...[All Store Types].[Mid-Size Grocery]
> 	
> 	with hasAll=false for Promotions.  This change does not 
> alter the output if an order function is used, as in this 
> modified version of the
> 	query:
> 	
> 	select {[Promotion Name].[Price Winners], [Promotion 
> Name].[Sale  Winners]} * 
> 	{Tail([Time].[Year].Members,3)} ON COLUMNS,  NON EMPTY  
> Order(Crossjoin({[Store].CurrentMember.Children},
> 	  {[Store Type].[All Store Types].Children}), 
> [Measures].[Store Sales], DESC) ON ROWS
> 	   from [Sales];
> 	
> 	which produces this row order based on descending 
> volume of store sales:
> 	
> 	...[All Store Types].[Supermarket]
> 	...[All Store Types].[Mid-Size Grocery]
> 	...[All Store Types].[Small Grocery]
> 
> Affected files ...
> 
> ... //open/mondrian/src/main/mondrian/rolap/RolapResult.java#110 edit
> ... 
> //open/mondrian/testsrc/main/mondrian/rolap/RolapResultTest.ja
> va#7 edit
> 
> Differences ...
> 
> ==== 
> //open/mondrian/src/main/mondrian/rolap/RolapResult.java#110 
> (ktext) ====
> 
> 2c2
> < // $Id: 
> //open/mondrian/src/main/mondrian/rolap/RolapResult.java#109 $
> ---
> > // $Id: 
> //open/mondrian/src/main/mondrian/rolap/RolapResult.java#110 $
> 41c41
> <  * @version $Id: 
> //open/mondrian/src/main/mondrian/rolap/RolapResult.java#109 $
> ---
> >  * @version $Id: 
> //open/mondrian/src/main/mondrian/rolap/RolapResult.java#110 $
> 1830a1831,1868
> > 
> >             // if there are unique members on both axes, 
> verify the member
> >             // order and create a replacement sorted list 
> if needed        
> >             if (list.size() > 0 && extras.size() > 0) {
> >                 String[] names = new String[list.size()];   
>          
> >                 int i = 0;
> >                 for (final Member memArray[]: list) {
> >                     StringBuilder name = new StringBuilder(100);
> >                     for (final Member mem: memArray) {
> >                         final String uniqueName = 
> mem.getUniqueName();
> >                         name.append(uniqueName);
> >                     }
> >                     names[i] = name.toString();
> >                     i++;
> >                 }
> >                 String[] namesCopy = names.clone();
> >                 java.util.Arrays.sort(names);
> >                 if (!java.util.Arrays.equals(names, 
> namesCopy)) {                
> >                     // create a sorted list from the original list
> >                     List<Member[]> sortedList = new 
> ArrayList<Member[]>();
> >                     for (int j = 0; j < names.length; j++) {
> >                         final String name = names[j];
> >                         for (final Member memArray[]: list) {
> >                             StringBuilder name2 = new 
> StringBuilder(100);
> >                             for (final Member mem: memArray) {
> >                                 final String uniqueName = 
> mem.getUniqueName();
> >                                 name2.append(uniqueName);
> >                             }
> >                             if (name.equals(name2.toString())) {
> >                                 sortedList.add(memArray);
> >                                 break;
> >                             }
> >                         }
> >                     }   
> >                     return new 
> RolapAxis.MemberArrayList(sortedList);
> >                 }                
> >             }
> > 
> 
> ==== 
> //open/mondrian/testsrc/main/mondrian/rolap/RolapResultTest.ja
> va#7 (ktext) ====
> 
> 2c2
> < // $Id: 
> //open/mondrian/testsrc/main/mondrian/rolap/RolapResultTest.java#6 $
> ---
> > // $Id: 
> //open/mondrian/testsrc/main/mondrian/rolap/RolapResultTest.java#7 $
> 23c23
> <  * @version $Id: 
> //open/mondrian/testsrc/main/mondrian/rolap/RolapResultTest.java#6 $
> ---
> >  * @version $Id: 
> //open/mondrian/testsrc/main/mondrian/rolap/RolapResultTest.java#7 $
> 287a288,329
> > 	
> > 	public void testNonAllPromotionMembers() {
> >         final TestContext testContext = 
> TestContext.createSubstitutingCube(
> >                 "Sales",
> >                 "<Dimension name=\"Promotions2\" 
> foreignKey=\"promotion_id\">\n" +
> >                 "  <Hierarchy hasAll=\"false\" 
> primaryKey=\"promotion_id\">\n" +
> >                 "    <Table name=\"promotion\"/>\n" +
> >                 "    <Level name=\"Promotion2 Name\" 
> column=\"promotion_name\" uniqueMembers=\"true\"/>\n" +
> >                 "  </Hierarchy>\n" +
> >                 "</Dimension>");
> > 
> >         testContext.assertQueryReturns(
> > 			"select {[Promotion2 Name].[Price 
> Winners], [Promotion2 Name].[Sale Winners]} * 
> {Tail([Time].[Year].Members,3)} ON COLUMNS, " +
> > 			"NON EMPTY 
> Crossjoin({[Store].CurrentMember.Children},  {[Store 
> Type].[All Store Types].Children}) ON ROWS " +
> > 			"from [Sales]",
> >             fold(
> >                 "Axis #0:\n" +
> >                 "{}\n" +
> >                 "Axis #1:\n" +
> >                 "{[Promotions2].[Price Winners], [Time].[1997]}\n" +
> >                 "{[Promotions2].[Price Winners], [Time].[1998]}\n" +
> >                 "{[Promotions2].[Sale Winners], [Time].[1997]}\n" +
> >                 "{[Promotions2].[Sale Winners], [Time].[1998]}\n" +
> >                 "Axis #2:\n" +
> >                 "{[Store].[All Stores].[USA], [Store 
> Type].[All Store Types].[Mid-Size Grocery]}\n" +
> >                 "{[Store].[All Stores].[USA], [Store 
> Type].[All Store Types].[Small Grocery]}\n" +
> >                 "{[Store].[All Stores].[USA], [Store 
> Type].[All Store Types].[Supermarket]}\n" +
> >                 "Row #0: \n" +
> >                 "Row #0: \n" +
> >                 "Row #0: 444\n" +
> >                 "Row #0: \n" +
> >                 "Row #1: 23\n" +
> >                 "Row #1: \n" +
> >                 "Row #1: \n" +
> >                 "Row #1: \n" +
> >                 "Row #2: 1,271\n" +
> >                 "Row #2: \n" +
> >                 "Row #2: \n" +
> >                 "Row #2: \n"));
> >     }
> > 	
> > 
> 




More information about the Mondrian mailing list