By Craig Harding, Consultant for PerformanceG2
 

Better performance will be realized by using parameters and prompts within Model Query Subjects, filter objects or stand alone calculations. This will allow for better SQL generation than inserting them within the SQL of a Datasource Query Subject.

These recommendations lead to better SQL generation, which in turn, leads to better performance.

Nearly any prompting can be done in a Model Query Subject:
[Product dimension].[Product line].[PRODUCT_LINE_CODE] =
#prompt(‘Product Line Code’, ‘integer’, ‘1’)#

Same with Calculations:
Case [Product dimension].[Product line].[PRODUCT_LINE_CODE]
When #prompt(‘Target Product Line Code’, ‘integer’, ‘1’)# then 1
Else 0
End

The added benefit of placing prompts in a stand alone calculation is that if the report author does not use the calculation, then the prompt is no longer needed. There is no need to add this case statement to the Base SQL.

When using calculations and filter objects in Model Query Subjects or as stand alone objects, the Datasource Query Subjects can be left untouched. This means the SQL remains minimized and eliminates the need to fetch metadata.

 
 
Contact the Blogger

 
Kirk Wiseman, VP of Training for PerformanceG2
 
In Cognos 8.4, it is now possible to use existing IBM Cognos 8 packages and reports as a data source in Cognos Transformer. This has made building data sources that much easier since any Cognos 8 report can be leveraged in Transformer, improving development time and ensuring that the data is all coming from the same underlying data source.

When using IBM Cognos 8.4 as a data source for Transformer, use the following guidelines to achieve efficient cube build times:

• If filtering and/or calculations are required, use Cognos 8 reports as the data source
• Create separate data sources in Transformer for each required dimension as well as for measures
• Include foreign keys found in fact tables to transformer modelers
• Avoid unnecessary local data processing on IBM Cognos 8 servers

 
Use IBM Cognos 8 Reports when Filters and/or Calculations are Required

While you can request that the IBM Cognos Framework Manager modeler include certain filters and calculations in an IBM Cognos 8 package, this requires extra maintenance on the Framework Manager modeler’s part. Instead, Transformer modelers can use list reports that incorporate filters/prompts and calculations as needed. If prompts are used in the report, Transformer modelers can control which filters are applied when the PowerCubes are built.

Regardless of whether the work is done in the Framework Manager model or in a report, the net gain remains the same; where supported, processing will be pushed to the database and the load on Transformer will be reduced. If filters/prompts are used, then Transformer will deal with a reduced record set, thereby decreasing build times.

Using reports allows for a greater degree of control to meet requirements and removes the need for additional development support.
 
Create Separate Queries in Transformer
When using a package as a data source in Transformer, individual queries should be created based on the data sources for the cube; one for each required dimension and one or more for the measures depending on how many underlying fact tables there are and what the business need is.

When using a report as a data source, either use separate list reports, again, one for each dimension and one or more for the measures, or create separate list report queries in one report. Then in Transformer, a query is created for each of the separate list reports or separate list queries within a single report.
 
Expose Fact Table Foreign Keys in the Package
Typically, foreign keys found in the tact table are not presented in the fact query subjects in a Cognos 8 FM model. Either they are not included in the model query subject, or they are hidden form the user. For most ad hoc query users or report authors, they provide little benefit and can cause confusion. To prevent confusion to the larger authoring audience, security can be used to expose the keys to a select group of authors only. For example, only Transformer modelers. In certain cases such as with Tranformer modelers, there is a clear benefit to exposing the foreign keys to the users. It will be more efficient to create a measures data source in Transformer that takes its keys from the actually fact table, rather than from its related dimension tables. In this way, the query will go against one table in the data source, rather than several. The database may rewrite the query to be more efficient before executing it, but a a general rule of thumb, the cleaner the SQL Cognos submits to the database, the more likely an efficient query will be run at the database layer which takes advantage of database optimization.
 
Avoid Unnecessary Local Data Processing on IBM Cognos 8 Servers
Certain query scenarios may require data processing on IBM Cognos 8 servers. In some of these cases, performance is not an issue and may even be required to produce a result set where a database vendor does not support the query. However, there are cases where local processing can adversely affect query performance and in turn, affect PowerCube build times. A typical example of this is when a function that is not supported by the database vendor is used in a calculation. This will cause a retrieval of the entire data set from the database and then perform the calculation locally on the Cognos 8 servers. To rectify the situation, use an equivalent function that is supported by the database vendor.

To see if local processing is occurring for a slow performing query, there are a couple of troubleshooting techniques. One is to look at the Cognos SQL generated for the query and compare it to the Native SQL. This can be done in Framework Manager or in Report Studio. Actions being performed in the Cognos SQL and not in the Native SQL indicate that processing is done locally. Another technique is to set query processing to Database Only. In Framework Manager this setting is found in the properties of the Data Source objects and is called Query Processing. In Report Studio, the setting is found in the properties of the query and is called Processing. When the setting is set to Database Only, an error will be generated in cases where the query can not be fully pushed to the database. The error will typically identify the offending time preventing the push to the database.

NOTE: This is based upon the IBM Best Practices Document which can be found at: http://www.ibm.com/developerworks/data/library/cognos/modeling/transformer/page512.html?ca=drs-
 
 
Contact the Blogger

 
By Sanjeev Datta, Senior Consultant for PerformanceG2
 
 
I always find interest in situations where, as a full-service CPM organization specializing in BI, we make an instant difference to our clients by significantly improving their profitability and performance, while at the same time, helping them cut costs and go green.

A few weeks ago, one of our clients approached us with a situation where invoice reports that went our to their customers who bought products via a Web site, were eating up their budgeted office supplies allocations more than they had expected. In a way, this was good news as sales figures were up, however the sales numbers would look more attractive if internal costs were down too.

One solution that we proposed to our client to meet this challenge was to create invoice reports in IBM Cognos Report Studio and distribute them via an automated nightly email process rather than a daily chore of printing out invoices and analyzing and calculating shipments, which can be time consuming and cumbersome tasks, especially for invoices going to global clientele.

PerformanceG2 took upon the challenge of creating this highly customized and extremely detailed invoice report in Report Studio version 8.4. With the use of singletons, which are data items that can be placed anywhere on the pixel perfect Web based reporting tool, and with the use of blocks, tables and various conditional statements to customize the report to include business rules for specialized groups of customers (e.g., domestic versus international, currency conversions, changing address lines, etc.), we delivered to our client The Invoice Report.

Report delivery was decided to go out in PDF format as an email attachment. For this, we used a feature in Report Studio known as “bursting”. The burst feature is used to run a report and divide the output based on a burst key, in this case, unique invoice numbers, which then generate multiple outputs inclusive of security features for each of these invoices. Therefore, this one developed report was to generate thousands of invoices per month and email them out to their respective customers daily, eliminating human errors and postal delivery delays all while significantly reducing printing costs, paper and my favorite: terminating the extremely time consuming task of folding these paper invoices and carefully placing them into envelopes. In addition, in the event a scheduled job halts, or is not completed in the specified time, an alert is sent out to the concerned owners who can take appropriate actions to rectify any inconsistencies before they reach the far end.

In the end, the challenge was met, the project was a success, and the project is now in production to our client’s and their customers’ satisfaction. With the use of Cognos Event Studio, and some creative green thinking, we have handed ownership of this project to our client.

 
 
 
Contact the Blogger