234 IBM Cognos Dynamic Cubes
"SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY",
"MRK_CAMPAIGN_LOOKUP"."CAMPAIGN_CODE",
"MRK_PROMOTION_DIM"."PROMOTION_KEY"
8.5.2 Creating recommended database aggregates
The DBA has the flexibility to choose which in-database aggregate recommendations to use
and how to implement them. The recommendations are intended to be easy to use and
relevant guidance based on the Aggregate Advisor workload and cube model analysis. Any
changes that the DBA makes to the actual database aggregates that are created should be
communicated to the modeler so that the database aggregates are properly modeled into the
cube. See 4.9.1, “Modeling aggregate cubes” on page 85. That section describes how to use
Cognos Cube Designer to include database aggregates in the model, to enable the cube so it
is aware of the database aggregate.
Many approaches to create aggregate tables exist. Each differs across various relational
database vendors. Example 8-10 shows how, in a simple manner, the information and
example SQL from the recommendation file can be used to create an aggregate table and
initially populate a database aggregate table. This example does not describe any of the
relational database considerations that are involved in creating and inserting into aggregate
tables, which a DBA will likely do, such as tablespaces, database transaction log
management, isolation levels, indexes, use of built-in database optimizer, or aggregate
support.
Example 8-10 Aggregate table
CREATE TABLE "GOSALESDW"."AGGR_BRANCH_ORDERMETHOD_PROMO" (
Region_code INTEGER,
Country_code INTEGER,
Branch_key INTEGER,
Order_method_key INTEGER,
Campaign_code INTEGER,
Promotion_key INTEGER,
Quantity BIGINT,
Revenue DECIMAL(38,2),
Gross_profit DECIMAL(38,2)
);
INSERT INTO "GOSALESDW"."AGGR_BRANCH_ORDERMETHOD_PROMO"
SELECT
"GO_REGION_DIM"."REGION_CODE" AS "Region_code",
"GO_BRANCH_DIM"."COUNTRY_CODE" AS "Country_code",
"GO_BRANCH_DIM"."BRANCH_KEY" AS "Branch_key",
"SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" AS
"Order_method_key",
"MRK_CAMPAIGN_LOOKUP"."CAMPAIGN_CODE" AS "Campaign_code",
"MRK_PROMOTION_DIM"."PROMOTION_KEY" AS "Promotion_key",
SUM("SLS_SALES_FACT"."QUANTITY") AS "Quantity",
SUM("SLS_SALES_FACT"."SALE_TOTAL") AS "Revenue",
SUM("SLS_SALES_FACT"."GROSS_PROFIT") AS "Gross_profit"
FROM
"GOSALESDW"."EMP_EMPLOYEE_DIM" "EMP_EMPLOYEE_DIM"
INNER JOIN "GOSALESDW"."GO_BRANCH_DIM" "GO_BRANCH_DIM"
ON "EMP_EMPLOYEE_DIM"."BRANCH_CODE" = "GO_BRANCH_DIM"."BRANCH_CODE"
INNER JOIN "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT"
ON "EMP_EMPLOYEE_DIM"."EMPLOYEE_KEY" = "SLS_SALES_FACT"."EMPLOYEE_KEY"
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset