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

Example 6 with SqlPattern

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

the class TestAggregationManager method testAggregatingTuples.

public void testAggregatingTuples() {
    propSaver.set(propSaver.properties.LevelPreCacheThreshold, 1);
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    if (!(MondrianProperties.instance().EnableNativeCrossJoin.get())) {
        return;
    }
    // flush cache, to be sure sql is executed
    TestContext.instance().flushSchemaCache();
    // This first query verifies that simple collapsed levels in aggregate
    // tables load as tuples correctly.  The collapsed levels appear
    // in the aggregate table SQL below.
    // also note that at the time of this writing, this exercising the high
    // cardinality tuple reader
    String query = "select {[Measures].[Unit Sales]} on columns, " + "non empty CrossJoin({[Gender].[M]},{[Marital Status].[M]}) on rows " + "from [Sales] ";
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, "select " + "`agg_g_ms_pcat_sales_fact_1997`.`gender` as `c0`, " + "`agg_g_ms_pcat_sales_fact_1997`.`marital_status` as `c1` " + "from " + "`agg_g_ms_pcat_sales_fact_1997` as `agg_g_ms_pcat_sales_fact_1997` " + "where " + "(`agg_g_ms_pcat_sales_fact_1997`.`gender` = 'M') " + "and (`agg_g_ms_pcat_sales_fact_1997`.`marital_status` = 'M') " + "group by " + "`agg_g_ms_pcat_sales_fact_1997`.`gender`, " + "`agg_g_ms_pcat_sales_fact_1997`.`marital_status` " + "order by " + "ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`gender`) ASC, " + "`agg_g_ms_pcat_sales_fact_1997`.`gender` ASC, " + "ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`marital_status`) ASC, " + "`agg_g_ms_pcat_sales_fact_1997`.`marital_status` ASC", null) };
    assertQuerySqlOrNot(getTestContext(), query, patterns, false, false, false);
    assertQueryReturns(query, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Gender].[M], [Marital Status].[M]}\n" + "Row #0: 66,460\n");
    // This second query verifies that joined levels on aggregate tables
    // load correctly.
    String query2 = "select {[Measures].[Unit Sales]} ON COLUMNS, " + "NON EMPTY {[Store].[Store State].Members} ON ROWS " + "from [Sales] where [Time].[1997].[Q1]";
    SqlPattern[] patterns2 = { new SqlPattern(ACCESS_MYSQL, "select " + "`store`.`store_country` as `c0`, " + "`store`.`store_state` as `c1` " + "from " + "`store` as `store`, " + "`agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997` " + "where " + "`agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id` and " + "`agg_c_14_sales_fact_1997`.`the_year` = 1997 and " + "`agg_c_14_sales_fact_1997`.`quarter` = 'Q1' " + "group by " + "`store`.`store_country`, `store`.`store_state` " + "order by " + "ISNULL(`store`.`store_country`) ASC, " + "`store`.`store_country` ASC, " + "ISNULL(`store`.`store_state`) ASC, " + "`store`.`store_state` ASC", null) };
    assertQuerySqlOrNot(getTestContext(), query2, patterns2, false, false, false);
    assertQueryReturns(query2, "Axis #0:\n" + "{[Time].[1997].[Q1]}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[USA].[WA]}\n" + "Row #0: 16,890\n" + "Row #1: 19,287\n" + "Row #2: 30,114\n");
}

Example 7 with SqlPattern

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

the class TestAggregationManager method testAggStarWithUnusedColumnsRequiresRollup.

