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

Example 56 with SqlPattern

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

the class TestAggregationManager method testNonEmptyCrossJoinLoneAxis.

/**
 * Tests that a NonEmptyCrossJoin uses the measure referenced by the query
 * (Store Sales) instead of the default measure (Unit Sales) in the case
 * where the query only has one result axis.  The setup here is necessarily
 * elaborate because the original bug was quite arbitrary.
 */
public void testNonEmptyCrossJoinLoneAxis() {
    // Not sure what this test is checking.
    // For now, only run it for derby.
    final Dialect dialect = getTestContext().getDialect();
    if (dialect.getDatabaseProduct() != Dialect.DatabaseProduct.DERBY) {
        return;
    }
    String mdxQuery = "With " + "Set [*NATIVE_CJ_SET] as " + "'NonEmptyCrossJoin([*BASE_MEMBERS_Store],[*BASE_MEMBERS_Product])' " + "Set [*BASE_MEMBERS_Store] as '{[Store].[All Stores].[USA]}' " + "Set [*GENERATED_MEMBERS_Store] as " + "'Generate([*NATIVE_CJ_SET], {[Store].CurrentMember})' " + "Set [*BASE_MEMBERS_Product] as " + "'{[Product].[All Products].[Food],[Product].[All Products].[Drink]}' " + "Set [*GENERATED_MEMBERS_Product] as " + "'Generate([*NATIVE_CJ_SET], {[Product].CurrentMember})' " + "Member [Store].[*FILTER_MEMBER] as 'Aggregate ([*GENERATED_MEMBERS_Store])' " + "Member [Product].[*FILTER_MEMBER] as 'Aggregate ([*GENERATED_MEMBERS_Product])' " + "Select {[Measures].[Store Sales]} on columns " + "From [Sales] " + "Where ([Store].[*FILTER_MEMBER], [Product].[*FILTER_MEMBER])";
    String derbySql = "select " + "\"store\".\"store_country\" as \"c0\", " + "\"time_by_day\".\"the_year\" as \"c1\", " + "\"product_class\".\"product_family\" as \"c2\", " + "sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from " + "\"store\" as \"store\", " + "\"sales_fact_1997\" as \"sales_fact_1997\", " + "\"time_by_day\" as \"time_by_day\", " + "\"product_class\" as \"product_class\", " + "\"product\" as \"product\" " + "where " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" and " + "\"store\".\"store_country\" = 'USA' and " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 and " + "\"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" and " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" " + "group by " + "\"store\".\"store_country\", \"time_by_day\".\"the_year\", " + "\"product_class\".\"product_family\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) };
    // For derby, the TestAggregationManager.testNonEmptyCrossJoinLoneAxis
    // test fails if the non-empty crossjoin optimizer is used.
    // With it on one gets a recursive call coming through the
    // RolapEvaluator.getCachedResult.
    assertNoQuerySql(mdxQuery, patterns);
}

Example 57 with SqlPattern

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

the class TestAggregationManager method testMultipleMeasures.

/**
 * Test a batch containing multiple measures:
 *   (store_state=CA, gender=F, measure=[Unit Sales])
 *   (store_state=CA, gender=M, measure=[Store Sales])
 *   (store_state=OR, gender=M, measure=[Unit Sales])
 */
