Exemplo n.º 1
0
 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();
 }
Exemplo n.º 2
0
 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);
 }
Exemplo n.º 3
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'"));
  }