– Migration from Business Objects to Cognos BI Umesh Ashok

BI – Migration from Business Objects to Cognos
Umesh Ashok
Tata Consultancy Services
Mumbai
[email protected]
ABSTRACT

Migrating Metadata
This White paper describes the POC Experience of
migrating reports from Business Object XI R2 to Cognos BI
8.3

Building reports in Cognos 8 BI

Testing reports for design, layout and data
considering BO reports look and feel as the base
line for layout

Implement Cognos reporting application in QA and
Production environment
It highlights the challenges, solutions and best practices
implemented for seamless transition
TARGET AUDIENCE
Cognos Architect and Developers of BO to Cognos
Migration project
3.2 Required Components
Table 1: Components
PRE REQUISTIC
Knowledge of Cognos BI 8.3, Business Objects XI R2 and
RDBMS Databases.
S.No.
1.
1. INTRODUCTION
2.
XYZ uses Business Object as a reporting tool to develop
and deliver reports to the business to analyze the data.
XYZ wants to upgrade these BO reports into Cognos
reports using Cognos 8 BI suite. Existing BO Reports
presented quite a challenge for migration because of many
reasons like diverse formats of reports and multiple
interfaces. Cognos BI environment is available with the
XYZ.
Component
Name
Cognos 8.3
Business Objects
XI R2
Type
Application
Application
3.
Oracle 10g
RDBMS (OLAP Database)
4.
LDAP
Authentication tool
Prime goal to migrate BO reports to Cognos BI.
2. BUSINESS CHALLENGES

Non Standard reporting formats

Installing BO in users machine and maintaining

Reports residing on users machine

Non availability
specification

Inadequate Security
of
functional
and
technical
3. ACTIVITIES & COMPONENTS
3.1 Activities

Analysis of existing reports for their functionality and
technical aspects.

Plan a road map for migration keeping all the
reusable component and designing of new
components with Enterprise architecture in mind
Page 1 of 7
4. ARCHITECTURE
4.1 Current – Architecture (Business
Objects)
4.2 Proposed- Architecture (Cognos 8 BI)
The Cognos 3 tier architecture is described below
5. Metadata of Business Objects and
Cognos BI
There are several types of objects that you can publish to
Business Objects Enterprise

Reports

Documents (from Desktop Intelligence)

Programs

Microsoft Excel/Word/PowerPoint files

Adobe Acrobat PDFs

Text files

Rich text format files hyperlinks

Object packages (which consist of report and/or
The following points outline the metadata of Business
Objects and Cognos 8 BI

In Business Objects, Universe is built using
Designer which is used by user to build reports

In Cognos 8 BI, Model is built using Framework
Manager which is published as package to
Cognos Connection portal to develop reports.
program objects)
Page 2 of 7
5.1 Security Set up in Framework
Manager
In Framework Manager, security is a way of restricting
access to metadata and data
Types of security in Framework Manager:

Data Security
Creating a security filter and applying it to a specific query
subject controls the data that is shown to the users when
they build reports

Object Security
Object can be secured directly by allowing users access to
the object or denying users access to the object or keeping
it hidden from all the users

Package Security
Security to a package can be set after identifying who
should and who should not have access to the package
5.2 Reporting Tools available in Cognos
8 BI

Query Studio
It is used by report users for creating self-service
business intelligence reports

Report Studio
5.
In Metadata type select IBM DB2 Cube Views 8.1
TO 8.2 (*.xml) as shown in the following screen
shot to import DB2 cubes
It is a Web-based tool that professional report authors use
to build sophisticated, multiple-page, multiple-query reports
against multiple databases

Analysis Studio
It is used for Exploring, analyzing, and comparing
dimensional data by report users

Event Studio
It contains actions based agents to notify decision makers
as events happen
6. MIGRATION OF BUSINESS OBJECT
UNIVERSE TO COGNOS FRAMEWORK
MANAGER MODEL
The following steps outline how to import DB2 Cubes into
Framework Manager.
1.
Open the universe in designer, which is required to
be migrated to Cognos
2.
Go to File on the toolbar and click ‘Exchange
Metadata’ to save it as ‘*.XML’ file.
3.
Open a new project in Framework Manager and
Run Metadata Wizard to import DB2 cube.
4.
Select ‘Third Party Metadata Sources’ as
Metadata source as shown in the following
screen shot.
Page 3 of 7
6.
Click ‘Next’
8.
Click ‘Next’
7.
Click ‘Next’
9.
Select the objects you want to import and Click
‘Next’
Page 4 of 7
10. Following screen shot below shows the metadata
of DB2 cube
enterprise search engine, Microsoft Office application
or Web browser

