IBM Cognos Proven Practices: Performance Tips when using Cross JoinQueries against a TM1 Datasource

Nature of Document: Tip or Technique; Product(s): IBM Cognos 8.4.1 FP2+ & TM1 9.4+; Area of Interest: Performance

The purpose of this document is to provide performance guidance when querying large datasets from a TM1 Datasource.

Introduction

Purpose

The purpose of this document is to provide performance guidance when querying large datasets from a TM1 Datasource

Applicability

This document is applicable to IBM Cognos 8.4.1 FP2 and greater using a data source of TM1 9.4 or greater.

Exclusions and Exceptions

There are no known exclusions or exceptions except those mentioned in the applicability section at this time.


The Effect of Large Cross Join Operations on your Cognos Reports

IBM Cognos8 handles the nesting of several member sets on an edge as a single cross join operation. The number of tuples in the cross joined set is the product of the set sizes. In typical cases, most tuples in a large cross join set contain nulls.

Cross joins are the Cartesian product of member sets:

CROSSJOIN({a1, a2},{b1,b2},{c}) = {(a1,b1,c) (a1,b2,c) (a2,b1,c) (a2,b2,c)}

Such cross joins can easily result in very large reports, those reports typically show symptoms such as:

Report runs a very, very long time for no apparent reason.

Report fails with an RSV-BBP-0022

The absolute affinity request 'asynchWait_Request' failed, the requested session does not exist.

Often accompanied by the BIBusTKServerMain (report server) consuming a precipitous amount of memory followed by a sudden drop in memory usage as the process fails.


New Filter Options to Improve Performance in your Cognos Reports

IBM Cognos has a implemented a filter located in the QFS_config.xml file under <install>/C8/Cognos/configuration, called “Provider Cross Join”, that can be set when using TM1 as a datasource to help mitigate this problem. The Provider Cross Join minimizes the data and metadata transferred from the TM1 server to the Cognos client in Query Studio, Analysis Studio, and Report Studio by pushing the cross join operation down to the TM1 provider and ignoring tuples with no data.

The results will vary based on a combination between the structure of the cube, the cardinality, the style of report, the size of the query and how sparse the results are. All the empty cells may disappear, 80% of them, 50% of them, or just a few rows. There is no specific algorithm that can be applied. In general, the effectiveness of this NON EMPTY clause is greatly dependent on the layout of the data within the cube. Data that is spread evenly in the cube (or in “diagonal” fashion) prevents the filtering benefit entirely. The adverse effect of having many independent axes grows as more dimensions are projected in the report. One report can contain nine dimensions with a data query that attempts to return 330 million nulls for only 8000 useful cells.

Reports that have millions or billions of potential rows will benefit most from these settings. There are some cubes that are so sparse that many members in the cube have no fact data, there are no datapoints in the cube which involve the members we are trying to retrieve. Small reports will not have the volume necessary to require the performance enhancements, unless the small report is going against huge sparse datasets.

Example: With a small list report on a large sparse dataset you could potentially be looking at a million rows but turn these filters on and you are left with 15. But if you write a report that only uses the root levels or high level consolidations you will not see an improvement, because all the work is being done by the server. If your report only returns 200 cells with empty rows, you would have better performance using local suppression from the GUI.


Setting the Provider Cross Join Parameters

To take advantage of the new TM1 optimizations in Cognos 8 you will also need to modify the configuration file <c8 installation>\configuration\qfs_config.xml under the TM1ODP provider. They affect every TM1 query against the Cognos BI server and are off by default. It’s recommended to begin with a setting of 10,000, reduce to 1,000 and then 10 to see what works best with your reports. The filters work on nulls and zeros as by default TM1 does not differentiate between nulls and zeros.

The cross join and data filtering options work best when enabled together. These are system wide settings; there currently is no way for reports to override the new behaviors once they are set.

	<parameter name="UseProviderCrossJoinThreshold" value="0"/>
	<parameter name="UseNonEmptyOnDataQueryThreshold" value="0"/>

This threshold is the number at which the adapter will ask the TM1 server to reduce the size of a large cross join set by filtering out the empty tuples according to the measures in the query.

For example, when the report is executing, the number of cells required to complete the query is determined, and Nulls are suppressed if the number of cells required to complete the query exceed the threshold parameter value.

If no measure is specified in the query when the cross join operation is evaluated, then the default measure will be used to filter the set.

The default setting of 0 disables this optimization. A threshold of 10,000 is a sensible initial value to test.

This threshold is the number of cells at which the adapter will ask the TM1 server to omit empty cells from the MDX data query results. Picking a threshold that is too small will negatively affect performance due to an increased volume of server and network round trips. Higher thresholds are less effective at removing null rows and columns. Empirical measurements suggest that values from 10,000 to 100,000 are best.


Tips

Your report may contain nulls and zeros after the provider crossjoin reduces the result set. This can occur when some smaller cross join operations are done locally, or when there are several measures referenced in the query. Dense cubes will not see the same gains as sparser cubes. The row/column suppression feature in the studios can be used to filter the remaining null rows.

You will need to restart the BI Server server after you modify the qfs config.xml file in order for the change to take effect. If you are testing several versions of the config setting it is possible to kill IBusTKServerMain.exe processes, between changes.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=570039
ArticleTitle=IBM Cognos Proven Practices: Performance Tips when using Cross JoinQueries against a TM1 Datasource
publish-date=11192012