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

Example 71 with TestContext

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

the class FastBatchingCellReaderTest method testLoadDistinctSqlMeasure.

/**
 * Checks that in dialects that request it (e.g. LucidDB),
 * distinct aggregates based on SQL expressions,
 * e.g. <code>count(distinct "col1" + "col2"), count(distinct query)</code>,
 * are loaded individually, and separately from the other aggregates.
 */
public void testLoadDistinctSqlMeasure() {
    // Some databases cannot handle scalar subqueries inside
    // count(distinct).
    final Dialect dialect = getTestContext().getDialect();
    switch(dialect.getDatabaseProduct()) {
        case ORACLE:
        // Oracle gives 'feature not supported' in Express 10.2
        case ACCESS:
        case TERADATA:
        // the 'select' keyword." in 12.0.
        case NEOVIEW:
        // aggregate function."
        case NETEZZA:
        // supported"
        case GREENPLUM:
        // Greenplum says 'Does not support yet that query'
        case VERTICA:
            // Vertica says "Aggregate function calls cannot contain subqueries"
            return;
    }
    String cube = "<Cube name=\"Warehouse2\">" + "   <Table name=\"warehouse\"/>" + "   <DimensionUsage name=\"Store Type\" source=\"Store Type\" foreignKey=\"stores_id\"/>" + "   <Measure name=\"Count Distinct of Warehouses (Large Owned)\" aggregator=\"distinct count\" formatString=\"#,##0\">" + "       <MeasureExpression>" + "       <SQL dialect=\"generic\">(select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Owned')</SQL>" + "       </MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count Distinct of Warehouses (Large Independent)\" aggregator=\"distinct count\" formatString=\"#,##0\">" + "       <MeasureExpression>" + "       <SQL dialect=\"generic\">(select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')</SQL>" + "       </MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count All of Warehouses (Large Independent)\" aggregator=\"count\" formatString=\"#,##0\">" + "       <MeasureExpression>" + "           <SQL dialect=\"generic\">(select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')</SQL>" + "       </MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count Distinct Store+Warehouse\" aggregator=\"distinct count\" formatString=\"#,##0\">" + "       <MeasureExpression><SQL dialect=\"generic\">`store_id`+`warehouse_id`</SQL></MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count All Store+Warehouse\" aggregator=\"count\" formatString=\"#,##0\">" + "       <MeasureExpression><SQL dialect=\"generic\">`store_id`+`warehouse_id`</SQL></MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Store Count\" column=\"stores_id\" aggregator=\"count\" formatString=\"#,###\"/>" + "</Cube>";
    cube = cube.replaceAll("`", dialect.getQuoteIdentifierString());
    if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ORACLE) {
        cube = cube.replaceAll(" AS ", " ");
    }
    String query = "select " + "   [Store Type].Children on rows, " + "   {[Measures].[Count Distinct of Warehouses (Large Owned)]," + "    [Measures].[Count Distinct of Warehouses (Large Independent)]," + "    [Measures].[Count All of Warehouses (Large Independent)]," + "    [Measures].[Count Distinct Store+Warehouse]," + "    [Measures].[Count All Store+Warehouse]," + "    [Measures].[Store Count]} on columns " + "from [Warehouse2]";
    TestContext testContext = TestContext.instance().create(null, cube, null, null, null, null);
    String desiredResult = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Count Distinct of Warehouses (Large Owned)]}\n" + "{[Measures].[Count Distinct of Warehouses (Large Independent)]}\n" + "{[Measures].[Count All of Warehouses (Large Independent)]}\n" + "{[Measures].[Count Distinct Store+Warehouse]}\n" + "{[Measures].[Count All Store+Warehouse]}\n" + "{[Measures].[Store Count]}\n" + "Axis #2:\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: 1\n" + "Row #0: 0\n" + "Row #0: 0\n" + "Row #0: 6\n" + "Row #0: 6\n" + "Row #0: 6\n" + "Row #1: 1\n" + "Row #1: 0\n" + "Row #1: 0\n" + "Row #1: 2\n" + "Row #1: 2\n" + "Row #1: 2\n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #3: 0\n" + "Row #3: 1\n" + "Row #3: 1\n" + "Row #3: 4\n" + "Row #3: 4\n" + "Row #3: 4\n" + "Row #4: 0\n" + "Row #4: 1\n" + "Row #4: 1\n" + "Row #4: 4\n" + "Row #4: 4\n" + "Row #4: 4\n" + "Row #5: 0\n" + "Row #5: 1\n" + "Row #5: 3\n" + "Row #5: 8\n" + "Row #5: 8\n" + "Row #5: 8\n";
    testContext.assertQueryReturns(query, desiredResult);
    String loadCountDistinct_luciddb1 = "select " + "\"store\".\"store_type\" as \"c0\", " + "count(distinct " + "(select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" " + "from \"warehouse_class\" AS \"warehouse_class\" " + "where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Owned')) as \"m0\" " + "from \"store\" as \"store\", \"warehouse\" as \"warehouse\" " + "where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_type\"";
    String loadCountDistinct_luciddb2 = "select " + "\"store\".\"store_type\" as \"c0\", " + "count(distinct " + "(select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" " + "from \"warehouse_class\" AS \"warehouse_class\" " + "where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\" " + "from \"store\" as \"store\", \"warehouse\" as \"warehouse\" " + "where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_type\"";
    String loadOtherAggs_luciddb = "select " + "\"store\".\"store_type\" as \"c0\", " + "count(" + "(select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" " + "from \"warehouse_class\" AS \"warehouse_class\" " + "where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\", " + "count(distinct \"store_id\"+\"warehouse_id\") as \"m1\", " + "count(\"store_id\"+\"warehouse_id\") as \"m2\", " + "count(\"warehouse\".\"stores_id\") as \"m3\" " + "from \"store\" as \"store\", \"warehouse\" as \"warehouse\" " + "where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_type\"";
    // Derby splits into multiple statements.
    String loadCountDistinct_derby1 = "select \"store\".\"store_type\" as \"c0\", count(distinct (select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" from \"warehouse_class\" AS \"warehouse_class\" where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Owned')) as \"m0\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    String loadCountDistinct_derby2 = "select \"store\".\"store_type\" as \"c0\", count(distinct (select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" from \"warehouse_class\" AS \"warehouse_class\" where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    String loadCountDistinct_derby3 = "select \"store\".\"store_type\" as \"c0\", count(distinct \"store_id\"+\"warehouse_id\") as \"m0\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    String loadOtherAggs_derby = "select \"store\".\"store_type\" as \"c0\", count((select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" from \"warehouse_class\" AS \"warehouse_class\" where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\", count(\"store_id\"+\"warehouse_id\") as \"m1\", count(\"warehouse\".\"stores_id\") as \"m2\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    // MySQL does it in one statement.
    String load_mysql = "select" + " `store`.`store_type` as `c0`," + " count(distinct (select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Owned')) as `m0`," + " count(distinct (select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')) as `m1`," + " count((select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')) as `m2`," + " count(distinct `store_id`+`warehouse_id`) as `m3`," + " count(`store_id`+`warehouse_id`) as `m4`," + " count(`warehouse`.`stores_id`) as `m5` " + "from `store` as `store`," + " `warehouse` as `warehouse` " + "where `warehouse`.`stores_id` = `store`.`store_id` " + "group by `store`.`store_type`";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, loadCountDistinct_luciddb1, loadCountDistinct_luciddb1), new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, loadCountDistinct_luciddb2, loadCountDistinct_luciddb2), new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, loadOtherAggs_luciddb, loadOtherAggs_luciddb), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadCountDistinct_derby1, loadCountDistinct_derby1), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadCountDistinct_derby2, loadCountDistinct_derby2), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadCountDistinct_derby3, loadCountDistinct_derby3), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadOtherAggs_derby, loadOtherAggs_derby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, load_mysql, load_mysql) };
    assertQuerySql(testContext, query, patterns);
}

