Aggregate
Advisor in-database aggregate recommendations can be derived from
the fact table or other in-database aggregates. When derived from
the latter, the sample SQL refers to columns from the sample SQL of
the second in-database aggregate. If a derived in-database aggregate
can be derived from multiple in-database aggregates, the sample SQL
is generated from the in-database aggregate that provides the optimal
solution.
In the following illustration, Diagram A shows in-memory and in-database aggregate
recommendations that are based on in-database aggregate recommendations (A1 and A2) that are built
from the fact table. Diagram B shows aggregate recommendations (B1 and B2) derived from the fact
table but there are also in-database aggregate recommendations (B3 and B4) built on other
in-database aggregates (B1 and B2). Figure 1. Aggregate recommendations derived from a fact table and from other in-database
aggregates
Deriving in-database aggregates from other in-database aggregates
provides these benefits:
Improved loading of in-memory aggregates by populating them from
relatively small in-database aggregates.
Improved refresh of in-database aggregates by populating them
from other in-database aggregates instead of the fact table.
The database administrator creates derived in-database aggregates
and applies them to the database after creating and applying the base
in-database aggregates.
The following extract of an Aggregate Advisor in-database recommendation
has two in-database aggregates: one is derived from the fact table,
the other derived from the first in-database aggregate. Note the following
points about the extract:
The prolog indicates how many in-database aggregates are recommended
including the number that are derived from the fact table and other
in-database aggregates.
The prolog has instructions about creating in-database aggregates
that are derivable from other in-database aggregates.
The first recommended in-database aggregate is derived from the
fact table. The SQL selects from the fact and dimension tables.
The second recommended in-database aggregate is derived from the
first recommended in-database aggregate. The SQL is much simpler since
it queries only the aggregate table and does not need to join to other
tables. The name that is chosen when creating the first in-database
aggregate can be specified here.
Important: If the database administrator creates the in-database
aggregate using different column names from what is in the sample
SQL, the SQL for any derived in-database aggregates must be modified
to match the column names used.
/*******************************************************************************
* Dynamic Cubes Aggregate Advisor
* In-Database Recommendations
*
* Cube: GoSales_AllEmp_en-us
* Start time: 2013-11-19 15:12:09.761 EST
*
* General options:
* - Do not use query workload logs
* - In-memory aggregates maximum size = 1000000 (bytes)
* - In-database aggregates maximum size = 10000000 (bytes)
* - Advisor run time limit: 60 (minutes)
*
* Summary:
* - Number of new database aggregates recommended: 2. Details are in
the next section.
* Number of new database aggregates derivable from the fact table: 1.
* Number of new database aggregates derivable from recommended database
aggregates: 1.
*******************************************************************************/
/*******************************************************************************
* Advisor recommended aggregates for the database. The recommendations are
listed in the following sections.
*
* The DBA must create the appropriate aggregate tables.
* The modeler must define an aggregate cube for each created aggregate table.
*
...
*
* Following the textual description is an example of the SQL that can aggregate
the data.
* For aggregates derivable from recommended database aggregates, the example SQL
is constructed in the context of the optimal underlying aggregate table and uses
a placeholder name for the table name.
*
...
*******************************************************************************/
/*******************************************************************************
* Aggregate: Month - Product type
*
* Dimension hierarchies with level they are aggregated at:
*
* Dimension Hierarchy Level
* --------------- --------------- ---------------
* Time Time Month
* Products Product Product type
* Retailer Retailer (by Region) [All]
*
* Measures:
* ---------------
* Quantity
*
* Column Data Type Cube Model Relationship
* --------------- --------------- ------------------------------
* CURRENT_YEAR SMALLINT
* QUARTER_KEY INTEGER
* MONTH_KEY INTEGER Time -> Time -> Month
* PRODUCT_LINE_CODE INTEGER
* PRODUCT_TYPE_KEY INTEGER Products -> Product ->Product type
* Quantity BIGINT measure: Quantity
*
* This aggregate cannot be derived from any other recommended aggregates for
the database.
*
Number of recommended in-memory aggregates that this aggregate can cover: 4
*
* Estimated row count of this aggregate: 21500
*
*******************************************************************************/
SELECT
"GO_TIME_DIM2"."CURRENT_YEAR" AS "CURRENT_YEAR",
"GO_TIME_DIM2"."QUARTER_KEY" AS "QUARTER_KEY",
"GO_TIME_DIM2"."MONTH_KEY" AS "MONTH_KEY",
"SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE" AS "PRODUCT_LINE_CODE",
"SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY" AS "PRODUCT_TYPE_KEY",
SUM("SLS_SALES_CLEAN_FACT"."QUANTITY") AS "Quantity"
FROM
"GOSALESDW"."GO_TIME_DIM" "GO_TIME_DIM2"
INNER JOIN "GOSALESDW"."SLS_SALES_CLEAN_FACT" "SLS_SALES_CLEAN_FACT"
ON "GO_TIME_DIM2"."DAY_KEY" = "SLS_SALES_CLEAN_FACT"."ORDER_DAY_KEY"
INNER JOIN "GOSALESDW"."SLS_PRODUCT_DIM" "SLS_PRODUCT_DIM"
ON "SLS_PRODUCT_DIM"."PRODUCT_KEY" =
"SLS_SALES_CLEAN_FACT"."PRODUCT_KEY"
GROUP BY
"GO_TIME_DIM2"."CURRENT_YEAR",
"GO_TIME_DIM2"."QUARTER_KEY",
"GO_TIME_DIM2"."MONTH_KEY",
"SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE",
"SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY"
/*******************************************************************************
* Aggregate: Quarter - Product line
*
* Dimension hierarchies with level they are aggregated at:
*
* Dimension Hierarchy Level
* --------------- --------------- ---------------
* Time Time Quarter
* Products Product Product line
* Retailer Retailer (by Region) [All]
*
* Measures:
* ---------------
* Quantity
*
* Column Data Type Cube Model Relationship
* --------------- --------------- ------------------------------
* CURRENT_YEAR SMALLINT
* QUARTER_KEY INTEGER Time -> Time -> Quarter
* PRODUCT_LINE_CODE INTEGER Products -> Product -> Product line
* Quantity BIGINT measure: Quantity
*
* This aggregate can be derived from any of the following other aggregates for
the database:
* Month - Product type
*
* Number of recommended in-memory aggregates that this aggregate can cover: 2
*
* Estimated row count of this aggregate: 4000
*
*******************************************************************************/
SELECT
"CURRENT_YEAR",
"QUARTER_KEY",
"PRODUCT_LINE_CODE",
SUM("Quantity") AS "Quantity"
FROM
[Month - Product type]
GROUP BY
"CURRENT_YEAR",
"QUARTER_KEY",
"PRODUCT_LINE_CODE"
