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

Example 11 with SqlPattern

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

the class TestAggregationManager method testCountDistinctRollup2.

/**
 * As above, but we rollup [Marital Status] but not [Gender].
 */
public void testCountDistinctRollup2() {
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    CellRequest request = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "time_by_day", "time_by_day", "time_by_day", "product_class", "product_class", "product_class", "customer" }, new String[] { "the_year", "quarter", "month_of_year", "product_family", "product_department", "product_category", "gender" }, new String[] { "1997", "Q1", "1", "Food", "Deli", "Meat", "F" });
    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`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` as `c6`," + " 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'" + " and `agg_g_ms_pcat_sales_fact_1997`.`gender` = 'F' " + "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`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender`", 58) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 12 with SqlPattern

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

the class VirtualCubeTest method testNonEmptyCJConstraintOnVirtualCube.

/**
 * Tests that the logic to apply non empty context constraint in virtual
 * cube is correct.  The joins shouldn't be cartesian product.
 */
public void testNonEmptyCJConstraintOnVirtualCube() {
    if (!MondrianProperties.instance().EnableNativeCrossJoin.get()) {
        // memory.
        return;
    }
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String query = "with " + "set [foo] as [Time].[Month].members " + "set [bar] as {[Store].[USA]} " + "Select {[Measures].[Warehouse Sales],[Measures].[Store Sales]} on columns, " + "nonemptycrossjoin([foo],[bar]) on rows " + "From [Warehouse and Sales] " + "Where ([Product].[All Products].[Food])";
    // Note that for MySQL (because MySQL sorts NULLs first), because there
    // is a UNION (which prevents us from sorting on column names or
    // expressions) the ORDER BY clause should be something like
    // ORDER BY ISNULL(1), 1 ASC, ISNULL(2), 2 ASC, ISNULL(3), 3 ASC,
    // ISNULL(4), 4 ASC
    // but ISNULL(1) isn't valid SQL, so we forego correct ordering of NULL
    // values.
    String mysqlSQL = MondrianProperties.instance().UseAggregates.get() ? "select\n" + "    *\n" + "from\n" + "    (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" + "    `product_class` as `product_class`,\n" + "    `product` as `product`\n" + "where\n" + "    `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `product_class`.`product_family` = 'Food'\n" + "and\n" + "    (`store`.`store_country` = 'USA')\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" + "union\n" + "select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `time_by_day`.`quarter` as `c1`,\n" + "    `time_by_day`.`month_of_year` as `c2`,\n" + "    `store`.`store_country` as `c3`\n" + "from\n" + "    `time_by_day` as `time_by_day`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `store` as `store`,\n" + "    `product_class` as `product_class`,\n" + "    `product` as `product`\n" + "where\n" + "    `inventory_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + "    `inventory_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `product_class`.`product_family` = 'Food'\n" + "and\n" + "    (`store`.`store_country` = 'USA')\n" + "group by\n" + "    `time_by_day`.`the_year`,\n" + "    `time_by_day`.`quarter`,\n" + "    `time_by_day`.`month_of_year`,\n" + "    `store`.`store_country`) as `unionQuery`\n" + "order by\n" + "    ISNULL(1) ASC, 1 ASC,\n" + "    ISNULL(2) ASC, 2 ASC,\n" + "    ISNULL(3) ASC, 3 ASC,\n" + "    ISNULL(4) ASC, 4 ASC" : "select\n" + "    *\n" + "from\n" + "    (select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `time_by_day`.`quarter` as `c1`,\n" + "    `time_by_day`.`month_of_year` as `c2`,\n" + "    `store`.`store_country` as `c3`\n" + "from\n" + "    `time_by_day` as `time_by_day`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `store` as `store`,\n" + "    `product_class` as `product_class`,\n" + "    `product` as `product`\n" + "where\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + "    `sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `product_class`.`product_family` = 'Food'\n" + "and\n" + "    (`store`.`store_country` = 'USA')\n" + "group by\n" + "    `time_by_day`.`the_year`,\n" + "    `time_by_day`.`quarter`,\n" + "    `time_by_day`.`month_of_year`,\n" + "    `store`.`store_country`\n" + "union\n" + "select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `time_by_day`.`quarter` as `c1`,\n" + "    `time_by_day`.`month_of_year` as `c2`,\n" + "    `store`.`store_country` as `c3`\n" + "from\n" + "    `time_by_day` as `time_by_day`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `store` as `store`,\n" + "    `product_class` as `product_class`,\n" + "    `product` as `product`\n" + "where\n" + "    `inventory_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + "    `inventory_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `product_class`.`product_family` = 'Food'\n" + "and\n" + "    (`store`.`store_country` = 'USA')\n" + "group by\n" + "    `time_by_day`.`the_year`,\n" + "    `time_by_day`.`quarter`,\n" + "    `time_by_day`.`month_of_year`,\n" + "    `store`.`store_country`) as `unionQuery`\n" + "order by\n" + "    ISNULL(1) ASC, 1 ASC,\n" + "    ISNULL(2) ASC, 2 ASC,\n" + "    ISNULL(3) ASC, 3 ASC,\n" + "    ISNULL(4) ASC, 4 ASC";
    SqlPattern[] mysqlPattern = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSQL, mysqlSQL) };
    String result = "Axis #0:\n" + "{[Product].[Food]}\n" + "Axis #1:\n" + "{[Measures].[Warehouse Sales]}\n" + "{[Measures].[Store 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: 16,083.015\n" + "Row #0: 32,993.12\n" + "Row #1: 9,298.379\n" + "Row #1: 32,139.91\n" + "Row #2: 10,129.659\n" + "Row #2: 36,128.29\n" + "Row #3: 11,415.462\n" + "Row #3: 30,747.21\n" + "Row #4: 11,358.086\n" + "Row #4: 31,896.24\n" + "Row #5: 10,425.768\n" + "Row #5: 32,792.55\n" + "Row #6: 13,684.193\n" + "Row #6: 36,324.76\n" + "Row #7: 11,332.797\n" + "Row #7: 33,842.75\n" + "Row #8: 15,667.978\n" + "Row #8: 31,640.09\n" + "Row #9: 11,902.18\n" + "Row #9: 30,337.12\n" + "Row #10: 10,144.841\n" + "Row #10: 38,709.15\n" + "Row #11: 9,705.561\n" + "Row #11: 41,484.40\n";
    assertQuerySql(query, mysqlPattern, true);
    assertQueryReturns(query, result);
}

