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'"));
}