IBM Business Analytics Proven Practices
Case Study - Migrating BO Reports to IBM Cognos BI without Universe Files
Product(s): IBM Cognos 10.1.x, 10.2.x; Area of Interest: Upgrade and Migration
This content is part # of # in the series: IBM Business Analytics Proven Practices
This content is part of the series:IBM Business Analytics Proven Practices
Stay tuned for additional content in this series.
Purpose of Document
This document provides a technical summary of how to migrate SAP Business Objects (BO) reports to IBM Cognos BI without the BO Universe project files. It covers the primary workflow of the migration activities and points out multiple items to consider and watch out for as a result of the complexity and difficulty of the migration process.
This document applies to IBM Cognos BI versions of 10.1.x and 10.2.x.
Exclusions and Exceptions
This document does not cover using IBM Cognos Report Studio or IBM Cognos Framework Manager (FM) or IBM Cognos BI performance tuning.
This document assumes readers have experience with IBM Cognos BI - in particular IBM Cognos Report Studio and IBM Cognos Framework Manager.
In a SaaS (Software as a Service) application report migration project, the authors successfully migrated over 40 SAP Business Objects reports to IBM Cognos BI within 6 months and summarized the best practice of migrating BO reports to Cognos when there are no BO Universe files that can be leveraged. Considerations about security, performance tuning and globalization of reports are mentioned in this document. In the last sections of this document, issues and limitations encountered along the way and corresponding solutions will be discussed.
The project the authors worked on is a SaaS (Software as a Service) application where reporting serves multiple client organizations known as tenants. The BO reports are built on top of a database called Operational Data Store (ODS) in the project, which is replicated from a production Online Transaction Processing (OLTP) database every evening.
As shown in Figure 1, the high level architecture has three tiers in this solution - the Data Tier, Business Tier and Client Tier. In the Data Tier, ODS is used as the data source. In the Business Tier, the Business View Manager Component of Business Objects is used to build Data Connection, Data Foundation, Business Elements and Business Views. In the Client Tier, the Crystal Reports component of Business Objects is used to design the reports.
Figure 1: High level architecture of the Business Objects solution
In order to efficiently migrate BO reports to Cognos BI, a star schema or snow-flake schema is not used in this project. Instead Cognos query subjects are built directly on top of ODS data source. Although building Cognos reports on top of a star schema is the best practice, with project constraints, the approach described in this document is the fastest way with the lowest risk. So that’s why this approach is used for short-term migration. Figure 2 shows the high level architecture for the new Cognos solution.
Figure 2: High level architecture of the IBM Cognos BI solution
There are also three tiers - the Data Tier, Business Tier and Client Tier. The Data Tier is exactly the same with BO one. For the Business Tier, we use IBM Cognos Framework Manager to build Query Subjects as the model of the reports. One Query Subject contains one or more Query Items. One or more Query Subjects can compose a package and the package can be published to the Cognos server so the Client Tier can use Cognos Report Studio to design and build the reports.
Steps of Migrating a BO Report to Cognos
The best practice of efficiently migrating Business Objects reports to IBM Cognos BI without the Universe file is summarized below and graphically represented in Figure 3. There are four main steps and two optional steps. The optional steps create outputs that are fed into one of the main steps. As shown in Figure 3, Optional step 1 feeds into main Step 2 and Optional step 2 feeds into main Step 4.
Figure 3: Flowchart of steps to migrate a BO report to IBM Cognos BI
The detailed description of each step listed as below.
Optional step 1: Export Business View in Business View Manager
Every BO report is built on top of a Business View. A Business View is a logical collection of Business Elements. Users see Business Views as abstract database of connections and the contained Business Elements as virtual tables that contain Business Fields.
A Business View file can be exported from BO and used in Step 2 in Figure 3. The “Name” element of the Business Element field in the Business View file can be used as the sub-query alias.
To find which Business View a report uses, in the Crystal Reports client use the Database Expert by selecting Database > Database Expert from the menu bar. The Business View name is in the right pane as shown in Figure 4.
Figure 4: Database Expert showing the Business View name
Once the Business View name is found, open Business View Manager, select Tools > Export, select the Business View and export it as an XML file.
Optional step 2: Export Business Objects report definition file
The Business Objects report definition file contains the report layout, data fields, filters and formulas. A Cognos report can be re-created using this definition file as reference. A BO report definition can be exported to a text file using the Crystal Reports client.
Open BO report file (with a RPT extension) in Crystal Reports. From the menu bar, select File > Export > Export Report (see Figure 5), a dialog opens, select the format “Report Definition (TXT)” and destination “Disk file”, then export, the report definition file will be saved.
Figure 5: Using Crystal Reports to export the Business Objects report definition file
Step 1: Generate Report SQL in Crystal Reports
BO report SQL can be generated in Crystal Reports client by the following steps.
- From the menu bar, select Database > Show SQL Query
- Input the parameter values in the prompt page if there is any
- Click OK to get the SQL
Step 2: Modify the Generated SQL
Now that we have the SQL, there are a few modifications that need to be made in order to use the SQL in a Cognos Query Subject.
- The BO SQL contains lots of sub-queries which don’t have an alias.
Since Cognos will report errors when creating a Query Subject using
SQL that doesn’t have aliases for sub-queries, aliases need to be
added for every sub-query in the SQL. It’s better to have a meaningful
alias for the sub-query and the BO Business Element field name can be
used as the alias. The field name can be found in Business View file.
For example, a sub-query in the SQL would be similar to the following,
where bad.bol_id = “ORDERS”.”BOL_ID” and bad.area_id = “ORDERS”.”DEST_AREA_ID”
Search this sub-query in the Business View file. The sub-query will be in the node
<Field>/<FormulaFieldInfo>/<Text>. The value of node
<Field>/<FormulaFieldInfo>/<Name>can be used as the alias.
<Field xsi:type=”CrystalEnterprise.MetaData.BusinessFormulaField” id=”3710”> <CUID>ATrLb00O2DBLtploDCUeZHA</CUID> <ObjectPrincipals xsi:type=”CrystalReports.PropertyBag” id=”3711” content-id=”9” /> <FormulaFieldInfo xsi:type=”CrystalReports.FormulaField” id=”3712” version=”2”> <Name>ORD DEST NEEDED BY DATE</Name> <Description>Order destination needed by start date</Description> <Type>xsd:dateTime</Type> <Length>12</Length> <HeadingText /> <Attributes xsi:type=”CrystalReports.PropertyBag” id=”3713” content-id=”78” /> <Syntax>SQL</Syntax> <Text>(select bad.NEEDED_BY_START from bol_area_dates bad where bad.bol_id = “ORDERS”.”BOL_ID” and bad.area_id = “ORDERS”.”DEST_AREA_ID”)</Text> <FormulaNullTreatment>Exception</FormulaNullTreatment> </FormulaFieldInfo> <SQLExpressionConnectionInfo xsi:type=”CrystalEnterprise.MetaData.DataConnection” id=”79” content-id=”79” /> </Field>
- The WHERE clause also needs to be modified in the BO generated SQL.
The conditions in the WHERE clause were generated by the inputs when
developer generated the SQL. The conditions should be replaced by
Cognos prompts so the report user can dynamically filter the report
output using the prompts. For example, there is a report showing the
maintenance costs for a machine in a specific date range. The report
user can choose the date range from a date control. When generating
report SQL in BO Crystal Clients, the date range needs to be provided.
For example, if the date range provided is from May 1, 2012 to May 1,
2013, SQL similar to the following will be generated,
SELECT *** FROM *** WHERE *** MAINTENANCE.MAINTENANCE_DATE >= TO_DATE(‘2012-05-01’,’YYYY-MM-DD’) AND MAINTENANCE.MAINTENANCE_DATE <= TO_DATE(‘2013-05-01’,’YYYY-MM-DD’)
The fixed date range in the SQL WHERE clause should be specified in Cognos BI similar to the following,
SELECT *** FROM *** WHERE *** MAINTENANCE.MAINTENANCE_DATE >= TO_DATE(#sq(prompt(‘From Date’,’date’, timestampMask($current_timestamp,’yyyy-mm-dd’)))#,’YYYY-MM-DD’) AND MAINTENANCE.MAINTENANCE_DATE <= TO_DATE(#sq(prompt(‘To Date’,'date', timestampMask($current_timestamp,'yyyy-mm-dd')))#,'YYYY-MM-DD')
The reason filters are put directly into the SQL is to improve report performance. Performance will be touched on later in the section titled Performance Tuning.
If there are a large number of reports, it is possible to automate adding the alias and filters, saving the effort of having to manually copy the Cognos prompts to the SQL.
For adding the alias, the automation program would need to:
- Read the SQL generated by Crystal Reports client and the corresponding Business View file.
- Parse the sub-queries in SQL select clause.
- For each sub-query, search it in the Business View file.
- Get the value of node
<Field>/<FormulaFieldInfo>/<Name>when finding matched node in the Business View file.
- Use the Name value as the alias for the SQL sub-query.
- Add the alias for all the sub-queries in the SQL and generate a new SQL file.
For adding filters, the report developer would need to create a project-specific template file and a parameter file containing the filters to be added to the report. For example, the filter example above would have a template file that contains the following,
012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789 DateRange $DateRange$ >= TO_DATE(#sq(prompt(‘From Date’,’date’, timestampMask($current_timestamp,’yyyy-mm-dd’)))#,’YYYY-MM-DD’) AND $DateRange$ <= TO_DATE(#sq(prompt(‘To Date’,'date', timestampMask($current_timestamp,'yyyy-mm-dd')))#,'YYYY-MM-DD')
The first field in the template file (
DateRange) is a tag that defines an entry and this entry will be referred to by the parameter file. The second field contains the SQL to be substituted. The SQL contains substitution variables that will be resolved using the parameter file.
The parameter file might look something like,
The first field (
DateRange) represents an entry in the template file and the second field (MAINTENANCE.MAINTENANCE_DATE) represents the actual SQL condition field to be used to replace the substitution variable ($DateRange$) in the SQL in the template file.
The automation program would perform the following tasks,
- Read the SQL file, the template file and the parameter file.
- Parse the conditions in the SQL WHERE clause.
- For the condition field
MAINTENANCE.MAINTENANCE_DATE) which matches the parameter file entry, get the entry name from the template file (
- Find the filter template in the template file according to the
entry name. In this example, the template entry name
DateRangemaps to the filter
$DateRange$ >= TO_DATE(#sq(prompt(‘From Date’,’date’,timestampMask($current_timestamp, ’yyyy-mm-dd’)))#,’YYYY-MM-DD’) AND $DateRange$ <= TO_DATE(#sq(prompt(‘To Date’, 'date',timestampMask($current_timestamp,'yyyy-mm-dd')))#,'YYYY-MM-DD')
- Generate the filter by replacing the variable in the filter
template with the condition field. In this instance replace
There would normally be only one template file that can be used for all of the reports and one parameter file for each report.
Step 3: Create a Cognos Query Subject
With the modified SQL, a Cognos Query Subject can now be created in IBM Cognos Framework Manager. The following points need to be followed when creating our Query Subject.
- The first step the New Query Subject wizard is the
Name and Type step. Select Data Source
(Tables and Columns) instead of the default option of
Model (Query Subjects and Query Items). This is
shown in Figure 6.
Figure 6: Select Name and Type from the New Query Subject wizard in Cognos Framework Manager
- In the Select Data Source step of the wizard, select the data source and unselect the Run database query subject wizard option.
- In New Query Subject step, copy the modified SQL to the right side editor. Switch to the Test tab and click Options. In the pop up dialog, switch to the SQL settings tab and select Native as the SQL type.
Step 4: Create an IBM Cognos Report
Create the Cognos report using the Business Objects report as a reference. For example, if BO report is a list report, then create a list report in Cognos. If the BO report is a crosstab report, then create a Cognos crosstab report.
Most of the reports in this project have prompt pages for users to filter the report output and also have the report footer information shows what parameters the user selected. It is possible to automate generating a blank report with a prompt page and report footer by creating an IBM Cognos SDK program. The program would extract all the prompt page and footer sections from the reports that the report developers have already developed and place them into a template report. When a new report needs to be developed, the Cognos SDK program would extract all the parameters in this new report and if the parameter can be found in the template report, the program will copy the corresponding section from the template report to the new report. If the parameter cannot be found in the report template, the program would alert the report developer to implement it manually.
Theoretically, for simple reports, the report page can also be generated automatically with the BO report definition file. But because BO report definition file doesn’t have the mapping information between column headers and data fields, column headers need to be changed after the report page generated. While it is a process that can be automated, it’s something that is normally done manually.
In a SaaS application, all the tenants use one application. Since data is stored on the vendor’s server, security becomes an issue. To prevent the data and IBM Cognos BIobjects from being accessed incorrectly across different tenants, the authors decided on the following for this project.
For the data security, session parameters in Framework Manager are used to control the data according to the user role, user organization or user ID. The SQL with session parameters looks something like this,
SELECT *** FROM ORDERS ORDERS WHERE ORDERS.ORG_ID = #$account.parameters.OrgAccountId#
IBM Cognos BI object security
IBM Cognos BI objects such as Folders, Reports and Report Outputs should have different permissions for different organizations, users and roles. For example, financial reports should not be visible for general report users and report outputs should not be accessed by the report users in other organization.
Folders and reports can be secured directly in IBM Cognos Administration by allowing or denying users access to the object or even keeping the object hidden from all the users.
For the report outputs in Business Objects, a user can only see their run history of the report. In IBM Cognos BI, if a report is accessible to all the users in the Public Folders area, one user can view the report output run by other users. This is not acceptable for users in a SaaS offering application. The solution is revoking read and write permissions on the report so that a user cannot view history anymore. If the user really needs the run history, they can create a Report View of the report and then save the report output.
A similar issue exists in scheduling a report. One user can see other user’s schedule and can even remove it if the Administrator doesn’t revoke the read and write permissions on the report. Once again, the solution is to create a Report View and then create a schedule on that Report View.
The SQL generated by BO reports can be tuned to get better performance. SQL performance tuning will not be discussed here as it is a general topic that is covered in depth by database vendor documentation as well as many other documents that can be found in an internet search.
IBM Cognos BI performance can also be improved by changing settings such as caching query results. For more information on tuning IBM Cognos BI servers, refer to the chapter titled Performance Monitoring and Tuning in the IBM Cognos Business Intelligence Architecture and Deployment Guide.
For this project, the authors determined it was best to add filters to the Query Subject instead of adding them to each report (see main Step 2 in Figure 3). This is because the authors did some performance tuning in the SQL FROM clause to reduce the data before joining to other tables. In this case, if filters were added to the report in Report Studio, there will be a two-stage query process. One is retrieving the large data set from database; the other is filtering the large data set by parameters. But if the filters were added to the query subject in FM, it will be a single query with conditions in WHERE clause and the performance will be much better.
A SaaS based application reporting requires the support of globalization. One standard report can be used by users in different countries. IBM Cognos BI supports multilingual reporting; however, the globalization of the FM model and reports means changing the model source file (model.xml) and report source file (report specification XML) whenever a new language is added. A regression test should be run whenever the model or report specifications are changed.
For information on creating a multilingual reporting environment in IBM Cognos BI, refer to the chapter titled Setting up a Multilingual Reporting Environment in the IBM Cognos Framework Manager User Guide.
It is possible to automate the report globalization task. The automation program would need to read the model XML file and all the XML report specifications and extract the static text values from these files to an English language resource file. This resource file can be provided to a translation service. After the translation is done, the automation program can read the translated files and add the multilingual content to the model XML and the XML report specifications.
Issues and Limitations
This section will briefly introduce the issues the authors encountered and some of the solutions found.
Internet Explorer support for Excel report output
The authors encountered some issues when using Internet Explorer (IE). For example, with IE 8 in Cognos 10.1.x or 10.2.x, if a report is run from IBM Cognos Connection with a report output format of Excel 2007 and the delivery option of View the report now, there is no prompt that lets the user choose to open or save the file. This issue was resolved by setting up security in IE.
FTP support in IBM Cognos BI
BO supports FTP as delivery method for report outputs. A user can select a specific FTP server or a default FTP server (see Figure 7).
Figure 7: Business Objects report delivery methods
IBM Cognos BI does not provide built-in support for delivering report output via FTP. Figure 8 shows the IBM Cognos BI delivery options which include View the report now, Save the report, Print the report and Send me the report by email.
Figure 8: IBM Cognos BI report delivery methods
It is possible to develop a solution that lets users get report output from a FTP site. First, a separate folder for every customer needs to be created in the FTP server. Then a shell script which will run on the IBM Cognos BI server will need to be created. The shell script will look for report outputs that get created when the Save report outputs to disk option is enabled in the Cognos BI environment, read the XML file that contains the information about who ran the report and FTP that report output to the proper folder where the user can be accessed it later via FTP.
Order of parameters in the IBM Cognos report confirmation page
When running an IBM Cognos BI report, a confirmation page may be is presented and on this page the list of parameters and their values appear in an order that does not always match the order they appear in the report. For example, as shown in Figure 9 a report has several parameters in its prompt page and the order of the parameters is as follows,
- Date Choice
- From Date
- From Date Offset
- To Date
- To Date Offset
- Origin City
- Destination City
- Carrier Name or SCAC choice and value
- Movement Type
- Vouchered Status
- Payee Name
Figure 9: Parameter Orders in Prompt Page
But when the user selects to save this report when running it, the confirmation page shown in Figure 10 presents with the parameters being listed in the following order,
- Vouchered Status
- Movement Type
- Destination City
- To Date
- From Date
- To Date Offset
- Carrier Name or SCAC
- Origin City
- Date Choice
- From Date Offset
Figure 10: Parameter Orders in Running Confirmation Page
At the time of this writing, there is no way to customize this however IBM
Cognos Support has created an APAR for this issue at the following