Data can be provided in several formats like HTML,
Excel, PDF, CSV, XML, and more

Data can be accessed through several mediums like
portal, e-mail, mobile, search, and office

Cognos 8 Framework manager automatically
generates database properties i.e. It can generate the
relations
between
tables
[Query
Subjects]
automatically

In Cognos we can generate a single report from
multiple data sources

Various complex Reporting features like Dash
boarding, Drill-through, Maps, Analytic Calculations,
Master Detail Reports, Charts, and Real Time
Reporting etc. could be performed in Cognos 8 BI

Creates an intelligent balance between providing
access to on-line data access and downloaded off-line
data, thereby satisfying all business users operational
reporting and historical analysis needs within a single
environment.
7. IMPLEMENTATION CHALLENGES &
THEIR SOLUTIONS
1.
Metadata of tables, views and procedure should be
consistent across database instance for smooth
promotion of code from one instance of the database
to another (ex. Promotion of a cognos report at DEV
instance of database to QA/Production instance)
2.
Value of parameters used in Stored Procedure to be
passed using macros from framework manager, same
macro variable name to be defined in the reports
3.
Creating same filters and calculations in framework
manager model which are used across different reports
4.
Creating tabbed report in Cognos (explained in detail in
Appendex-1)
9. VALUE ADDITIONS TO CUSTOMER

Standardization of reports

Robust BI tool to accommodate future high volumes of
data & multidimensional analysis feature.

Provision to accommodate complex multidimensional
Adhoc reports.

Provision to accommodate complex Scorecards /
Dashboards

Better Scalability.

Better Role based / Access driven Security.
10. SUMMARY
8. BENEFITS

Data can be published in different languages

Cognos 8 BI is built with a single, modern Web
services architecture. The common zero-footprint,
browser-based environment requires no desktop
installs, plug-ins, or applets—minimizing the resources
required
for
development,
deployment
and
maintenance

Easy access to all the data – completely, quickly, and
accurately. The common metadata built can be used
for users to optimizes the work

Integration of Cognos 8 BI to all 3rd party software, its
open architecture makes it adaptable to changes

Cognos 8 Go! access modes help you increase
business intelligence user adoption rates within
organization by letting users interact with reports,
analyses, and other BI content using familiar
applications or devices, such as a BlackBerry®,
Instead of using BO for reporting, Cognos 8 BI will be used
for reporting purpose. Various complex Reporting features
like Dash boarding, Drill-through, Maps, Analytic
Calculations, Master Detail Reports, Charts, and Real Time
Reporting etc. can be performed in Cognos 8 BI
11. REFERENCES






