...

Guidelines to using IBM Cognos Business Viewpoint with IBM Cognos Controller

by user

on
Category: Documents
3

views

Report

Comments

Transcript

Guidelines to using IBM Cognos Business Viewpoint with IBM Cognos Controller
Guideline
Guidelines to using IBM Cognos
Business Viewpoint with IBM
Cognos Controller
Product(s): IBM Business Viewpoint and IBM
Cognos Controller
Area of Interest: Financial Management
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2
Copyright and Trademarks
Licensed Materials - Property of IBM.
© Copyright IBM Corp. 2009
IBM, the IBM logo, and Cognos are trademarks or registered trademarks of
International Business Machines Corp., registered in many jurisdictions worldwide.
Other product and service names might be trademarks of IBM or other companies. A
current list of IBM trademarks is available on the Web at
http://www.ibm.com/legal/copytrade.shtml
While every attempt has been made to ensure that the information in this document
is accurate and complete, some typographical errors or technical inaccuracies may
exist. IBM does not accept responsibility for any kind of loss resulting from the use of
information contained in this document. The information contained in this document
is subject to change without notice.
This document is maintained by the Best Practices, Product and Technology team.
You can send comments, suggestions, and additions to [email protected]
Microsoft SQL is the property of Microsoft and its related companies.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
IBM COGNOS BUSINESS VIEWPOINT AND CONTROLLER............................ 4
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
2.10
2.11
2.12
2.13
2.14
IBM BUSINESS VIEWPOINT ......................................................................................4
LIMITATIONS WITH IBM COGNOS CONTROLLER..............................................................5
IBM BUSINESS VIEWPOINT ADAPTER ..........................................................................6
IBM BUSINESS VIEWPOINT AND IBM COGNOS CONTROLLER DATA SOURCE ............................6
GENERATE CMD FILE TO LAUNCH BVC CONTROLLER ADAPTER.......................................... 10
CONFIGURING THE IBM COGNOS CONTROLLER IMPORT SPECIFICATION............................... 12
DEFINING THE IBM COGNOS CONTROLLER IMPORT SPECIFICATION .................................... 13
IBM COGNOS BUSINESS VIEWPOINT CONTROLLER ADAPTER OPERATIONS............................ 14
IBM BUSINESS VIEWPOINT CONTROLLER ADAPTER NOMINATION PROCESS........................... 15
IBM BUSINESS VIEWPOINT CONTROLLER ADAPTER SUBSCRIPTION PROCESS ......................... 17
IBM BUSINESS VIEWPOINT CONTROLLER ADAPTER UPDATING COMPONENT PROCESS .............. 24
ADDITIONAL STEPS REQUIRED TO UPDATE IBM BUSINESS VIEWPOINT CONTROLLER ADAPTER .... 32
SPECIAL FEATURE OF BV CONTROLLER ADAPTER .......................................................... 36
OTHER SOURCES................................................................................................. 38
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
4
1 Introduction
1.1
Purpose
This document discusses using IBM Cognos Business Viewpoint with IBM
Cognos Controller.
1.2
Applicability
IBM Cognos Controller and IBM Business Viewpoint
1.3
Exclusions and Exceptions
There are no known exceptions and exclusions at the time this document was
written.
2 IBM Cognos Business Viewpoint and Controller
2.1
IBM Business Viewpoint
IBM Cognos 8 Business Viewpoint enables business users, such as business
and financial analysts, to maintain their business definitions of the dimensions
they use to drive performance whether they are creating new dimensions, or
adding to existing structures.
The business user gains access to the data domains they require to drive
performance and are now empowered with a business driven user interface
to quickly respond to changes as they happen in domains such as chart of
accounts, cost center changes, product etc. They save time typically spent
on manually reconciling reports, plans, forecasts and budgets.
Replace manual, error-prone efforts with fully transparent,
automated and auditable processes
• Collaborate through multi-user contribution and multi-step, multiuser workflow
• Audit who makes changes and what changes are made
• Apply version control with ability to roll back
• Assign security for dimensions, users and user roles
• Review lineage of maintenance history back to the source
• Retain all work in central dimension store
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2.2
5
Limitations with IBM Cognos Controller
The current version of IBM Cognos Controller 8.4 limits the number of
structures that can be imported in the application.
•
•
•
•
Company Structures
Account Structures
Dim1-4 Structures
Currency Rates
IBM Cognos Controller Best Practices
Company and Account structures are recommended to be exported
from IBM Cognos Controller. The company structure has complex
attributes such as ownership percentage, linking between group
companies, etc. It is due to this complexity that it is a best practice to
pull the ownership structures into IBM Business Viewpoint so they can
be utilized for other applications. While it is possible to import
companies back into IBM Cognos Controller, there are some
limitations and it is not a recommended best practice.
Extended Dimensions 1-4 and can be either imported or exported
from IBM Cognos Controller into IBM Business Viewpoint. However,
you need to consider when making changes to level of dimension
structures that you may have issues with other structures and
historical data, i.e. going from 1 level deep on an account to 2-level
deep. You should not change a child member to a parent member as
this would have design implications. In general, changing the
hierarchy and or adding additional child members is acceptable.
Currency Rates can be either imported to or exported from IBM
Cognos Controller. It is recommended that you import these rates
from IBM Business Viewpoint into IBM Cognos Controller. Importing
will enable you to have a central spot for the all currency rates that
can be shared by other applications.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
IBM Business Viewpoint
IBM Business Viewpoint allows you to export country and currency
codes from IBM Cognos Controller. However, you are not able to
import these structures into IBM Cognos Controller due to the fact
these structures are not available for importing. You would still be
required to manually maintain these codes between the two
applications, if you make any changes or add currency codes. IBM
Cognos Controller contains most currency and country codes
predefined so it could be used as a suitable source.
2.3
IBM Business Viewpoint Adapter
The adapters are a communication mechanism with a UI that facilitates the
conversations between an IBM Business Viewpoint repository and IBM
Cognos Controller.
2.4
IBM Business Viewpoint and IBM Cognos Controller Adapter
Configuration
IBM Business Viewpoint requires a configuration of IBM Cognos Controller
Adapter and the data source with IBM Cognos Controller
IBM Cognos Controller only supports Oracle and Microsoft SQL Server.
To set up a new data source follow these steps:
1. Select IBM Cognos 8 > IBM Cognos 8 Business Viewpoint
Client > IBM Cognos Configuration.
6
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2. Right click Business Viewpoint Client Adapter and select New
resource > Adapter.
3. Create a new resource adapter and select the Type as Controller.
7
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
8
4. Enter values in the Enabled?, Adapter ID and the Controller user
name fields.
5. Create a new database resource adapter.
The Adapter ID: Any numeric number; should be unique. Controller
User Name : ADM (or the administrator of the IBM Cognos Controller
Application). You also need to select the database type. IBM Cognos
Controller only supports Oracle and Microsoft SQL Server.
6. Configure the database server host and port and enter the database
name.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
7. Enter database username and password value.
9
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2.5
10
Generate CMD file to launch BVC Controller Adapter
IBM Business Viewpoint requires a create a series of .BAT files as part of
installing the BVC Controller Adapter.
•
Go to the <BVC installed directory >\BusinessViewpointClient\bin folder
and double-click the file generateFiles.cmd.
– The utility will set up all the adapters that you configured. You can
configure many different adapters and generate all the .CMD files
at the same time.
•
Test the configuration by double-clicking the file named
ViewpointClient_adapter_name.cmd in the BusinessViewpointClient\bin
folder where adapter_name is the name you gave the adapter.
•
For example, if you named the adapter Controller_Adapter in IBM Cognos
Configuration, you double-click the
ViewpointClient_Controller_Adapter.cmd file.
There are two ways to launch the BVC Controller Adapter
•
Run Controller adapter from
<BVC installed directory>\ cognos\BusinessViewpointClient\bin\
Viewpoint_Client_Controller_Adapter.cmd
•
Run Controller Adapter from Controller
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
11
Configure and Launch the Controller Adapter from Controller
The following steps will guide you to configuring the IBM Cognos
Adapter:
• Launch IBM Cognos Controller
• Select Maintain\User\Personal Defaults\Integration
• Enable IBM Cognos 8 Business Viewpoint Integration and
select the file location of the
ViewpointClient_Controller.cmd.
• You will then be require to log off and log on for the
change to be registered by IBM Cognos Controller.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
2.6
View of the BVC Controller adapter
Configuring the IBM Cognos Controller Import Specification
The import specification needs to be defined in order for the import
specification to communicate with IBM Cognos Business Viewpoint. The
import specification allows you to import structures, such as:
•
•
•
•
•
•
•
Account Structure
Company Structure
Extended Dimension
Extended Dimension
Extended Dimension
Extended Dimension
Currency rates
1
2
3
4
12
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2.7
13
Defining the IBM Cognos Controller Import Specification
In Controller, click Menu Transfer > External Structures > Define
Import Structure Specification.
• Define the structure & specification.
– Select Account Structure and select the default specification
#ST_ACCSTRUC
– Click Save As and save this structure with the name
BV_ACCSTRUC. Repeat the steps above for Company Structure,
Extended Dimensions 1- 4, and Currency Conversion Rates saving
the default specifications #ST_xxxxx using the following names
• ACCOUNT_STRUCTUR ◊ BV_ACCSTRUC
• COMPANY_STRUCTUR ◊ BV_COMPSTRUC
• EXT_DIM1_STRUCTUR ◊ BV_EXTDIM1
•
•
•
•
EXT_DIM2_STRUCTUR ◊BV_EXTDIM2
EXT_DIM3_STRUCTUR ◊ BV_EXTDIM3
EXT_DIM4_STRUCTUR ◊ BV_EXTDIM4
• CURRENCY_CONV_RATES ◊ BV_CURRRATES
Look at the following account structure import specification
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2.8
14
IBM Cognos Business Viewpoint Controller Adapter Operations
The BVC Adapter performs the following operations
• Nomination:
– Select the structure and move the structure from Controller
to IBM Cognos Business Viewpoint Studio
• Subscription:
– Take the structure from IBM Cognos Business Viewpoint
and make the entry in to the staging table so that it will
now be IBM Cognos Controller’s responsibility to import
this structure.
• Update Components:
– Make some change in to the available structure in Cognos
Business Viewpoint Studio and call the update component.
This will again update the entry in the staging table making
it Controller’s responsibility to import this updated
structure.
– Synchronize the Cognos Business Viewpoint Studio and
IBM Cognos Controller
• Update IBM COGNOS BUSINESS VIEWPOINT STUDIO:
– Modify or update some structure in Controller and call
Update IBM Cognos Business Viewpoint Studio will do
changes in IBM Cognos Business Viewpoint Studio available
structure, so the Controller and IBM Cognos Business
Viewpoint Studio will be in synch.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
2.9
15
IBM Business Viewpoint Controller Adapter Nomination Process
IBM Cognos Business Viewpoint Studio helps organizations better manage
their dimensional data. After the dimension has been modelled, it is sent to
you so that you can nominate data based on your knowledge of your
business domain. The following graphic provides an overview of the workflow
used in Business Viewpoint Studio
Example #1 Extended Dim1 in Controller:
– Created 3 Extended Dim 1 structures in Controller as follows
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
1. Launch the Business Viewpoint Client Controller Adapter
2. Click the Nomination button. Nomination Wizard shows the list of
the available structures.
3. Select Extended Dim1 and click Next.
4. Select Extended Dim1 as hierarchy and then click Finish.
16
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
17
Extended Dim1 in IBM COGNOS BUSINESS VIEWPOINT STUDIO as
Hierarchy
2.10 IBM Business Viewpoint Controller Adapter Subscription Process
To get a copy of an entry in the IBM Cognos 8 Business Viewpoint Studio
master repository for use in IBM Cognos 8 Controller, create a link between
the two sources using a subscription. This tracks changes between the
master dimensions in Business Viewpoint Studio and the copy in Controller.
Using the subscription wizard, select the version containing the desired
dimension, and the specific data (list, hierarchy, and set) in that dimension to
use in Controller. You can choose to use an alternate name or a translation of
the member name by using an alias instead of the values in the Name
attribute. For example, an alias is defined in Business Viewpoint Studio to
include a different name or to use French instead of English. When you
subscribe to that item in IBM Cognos 8 Business Viewpoint Client, you can
choose the French alias for the item.
Note: You can create individual subscriptions for as many entries as you
require.
Example of making changes in Business Viewpoint and then
replicating this process change in IBM Cognos Controller
• Create Extended Dim2 in IBM COGNOS BUSINESS VIEWPOINT STUDIO
as Hierarchy
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
1. Launch IBM Cognos Business Viewpoint Client Controller Adapter
2. Click the Subscription button
3. Subscription wizard shows the list of the available structures in IBM
Cognos Business Viewpoint Studio.
4. Select Extended Dim2 and click Next.
18
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
5. Select Extended Dim 2.
6. Click Finish.
19
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
20
To view the data in Controller we need to take the data from staging table. Open the
Controller database and look at the staging tables.
Open xstagedim2. The staging table ID is 2.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
•
•
•
•
•
•
•
21
Note: While importing the structure the user should be in single mode
(Maintain > User > Single Mode).
Open Controller and click Transfer > External Structure > Import
Select Structure as Extended Dim2.
Select Import Specification as BV_EXTDIM2 (Created in Step 2.7)
Select the staging table ID (transfer data from staging table to
Controller).
Here select the Job Name as 2
Click Run.
After execution it shows the report
See the screenshots below.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
Log Reports tell you what information has been loaded and if there are
any errors.
22
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
23
You can review the dimension2 upload by selecting Maintain >
Dimension 2. This is a good step to ensure the load worked properly. It
is recommended that you validate each load to ensure that the
information is correct. There is additional documentation on the Proven
Practice site that discusses the best practices around maintaining
dimensions.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
24
2.11 IBM Cognos Business Viewpoint Controller Adapter Updating
Component Process
Make a change in the available structure in IBM Cognos Business Viewpoint
Studio and use the update component. This will update the entry in the
staging table and it is IBM Cognos Controller’s responsibility to import this
updated structure. These processes will synchronize IBM Cognos Business
Viewpoint Studio and IBM Cognos Controller.
Example #1
• In IBM Cognos Business Viewpoint Studio modify the Structure
Extended Dim 1
• Remove the parent of Extended Dim 1(4545) and change the level
value from 3 to 1.
• Create the new version of the dimension.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
25
In Controller Adapter click Refresh.
Look at the flag of Extended Dim 1, it has been changed and shows
one structure has been updated.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
•
Select Extended Dim1
Click the Update Data button
Click Update Component
•
•
Select the second version for updating
Click Finish
26
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
27
To view the updated Extended Dim1 then you will need to take
the data from staging tables to import to IBM Cognos Controller .
•
•
•
•
•
Note: While importing the structure the user should be in
single mode (Maintain>User>Single Mode).
Open Controller, Go to Menu Transfer > External
Structure > Import
Select Structure as Extended Dim1.
Select import specification as BV_EXTDIM1
Click the Run button
After execution it shows the report
See the screenshots below
•
Importing External Structure in IBM Cognos Controller
•
•
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
Import external structure log report
28
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
29
•
In Controller open Extended Dim1 and now 4545 is no longer
the child of 9898
•
In Controller open Extended Dim2 and create a new Extended
Dim 2 (2250 DIM22) assign parent as DIM2.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
•
Select Extended Dim2
Click the Update Data button
Click Update Business Viewpoint Studio
•
Click on Finish which will show the success message.
30
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
•
Open IBM Cognos Business Viewpoint Studio
Click the Refresh button
Look at the Extended Dim2 with updated values.
31
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
32
2.12 Additional Steps required to update IBM Business Viewpoint
Controller Adapter
Note: For Subscription & Update Component
• After subscribe\ update component from Business Viewpoint Controller,
here are the extra steps to import the structure in Controller.
• Steps:
• Open Controller
• Click Maintain > User > Single Mode
• Click Transfer > External Structure > Import
• Select the Structure
• Select Import Specification which is created in
configuration steps
• Select the import Job
• Click Run
•
Business Viewpoint Controller Adapter supports the renaming of the
following controller structures.
– Extended Dim1
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
–
–
–
•
•
33
Extended Dim2
Extended Dim3
Extended Dim4
• Open IBM Cognos Controller
• Go to menu Maintain > Configuration
>General> General 2
• Rename from Extended Dim1 to Group Dim 1
• Extended Dim2
• Rename from Extended Dim1 to Group Dim 3
• Extended Dim4
Open IBM Cognos Controller .
Go to the Maintain menu to see the renamed structure Group Dim 1.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
•
34
Launch Business Viewpoint Client_Controller_Adapter.cmd
Click the Nomination button
Nomination Wizard shows the list of the available structures.
• Here Extended Dim1 renamed to Group Dim 1 and Extended Dim3
renamed to Group Dim 3
• Select Group Dim 1 as hierarchy and then click Finish.
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
Extended Dim1 renamed as Group Dim 1
35
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
36
2.13 Special Feature of BV Controller Adapter
IBM Business Viewpoint Controller adapter supports all following operation for
the renamed structures.
– Nomination
– Subscription
– Update Component
– Update IBM COGNOS BUSINESS VIEWPOINT STUDIO
BV Controller Adapter supports the renaming of the following IBM Cognos
Structures
– Extended Dim1
– Extended Dim2
– Extended Dim3
– Extended Dim4
Open
–
–
–
–
–
IBM Cognos Controller
Go to menu > Configuration > General > General 2
Rename from Extended Dim1 to Group Dim1
Rename Extended Dim 2 to Extended Dim 2
Rename Extended Dim 3 to Group Dim 3
Rename Extended Dim 4 t Extended Dim 4
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
-NB- You must log off and log on to be able to work with the new
settings.
37
Guidelines to using IBM Cognos Business Viewpoint with IBM
Cognos Controller
•
•
38
Launch IBM Cognos Business Viewpoint Adapter
Click the nomination button (nomination button) – the nomination
wizard shows the list of available structures – Here you will see
the dimension structures have been renamed
2.14 Other Sources
There are several Proven Practice documents on IBM Cognos Controller
application. These documents should be reviewed as part of using IBM
Cognos Business Viewpoint with IBM Cognos Controller
• Importing Structures using Staging Tables and Microsoft SQL
Server.zip
• Trouble Shooting to importing structures using Staging Tables –
Controller 8x.doc
• Best Practices to using dimension in IBM Cognos Controller.doc
Fly UP