202 IBM Cognos Dynamic Cubes
8.1 Performance implications of OLAP-style reports
Query performance is mostly determined by how much data is fetched from the caches and
the database, and how much information the report is trying to show the user. In general,
when querying against large warehouse data, use context filters to constrain the data that is
processed.
An expectation is that a vast majority of OLAP-style reports and analyses that run against
Cognos Dynamic Cubes show data at high levels of hierarchies. For example, the report on
the Gross Profit for Retailers Region and Products Product Line.
Any deeper analysis or drilling down will have filters to highly constrain the resulting data. For
example, showing gross profit for a particular product in a specific retailer site.
A report is unlikely to be at the deepest level of all hierarchies. If it is at the bottom of a large
dimension hierarchy, it probably is highly constrained. For example, showing data for one
employee by region, or the top 10 products.
8.2 Aggregate awareness in Cognos Dynamic Cubes
Use of aggregates can significantly improve performance of queries by providing data that is
aggregated at levels higher than the grain of the fact.
Database aggregates are tables of precomputed data that can improve query performance by
reducing the number of database rows processed for a query. Because not all relational
databases have built-in aggregate support, or if they do have built-in support, do not do it well
for all types of queries. Cognos Dynamic Cubes brings the aggregate routing logic up into its
query engine where the multi-dimensional OLAP context is preserved and can better
determine whether to route to aggregates.
Cognos Dynamic Cubes can also use aggregate tables that the database optimizer does not
know about or the database optimizer might not route to because of complex OLAP-style
SQL.
The aggregate tables can be regular tables that happen to hold aggregated data. The Great
Outdoors warehouse has an existing database aggregate table:
AGGR_TIME_PROD_OM_FACT
See 4.9.1, “Modeling aggregate cubes” on page 85 for a description of how to include
database aggregates in the model to enable the cube to be database aggregate-aware. By
enabling the cube to be aware of database aggregate tables, Cognos Dynamic Cubes
guarantees routing to them.
In addition to guaranteed routing to aggregates in the database, a major performance feature
of Cognos Dynamic Cubes is its support of aggregates that are in memory. Not only do
in-memory aggregates provide precomputed data, they can greatly improve query
Note: Reporting and analysis users need to be aware of the performance implications if
reports are not filtered or scoped appropriately. A vast majority of OLAP-style reports and
analyses are filtered and scoped to show data at high levels of aggregation. Any deeper
analysis should have filters to highly constrain the resulting data. For example, showing
data for customers within a region, as opposed to including all leaf-level customers of an
entire dimension.
..................Content has been hidden....................

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