Example 13 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testOptimizeChildren.

public void testOptimizeChildren() {
    String query = "with member gender.x as " + "'aggregate(" + "{gender.gender.members * Store.[all stores].[usa].children})' " + "select {gender.x} on 0, measures.[customer count] on 1 from sales";
    String expected = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Gender].[x]}\n" + "Axis #2:\n" + "{[Measures].[Customer Count]}\n" + "Row #0: 5,581\n";
    assertQueryReturns(query, expected);
    String derbySql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" as \"time_by_day\", \"sales_fact_1997\" as \"sales_fact_1997\", \"store\" as \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and \"store\".\"store_country\" = 'USA' group by \"time_by_day\".\"the_year\"";
    String accessSql = "select `d0` as `c0`, count(`m0`) as `c1` " + "from (select distinct `time_by_day`.`the_year` as `d0`, `sales_fact_1997`.`customer_id` as `m0` " + "from `time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and `time_by_day`.`the_year` = 1997 " + "and `sales_fact_1997`.`store_id` = `store`.`store_id` " + "and `store`.`store_country` = 'USA') as `dummyname` group by `d0`";
    // For LucidDB, we don't optimize since it supports
    // unlimited IN list.
    String luciddbSql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" as \"time_by_day\", \"sales_fact_1997\" as \"sales_fact_1997\", \"customer\" as \"customer\", \"store\" as \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and \"time_by_day\".\"the_year\" = 1997 and \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and (((\"store\".\"store_state\", \"customer\".\"gender\") in (('CA', 'F'), ('OR', 'F'), ('WA', 'F'), ('CA', 'M'), ('OR', 'M'), ('WA', 'M')))) group by \"time_by_day\".\"the_year\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql), new SqlPattern(Dialect.DatabaseProduct.ACCESS, accessSql, accessSql), new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, luciddbSql, luciddbSql) };
    assertQuerySql(query, patterns);
}