public void testMultipleMeasures() {
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    CellRequest[] requests = new CellRequest[] { createRequest("Sales", "[Measures].[Unit Sales]", new String[] { "customer", "store" }, new String[] { "gender", "store_state" }, new String[] { "F", "CA" }), createRequest("Sales", "[Measures].[Store Sales]", new String[] { "customer", "store" }, new String[] { "gender", "store_state" }, new String[] { "M", "CA" }), createRequest("Sales", "[Measures].[Unit Sales]", new String[] { "customer", "store" }, new String[] { "gender", "store_state" }, new String[] { "F", "OR" }) };
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, "select `store`.`store_state` as `c0`," + " `customer`.`gender` as `c1`," + " sum(`agg_l_05_sales_fact_1997`.`unit_sales`) as `m0`," + " sum(`agg_l_05_sales_fact_1997`.`store_sales`) as `m1` " + "from `store` as `store`," + " `agg_l_05_sales_fact_1997` as `agg_l_05_sales_fact_1997`," + " `customer` as `customer` " + "where `agg_l_05_sales_fact_1997`.`store_id` = `store`.`store_id` " + "and `store`.`store_state` in ('CA', 'OR') " + "and `agg_l_05_sales_fact_1997`.`customer_id` = `customer`.`customer_id` " + "group by `store`.`store_state`, " + "`customer`.`gender`", 29) };
    assertRequestSql(requests, patterns);
}

Example 58 with SqlPattern

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

the class TestAggregationManager method testCountDistinctWithConstraintAggMiss.

/**
 * Test that using compound member constrant disables using AggregateTable
 */
public void testCountDistinctWithConstraintAggMiss() {
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    // Request has granularity
    // [Product].[Category]
    // and the compound constraint on
    // [Time].[Quarter]
    // 
    // whereas agg table "agg_g_ms_pcat_sales_fact_1997" has
    // granularity
    // 
    // [Time].[Quarter]
    // [Product].[Category]
    // [Gender].[Gender]
    // [Marital Status].[Marital Status]
    // 
    // The presence of compound constraint causes agg table not used.
    // 
    // Note ideally we should also test that non distinct measures could be
    // loaded from Aggregate table; however, the testing framework here uses
    // CellRequest directly which causes any compound constraint to be kept
    // separately. This will cause Aggregate tables not to be used.
    // 
    // CellRequest generated by the code form MDX will in this case not
    // separate out the compound constraint from the "regular" constraints
    // and Aggregate tables can still be used.
    List<String[]> compoundMembers = new ArrayList<String[]>();
    compoundMembers.add(new String[] { "1997", "Q1", "1" });
    CellRequest request = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "product_class", "product_class", "product_class" }, new String[] { "product_family", "product_department", "product_category" }, new String[] { "Food", "Deli", "Meat" }, makeConstraintYearQuarterMonth(compoundMembers));
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, "select " + "`product_class`.`product_family` as `c0`, " + "`product_class`.`product_department` as `c1`, " + "`product_class`.`product_category` as `c2`, " + "count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from " + "`product_class` as `product_class`, `product` as `product`, " + "`sales_fact_1997` as `sales_fact_1997`, `time_by_day` as `time_by_day` " + "where " + "`sales_fact_1997`.`product_id` = `product`.`product_id` and " + "`product`.`product_class_id` = `product_class`.`product_class_id` and " + "`product_class`.`product_family` = 'Food' and " + "`product_class`.`product_department` = 'Deli' and " + "`product_class`.`product_category` = 'Meat' and " + "`sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "(`time_by_day`.`the_year` = 1997 and `time_by_day`.`quarter` = 'Q1' and " + "`time_by_day`.`month_of_year` = 1) " + "group by " + "`product_class`.`product_family`, `product_class`.`product_department`, " + "`product_class`.`product_category`", 58) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 59 with SqlPattern

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

the class TestAggregationManager method testNonCollapsedAggregateAllLevelsPresentInQuerySnowflake.