public void testAggStarWithUnusedColumnsRequiresRollup() {
    propSaver.set(propSaver.properties.ReadAggregates, true);
    propSaver.set(propSaver.properties.UseAggregates, true);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    final TestContext context = TestContext.instance().withSchema("<Schema name=\"FoodMart\">" + "<Cube name=\"Sales\" defaultMeasure=\"Unit Sales\">\n" + "  <Table name=\"sales_fact_1997\" />\n" + "  <Dimension name=\"Gender\" foreignKey=\"customer_id\">\n" + "    <Hierarchy hasAll=\"true\" allMemberName=\"All Gender\" primaryKey=\"customer_id\">\n" + "      <Table name=\"customer\"/>\n" + "      <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\"/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "</Cube>\n" + "</Schema>");
    RolapStar star = context.getConnection().getSchemaReader().getSchema().getStar("sales_fact_1997");
    AggStar aggStarSpy = spy(getAggStar(star, "agg_c_special_sales_fact_1997"));
    // make sure the test AggStar will be prioritized first
    when(aggStarSpy.getSize()).thenReturn(0l);
    context.getConnection().getSchemaReader().getSchema().getStar("sales_fact_1997").addAggStar(aggStarSpy);
    boolean[] rollup = { false };
    AggStar returnedStar = AggregationManager.findAgg(star, aggStarSpy.getLevelBitKey(), aggStarSpy.getMeasureBitKey(), rollup);
    assertTrue("Rollup should be true since AggStar has ignored columns ", rollup[0]);
    assertEquals(aggStarSpy, returnedStar);
    assertTrue("Unused columns are present, should be marked as " + "having ignored columns.", aggStarSpy.hasIgnoredColumns());
    String sqlOra = "select\n" + "    \"customer\".\"gender\" as \"c0\",\n" + "    sum(\"agg_c_special_sales_fact_1997\".\"unit_sales_sum\") as \"m0\"\n" + "from\n" + "    \"customer\" \"customer\",\n" + "    \"agg_c_special_sales_fact_1997\" \"agg_c_special_sales_fact_1997\"\n" + "where\n" + "    \"agg_c_special_sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" + "group by\n" + "    \"customer\".\"gender\"";
    String sqlMysql = "select\n" + "    `customer`.`gender` as `c0`,\n" + "    sum(`agg_c_special_sales_fact_1997`.`unit_sales_sum`) as `m0`\n" + "from\n" + "    `customer` as `customer`,\n" + "    `agg_c_special_sales_fact_1997` as `agg_c_special_sales_fact_1997`\n" + "where\n" + "    `agg_c_special_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "group by\n" + "    `customer`.`gender`";
    assertQuerySqlOrNot(context, "select gender.gender.members on 0 from sales", new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()), new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOra, sqlOra.length()) }, false, false, true);
}

Example 8 with SqlPattern

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

the class TestAggregationManager method testHierarchyInFactTable.

/**
 * If a hierarchy lives in the fact table, we should not generate a join.
 */
public void testHierarchyInFactTable() {
    CellRequest request = createRequest("Store", "[Measures].[Store Sqft]", "store", "store_type", "Supermarket");
    String accessMysqlSql = "select `store`.`store_type` as `c0`," + " sum(`store`.`store_sqft`) as `m0` " + "from `store` as `store` " + "where `store`.`store_type` = 'Supermarket' " + "group by `store`.`store_type`";
    String derbySql = "select " + "\"store\".\"store_type\" as \"c0\", " + "sum(\"store\".\"store_sqft\") as \"m0\" " + "from " + "\"store\" as \"store\" " + "where " + "\"store\".\"store_type\" = 'Supermarket' " + "group by \"store\".\"store_type\"";
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, accessMysqlSql, 26), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 9 with SqlPattern

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

the class TestAggregationManager method testCountDistinctBatchLoading.

/**
 * Test that cells with the same compound member constraints are
 * loaded in one Sql statement.
 *
 * Cells [Food] and [Drink] have the same constraint:
 *
 *  {[1997].[Q1].[1], [1997].[Q3].[7]}
 */
public void testCountDistinctBatchLoading() {
    List<String[]> compoundMembers = new ArrayList<String[]>();
    compoundMembers.add(new String[] { "1997", "Q1", "1" });
    compoundMembers.add(new String[] { "1997", "Q3", "7" });
    CellRequestConstraint aggConstraint = makeConstraintYearQuarterMonth(compoundMembers);
    CellRequest request1 = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "product_class" }, new String[] { "product_family" }, new String[] { "Food" }, aggConstraint);
    CellRequest request2 = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "product_class" }, new String[] { "product_family" }, new String[] { "Drink" }, aggConstraint);
    String mysqlSql = "select `product_class`.`product_family` as `c0`, " + "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 " + "`sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "(((`time_by_day`.`the_year`, `time_by_day`.`quarter`, `time_by_day`.`month_of_year`) " + "in ((1997, 'Q1', 1), (1997, 'Q3', 7)))) " + "group by `product_class`.`product_family`";
    String derbySql = "select \"product_class\".\"product_family\" as \"c0\", " + "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 " + "\"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) or " + "(\"time_by_day\".\"the_year\" = 1997 and \"time_by_day\".\"quarter\" = 'Q3' and \"time_by_day\".\"month_of_year\" = 7)) " + "group by \"product_class\".\"product_family\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) };
    assertRequestSql(new CellRequest[] { request1, request2 }, patterns);
}

