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

Example 36 with TestContext

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

the class NativeEvalVirtualCubeTest method testTupleQueryShouldBeCachedForVirtualCube.

/**
 * Testcase for bug
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-2597">MONDRIAN-2597,
 * "readTuples and cardinality queries sent twice to the database
 * when using Virtual Cube (Not cached)"</a>.
 */
public void testTupleQueryShouldBeCachedForVirtualCube() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mySqlMembersQuery = "select\n" + "    *\n" + "from\n" + "    (select\n" + "    `product_class`.`product_family` as `c0`,\n" + "    `product_class`.`product_department` as `c1`,\n" + "    `time_by_day`.`the_year` as `c2`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "group by\n" + "    `product_class`.`product_family`,\n" + "    `product_class`.`product_department`,\n" + "    `time_by_day`.`the_year`\n" + "union\n" + "select\n" + "    `product_class`.`product_family` as `c0`,\n" + "    `product_class`.`product_department` as `c1`,\n" + "    `time_by_day`.`the_year` as `c2`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `inventory_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "group by\n" + "    `product_class`.`product_family`,\n" + "    `product_class`.`product_department`,\n" + "    `time_by_day`.`the_year`) as `unionQuery`\n" + "order by\n" + "    ISNULL(1) ASC, 1 ASC,\n" + "    ISNULL(2) ASC, 2 ASC,\n" + "    ISNULL(3) ASC, 3 ASC";
    TestContext tc = getTestContext().withFreshConnection();
    SqlPattern mysqlPatternMembers = new SqlPattern(Dialect.DatabaseProduct.MYSQL, mySqlMembersQuery, mySqlMembersQuery);
    // The MDX with default measure of [Warehouse and Sales] virtual cube:
    // Store Sales that belongs to the redular [Sale] cube
    String mdx = "WITH\n" + "SET [*NATIVE_CJ_SET] AS 'NONEMPTYCROSSJOIN([*BASE_MEMBERS__Product_],[*BASE_MEMBERS__Time_])'\n" + "SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],ANCESTOR([Product].CURRENTMEMBER, [Product].[Product Family]).ORDERKEY,BASC,[Product].CURRENTMEMBER.ORDERKEY,BASC,[Time].CURRENTMEMBER.ORDERKEY,BASC)'\n" + "SET [*BASE_MEMBERS__Measures_] AS '{[Measures].[*FORMATTED_MEASURE_0]}'\n" + "SET [*BASE_MEMBERS__Product_] AS '[Product].[Product Department].MEMBERS'\n" + "SET [*BASE_MEMBERS__Time_] AS '[Time].[Year].MEMBERS'\n" + "SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET], {([Product].CURRENTMEMBER,[Time].CURRENTMEMBER)})'\n" + "MEMBER [Measures].[*FORMATTED_MEASURE_0] AS '[Measures].[Store Sales]', FORMAT_STRING = '#,###.00', SOLVE_ORDER=500\n" + "SELECT\n" + "[*BASE_MEMBERS__Measures_] ON COLUMNS\n" + ", NON EMPTY\n" + "[*SORTED_ROW_AXIS] ON ROWS\n" + "FROM [Warehouse and Sales]";
    // first MDX with a fresh query should result in product_family,
    // product_department and the_year query.
    assertQuerySqlOrNot(tc, mdx, new SqlPattern[] { mysqlPatternMembers }, false, false, false);
    // rerun the MDX, since the previous assert aborts when it hits the SQL.
    tc.executeQuery(mdx);
    // Subsequent query should pull from cache, not rerun product_family,
    // product_department and the_year query.
    assertQuerySqlOrNot(tc, mdx, new SqlPattern[] { mysqlPatternMembers }, true, false, false);
    // The MDX with added Warehouse Sales measure
    // that belongs to the regulr [Warehouse].
    String mdx1 = "WITH\n" + "SET [*NATIVE_CJ_SET] AS 'NONEMPTYCROSSJOIN([*BASE_MEMBERS__Product_],[*BASE_MEMBERS__Time_])'\n" + "SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],ANCESTOR([Product].CURRENTMEMBER, [Product].[Product Family]).ORDERKEY,BASC,[Product].CURRENTMEMBER.ORDERKEY,BASC,[Time].CURRENTMEMBER.ORDERKEY,BASC)'\n" + "SET [*BASE_MEMBERS__Measures_] AS '{[Measures].[Warehouse Sales]}'\n" + "SET [*BASE_MEMBERS__Product_] AS '[Product].[Product Department].MEMBERS'\n" + "SET [*BASE_MEMBERS__Time_] AS '[Time].[Year].MEMBERS'\n" + "SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET], {([Product].CURRENTMEMBER,[Time].CURRENTMEMBER)})'\n" + "SELECT\n" + "[*BASE_MEMBERS__Measures_] ON COLUMNS\n" + ", NON EMPTY\n" + "[*SORTED_ROW_AXIS] ON ROWS\n" + "FROM [Warehouse and Sales]";
    // Subsequent query should pull from cache, not rerun product_family,
    // product_department and the_year query.
    assertQuerySqlOrNot(tc, mdx1, new SqlPattern[] { mysqlPatternMembers }, true, false, false);
}

Example 37 with TestContext

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

the class NonEmptyTest method checkIndependentSlicerMemberNative.

private void checkIndependentSlicerMemberNative(boolean useNative) {
    propSaver.set(MondrianProperties.instance().EnableNativeCrossJoin, useNative);
    // Get a fresh connection; Otherwise the mondrian property setting
    // is not refreshed for this parameter.
    final TestContext context = getTestContext().withFreshConnection();
    try {
        context.assertQueryReturns("with set [p] as '[Product].[Product Family].members' " + "set [s] as '[Store].[Store Country].members' " + "set [ne] as 'nonemptycrossjoin([p],[s])' " + "set [nep] as 'Generate([ne],{[Product].CurrentMember})' " + "select [nep] on columns from sales " + "where ([Store].[Store Country].[Mexico])", "Axis #0:\n" + "{[Store].[Mexico]}\n" + "Axis #1:\n" + "{[Product].[Drink]}\n" + "{[Product].[Food]}\n" + "{[Product].[Non-Consumable]}\n" + "Row #0: \n" + "Row #0: \n" + "Row #0: \n");
    } finally {
        context.close();
    }
}

Example 38 with TestContext

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

the class NonEmptyTest method testMultiLevelMemberConstraintMixedNullNonNullParent.

/**
 * Check that multi-level member list generates compact form of SQL where
 * clause:
 * (1) Use IN list if possible(not possible if there are null values because
 *     NULLs in IN lists do not match)
 * (2) Group members sharing the same parent, including parents with NULLs.
 * (3) If parent levels include NULLs, comparision includes any unique
 *     level.
 * (4) Can handle predicates correctly if the member list contains both NULL
 * and non NULL parent levels.
 */
public void testMultiLevelMemberConstraintMixedNullNonNullParent() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    if (!isDefaultNullMemberRepresentation()) {
        return;
    }
    if (!MondrianProperties.instance().FilterChildlessSnowflakeMembers.get()) {
        return;
    }
    String dimension = "<Dimension name=\"Warehouse2\">\n" + "  <Hierarchy hasAll=\"true\" primaryKey=\"warehouse_id\">\n" + "    <Table name=\"warehouse\"/>\n" + "    <Level name=\"fax\" column=\"warehouse_fax\" uniqueMembers=\"true\"/>\n" + "    <Level name=\"address1\" column=\"wa_address1\" uniqueMembers=\"false\"/>\n" + "    <Level name=\"name\" column=\"warehouse_name\" uniqueMembers=\"false\"/>\n" + "  </Hierarchy>\n" + "</Dimension>\n";
    String cube = "<Cube name=\"Warehouse2\">\n" + "  <Table name=\"inventory_fact_1997\"/>\n" + "  <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + "  <DimensionUsage name=\"Warehouse2\" source=\"Warehouse2\" foreignKey=\"warehouse_id\"/>\n" + "  <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" aggregator=\"sum\"/>\n" + "  <Measure name=\"Warehouse Sales\" column=\"warehouse_sales\" aggregator=\"sum\"/>\n" + "</Cube>";
    String query = "with\n" + "set [Filtered Warehouse Set] as " + "{[Warehouse2].[#null].[234 West Covina Pkwy].[Freeman And Co]," + " [Warehouse2].[971-555-6213].[3377 Coachman Place].[Jones International]} " + "set [NECJ] as NonEmptyCrossJoin([Filtered Warehouse Set], {[Product].[Product Family].Food}) " + "select [NECJ] on columns from [Warehouse2]";
    String necjSqlMySql = "select\n" + "    `warehouse`.`warehouse_fax` as `c0`,\n" + "    `warehouse`.`wa_address1` as `c1`,\n" + "    `warehouse`.`warehouse_name` as `c2`,\n" + "    `product_class`.`product_family` as `c3`\n" + "from\n" + "    `warehouse` as `warehouse`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`\n" + "where\n" + "    `inventory_fact_1997`.`warehouse_id` = `warehouse`.`warehouse_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    ((`warehouse`.`warehouse_name`, `warehouse`.`wa_address1`, `warehouse`.`warehouse_fax`) in (('Jones International', '3377 Coachman Place', '971-555-6213')) or (( `warehouse`.`warehouse_fax` IS NULL ) and (`warehouse`.`warehouse_name`, `warehouse`.`wa_address1`) in (('Freeman And Co', '234 West Covina Pkwy'))))\n" + "and\n" + "    (`product_class`.`product_family` = 'Food')\n" + "group by\n" + "    `warehouse`.`warehouse_fax`,\n" + "    `warehouse`.`wa_address1`,\n" + "    `warehouse`.`warehouse_name`,\n" + "    `product_class`.`product_family`\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(`warehouse`.`warehouse_fax`) ASC, `warehouse`.`warehouse_fax` ASC,\n" + "    ISNULL(`warehouse`.`wa_address1`) ASC, `warehouse`.`wa_address1` ASC,\n" + "    ISNULL(`warehouse`.`warehouse_name`) ASC, `warehouse`.`warehouse_name` ASC,\n" + "    ISNULL(`product_class`.`product_family`) ASC, `product_class`.`product_family` ASC");
    TestContext testContext = TestContext.instance().create(dimension, cube, null, null, null, null);
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(testContext, query, patterns);
}

Example 39 with TestContext

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

the class NonEmptyTest method testCalculatedDefaultMeasureOnVirtualCubeNoThrowException.

/**
 * Before the fix this test would throw an IndexOutOfBounds exception
 * in SqlConstraintUtils.removeDefaultMembers.  The method assumed that the
 * first member in the list would exist and be a measure.  But, when the
 * default measure is calculated, it would have already been removed from
 * the list by removeCalculatedMembers, and thus the assumption was wrong.
 */
public void testCalculatedDefaultMeasureOnVirtualCubeNoThrowException() {
    propSaver.set(MondrianProperties.instance().EnableNativeNonEmpty, true);
    final TestContext context = TestContext.instance().withSchema("<Schema name=\"FoodMart\">" + "  <Dimension name=\"Store\">" + "    <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">" + "      <Table name=\"store\" />" + "      <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\" />" + "      <Level name=\"Store State\" column=\"store_state\" uniqueMembers=\"true\" />" + "      <Level name=\"Store City\" column=\"store_city\" uniqueMembers=\"false\" />" + "      <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\">" + "        <Property name=\"Store Type\" column=\"store_type\" />" + "        <Property name=\"Store Manager\" column=\"store_manager\" />" + "        <Property name=\"Store Sqft\" column=\"store_sqft\" type=\"Numeric\" />" + "        <Property name=\"Grocery Sqft\" column=\"grocery_sqft\" type=\"Numeric\" />" + "        <Property name=\"Frozen Sqft\" column=\"frozen_sqft\" type=\"Numeric\" />" + "        <Property name=\"Meat Sqft\" column=\"meat_sqft\" type=\"Numeric\" />" + "        <Property name=\"Has coffee bar\" column=\"coffee_bar\" type=\"Boolean\" />" + "        <Property name=\"Street address\" column=\"store_street_address\" type=\"String\" />" + "      </Level>" + "    </Hierarchy>" + "  </Dimension>" + "  <Cube name=\"Sales\" defaultMeasure=\"Unit Sales\">" + "    <Table name=\"sales_fact_1997\" />" + "    <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\" />" + "    <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\" formatString=\"Standard\" />" + "    <CalculatedMember name=\"dummyMeasure\" dimension=\"Measures\">" + "      <Formula>1</Formula>" + "    </CalculatedMember>" + "  </Cube>" + "  <VirtualCube defaultMeasure=\"dummyMeasure\" name=\"virtual\">" + "    <VirtualCubeDimension name=\"Store\" />" + "    <VirtualCubeMeasure cubeName=\"Sales\" name=\"[Measures].[Unit Sales]\" />" + "    <VirtualCubeMeasure name=\"[Measures].[dummyMeasure]\" cubeName=\"Sales\" />" + "  </VirtualCube>" + "</Schema>");
    context.assertQueryReturns("select " + " [Measures].[Unit Sales] on COLUMNS, " + " NON EMPTY {[Store].[Store State].Members} ON ROWS " + " from [virtual] ", "Axis #0:\n" + "{}\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: 74,748\n" + "Row #1: 67,659\n" + "Row #2: 124,366\n");
}

Example 40 with TestContext

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

the class NonEmptyTest method testBugCantRestrictSlicerToCalcMember.

public void testBugCantRestrictSlicerToCalcMember() throws Exception {
    TestContext ctx = getTestContext();
    ctx.assertQueryReturns("WITH Member [Time].[Time].[Aggr] AS 'Aggregate({[Time].[1998].[Q1], [Time].[1998].[Q2]})' " + "SELECT {[Measures].[Store Sales]} ON COLUMNS, " + "NON EMPTY Order(TopCount([Customers].[Name].Members,3,[Measures].[Store Sales]),[Measures].[Store Sales],BASC) ON ROWS " + "FROM [Sales] " + "WHERE ([Time].[Aggr])", "Axis #0:\n" + "{[Time].[Aggr]}\n" + "Axis #1:\n" + "{[Measures].[Store Sales]}\n" + "Axis #2:\n");
}