Project Documents
TCS Knowmax Cognos forum
Cognos CoE Assets
www.Cognos.com
www.BO.com
www.ibm.com
Page 5 of 7
Appendex-1
How to create a tabbed report
Problem(Abstract)
Cognos BI doesn’t have a Tabbed Report template, but
a similar report can be created using drill-through. A
tabbed report has a series of tabs along the top
allowing the user to select the subreport they wish to
view.
Resolving the problem
We will be creating a report which conditionally displays
a subreport depending on which tab is clicked. In
reality, we are simply creating a 1-row table and doing
a drill-through on each cell, using the parameter
passed to determine which subreport to display.
Steps: Create the following report using Go Sales &
Retailers:
1) Create a List report.
2) Create a Crosstab report below the list report
associated with Query 2.
3) Go to Query Explorer and select Queries.
a) Add one new query, Query 3.
4) Create a parameter:
a) Add a Value Prompt to the main report
page. Place it just before the List Report.
b) Create a new parameter named "report"
and click Finish.
c) Select the value prompt and under
Properties pane, set the following:
c.1) Box Type to "None"
c.2) Default Selections to "List Report"
(choose Simple Selection)
c.3) Required to No.
This creates a parameter without a prompt.
5) Go to Query Explorer and select Query 3.
a) Under the Insertable objects add "Quantity"
from the "Orders" query subject to the Data
Items
pane.
b) Select the Toolbox tab and drag a Data Item
to the Data Items Pane. Enter the following
expression:
'List
Report'.
c) Drag a second Data Item to the Data Items
pane. Enter the following expression: 'Crosstab
Report'.
e) Select "Data Item1" and set the "Name"
property
to
"List
Report1".
f) Select "Data Item2" and set the "Name"
property to "Crosstab Report1".
6) Go to Page Explorer and select the report page.
7) Select any item in the report and under the Ancestor
tab (next to Properties title in properties pane), select
Page. Then set the Query property to Query 3.
8) Select
Variables.
the
Conditional
Explorer
and
click
on
a) Add a new string variable called "Report
Name".
b) The expression for this variable is:
if (ParamValue('report') is null) then ('List
Report') else (ParamValue('report'))
Note: The default report is the "List Report"
which is defined if the 'report' parameter is null
c) Add a value to the "Report Name" variable
for each sub report you want in the tabbed
report. E.g.: "List Report", "Crosstab Report",
etc.
9) From the report page, click the Toolbox tab under
Insertable Objects and drag a Conditional Blocks object
just below the Crosstab report.
10) Select the Conditional Block. Under the Properties
pane, "Block Variable" to the "Report Name" variable
defined in step (8). Click OK.
11) Then set the "Current Block" property to "List
Report", select a column in the list report and choose
the "List" under the Ancestor tab (to the right of the
Properties pane title). Then, drag the List report into
the Conditional Block.
12) Now, select the Conditional Block again and set the
"Current Block" property to "Crosstab Report". Then,
select a cell in the crosstab report and choose the
"Crosstab" under the Ancestor tab (to the right of the
Properties pane title). Then drag the Crosstab into the
Conditional Block.
Note: Populating the conditional block with each of the
reports you want to display by using the "Current
Block" property to associate each report with a "Report
Name" value.
You now have a report which displays a different
subreport depending on the value of a parameter.
13) Click the Toolbox tab under Insertable Objects and
drag a New List report below the Conditional Block.
a) Select the list report and under Ancestor tab
(to the right of the Properties pane title), select
List.
b) Set the Query to Query 3.
14) Add an N x 1 table into the List report created in
step 13, where N is the number of tabs you have. Click
the padlock button to unlock the cells in the report.
Page 6 of 7
a) In the left column of the table, add a text
item labelled "List Report".
b) In the right column of the table, add a text
item labelled "Crosstab Report".
c) Next, we will add a drill-through to each
label in the table. Save the report as
"tabbedreport".
15) Select the "List Report" label and choose the "Drill
throughs" button on the toolbar.
a) Set the "Report" to the "tabbedreport" you
just saved
b) Click the "pencil" icon to edit the parameters
in the drill through
c) Set "method" to "Pass data item value"
d) Set the "value" to "List Report1"
16) select the "Crosstab Report" label and choose the
"Drill throughs" button on the toolbar.
a) Set the "Report" to the "tabbedreport" you
just saved
b) Click the "pencil" icon to edit the parameters
in the drill through
c) Set "method" to "Pass data item value"
d) Set the "value" to "Crosstab Report1"
b) Set the Conditional Style(CRN)/Style
Variable(C8) property to "Report Name".
c) Select the "Table cell" containing "Crosstab
Report" tab text.
d) Set the Conditional Style(CRN) / Style
Variable(C8) property to "Report Name".
e) Go to the Conditional Explorer and select
the "List Report" under the "Report Name"
variable.
f) Select the "Table cell" containing the "List
Report" tab text and under the properties set
the "Background Color" to "lime".
g) Go to the Conditional Explorer and select
the "Crosstab Report" under the "Report
Name" variable.
h) Select the "Table cell" containing the
"Crosstab Report" tab text and under the
properties set the "Background Color" to
"Aqua".
21) Save the report and run it.
22) Clicking on the Crosstab Report tab will display the
crosstab report and clicking on the List Report tab will
display the List Report.
17) Select the Conditional Block and drag it into the
List Report defined in step 13, just below the Table.
Note: If the conditional block ends up to the right of
the table, you must undo the move and redo it making
sure it ends up below the table within the List report
object. (You should place the cursor in the bottom-right
of the table when dropping the conditional block into
the list. This will ensure it gets placed in the List report
just below the table.)
18) Select any item in the List report created in Step
13. Under the Ancestor tab choose the "List Column",
not "List Columns". Double-click the "Border" property
and set the following:
a) Style=None
b) Click the Button just below the "Preview"
text. Click Apply (This removes the border for
the List Column).
c) Click OK.
19) Select the "List Column Title" for the List report
created in Step 13 (it currently has a "Table" text item
listed init). Under the Ancestor tab choose the List. Set
the following in the Properties pane:
Column Titles=Hide
20) You will probably want to change the colour of the
tabs based on whether they're selected or not. To do
this:
a) Select the "Table cell" containing "List
Report" tab text.
Page 7 of 7