Java tutorial
/* // This software is subject to the terms of the Eclipse Public License v1.0 // Agreement, available at the following URL: // http://www.eclipse.org/legal/epl-v10.html. // You must accept the terms of that agreement to use this software. // // Copyright (c) 2002-2014 Pentaho Corporation.. All rights reserved. */ package mondrian.test; import mondrian.olap.*; import mondrian.olap.Category; import mondrian.olap.Hierarchy; import mondrian.olap.Level; import mondrian.rolap.RolapConnectionProperties; import mondrian.rolap.aggmatcher.AggTableManager; import mondrian.spi.Dialect; import mondrian.spi.PropertyFormatter; import mondrian.util.Bug; import org.apache.commons.io.IOUtils; import org.apache.log4j.*; import org.apache.log4j.varia.LevelRangeFilter; import org.olap4j.metadata.NamedList; import java.io.*; import java.sql.SQLException; import java.util.List; import java.util.Map; /** * Unit tests for various schema features. * * @see SchemaVersionTest * @see mondrian.rolap.SharedDimensionTest * * @author jhyde * @since August 7, 2006 */ public class SchemaTest extends FoodMartTestCase { public SchemaTest(String name) { super(name); } /** * Asserts that a list of exceptions (probably from * {@link mondrian.olap.Schema#getWarnings()}) contains the expected * exception. * * @param exceptionList List of exceptions * @param expected Expected message */ private void assertContains(List<Exception> exceptionList, String expected) { StringBuilder buf = new StringBuilder(); for (Exception exception : exceptionList) { if (exception.getMessage().matches(expected)) { return; } if (buf.length() > 0) { buf.append(Util.nl); } buf.append(exception.getMessage()); } fail("Exception list did not contain expected exception '" + expected + "'. Exception list is:" + buf.toString()); } // Tests follow... public void testSolveOrderInCalculatedMember() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", null, "<CalculatedMember\n" + " name=\"QuantumProfit\"\n" + " dimension=\"Measures\">\n" + " <Formula>[Measures].[Store Sales] / [Measures].[Store Cost]</Formula>\n" + " <CalculatedMemberProperty name=\"FORMAT_STRING\" value=\"$#,##0.00\"/>\n" + " </CalculatedMember>, <CalculatedMember\n" + " name=\"foo\"\n" + " dimension=\"Gender\">\n" + " <Formula>Sum(Gender.Members)</Formula>\n" + " <CalculatedMemberProperty name=\"FORMAT_STRING\" value=\"$#,##0.00\"/>\n" + " <CalculatedMemberProperty name=\"SOLVE_ORDER\" value=\'2000\'/>\n" + " </CalculatedMember>"); testContext.assertQueryReturns("select {[Measures].[QuantumProfit]} on 0, {(Gender.foo)} on 1 from sales", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[QuantumProfit]}\n" + "Axis #2:\n" + "{[Gender].[foo]}\n" + "Row #0: $7.52\n"); } public void testHierarchyDefaultMember() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Gender with default\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" " + "primaryKey=\"customer_id\" " // Define a default member's whose unique name includes the // 'all' member. + "defaultMember=\"[Gender with default].[All Gender with defaults].[M]\" >\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\" />\n" + " </Hierarchy>\n" + " </Dimension>"); testContext.assertQueryReturns("select {[Gender with default]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Gender with default].[M]}\n" + "Row #0: 135,215\n"); } /** * Test case for the issue described in * <a href="http://forums.pentaho.com/showthread.php?p=190737">Pentaho * forum post 'wrong unique name for default member when hasAll=false'</a>. */ public void testDefaultMemberName() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Product with no all\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Class\" table=\"product_class\" nameColumn=\"product_subcategory\"\n" + " column=\"product_class_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Brand Name\" table=\"product\" column=\"brand_name\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Name\" table=\"product\" column=\"product_name\"\n" + " uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n"); // note that default member name has no 'all' and has a name not an id testContext.assertQueryReturns("select {[Product with no all]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Product with no all].[Nuts]}\n" + "Row #0: 4,400\n"); } public void testHierarchyAbbreviatedDefaultMember() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Gender with default\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" " + "primaryKey=\"customer_id\" " // Default member unique name does not include 'All'. + "defaultMember=\"[Gender with default].[F]\" >\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\" />\n" + " </Hierarchy>\n" + " </Dimension>"); testContext.assertQueryReturns("select {[Gender with default]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" // Note that the 'all' member is named according to the rule // '[<hierarchy>].[All <hierarchy>s]'. + "{[Gender with default].[F]}\n" + "Row #0: 131,558\n"); } public void testHierarchyNoLevelsFails() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name='Gender no levels' foreignKey='customer_id'>\n" + " <Hierarchy hasAll='true' primaryKey='customer_id'>\n" + " <Table name='customer'/>\n" + " </Hierarchy>\n" + " </Dimension>"); testContext.assertQueryThrows("select {[Gender no levels]} on columns from [Sales]", "Hierarchy '[Gender no levels]' must have at least one level."); } public void testHierarchyNonUniqueLevelsFails() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name='Gender dup levels' foreignKey='customer_id'>\n" + " <Hierarchy hasAll='true' primaryKey='customer_id'>\n" + " <Table name='customer'/>\n" + " <Level name='Gender' column='gender' uniqueMembers='true' />\n" + " <Level name='Gender' column='gender' uniqueMembers='true' />\n" + " </Hierarchy>\n" + " </Dimension>"); testContext.assertQueryThrows("select {[Gender dup levels]} on columns from [Sales]", "Level names within hierarchy '[Gender dup levels]' are not unique; there is more than one level with name 'Gender'."); } /** * Tests a measure based on 'count'. */ public void testCountMeasure() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", null, "<Measure name=\"Fact Count\" aggregator=\"count\"/>\n"); testContext.assertQueryReturns( "select {[Measures].[Fact Count], [Measures].[Unit Sales]} on 0,\n" + "[Gender].members on 1\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Fact Count]}\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Gender].[All Gender]}\n" + "{[Gender].[F]}\n" + "{[Gender].[M]}\n" + "Row #0: 86,837\n" + "Row #0: 266,773\n" + "Row #1: 42,831\n" + "Row #1: 131,558\n" + "Row #2: 44,006\n" + "Row #2: 135,215\n"); } /** * Tests that an error occurs if a hierarchy is based on a non-existent * table. */ public void testHierarchyTableNotFound() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income3\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer_not_found\"/>\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); // FIXME: This should validate the schema, and fail. testContext.assertSimpleQuery(); // FIXME: Should give better error. testContext.assertQueryThrows("select [Yearly Income3].Children on 0 from [Sales]", "Internal error: while building member cache"); } public void testPrimaryKeyTableNotFound() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income4\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\" primaryKeyTable=\"customer_not_found\">\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryThrows("select from [Sales]", "no table 'customer_not_found' found in hierarchy [Yearly Income4]"); } public void testLevelTableNotFound() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income5\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Yearly Income\" table=\"customer_not_found\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryThrows("select from [Sales]", "Table 'customer_not_found' not found"); } public void testHierarchyBadDefaultMember() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Gender with default\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" " + "primaryKey=\"customer_id\" " // Default member unique name does not include 'All'. + "defaultMember=\"[Gender with default].[Non].[Existent]\" >\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\" />\n" + " </Hierarchy>\n" + " </Dimension>"); testContext.assertQueryThrows("select {[Gender with default]} on columns from [Sales]", "Can not find Default Member with name \"[Gender with default].[Non].[Existent]\" in Hierarchy \"Gender with default\""); } /** * WG: Note, this no longer throws an exception with the new RolapCubeMember * functionality. * * <p>Tests that an error is issued if two dimensions use the same table via * different drill-paths and do not use a different alias. If this error is * not issued, the generated SQL can be missing a join condition, as in * <a href="http://jira.pentaho.com/browse/MONDRIAN-236"> * Bug MONDRIAN-236, "Mondrian generates invalid SQL"</a>. */ public void testDuplicateTableAlias() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income2\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryReturns( "select {[Yearly Income2]} on columns, {[Measures].[Unit Sales]} on rows from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Yearly Income2].[All Yearly Income2s]}\n" + "Axis #2:\n" + "{[Measures].[Unit Sales]}\n" + "Row #0: 266,773\n"); } /** * This result is somewhat peculiar. If two dimensions share a foreign key, * what is the expected result? Also, in this case, they share the same * table without an alias, and the system doesn't complain. */ public void testDuplicateTableAliasSameForeignKey() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income2\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryReturns("select from [Sales]", "Axis #0:\n" + "{}\n" + "266,773"); // NonEmptyCrossJoin Fails if (false) { testContext.assertQueryReturns( "select NonEmptyCrossJoin({[Yearly Income2].[All Yearly Income2s]},{[Customers].[All Customers]}) on rows," + "NON EMPTY {[Measures].[Unit Sales]} on columns" + " from [Sales]", "Axis #0:\n" + "{}\n" + "266,773"); } } /** * Tests two dimensions using same table (via different join paths). * Without the table alias, generates SQL which is missing a join condition. * See {@link #testDuplicateTableAlias()}. */ public void testDimensionsShareTable() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income2\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer\" alias=\"customerx\" />\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryReturns( "select {[Yearly Income].[$10K - $30K]} on columns," + "{[Yearly Income2].[$150K +]} on rows from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Yearly Income].[$10K - $30K]}\n" + "Axis #2:\n" + "{[Yearly Income2].[$150K +]}\n" + "Row #0: 918\n"); testContext.assertQueryReturns( "select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,\n" + "NON EMPTY Crossjoin({[Yearly Income].[All Yearly Incomes].Children},\n" + " [Yearly Income2].[All Yearly Income2s].Children) ON ROWS\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$90K - $110K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$90K - $110K]}\n" + "Row #0: 12,824\n" + "Row #1: 2,822\n" + "Row #2: 2,933\n" + "Row #3: 918\n" + "Row #4: 18,381\n" + "Row #5: 10,436\n" + "Row #6: 6,777\n" + "Row #7: 2,859\n" + "Row #8: 2,432\n" + "Row #9: 532\n" + "Row #10: 566\n" + "Row #11: 177\n" + "Row #12: 3,877\n" + "Row #13: 2,131\n" + "Row #14: 1,319\n" + "Row #15: 527\n" + "Row #16: 3,331\n" + "Row #17: 643\n" + "Row #18: 703\n" + "Row #19: 187\n" + "Row #20: 4,497\n" + "Row #21: 2,629\n" + "Row #22: 1,681\n" + "Row #23: 721\n" + "Row #24: 1,123\n" + "Row #25: 224\n" + "Row #26: 257\n" + "Row #27: 109\n" + "Row #28: 1,924\n" + "Row #29: 1,026\n" + "Row #30: 675\n" + "Row #31: 291\n" + "Row #32: 19,067\n" + "Row #33: 4,078\n" + "Row #34: 4,235\n" + "Row #35: 1,569\n" + "Row #36: 28,160\n" + "Row #37: 15,368\n" + "Row #38: 10,329\n" + "Row #39: 4,504\n" + "Row #40: 9,708\n" + "Row #41: 2,353\n" + "Row #42: 2,243\n" + "Row #43: 748\n" + "Row #44: 14,469\n" + "Row #45: 7,966\n" + "Row #46: 5,272\n" + "Row #47: 2,208\n" + "Row #48: 7,320\n" + "Row #49: 1,630\n" + "Row #50: 1,602\n" + "Row #51: 541\n" + "Row #52: 10,550\n" + "Row #53: 5,843\n" + "Row #54: 3,997\n" + "Row #55: 1,562\n" + "Row #56: 2,722\n" + "Row #57: 597\n" + "Row #58: 568\n" + "Row #59: 193\n" + "Row #60: 3,800\n" + "Row #61: 2,192\n" + "Row #62: 1,324\n" + "Row #63: 523\n"); } /** * Tests two dimensions using same table (via different join paths). * native non empty cross join sql generation returns empty query. * note that this works when native cross join is disabled */ public void testDimensionsShareTableNativeNonEmptyCrossJoin() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income2\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer\" alias=\"customerx\" />\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryReturns( "select NonEmptyCrossJoin({[Yearly Income2].[All Yearly Income2s]},{[Customers].[All Customers]}) on rows," + "NON EMPTY {[Measures].[Unit Sales]} on columns" + " from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Yearly Income2].[All Yearly Income2s], [Customers].[All Customers]}\n" + "Row #0: 266,773\n"); } /** * Tests two dimensions using same table with same foreign key * one table uses an alias. */ public void testDimensionsShareTableSameForeignKeys() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Yearly Income2\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Table name=\"customer\" alias=\"customerx\" />\n" + " <Level name=\"Yearly Income\" column=\"yearly_income\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>"); testContext.assertQueryReturns( "select {[Yearly Income].[$10K - $30K]} on columns," + "{[Yearly Income2].[$150K +]} on rows from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Yearly Income].[$10K - $30K]}\n" + "Axis #2:\n" + "{[Yearly Income2].[$150K +]}\n" + "Row #0: \n"); testContext.assertQueryReturns( "select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,\n" + "NON EMPTY Crossjoin({[Yearly Income].[All Yearly Incomes].Children},\n" + " [Yearly Income2].[All Yearly Income2s].Children) ON ROWS\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Yearly Income].[$10K - $30K], [Yearly Income2].[$10K - $30K]}\n" + "{[Yearly Income].[$110K - $130K], [Yearly Income2].[$110K - $130K]}\n" + "{[Yearly Income].[$130K - $150K], [Yearly Income2].[$130K - $150K]}\n" + "{[Yearly Income].[$150K +], [Yearly Income2].[$150K +]}\n" + "{[Yearly Income].[$30K - $50K], [Yearly Income2].[$30K - $50K]}\n" + "{[Yearly Income].[$50K - $70K], [Yearly Income2].[$50K - $70K]}\n" + "{[Yearly Income].[$70K - $90K], [Yearly Income2].[$70K - $90K]}\n" + "{[Yearly Income].[$90K - $110K], [Yearly Income2].[$90K - $110K]}\n" + "Row #0: 57,950\n" + "Row #1: 11,561\n" + "Row #2: 14,392\n" + "Row #3: 5,629\n" + "Row #4: 87,310\n" + "Row #5: 44,967\n" + "Row #6: 33,045\n" + "Row #7: 11,919\n"); } /** * test hierarchy with completely different join path to fact table than * first hierarchy. tables are auto-aliased as necessary to guarantee * unique joins to the fact table. */ public void testSnowflakeHierarchyValidationNotNeeded() { // this test breaks when using aggregates at the moment // due to a known limitation if ((MondrianProperties.instance().ReadAggregates.get() || MondrianProperties.instance().UseAggregates.get()) && !Bug.BugMondrian361Fixed) { return; } final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <Dimension name=\"Store\" foreignKey=\"store_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Join leftKey=\"sales_district_id\" rightKey=\"promotion_id\">\n" + " <Table name=\"region\"/>\n" + " <Table name=\"promotion\"/>\n" + " </Join>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\"/>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_region\" />\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" />\n" + " </Hierarchy>\n" + " <Hierarchy name=\"MyHierarchy\" hasAll=\"true\" primaryKeyTable=\"customer\" primaryKey=\"customer_id\">\n" + " <Join leftKey=\"customer_region_id\" rightKey=\"region_id\">\n" + " <Table name=\"customer\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Country\" table=\"customer\" column=\"country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Region\" table=\"region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" table=\"customer\" column=\"city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Name\" table=\"customer\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Customers\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKeyTable=\"customer\" primaryKey=\"customer_id\">\n" + " <Join leftKey=\"customer_region_id\" rightKey=\"region_id\">\n" + " <Table name=\"customer\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Country\" table=\"customer\" column=\"country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Region\" table=\"region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" table=\"customer\" column=\"city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Name\" table=\"customer\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[Store.MyHierarchy].[Mexico]} on rows," + "{[Customers].[USA].[South West]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Customers].[USA].[South West]}\n" + "Axis #2:\n" + "{[Store].[MyHierarchy].[Mexico]}\n" + "Row #0: 51,298\n"); } /** * test hierarchy with slightly different join path to fact table than * first hierarchy. tables from first and second hierarchy should contain * the same join aliases to the fact table. */ public void testSnowflakeHierarchyValidationNotNeeded2() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude pattern=\"agg_lc_06_sales_fact_1997\"/>\n" + " </Table>" + " <Dimension name=\"Store\" foreignKey=\"store_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Join leftKey=\"sales_district_id\" rightKey=\"promotion_id\">\n" + " <Table name=\"region\"/>\n" + " <Table name=\"promotion\"/>\n" + " </Join>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\"/>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_region\" />\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" />\n" + " </Hierarchy>\n" + " <Hierarchy name=\"MyHierarchy\" hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\"/>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_region\" />\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" />\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Customers\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKeyTable=\"customer\" primaryKey=\"customer_id\">\n" + " <Join leftKey=\"customer_region_id\" rightKey=\"region_id\">\n" + " <Table name=\"customer\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Country\" table=\"customer\" column=\"country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Region\" table=\"region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" table=\"customer\" column=\"city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Name\" table=\"customer\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[Store.MyHierarchy].[USA].[South West]} on rows," + "{[Customers].[USA].[South West]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Customers].[USA].[South West]}\n" + "Axis #2:\n" + "{[Store].[MyHierarchy].[USA].[South West]}\n" + "Row #0: 72,631\n"); } /** * WG: This no longer throws an exception, it is now possible * * Tests two dimensions using same table (via different join paths). * both using a table alias. */ public void testDimensionsShareJoinTable() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude pattern=\"agg_lc_06_sales_fact_1997\"/>\n" + " </Table>" + "<Dimension name=\"Store\" foreignKey=\"store_id\">\n" + "<Hierarchy hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Dimension name=\"Customers\" foreignKey=\"customer_id\">\n" + "<Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKeyTable=\"customer\" primaryKey=\"customer_id\">\n" + " <Join leftKey=\"customer_region_id\" rightKey=\"region_id\">\n" + " <Table name=\"customer\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Country\" table=\"customer\" column=\"country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Region\" table=\"region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" table=\"customer\" column=\"city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Name\" table=\"customer\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "<Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\" formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[Store].[USA].[South West]} on rows," + "{[Customers].[USA].[South West]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Customers].[USA].[South West]}\n" + "Axis #2:\n" + "{[Store].[USA].[South West]}\n" + "Row #0: 72,631\n"); } /** * Tests two dimensions using same table (via different join paths). * both using a table alias. */ public void testDimensionsShareJoinTableOneAlias() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude pattern=\"agg_lc_06_sales_fact_1997\"/>\n" + " </Table>" + "<Dimension name=\"Store\" foreignKey=\"store_id\">\n" + "<Hierarchy hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Dimension name=\"Customers\" foreignKey=\"customer_id\">\n" + "<Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKeyTable=\"customer\" primaryKey=\"customer_id\">\n" + " <Join leftKey=\"customer_region_id\" rightKey=\"region_id\">\n" + " <Table name=\"customer\"/>\n" + " <Table name=\"region\" alias=\"customer_region\"/>\n" + " </Join>\n" + " <Level name=\"Country\" table=\"customer\" column=\"country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Region\" table=\"customer_region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" table=\"customer\" column=\"city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Name\" table=\"customer\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "<Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\" formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[Store].[USA].[South West]} on rows," + "{[Customers].[USA].[South West]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Customers].[USA].[South West]}\n" + "Axis #2:\n" + "{[Store].[USA].[South West]}\n" + "Row #0: 72,631\n"); } /** * Tests two dimensions using same table (via different join paths). * both using a table alias. */ public void testDimensionsShareJoinTableTwoAliases() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude pattern=\"agg_lc_06_sales_fact_1997\"/>\n" + " </Table>" + "<Dimension name=\"Store\" foreignKey=\"store_id\">\n" + "<Hierarchy hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Table name=\"region\" alias=\"store_region\"/>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Store Region\" table=\"store_region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Dimension name=\"Customers\" foreignKey=\"customer_id\">\n" + "<Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKeyTable=\"customer\" primaryKey=\"customer_id\">\n" + " <Join leftKey=\"customer_region_id\" rightKey=\"region_id\">\n" + " <Table name=\"customer\"/>\n" + " <Table name=\"region\" alias=\"customer_region\"/>\n" + " </Join>\n" + " <Level name=\"Country\" table=\"customer\" column=\"country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"Region\" table=\"customer_region\" column=\"sales_region\" uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" table=\"customer\" column=\"city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Name\" table=\"customer\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "<Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\" formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[Store].[USA].[South West]} on rows," + "{[Customers].[USA].[South West]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Customers].[USA].[South West]}\n" + "Axis #2:\n" + "{[Store].[USA].[South West]}\n" + "Row #0: 72,631\n"); } /** * Tests two dimensions using same table (via different join paths). * both using a table alias. */ public void testTwoAliasesDimensionsShareTable() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"inventory_fact_1997\"/>\n" + " <Dimension name=\"StoreA\" foreignKey=\"store_id\">" + " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">" + " <Table name=\"store\" alias=\"storea\"/>" + " <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>" + " <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\"/>" + " </Hierarchy>" + " </Dimension>" + " <Dimension name=\"StoreB\" foreignKey=\"warehouse_id\">" + " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">" + " <Table name=\"store\" alias=\"storeb\"/>" + " <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>" + " <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\"/>" + " </Hierarchy>" + " </Dimension>" + " <Measure name=\"Store Invoice\" column=\"store_invoice\" " + "aggregator=\"sum\"/>\n" + " <Measure name=\"Supply Time\" column=\"supply_time\" " + "aggregator=\"sum\"/>\n" + " <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" " + "aggregator=\"sum\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[StoreA].[USA]} on rows," + "{[StoreB].[USA]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[StoreB].[USA]}\n" + "Axis #2:\n" + "{[StoreA].[USA]}\n" + "Row #0: 10,425\n"); } /** * Tests two dimensions using same table with same foreign key. * both using a table alias. */ public void testTwoAliasesDimensionsShareTableSameForeignKeys() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"AliasedDimensionsTesting\" defaultMeasure=\"Supply Time\">\n" + " <Table name=\"inventory_fact_1997\"/>\n" + " <Dimension name=\"StoreA\" foreignKey=\"store_id\">" + " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">" + " <Table name=\"store\" alias=\"storea\"/>" + " <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>" + " <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\"/>" + " </Hierarchy>" + " </Dimension>" + " <Dimension name=\"StoreB\" foreignKey=\"store_id\">" + " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">" + " <Table name=\"store\" alias=\"storeb\"/>" + " <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>" + " <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\"/>" + " </Hierarchy>" + " </Dimension>" + " <Measure name=\"Store Invoice\" column=\"store_invoice\" " + "aggregator=\"sum\"/>\n" + " <Measure name=\"Supply Time\" column=\"supply_time\" " + "aggregator=\"sum\"/>\n" + " <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" " + "aggregator=\"sum\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select {[StoreA].[USA]} on rows," + "{[StoreB].[USA]} on columns" + " from " + "AliasedDimensionsTesting", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[StoreB].[USA]}\n" + "Axis #2:\n" + "{[StoreA].[USA]}\n" + "Row #0: 10,425\n"); } /** * Test Multiple DimensionUsages on same Dimension. * Alias the fact table to avoid issues with aggregation rules * and multiple column names */ public void testMultipleDimensionUsages() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Two Dimensions\">\n" + " <Table name=\"sales_fact_1997\" alias=\"sales_fact_1997_mdu\"/>\n" + " <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Time2\" source=\"Time\" foreignKey=\"product_id\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select\n" + " {[Time2].[1997]} on columns,\n" + " {[Time].[1997].[Q3]} on rows\n" + "From [Sales Two Dimensions]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + (MondrianProperties.instance().SsasCompatibleNaming.get() ? "{[Time2].[Time].[1997]}\n" : "{[Time2].[1997]}\n") + "Axis #2:\n" + "{[Time].[1997].[Q3]}\n" + "Row #0: 16,266\n"); } /** * Test Multiple DimensionUsages on same Dimension. * Alias the fact table to avoid issues with aggregation rules * and multiple column names */ public void testMultipleDimensionHierarchyCaptionUsages() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Two Dimensions\">\n" + " <Table name=\"sales_fact_1997\" alias=\"sales_fact_1997_mdu\"/>\n" + " <DimensionUsage name=\"Time\" caption=\"TimeOne\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Time2\" caption=\"TimeTwo\" source=\"Time\" foreignKey=\"product_id\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); String query = "select\n" + " {[Time2].[1997]} on columns,\n" + " {[Time].[1997].[Q3]} on rows\n" + "From [Sales Two Dimensions]"; Result result = testContext.executeQuery(query); // Time2.1997 Member Member member1 = result.getAxes()[0].getPositions().iterator().next().iterator().next(); // NOTE: The caption is modified at the dimension, not the hierarchy assertEquals("TimeTwo", member1.getLevel().getDimension().getCaption()); Member member2 = result.getAxes()[1].getPositions().iterator().next().iterator().next(); assertEquals("TimeOne", member2.getLevel().getDimension().getCaption()); } /** * This test verifies that the createDimension() API call is working * correctly. */ public void testDimensionCreation() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Create Dimension\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); Cube cube = testContext.getConnection().getSchema().lookupCube("Sales Create Dimension", true); testContext.assertQueryReturns( "select\n" + "NON EMPTY {[Store].[All Stores].children} on columns \n" + "From [Sales Create Dimension]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA]}\n" + "Row #0: 266,773\n"); String dimension = "<DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>"; testContext.getConnection().getSchema().createDimension(cube, dimension); testContext.assertQueryReturns( "select\n" + "NON EMPTY {[Store].[All Stores].children} on columns, \n" + "{[Time].[1997].[Q1]} on rows \n" + "From [Sales Create Dimension]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA]}\n" + "Axis #2:\n" + "{[Time].[1997].[Q1]}\n" + "Row #0: 66,291\n"); } /** * Test DimensionUsage level attribute */ public void testDimensionUsageLevel() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Customer Usage Level\">\n" + " <Table name=\"customer\"/>\n" // + alias=\"sales_fact_1997_multi\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" level=\"Store State\" foreignKey=\"state_province\"/>\n" + " <Measure name=\"Cars\" column=\"num_cars_owned\" aggregator=\"sum\"/>\n" + " <Measure name=\"Children\" column=\"total_children\" aggregator=\"sum\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select\n" + " {[Store].[Store State].members} on columns \n" + "From [Customer Usage Level]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[Canada].[BC]}\n" + "{[Store].[Mexico].[DF]}\n" + "{[Store].[Mexico].[Guerrero]}\n" + "{[Store].[Mexico].[Jalisco]}\n" + "{[Store].[Mexico].[Veracruz]}\n" + "{[Store].[Mexico].[Yucatan]}\n" + "{[Store].[Mexico].[Zacatecas]}\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[WA]}\n" + "Row #0: 7,700\n" + "Row #0: 1,492\n" + "Row #0: 228\n" + "Row #0: 206\n" + "Row #0: 195\n" + "Row #0: 229\n" + "Row #0: 1,209\n" + "Row #0: 46,965\n" + "Row #0: 4,686\n" + "Row #0: 32,767\n"); // BC.children should return an empty list, considering that we've // joined Store at the State level. if (false) { testContext .assertQueryReturns("select\n" + " {[Store].[All Stores].[Canada].[BC].children} on columns \n" + "From [Customer Usage Level]", "Axis #0:\n" + "{}\n" + "Axis #1:\n"); } } /** * Test to verify naming of all member with * dimension usage name is different then source name */ public void testAllMemberMultipleDimensionUsages() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Two Sales Dimensions\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <DimensionUsage name=\"Store\" caption=\"First Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <DimensionUsage name=\"Store2\" caption=\"Second Store\" source=\"Store\" foreignKey=\"product_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); // If SsasCompatibleNaming (the new behavior), the usages of the // [Store] dimension create dimensions called [Store] // and [Store2], each with a hierarchy called [Store]. // Therefore Store2's all member is [Store2].[Store].[All Stores], // or [Store2].[All Stores] for short. // // Under the old behavior, the member is called [Store2].[All Store2s]. final String store2AllMember = MondrianProperties.instance().SsasCompatibleNaming.get() ? "[Store2].[All Stores]" : "[Store2].[All Store2s]"; testContext.assertQueryReturns( "select\n" + " {[Store].[Store].[All Stores]} on columns,\n" + " {" + store2AllMember + "} on rows\n" + "From [Sales Two Sales Dimensions]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[All Stores]}\n" + "Axis #2:\n" + "{[Store2].[Store].[All Stores]}\n" + "Row #0: 266,773\n"); final Result result = testContext.executeQuery("select ([Store].[All Stores], " + store2AllMember + ") on 0\n" + "from [Sales Two Sales Dimensions]"); final Axis axis = result.getAxes()[0]; final Position position = axis.getPositions().get(0); assertEquals("First Store", position.get(0).getDimension().getCaption()); assertEquals("Second Store", position.get(1).getDimension().getCaption()); } /** * This test displays an informative error message if someone uses * an unaliased name instead of an aliased name */ public void testNonAliasedDimensionUsage() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Two Dimensions\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <DimensionUsage name=\"Time2\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); final String query = "select\n" + " {[Time].[1997]} on columns \n" + "From [Sales Two Dimensions]"; if (!MondrianProperties.instance().SsasCompatibleNaming.get()) { testContext.assertQueryThrows(query, "In cube \"Sales Two Dimensions\" use of unaliased Dimension name \"[Time]\" rather than the alias name \"Time2\""); } else { // In new behavior, resolves to the hierarchy name [Time] even if // not qualified by dimension name [Time2]. testContext.assertQueryReturns(query, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Time2].[Time].[1997]}\n" + "Row #0: 266,773\n"); } } /** * Tests a cube whose fact table is a <View> element as well as a * degenerate dimension. */ public void testViewDegenerateDims() { final TestContext testContext = TestContext.instance().create(null, // Warehouse cube where the default member in the Warehouse // dimension is USA. "<Cube name=\"Warehouse (based on view)\">\n" + " <View alias=\"FACT\">\n" + " <SQL dialect=\"generic\">\n" + " <![CDATA[select * from \"inventory_fact_1997\" as \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"oracle\">\n" + " <![CDATA[select * from \"inventory_fact_1997\" \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"mysql\">\n" + " <![CDATA[select * from `inventory_fact_1997` as `FOOBAR`]]>\n" + " </SQL>\n" + " <SQL dialect=\"infobright\">\n" + " <![CDATA[select * from `inventory_fact_1997` as `FOOBAR`]]>\n" + " </SQL>\n" + " </View>\n" + " <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Dimension name=\"Warehouse\">\n" + " <Hierarchy hasAll=\"true\"> \n" + " <View alias=\"FACT\">\n" + " <SQL dialect=\"generic\">\n" + " <![CDATA[select * from \"inventory_fact_1997\" as \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"oracle\">\n" + " <![CDATA[select * from \"inventory_fact_1997\" \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"mysql\">\n" + " <![CDATA[select * from `inventory_fact_1997` as `FOOBAR`]]>\n" + " </SQL>\n" + " <SQL dialect=\"infobright\">\n" + " <![CDATA[select * from `inventory_fact_1997` as `FOOBAR`]]>\n" + " </SQL>\n" + " </View>\n" + " <Level name=\"Warehouse ID\" column=\"warehouse_id\"\n" + " uniqueMembers=\"true\" type=\"Numeric\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" aggregator=\"sum\"/>\n" + " <Measure name=\"Warehouse Sales\" column=\"warehouse_sales\" aggregator=\"sum\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select\n" + " NON EMPTY {[Time].[1997], [Time].[1997].[Q3]} on columns,\n" + " NON EMPTY {[Store].[USA].Children} on rows\n" + "From [Warehouse (based on view)]\n" + "where [Warehouse].[2]", "Axis #0:\n" + "{[Warehouse].[2]}\n" + "Axis #1:\n" + "{[Time].[1997]}\n" + "Axis #2:\n" + "{[Store].[USA].[WA]}\n" + "Row #0: 917.554\n"); } /** * Tests a cube whose fact table is a <View> element. */ public void testViewFactTable() { final TestContext testContext = TestContext.instance().create(null, // Warehouse cube where the default member in the Warehouse // dimension is USA. "<Cube name=\"Warehouse (based on view)\">\n" + " <View alias=\"FACT\">\n" + " <SQL dialect=\"generic\">\n" + " <![CDATA[select * from \"inventory_fact_1997\" as \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"oracle\">\n" + " <![CDATA[select * from \"inventory_fact_1997\" \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"mysql\">\n" + " <![CDATA[select * from `inventory_fact_1997` as `FOOBAR`]]>\n" + " </SQL>\n" + " <SQL dialect=\"infobright\">\n" + " <![CDATA[select * from `inventory_fact_1997` as `FOOBAR`]]>\n" + " </SQL>\n" + " </View>\n" + " <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Dimension name=\"Warehouse\" foreignKey=\"warehouse_id\">\n" + " <Hierarchy hasAll=\"false\" defaultMember=\"[USA]\" primaryKey=\"warehouse_id\"> \n" + " <Table name=\"warehouse\"/>\n" + " <Level name=\"Country\" column=\"warehouse_country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"State Province\" column=\"warehouse_state_province\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" column=\"warehouse_city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Warehouse Name\" column=\"warehouse_name\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" aggregator=\"sum\"/>\n" + " <Measure name=\"Warehouse Sales\" column=\"warehouse_sales\" aggregator=\"sum\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select\n" + " {[Time].[1997], [Time].[1997].[Q3]} on columns,\n" + " {[Store].[USA].Children} on rows\n" + "From [Warehouse (based on view)]\n" + "where [Warehouse].[USA]", "Axis #0:\n" + "{[Warehouse].[USA]}\n" + "Axis #1:\n" + "{[Time].[1997]}\n" + "{[Time].[1997].[Q3]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[WA]}\n" + "Row #0: 25,789.086\n" + "Row #0: 8,624.791\n" + "Row #1: 17,606.904\n" + "Row #1: 3,812.023\n" + "Row #2: 45,647.262\n" + "Row #2: 12,664.162\n"); } /** * Tests a cube whose fact table is a <View> element, and which * has dimensions based on the fact table. */ public void testViewFactTable2() { final TestContext testContext = TestContext.instance().create(null, // Similar to "Store" cube in FoodMart.xml. "<Cube name=\"Store2\">\n" + " <View alias=\"FACT\">\n" + " <SQL dialect=\"generic\">\n" + " <![CDATA[select * from \"store\" as \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"oracle\">\n" + " <![CDATA[select * from \"store\" \"FOOBAR\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"mysql\">\n" + " <![CDATA[select * from `store` as `FOOBAR`]]>\n" + " </SQL>\n" + " <SQL dialect=\"infobright\">\n" + " <![CDATA[select * from `store` as `FOOBAR`]]>\n" + " </SQL>\n" + " </View>\n" + " <!-- We could have used the shared dimension \"Store Type\", but we\n" + " want to test private dimensions without primary key. -->\n" + " <Dimension name=\"Store Type\">\n" + " <Hierarchy hasAll=\"true\">\n" + " <Level name=\"Store Type\" column=\"store_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + "\n" + " <Measure name=\"Store Sqft\" column=\"store_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + " <Measure name=\"Grocery Sqft\" column=\"grocery_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns("select {[Store Type].Children} on columns from [Store2]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store Type].[Deluxe Supermarket]}\n" + "{[Store Type].[Gourmet Supermarket]}\n" + "{[Store Type].[HeadQuarters]}\n" + "{[Store Type].[Mid-Size Grocery]}\n" + "{[Store Type].[Small Grocery]}\n" + "{[Store Type].[Supermarket]}\n" + "Row #0: 146,045\n" + "Row #0: 47,447\n" + "Row #0: \n" + "Row #0: 109,343\n" + "Row #0: 75,281\n" + "Row #0: 193,480\n"); } /** * Tests that the deprecated "distinct count" value for the * Measure@aggregator attribute still works. The preferred value these days * is "distinct-count". */ public void testDeprecatedDistinctCountAggregator() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", null, " <Measure name=\"Customer Count2\" column=\"customer_id\"\n" + " aggregator=\"distinct count\" formatString=\"#,###\"/>\n" + " <CalculatedMember\n" + " name=\"Half Customer Count\"\n" + " dimension=\"Measures\"\n" + " visible=\"false\"\n" + " formula=\"[Measures].[Customer Count2] / 2\">\n" + " </CalculatedMember>"); testContext.assertQueryReturns( "select {[Measures].[Unit Sales]," + " [Measures].[Customer Count], " + " [Measures].[Customer Count2], " + " [Measures].[Half Customer Count]} on 0,\n" + " {[Store].[USA].Children} ON 1\n" + "FROM [Sales]\n" + "WHERE ([Gender].[M])", "Axis #0:\n" + "{[Gender].[M]}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "{[Measures].[Customer Count]}\n" + "{[Measures].[Customer Count2]}\n" + "{[Measures].[Half Customer Count]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[WA]}\n" + "Row #0: 37,989\n" + "Row #0: 1,389\n" + "Row #0: 1,389\n" + "Row #0: 695\n" + "Row #1: 34,623\n" + "Row #1: 536\n" + "Row #1: 536\n" + "Row #1: 268\n" + "Row #2: 62,603\n" + "Row #2: 901\n" + "Row #2: 901\n" + "Row #2: 451\n"); } /** * Tests that an invalid aggregator causes an error. */ public void testInvalidAggregator() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", null, " <Measure name=\"Customer Count3\" column=\"customer_id\"\n" + " aggregator=\"invalidAggregator\" formatString=\"#,###\"/>\n" + " <CalculatedMember\n" + " name=\"Half Customer Count\"\n" + " dimension=\"Measures\"\n" + " visible=\"false\"\n" + " formula=\"[Measures].[Customer Count2] / 2\">\n" + " </CalculatedMember>"); testContext.assertQueryThrows("select from [Sales]", "Unknown aggregator 'invalidAggregator'; valid aggregators are: 'sum', 'count', 'min', 'max', 'avg', 'distinct-count'"); } /** * Testcase for * <a href="http://jira.pentaho.com/browse/MONDRIAN-291"> * Bug MONDRIAN-291, "'unknown usage' messages"</a>. */ public void testUnknownUsages() { if (!MondrianProperties.instance().ReadAggregates.get()) { return; } final Logger logger = Logger.getLogger(AggTableManager.class); propSaver.setAtLeast(logger, org.apache.log4j.Level.WARN); final StringWriter sw = new StringWriter(); final Appender appender = new WriterAppender(new SimpleLayout(), sw); final LevelRangeFilter filter = new LevelRangeFilter(); filter.setLevelMin(org.apache.log4j.Level.WARN); appender.addFilter(filter); logger.addAppender(appender); try { final TestContext testContext = TestContext.instance().withSchema("<?xml version=\"1.0\"?>\n" + "<Schema name=\"FoodMart\">\n" + "<Cube name=\"Sales Degen\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude pattern=\"agg_c_14_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_l_05_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_g_ms_pcat_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_ll_01_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_c_special_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_l_03_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_l_04_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_pl_01_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_lc_06_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_lc_100_sales_fact_1997\"/>\n" + " <AggName name=\"agg_c_10_sales_fact_1997\">\n" + " <AggFactCount column=\"fact_count\"/>\n" + " <AggMeasure name=\"[Measures].[Store Cost]\" column=\"store_cost\" />\n" + " <AggMeasure name=\"[Measures].[Store Sales]\" column=\"store_sales\" />\n" + " </AggName>\n" + " </Table>\n" + " <Dimension name=\"Time\" type=\"TimeDimension\" foreignKey=\"time_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"time_id\">\n" + " <Table name=\"time_by_day\"/>\n" + " <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\"\n" + " levelType=\"TimeYears\"/>\n" + " <Level name=\"Quarter\" column=\"quarter\" uniqueMembers=\"false\"\n" + " levelType=\"TimeQuarters\"/>\n" + " <Level name=\"Month\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\"\n" + " levelType=\"TimeMonths\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Time Degenerate\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"time_id\">\n" + " <Level name=\"day\" column=\"time_id\"/>\n" + " <Level name=\"month\" column=\"product_id\" type=\"Numeric\"/>\n" + " </Hierarchy>" + " </Dimension>" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n" + " formatString=\"#,###.00\"/>\n" + " <Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\"\n" + " formatString=\"#,###.00\"/>\n" + "</Cube>\n" + "</Schema>"); testContext.assertQueryReturns("select from [Sales Degen]", "Axis #0:\n" + "{}\n" + "225,627.23"); } finally { logger.removeAppender(appender); } // Note that 'product_id' is NOT one of the columns with unknown usage. // It is used as a level in the degenerate dimension [Time Degenerate]. TestContext.assertEqualsVerbose( "WARN - Recognizer.checkUnusedColumns: Candidate aggregate table 'agg_c_10_sales_fact_1997' for fact table 'sales_fact_1997' has a column 'customer_count' with unknown usage.\n" + "WARN - Recognizer.checkUnusedColumns: Candidate aggregate table 'agg_c_10_sales_fact_1997' for fact table 'sales_fact_1997' has a column 'month_of_year' with unknown usage.\n" + "WARN - Recognizer.checkUnusedColumns: Candidate aggregate table 'agg_c_10_sales_fact_1997' for fact table 'sales_fact_1997' has a column 'quarter' with unknown usage.\n" + "WARN - Recognizer.checkUnusedColumns: Candidate aggregate table 'agg_c_10_sales_fact_1997' for fact table 'sales_fact_1997' has a column 'the_year' with unknown usage.\n" + "WARN - Recognizer.checkUnusedColumns: Candidate aggregate table 'agg_c_10_sales_fact_1997' for fact table 'sales_fact_1997' has a column 'unit_sales' with unknown usage.\n", sw.toString()); } public void testUnknownUsages1() { if (!MondrianProperties.instance().ReadAggregates.get()) { return; } final Logger logger = Logger.getLogger(AggTableManager.class); propSaver.setAtLeast(logger, org.apache.log4j.Level.WARN); final StringWriter sw = new StringWriter(); final Appender appender = new WriterAppender(new SimpleLayout(), sw); final LevelRangeFilter filter = new LevelRangeFilter(); filter.setLevelMin(org.apache.log4j.Level.WARN); appender.addFilter(filter); logger.addAppender(appender); try { final TestContext testContext = TestContext.instance().withSchema("<?xml version=\"1.0\"?>\n" + "<Schema name=\"FoodMart\">\n" + "<Cube name=\"Denormalized Sales\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude pattern=\"agg_c_14_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_l_05_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_g_ms_pcat_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_ll_01_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_c_special_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_l_04_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_pl_01_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_c_10_sales_fact_1997\"/>\n" + " <AggExclude pattern=\"agg_lc_06_sales_fact_1997\"/>\n" + " <AggName name=\"agg_l_03_sales_fact_1997\">\n" + " <AggFactCount column=\"fact_count\"/>\n" + " <AggMeasure name=\"[Measures].[Store Cost]\" column=\"store_cost\" />\n" + " <AggMeasure name=\"[Measures].[Store Sales]\" column=\"store_sales\" />\n" + " <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + " <AggLevel name=\"[Customer].[Customer ID]\" column=\"customer_id\" />\n" + " <AggForeignKey factColumn=\"time_id\" aggColumn=\"time_id\" />\n" + " </AggName>\n" + " </Table>\n" + " <Dimension name=\"Time\" type=\"TimeDimension\" foreignKey=\"time_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"time_id\">\n" + " <Table name=\"time_by_day\"/>\n" + " <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\"\n" + " levelType=\"TimeYears\"/>\n" + " <Level name=\"Quarter\" column=\"quarter\" uniqueMembers=\"false\"\n" + " levelType=\"TimeQuarters\"/>\n" + " <Level name=\"Month\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\"\n" + " levelType=\"TimeMonths\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Customer\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + " <Level name=\"Customer ID\" column=\"customer_id\"/>\n" + " </Hierarchy>" + " </Dimension>" + " <Dimension name=\"Product\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"product_id\">\n" + " <Level name=\"Product ID\" column=\"product_id\"/>\n" + " </Hierarchy>" + " </Dimension>" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n" + " formatString=\"#,###.00\"/>\n" + " <Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\"\n" + " formatString=\"#,###.00\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "</Cube>\n" + "</Schema>"); testContext.assertQueryReturns("select from [Denormalized Sales]", "Axis #0:\n" + "{}\n" + "225,627.23"); } finally { logger.removeAppender(appender); } TestContext.assertEqualsVerbose( "WARN - Recognizer.checkUnusedColumns: Candidate aggregate table 'agg_l_03_sales_fact_1997' for fact table 'sales_fact_1997' has a column 'time_id' with unknown usage.\n", sw.toString()); } public void testPropertyFormatter() { final TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Store2\" foreignKey=\"store_id\">\n" + " <Hierarchy name=\"Store2\" hasAll=\"true\" allMemberName=\"All Stores\" primaryKey=\"store_id\">\n" + " <Table name=\"store_ragged\"/>\n" + " <Level name=\"Store2\" table=\"store_ragged\" column=\"store_id\" captionColumn=\"store_name\" uniqueMembers=\"true\">\n" + " <Property name=\"Store Type\" column=\"store_type\" formatter=\"" + DummyPropertyFormatter.class.getName() + "\"/>" + " <Property name=\"Store Manager\" column=\"store_manager\"/>" + " </Level>" + " </Hierarchy>\n" + " </Dimension>\n"); try { testContext.assertSimpleQuery(); fail("expected exception"); } catch (RuntimeException e) { TestContext.checkThrowable(e, "Failed to load formatter class 'mondrian.test.SchemaTest$DummyPropertyFormatter' for property 'Store Type'."); } } /** * Bug <a href="http://jira.pentaho.com/browse/MONDRIAN-233">MONDRIAN-233, * "ClassCastException in AggQuerySpec"</a> occurs when two cubes * have the same fact table, distinct aggregate tables, and measures with * the same name. * * <p>This test case attempts to reproduce this issue by creating that * environment, but it found a different issue: a measure came back with a * cell value which was from a different measure. The root cause is * probably the same: when measures are registered in a star, they should * be qualified by cube name. */ public void testBugMondrian233() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales2\" defaultMeasure=\"Unit Sales\">" + " <Table name=\"sales_fact_1997\">\n" + " </Table>\n" + " <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); // With bug, and with aggregates enabled, query against Sales returns // 565,238, which is actually the total for [Store Sales]. I think the // aggregate tables are getting crossed. final String expected = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Row #0: 266,773\n"; testContext.assertQueryReturns("select {[Measures]} on 0 from [Sales2]", expected); testContext.assertQueryReturns("select {[Measures]} on 0 from [Sales]", expected); } /** * Test case for bug <a href="http://jira.pentaho.com/browse/MONDRIAN-303"> * MONDRIAN-303, "Property column shifting when use captionColumn"</a>. */ public void testBugMondrian303() { // In order to reproduce the problem a dimension specifying // captionColumn and Properties were required. TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Store2\" foreignKey=\"store_id\">\n" + " <Hierarchy name=\"Store2\" hasAll=\"true\" allMemberName=\"All Stores\" primaryKey=\"store_id\">\n" + " <Table name=\"store_ragged\"/>\n" + " <Level name=\"Store2\" table=\"store_ragged\" column=\"store_id\" captionColumn=\"store_name\" uniqueMembers=\"true\">\n" + " <Property name=\"Store Type\" column=\"store_type\"/>" + " <Property name=\"Store Manager\" column=\"store_manager\"/>" + " </Level>" + " </Hierarchy>\n" + " </Dimension>\n"); // In the query below Mondrian (prior to the fix) would // return the store name instead of the store type. testContext.assertQueryReturns("WITH\n" + " MEMBER [Measures].[StoreType] AS \n" + " '[Store2].CurrentMember.Properties(\"Store Type\")'\n" + "SELECT\n" + " NonEmptyCrossJoin({[Store2].[All Stores].children}, {[Product].[All Products]}) ON ROWS,\n" + " { [Measures].[Store Sales], [Measures].[StoreType]} ON COLUMNS\n" + "FROM Sales", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Store Sales]}\n" + "{[Measures].[StoreType]}\n" + "Axis #2:\n" + "{[Store2].[2], [Product].[All Products]}\n" + "{[Store2].[3], [Product].[All Products]}\n" + "{[Store2].[6], [Product].[All Products]}\n" + "{[Store2].[7], [Product].[All Products]}\n" + "{[Store2].[11], [Product].[All Products]}\n" + "{[Store2].[13], [Product].[All Products]}\n" + "{[Store2].[14], [Product].[All Products]}\n" + "{[Store2].[15], [Product].[All Products]}\n" + "{[Store2].[16], [Product].[All Products]}\n" + "{[Store2].[17], [Product].[All Products]}\n" + "{[Store2].[22], [Product].[All Products]}\n" + "{[Store2].[23], [Product].[All Products]}\n" + "{[Store2].[24], [Product].[All Products]}\n" + "Row #0: 4,739.23\n" + "Row #0: Small Grocery\n" + "Row #1: 52,896.30\n" + "Row #1: Supermarket\n" + "Row #2: 45,750.24\n" + "Row #2: Gourmet Supermarket\n" + "Row #3: 54,545.28\n" + "Row #3: Supermarket\n" + "Row #4: 55,058.79\n" + "Row #4: Supermarket\n" + "Row #5: 87,218.28\n" + "Row #5: Deluxe Supermarket\n" + "Row #6: 4,441.18\n" + "Row #6: Small Grocery\n" + "Row #7: 52,644.07\n" + "Row #7: Supermarket\n" + "Row #8: 49,634.46\n" + "Row #8: Supermarket\n" + "Row #9: 74,843.96\n" + "Row #9: Deluxe Supermarket\n" + "Row #10: 4,705.97\n" + "Row #10: Small Grocery\n" + "Row #11: 24,329.23\n" + "Row #11: Mid-Size Grocery\n" + "Row #12: 54,431.14\n" + "Row #12: Supermarket\n"); } public void testCubeWithOneDimensionOneMeasure() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"OneDim\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <Dimension name=\"Promotion Media\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Media\" primaryKey=\"promotion_id\" defaultMember=\"All Media\">\n" + " <Table name=\"promotion\"/>\n" + " <Level name=\"Media Type\" column=\"media_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns("select {[Promotion Media]} on columns from [OneDim]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Promotion Media].[All Media]}\n" + "Row #0: 266,773\n"); } public void testCubeWithOneDimensionUsageOneMeasure() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"OneDimUsage\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns("select {[Product].Children} on columns from [OneDimUsage]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Product].[Drink]}\n" + "{[Product].[Food]}\n" + "{[Product].[Non-Consumable]}\n" + "Row #0: 24,597\n" + "Row #0: 191,940\n" + "Row #0: 50,236\n"); } public void testCubeHasFact() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Cube with caption\" caption=\"Cube with name\"/>\n", null, null, null, null); Throwable throwable = null; try { testContext.assertSimpleQuery(); } catch (Throwable e) { throwable = e; } TestContext.checkThrowable(throwable, "Must specify fact table of cube 'Cube with caption'"); } public void testCubeCaption() throws SQLException { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Cube with caption\" caption=\"Cube with name\">" + " <Table name='sales_fact_1997'/>" + "</Cube>\n", "<VirtualCube name=\"Warehouse and Sales with caption\" " + " caption=\"Warehouse and Sales with name\" " + "defaultMeasure=\"Store Sales\">\n" + " <VirtualCubeDimension cubeName=\"Sales\" name=\"Customers\"/>\n" + "</VirtualCube>", null, null, null); final NamedList<org.olap4j.metadata.Cube> cubes = testContext.getOlap4jConnection().getOlapSchema() .getCubes(); final org.olap4j.metadata.Cube cube = cubes.get("Cube with caption"); assertEquals("Cube with name", cube.getCaption()); final org.olap4j.metadata.Cube cube2 = cubes.get("Warehouse and Sales with caption"); assertEquals("Warehouse and Sales with name", cube2.getCaption()); } public void testCubeWithNoDimensions() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"NoDim\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns("select {[Measures].[Unit Sales]} on columns from [NoDim]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Row #0: 266,773\n"); } public void testCubeWithNoMeasuresFails() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"NoMeasures\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <Dimension name=\"Promotion Media\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Media\" primaryKey=\"promotion_id\" defaultMember=\"All Media\">\n" + " <Table name=\"promotion\"/>\n" + " <Level name=\"Media Type\" column=\"media_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + "</Cube>", null, null, null, null); // Does not fail with // "Hierarchy '[Measures]' is invalid (has no members)" // because of the implicit [Fact Count] measure. testContext.assertSimpleQuery(); } public void testCubeWithOneCalcMeasure() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"OneCalcMeasure\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <Dimension name=\"Promotion Media\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Media\" primaryKey=\"promotion_id\" defaultMember=\"All Media\">\n" + " <Table name=\"promotion\"/>\n" + " <Level name=\"Media Type\" column=\"media_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <CalculatedMember\n" + " name=\"One\"\n" + " dimension=\"Measures\"\n" + " formula=\"1\"/>\n" + "</Cube>", null, null, null, null); // Because there are no explicit stored measures, the default measure is // the implicit stored measure, [Fact Count]. Stored measures, even // non-visible ones, come before calculated measures. testContext.assertQueryReturns( "select {[Measures]} on columns from [OneCalcMeasure]\n" + "where [Promotion Media].[TV]", "Axis #0:\n" + "{[Promotion Media].[TV]}\n" + "Axis #1:\n" + "{[Measures].[Fact Count]}\n" + "Row #0: 1,171\n"); } /** * Test case for feature * <a href="http://jira.pentaho.com/browse/MONDRIAN-960">MONDRIAN-960, * "Ability to define non-measure calculated members in a cube under a * specifc parent"</a>. */ public void testCalcMemberInCube() { final TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Store]'\n" + " parent='[Store].[USA].[CA]'>\n" + " <Formula>\n" + " [Store].[USA].[CA].[San Francisco]\n" + " + [Store].[USA].[CA].[Los Angeles]\n" + " </Formula>\n" + "</CalculatedMember>", null); // Because there are no explicit stored measures, the default measure is // the implicit stored measure, [Fact Count]. Stored measures, even // non-visible ones, come before calculated measures. testContext.assertQueryReturns("select {[Store].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); // Now access the same member using a path that is not its unique name. // Only works with new name resolver (if ssas = true). if (MondrianProperties.instance().SsasCompatibleNaming.get()) { testContext.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); } // Test where hierarchy & dimension both specified. should fail try { final TestContext testContextFail1 = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Store]'\n" + " dimension='[Store]'\n" + " parent='[Store].[USA].[CA]'>\n" + " <Formula>\n" + " [Store].[USA].[CA].[San Francisco]\n" + " + [Store].[USA].[CA].[Los Angeles]\n" + " </Formula>\n" + "</CalculatedMember>", null); testContextFail1.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage().contains("Cannot specify both a dimension and hierarchy" + " for calculated member 'SF and LA' in cube 'Sales'")); } // test where hierarchy is not uname of valid hierarchy. should fail try { final TestContext testContextFail1 = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Bacon]'\n" + " parent='[Store].[USA].[CA]'>\n" + " <Formula>\n" + " [Store].[USA].[CA].[San Francisco]\n" + " + [Store].[USA].[CA].[Los Angeles]\n" + " </Formula>\n" + "</CalculatedMember>", null); testContextFail1.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage().contains( "Unknown dimension '[Bacon]' for calculated member" + " 'SF and LA' in cube 'Sales'")); } // test where formula is invalid. should fail try { final TestContext testContextFail1 = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Store]'\n" + " parent='[Store].[USA].[CA]'>\n" + " <Formula>\n" + " Baconating!\n" + " </Formula>\n" + "</CalculatedMember>", null); testContextFail1.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage().contains("Named set in cube 'Sales' has bad formula")); } // Test where parent is invalid. should fail try { final TestContext testContextFail1 = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Store]'\n" + " parent='[Store].[USA].[CA].[Baconville]'>\n" + " <Formula>\n" + " [Store].[USA].[CA].[San Francisco]\n" + " + [Store].[USA].[CA].[Los Angeles]\n" + " </Formula>\n" + "</CalculatedMember>", null); testContextFail1.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage().contains("Cannot find a parent with name '[Store].[USA].[CA]" + ".[Baconville]' for calculated member 'SF and LA'" + " in cube 'Sales'")); } // test where parent is not in same hierarchy as hierarchy. should fail try { final TestContext testContextFail1 = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Store Type]'\n" + " parent='[Store].[USA].[CA]'>\n" + " <Formula>\n" + " [Store].[USA].[CA].[San Francisco]\n" + " + [Store].[USA].[CA].[Los Angeles]\n" + " </Formula>\n" + "</CalculatedMember>", null); testContextFail1.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage() .contains("The calculated member 'SF and LA' in cube 'Sales'" + " is defined for hierarchy '[Store Type]' but its" + " parent member is not part of that hierarchy")); } // test where calc member has no formula (formula attribute or // embedded element); should fail try { final TestContext testContextFail1 = TestContext.instance().createSubstitutingCube("Sales", null, null, "<CalculatedMember\n" + " name='SF and LA'\n" + " hierarchy='[Store]'\n" + " parent='[Store].[USA].[CA]'>\n" + " <Formula>\n" + " </Formula>\n" + "</CalculatedMember>", null); testContextFail1.assertQueryReturns( "select {[Store].[All Stores].[USA].[CA].[SF and LA]} on columns from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA].[CA].[SF and LA]}\n" + "Row #0: 27,780\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage().contains("Named set in cube 'Sales' has bad formula")); } } /** * this test triggers an exception out of the aggregate table manager */ public void testAggTableSupportOfSharedDims() { if (Bug.BugMondrian361Fixed) { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Two Dimensions\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Time2\" source=\"Time\" foreignKey=\"product_id\"/>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select\n" + " {[Time2].[1997]} on columns,\n" + " {[Time].[1997].[Q3]} on rows\n" + "From [Sales Two Dimensions]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Time2].[1997]}\n" + "Axis #2:\n" + "{[Time].[1997].[Q3]}\n" + "Row #0: 16,266\n"); MondrianProperties props = MondrianProperties.instance(); // turn off caching propSaver.set(props.DisableCaching, true); // re-read aggregates propSaver.set(props.UseAggregates, true); propSaver.set(props.ReadAggregates, false); propSaver.set(props.ReadAggregates, true); // force reloading of aggregates, which currently throws an // exception } } /** * Verifies that RolapHierarchy.tableExists() supports views. */ public void testLevelTableAttributeAsView() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"GenderCube\">\n" + " <Table name=\"sales_fact_1997\" alias=\"sales_fact_1997_gender\"/>\n" + "<Dimension name=\"Gender2\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Gender\" primaryKey=\"customer_id\">\n" + " <View alias=\"gender2\">\n" + " <SQL dialect=\"generic\">\n" + " <![CDATA[SELECT * FROM customer]]>\n" + " </SQL>\n" + " <SQL dialect=\"oracle\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"derby\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"hsqldb\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"luciddb\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"neoview\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"netezza\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " <SQL dialect=\"db2\">\n" + " <![CDATA[SELECT * FROM \"customer\"]]>\n" + " </SQL>\n" + " </View>\n" + " <Level name=\"Gender\" table=\"gender2\" column=\"gender\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + "</Dimension>" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>", null, null, null, null); if (!testContext.getDialect().allowsFromQuery()) { return; } Result result = testContext.executeQuery("select {[Gender2].members} on columns from [GenderCube]"); TestContext.assertEqualsVerbose("[Gender2].[All Gender]\n" + "[Gender2].[F]\n" + "[Gender2].[M]", TestContext.toString(result.getAxes()[0].getPositions())); } public void testInvalidSchemaAccess() { final TestContext testContext = TestContext.instance() .create(null, null, null, null, null, "<Role name=\"Role1\">\n" + " <SchemaGrant access=\"invalid\"/>\n" + "</Role>") .withRole("Role1"); testContext.assertQueryThrows("select from [Sales]", "In Schema: In Role: In SchemaGrant: " + "Value 'invalid' of attribute 'access' has illegal value 'invalid'. " + "Legal values: {all, custom, none, all_dimensions}"); } public void testAllMemberNoStringReplace() { final TestContext testContext = TestContext.instance().create(null, "<Cube name=\"Sales Special Time\">\n" + " <Table name=\"sales_fact_1997\"/>\n" + "<Dimension name=\"TIME\" foreignKey=\"time_id\" type=\"TimeDimension\">" + "<Hierarchy name=\"CALENDAR\" hasAll=\"true\" allMemberName=\"All TIME(CALENDAR)\" primaryKey=\"time_id\">" + " <Table name=\"time_by_day\"/>" + " <Level name=\"Years\" column=\"the_year\" uniqueMembers=\"true\" levelType=\"TimeYears\"/>" + " <Level name=\"Quarters\" column=\"quarter\" uniqueMembers=\"false\" levelType=\"TimeQuarters\"/>" + " <Level name=\"Months\" column=\"month_of_year\" uniqueMembers=\"false\" levelType=\"TimeMonths\"/>" + "</Hierarchy>" + "</Dimension>" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" " + " formatString=\"Standard\"/>\n" + " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"" + " formatString=\"#,###.00\"/>\n" + "</Cube>", null, null, null, null); testContext.assertQueryReturns( "select [TIME.CALENDAR].[All TIME(CALENDAR)] on columns\n" + "from [Sales Special Time]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[TIME].[CALENDAR].[All TIME(CALENDAR)]}\n" + "Row #0: 266,773\n"); } public void testUnionRole() { final TestContext testContext = TestContext.instance() .create(null, null, null, null, null, "<Role name=\"Role1\">\n" + " <SchemaGrant access=\"all\"/>\n" + "</Role>\n" + "<Role name=\"Role2\">\n" + " <SchemaGrant access=\"all\"/>\n" + "</Role>\n" + "<Role name=\"Role1Plus2\">\n" + " <Union>\n" + " <RoleUsage roleName=\"Role1\"/>\n" + " <RoleUsage roleName=\"Role2\"/>\n" + " </Union>\n" + "</Role>\n" + "<Role name=\"Role1Plus2Plus1\">\n" + " <Union>\n" + " <RoleUsage roleName=\"Role1Plus2\"/>\n" + " <RoleUsage roleName=\"Role1\"/>\n" + " </Union>\n" + "</Role>\n") .withRole("Role1Plus2Plus1"); testContext.assertQueryReturns("select from [Sales]", "Axis #0:\n" + "{}\n" + "266,773"); } public void testUnionRoleContainsGrants() { final TestContext testContext = TestContext.instance() .create(null, null, null, null, null, "<Role name=\"Role1\">\n" + " <SchemaGrant access=\"all\"/>\n" + "</Role>\n" + "<Role name=\"Role1Plus2\">\n" + " <SchemaGrant access=\"all\"/>\n" + " <Union>\n" + " <RoleUsage roleName=\"Role1\"/>\n" + " <RoleUsage roleName=\"Role1\"/>\n" + " </Union>\n" + "</Role>\n") .withRole("Role1Plus2"); testContext.assertQueryThrows("select from [Sales]", "Union role must not contain grants"); } public void testUnionRoleIllegalForwardRef() { final TestContext testContext = TestContext.instance().create(null, null, null, null, null, "<Role name=\"Role1\">\n" + " <SchemaGrant access=\"all\"/>\n" + "</Role>\n" + "<Role name=\"Role1Plus2\">\n" + " <Union>\n" + " <RoleUsage roleName=\"Role1\"/>\n" + " <RoleUsage roleName=\"Role2\"/>\n" + " </Union>\n" + "</Role>\n" + "<Role name=\"Role2\">\n" + " <SchemaGrant access=\"all\"/>\n" + "</Role>") .withRole("Role1Plus2"); testContext.assertQueryThrows("select from [Sales]", "Unknown role 'Role2'"); } public void testVirtualCubeNamedSetSupportInSchema() { TestContext testContext = TestContext.instance().createSubstitutingCube("Warehouse and Sales", null, null, null, "<NamedSet name=\"Non CA State Stores\" " + "formula=\"EXCEPT({[Store].[Store Country].[USA].children},{[Store].[Store Country].[USA].[CA]})\"/>"); testContext.assertQueryReturns( "WITH " + "SET [Non CA State Stores] AS 'EXCEPT({[Store].[Store Country].[USA].children}," + "{[Store].[Store Country].[USA].[CA]})'\n" + "MEMBER " + "[Store].[Total Non CA State] AS \n" + "'SUM({[Non CA State Stores]})'\n" + "SELECT {[Store].[Store Country].[USA],[Store].[Total Non CA State]} ON 0," + "{[Measures].[Unit Sales]} ON 1 FROM [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA]}\n" + "{[Store].[Total Non CA State]}\n" + "Axis #2:\n" + "{[Measures].[Unit Sales]}\n" + "Row #0: 266,773\n" + "Row #0: 192,025\n"); testContext.assertQueryReturns( "WITH " + "MEMBER " + "[Store].[Total Non CA State] AS \n" + "'SUM({[Non CA State Stores]})'\n" + "SELECT {[Store].[Store Country].[USA],[Store].[Total Non CA State]} ON 0," + "{[Measures].[Unit Sales]} ON 1 FROM [Warehouse and Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA]}\n" + "{[Store].[Total Non CA State]}\n" + "Axis #2:\n" + "{[Measures].[Unit Sales]}\n" + "Row #0: 266,773\n" + "Row #0: 192,025\n"); } public void testVirtualCubeNamedSetSupportInSchemaError() { TestContext testContext = TestContext.instance().createSubstitutingCube("Warehouse and Sales", null, null, null, "<NamedSet name=\"Non CA State Stores\" " + "formula=\"EXCEPT({[Store].[Store State].[USA].children},{[Store].[Store Country].[USA].[CA]})\"/>"); try { testContext.assertQueryReturns( "WITH " + "SET [Non CA State Stores] AS 'EXCEPT({[Store].[Store Country].[USA].children}," + "{[Store].[Store Country].[USA].[CA]})'\n" + "MEMBER " + "[Store].[Total Non CA State] AS \n" + "'SUM({[Non CA State Stores]})'\n" + "SELECT {[Store].[Store Country].[USA],[Store].[Total Non CA State]} ON 0," + "{[Measures].[Unit Sales]} ON 1 FROM [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Store].[USA]}\n" + "{[Store].[Total Non CA State]}\n" + "Axis #2:\n" + "{[Measures].[Unit Sales]}\n" + "Row #0: 266,773\n" + "Row #0: 192,025\n"); fail(); } catch (MondrianException e) { assertTrue(e.getMessage().indexOf("bad formula") >= 0); } } public void _testValidatorFindsNumericLevel() { // In the real foodmart, the level has type="Numeric" final TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Store Size in SQFT\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">\n" + " <Table name=\"store\"/>\n" + " <Level name=\"Store Sqft\" column=\"store_sqft\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>"); final List<Exception> exceptionList = testContext.getSchemaWarnings(); assertContains(exceptionList, "todo xxxxx"); } public void testInvalidRoleError() { String schema = TestContext.getRawFoodMartSchema(); schema = schema.replaceFirst("<Schema name=\"FoodMart\"", "<Schema name=\"FoodMart\" defaultRole=\"Unknown\""); final TestContext testContext = TestContext.instance().withSchema(schema); final List<Exception> exceptionList = testContext.getSchemaWarnings(); assertContains(exceptionList, "Role 'Unknown' not found"); } /** * Test case for bug <a href="http://jira.pentaho.com/browse/MONDRIAN-413"> * MONDRIAN-413, "RolapMember causes ClassCastException in compare()"</a>, * caused by binary column value. */ public void testBinaryLevelKey() { switch (TestContext.instance().getDialect().getDatabaseProduct()) { case DERBY: case MYSQL: break; default: // Not all databases support binary literals (e.g. X'AB01'). Only // Derby returns them as byte[] values from its JDBC driver and // therefore experiences bug MONDRIAN-413. return; } TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Binary\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"id\">\n" + " <InlineTable alias=\"binary\">\n" + " <ColumnDefs>\n" + " <ColumnDef name=\"id\" type=\"Integer\"/>\n" + " <ColumnDef name=\"bin\" type=\"Integer\"/>\n" + " <ColumnDef name=\"name\" type=\"String\"/>\n" + " </ColumnDefs>\n" + " <Rows>\n" + " <Row>\n" + " <Value column=\"id\">2</Value>\n" + " <Value column=\"bin\">X'4546'</Value>\n" + " <Value column=\"name\">Ben</Value>\n" + " </Row>\n" + " <Row>\n" + " <Value column=\"id\">3</Value>\n" + " <Value column=\"bin\">X'424344'</Value>\n" + " <Value column=\"name\">Bill</Value>\n" + " </Row>\n" + " <Row>\n" + " <Value column=\"id\">4</Value>\n" + " <Value column=\"bin\">X'424344'</Value>\n" + " <Value column=\"name\">Bill</Value>\n" + " </Row>\n" + " </Rows>\n" + " </InlineTable>\n" + " <Level name=\"Level1\" column=\"bin\" nameColumn=\"name\" ordinalColumn=\"name\" />\n" + " <Level name=\"Level2\" column=\"id\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n"); testContext.assertQueryReturns("select {[Binary].members} on 0 from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Binary].[Ben]}\n" + "{[Binary].[Ben].[2]}\n" + "{[Binary].[Bill]}\n" + "{[Binary].[Bill].[3]}\n" + "{[Binary].[Bill].[4]}\n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n"); testContext.assertQueryReturns("select hierarchize({[Binary].members}) on 0 from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Binary].[Ben]}\n" + "{[Binary].[Ben].[2]}\n" + "{[Binary].[Bill]}\n" + "{[Binary].[Bill].[3]}\n" + "{[Binary].[Bill].[4]}\n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n"); } /** * Test case for the Level@internalType attribute. * * <p>See bug <a href="http://jira.pentaho.com/browse/MONDRIAN-896"> * MONDRIAN-896, "Oracle integer columns overflow if value >>2^31"</a>. */ public void testLevelInternalType() { // One of the keys is larger than Integer.MAX_VALUE (2 billion), so // will only work if we use long values. TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Big numbers\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"id\">\n" + " <InlineTable alias=\"t\">\n" + " <ColumnDefs>\n" + " <ColumnDef name=\"id\" type=\"Integer\"/>\n" + " <ColumnDef name=\"big_num\" type=\"Integer\"/>\n" + " <ColumnDef name=\"name\" type=\"String\"/>\n" + " </ColumnDefs>\n" + " <Rows>\n" + " <Row>\n" + " <Value column=\"id\">0</Value>\n" + " <Value column=\"big_num\">1234</Value>\n" + " <Value column=\"name\">Ben</Value>\n" + " </Row>\n" + " <Row>\n" + " <Value column=\"id\">519</Value>\n" + " <Value column=\"big_num\">1234567890123</Value>\n" + " <Value column=\"name\">Bill</Value>\n" + " </Row>\n" + " </Rows>\n" + " </InlineTable>\n" + " <Level name=\"Level1\" column=\"big_num\" internalType=\"long\"/>\n" + " <Level name=\"Level2\" column=\"id\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n"); testContext.assertQueryReturns("select {[Big numbers].members} on 0 from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Big numbers].[1234]}\n" + "{[Big numbers].[1234].[0]}\n" + "{[Big numbers].[1234567890123]}\n" + "{[Big numbers].[1234567890123].[519]}\n" + "Row #0: 195,448\n" + "Row #0: 195,448\n" + "Row #0: 739\n" + "Row #0: 739\n"); } /** * Negative test for Level@internalType attribute. */ public void testLevelInternalTypeErr() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Big numbers\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"id\">\n" + " <InlineTable alias=\"t\">\n" + " <ColumnDefs>\n" + " <ColumnDef name=\"id\" type=\"Integer\"/>\n" + " <ColumnDef name=\"big_num\" type=\"Integer\"/>\n" + " <ColumnDef name=\"name\" type=\"String\"/>\n" + " </ColumnDefs>\n" + " <Rows>\n" + " <Row>\n" + " <Value column=\"id\">0</Value>\n" + " <Value column=\"big_num\">1234</Value>\n" + " <Value column=\"name\">Ben</Value>\n" + " </Row>\n" + " </Rows>\n" + " </InlineTable>\n" + " <Level name=\"Level1\" column=\"big_num\" type=\"Integer\" internalType=\"char\"/>\n" + " <Level name=\"Level2\" column=\"id\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n"); testContext.assertQueryThrows("select {[Big numbers].members} on 0 from [Sales]", "In Schema: In Cube: In Dimension: In Hierarchy: In Level: Value 'char' of attribute 'internalType' has illegal value 'char'. Legal values: {int, long, Object, String}"); } public void _testAttributeHierarchy() { // from email from peter tran dated 2008/9/8 // TODO: schema syntax to create attribute hierarchy assertQueryReturns("WITH \n" + " MEMBER\n" + " Measures.SalesPerWorkingDay AS \n" + " IIF(\n" + " Count(\n" + " Filter(\n" + " Descendants(\n" + " [Date].[Calendar].CurrentMember\n" + " ,[Date].[Calendar].[Date]\n" + " ,SELF)\n" + " , [Date].[Day of Week].CurrentMember.Name <> \"1\"\n" + " )\n" + " ) = 0\n" + " ,NULL\n" + " ,[Measures].[Internet Sales Amount]\n" + " /\n" + " Count(\n" + " Filter(\n" + " Descendants(\n" + " [Date].[Calendar].CurrentMember\n" + " ,[Date].[Calendar].[Date]\n" + " ,SELF)\n" + " , [Date].[Day of Week].CurrentMember.Name <> \"1\"\n" + " )\n" + " )\n" + " )\n" + " '\n" + "SELECT [Measures].[SalesPerWorkingDay] ON 0\n" + ", [Date].[Calendar].[Month].MEMBERS ON 1\n" + "FROM [Adventure Works]", "x"); } /** * Testcase for a problem which involved a slowly changing dimension. * Not actually a slowly-changing dimension - we don't have such a thing in * the foodmart schema - but the same structure. The dimension is a two * table snowflake, and the table nearer to the fact table is not used by * any level. */ public void testScdJoin() { final TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Product truncated\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Class\" table=\"product_class\" nameColumn=\"product_subcategory\"\n" + " column=\"product_class_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n", null, null, null); testContext.assertQueryReturns( "select non empty {[Measures].[Unit Sales]} on 0,\n" + " non empty Filter({[Product truncated].Members}, [Measures].[Unit Sales] > 10000) on 1\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Product truncated].[All Product truncateds]}\n" + "{[Product truncated].[Fresh Vegetables]}\n" + "{[Product truncated].[Fresh Fruit]}\n" + "Row #0: 266,773\n" + "Row #1: 20,739\n" + "Row #2: 11,767\n"); } // TODO: enable this test as part of PhysicalSchema work // TODO: also add a test that Table.alias, Join.leftAlias and // Join.rightAlias cannot be the empty string. public void _testNonUniqueAlias() { final TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Product truncated\" foreignKey=\"product_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\" alias=\"product_class\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Class\" table=\"product_class\" nameColumn=\"product_subcategory\"\n" + " column=\"product_class_id\" type=\"Numeric\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n", null, null, null); Throwable throwable = null; try { testContext.assertSimpleQuery(); } catch (Throwable e) { throwable = e; } // neither a source column or source expression specified TestContext.checkThrowable(throwable, "Alias not unique"); } /** * Test case for bug <a href="http://jira.pentaho.com/browse/MONDRIAN-482"> * MONDRIAN-482, "ClassCastException when obtaining RolapCubeLevel"</a>. */ public void testBugMondrian482() { // until bug MONDRIAN-495, "Table filter concept does not support // dialects." is fixed, this test case only works on MySQL if (!Bug.BugMondrian495Fixed && TestContext.instance().getDialect().getDatabaseProduct() != Dialect.DatabaseProduct.MYSQL) { return; } // skip this test if using aggregates, the agg tables do not // enforce the SQL element in the fact table if (MondrianProperties.instance().UseAggregates.booleanValue()) { return; } // In order to reproduce the problem it was necessary to only have one // non empty member under USA. In the cube definition below we create a // cube with only CA data to achieve this. String salesCube1 = "<Cube name=\"Sales2\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\" >\n" + " <SQL dialect=\"default\">\n" + " <![CDATA[`sales_fact_1997`.`store_id` in (select distinct `store_id` from `store` where `store`.`store_state` = \"CA\")]]>\n" + " </SQL>\n" + " </Table>\n" + " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + " <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + " <Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "</Cube>\n"; final TestContext testContext = TestContext.instance().create(null, salesCube1, null, null, null, null); // First query all children of the USA. This should only return CA since // all the other states were filtered out. CA will be put in the member // cache String query1 = "WITH SET [#DataSet#] as " + "'NonEmptyCrossjoin({[Product].[All Products]}, {[Store].[All Stores].[USA].Children})' " + "SELECT {[Measures].[Unit Sales]} on columns, " + "NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [Sales2]"; testContext.assertQueryReturns(query1, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Product].[All Products], [Store].[USA].[CA]}\n" + "Row #0: 74,748\n"); // Now query the children of CA using the descendants function // This is where the ClassCastException occurs String query2 = "WITH SET [#DataSet#] as " + "'{Descendants([Store].[All Stores], 3)}' " + "SELECT {[Measures].[Unit Sales]} on columns, " + "NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [Sales2]"; testContext.assertQueryReturns(query2, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA].[Beverly Hills]}\n" + "{[Store].[USA].[CA].[Los Angeles]}\n" + "{[Store].[USA].[CA].[San Diego]}\n" + "{[Store].[USA].[CA].[San Francisco]}\n" + "Row #0: 21,333\n" + "Row #1: 25,663\n" + "Row #2: 25,635\n" + "Row #3: 2,117\n"); } /** * Test case for * <a href="http://jira.pentaho.com/browse/MONDRIAN-355">Bug MONDRIAN-355, * "adding hours/mins as levelType for level of type Dimension"</a>. */ public void testBugMondrian355() { checkBugMondrian355("TimeHalfYears"); // make sure that the deprecated name still works checkBugMondrian355("TimeHalfYear"); } public void checkBugMondrian355(String timeHalfYear) { final String xml = "<Dimension name=\"Time2\" foreignKey=\"time_id\" type=\"TimeDimension\">\n" + "<Hierarchy hasAll=\"true\" primaryKey=\"time_id\">\n" + " <Table name=\"time_by_day\"/>\n" + " <Level name=\"Years\" column=\"the_year\" uniqueMembers=\"true\" type=\"Numeric\" levelType=\"TimeYears\"/>\n" + " <Level name=\"Half year\" column=\"quarter\" uniqueMembers=\"false\" levelType=\"" + timeHalfYear + "\"/>\n" + " <Level name=\"Hours\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\" levelType=\"TimeHours\"/>\n" + " <Level name=\"Quarter hours\" column=\"time_id\" uniqueMembers=\"false\" type=\"Numeric\" levelType=\"TimeUndefined\"/>\n" + "</Hierarchy>\n" + "</Dimension>"; TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", xml); testContext.assertQueryReturns( "select Head([Time2].[Quarter hours].Members, 3) on columns\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Time2].[1997].[Q1].[1].[367]}\n" + "{[Time2].[1997].[Q1].[1].[368]}\n" + "{[Time2].[1997].[Q1].[1].[369]}\n" + "Row #0: 348\n" + "Row #0: 635\n" + "Row #0: 589\n"); // Check that can apply ParallelPeriod to a TimeUndefined level. testContext.assertAxisReturns("PeriodsToDate([Time2].[Quarter hours], [Time2].[1997].[Q1].[1].[368])", "[Time2].[1997].[Q1].[1].[368]"); testContext.assertAxisReturns("PeriodsToDate([Time2].[Half year], [Time2].[1997].[Q1].[1].[368])", "[Time2].[1997].[Q1].[1].[367]\n" + "[Time2].[1997].[Q1].[1].[368]"); // Check that get an error if give invalid level type try { TestContext.instance() .createSubstitutingCube("Sales", Util.replace(xml, "TimeUndefined", "TimeUnspecified")) .assertSimpleQuery(); fail("expected error"); } catch (Throwable e) { TestContext.checkThrowable(e, "Value 'TimeUnspecified' of attribute 'levelType' has illegal value 'TimeUnspecified'. Legal values: {Regular, TimeYears, "); } } /** * Test for descriptions, captions and annotations of various schema * elements. */ public void testCaptionDescriptionAndAnnotation() { final String schemaName = "Description schema"; final String salesCubeName = "DescSales"; final String virtualCubeName = "DescWarehouseAndSales"; final String warehouseCubeName = "Warehouse"; final TestContext testContext = TestContext.instance().withSchema("<Schema name=\"" + schemaName + "\"\n" + " description=\"Schema to test descriptions and captions\">\n" + " <Annotations>\n" + " <Annotation name=\"a\">Schema</Annotation>\n" + " <Annotation name=\"b\">Xyz</Annotation>\n" + " </Annotations>\n" + " <Dimension name=\"Time\" type=\"TimeDimension\"\n" + " caption=\"Time shared caption\"\n" + " description=\"Time shared description\">\n" + " <Annotations><Annotation name=\"a\">Time shared</Annotation></Annotations>\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"time_id\"\n" + " caption=\"Time shared hierarchy caption\"\n" + " description=\"Time shared hierarchy description\">\n" + " <Table name=\"time_by_day\"/>\n" + " <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\"\n" + " levelType=\"TimeYears\"/>\n" + " <Level name=\"Quarter\" column=\"quarter\" uniqueMembers=\"false\"\n" + " levelType=\"TimeQuarters\"/>\n" + " <Level name=\"Month\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\"\n" + " levelType=\"TimeMonths\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Warehouse\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"warehouse_id\">\n" + " <Table name=\"warehouse\"/>\n" + " <Level name=\"Country\" column=\"warehouse_country\" uniqueMembers=\"true\"/>\n" + " <Level name=\"State Province\" column=\"warehouse_state_province\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"City\" column=\"warehouse_city\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Warehouse Name\" column=\"warehouse_name\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Cube name=\"" + salesCubeName + "\"\n" + " description=\"Cube description\">\n" + " <Annotations><Annotation name=\"a\">Cube</Annotation></Annotations>\n" + " <Table name=\"sales_fact_1997\"/>\n" + " <Dimension name=\"Store\" foreignKey=\"store_id\"\n" + " caption=\"Dimension caption\"\n" + " description=\"Dimension description\">\n" + " <Annotations><Annotation name=\"a\">Dimension</Annotation></Annotations>\n" + " <Hierarchy hasAll=\"true\" primaryKeyTable=\"store\" primaryKey=\"store_id\"\n" + " caption=\"Hierarchy caption\"\n" + " description=\"Hierarchy description\">\n" + " <Annotations><Annotation name=\"a\">Hierarchy</Annotation></Annotations>\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Join leftKey=\"sales_district_id\" rightKey=\"promotion_id\">\n" + " <Table name=\"region\"/>\n" + " <Table name=\"promotion\"/>\n" + " </Join>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\"\n" + " description=\"Level description\"" + " caption=\"Level caption\">\n" + " <Annotations><Annotation name=\"a\">Level</Annotation></Annotations>\n" + " </Level>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_region\" />\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\" />\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <DimensionUsage name=\"Time1\"\n" + " caption=\"Time usage caption\"\n" + " description=\"Time usage description\"\n" + " source=\"Time\" foreignKey=\"time_id\">\n" + " <Annotations><Annotation name=\"a\">Time usage</Annotation></Annotations>\n" + " </DimensionUsage>\n" + " <DimensionUsage name=\"Time2\"\n" + " source=\"Time\" foreignKey=\"time_id\"/>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\"\n" + " aggregator=\"sum\" formatString=\"Standard\"\n" + " caption=\"Measure caption\"\n" + " description=\"Measure description\">\n" + " <Annotations><Annotation name=\"a\">Measure</Annotation></Annotations>\n" + "</Measure>\n" + "<CalculatedMember name=\"Foo\" dimension=\"Measures\" \n" + " caption=\"Calc member caption\"\n" + " description=\"Calc member description\">\n" + " <Annotations><Annotation name=\"a\">Calc member</Annotation></Annotations>\n" + " <Formula>[Measures].[Unit Sales] + 1</Formula>\n" + " <CalculatedMemberProperty name=\"FORMAT_STRING\" value=\"$#,##0.00\"/>\n" + " </CalculatedMember>\n" + " <NamedSet name=\"Top Periods\"\n" + " caption=\"Named set caption\"\n" + " description=\"Named set description\">\n" + " <Annotations><Annotation name=\"a\">Named set</Annotation></Annotations>\n" + " <Formula>TopCount([Time1].MEMBERS, 5, [Measures].[Foo])</Formula>\n" + " </NamedSet>\n" + "</Cube>\n" + "<Cube name=\"" + warehouseCubeName + "\">\n" + " <Table name=\"inventory_fact_1997\"/>\n" + "\n" + " <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + " <DimensionUsage name=\"Warehouse\" source=\"Warehouse\" foreignKey=\"warehouse_id\"/>\n" + "\n" + " <Measure name=\"Units Shipped\" column=\"units_shipped\" aggregator=\"sum\" formatString=\"#.0\"/>\n" + "</Cube>\n" + "<VirtualCube name=\"" + virtualCubeName + "\"\n" + " caption=\"Virtual cube caption\"\n" + " description=\"Virtual cube description\">\n" + " <Annotations><Annotation name=\"a\">Virtual cube</Annotation></Annotations>\n" + " <VirtualCubeDimension name=\"Time\"/>\n" + " <VirtualCubeDimension cubeName=\"" + warehouseCubeName + "\" name=\"Warehouse\"/>\n" + " <VirtualCubeMeasure cubeName=\"" + salesCubeName + "\" name=\"[Measures].[Unit Sales]\">\n" + " <Annotations><Annotation name=\"a\">Virtual cube measure</Annotation></Annotations>\n" + " </VirtualCubeMeasure>\n" + " <VirtualCubeMeasure cubeName=\"" + warehouseCubeName + "\" name=\"[Measures].[Units Shipped]\"/>\n" + " <CalculatedMember name=\"Profit Per Unit Shipped\" dimension=\"Measures\">\n" + " <Formula>1 / [Measures].[Units Shipped]</Formula>\n" + " </CalculatedMember>\n" + "</VirtualCube>" + "</Schema>"); final Result result = testContext.executeQuery("select from [" + salesCubeName + "]"); final Cube cube = result.getQuery().getCube(); assertEquals("Cube description", cube.getDescription()); checkAnnotations(cube.getAnnotationMap(), "a", "Cube"); final Schema schema = cube.getSchema(); checkAnnotations(schema.getAnnotationMap(), "a", "Schema", "b", "Xyz"); final Dimension dimension = cube.getDimensions()[1]; assertEquals("Dimension description", dimension.getDescription()); assertEquals("Dimension caption", dimension.getCaption()); checkAnnotations(dimension.getAnnotationMap(), "a", "Dimension"); final Hierarchy hierarchy = dimension.getHierarchies()[0]; assertEquals("Hierarchy description", hierarchy.getDescription()); assertEquals("Hierarchy caption", hierarchy.getCaption()); checkAnnotations(hierarchy.getAnnotationMap(), "a", "Hierarchy"); final mondrian.olap.Level level = hierarchy.getLevels()[1]; assertEquals("Level description", level.getDescription()); assertEquals("Level caption", level.getCaption()); checkAnnotations(level.getAnnotationMap(), "a", "Level"); // Caption comes from the CAPTION member property, defaults to name. // Description comes from the DESCRIPTION member property. // Annotations are always empty for regular members. final List<Member> memberList = cube.getSchemaReader(null).withLocus().getLevelMembers(level, false); final Member member = memberList.get(0); assertEquals("Canada", member.getName()); assertEquals("Canada", member.getCaption()); assertNull(member.getDescription()); checkAnnotations(member.getAnnotationMap()); // All member. Caption defaults to name; description is null. final Member allMember = member.getParentMember(); assertEquals("All Stores", allMember.getName()); assertEquals("All Stores", allMember.getCaption()); assertNull(allMember.getDescription()); // All level. final mondrian.olap.Level allLevel = hierarchy.getLevels()[0]; assertEquals("(All)", allLevel.getName()); assertNull(allLevel.getDescription()); assertEquals(allLevel.getName(), allLevel.getCaption()); checkAnnotations(allLevel.getAnnotationMap()); // the first time dimension overrides the caption and description of the // shared time dimension final Dimension timeDimension = cube.getDimensions()[2]; assertEquals("Time1", timeDimension.getName()); assertEquals("Time usage description", timeDimension.getDescription()); assertEquals("Time usage caption", timeDimension.getCaption()); checkAnnotations(timeDimension.getAnnotationMap(), "a", "Time usage"); // Time1 is a usage of a shared dimension Time. // Now look at the hierarchy usage within that dimension usage. // Because the dimension usage has a name, use that as a prefix for // name, caption and description of the hierarchy usage. final Hierarchy timeHierarchy = timeDimension.getHierarchies()[0]; // The hierarchy in the shared dimension does not have a name, so the // hierarchy usage inherits the name of the dimension usage, Time1. final boolean ssasCompatibleNaming = MondrianProperties.instance().SsasCompatibleNaming.get(); if (ssasCompatibleNaming) { assertEquals("Time", timeHierarchy.getName()); assertEquals("Time1", timeHierarchy.getDimension().getName()); } else { assertEquals("Time1", timeHierarchy.getName()); } // The description is prefixed by the dimension usage name. assertEquals("Time usage caption.Time shared hierarchy description", timeHierarchy.getDescription()); // The hierarchy caption is prefixed by the caption of the dimension // usage. assertEquals("Time usage caption.Time shared hierarchy caption", timeHierarchy.getCaption()); // No annotations. checkAnnotations(timeHierarchy.getAnnotationMap()); // the second time dimension does not overrides caption and description final Dimension time2Dimension = cube.getDimensions()[3]; assertEquals("Time2", time2Dimension.getName()); assertEquals("Time shared description", time2Dimension.getDescription()); assertEquals("Time shared caption", time2Dimension.getCaption()); checkAnnotations(time2Dimension.getAnnotationMap()); final Hierarchy time2Hierarchy = time2Dimension.getHierarchies()[0]; // The hierarchy in the shared dimension does not have a name, so the // hierarchy usage inherits the name of the dimension usage, Time2. if (ssasCompatibleNaming) { assertEquals("Time", time2Hierarchy.getName()); assertEquals("Time2", time2Hierarchy.getDimension().getName()); } else { assertEquals("Time2", time2Hierarchy.getName()); } // The description is prefixed by the dimension usage name (because // dimension usage has no caption). assertEquals("Time2.Time shared hierarchy description", time2Hierarchy.getDescription()); // The hierarchy caption is prefixed by the dimension usage name // (because the dimension usage has no caption. assertEquals("Time2.Time shared hierarchy caption", time2Hierarchy.getCaption()); // No annotations. checkAnnotations(time2Hierarchy.getAnnotationMap()); final Dimension measuresDimension = cube.getDimensions()[0]; final Hierarchy measuresHierarchy = measuresDimension.getHierarchies()[0]; final mondrian.olap.Level measuresLevel = measuresHierarchy.getLevels()[0]; final SchemaReader schemaReader = cube.getSchemaReader(null); final List<Member> measures = schemaReader.getLevelMembers(measuresLevel, true); final Member measure = measures.get(0); assertEquals("Unit Sales", measure.getName()); assertEquals("Measure caption", measure.getCaption()); assertEquals("Measure description", measure.getDescription()); assertEquals(measure.getDescription(), measure.getPropertyValue(Property.DESCRIPTION.name)); assertEquals(measure.getCaption(), measure.getPropertyValue(Property.CAPTION.name)); assertEquals(measure.getCaption(), measure.getPropertyValue(Property.MEMBER_CAPTION.name)); checkAnnotations(measure.getAnnotationMap(), "a", "Measure"); // The implicitly created [Fact Count] measure final Member factCountMeasure = measures.get(1); assertEquals("Fact Count", factCountMeasure.getName()); assertEquals(false, factCountMeasure.getPropertyValue(Property.VISIBLE.name)); final Member calcMeasure = measures.get(2); assertEquals("Foo", calcMeasure.getName()); assertEquals("Calc member caption", calcMeasure.getCaption()); assertEquals("Calc member description", calcMeasure.getDescription()); assertEquals(calcMeasure.getDescription(), calcMeasure.getPropertyValue(Property.DESCRIPTION.name)); assertEquals(calcMeasure.getCaption(), calcMeasure.getPropertyValue(Property.CAPTION.name)); assertEquals(calcMeasure.getCaption(), calcMeasure.getPropertyValue(Property.MEMBER_CAPTION.name)); checkAnnotations(calcMeasure.getAnnotationMap(), "a", "Calc member"); final NamedSet namedSet = cube.getNamedSets()[0]; assertEquals("Top Periods", namedSet.getName()); assertEquals("Named set caption", namedSet.getCaption()); assertEquals("Named set description", namedSet.getDescription()); checkAnnotations(namedSet.getAnnotationMap(), "a", "Named set"); final Result result2 = testContext.executeQuery("select from [" + virtualCubeName + "]"); final Cube cube2 = result2.getQuery().getCube(); assertEquals("Virtual cube description", cube2.getDescription()); checkAnnotations(cube2.getAnnotationMap(), "a", "Virtual cube"); final SchemaReader schemaReader2 = cube2.getSchemaReader(null); final Dimension measuresDimension2 = cube2.getDimensions()[0]; final Hierarchy measuresHierarchy2 = measuresDimension2.getHierarchies()[0]; final mondrian.olap.Level measuresLevel2 = measuresHierarchy2.getLevels()[0]; final List<Member> measures2 = schemaReader2.getLevelMembers(measuresLevel2, true); final Member measure2 = measures2.get(0); assertEquals("Unit Sales", measure2.getName()); assertEquals("Measure caption", measure2.getCaption()); assertEquals("Measure description", measure2.getDescription()); assertEquals(measure2.getDescription(), measure2.getPropertyValue(Property.DESCRIPTION.name)); assertEquals(measure2.getCaption(), measure2.getPropertyValue(Property.CAPTION.name)); assertEquals(measure2.getCaption(), measure2.getPropertyValue(Property.MEMBER_CAPTION.name)); checkAnnotations(measure2.getAnnotationMap(), "a", "Virtual cube measure"); } private static void checkAnnotations(Map<String, Annotation> annotationMap, String... nameVal) { assertNotNull(annotationMap); assertEquals(0, nameVal.length % 2); assertEquals(nameVal.length / 2, annotationMap.size()); int i = 0; for (Map.Entry<String, Annotation> entry : annotationMap.entrySet()) { assertEquals(nameVal[i++], entry.getKey()); assertEquals(nameVal[i++], entry.getValue().getValue()); } } public void testCaption() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"Gender2\" foreignKey=\"customer_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\" >\n" + " <Table name=\"customer\"/>\n" + " <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\" >\n" + " <CaptionExpression>\n" + " <SQL dialect='generic'>'foobar'</SQL>\n" + " </CaptionExpression>\n" + " </Level>\n" + " </Hierarchy>\n" + " </Dimension>"); switch (testContext.getDialect().getDatabaseProduct()) { case POSTGRESQL: // Postgres fails with: // Internal error: while building member cache; sql=[select // "customer"."gender" as "c0", 'foobar' as "c1" from "customer" // as "customer" group by "customer"."gender", 'foobar' order by // "customer"."\ gender" ASC NULLS LAST] // Caused by: org.postgresql.util.PSQLException: ERROR: // non-integer constant in GROUP BY // // It's difficult for mondrian to spot that it's been given a // constant expression. We can live with this bug. Postgres // shouldn't be so picky, and people shouldn't be so daft. return; } Result result = testContext.executeQuery("select {[Gender2].Children} on columns from [Sales]"); assertEquals("foobar", result.getAxes()[0].getPositions().get(0).get(0).getCaption()); } /** * Implementation of {@link PropertyFormatter} that throws. */ public static class DummyPropertyFormatter implements PropertyFormatter { public DummyPropertyFormatter() { throw new RuntimeException("oops"); } public String formatProperty(Member member, String propertyName, Object propertyValue) { return null; } } /** * Unit test for bug * <a href="http://jira.pentaho.com/browse/MONDRIAN-747"> * MONDRIAN-747, "When joining a shared dimension into a cube at a level * other than its leaf level, Mondrian gives wrong results"</a>. */ public void testBugMondrian747() { // Test case requires a pecular inline view, and it works on dialects // that scalar subqery, viz oracle. I believe that the mondrian code // being works in all dialects. switch (TestContext.instance().getDialect().getDatabaseProduct()) { case ORACLE: break; default: return; } final TestContext testContext = TestContext.instance().withSchema("<Schema name='Test_DimensionUsage'> \n" + " <Dimension type='StandardDimension' name='Store'> \n" + " <Hierarchy hasAll='true' primaryKey='store_id'> \n" + " <Table name='store'> \n" + " </Table> \n" + " <Level name='country' column='store_country' type='String' uniqueMembers='false' levelType='Regular' hideMemberIf='Never'> \n" + " </Level> \n" + " <Level name='state' column='store_state' type='String' uniqueMembers='false' levelType='Regular' hideMemberIf='Never'> \n" + " </Level> \n" + " <Level name='city' column='store_city' type='String' uniqueMembers='false' levelType='Regular' hideMemberIf='Never'> \n" + " </Level> \n" + " </Hierarchy> \n" + " </Dimension> \n" + " <Dimension type='StandardDimension' name='Product'> \n" + " <Hierarchy name='New Hierarchy 0' hasAll='true' primaryKey='product_id'> \n" + " <Table name='product'> \n" + " </Table> \n" + " <Level name='product_name' column='product_name' type='String' uniqueMembers='false' levelType='Regular' hideMemberIf='Never'> \n" + " </Level> \n" + " </Hierarchy> \n" + " </Dimension> \n" + " <Cube name='cube1' cache='true' enabled='true'> \n" + " <Table name='sales_fact_1997'> \n" + " </Table> \n" + " <DimensionUsage source='Store' name='Store' foreignKey='store_id'> \n" + " </DimensionUsage> \n" + " <DimensionUsage source='Product' name='Product' foreignKey='product_id'> \n" + " </DimensionUsage> \n" + " <Measure name='unitsales1' column='unit_sales' datatype='Numeric' aggregator='sum' visible='true'> \n" + " </Measure> \n" + " </Cube> \n" + " <Cube name='cube2' cache='true' enabled='true'> \n" // + " <Table name='sales_fact_1997_test'/> \n" + " <View alias='sales_fact_1997_test'> \n" + " <SQL dialect='generic'>select \"product_id\", \"time_id\", \"customer_id\", \"promotion_id\", \"store_id\", \"store_sales\", \"store_cost\", \"unit_sales\", (select \"store_state\" from \"store\" where \"store_id\" = \"sales_fact_1997\".\"store_id\") as \"sales_state_province\" from \"sales_fact_1997\"</SQL>\n" + " </View> \n" + " <DimensionUsage source='Store' level='state' name='Store' foreignKey='sales_state_province'> \n" + " </DimensionUsage> \n" + " <DimensionUsage source='Product' name='Product' foreignKey='product_id'> \n" + " </DimensionUsage> \n" + " <Measure name='unitsales2' column='unit_sales' datatype='Numeric' aggregator='sum' visible='true'> \n" + " </Measure> \n" + " </Cube> \n" + " <VirtualCube enabled='true' name='virtual_cube'> \n" + " <VirtualCubeDimension name='Store'> \n" + " </VirtualCubeDimension> \n" + " <VirtualCubeDimension name='Product'> \n" + " </VirtualCubeDimension> \n" + " <VirtualCubeMeasure cubeName='cube1' name='[Measures].[unitsales1]' visible='true'> \n" + " </VirtualCubeMeasure> \n" + " <VirtualCubeMeasure cubeName='cube2' name='[Measures].[unitsales2]' visible='true'> \n" + " </VirtualCubeMeasure> \n" + " </VirtualCube> \n" + "</Schema>"); if (!Bug.BugMondrian747Fixed && MondrianProperties.instance().EnableGroupingSets.get()) { // With grouping sets enabled, MONDRIAN-747 behavior is even worse. return; } // [Store].[All Stores] and [Store].[USA] should be 266,773. A higher // value would indicate that there is a cartesian product going on -- // because "store_state" is not unique in "store" table. final String x = !Bug.BugMondrian747Fixed ? "1,379,620" : "266,773"; testContext.assertQueryReturns( "select non empty {[Measures].[unitsales2]} on 0,\n" + " non empty [Store].members on 1\n" + "from [cube2]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[unitsales2]}\n" + "Axis #2:\n" + "{[Store].[All Stores]}\n" + "{[Store].[USA]}\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[WA]}\n" + "Row #0: 266,773\n" + "Row #1: " + x + "\n" + "Row #2: 373,740\n" + "Row #3: 135,318\n" + "Row #4: 870,562\n"); testContext.assertQueryReturns( "select non empty {[Measures].[unitsales1]} on 0,\n" + " non empty [Store].members on 1\n" + "from [cube1]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[unitsales1]}\n" + "Axis #2:\n" + "{[Store].[All Stores]}\n" + "{[Store].[USA]}\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[CA].[Beverly Hills]}\n" + "{[Store].[USA].[CA].[Los Angeles]}\n" + "{[Store].[USA].[CA].[San Diego]}\n" + "{[Store].[USA].[CA].[San Francisco]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[OR].[Portland]}\n" + "{[Store].[USA].[OR].[Salem]}\n" + "{[Store].[USA].[WA]}\n" + "{[Store].[USA].[WA].[Bellingham]}\n" + "{[Store].[USA].[WA].[Bremerton]}\n" + "{[Store].[USA].[WA].[Seattle]}\n" + "{[Store].[USA].[WA].[Spokane]}\n" + "{[Store].[USA].[WA].[Tacoma]}\n" + "{[Store].[USA].[WA].[Walla Walla]}\n" + "{[Store].[USA].[WA].[Yakima]}\n" + "Row #0: 266,773\n" + "Row #1: 266,773\n" + "Row #2: 74,748\n" + "Row #3: 21,333\n" + "Row #4: 25,663\n" + "Row #5: 25,635\n" + "Row #6: 2,117\n" + "Row #7: 67,659\n" + "Row #8: 26,079\n" + "Row #9: 41,580\n" + "Row #10: 124,366\n" + "Row #11: 2,237\n" + "Row #12: 24,576\n" + "Row #13: 25,011\n" + "Row #14: 23,591\n" + "Row #15: 35,257\n" + "Row #16: 2,203\n" + "Row #17: 11,491\n"); testContext.assertQueryReturns( "select non empty {[Measures].[unitsales2], [Measures].[unitsales1]} on 0,\n" + " non empty [Store].members on 1\n" + "from [virtual_cube]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[unitsales2]}\n" + "{[Measures].[unitsales1]}\n" + "Axis #2:\n" + "{[Store].[All Stores]}\n" + "{[Store].[USA]}\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[CA].[Beverly Hills]}\n" + "{[Store].[USA].[CA].[Los Angeles]}\n" + "{[Store].[USA].[CA].[San Diego]}\n" + "{[Store].[USA].[CA].[San Francisco]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[OR].[Portland]}\n" + "{[Store].[USA].[OR].[Salem]}\n" + "{[Store].[USA].[WA]}\n" + "{[Store].[USA].[WA].[Bellingham]}\n" + "{[Store].[USA].[WA].[Bremerton]}\n" + "{[Store].[USA].[WA].[Seattle]}\n" + "{[Store].[USA].[WA].[Spokane]}\n" + "{[Store].[USA].[WA].[Tacoma]}\n" + "{[Store].[USA].[WA].[Walla Walla]}\n" + "{[Store].[USA].[WA].[Yakima]}\n" + "Row #0: 266,773\n" + "Row #0: 266,773\n" + "Row #1: 1,379,620\n" + "Row #1: 266,773\n" + "Row #2: 373,740\n" + "Row #2: 74,748\n" + "Row #3: \n" + "Row #3: 21,333\n" + "Row #4: \n" + "Row #4: 25,663\n" + "Row #5: \n" + "Row #5: 25,635\n" + "Row #6: \n" + "Row #6: 2,117\n" + "Row #7: 135,318\n" + "Row #7: 67,659\n" + "Row #8: \n" + "Row #8: 26,079\n" + "Row #9: \n" + "Row #9: 41,580\n" + "Row #10: 870,562\n" + "Row #10: 124,366\n" + "Row #11: \n" + "Row #11: 2,237\n" + "Row #12: \n" + "Row #12: 24,576\n" + "Row #13: \n" + "Row #13: 25,011\n" + "Row #14: \n" + "Row #14: 23,591\n" + "Row #15: \n" + "Row #15: 35,257\n" + "Row #16: \n" + "Row #16: 2,203\n" + "Row #17: \n" + "Row #17: 11,491\n"); } /** * Unit test for bug * <a href="http://jira.pentaho.com/browse/MONDRIAN-463"> * MONDRIAN-463, "Snowflake dimension with 3-way join."</a>. */ public void testBugMondrian463() { if (!MondrianProperties.instance().FilterChildlessSnowflakeMembers.get()) { // Similar to aggregates. If we turn off filtering, // we get wild stuff because of referential integrity. return; } // To build a dimension that is a 3-way snowflake, take the 2-way // product -> product_class join and convert to product -> store -> // product_class. // // It works because product_class_id covers the range 1 .. 110; // store_id covers every value in 0 .. 24; // region_id has 24 distinct values in the range 0 .. 106 (region_id 25 // occurs twice). // Therefore in store, store_id -> region_id is a 25 to 24 mapping. checkBugMondrian463(TestContext.instance().createSubstitutingCube("Sales", "<Dimension name='Product3' foreignKey='product_id'>\n" + " <Hierarchy hasAll='true' primaryKey='product_id' primaryKeyTable='product'>\n" + " <Join leftKey='product_class_id' rightKey='store_id'>\n" + " <Table name='product'/>\n" + " <Join leftKey='region_id' rightKey='product_class_id'>\n" + " <Table name='store'/>\n" + " <Table name='product_class'/>\n" + " </Join>\n" + " </Join>\n" + " <Level name='Product Family' table='product_class' column='product_family' uniqueMembers='true'/>\n" + " <Level name='Product Department' table='product_class' column='product_department' uniqueMembers='false'/>\n" + " <Level name='Product Category' table='product_class' column='product_category' uniqueMembers='false'/>\n" + " <Level name='Product Subcategory' table='product_class' column='product_subcategory' uniqueMembers='false'/>\n" + " <Level name='Product Class' table='store' column='store_id' type='Numeric' uniqueMembers='true'/>\n" + " <Level name='Brand Name' table='product' column='brand_name' uniqueMembers='false'/>\n" + " <Level name='Product Name' table='product' column='product_name' uniqueMembers='true'/>\n" + " </Hierarchy>\n" + "</Dimension>")); // As above, but using shared dimension. if (MondrianProperties.instance().ReadAggregates.get() && MondrianProperties.instance().UseAggregates.get()) { // With aggregates enabled, query gives different answer. This is // expected because some of the foreign keys have referential // integrity problems. return; } checkBugMondrian463(TestContext.instance().withSchema("<?xml version='1.0'?>\n" + "<Schema name='FoodMart'>\n" + "<Dimension name='Product3'>\n" + " <Hierarchy hasAll='true' primaryKey='product_id' primaryKeyTable='product'>\n" + " <Join leftKey='product_class_id' rightKey='store_id'>\n" + " <Table name='product'/>\n" + " <Join leftKey='region_id' rightKey='product_class_id'>\n" + " <Table name='store'/>\n" + " <Table name='product_class'/>\n" + " </Join>\n" + " </Join>\n" + " <Level name='Product Family' table='product_class' column='product_family' uniqueMembers='true'/>\n" + " <Level name='Product Department' table='product_class' column='product_department' uniqueMembers='false'/>\n" + " <Level name='Product Category' table='product_class' column='product_category' uniqueMembers='false'/>\n" + " <Level name='Product Subcategory' table='product_class' column='product_subcategory' uniqueMembers='false'/>\n" + " <Level name='Product Class' table='store' column='store_id' type='Numeric' uniqueMembers='true'/>\n" + " <Level name='Brand Name' table='product' column='brand_name' uniqueMembers='false'/>\n" + " <Level name='Product Name' table='product' column='product_name' uniqueMembers='true'/>\n" + " </Hierarchy>\n" + "</Dimension>\n" + "<Cube name='Sales'>\n" + " <Table name='sales_fact_1997'/>\n" + " <Dimension name='Time' type='TimeDimension' foreignKey='time_id'>\n" + " <Hierarchy hasAll='false' primaryKey='time_id'>\n" + " <Table name='time_by_day'/>\n" + " <Level name='Year' column='the_year' type='Numeric' uniqueMembers='true'\n" + " levelType='TimeYears'/>\n" + " <Level name='Quarter' column='quarter' uniqueMembers='false'\n" + " levelType='TimeQuarters'/>\n" + " <Level name='Month' column='month_of_year' uniqueMembers='false' type='Numeric'\n" + " levelType='TimeMonths'/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <DimensionUsage source='Product3' name='Product3' foreignKey='product_id'/>\n" + " <Measure name='Unit Sales' column='unit_sales' aggregator='sum'\n" + " formatString='#,###'/>\n" + "</Cube>\n" + "</Schema>")); } private void checkBugMondrian463(TestContext testContext) { testContext.assertQueryReturns( "select [Measures] on 0,\n" + " head([Product3].members, 10) on 1\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Product3].[All Product3s]}\n" + "{[Product3].[Drink]}\n" + "{[Product3].[Drink].[Baking Goods]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods].[Coffee]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods].[Coffee].[24]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods].[Coffee].[24].[Amigo]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods].[Coffee].[24].[Amigo].[Amigo Lox]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods].[Coffee].[24].[Curlew]}\n" + "{[Product3].[Drink].[Baking Goods].[Dry Goods].[Coffee].[24].[Curlew].[Curlew Lox]}\n" + "Row #0: 266,773\n" + "Row #1: 2,647\n" + "Row #2: 835\n" + "Row #3: 835\n" + "Row #4: 835\n" + "Row #5: 835\n" + "Row #6: 175\n" + "Row #7: 175\n" + "Row #8: 186\n" + "Row #9: 186\n"); } /** * Tests that a join nested left-deep, that is (Join (Join A B) C), fails. * The correct way to use a join is right-deep, that is (Join A (Join B C)). * Same schema as {@link #testBugMondrian463}, except left-deep. */ public void testLeftDeepJoinFails() { TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name='Product3' foreignKey='product_id'>\n" + " <Hierarchy hasAll='true' primaryKey='product_id' primaryKeyTable='product'>\n" + " <Join leftKey='store_id' rightKey='product_class_id'>\n" + " <Join leftKey='product_class_id' rightKey='region_id'>\n" + " <Table name='product'/>\n" + " <Table name='store'/>\n" + " </Join>\n" + " <Table name='product_class'/>\n" + " </Join>\n" + " <Level name='Product Family' table='product_class' column='product_family' uniqueMembers='true'/>\n" + " <Level name='Product Department' table='product_class' column='product_department' uniqueMembers='false'/>\n" + " <Level name='Product Category' table='product_class' column='product_category' uniqueMembers='false'/>\n" + " <Level name='Product Subcategory' table='product_class' column='product_subcategory' uniqueMembers='false'/>\n" + " <Level name='Product Class' table='store' column='store_id' uniqueMembers='true'/>\n" + " <Level name='Brand Name' table='product' column='brand_name' uniqueMembers='false'/>\n" + " <Level name='Product Name' table='product' column='product_name' uniqueMembers='true'/>\n" + " </Hierarchy>\n" + "</Dimension>"); try { testContext.assertSimpleQuery(); fail("expected error"); } catch (MondrianException e) { assertEquals( "Mondrian Error:Left side of join must not be a join; mondrian only supports right-deep joins.", e.getMessage()); } } /** * Test for MONDRIAN-943 and MONDRIAN-465. */ public void testCaptionWithOrdinalColumn() { final TestContext tc = TestContext.instance().createSubstitutingCube("HR", "<Dimension name=\"Position\" foreignKey=\"employee_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Position\" primaryKey=\"employee_id\">\n" + " <Table name=\"employee\"/>\n" + " <Level name=\"Management Role\" uniqueMembers=\"true\" column=\"management_role\"/>\n" + " <Level name=\"Position Title\" uniqueMembers=\"false\" column=\"position_title\" ordinalColumn=\"position_id\" captionColumn=\"position_title\"/>\n" + " </Hierarchy>\n" + "</Dimension>\n"); String mdxQuery = "WITH SET [#DataSet#] as '{Descendants([Position].[All Position], 2)}' " + "SELECT {[Measures].[Org Salary]} on columns, " + "NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [HR]"; Result result = tc.executeQuery(mdxQuery); Axis[] axes = result.getAxes(); List<Position> positions = axes[1].getPositions(); Member mall = positions.get(0).get(0); String caption = mall.getHierarchy().getCaption(); assertEquals("Position", caption); String captionValue = mall.getCaption(); assertEquals("HQ Information Systems", captionValue); mall = positions.get(14).get(0); captionValue = mall.getCaption(); assertEquals("Store Manager", captionValue); mall = positions.get(15).get(0); captionValue = mall.getCaption(); assertEquals("Store Assistant Manager", captionValue); } /** * This is a test case for bug Mondrian-923. When a virtual cube included * calculated members in its schema, they were not included in the list of * existing measures because of an override of the hierarchy schema reader * which was done at cube init time when resolving the calculated members * of the base cubes. */ public void testBugMondrian923() throws Exception { TestContext context = TestContext.instance().createSubstitutingCube("Warehouse and Sales", null, null, "<CalculatedMember name=\"Image Unit Sales\" dimension=\"Measures\"><Formula>[Measures].[Unit Sales]</Formula><CalculatedMemberProperty name=\"FORMAT_STRING\" value=\"|$#,###.00|image=icon_chart\\.gif|link=http://www\\.pentaho\\.com\"/></CalculatedMember>" + "<CalculatedMember name=\"Arrow Unit Sales\" dimension=\"Measures\"><Formula>[Measures].[Unit Sales]</Formula><CalculatedMemberProperty name=\"FORMAT_STRING\" expression=\"IIf([Measures].[Unit Sales] > 10000,'|#,###|arrow=up',IIf([Measures].[Unit Sales] > 5000,'|#,###|arrow=down','|#,###|arrow=none'))\"/></CalculatedMember>" + "<CalculatedMember name=\"Style Unit Sales\" dimension=\"Measures\"><Formula>[Measures].[Unit Sales]</Formula><CalculatedMemberProperty name=\"FORMAT_STRING\" expression=\"IIf([Measures].[Unit Sales] > 100000,'|#,###|style=green',IIf([Measures].[Unit Sales] > 50000,'|#,###|style=yellow','|#,###|style=red'))\"/></CalculatedMember>", null); for (Cube cube : context.getConnection().getSchemaReader().getCubes()) { if (cube.getName().equals("Warehouse and Sales")) { for (Dimension dim : cube.getDimensions()) { if (dim.isMeasures()) { List<Member> members = context.getConnection().getSchemaReader() .getLevelMembers(dim.getHierarchy().getLevels()[0], true); assertTrue(members.toString().contains("[Measures].[Profit Per Unit Shipped]")); assertTrue(members.toString().contains("[Measures].[Image Unit Sales]")); assertTrue(members.toString().contains("[Measures].[Arrow Unit Sales]")); assertTrue(members.toString().contains("[Measures].[Style Unit Sales]")); assertTrue(members.toString().contains("[Measures].[Average Warehouse Sale]")); return; } } } } fail("Didn't find measures in sales cube."); } public void testCubesVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<Cube name=\"Foo\" visible=\"@REPLACE_ME@\">\n" + " <Table name=\"store\"/>\n" + " <Dimension name=\"Store Type\">\n" + " <Hierarchy hasAll=\"true\">\n" + " <Level name=\"Store Type\" column=\"store_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Store Sqft\" column=\"store_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "</Cube>\n"; cubeDef = cubeDef.replace("@REPLACE_ME@", String.valueOf(testValue)); final TestContext context = TestContext.instance().create(null, cubeDef, null, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); assertTrue(testValue.equals(cube.isVisible())); } } public void testVirtualCubesVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<VirtualCube name=\"Foo\" defaultMeasure=\"Store Sales\" visible=\"@REPLACE_ME@\">\n" + " <VirtualCubeDimension cubeName=\"Sales\" name=\"Customers\"/>\n" + " <VirtualCubeMeasure cubeName=\"Sales\" name=\"[Measures].[Store Sales]\"/>\n" + "</VirtualCube>\n"; cubeDef = cubeDef.replace("@REPLACE_ME@", String.valueOf(testValue)); final TestContext context = TestContext.instance().create(null, null, cubeDef, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); assertTrue(testValue.equals(cube.isVisible())); } } public void testDimensionVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<Cube name=\"Foo\">\n" + " <Table name=\"store\"/>\n" + " <Dimension name=\"Bar\" visible=\"@REPLACE_ME@\">\n" + " <Hierarchy hasAll=\"true\">\n" + " <Level name=\"Store Type\" column=\"store_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Store Sqft\" column=\"store_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "</Cube>\n"; cubeDef = cubeDef.replace("@REPLACE_ME@", String.valueOf(testValue)); final TestContext context = TestContext.instance().create(null, cubeDef, null, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); Dimension dim = null; for (Dimension dimCheck : cube.getDimensions()) { if (dimCheck.getName().equals("Bar")) { dim = dimCheck; } } assertNotNull(dim); assertTrue(testValue.equals(dim.isVisible())); } } public void testVirtualDimensionVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<VirtualCube name=\"Foo\" defaultMeasure=\"Store Sales\">\n" + " <VirtualCubeDimension cubeName=\"Sales\" name=\"Customers\" visible=\"@REPLACE_ME@\"/>\n" + " <VirtualCubeMeasure cubeName=\"Sales\" name=\"[Measures].[Store Sales]\"/>\n" + "</VirtualCube>\n"; cubeDef = cubeDef.replace("@REPLACE_ME@", String.valueOf(testValue)); final TestContext context = TestContext.instance().create(null, null, cubeDef, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); Dimension dim = null; for (Dimension dimCheck : cube.getDimensions()) { if (dimCheck.getName().equals("Customers")) { dim = dimCheck; } } assertNotNull(dim); assertTrue(testValue.equals(dim.isVisible())); } } public void testDimensionUsageVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<Cube name=\"Foo\">\n" + " <Table name=\"store\"/>\n" + " <Dimension name=\"Bacon\">\n" + " <Hierarchy hasAll=\"true\">\n" + " <Level name=\"Store Type\" column=\"store_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Store Sqft\" column=\"store_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "</Cube>\n"; final TestContext context = TestContext.instance().create(null, cubeDef, null, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); String dimensionDef = "<DimensionUsage name=\"Bar\" source=\"Time\" foreignKey=\"time_id\" visible=\"@REPLACE_ME@\"/>"; dimensionDef = dimensionDef.replace("@REPLACE_ME@", String.valueOf(testValue)); context.getConnection().getSchema().createDimension(cube, dimensionDef); Dimension dim = null; for (Dimension dimCheck : cube.getDimensions()) { if (dimCheck.getName().equals("Bar")) { dim = dimCheck; } } assertNotNull(dim); assertTrue(testValue.equals(dim.isVisible())); } } public void testHierarchyVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<Cube name=\"Foo\">\n" + " <Table name=\"store\"/>\n" + " <Dimension name=\"Bar\">\n" + " <Hierarchy name=\"Bacon\" hasAll=\"true\" visible=\"@REPLACE_ME@\">\n" + " <Level name=\"Store Type\" column=\"store_type\" uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Store Sqft\" column=\"store_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "</Cube>\n"; cubeDef = cubeDef.replace("@REPLACE_ME@", String.valueOf(testValue)); final TestContext context = TestContext.instance().create(null, cubeDef, null, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); Dimension dim = null; for (Dimension dimCheck : cube.getDimensions()) { if (dimCheck.getName().equals("Bar")) { dim = dimCheck; } } assertNotNull(dim); final Hierarchy hier = dim.getHierarchy(); assertNotNull(hier); assertEquals(MondrianProperties.instance().SsasCompatibleNaming.get() ? "Bacon" : "Bar.Bacon", hier.getName()); assertTrue(testValue.equals(hier.isVisible())); } } public void testLevelVisibility() throws Exception { for (Boolean testValue : new Boolean[] { true, false }) { String cubeDef = "<Cube name=\"Foo\">\n" + " <Table name=\"store\"/>\n" + " <Dimension name=\"Bar\">\n" + " <Hierarchy name=\"Bacon\" hasAll=\"false\">\n" + " <Level name=\"Samosa\" column=\"store_type\" uniqueMembers=\"true\" visible=\"@REPLACE_ME@\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Store Sqft\" column=\"store_sqft\" aggregator=\"sum\"\n" + " formatString=\"#,###\"/>\n" + "</Cube>\n"; cubeDef = cubeDef.replace("@REPLACE_ME@", String.valueOf(testValue)); final TestContext context = TestContext.instance().create(null, cubeDef, null, null, null, null); final Cube cube = context.getConnection().getSchema().lookupCube("Foo", true); Dimension dim = null; for (Dimension dimCheck : cube.getDimensions()) { if (dimCheck.getName().equals("Bar")) { dim = dimCheck; } } assertNotNull(dim); final Hierarchy hier = dim.getHierarchy(); assertNotNull(hier); assertEquals(MondrianProperties.instance().SsasCompatibleNaming.get() ? "Bacon" : "Bar.Bacon", hier.getName()); final mondrian.olap.Level level = hier.getLevels()[0]; assertEquals("Samosa", level.getName()); assertTrue(testValue.equals(level.isVisible())); } } public void testNonCollapsedAggregate() throws Exception { if (MondrianProperties.instance().UseAggregates.get() == false && MondrianProperties.instance().ReadAggregates.get() == false) { return; } final String cube = "<Cube name=\"Foo\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude name=\"agg_g_ms_pcat_sales_fact_1997\"/>" + " <AggExclude name=\"agg_c_14_sales_fact_1997\"/>" + " <AggExclude name=\"agg_pl_01_sales_fact_1997\"/>" + " <AggExclude name=\"agg_ll_01_sales_fact_1997\"/>" + " <AggName name=\"agg_l_05_sales_fact_1997\">" + " <AggFactCount column=\"fact_count\"/>\n" + " <AggIgnoreColumn column=\"customer_id\"/>\n" + " <AggIgnoreColumn column=\"store_id\"/>\n" + " <AggIgnoreColumn column=\"promotion_id\"/>\n" + " <AggIgnoreColumn column=\"store_sales\"/>\n" + " <AggIgnoreColumn column=\"store_cost\"/>\n" + " <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + " <AggLevel name=\"[Product].[Product Id]\" column=\"product_id\" collapsed=\"false\"/>\n" + " </AggName>\n" + "</Table>\n" + "<Dimension foreignKey=\"product_id\" name=\"Product\">\n" + "<Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Family\" table=\"product_class\" column=\"product_family\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Department\" table=\"product_class\" column=\"product_department\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Category\" table=\"product_class\" column=\"product_category\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Subcategory\" table=\"product_class\" column=\"product_subcategory\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Brand Name\" table=\"product\" column=\"brand_name\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Name\" table=\"product\" column=\"product_name\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Id\" table=\"product\" column=\"product_id\"\n" + " uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>\n"; final TestContext context = TestContext.instance().create(null, cube, null, null, null, null); context.assertQueryReturns( "select {[Product].[Product Family].Members} on rows, {[Measures].[Unit Sales]} on columns from [Foo]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Product].[Drink]}\n" + "{[Product].[Food]}\n" + "{[Product].[Non-Consumable]}\n" + "Row #0: 24,597\n" + "Row #1: 191,940\n" + "Row #2: 50,236\n"); } public void testNonCollapsedAggregateOnNonUniqueLevelFails() throws Exception { if (MondrianProperties.instance().UseAggregates.get() == false && MondrianProperties.instance().ReadAggregates.get() == false) { return; } final String cube = "<Cube name=\"Foo\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude name=\"agg_g_ms_pcat_sales_fact_1997\"/>" + " <AggExclude name=\"agg_c_14_sales_fact_1997\"/>" + " <AggExclude name=\"agg_pl_01_sales_fact_1997\"/>" + " <AggExclude name=\"agg_ll_01_sales_fact_1997\"/>" + " <AggName name=\"agg_l_05_sales_fact_1997\">" + " <AggFactCount column=\"fact_count\"/>\n" + " <AggIgnoreColumn column=\"customer_id\"/>\n" + " <AggIgnoreColumn column=\"store_id\"/>\n" + " <AggIgnoreColumn column=\"promotion_id\"/>\n" + " <AggIgnoreColumn column=\"store_sales\"/>\n" + " <AggIgnoreColumn column=\"store_cost\"/>\n" + " <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + " <AggLevel name=\"[Product].[Product Name]\" column=\"product_id\" collapsed=\"false\"/>\n" + " </AggName>\n" + "</Table>\n" + "<Dimension foreignKey=\"product_id\" name=\"Product\">\n" + "<Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Family\" table=\"product_class\" column=\"product_family\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Department\" table=\"product_class\" column=\"product_department\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Category\" table=\"product_class\" column=\"product_category\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Subcategory\" table=\"product_class\" column=\"product_subcategory\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Brand Name\" table=\"product\" column=\"brand_name\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Name\" table=\"product\" column=\"product_name\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Id\" table=\"product\" column=\"product_id\"\n" + " uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>\n"; final TestContext context = TestContext.instance().create(null, cube, null, null, null, null); context.assertQueryThrows( "select {[Product].[Product Family].Members} on rows, {[Measures].[Unit Sales]} on columns from [Foo]", "mondrian.olap.MondrianException: Mondrian Error:Too many errors, '1', while loading/reloading aggregates."); } public void testTwoNonCollapsedAggregate() throws Exception { if (MondrianProperties.instance().UseAggregates.get() == false && MondrianProperties.instance().ReadAggregates.get() == false) { return; } final String cube = "<Cube name=\"Foo\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude name=\"agg_g_ms_pcat_sales_fact_1997\"/>" + " <AggExclude name=\"agg_c_14_sales_fact_1997\"/>" + " <AggExclude name=\"agg_pl_01_sales_fact_1997\"/>" + " <AggExclude name=\"agg_ll_01_sales_fact_1997\"/>" + " <AggName name=\"agg_l_05_sales_fact_1997\">" + " <AggFactCount column=\"fact_count\"/>\n" + " <AggIgnoreColumn column=\"customer_id\"/>\n" + " <AggIgnoreColumn column=\"promotion_id\"/>\n" + " <AggIgnoreColumn column=\"store_sales\"/>\n" + " <AggIgnoreColumn column=\"store_cost\"/>\n" + " <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + " <AggLevel name=\"[Product].[Product Id]\" column=\"product_id\" collapsed=\"false\"/>\n" + " <AggLevel name=\"[Store].[Store Id]\" column=\"store_id\" collapsed=\"false\"/>\n" + " </AggName>\n" + "</Table>\n" + "<Dimension foreignKey=\"product_id\" name=\"Product\">\n" + "<Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Family\" table=\"product_class\" column=\"product_family\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Department\" table=\"product_class\" column=\"product_department\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Category\" table=\"product_class\" column=\"product_category\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Subcategory\" table=\"product_class\" column=\"product_subcategory\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Brand Name\" table=\"product\" column=\"brand_name\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Name\" table=\"product\" column=\"product_name\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Id\" table=\"product\" column=\"product_id\"\n" + " uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + " <Dimension name=\"Store\" foreignKey=\"store_id\" >\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\"\n" + " primaryKeyTable=\"store\">\n" + " <Join leftKey=\"region_id\" rightKey=\"region_id\">\n" + " <Table name=\"store\"/>\n" + " <Table name=\"region\"/>\n" + " </Join>\n" + " <Level name=\"Store Region\" table=\"region\" column=\"sales_city\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Store Id\" table=\"store\" column=\"store_id\"\n" + " uniqueMembers=\"true\">\n" + " </Level>\n" + " </Hierarchy>\n" + " </Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>\n"; final TestContext context = TestContext.instance().create(null, cube, null, null, null, null); context.assertQueryReturns( "select {Crossjoin([Product].[Product Family].Members, [Store].[Store Id].Members)} on rows, {[Measures].[Unit Sales]} on columns from [Foo]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Product].[Drink], [Store].[Acapulco].[1]}\n" + "{[Product].[Drink], [Store].[Bellingham].[2]}\n" + "{[Product].[Drink], [Store].[Beverly Hills].[6]}\n" + "{[Product].[Drink], [Store].[Bremerton].[3]}\n" + "{[Product].[Drink], [Store].[Camacho].[4]}\n" + "{[Product].[Drink], [Store].[Guadalajara].[5]}\n" + "{[Product].[Drink], [Store].[Hidalgo].[12]}\n" + "{[Product].[Drink], [Store].[Hidalgo].[18]}\n" + "{[Product].[Drink], [Store].[Los Angeles].[7]}\n" + "{[Product].[Drink], [Store].[Merida].[8]}\n" + "{[Product].[Drink], [Store].[Mexico City].[9]}\n" + "{[Product].[Drink], [Store].[None].[0]}\n" + "{[Product].[Drink], [Store].[Orizaba].[10]}\n" + "{[Product].[Drink], [Store].[Portland].[11]}\n" + "{[Product].[Drink], [Store].[Salem].[13]}\n" + "{[Product].[Drink], [Store].[San Andres].[21]}\n" + "{[Product].[Drink], [Store].[San Diego].[24]}\n" + "{[Product].[Drink], [Store].[San Francisco].[14]}\n" + "{[Product].[Drink], [Store].[Seattle].[15]}\n" + "{[Product].[Drink], [Store].[Spokane].[16]}\n" + "{[Product].[Drink], [Store].[Tacoma].[17]}\n" + "{[Product].[Drink], [Store].[Vancouver].[19]}\n" + "{[Product].[Drink], [Store].[Victoria].[20]}\n" + "{[Product].[Drink], [Store].[Walla Walla].[22]}\n" + "{[Product].[Drink], [Store].[Yakima].[23]}\n" + "{[Product].[Food], [Store].[Acapulco].[1]}\n" + "{[Product].[Food], [Store].[Bellingham].[2]}\n" + "{[Product].[Food], [Store].[Beverly Hills].[6]}\n" + "{[Product].[Food], [Store].[Bremerton].[3]}\n" + "{[Product].[Food], [Store].[Camacho].[4]}\n" + "{[Product].[Food], [Store].[Guadalajara].[5]}\n" + "{[Product].[Food], [Store].[Hidalgo].[12]}\n" + "{[Product].[Food], [Store].[Hidalgo].[18]}\n" + "{[Product].[Food], [Store].[Los Angeles].[7]}\n" + "{[Product].[Food], [Store].[Merida].[8]}\n" + "{[Product].[Food], [Store].[Mexico City].[9]}\n" + "{[Product].[Food], [Store].[None].[0]}\n" + "{[Product].[Food], [Store].[Orizaba].[10]}\n" + "{[Product].[Food], [Store].[Portland].[11]}\n" + "{[Product].[Food], [Store].[Salem].[13]}\n" + "{[Product].[Food], [Store].[San Andres].[21]}\n" + "{[Product].[Food], [Store].[San Diego].[24]}\n" + "{[Product].[Food], [Store].[San Francisco].[14]}\n" + "{[Product].[Food], [Store].[Seattle].[15]}\n" + "{[Product].[Food], [Store].[Spokane].[16]}\n" + "{[Product].[Food], [Store].[Tacoma].[17]}\n" + "{[Product].[Food], [Store].[Vancouver].[19]}\n" + "{[Product].[Food], [Store].[Victoria].[20]}\n" + "{[Product].[Food], [Store].[Walla Walla].[22]}\n" + "{[Product].[Food], [Store].[Yakima].[23]}\n" + "{[Product].[Non-Consumable], [Store].[Acapulco].[1]}\n" + "{[Product].[Non-Consumable], [Store].[Bellingham].[2]}\n" + "{[Product].[Non-Consumable], [Store].[Beverly Hills].[6]}\n" + "{[Product].[Non-Consumable], [Store].[Bremerton].[3]}\n" + "{[Product].[Non-Consumable], [Store].[Camacho].[4]}\n" + "{[Product].[Non-Consumable], [Store].[Guadalajara].[5]}\n" + "{[Product].[Non-Consumable], [Store].[Hidalgo].[12]}\n" + "{[Product].[Non-Consumable], [Store].[Hidalgo].[18]}\n" + "{[Product].[Non-Consumable], [Store].[Los Angeles].[7]}\n" + "{[Product].[Non-Consumable], [Store].[Merida].[8]}\n" + "{[Product].[Non-Consumable], [Store].[Mexico City].[9]}\n" + "{[Product].[Non-Consumable], [Store].[None].[0]}\n" + "{[Product].[Non-Consumable], [Store].[Orizaba].[10]}\n" + "{[Product].[Non-Consumable], [Store].[Portland].[11]}\n" + "{[Product].[Non-Consumable], [Store].[Salem].[13]}\n" + "{[Product].[Non-Consumable], [Store].[San Andres].[21]}\n" + "{[Product].[Non-Consumable], [Store].[San Diego].[24]}\n" + "{[Product].[Non-Consumable], [Store].[San Francisco].[14]}\n" + "{[Product].[Non-Consumable], [Store].[Seattle].[15]}\n" + "{[Product].[Non-Consumable], [Store].[Spokane].[16]}\n" + "{[Product].[Non-Consumable], [Store].[Tacoma].[17]}\n" + "{[Product].[Non-Consumable], [Store].[Vancouver].[19]}\n" + "{[Product].[Non-Consumable], [Store].[Victoria].[20]}\n" + "{[Product].[Non-Consumable], [Store].[Walla Walla].[22]}\n" + "{[Product].[Non-Consumable], [Store].[Yakima].[23]}\n" + "Row #0: \n" + "Row #1: 208\n" + "Row #2: 1,945\n" + "Row #3: 2,288\n" + "Row #4: \n" + "Row #5: \n" + "Row #6: \n" + "Row #7: \n" + "Row #8: 2,422\n" + "Row #9: \n" + "Row #10: \n" + "Row #11: \n" + "Row #12: \n" + "Row #13: 2,371\n" + "Row #14: 3,735\n" + "Row #15: \n" + "Row #16: 2,560\n" + "Row #17: 175\n" + "Row #18: 2,213\n" + "Row #19: 2,238\n" + "Row #20: 3,092\n" + "Row #21: \n" + "Row #22: \n" + "Row #23: 191\n" + "Row #24: 1,159\n" + "Row #25: \n" + "Row #26: 1,587\n" + "Row #27: 15,438\n" + "Row #28: 17,809\n" + "Row #29: \n" + "Row #30: \n" + "Row #31: \n" + "Row #32: \n" + "Row #33: 18,294\n" + "Row #34: \n" + "Row #35: \n" + "Row #36: \n" + "Row #37: \n" + "Row #38: 18,632\n" + "Row #39: 29,905\n" + "Row #40: \n" + "Row #41: 18,369\n" + "Row #42: 1,555\n" + "Row #43: 18,159\n" + "Row #44: 16,925\n" + "Row #45: 25,453\n" + "Row #46: \n" + "Row #47: \n" + "Row #48: 1,622\n" + "Row #49: 8,192\n" + "Row #50: \n" + "Row #51: 442\n" + "Row #52: 3,950\n" + "Row #53: 4,479\n" + "Row #54: \n" + "Row #55: \n" + "Row #56: \n" + "Row #57: \n" + "Row #58: 4,947\n" + "Row #59: \n" + "Row #60: \n" + "Row #61: \n" + "Row #62: \n" + "Row #63: 5,076\n" + "Row #64: 7,940\n" + "Row #65: \n" + "Row #66: 4,706\n" + "Row #67: 387\n" + "Row #68: 4,639\n" + "Row #69: 4,428\n" + "Row #70: 6,712\n" + "Row #71: \n" + "Row #72: \n" + "Row #73: 390\n" + "Row #74: 2,140\n"); } public void testCollapsedError() throws Exception { if (MondrianProperties.instance().UseAggregates.get() == false && MondrianProperties.instance().ReadAggregates.get() == false) { return; } final String cube = "<Cube name=\"Foo\" defaultMeasure=\"Unit Sales\">\n" + " <Table name=\"sales_fact_1997\">\n" + " <AggExclude name=\"agg_g_ms_pcat_sales_fact_1997\"/>" + " <AggExclude name=\"agg_c_14_sales_fact_1997\"/>" + " <AggExclude name=\"agg_pl_01_sales_fact_1997\"/>" + " <AggExclude name=\"agg_ll_01_sales_fact_1997\"/>" + " <AggName name=\"agg_l_05_sales_fact_1997\">" + " <AggFactCount column=\"fact_count\"/>\n" + " <AggIgnoreColumn column=\"customer_id\"/>\n" + " <AggIgnoreColumn column=\"store_id\"/>\n" + " <AggIgnoreColumn column=\"promotion_id\"/>\n" + " <AggIgnoreColumn column=\"store_sales\"/>\n" + " <AggIgnoreColumn column=\"store_cost\"/>\n" + " <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + " <AggLevel name=\"[Product].[Product Id]\" column=\"product_id\" collapsed=\"true\"/>\n" + " </AggName>\n" + "</Table>\n" + "<Dimension foreignKey=\"product_id\" name=\"Product\">\n" + "<Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n" + " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n" + " <Table name=\"product\"/>\n" + " <Table name=\"product_class\"/>\n" + " </Join>\n" + " <Level name=\"Product Family\" table=\"product_class\" column=\"product_family\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Department\" table=\"product_class\" column=\"product_department\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Category\" table=\"product_class\" column=\"product_category\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Subcategory\" table=\"product_class\" column=\"product_subcategory\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Brand Name\" table=\"product\" column=\"brand_name\" uniqueMembers=\"false\"/>\n" + " <Level name=\"Product Name\" table=\"product\" column=\"product_name\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Product Id\" table=\"product\" column=\"product_id\"\n" + " uniqueMembers=\"true\"/>\n" + "</Hierarchy>\n" + "</Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + " formatString=\"Standard\"/>\n" + "</Cube>\n"; final TestContext context = TestContext.instance().create(null, cube, null, null, null, null); context.assertQueryThrows( "select {[Product].[Product Family].Members} on rows, {[Measures].[Unit Sales]} on columns from [Foo]", "Too many errors, '1', while loading/reloading aggregates."); } /** * Test case for bug * <a href="http://jira.pentaho.com/browse/MONDRIAN-1047">MONDRIAN-1047, * "IllegalArgumentException when cube has closure tables and many * levels"</a>. */ public void testBugMondrian1047() { // Test case only works under MySQL, due to how columns are quoted. switch (TestContext.instance().getDialect().getDatabaseProduct()) { case MYSQL: break; default: return; } checkBugMondrian1047(100); // 115 bits checkBugMondrian1047(50); // 65 bits checkBugMondrian1047(49); // 64 bits checkBugMondrian1047(48); // 63 bits checkBugMondrian1047(113); // 128 bits checkBugMondrian1047(114); // 129 bits } public void checkBugMondrian1047(int n) { TestContext testContext = TestContext.instance().createSubstitutingCube("HR", TestContext.repeatString(n, "<Dimension name='Position %1$d' foreignKey='employee_id'>\n" + " <Hierarchy hasAll='true' allMemberName='All Position' primaryKey='employee_id'>\n" + " <Table name='employee'/>\n" + " <Level name='Position Title' uniqueMembers='false' ordinalColumn='position_id'>\n" + " <KeyExpression><SQL dialect='generic'>`position_title` + %1$d</SQL></KeyExpression>\n" + " </Level>\n" + " </Hierarchy>\n" + "</Dimension>"), null); testContext.assertQueryReturns("select from [HR]", "Axis #0:\n" + "{}\n" + "$39,431.67"); } /** * Test case for bug * <a href="http://jira.pentaho.com/browse/MONDRIAN-1065">MONDRIAN-1065, * Incorrect data column is used in the WHERE clause of the SQL when * using Oracle DB</a>. */ public void testBugMondrian1065() { // Test case only works under Oracle switch (TestContext.instance().getDialect().getDatabaseProduct()) { case ORACLE: break; default: return; } TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", " <Dimension name=\"PandaSteak\" foreignKey=\"promotion_id\">\n" + " <Hierarchy hasAll=\"false\" primaryKey=\"lvl_3_id\">\n" + " <InlineTable alias=\"meatShack\">\n" + " <ColumnDefs>\n" + " <ColumnDef name=\"lvl_1_id\" type=\"Integer\"/>\n" + " <ColumnDef name=\"lvl_1_name\" type=\"String\"/>\n" + " <ColumnDef name=\"lvl_2_id\" type=\"Integer\"/>\n" + " <ColumnDef name=\"lvl_2_name\" type=\"String\"/>\n" + " <ColumnDef name=\"lvl_3_id\" type=\"Integer\"/>\n" + " <ColumnDef name=\"lvl_3_name\" type=\"String\"/>\n" + " </ColumnDefs>\n" + " <Rows>\n" + " <Row>\n" + " <Value column=\"lvl_1_id\">1</Value>\n" + " <Value column=\"lvl_1_name\">level 1</Value>\n" + " <Value column=\"lvl_2_id\">1</Value>\n" + " <Value column=\"lvl_2_name\">level 2 - 1</Value>\n" + " <Value column=\"lvl_3_id\">112</Value>\n" + " <Value column=\"lvl_3_name\">level 3 - 1</Value>\n" + " </Row>\n" + " <Row>\n" + " <Value column=\"lvl_1_id\">1</Value>\n" + " <Value column=\"lvl_1_name\">level 1</Value>\n" + " <Value column=\"lvl_2_id\">1</Value>\n" + " <Value column=\"lvl_2_name\">level 2 - 1</Value>\n" + " <Value column=\"lvl_3_id\">114</Value>\n" + " <Value column=\"lvl_3_name\">level 3 - 2</Value>\n" + " </Row>\n" + " </Rows>\n" + " </InlineTable>\n" + " <Level name=\"Level1\" column=\"lvl_1_id\" nameColumn=\"lvl_1_name\" />\n" + " <Level name=\"Level2\" column=\"lvl_2_id\" nameColumn=\"lvl_2_name\" />\n" + " <Level name=\"Level3\" column=\"lvl_3_id\" nameColumn=\"lvl_3_name\" />\n" + " </Hierarchy>\n" + " </Dimension>\n"); testContext.assertQueryReturns( "select non empty crossjoin({[PandaSteak].[Level3].[level 3 - 1], [PandaSteak].[Level3].[level 3 - 2]}, {[Measures].[Unit Sales], [Measures].[Store Cost]}) on columns, {[Product].[Product Family].Members} on rows from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[PandaSteak].[level 1].[level 2 - 1].[level 3 - 1], [Measures].[Unit Sales]}\n" + "{[PandaSteak].[level 1].[level 2 - 1].[level 3 - 1], [Measures].[Store Cost]}\n" + "{[PandaSteak].[level 1].[level 2 - 1].[level 3 - 2], [Measures].[Unit Sales]}\n" + "{[PandaSteak].[level 1].[level 2 - 1].[level 3 - 2], [Measures].[Store Cost]}\n" + "Axis #2:\n" + "{[Product].[Drink]}\n" + "{[Product].[Food]}\n" + "{[Product].[Non-Consumable]}\n" + "Row #0: 5\n" + "Row #0: 3.50\n" + "Row #0: 9\n" + "Row #0: 7.70\n" + "Row #1: 27\n" + "Row #1: 20.77\n" + "Row #1: 46\n" + "Row #1: 39.88\n" + "Row #2: 10\n" + "Row #2: 9.63\n" + "Row #2: 17\n" + "Row #2: 16.21\n"); } /** * This is a test for * <a href="http://jira.pentaho.com/browse/MONDRIAN-1390">MONDRIAN-1390</a> * * <p>Calling {@link SchemaReader#getLevelMembers(Level, boolean)} * directly would return the null members at the end, since it was * using TupleReader#readTuples instead of TupleReader#readMembers. */ public void testMondrian1390() throws Exception { Schema schema = getConnection().getSchema(); Cube salesCube = schema.lookupCube("Sales", true); SchemaReader sr = salesCube.getSchemaReader(null).withLocus(); List<Member> members = sr .getLevelMembers( (Level) Util.lookupCompound(sr, salesCube, Util.parseIdentifier("[Store Size in SQFT].[Store Sqft]"), true, Category.Level), true); assertEquals("[[Store Size in SQFT].[#null], " + "[Store Size in SQFT].[20319], " + "[Store Size in SQFT].[21215], " + "[Store Size in SQFT].[22478], " + "[Store Size in SQFT].[23112], " + "[Store Size in SQFT].[23593], " + "[Store Size in SQFT].[23598], " + "[Store Size in SQFT].[23688], " + "[Store Size in SQFT].[23759], " + "[Store Size in SQFT].[24597], " + "[Store Size in SQFT].[27694], " + "[Store Size in SQFT].[28206], " + "[Store Size in SQFT].[30268], " + "[Store Size in SQFT].[30584], " + "[Store Size in SQFT].[30797], " + "[Store Size in SQFT].[33858], " + "[Store Size in SQFT].[34452], " + "[Store Size in SQFT].[34791], " + "[Store Size in SQFT].[36509], " + "[Store Size in SQFT].[38382], " + "[Store Size in SQFT].[39696]]", members.toString()); } public void testMondrian1499() throws Exception { propSaver.set(propSaver.properties.UseAggregates, false); propSaver.set(propSaver.properties.ReadAggregates, false); final TestContext woAlias = TestContext.instance().withSchema("<?xml version='1.0'?>\n" + "<Schema name='FoodMart'>\n" + "<Cube name=\"HR\">\n" + " <Table name=\"salary\"/>\n" + " <Dimension name=\"Store\" foreignKey=\"employee_id\" >\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"employee_id\"\n" + " primaryKeyTable=\"employee\">\n" + " <Join leftKey=\"store_id\" rightKey=\"store_id\">\n" + " <Table name=\"employee\">\n" + " <SQL>1 = 1</SQL>\n" + " </Table>\n" + " <Table name=\"store\"/>\n" + " </Join>\n" + " <Level name=\"Store Country\" table=\"store\" column=\"store_country\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Store State\" table=\"store\" column=\"store_state\"\n" + " uniqueMembers=\"true\"/>\n" + " <Level name=\"Store City\" table=\"store\" column=\"store_city\"\n" + " uniqueMembers=\"false\"/>\n" + " <Level name=\"Store Name\" table=\"store\" column=\"store_name\"\n" + " uniqueMembers=\"true\">\n" + " <Property name=\"Store Type\" column=\"store_type\"/>\n" + " <Property name=\"Store Manager\" column=\"store_manager\"/>\n" + " <Property name=\"Store Sqft\" column=\"store_sqft\" type=\"Numeric\"/>\n" + " <Property name=\"Grocery Sqft\" column=\"grocery_sqft\" type=\"Numeric\"/>\n" + " <Property name=\"Frozen Sqft\" column=\"frozen_sqft\" type=\"Numeric\"/>\n" + " <Property name=\"Meat Sqft\" column=\"meat_sqft\" type=\"Numeric\"/>\n" + " <Property name=\"Has coffee bar\" column=\"coffee_bar\" type=\"Boolean\"/>\n" + " <Property name=\"Street address\" column=\"store_street_address\"\n" + " type=\"String\"/>\n" + " </Level>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Pay Type\" foreignKey=\"employee_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKey=\"employee_id\"\n" + " primaryKeyTable=\"employee\">\n" + " <Join leftKey=\"position_id\" rightKey=\"position_id\">\n" + " <Table name=\"employee\">\n" + " <SQL>1 = 1</SQL>\n" + " </Table>\n" + " <Table name=\"position\"/>\n" + " </Join>\n" + " <Level name=\"Pay Type\" table=\"position\" column=\"pay_type\"\n" + " uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Store Type\" foreignKey=\"employee_id\">\n" + " <Hierarchy hasAll=\"true\" primaryKeyTable=\"employee\" primaryKey=\"employee_id\">\n" + " <Join leftKey=\"store_id\" rightKey=\"store_id\">\n" + " <Table name=\"employee\">\n" + " <SQL>1 = 1</SQL>\n" + " </Table>\n" + " <Table name=\"store\"/>\n" + " </Join>\n" + " <Level name=\"Store Type\" table=\"store\" column=\"store_type\"\n" + " uniqueMembers=\"true\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Position\" foreignKey=\"employee_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Position\"\n" + " primaryKey=\"employee_id\">\n" + " <Table name=\"employee\">\n" + " <SQL>1 = 1</SQL>\n" + " </Table>\n" + " <Level name=\"Management Role\" uniqueMembers=\"true\"\n" + " column=\"management_role\"/>\n" + " <Level name=\"Position Title\" uniqueMembers=\"false\"\n" + " column=\"position_title\" ordinalColumn=\"position_id\"/>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Dimension name=\"Employees\" foreignKey=\"employee_id\">\n" + " <Hierarchy hasAll=\"true\" allMemberName=\"All Employees\"\n" + " primaryKey=\"employee_id\">\n" + " <Table name=\"employee\">\n" + " <SQL>1 = 1</SQL>\n" + " </Table>\n" + " <Level name=\"Employee Id\" type=\"Numeric\" uniqueMembers=\"true\"\n" + " column=\"employee_id\" parentColumn=\"supervisor_id\"\n" + " nameColumn=\"full_name\" nullParentValue=\"0\">\n" + " <Closure parentColumn=\"supervisor_id\" childColumn=\"employee_id\">\n" + " <Table name=\"employee_closure\"/>\n" + " </Closure>\n" + " <Property name=\"Marital Status\" column=\"marital_status\"/>\n" + " <Property name=\"Position Title\" column=\"position_title\"/>\n" + " <Property name=\"Gender\" column=\"gender\"/>\n" + " <Property name=\"Salary\" column=\"salary\"/>\n" + " <Property name=\"Education Level\" column=\"education_level\"/>\n" + " <Property name=\"Management Role\" column=\"management_role\"/>\n" + " </Level>\n" + " </Hierarchy>\n" + " </Dimension>\n" + " <Measure name=\"Org Salary\" column=\"salary_paid\" aggregator=\"sum\"\n" + " formatString=\"Currency\"/>\n" + "</Cube>\n" + "</Schema>"); woAlias.assertQueryReturns( "select {[Measures].[Org Salary]} on columns,\n" + "{[Store].[Store Country].Members} on rows from [HR]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Org Salary]}\n" + "Axis #2:\n" + "{[Store].[Canada]}\n" + "{[Store].[Mexico]}\n" + "{[Store].[USA]}\n" + "Row #0: $7,473.54\n" + "Row #1: $180,599.76\n" + "Row #2: $83,479.14\n"); } public void testMultiByteSchemaReadFromFile() throws IOException { String rawSchema = TestContext.getRawFoodMartSchema().replace( "<Hierarchy hasAll=\"true\" allMemberName=\"All Gender\" primaryKey=\"customer_id\">", "<Hierarchy name=\"\" hasAll=\"true\" allMemberName=\"All Gender\" primaryKey=\"customer_id\">"); File schemaFile = File.createTempFile("multiByteSchema", ",xml"); schemaFile.deleteOnExit(); FileOutputStream output = new FileOutputStream(schemaFile); IOUtils.write(rawSchema, output); output.close(); TestContext context = getTestContext(); final Util.PropertyList properties = context.getConnectionProperties().clone(); properties.put(RolapConnectionProperties.Catalog.name(), schemaFile.getAbsolutePath()); context.withProperties(properties).assertQueryReturns("select [Gender].members on 0 from sales", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Gender.].[All Gender]}\n" + "{[Gender.].[F]}\n" + "{[Gender.].[M]}\n" + "Row #0: 266,773\n" + "Row #0: 131,558\n" + "Row #0: 135,215\n"); } } // End SchemaTest.java