BI – Migration from BusinessObjectsto Cognos

Umesh Ashok
Tata Consultancy Services
Mumbai

/ Page 1 of 7

ABSTRACT

This White paper describes the POC Experience of migrating reports from Business Object XI R2 to Cognos BI 8.3

It highlights the challenges, solutions and best practices implemented for seamless transition

TARGET AUDIENCE

Cognos Architect and Developers of BO to Cognos Migration project

PRE REQUISTIC

Knowledge of Cognos BI 8.3, BusinessObjectsXI R2 andRDBMS Databases.

1.INTRODUCTION

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.ExistingBO 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.

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 of functional and technical specification
  • Inadequate Security

3.ACTIVITIESCOMPONENTS

3.1Activities

  • 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
  • Migrating Metadata
  • Building reportsin 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

3.2Required Components

Table 1: Components

S.No. / Component
Name / Type
1. / Cognos 8.3 / Application
2. / BusinessObjects
XI R2 / Application
3. / Oracle 10g / RDBMS (OLAP Database)
4. / LDAP / Authenticationtool

4.ARCHITECTURE

4.1Current – Architecture(BusinessObjects)

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 fileshyperlinks
  • Object packages (which consist of report and/or program objects)

4.2Proposed- Architecture(Cognos8 BI)

The Cognos 3 tier architecture is described below

5.Metadata of Business Objectsand Cognos BI

The followingpoints outline the metadata of Business Objects and Cognos 8 BI

  • In Business Objects, Universeis 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.

5.1Security 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 datathat 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 theobjector keeping it hidden from all the users

  • Package Security

Security to a package can be setafter identifying who should and who should not have access to the package

5.2Reporting Tools available in Cognos 8 BI

  • Query Studio

It is used by report users for creating self-service business intelligence reports

  • Report Studio

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 howto 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.
  1. 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

  1. Click ‘Next’
  1. Click ‘Next’
  1. Click ‘Next’
  1. Select the objects you want to import and Click ‘Next’

  1. Following screen shot below shows the metadata of DB2 cube

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)
  1. Value of parameters used in Stored Procedureto be passed using macros from framework manager, same macro variable name to be defined in the reports
  1. Creating same filters and calculations in framework manager model which are used across different reports
  1. Creating tabbed report in Cognos (explained in detail in Appendex-1)

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®, enterprise search engine, Microsoft Office applicationor 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.

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

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

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 the Conditional Explorer and click on Variables.

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.

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"

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.
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.

/ Page 1 of 7