protected void setUp() throws Exception {
super.setUp(); // parent setUp enabled agg
propSaver.set(propSaver.properties.EnableNativeCrossJoin, true);
propSaver.set(propSaver.properties.EnableNativeNonEmpty, true);
propSaver.set(propSaver.properties.GenerateFormattedSql, true);
TestContext.instance().flushSchemaCache();
}
static TestContext setupMultiColDimCube(
String aggName,
String yearCols,
String qtrCols,
String monthCols,
String monthProp,
String defaultMeasure) {
String cube =
"<?xml version=\"1.0\"?>\n"
+ "<Schema name=\"FoodMart\">\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"
+ " <Property name=\"Street address\" column=\"store_street_address\" type=\"String\"/>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ " </Dimension>\n"
+ " <Dimension 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"
+ " </Hierarchy>\n"
+ " </Dimension>\n"
+ "<Cube name=\"ExtraCol\" defaultMeasure='#DEFMEASURE#'>\n"
+ " <Table name=\"sales_fact_1997\">\n"
+ " #AGGNAME# "
+ " </Table>"
+ " <Dimension name=\"TimeExtra\" foreignKey=\"time_id\">\n"
+ " <Hierarchy hasAll=\"false\" primaryKey=\"time_id\">\n"
+ " <Table name=\"time_by_day\"/>\n"
+ " <Level name=\"Year\" #YEARCOLS# type=\"Numeric\" uniqueMembers=\"true\""
+ " levelType=\"TimeYears\">\n"
+ " </Level>\n"
+ " <Level name=\"Quarter\" #QTRCOLS# uniqueMembers=\"false\""
+ " levelType=\"TimeQuarters\">\n"
+ " </Level>\n"
+ " <Level name=\"Month\" #MONTHCOLS# uniqueMembers=\"false\" type=\"Numeric\""
+ " levelType=\"TimeMonths\">\n"
+ " #MONTHPROP# "
+ " </Level>\n"
+ " </Hierarchy>\n"
+ " </Dimension>\n"
+ " <Dimension name=\"Gender\" foreignKey=\"customer_id\">\n"
+ " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n"
+ " <Table name=\"customer\"/>\n"
+ " <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\"/>\n"
+ " </Hierarchy>\n"
+ " </Dimension> "
+ " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>"
+ " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>"
+ "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n"
+ " formatString=\"Standard\" visible=\"false\"/>\n"
+ "<Measure name=\"Avg Unit Sales\" column=\"unit_sales\" aggregator=\"avg\"\n"
+ " formatString=\"Standard\" visible=\"false\"/>\n"
+ " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n"
+ " formatString=\"#,###.00\"/>\n"
+ "<Measure name=\"Customer Count\" column=\"customer_id\" aggregator=\"distinct-count\" formatString=\"#,###\"/>"
+ "</Cube>\n"
+ "</Schema>";
cube =
cube.replace("#AGGNAME#", aggName)
.replace("#YEARCOLS#", yearCols)
.replace("#QTRCOLS#", qtrCols)
.replace("#MONTHCOLS#", monthCols)
.replace("#MONTHPROP#", monthProp)
.replace("#DEFMEASURE#", defaultMeasure);
return TestContext.instance().withSchema(cube);
}
public void testExplicitAggPropertiesOnAggTable() throws SQLException {
TestContext testContext =
setupMultiColDimCube(
" <AggName name=\"exp_agg_test_distinct_count\">\n"
+ " <AggFactCount column=\"FACT_COUNT\"/>\n"
+ " <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_s\" />\n"
+ " <AggMeasure name=\"[Measures].[Customer Count]\" column=\"cust_cnt\" />\n"
+ " <AggLevel name=\"[TimeExtra].[Year]\" column=\"testyear\" />\n"
+ " <AggLevel name=\"[Gender].[Gender]\" column=\"gender\" />\n"
+ " <AggLevel name=\"[Store].[Store Country]\" column=\"store_country\" />\n"
+ " <AggLevel name=\"[Store].[Store State]\" column=\"store_st\" />\n"
+ " <AggLevel name=\"[Store].[Store City]\" column=\"store_cty\" />\n"
+ " <AggLevel name=\"[Store].[Store Name]\" column=\"store_name\" >\n"
+ " <AggLevelProperty name='Street address' column='store_add' />"
+ " </AggLevel>\n"
+ " </AggName>\n",
"column=\"the_year\"",
"column=\"quarter\"",
"column=\"month_of_year\" captionColumn=\"the_month\" ordinalColumn=\"month_of_year\"",
"");
String query =
"with member measures.propVal as 'Store.CurrentMember.Properties(\"Street Address\")'"
+ "select { measures.[propVal], measures.[Customer Count], [Measures].[Unit Sales]} on columns, "
+ "non empty CrossJoin({[Gender].Gender.members},{[Store].[USA].[WA].[Spokane].[Store 16]}) on rows "
+ "from [ExtraCol]";
assertQuerySql(
testContext,
query,
mysqlPattern(
"select\n"
+ " `exp_agg_test_distinct_count`.`gender` as `c0`,\n"
+ " `exp_agg_test_distinct_count`.`store_country` as `c1`,\n"
+ " `exp_agg_test_distinct_count`.`store_st` as `c2`,\n"
+ " `exp_agg_test_distinct_count`.`store_cty` as `c3`,\n"
+ " `exp_agg_test_distinct_count`.`store_name` as `c4`,\n"
+ " `exp_agg_test_distinct_count`.`store_add` as `c5`\n"
+ "from\n"
+ " `exp_agg_test_distinct_count` as `exp_agg_test_distinct_count`\n"
+ "where\n"
+ " (`exp_agg_test_distinct_count`.`store_name` = 'Store 16')\n"
+ "group by\n"
+ " `exp_agg_test_distinct_count`.`gender`,\n"
+ " `exp_agg_test_distinct_count`.`store_country`,\n"
+ " `exp_agg_test_distinct_count`.`store_st`,\n"
+ " `exp_agg_test_distinct_count`.`store_cty`,\n"
+ " `exp_agg_test_distinct_count`.`store_name`,\n"
+ " `exp_agg_test_distinct_count`.`store_add`\n"
+ "order by\n"
+ " ISNULL(`exp_agg_test_distinct_count`.`gender`) ASC, `exp_agg_test_distinct_count`.`gender` ASC,\n"
+ " ISNULL(`exp_agg_test_distinct_count`.`store_country`) ASC, `exp_agg_test_distinct_count`.`store_country` ASC,\n"
+ " ISNULL(`exp_agg_test_distinct_count`.`store_st`) ASC, `exp_agg_test_distinct_count`.`store_st` ASC,\n"
+ " ISNULL(`exp_agg_test_distinct_count`.`store_cty`) ASC, `exp_agg_test_distinct_count`.`store_cty` ASC,\n"
+ " ISNULL(`exp_agg_test_distinct_count`.`store_name`) ASC, `exp_agg_test_distinct_count`.`store_name` ASC"));
testContext.assertQueryReturns(
"Store Address Property should be '5922 La Salle Ct'",
query,
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[propVal]}\n"
+ "{[Measures].[Customer Count]}\n"
+ "{[Measures].[Unit Sales]}\n"
+ "Axis #2:\n"
+ "{[Gender].[F], [Store].[USA].[WA].[Spokane].[Store 16]}\n"
+ "{[Gender].[M], [Store].[USA].[WA].[Spokane].[Store 16]}\n"
+ "Row #0: 5922 La Salle Ct\n"
+ "Row #0: 45\n"
+ "Row #0: 12,068\n"
+ "Row #1: 5922 La Salle Ct\n"
+ "Row #1: 39\n"
+ "Row #1: 11,523\n");
// Should use agg table for distinct count measure
assertQuerySql(
testContext,
query,
mysqlPattern(
"select\n"
+ " `exp_agg_test_distinct_count`.`testyear` as `c0`,\n"
+ " `exp_agg_test_distinct_count`.`gender` as `c1`,\n"
+ " `exp_agg_test_distinct_count`.`store_name` as `c2`,\n"
+ " `exp_agg_test_distinct_count`.`unit_s` as `m0`,\n"
+ " `exp_agg_test_distinct_count`.`cust_cnt` as `m1`\n"
+ "from\n"
+ " `exp_agg_test_distinct_count` as `exp_agg_test_distinct_count`\n"
+ "where\n"
+ " `exp_agg_test_distinct_count`.`testyear` = 1997\n"
+ "and\n"
+ " `exp_agg_test_distinct_count`.`store_name` = 'Store 16'"));
}