Example 72 with TestContext

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

the class FilterTest method testMultiValueInWithNullVals.

/**
 * http://jira.pentaho.com/browse/MONDRIAN-1458
 * When using a multi value IN clause which includes null values
 * against a collapsed field on an aggregate table, the dimension table
 * field was referenced as the column expression, causing sql errors.
 */
public void testMultiValueInWithNullVals() {
    // MONDRIAN-1458 - Native exclusion predicate doesn't use agg table
    // when checking for nulls
    TestContext context = getTestContext();
    if (!propSaver.properties.EnableNativeCrossJoin.get() || !propSaver.properties.ReadAggregates.get() || !propSaver.properties.UseAggregates.get()) {
        return;
    }
    String sql;
    if (!context.getDialect().supportsMultiValueInExpr()) {
        sql = "select `agg_g_ms_pcat_sales_fact_1997`.`product_family` " + "as `c0`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` as " + "`c1`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` as `c2` " + "from `agg_g_ms_pcat_sales_fact_1997` as " + "`agg_g_ms_pcat_sales_fact_1997` " + "where (not ((`agg_g_ms_pcat_sales_fact_1997`." + "`product_family` = 'Food'" + " and `agg_g_ms_pcat_sales_fact_1997`." + "`product_department` = 'Baked Goods') " + "or (`agg_g_ms_pcat_sales_fact_1997`.`product_family` " + "= 'Drink' " + "and `agg_g_ms_pcat_sales_fact_1997`." + "`product_department` = 'Dairy')) " + "or ((`agg_g_ms_pcat_sales_fact_1997`." + "`product_department` is null " + "or `agg_g_ms_pcat_sales_fact_1997`." + "`product_family` is null) " + "and not((`agg_g_ms_pcat_sales_fact_1997`.`product_family`" + " = 'Food' " + "and `agg_g_ms_pcat_sales_fact_1997`.`product_department` " + "= 'Baked Goods') " + "or (`agg_g_ms_pcat_sales_fact_1997`.`product_family` = " + "'Drink' " + "and `agg_g_ms_pcat_sales_fact_1997`.`product_department` " + "= 'Dairy')))) " + "group by `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`.`gender` " + "order by ISNULL(`agg_g_ms_pcat_sales_fact_1997`." + "`product_family`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`." + "`product_department`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`gender`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` ASC";
    } else {
        sql = "select `agg_g_ms_pcat_sales_fact_1997`." + "`product_family` as `c0`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` as `c1`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` as `c2` " + "from `agg_g_ms_pcat_sales_fact_1997` as " + "`agg_g_ms_pcat_sales_fact_1997` " + "where (not ((`agg_g_ms_pcat_sales_fact_1997`.`product_department`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family`) in " + "(('Baked Goods'," + " 'Food')," + " ('Dairy'," + " 'Drink'))) or (`agg_g_ms_pcat_sales_fact_1997`." + "`product_department` " + "is null or `agg_g_ms_pcat_sales_fact_1997`.`product_family` " + "is null)) " + "group by `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`.`gender` order by " + "ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`product_family`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`product_department`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`gender`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` ASC";
    }
    String mdx = "select NonEmptyCrossjoin( \n" + "   filter ( product.[product department].members,\n" + "      NOT ([Product].CurrentMember IN  \n" + "  { [Product].[Food].[Baked Goods], Product.Drink.Dairy})),\n" + "   gender.gender.members\n" + ")\n" + "on 0 from sales\n";
    assertQuerySql(mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sql, null) });
}

