Examples with TestContext mondrian.test.TestContext used on opensource projects

Example 96 with TestContext

use of mondrian.test.TestContext in project mondrian by pentaho.

the class TestAggregationManager method testMondrian1271.

/**
 * This is a test for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-1271">MONDRIAN-1271</a>
 *
 * When a non-collapsed AggLevel was used, Mondrian would join on the
 * key column of the lowest level instead of the one it should have.
 */
public void testMondrian1271() {
    if (!propSaver.properties.EnableNativeCrossJoin.get()) {
        return;
    }
    propSaver.set(MondrianProperties.instance().UseAggregates, true);
    propSaver.set(MondrianProperties.instance().ReadAggregates, true);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    final String schema = "<?xml version=\"1.0\"?>\n" + "<Schema name=\"custom\">\n" + "  <Dimension name=\"Store\">\n" + "    <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">\n" + "      <Table name=\"store\"/>\n" + "      <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>\n" + "      <Level name=\"Store State\" column=\"store_state\" uniqueMembers=\"true\"/>\n" + "      <Level name=\"Store City\" column=\"store_city\" uniqueMembers=\"false\"/>\n" + "      <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\">\n" + "      </Level>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Dimension name=\"Time\" type=\"TimeDimension\">\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=\"true\" type=\"Numeric\"\n" + "          levelType=\"TimeMonths\"/>\n" + "      <Level name=\"Day\" column=\"day_of_month\" uniqueMembers=\"false\" type=\"Numeric\"\n" + "          levelType=\"TimeDays\"/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Cube name=\"Sales1\" defaultMeasure=\"Unit Sales\">\n" + "    <Table name=\"sales_fact_1997\">\n" + "      <AggExclude name=\"agg_c_special_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_c_10_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_l_04_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_g_ms_pcat_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_lc_06_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_l_03_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_lc_100_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_pl_01_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_ll_01_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_l_05_sales_fact_1997\"/>" + "      <AggName name=\"agg_c_14_sales_fact_1997\">\n" + "        <AggFactCount column=\"fact_count\"/>\n" + "        <AggIgnoreColumn column=\"product_id\" />\n" + "        <AggIgnoreColumn column=\"customer_id\" />\n" + "        <AggIgnoreColumn column=\"promotion_id\" />\n" + "        <AggIgnoreColumn column=\"the_year\" />\n" + "        <AggIgnoreColumn column=\"quarter\" />\n" + "        <AggForeignKey factColumn=\"store_id\" aggColumn=\"store_id\" />\n" + "        <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + "        <AggMeasure name=\"[Measures].[Store Cost]\" column=\"store_cost\" />\n" + "        <AggMeasure name=\"[Measures].[Store Sales]\" column=\"store_sales\" />\n" + "        <AggLevel name=\"[Time].[Month]\" column=\"month_of_year\" collapsed=\"false\" />\n" + "      </AggName>\n" + "    </Table>\n" + "    <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + "    <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_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" + "    <Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\"\n" + "      formatString=\"#,###.00\"/>\n" + "  </Cube>\n" + "</Schema>\n";
    final String mdx = "select {NonEmptyCrossJoin([Time].[Year].Members, [Store].[Store Country].Members)} on rows," + "{[Measures].[Unit Sales]} on columns " + "from [Sales1]";
    final String mdxTooLowForAgg = "select {NonEmptyCrossJoin([Time].[Day].Members, [Store].[Store Country].Members)} on rows," + "{[Measures].[Unit Sales]} on columns " + "from [Sales1]";
    final String sqlMysqlTupleQuery = "select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `store`.`store_country` as `c1`\n" + "from\n" + "    `time_by_day` as `time_by_day`,\n" + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n" + "    `store` as `store`\n" + "where\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year` = `time_by_day`.`month_of_year`\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "group by\n" + "    `time_by_day`.`the_year`,\n" + "    `store`.`store_country`\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC" : "    ISNULL(`time_by_day`.`the_year`) ASC, `time_by_day`.`the_year` ASC,\n" + "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC");
    final String sqlMysqlSegmentQuery = "select\n" + "    `store`.`store_country` as `c0`,\n" + "    `time_by_day`.`the_year` as `c1`,\n" + "    sum(`agg_c_14_sales_fact_1997`.`unit_sales`) as `m0`\n" + "from\n" + "    `store` as `store`,\n" + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `store`.`store_country` = 'USA'\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year` = `time_by_day`.`month_of_year`\n" + "group by\n" + "    `store`.`store_country`,\n" + "    `time_by_day`.`the_year`";
    final String sqlMysqlTooLowTupleQuery = "select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `time_by_day`.`quarter` as `c1`,\n" + "    `time_by_day`.`month_of_year` as `c2`,\n" + "    `time_by_day`.`day_of_month` as `c3`,\n" + "    `store`.`store_country` as `c4`\n" + "from\n" + "    `time_by_day` as `time_by_day`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `store` as `store`\n" + "where\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + "    `sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "group by\n" + "    `time_by_day`.`the_year`,\n" + "    `time_by_day`.`quarter`,\n" + "    `time_by_day`.`month_of_year`,\n" + "    `time_by_day`.`day_of_month`,\n" + "    `store`.`store_country`\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC,\n" + "    ISNULL(`c2`) ASC, `c2` ASC,\n" + "    ISNULL(`c3`) ASC, `c3` ASC,\n" + "    ISNULL(`c4`) ASC, `c4` ASC" : "    ISNULL(`time_by_day`.`the_year`) ASC, `time_by_day`.`the_year` ASC,\n" + "    ISNULL(`time_by_day`.`quarter`) ASC, `time_by_day`.`quarter` ASC,\n" + "    ISNULL(`time_by_day`.`month_of_year`) ASC, `time_by_day`.`month_of_year` ASC,\n" + "    ISNULL(`time_by_day`.`day_of_month`) ASC, `time_by_day`.`day_of_month` ASC,\n" + "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC");
    final String sqlMysqlTooLowSegmentQuery = "select\n" + "    `store`.`store_country` as `c0`,\n" + "    `time_by_day`.`month_of_year` as `c1`,\n" + "    `time_by_day`.`day_of_month` as `c2`,\n" + "    sum(`sales_fact_1997`.`unit_sales`) as `m0`\n" + "from\n" + "    `store` as `store`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `store`.`store_country` = 'USA'\n" + "and\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "group by\n" + "    `store`.`store_country`,\n" + "    `time_by_day`.`month_of_year`,\n" + "    `time_by_day`.`day_of_month`";
    final TestContext context = TestContext.instance().withSchema(schema);
    assertQuerySqlOrNot(context, mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysqlTupleQuery, sqlMysqlTupleQuery.length()) }, false, false, true);
    assertQuerySqlOrNot(context, mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysqlSegmentQuery, sqlMysqlSegmentQuery.length()) }, false, false, true);
    // Because we have caused a many-to-many relation between the agg table
    // and the dim table, we expect retarded numbers here.
    context.assertQueryReturns(mdx, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Time].[1997], [Store].[USA]}\n" + "{[Time].[1998], [Store].[USA]}\n" + "Row #0: 8,119,905\n" + "Row #1: 8,119,905\n");
    // Make sure that queries on lower levels don't trigger a
    // false positive with the agg matcher.
    assertQuerySqlOrNot(context, mdxTooLowForAgg, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysqlTooLowTupleQuery, sqlMysqlTooLowTupleQuery.length()) }, false, false, true);
    assertQuerySqlOrNot(context, mdxTooLowForAgg, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysqlTooLowSegmentQuery, sqlMysqlTooLowSegmentQuery.length()) }, false, false, true);
}

Example 97 with TestContext

use of mondrian.test.TestContext in project mondrian by pentaho.

the class TestAggregationManager method testColumnCadinalityCache.

/**
 *  Test that once fetched, column cardinality can be shared between
 *  different queries using the same connection.
 *
 *  <p>Test also that expressions with only table alias difference do not
 *  share cardinality result.
 */
public void testColumnCadinalityCache() {
    String query1 = "select " + "NonEmptyCrossJoin(" + "[Product].[Product Family].Members, " + "[Gender].[Gender].Members) on columns " + "from [Sales]";
    String query2 = "select " + "NonEmptyCrossJoin(" + "[Store].[Store Country].Members, " + "[Product].[Product Family].Members) on columns " + "from [Warehouse]";
    String cardinalitySqlDerby = "select " + "count(distinct \"product_class\".\"product_family\") " + "from \"product_class\" as \"product_class\"";
    String cardinalitySqlMySql = "select " + "count(distinct `product_class`.`product_family`) as `c0` " + "from `product_class` as `product_class`";
    SqlPattern[] patterns = new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.DERBY, cardinalitySqlDerby, cardinalitySqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, cardinalitySqlMySql, cardinalitySqlMySql) };
    final TestContext context = getTestContext().withFreshConnection();
    try {
        // This MDX gets the [Product].[Product Family] cardinality
        // from the DB.
        context.executeQuery(query1);
        // This MDX should be able to reuse the cardinality for
        // [Product].[Product Family]; and should not issue a SQL to fetch
        // that from DB again.
        assertQuerySqlOrNot(context, query2, patterns, true, false, false);
    } finally {
        context.close();
    }
}

Example 98 with TestContext

use of mondrian.test.TestContext in project mondrian by pentaho.

the class TestAggregationManager method testLevelKeyAsSqlExpWithAgg.

/**
 * Test case for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-812">bug MONDRIAN-812,
 * "Issues with aggregate table recognition when using
 * &lt;KeyExpression&gt;&lt;SQL&gt; ... &lt;/SQL&gt;&lt;/KeyExpression&gt;
 * to define a level"</a>. Using a key expression for a level
 * element would make aggregate tables fail to be used.
 */
public void testLevelKeyAsSqlExpWithAgg() {
    final boolean p;
    switch(getTestContext().getDialect().getDatabaseProduct()) {
        case POSTGRESQL:
            // Results are slightly different order on Postgres. It collates
            // "Sale Winners" before "Sales Days", because " " < "A".
            p = true;
            break;
        default:
            p = false;
            break;
    }
    propSaver.set(MondrianProperties.instance().UseAggregates, true);
    propSaver.set(MondrianProperties.instance().ReadAggregates, true);
    final String mdxQuery = "select non empty{[Promotions].[All Promotions].Children} ON rows, " + "non empty {[Store].[All Stores]} ON columns " + "from [Sales] " + "where {[Measures].[Unit Sales]}";
    // Provoke an error in the key resolution to prove it uses it.
    final String colName = TestContext.instance().getDialect().quoteIdentifier("promotion_name");
    TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Promotions\" foreignKey=\"promotion_id\">\n" + "  <Hierarchy hasAll=\"true\" allMemberName=\"All Promotions\" primaryKey=\"promotion_id\" defaultMember=\"[All Promotions]\">\n" + "    <Table name=\"promotion\"/>\n" + "    <Level name=\"Promotion Name\" column=\"promotion_name\" uniqueMembers=\"true\">\n" + "      <KeyExpression><SQL>ERROR_TEST_FUNCTION_NAME(" + colName + ")</SQL></KeyExpression>\n" + "    </Level>\n" + "  </Hierarchy>\n" + "</Dimension>");
    testContext.assertQueryThrows(mdxQuery, "ERROR_TEST_FUNCTION_NAME");
    // Run for real this time
    testContext = TestContext.instance().createSubstitutingCube("Sales", "<Dimension name=\"Promotions\" foreignKey=\"promotion_id\">\n" + "  <Hierarchy hasAll=\"true\" allMemberName=\"All Promotions\" primaryKey=\"promotion_id\" defaultMember=\"[All Promotions]\">\n" + "    <Table name=\"promotion\"/>\n" + "    <Level name=\"Promotion Name\" column=\"promotion_name\" uniqueMembers=\"true\">\n" + "      <KeyExpression><SQL>RTRIM(" + colName + ")</SQL></KeyExpression>\n" + "    </Level>\n" + "  </Hierarchy>\n" + "</Dimension>");
    testContext.assertQueryReturns("select non empty{[Promotions].[All Promotions].Children} ON rows, " + "non empty {[Store].[All Stores]} ON columns " + "from [Sales] " + "where {[Measures].[Unit Sales]}", "Axis #0:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #1:\n" + "{[Store].[All Stores]}\n" + "Axis #2:\n" + "{[Promotions].[Bag Stuffers]}\n" + "{[Promotions].[Best Savings]}\n" + "{[Promotions].[Big Promo]}\n" + "{[Promotions].[Big Time Discounts]}\n" + "{[Promotions].[Big Time Savings]}\n" + "{[Promotions].[Bye Bye Baby]}\n" + "{[Promotions].[Cash Register Lottery]}\n" + "{[Promotions].[Dimes Off]}\n" + "{[Promotions].[Dollar Cutters]}\n" + "{[Promotions].[Dollar Days]}\n" + "{[Promotions].[Double Down Sale]}\n" + "{[Promotions].[Double Your Savings]}\n" + "{[Promotions].[Free For All]}\n" + "{[Promotions].[Go For It]}\n" + "{[Promotions].[Green Light Days]}\n" + "{[Promotions].[Green Light Special]}\n" + "{[Promotions].[High Roller Savings]}\n" + "{[Promotions].[I Cant Believe It Sale]}\n" + "{[Promotions].[Money Savers]}\n" + "{[Promotions].[Mystery Sale]}\n" + "{[Promotions].[No Promotion]}\n" + "{[Promotions].[One Day Sale]}\n" + "{[Promotions].[Pick Your Savings]}\n" + "{[Promotions].[Price Cutters]}\n" + "{[Promotions].[Price Destroyers]}\n" + "{[Promotions].[Price Savers]}\n" + "{[Promotions].[Price Slashers]}\n" + "{[Promotions].[Price Smashers]}\n" + "{[Promotions].[Price Winners]}\n" + (p ? "" : "{[Promotions].[Sale Winners]}\n") + "{[Promotions].[Sales Days]}\n" + "{[Promotions].[Sales Galore]}\n" + (!p ? "" : "{[Promotions].[Sale Winners]}\n") + "{[Promotions].[Save-It Sale]}\n" + "{[Promotions].[Saving Days]}\n" + "{[Promotions].[Savings Galore]}\n" + "{[Promotions].[Shelf Clearing Days]}\n" + "{[Promotions].[Shelf Emptiers]}\n" + "{[Promotions].[Super Duper Savers]}\n" + "{[Promotions].[Super Savers]}\n" + "{[Promotions].[Super Wallet Savers]}\n" + "{[Promotions].[Three for One]}\n" + "{[Promotions].[Tip Top Savings]}\n" + "{[Promotions].[Two Day Sale]}\n" + "{[Promotions].[Two for One]}\n" + "{[Promotions].[Unbeatable Price Savers]}\n" + "{[Promotions].[Wallet Savers]}\n" + "{[Promotions].[Weekend Markdown]}\n" + "{[Promotions].[You Save Days]}\n" + "Row #0: 901\n" + "Row #1: 2,081\n" + "Row #2: 1,789\n" + "Row #3: 932\n" + "Row #4: 700\n" + "Row #5: 921\n" + "Row #6: 4,792\n" + "Row #7: 1,219\n" + "Row #8: 781\n" + "Row #9: 1,652\n" + "Row #10: 1,959\n" + "Row #11: 843\n" + "Row #12: 1,638\n" + "Row #13: 689\n" + "Row #14: 1,607\n" + "Row #15: 436\n" + "Row #16: 2,654\n" + "Row #17: 253\n" + "Row #18: 899\n" + "Row #19: 1,021\n" + "Row #20: 195,448\n" + "Row #21: 1,973\n" + "Row #22: 323\n" + "Row #23: 1,624\n" + "Row #24: 2,173\n" + "Row #25: 4,094\n" + "Row #26: 1,148\n" + "Row #27: 504\n" + "Row #28: 1,294\n" + (p ? ("Row #29: 2,055\n" + "Row #30: 2,572\n" + "Row #31: 444\n") : ("Row #29: 444\n" + "Row #30: 2,055\n" + "Row #31: 2,572\n")) + "Row #32: 2,203\n" + "Row #33: 1,446\n" + "Row #34: 1,382\n" + "Row #35: 754\n" + "Row #36: 2,118\n" + "Row #37: 2,628\n" + "Row #38: 2,497\n" + "Row #39: 1,183\n" + "Row #40: 1,155\n" + "Row #41: 525\n" + "Row #42: 2,053\n" + "Row #43: 335\n" + "Row #44: 2,100\n" + "Row #45: 916\n" + "Row #46: 914\n" + "Row #47: 3,145\n");
}

Example 99 with TestContext

use of mondrian.test.TestContext in project mondrian by pentaho.

the class VirtualCubeTest method testCalculatedMeasureAsDefaultMeasureInVC.

public void testCalculatedMeasureAsDefaultMeasureInVC() {
    TestContext testContext = TestContext.instance().create(null, null, "<VirtualCube name=\"Sales vs Warehouse\" defaultMeasure=\"Profit\">\n" + "<VirtualCubeDimension name=\"Product\"/>\n" + "<VirtualCubeMeasure cubeName=\"Warehouse\" " + "name=\"[Measures].[Warehouse Sales]\"/>\n" + "<VirtualCubeMeasure cubeName=\"Sales\" " + "name=\"[Measures].[Unit Sales]\"/>\n" + "<VirtualCubeMeasure cubeName=\"Sales\" " + "name=\"[Measures].[Profit]\"/>\n" + "</VirtualCube>", null, null, null);
    String query1 = "select from [Sales vs Warehouse]";
    String query2 = "select from [Sales vs Warehouse] where measures.profit";
    assertQueriesReturnSimilarResults(query1, query2, testContext);
}

Example 100 with TestContext

use of mondrian.test.TestContext in project mondrian by pentaho.

the class VirtualCubeTest method testMemberVisibility.

public void testMemberVisibility() {
    TestContext testContext = TestContext.instance().create(null, null, "<VirtualCube name=\"Warehouse and Sales Member Visibility\">\n" + "  <VirtualCubeDimension cubeName=\"Sales\" name=\"Customers\"/>\n" + "  <VirtualCubeDimension name=\"Time\"/>\n" + "  <VirtualCubeMeasure cubeName=\"Sales\" name=\"[Measures].[Sales Count]\" visible=\"true\" />\n" + "  <VirtualCubeMeasure cubeName=\"Sales\" name=\"[Measures].[Store Cost]\" visible=\"false\" />\n" + "  <VirtualCubeMeasure cubeName=\"Sales\" name=\"[Measures].[Store Sales]\"/>\n" + "  <VirtualCubeMeasure cubeName=\"Warehouse\" name=\"[Measures].[Units Shipped]\" visible=\"false\" />\n" + "  <CalculatedMember name=\"Profit\" dimension=\"Measures\" visible=\"false\" >\n" + "    <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>\n" + "  </CalculatedMember>\n" + "</VirtualCube>", null, null, null);
    Result result = testContext.executeQuery("select {[Measures].[Sales Count],\n" + " [Measures].[Store Cost],\n" + " [Measures].[Store Sales],\n" + " [Measures].[Units Shipped],\n" + " [Measures].[Profit]} on columns\n" + "from [Warehouse and Sales Member Visibility]");
    // explicitly visible
    assertVisibility(result, 0, "Sales Count", true);
    assertVisibility(result, 1, "Store Cost", // explicitly invisible
    false);
    // visible by default
    assertVisibility(result, 2, "Store Sales", true);
    assertVisibility(result, 3, "Units Shipped", // explicitly invisible
    false);
    // explicitly invisible
    assertVisibility(result, 4, "Profit", false);
}