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

Example 61 with SqlPattern

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

the class TestAggregationManager method testVirtualCubeAggBugMondrian1221.

/**
 * This is a test for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-1221">MONDRIAN-1221</a>
 *
 * When performing a non-empty crossjoin over a virtual cube with agg
 * tables, there was no match with any agg tables.
 */
public void testVirtualCubeAggBugMondrian1221() {
    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=\"false\" type=\"Numeric\"\n" + "          levelType=\"TimeMonths\"/>\n" + "    </Hierarchy>\n" + "    <Hierarchy hasAll=\"true\" name=\"Weekly\" 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=\"Week\" column=\"week_of_year\" type=\"Numeric\" uniqueMembers=\"false\"\n" + "          levelType=\"TimeWeeks\"/>\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" + "      <AggName name=\"agg_c_special_sales_fact_1997\">\n" + "        <AggFactCount column=\"FACT_COUNT\"/>\n" + "        <AggIgnoreColumn column=\"foo\"/>\n" + "        <AggIgnoreColumn column=\"bar\"/>\n" + "        <AggIgnoreColumn column=\"PRODUCT_ID\" />\n" + "        <AggIgnoreColumn column=\"CUSTOMER_ID\" />\n" + "        <AggIgnoreColumn column=\"PROMOTION_ID\" />\n" + "        <AggForeignKey factColumn=\"store_id\" aggColumn=\"STORE_ID\" />\n" + "        <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"UNIT_SALES_SUM\" />\n" + "        <AggMeasure name=\"[Measures].[Store Cost]\" column=\"STORE_COST_SUM\" />\n" + "        <AggMeasure name=\"[Measures].[Store Sales]\" column=\"STORE_SALES_SUM\" />\n" + "        <AggLevel name=\"[Time].[Year]\" column=\"TIME_YEAR\" />\n" + "        <AggLevel name=\"[Time].[Quarter]\" column=\"TIME_QUARTER\" />\n" + "        <AggLevel name=\"[Time].[Month]\" column=\"TIME_MONTH\" />\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" + "  <Cube name=\"Sales2\" defaultMeasure=\"Unit Sales\">\n" + "    <Table name=\"sales_fact_1997\">\n" + "      <AggName name=\"agg_c_special_sales_fact_1997\">\n" + "        <AggFactCount column=\"FACT_COUNT\"/>\n" + "        <AggIgnoreColumn column=\"foo\"/>\n" + "        <AggIgnoreColumn column=\"bar\"/>\n" + "        <AggIgnoreColumn column=\"PRODUCT_ID\" />\n" + "        <AggIgnoreColumn column=\"CUSTOMER_ID\" />\n" + "        <AggIgnoreColumn column=\"PROMOTION_ID\" />\n" + "        <AggForeignKey factColumn=\"store_id\" aggColumn=\"STORE_ID\" />\n" + "        <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"UNIT_SALES_SUM\" />\n" + "        <AggMeasure name=\"[Measures].[Store Cost]\" column=\"STORE_COST_SUM\" />\n" + "        <AggMeasure name=\"[Measures].[Store Sales]\" column=\"STORE_SALES_SUM\" />\n" + "        <AggLevel name=\"[Time].[Year]\" column=\"TIME_YEAR\" />\n" + "        <AggLevel name=\"[Time].[Quarter]\" column=\"TIME_QUARTER\" />\n" + "        <AggLevel name=\"[Time].[Month]\" column=\"TIME_MONTH\" />\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" + "  <VirtualCube name=\"SuperSales\" defaultMeasure=\"Unit Sales\">\n" + "    <VirtualCubeDimension cubeName=\"Sales1\" name=\"Store\"/>\n" + " <VirtualCubeDimension cubeName=\"Sales1\" name=\"Time\"/>\n" + "    <VirtualCubeMeasure cubeName=\"Sales2\" name=\"[Measures].[Unit Sales]\"/>\n" + " <VirtualCubeMeasure cubeName=\"Sales2\" name=\"[Measures].[Store Cost]\"/>\n" + " <VirtualCubeMeasure cubeName=\"Sales2\" name=\"[Measures].[Store Sales]\"/>\n" + "  </VirtualCube>\n" + "</Schema>\n";
    final String mdx = "select {NonEmptyCrossJoin([Time].[Month].Members, [Store].[Store Country].Members)} on rows," + "{[Measures].[Unit Sales]} on columns " + "from [SuperSales]";
    final TestContext context = TestContext.instance().withSchema(schema);
    if (MondrianProperties.instance().EnableNativeCrossJoin.get()) {
        final String sqlMysql = "select\n" + "    `agg_c_14_sales_fact_1997`.`the_year` as `c0`,\n" + "    `agg_c_14_sales_fact_1997`.`quarter` as `c1`,\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year` as `c2`,\n" + "    `store`.`store_country` as `c3`\n" + "from\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`.`store_id` = `store`.`store_id`\n" + "group by\n" + "    `agg_c_14_sales_fact_1997`.`the_year`,\n" + "    `agg_c_14_sales_fact_1997`.`quarter`,\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year`,\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" : "    ISNULL(`agg_c_14_sales_fact_1997`.`the_year`) ASC, `agg_c_14_sales_fact_1997`.`the_year` ASC,\n" + "    ISNULL(`agg_c_14_sales_fact_1997`.`quarter`) ASC, `agg_c_14_sales_fact_1997`.`quarter` ASC,\n" + "    ISNULL(`agg_c_14_sales_fact_1997`.`month_of_year`) ASC, `agg_c_14_sales_fact_1997`.`month_of_year` ASC,\n" + "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC");
        assertQuerySqlOrNot(context, mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()) }, false, false, true);
    }
    context.assertQueryReturns(mdx, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Time].[1997].[Q1].[1], [Store].[USA]}\n" + "{[Time].[1997].[Q1].[2], [Store].[USA]}\n" + "{[Time].[1997].[Q1].[3], [Store].[USA]}\n" + "{[Time].[1997].[Q2].[4], [Store].[USA]}\n" + "{[Time].[1997].[Q2].[5], [Store].[USA]}\n" + "{[Time].[1997].[Q2].[6], [Store].[USA]}\n" + "{[Time].[1997].[Q3].[7], [Store].[USA]}\n" + "{[Time].[1997].[Q3].[8], [Store].[USA]}\n" + "{[Time].[1997].[Q3].[9], [Store].[USA]}\n" + "{[Time].[1997].[Q4].[10], [Store].[USA]}\n" + "{[Time].[1997].[Q4].[11], [Store].[USA]}\n" + "{[Time].[1997].[Q4].[12], [Store].[USA]}\n" + "Row #0: 21,628\n" + "Row #1: 20,957\n" + "Row #2: 23,706\n" + "Row #3: 20,179\n" + "Row #4: 21,081\n" + "Row #5: 21,350\n" + "Row #6: 23,763\n" + "Row #7: 21,697\n" + "Row #8: 20,388\n" + "Row #9: 19,958\n" + "Row #10: 25,270\n" + "Row #11: 26,796\n");
}

Example 62 with SqlPattern

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

the class TestAggregationManager method testCountDistinctRollupAlongDim.

/**
 * Now, here's a funny thing. Usually you can't roll up a distinct-count
 * aggregate. But if you're rolling up along the dimension which the
 * count is counting, it's OK. In this case, you know that every member
 * can only belong to one group.
 */
public void testCountDistinctRollupAlongDim() {
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    // Request has granularity
    // [Time].[Month]
    // [Product].[Category]
    // 
    // whereas agg table "agg_g_ms_pcat_sales_fact_1997" has
    // granularity
    // 
    // [Time].[Month]
    // [Product].[Category]
    // [Gender].[Gender]
    // [Marital Status].[Marital Status]
    // 
    // Because [Gender] and [Marital Status] come from the [Customer]
    // table (the same as the distinct-count measure), we can roll up.
    CellRequest request = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "time_by_day", "time_by_day", "time_by_day", "product_class", "product_class", "product_class" }, new String[] { "the_year", "quarter", "month_of_year", "product_family", "product_department", "product_category" }, new String[] { "1997", "Q1", "1", "Food", "Deli", "Meat" });
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, "select `agg_g_ms_pcat_sales_fact_1997`.`the_year` as `c0`," + " `agg_g_ms_pcat_sales_fact_1997`.`quarter` as `c1`," + " `agg_g_ms_pcat_sales_fact_1997`.`month_of_year` as `c2`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family` as `c3`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` as `c4`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_category` as `c5`," + " sum(`agg_g_ms_pcat_sales_fact_1997`.`customer_count`) as `m0` " + "from `agg_g_ms_pcat_sales_fact_1997` as `agg_g_ms_pcat_sales_fact_1997` " + "where `agg_g_ms_pcat_sales_fact_1997`.`the_year` = 1997" + " and `agg_g_ms_pcat_sales_fact_1997`.`quarter` = 'Q1'" + " and `agg_g_ms_pcat_sales_fact_1997`.`month_of_year` = 1" + " and `agg_g_ms_pcat_sales_fact_1997`.`product_family` = 'Food'" + " and `agg_g_ms_pcat_sales_fact_1997`.`product_department` = 'Deli'" + " and `agg_g_ms_pcat_sales_fact_1997`.`product_category` = 'Meat' " + "group by `agg_g_ms_pcat_sales_fact_1997`.`the_year`," + " `agg_g_ms_pcat_sales_fact_1997`.`quarter`," + " `agg_g_ms_pcat_sales_fact_1997`.`month_of_year`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_category`", 58) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 63 with SqlPattern

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