Example 73 with TestContext

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

the class FilterTest method testNativeFilterWithNullMeasure.

public void testNativeFilterWithNullMeasure() {
    // Currently this behaves differently from the non-native evaluation.
    propSaver.set(MondrianProperties.instance().EnableNativeFilter, true);
    propSaver.set(MondrianProperties.instance().ExpandNonNative, false);
    // Get a fresh connection; Otherwise the mondrian property setting
    // is not refreshed for this parameter.
    final TestContext context = getTestContext().withFreshConnection();
    try {
        context.assertQueryReturns("select Filter([Store].[Store Name].members, " + "              Not ([Measures].[Store Sqft] - [Measures].[Grocery Sqft] < 10000)) on rows, " + "{[Measures].[Store Sqft], [Measures].[Grocery Sqft]} on columns " + "from [Store]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Store Sqft]}\n" + "{[Measures].[Grocery Sqft]}\n" + "Axis #2:\n" + "{[Store].[Mexico].[DF].[Mexico City].[Store 9]}\n" + "{[Store].[Mexico].[Yucatan].[Merida].[Store 8]}\n" + "{[Store].[USA].[WA].[Bremerton].[Store 3]}\n" + "{[Store].[USA].[WA].[Tacoma].[Store 17]}\n" + "Row #0: 36,509\n" + "Row #0: 22,450\n" + "Row #1: 30,797\n" + "Row #1: 20,141\n" + "Row #2: 39,696\n" + "Row #2: 24,390\n" + "Row #3: 33,858\n" + "Row #3: 22,123\n");
    } finally {
        context.close();
    }
}

