Chapter 8. Optimization and performance tuning 231
Keeping the aggregate cache trace-level logging on for longer than what is required can
negatively affect performance. Because the trace log level will generate a lot of data to the
DQM server log, it should be enabled only for the problem query, and troubleshooting be done
when there is little to no other user activity.
The trace log level for the aggregate cache will dump the intermediate calculations from each
of the subtask threads to the log. This information can be used to determine the source of the
incorrect values.
8.5 Database aggregates
As described earlier in this chapter, database aggregates can either be built-in relational
database system constructs, or be regular tables that hold aggregated data. Both types can
be modeled into the cube, and Cognos Dynamic Cubes can guarantee routing to them.
For warehouses that do not yet have aggregates, or want to supplement existing database
aggregates with in-memory and other in-database aggregates, run the Aggregate Advisor as
described earlier in this chapter to get aggregate recommendations.
This section describes taking the in-database recommendations from the Aggregate Advisor,
giving them to the DBA for creation, and other considerations.
8.5.1 In-database aggregate recommendations
In 8.3, “Overview of the Aggregate Advisor” on page 203, the Aggregate Advisor
recommended three in-database aggregates for the gosldw_sales cube. After opening and
reviewing the advisor result, the in-database output text file was saved.
The in-database recommendations output file describes each aggregate in terms of what it
logically contains, and other relevant information in a header section. This file can be given to
the DBA and modeler as the basis for creating the database aggregates and modeling their
support in the cube. The remainder of this section describes and has an example of the
header section that is available for each aggregate.
Aggregate name
Each aggregate is given a name based on the levels included.:
* Aggregate: Branch - Order method - Promotion
List of dimensions, hierarchies, and level of aggregation
Example 8-6 shows the dimension hierarchies with the level at which they are aggregated.
Example 8-6 Dimensions, hierarchies, and level of aggregation
* Dimension Hierarchy Level
* --------------- --------------- ---------------
* Branch Branch Branch
* Employee by region Employee by region [All]
* Order method Order method Order method
Note: Do not keep aggregate cache trace-level logging enabled longer than what is
required for troubleshooting. The trace-level log will generate a lot of log information and
may negatively affect performance.
232 IBM Cognos Dynamic Cubes
* Product brand Product brand [All]
* Products Products [All]
* Promotions Promotions Promotion
* Retailers Retailers [All]
* Time Time [All]
* Time (close date) Time (close date) [All]
* Time (ship date) Time (ship date) [All]
List of measures
Example 8-7 shows the list of measures.
Example 8-7 Measures
* Measures:
* ---------------
* Quantity
* Revenue
* Gross profit
Description of the columns that must be created for the aggregate table
Example 8-8 shows the columns and data types for the aggregate table.
Example 8-8 Description of columns
* Column Data Type
* --------------- ---------------
* 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)
If the data type of the column is unknown, refer to the definition of the column in the example
of the SQL (in “Example of the SQL that illustrates how to aggregate the data” on page 233)
and the documentation for the database to determine the precise data type.
List of other database aggregates from which the aggregate can be
derived
This information can be used by the DBA to create stacked aggregates, or aggregates that
can be derived from another recommended database aggregate. Although the example SQL
given is still based from the underlying warehouse tables, if the DBA chooses to derive its
values from another aggregate table, the DBA has the flexibility to modify the SQL, as follows:
* This aggregate can be derived from any of the following other aggregates for the
database:
* Branch - Order method - Product name - Product - Promotion
* Branch - Order method - Product name - Product - Promotion - Retailer site
Chapter 8. Optimization and performance tuning 233
Number of recommended in-memory aggregates from the same,
corresponding advisor result that the aggregate can cover
This information can be used to identify database aggregates that can accelerate the loading
of in-memory aggregates, because they can satisfy this number of in-memory aggregates, if
the in-memory aggregates from the same advisor result are used.
Database aggregates that cover zero in-memory aggregates can still provide performance
benefits because queries against aggregate tables are faster than those against the
underlying warehouse tables:
* Number of recommended in-memory aggregates that this aggregate can cover: 0
Example of the SQL that illustrates how to aggregate the data
Example 8-9 is not an executable SQL script to create database aggregate tables. It is an
example of SQL that can aggregate the data and be used as guidance. The DBA can take
this information and choose which aggregates to actually create. The DBA might even decide
to modify the SQL to create a subset of the aggregate to contain only a certain subset of
members instead of including the entire level, such as specific years from the Time
dimension.
Example 8-9 SQL that illustrates how to aggregate the data
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"
INNER JOIN "GOSALESDW"."GO_REGION_DIM" "GO_REGION_DIM"
ON "GO_REGION_DIM"."COUNTRY_CODE" = "GO_BRANCH_DIM"."COUNTRY_CODE"
INNER JOIN "GOSALESDW"."MRK_PROMOTION_DIM" "MRK_PROMOTION_DIM"
ON "MRK_PROMOTION_DIM"."PROMOTION_KEY" =
"SLS_SALES_FACT"."PROMOTION_KEY"
INNER JOIN "GOSALESDW"."SLS_ORDER_METHOD_DIM"
"SLS_ORDER_METHOD_DIM"
ON "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" =
"SLS_SALES_FACT"."ORDER_METHOD_KEY"
INNER JOIN "GOSALESDW"."MRK_CAMPAIGN_LOOKUP"
"MRK_CAMPAIGN_LOOKUP"
ON "MRK_PROMOTION_DIM"."CAMPAIGN_CODE" =
"MRK_CAMPAIGN_LOOKUP"."CAMPAIGN_CODE"
GROUP BY
"GO_REGION_DIM"."REGION_CODE",
"GO_BRANCH_DIM"."COUNTRY_CODE",
"GO_BRANCH_DIM"."BRANCH_KEY",
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.