Example 10 with SqlPattern

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

the class TestAggregationManager method testAggStarWithIgnoredColumnsRequiresRollup.

public void testAggStarWithIgnoredColumnsRequiresRollup() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    propSaver.set(propSaver.properties.ReadAggregates, true);
    propSaver.set(propSaver.properties.UseAggregates, true);
    final TestContext context = TestContext.instance().withSchema("<Schema name=\"FoodMart\">" + "  <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" + "    </Hierarchy>\n" + "  </Dimension>\n" + "<Cube name=\"Sales\" defaultMeasure=\"Unit Sales\">\n" + "  <Table name=\"sales_fact_1997\">\n" + "    <AggExclude name=\"agg_c_special_sales_fact_1997\" />\n" + "    <AggExclude name=\"agg_lc_100_sales_fact_1997\" />\n" + "    <AggExclude name=\"agg_lc_10_sales_fact_1997\" />\n" + "    <AggExclude name=\"agg_pc_10_sales_fact_1997\" />\n" + "    <AggName name=\"agg_c_10_sales_fact_1997\">\n" + "        <AggFactCount column=\"FACT_COUNT\"/>\n" + "        <AggIgnoreColumn column=\"Quarter\"/>\n" + "        <AggIgnoreColumn column=\"MONTH_OF_YEAR\"/>\n" + "        <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\" />\n" + "        <AggLevel name=\"[Time].[Year]\" column=\"the_year\" />\n" + "    </AggName>\n" + "  </Table>\n" + "  <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + "  <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "</Cube>\n" + "</Schema>");
    RolapStar star = context.getConnection().getSchemaReader().getSchema().getStar("sales_fact_1997");
    AggStar aggStarSpy = spy(getAggStar(star, "agg_c_10_sales_fact_1997"));
    // make sure the test AggStar will be prioritized first
    when(aggStarSpy.getSize()).thenReturn(0l);
    context.getConnection().getSchemaReader().getSchema().getStar("sales_fact_1997").addAggStar(aggStarSpy);
    boolean[] rollup = { false };
    AggStar returnedStar = AggregationManager.findAgg(star, aggStarSpy.getLevelBitKey(), aggStarSpy.getMeasureBitKey(), rollup);
    assertTrue("Rollup should be true since AggStar has ignored columns ", rollup[0]);
    assertEquals(aggStarSpy, returnedStar);
    assertTrue("Columns marked with AggIgnoreColumn, so AggStar " + ".hasIgnoredColumns() should be true", aggStarSpy.hasIgnoredColumns());
    String sqlMysql = "select\n" + "    `agg_c_10_sales_fact_1997`.`the_year` as `c0`,\n" + "    sum(`agg_c_10_sales_fact_1997`.`unit_sales`) as `m0`\n" + "from\n" + "    `agg_c_10_sales_fact_1997` as `agg_c_10_sales_fact_1997`\n" + "where\n" + "    `agg_c_10_sales_fact_1997`.`the_year` = 1997\n" + "group by\n" + "    `agg_c_10_sales_fact_1997`.`the_year`";
    String sqlOra = "select\n" + "    \"agg_c_10_sales_fact_1997\".\"the_year\" as \"c0\",\n" + "    sum(\"agg_c_10_sales_fact_1997\".\"unit_sales\") as \"m0\"\n" + "from\n" + "    \"agg_c_10_sales_fact_1997\" \"agg_c_10_sales_fact_1997\"\n" + "where\n" + "    \"agg_c_10_sales_fact_1997\".\"the_year\" = 1997\n" + "group by\n" + "    \"agg_c_10_sales_fact_1997\".\"the_year\"";
    assertQuerySqlOrNot(context, "select Time.[1997] on 0 from sales", new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()), new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOra, sqlOra.length()) }, false, false, true);
}