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

 
Kirk Wiseman, VP of Training for PerformanceG2
 
For many organizations, TM1 is a critical application that must be maintained as a 24×7 enterprise application. In these cases, it is not acceptable to shutdown the TM1 server to perform a backup. TM1 provides organizations the ability to maintain 24×7 uptime, and also perform regular backups of the data directory. Several changes will need to be made to the default settings of the TM1 server configuration file (tm1s.cfg) in order to perform a live backup successfully.

The advantages of this method include ease of administration, 24×7 system uptime and automation which can be created within TM1 using Turbo Integrator and Chores. The disadvantages of this method include initial configuration that must be performed, and the possibility of lost data between “saves” if the data directory files become corrupt.

The following steps must be performed in order to run backups from a live TM1 server:

1) Modify the default TM1 server configuration file (tm1s.cfg)

• Point the log files to a location other than the data directory by inserting the “LoggingDirectory” parameter in the configuration file. By default, TM1 writes log files to the data directory. Since these files will be open and active while the TM1 server is running, any attempt to back them up will result in a file sharing violation and may cause a backup process to fail.
• Verify that the “AuditLogOn” parameter is set to ‘F’. This is the default setting, however if the “AuditLogOn” parameter is set to ‘T’, then as in the case above with the transaction logs, open and active files will be in the data directory. Attempting a backup will cause a file sharing violation which may result in a backup failure.

2) The administrator must perform, or must set up automation to perform, a “Save Data All” command. This command should be executed immediately prior to backing up the TM1 server. This command takes all changed data in RAM and saves it to the TM1 data directory files, where it is then able to be captured in the back up process.

3) Backup all files in the TM1 data directory

• Create a batch file called tm1back.bat that has the following script: “C:\program files\winzip\winzip32.exe” -a -p -r c:\backup\tm1backup.zip “C:\<tm1data folder>\*.*”

4) This process can easily be automated through the use of Turbo Integrator calling the batch file and scheduling the Turbo Integrator job via the TM1 Chores.

• Expand the TM1 sever and click on “Processes”;
• Click on the “Advanced” tab;
• Click on the “Epilog” tab;
• Below the “#****End: Generated Statements****” line, type the following:
  SaveDataAll;
  ExecuteCommand(‘C:\<folder where you saved the batch file>\TM1backup.bat’,0);
• Click on the “Save” icon or choose from the menu, “File” > “Save As…”
• Enter a name for the process, for example Backup
• Next, click on the “Schedule” tab;
• Check the box “Schedule this process as a chore named:”;
• Enter a name for the new Chore, for example Backup Chore
• Choose a start date and a frequency and then click “Save”

There you have it – a scheduled backup procedure that will create a backup of your TM1 server without having to shutdown the server!
 
Contact the Blogger

 
Kirk Wiseman, VP of Training for PerformanceG2
 
This month’s IBM Cognos Supportlink newsletter has a great article on how to do “on the fly” language translation using Cognos 8 Report Studio and Google’s language API. As we know, Cognos 8 is a multilingual reporting tool that can automatically switch languages based on a user locale. However, this requires storing all the different language variations within the client’s database. Using the Google API allows the client to have a translation applied automatically without having to store the data locally. To read the full article, click here.
 

 
PerformanceG2 is an advanced business partner of IBM, and IBM Cognos Software. For more information on our Cognos offerings, click here.
 
 
Contact the Blogger

 
Kirk Wiseman, VP of Training for PerformanceG2
 
Often with large text items, the Select and Search prompt display areas are not wide enough to display the data. Here’s a way to make the display areas dynamically re-size to meet the demands of the wider data:

1. Locate and backup the file ..\c8\webcontent\prompting\properties.js located on the Cognos 8 gateway component.

2. Set the SYSTEMPROPERTY_CSEARCH_AUTO_RESIZE_RESULT_LIST to true.

See below:
————————————————————————–
// Copyright (C) 2008 Cognos ULC, an IBM Company. All rightsreserved.
// Cognos (R) is a trademark of Cognos ULC, (formerly CognosIncorporated).
/* Use this file to override flags in the prompt controls. */
/*
Use the following flag to disable search request when the user press Enter in the search input box.
This is a workaround to prevent searches when users use japanese IME input control.
*/
var Search_DisableEnterKeyAutoSubmit = false;
var SYSTEMPROPERTY_CSEARCH_AUTO_RESIZE_RESULT_LIST = true;
var SYSTEMPROPERTY_CREATE_CUSTOM_SCRIPTS_REFS = true;
var SYSTEMPROPERTY_CUSTOM_SCRIPTS_OVERRIDE_FORM_SUBMIT = false;
var SYSTEMPROPERTY_REORDER_DROPDOWN_VALUES_IN_RANGES = false;
—————————————————————————-
The Select and Search prompt control display areas are variable width and therefore wide information is no longer cut off.

 
Contact the Blogger
 

 
Kirk Wiseman, VP of Training for PerformanceG2

I just uploaded a new video on YouTube which examines the issues of managing Slowly Changing Dimension in TM1.  

As we know, data that we want to keep track of within a TM1 dimension can change over time. For example, sales reps become responsible for different geographies; Products are rebranded or reformulated and so on. These types of slow changes are appropriately named Slowly Changing Dimensions or SCD’s.

There are several types of SCD’s but the two most common are Type 1’s (we don’t care about history) and Type 2’s (we care about and wish to track historical changes).  An example of a Type 1 change would be if a product, say tents, rolled up to a product type of camping equipment, however a change was made to consolidate tents into the product type of Outdoors equipment. In this case, we want to see all data, including history, roll-up to the new product type of outdoors equipment.

On the other-hand a type 2 change would be if we wanted to see the historical data for tents roll-up into Camping equipment and any new data for tents to rollup to Outdoor Equipment. This can be useful for trending analysis, etc.

To watch this example applied to TM1 — check out the video on YouTube:

http://www.youtube.com/watch?v=KD3QwRHPZ38

For more information on how PerformanceG2 can assist you in your TM1 needs, click here.

Contact the Blogger

Kirk Wiseman, VP of Training for PerformanceG2

1. Keep color schemes simple. Use the excel color scheme for visual acceptance.

2. Use Sparklines (microcharts) to add visual relevance to lists and crosstabs.

3. Gauges should be limited to 4 or fewer needles.

4. Create a report that references the last update date of the dashboard’s data.

5. Use Event Studio to create Alerts that will appear in top left Corner as Newsitems.

6. Use Event Studio to send emails about the critical alerts.

7. Create a scheduled job to run all the reports for the dashboard and have that job called by a trigger event – such as a db refresh.

8. Dashboards are not static – expect them to change and morph within the first 18 months.

Kirk Wiseman, VP of Training for PerformanceG2

Great article from ZDnet Asia on how economic uncertainties have intensified the need for timely, accurate and relevant information to make sound business decisions.
Read the article here.

Contact the Blogger

Kirk Wiseman, VP of Training for PerformanceG2
A requirement of the 2009 American Recovery and Reinvestment Act (ARRA) is for all funds to be tracked and results measured. IBM Cognos 8 and PerformanceG2 are perfectly positioned to help Federal, State and local governments meet the stringent ARRA requirements. Check out the links below to learn more about reporting on your Economic Recovery Funds:
Tracking and Measuring Funds with IBM Cognos 8

Tracking and Measuring the Success of Your Agency’s Stimulus Dollars archive seminar

Contact the Blogger