the class TestAggregationManager method testKeyExpressionCardinalityCache.

public void testKeyExpressionCardinalityCache() {
    String storeDim1 = "<Dimension name=\"Store1\">\n" + "  <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">\n" + "  <Table name=\"store\"/>\n" + "    <Level name=\"Store Country\" uniqueMembers=\"true\">\n" + "      <KeyExpression>\n" + "        <SQL dialect=\"oracle\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"hsqldb\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"derby\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"luciddb\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"mysql\">\n" + "`store_country`\n" + "        </SQL>\n" + "        <SQL dialect=\"netezza\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"neoview\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"generic\">\n" + "store_country\n" + "        </SQL>\n" + "      </KeyExpression>\n" + "    </Level>\n" + "  </Hierarchy>\n" + "</Dimension>\n";
    String storeDim2 = "<Dimension name=\"Store2\">\n" + "  <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">\n" + "  <Table name=\"store_ragged\"/>\n" + "    <Level name=\"Store Country\" uniqueMembers=\"true\">\n" + "      <KeyExpression>\n" + "        <SQL dialect=\"oracle\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"derby\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"luciddb\">\n" + "\"store_country\"\n" + "        </SQL>\n" + "        <SQL dialect=\"mysql\">\n" + "`store_country`\n" + "        </SQL>\n" + "        <SQL dialect=\"generic\">\n" + "store_country\n" + "        </SQL>\n" + "      </KeyExpression>\n" + "    </Level>\n" + "  </Hierarchy>\n" + "</Dimension>\n";
    String salesCube1 = "<Cube name=\"Sales1\" defaultMeasure=\"Unit Sales\">\n" + "  <Table name=\"sales_fact_1997\" />\n" + "  <DimensionUsage name=\"Store1\" source=\"Store1\" foreignKey=\"store_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";
    String salesCube2 = "<Cube name=\"Sales2\" defaultMeasure=\"Unit Sales\">\n" + "  <Table name=\"sales_fact_1997\" />\n" + "  <DimensionUsage name=\"Store2\" source=\"Store2\" foreignKey=\"store_id\"/>\n" + "  <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\"/>\n" + "</Cube>\n";
    String query = "select {[Measures].[Unit Sales]} ON COLUMNS, {[Store1].members} ON ROWS FROM [Sales1]";
    String query1 = "select {[Measures].[Store Sales]} ON COLUMNS, {[Store1].members} ON ROWS FROM [Sales1]";
    String query2 = "select {[Measures].[Unit Sales]} ON COLUMNS, {[Store2].members} ON ROWS FROM [Sales2]";
    String cardinalitySqlDerby1 = "select count(distinct \"store_country\") from \"store\" as \"store\"";
    String cardinalitySqlMySql1 = "select count(distinct `store_country`) as `c0` from `store` as `store`";
    String cardinalitySqlDerby2 = "select count(*) from (select distinct \"store_country\" as \"c0\" from \"store_ragged\" as \"store_ragged\") as \"init\"";
    String cardinalitySqlMySql2 = "select count(*) from (select distinct `store_country` as `c0` from `store_ragged` as `store_ragged`) as `init`";
    SqlPattern[] patterns1 = new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.DERBY, cardinalitySqlDerby1, cardinalitySqlDerby1), new SqlPattern(Dialect.DatabaseProduct.MYSQL, cardinalitySqlMySql1, cardinalitySqlMySql1) };
    SqlPattern[] patterns2 = new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.DERBY, cardinalitySqlDerby2, cardinalitySqlDerby2), new SqlPattern(Dialect.DatabaseProduct.MYSQL, cardinalitySqlMySql2, cardinalitySqlMySql2) };
    TestContext testContext = TestContext.instance().create(storeDim1 + storeDim2, salesCube1 + salesCube2, null, null, null, null);
    // This query causes "store"."store_country" cardinality to be
    // retrieved.
    testContext.executeQuery(query);
    // Query1 will find the "store"."store_country" cardinality in cache.
    assertQuerySqlOrNot(testContext, query1, patterns1, true, false, false);
    // Query2 again will not find the "store_ragged"."store_country"
    // cardinality in cache.
    assertQuerySqlOrNot(testContext, query2, patterns2, false, false, false);
}

Example 64 with SqlPattern

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

the class TestAggregationManager method testUniqueMembers.

// todo: test unrestricted column, (Unit Sales, Gender=*)
// todo: test one unrestricted, one restricted, (UNit Sales, Gender=*,
// State={CA, OR})
// todo: test with 2 dimension columns on the same table, e.g.
// (Unit Sales, Gender={F}, MaritalStatus={S}) and make sure that the
// table only appears once in the from clause.
/**
 * Tests that if a level is marked 'unique members', then its parent
 * is not constrained.
 */