public void testNonCollapsedAggregateAllLevelsPresentInQuerySnowflake() throws Exception {
    // MONDRIAN-1072.
    propSaver.set(MondrianProperties.instance().UseAggregates, true);
    propSaver.set(MondrianProperties.instance().ReadAggregates, true);
    final String cube = "<Schema name=\"AMC\"><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\"/>" + "    <AggExclude name=\"agg_l_03_sales_fact_1997\"/>" + "    <AggExclude name=\"agg_lc_06_sales_fact_1997\"/>" + "    <AggExclude name=\"agg_l_04_sales_fact_1997\"/>" + "    <AggExclude name=\"agg_c_10_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" + " <AggForeignKey factColumn=\"product_id\" aggColumn=\"product_id\"/>" + "        <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" + "    </AggName>\n" + "</Table>\n" + "  <Dimension name=\"Product\" 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 Family\" table=\"product_class\" column=\"product_family\"\n" + "        uniqueMembers=\"true\"/>" + "    </Hierarchy>\n" + "  </Dimension>\n" + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "<Measure name=\"Customer Count\" column=\"customer_id\" aggregator=\"distinct-count\"\n" + "      formatString=\"Standard\"/>\n" + "<Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "<Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "</Cube></Schema>\n";
    final TestContext context = TestContext.instance().withSchema(cube);
    final String mdx = "select \n" + "{ " + "[Product].[Product Family].members } on rows, " + "{[Measures].[Unit Sales]} on columns from [Foo]";
    context.assertQueryReturns(mdx, "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");
    final String sqlMysql = "select `product_class`.`product_family` as `c0`, sum(`agg_l_05_sales_fact_1997`.`unit_sales`) as `m0` from `product_class` as `product_class`, `product` as `product`, `agg_l_05_sales_fact_1997` as `agg_l_05_sales_fact_1997` where `agg_l_05_sales_fact_1997`.`product_id` = `product`.`product_id` and `product`.`product_class_id` = `product_class`.`product_class_id` group by `product_class`.`product_family`";
    assertQuerySqlOrNot(context, mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()) }, false, false, true);
}

Example 60 with SqlPattern

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

the class TestAggregationManager method testCountDistinctCannotRollup.

public void testCountDistinctCannotRollup() {
    // Summary "agg_g_ms_pcat_sales_fact_1997" doesn't match,
    // because we'd need to roll-up the distinct-count measure over
    // "month_of_year".
    CellRequest request = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "time_by_day", "time_by_day", "product_class" }, new String[] { "the_year", "quarter", "product_family" }, new String[] { "1997", "Q1", "Food" });
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, "select" + " `time_by_day`.`the_year` as `c0`," + " `time_by_day`.`quarter` as `c1`," + " `product_class`.`product_family` as `c2`," + " count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from `time_by_day` as `time_by_day`," + " `sales_fact_1997` as `sales_fact_1997`," + " `product_class` as `product_class`," + " `product` as `product` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `time_by_day`.`quarter` = `Q1`" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `product_class`.`product_family` = `Food` " + "group by `time_by_day`.`the_year`," + " `time_by_day`.`quarter`," + " `product_class`.`product_family`", 23), new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select" + " `d0` as `c0`," + " `d1` as `c1`," + " `d2` as `c2`," + " count(`m0`) as `c3` " + "from (" + "select distinct `time_by_day`.`the_year` as `d0`," + " `time_by_day`.`quarter` as `d1`," + " `product_class`.`product_family` as `d2`," + " `sales_fact_1997`.`customer_id` as `m0` " + "from `time_by_day` as `time_by_day`," + " `sales_fact_1997` as `sales_fact_1997`," + " `product_class` as `product_class`," + " `product` as `product` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `time_by_day`.`quarter` = 'Q1'" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `product_class`.`product_family` = 'Food') as `dummyname` " + "group by `d0`, `d1`, `d2`", 23), new SqlPattern(Dialect.DatabaseProduct.DERBY, "select " + "\"time_by_day\".\"the_year\" as \"c0\", \"time_by_day\".\"quarter\" as \"c1\", " + "\"product_class\".\"product_family\" as \"c2\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from " + "\"time_by_day\" as \"time_by_day\", \"sales_fact_1997\" as \"sales_fact_1997\", " + "\"product_class\" as \"product_class\", \"product\" as \"product\" " + "where " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 and " + "\"time_by_day\".\"quarter\" = 'Q1' and " + "\"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" and " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" and " + "\"product_class\".\"product_family\" = 'Food' " + "group by \"time_by_day\".\"the_year\", \"time_by_day\".\"quarter\", " + "\"product_class\".\"product_family\"", 23) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}