Example 14 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testOptimizeListWhenTuplesAreFormedWithDifferentLevels.

public void testOptimizeListWhenTuplesAreFormedWithDifferentLevels() {
    String query = "WITH\n" + "MEMBER Product.Agg AS \n" + "'Aggregate({[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pot Scrubbers].[Cormorant],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pot Scrubbers].[Denny],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pot Scrubbers].[High Quality],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pot Scrubbers].[Red Wing],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pots and Pans].[Cormorant],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pots and Pans].[Denny],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pots and Pans].[High Quality],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pots and Pans].[Red Wing],\n" + "[Product].[All Products].[Non-Consumable].[Household].[Kitchen Products].[Pots and Pans].[Sunset]} *\n" + "{[Gender].[Gender].Members})'\n" + "SELECT {Product.Agg} on 0, {[Measures].[Customer Count]} on 1\n" + "from Sales\n" + "where [Time.Weekly].[1997]";
    String expected = "Axis #0:\n" + "{[Time].[Weekly].[1997]}\n" + "Axis #1:\n" + "{[Product].[Agg]}\n" + "Axis #2:\n" + "{[Measures].[Customer Count]}\n" + "Row #0: 421\n";
    assertQueryReturns(query, expected);
    String derbySql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "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\" as \"product\", \"product_class\" as \"product_class\" " + "where \"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\" " + "and (((\"product\".\"brand_name\" = 'Red Wing' and \"product_class\".\"product_subcategory\" = 'Pot Scrubbers' " + "and \"product_class\".\"product_category\" = 'Kitchen Products' " + "and \"product_class\".\"product_department\" = 'Household' " + "and \"product_class\".\"product_family\" = 'Non-Consumable') " + "or (\"product\".\"brand_name\" = 'Cormorant' and \"product_class\".\"product_subcategory\" = 'Pot Scrubbers' " + "and \"product_class\".\"product_category\" = 'Kitchen Products' " + "and \"product_class\".\"product_department\" = 'Household' " + "and \"product_class\".\"product_family\" = 'Non-Consumable') " + "or (\"product\".\"brand_name\" = 'Denny' and \"product_class\".\"product_subcategory\" = 'Pot Scrubbers' " + "and \"product_class\".\"product_category\" = 'Kitchen Products' " + "and \"product_class\".\"product_department\" = 'Household' " + "and \"product_class\".\"product_family\" = 'Non-Consumable') or (\"product\".\"brand_name\" = 'High Quality' " + "and \"product_class\".\"product_subcategory\" = 'Pot Scrubbers' " + "and \"product_class\".\"product_category\" = 'Kitchen Products' " + "and \"product_class\".\"product_department\" = 'Household' and \"product_class\".\"product_family\" = 'Non-Consumable')) " + "or (\"product_class\".\"product_subcategory\" = 'Pots and Pans' " + "and \"product_class\".\"product_category\" = 'Kitchen Products' and \"product_class\".\"product_department\" = 'Household' " + "and \"product_class\".\"product_family\" = 'Non-Consumable')) " + "group by \"time_by_day\".\"the_year\"";
    String accessSql = "select `d0` as `c0`, count(`m0`) as `c1` from (select distinct `time_by_day`.`the_year` as `d0`, `sales_fact_1997`.`customer_id` as `m0` " + "from `time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997`, `product` as `product`, `product_class` as `product_class` " + "where `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` " + "and (((`product`.`brand_name` = 'High Quality' and `product_class`.`product_subcategory` = 'Pot Scrubbers' " + "and `product_class`.`product_category` = 'Kitchen Products' and `product_class`.`product_department` = 'Household' " + "and `product_class`.`product_family` = 'Non-Consumable') or (`product`.`brand_name` = 'Denny' " + "and `product_class`.`product_subcategory` = 'Pot Scrubbers' and `product_class`.`product_category` = 'Kitchen Products' " + "and `product_class`.`product_department` = 'Household' " + "and `product_class`.`product_family` = 'Non-Consumable') or (`product`.`brand_name` = 'Red Wing' " + "and `product_class`.`product_subcategory` = 'Pot Scrubbers' and `product_class`.`product_category` = 'Kitchen Products' " + "and `product_class`.`product_department` = 'Household' " + "and `product_class`.`product_family` = 'Non-Consumable') or (`product`.`brand_name` = 'Cormorant' " + "and `product_class`.`product_subcategory` = 'Pot Scrubbers' and `product_class`.`product_category` = 'Kitchen Products' " + "and `product_class`.`product_department` = 'Household' " + "and `product_class`.`product_family` = 'Non-Consumable')) or (`product_class`.`product_subcategory` = 'Pots and Pans' " + "and `product_class`.`product_category` = 'Kitchen Products' and `product_class`.`product_department` = 'Household' " + "and `product_class`.`product_family` = 'Non-Consumable'))) as `dummyname` group by `d0`";
    // FIXME jvs 20-Sept-2008: The Derby pattern fails, probably due to
    // usage of non-order-deterministic Hash data structures in
    // AggregateFunDef.  (Access may be failing too; I haven't tried it.)
    // So it is disabled for now.  Perhaps this test should be calling
    // directly into optimizeChildren like some of the tests below rather
    // than using SQL pattern verification.
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, accessSql, accessSql) };
    assertQuerySql(query, patterns);
}

