예제 #1
0
  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'"));
  }