Example 74 with TestContext

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

the class FilterTest method testBugMondrian706.

/**
 * Testcase for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-706">bug MONDRIAN-706,
 * "SQL using hierarchy attribute 'Column Name' instead of 'Column' in the
 * filter"</a>.
 */
public void testBugMondrian706() {
    propSaver.set(MondrianProperties.instance().UseAggregates, false);
    propSaver.set(MondrianProperties.instance().ReadAggregates, false);
    propSaver.set(MondrianProperties.instance().DisableCaching, false);
    propSaver.set(MondrianProperties.instance().EnableNativeNonEmpty, true);
    propSaver.set(MondrianProperties.instance().CompareSiblingsByOrderKey, true);
    propSaver.set(MondrianProperties.instance().NullDenominatorProducesNull, true);
    propSaver.set(MondrianProperties.instance().ExpandNonNative, true);
    propSaver.set(MondrianProperties.instance().EnableNativeFilter, true);
    // With bug MONDRIAN-706, would generate
    // 
    // ((`store`.`store_name`, `store`.`store_city`, `store`.`store_state`)
    // in (('11', 'Portland', 'OR'), ('14', 'San Francisco', 'CA'))
    // 
    // Notice that the '11' and '14' store ID is applied on the store_name
    // instead of the store_id. So it would return no rows.
    final String badMysqlSQL = "select `store`.`store_country` as `c0`, `store`.`store_state` as `c1`, `store`.`store_city` as `c2`, `store`.`store_id` as `c3`, `store`.`store_name` as `c4`, `store`.`store_type` as `c5`, `store`.`store_manager` as `c6`, `store`.`store_sqft` as `c7`, `store`.`grocery_sqft` as `c8`, `store`.`frozen_sqft` as `c9`, `store`.`meat_sqft` as `c10`, `store`.`coffee_bar` as `c11`, `store`.`store_street_address` as `c12` from `FOODMART`.`store` as `store` where (`store`.`store_state` in ('CA', 'OR')) and ((`store`.`store_name`,`store`.`store_city`,`store`.`store_state`) in (('11','Portland','OR'),('14','San Francisco','CA'))) group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `store`.`store_id`, `store`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` having NOT((sum(`store`.`store_sqft`) is null)) " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "order by ISNULL(`c0`) ASC, `c0` ASC, " + "ISNULL(`c1`) ASC, `c1` ASC, " + "ISNULL(`c2`) ASC, `c2` ASC, " + "ISNULL(`c3`) ASC, `c3` ASC" : "order by ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC, " + "ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC, " + "ISNULL(`store`.`store_city`) ASC, `store`.`store_city` ASC, " + "ISNULL(`store`.`store_id`) ASC, `store`.`store_id` ASC");
    final String goodMysqlSQL = "select `store`.`store_country` as `c0`, `store`.`store_state` as `c1`, `store`.`store_city` as `c2`, `store`.`store_id` as `c3`, `store`.`store_name` as `c4` from `store` as `store` where (`store`.`store_state` in ('CA', 'OR')) and ((`store`.`store_id`, `store`.`store_city`, `store`.`store_state`) in ((11, 'Portland', 'OR'), (14, 'San Francisco', 'CA'))) group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `store`.`store_id`, `store`.`store_name` having NOT((sum(`store`.`store_sqft`) is null)) " + (TestContext.instance().getDialect().requiresOrderByAlias() ? " order by ISNULL(`c0`) ASC, `c0` ASC, " + "ISNULL(`c1`) ASC, `c1` ASC, " + "ISNULL(`c2`) ASC, `c2` ASC, " + "ISNULL(`c3`) ASC, `c3` ASC" : " order by ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC, " + "ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC, " + "ISNULL(`store`.`store_city`) ASC, `store`.`store_city` ASC, " + "ISNULL(`store`.`store_id`) ASC, `store`.`store_id` ASC");
    final String mdx = "With\n" + "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Store], Not IsEmpty ([Measures].[Store Sqft]))'\n" + "Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],Ancestor([Store].CurrentMember, [Store].[Store Country]).OrderKey,BASC,Ancestor([Store].CurrentMember, [Store].[Store State]).OrderKey,BASC,Ancestor([Store].CurrentMember,\n" + "[Store].[Store City]).OrderKey,BASC,[Store].CurrentMember.OrderKey,BASC)'\n" + "Set [*NATIVE_MEMBERS_Store] as 'Generate([*NATIVE_CJ_SET], {[Store].CurrentMember})'\n" + "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n" + "Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Store].currentMember)})'\n" + "Set [*BASE_MEMBERS_Store] as 'Filter([Store].[Store Name].Members,(Ancestor([Store].CurrentMember, [Store].[Store State]) In {[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR]}) AND ([Store].CurrentMember In\n" + "{[Store].[All Stores].[USA].[OR].[Portland].[Store 11],[Store].[All Stores].[USA].[CA].[San Francisco].[Store 14]}))'\n" + "Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'\n" + "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Store Sqft]', FORMAT_STRING = '#,###', SOLVE_ORDER=400\n" + "Select\n" + "[*BASE_MEMBERS_Measures] on columns,\n" + "[*SORTED_ROW_AXIS] on rows\n" + "From [Store] \n";
    final SqlPattern[] badPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, badMysqlSQL, null) };
    final SqlPattern[] goodPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, goodMysqlSQL, null) };
    final TestContext testContext = TestContext.instance().createSubstitutingCube("Store", "<Dimension name='Store Type'>\n" + "    <Hierarchy name='Store Types Hierarchy' allMemberName='All Store Types Member Name' hasAll='true'>\n" + "      <Level name='Store Type' column='store_type' uniqueMembers='true'/>\n" + "    </Hierarchy>\n" + "  </Dimension>\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_id' type='Numeric' nameColumn='store_name' uniqueMembers='false'/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n");
    assertQuerySqlOrNot(testContext, mdx, badPatterns, true, true, true);
    assertQuerySqlOrNot(testContext, mdx, goodPatterns, false, true, true);
    testContext.assertQueryReturns(mdx, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[*FORMATTED_MEASURE_0]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA].[San Francisco].[Store 14]}\n" + "{[Store].[USA].[OR].[Portland].[Store 11]}\n" + "Row #0: 22,478\n" + "Row #1: 20,319\n");
}

Example 75 with TestContext

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

the class HierarchyBugTest method verifyMemberLevelNamesIdentityOlap4jWeekly.

private void verifyMemberLevelNamesIdentityOlap4jWeekly(String mdx, String expected) throws SQLException {
    TestContext context = getTestContext();
    String dateDim = "<Dimension name=\"Date\" type=\"TimeDimension\" foreignKey=\"time_id\">\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>";
    context = context.createSubstitutingCube("Sales", dateDim);
    verifyLevelMemberNamesIdentityOlap4j(mdx, context, expected);
}