Example 15 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testAggregationOnCJofMembersGeneratesOptimalQuery.

public void testAggregationOnCJofMembersGeneratesOptimalQuery() {
    // Mondrian does not use GROUPING SETS for distinct-count measures.
    // So, this test should not use GROUPING SETS, even if they are enabled.
    // See change 12310, bug MONDRIAN-470 (aka SF.net 2207515).
    Util.discard(props.EnableGroupingSets);
    String oraTeraSql = "select \"store\".\"store_state\" as \"c0\"," + " \"time_by_day\".\"the_year\" as \"c1\"," + " count(distinct \"sales_fact_1997\".\"customer_id\") 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 \"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\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSql, oraTeraSql), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSql, oraTeraSql) };
    assertQuerySql("WITH \n" + "SET [COG_OQP_INT_s2] AS 'CROSSJOIN({[Store].[Store].MEMBERS}, " + "{{[Gender].[Gender].MEMBERS}, " + "{([Gender].[COG_OQP_USR_Aggregate(Gender)])}})' \n" + "SET [COG_OQP_INT_s1] AS 'CROSSJOIN({[Store].[Store].MEMBERS}, " + "{[Gender].[Gender].MEMBERS})' \n" + "\n" + "MEMBER [Store].[COG_OQP_USR_Aggregate(Store)] AS '\n" + "AGGREGATE({COG_OQP_INT_s1})', SOLVE_ORDER = 4 \n" + "\n" + "MEMBER [Gender].[COG_OQP_USR_Aggregate(Gender)] AS '\n" + "AGGREGATE({[Gender].DEFAULTMEMBER})', SOLVE_ORDER = 8 \n" + "\n" + "\n" + "SELECT {[Measures].[Customer Count]} ON AXIS(0), \n" + "{[COG_OQP_INT_s2], HEAD({([Store].[COG_OQP_USR_Aggregate(Store)], " + "[Gender].DEFAULTMEMBER)}, " + "IIF(COUNT([COG_OQP_INT_s1], INCLUDEEMPTY) > 0, 1, 0))} ON AXIS(1) \n" + "FROM [sales]", patterns);
}