public void testUniqueMembers() {
    // [Store].[Store State] is unique, so we don't expect to see any
    // references to country.
    final String mdxQuery = "select {[Measures].[Unit Sales]} on columns," + " {[Store].[USA].[CA], [Store].[USA].[OR]} on rows " + "from [Sales]";
    SqlPattern[] patterns;
    String accessMysqlSql, derbySql;
    // references to the parent level column "store_country".
    if (MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get()) {
        accessMysqlSql = "select `store`.`store_state` as `c0`," + " `agg_c_14_sales_fact_1997`.`the_year` as `c1`," + " sum(`agg_c_14_sales_fact_1997`.`unit_sales`) as `m0` " + "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 `store`.`store_state` in ('CA', 'OR')" + " and `agg_c_14_sales_fact_1997`.`the_year` = 1997 " + "group by `store`.`store_state`," + " `agg_c_14_sales_fact_1997`.`the_year`";
        derbySql = "select " + "\"store\".\"store_state\" as \"c0\", \"agg_c_14_sales_fact_1997\".\"the_year\" as \"c1\", " + "sum(\"agg_c_14_sales_fact_1997\".\"unit_sales\") as \"m0\" " + "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 " + "\"store\".\"store_state\" in ('CA', 'OR') and " + "\"agg_c_14_sales_fact_1997\".\"the_year\" = 1997 " + "group by " + "\"store\".\"store_state\", \"agg_c_14_sales_fact_1997\".\"the_year\"";
        patterns = new SqlPattern[] { new SqlPattern(ACCESS_MYSQL, accessMysqlSql, 50), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) };
    } else {
        accessMysqlSql = "select `store`.`store_state` as `c0`," + " `time_by_day`.`the_year` as `c1`," + " 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` " + "where `sales_fact_1997`.`store_id` = `store`.`store_id`" + " and `store`.`store_state` in ('CA', 'OR')" + " and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997 " + "group by `store`.`store_state`, `time_by_day`.`the_year`";
        derbySql = "select \"store\".\"store_state\" as \"c0\", \"time_by_day\".\"the_year\" as \"c1\", " + "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\" " + "where " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" and " + "\"store\".\"store_state\" in ('CA', 'OR') and " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 " + "group by " + "\"store\".\"store_state\", \"time_by_day\".\"the_year\"";
        patterns = new SqlPattern[] { new SqlPattern(ACCESS_MYSQL, accessMysqlSql, 50), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) };
    }
    assertQuerySql(mdxQuery, patterns);
}

Example 65 with SqlPattern

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

the class TestAggregationManager method testTwoNonCollapsedAggregate.

public void testTwoNonCollapsedAggregate() throws Exception {
    propSaver.set(MondrianProperties.instance().UseAggregates, true);
    propSaver.set(MondrianProperties.instance().ReadAggregates, true);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    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);
    final String mdx = "select {Crossjoin([Product].[Product Family].Members, [Store].[Store Id].Members)} on rows, {[Measures].[Unit Sales]} on columns from [Foo]";
    final String sqlOracle = "select\n" + "    \"product_class\".\"product_family\" as \"c0\",\n" + "    \"agg_l_05_sales_fact_1997\".\"store_id\" as \"c1\",\n" + "    sum(\"agg_l_05_sales_fact_1997\".\"unit_sales\") as \"m0\"\n" + "from\n" + "    \"product_class\" \"product_class\",\n" + "    \"product\" \"product\",\n" + "    \"agg_l_05_sales_fact_1997\" \"agg_l_05_sales_fact_1997\"\n" + "where\n" + "    \"agg_l_05_sales_fact_1997\".\"product_id\" = \"product\".\"product_id\"\n" + "and\n" + "    \"product\".\"product_class_id\" = \"product_class\".\"product_class_id\"\n" + "group by\n" + "    \"product_class\".\"product_family\",\n" + "    \"agg_l_05_sales_fact_1997\".\"store_id\"";
    final String sqlMysql = "select\n" + "    `product_class`.`product_family` as `c0`,\n" + "    `agg_l_05_sales_fact_1997`.`store_id` as `c1`,\n" + "    sum(`agg_l_05_sales_fact_1997`.`unit_sales`) as `m0`\n" + "from\n" + "    `product_class` as `product_class`,\n" + "    `product` as `product`,\n" + "    `agg_l_05_sales_fact_1997` as `agg_l_05_sales_fact_1997`\n" + "where\n" + "    `agg_l_05_sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "group by\n" + "    `product_class`.`product_family`,\n" + "    `agg_l_05_sales_fact_1997`.`store_id`";
    assertQuerySqlOrNot(context, mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()), new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()) }, false, false, true);
}