...

Using Dimensions in IBM Cognos Controller Guideline

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Using Dimensions in IBM Cognos Controller Guideline
Guideline
Using Dimensions in IBM Cognos
Controller
Product(s): Controller 8
Area of Interest: Financial Management
Using Dimensions in 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]
Using Dimensions in IBM Cognos Controller
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ................................................................................................................ 4
APPLICABILITY ......................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS ..................................................................................... 4
2
GENERAL CONSIDERATIONS AND SETUP..................................................... 4
2.1
2.2
2.3
2.4
2.5
2.6
2.7
OVERVIEW .............................................................................................................. 4
USAGE ................................................................................................................... 4
GENERAL CONFIGURATION .......................................................................................... 5
ACCOUNT STRUCTURES .............................................................................................. 6
FORM STRUCTURE ..................................................................................................... 7
DIMENSION SIZING ................................................................................................... 8
ADDING OR CHANGING THE LEVEL OF DIMENSIONS ............................................................ 8
3
CONSOLIDATION .......................................................................................... 9
3.1
COMPANY AND GROUP JOURNALS .................................................................................. 9
3.2
CURRENCY CONVERSION........................................................................................... 10
3.3
INTERCOMPANY CONTROL TABLES ............................................................................... 11
3.3.1 Intercompany Control Tables (without counter dimension) .................................... 11
3.3.2 Intercompany Control Tables (with counter dimension) ......................................... 12
EXAMPLE: ......................................................................................................................... 12
Using Dimensions in IBM Cognos Controller
4
1 Introduction
1.1
Purpose
This document provides guidelines to using dimensions in IBM Cognos
Controller.
1.2
Applicability
Controller 8 and higher.
1.3
Exclusions and Exceptions
There are no known exceptions and exclusions at the time this document was
created.
2 General considerations and setup
2.1
Overview
IBM Cognos Controller allows for the use of four main dimensions. These four
dimensions are referred to as Dim1, Dim2, Dim3 and Dim4. In order to use
dimensions they must first be enabled and named in the general
configuration. Each dimension is defined as a hierarchy. The dimensions are
tightly linked to the accounts as they split accounts into detailed information.
2.2
Usage
Dimensions serve several purposes. One such purpose is that they split
account information to provide additional details, for example splitting into
Business Units for segment reporting to give sales by region or by client.
In IBM Cognos Controller there are different ways to provide detail
information:
o Company Structure
o Movements
o Dimensions
Each of the options has advantages and disadvantages as listed here:
Example: Segment Reporting
Best practice = Dimensions
Company Structure
Dimensions
All information will be reported by
segment in the company
Not all information in a company
needs to be split into segments
Requires different company
structure to consolidate / group by
segments – require additional
consolidation run, additional group
journals or split of existing group
journals into the segment structure.
Only one consolidation runs for
segment reporting as well as
“normal” reporting. Journals cover
both views as they have to be split
by dimension while being entered.
Intercompany elimination as usual.
Only Dimension 1 can be used for
Using Dimensions in IBM Cognos Controller
5
User needs to enter segment
(company) as counter company. IC
reconciliation will be done on
segment level (lowest level of
company structure).
intra-segment IC elimination. User
needs to enter partner dimension
element of the partner company. IC
matching will happen by company
and by segment on one go.
New segments need to be entered
as companies for each legal
company separately.
New dimension elements can easily
be entered and made available to all
companies.
Consistency is less easy as it
requires a consolidation to view the
company view. But, the different
segment companies could be put on
one form with a total to check
consistency.
Consistency is easily checked, as the
sum of all dimension elements give
the company value. This is usually
visible in the same form.
If you want to be able to move the
divisions (departments) between
companies then the company
structure is probably better.
Company structure can be tracked
using a time stamp. Changes can
trigger changes in scope
calculations.
No time stamp on dimensions.
Both methods have their advantages and limitations; you have to best match
these with your requirements.
Example: P&L by functions
Best practice = movements
Movements
Dimensions
Each movement can be separately
defined for summations, currency
conversion and more.
All dimensions are treated as the
base accounts. Separate summation
of single elements to other totals is
not possible.
Movement accounts have to be
summed by function to get the total
on total accounts – quite a lot of
work.
2.3
General Configuration
Activate the dimensionality feature in your General Configuration.
Using Dimensions in IBM Cognos Controller
6
In the screen shot above Dim1-3 is activated for use in IBM Cognos
Controller. You can provide your own names. If you activate a new dimension
or level detail then the historical data will not be correct or even be loaded.
You should always backup your system prior to any changes and decide how
you want to handle the historical data from the previous period or you may
decide just to do a go forward. See section 2.7 of this document for more
information.
2.4
Account Structures
The account structures allow you to assign the dim1-4 and the appropriate
level of dimensionality. It is very important to understand the level of detail
available at each account level. Six levels is the maximum number of levels
available. It is possible to use different levels on different accounts. All
elements on the level chosen will be available for data entry on the respective
account. Summations are only possible from an account with lower level to
an account with a higher dimension level.
Using Dimensions in IBM Cognos Controller
7
You may want to review the level of detail that you are looking to bring into
IBM Controller to see if it makes sense for reporting.
You may not require six levels of details as part of your reporting requirement
and this information can be better loaded into another financial reporting
tool, like BI or TM1.
2.5
Form structure
During the definition of a form, one dimension level for each dimension needs
to be specified. This level has to reflect the dimension level set up in the
account structure for all of the accounts that will be part of this form.
On each form it is only possible to use one fix dimension level by dimension.
If you have data, for example, for sales by product and then the rest of the
P&L on product group level, you need to have two forms - one with the
detailed sales by product, running into another account sales by product line
which will then appear in the P&L form.
There is no other way – except using free forms. Otherwise the data cannot
be displayed correctly.
Using Dimensions in IBM Cognos Controller
8
2.6
Dimension Sizing
As discussed earlier IBM Cognos Controller allows you to have 4 main
dimensions (1-4) and each dimension level allows up to 6 dimension levels.
If more than 4 dimensions are needed, it is possible to use one of the
dimensions and take the first level to split the dimension into different
dimensions. It will not be possible – like with the regular 4 dimensions – to
combine these dimensions for analysis purposes.
To make the system more transparent, use linked structures to link accounts
and dimension elements.
However, when you are looking at using dimensions and decide to include
information that maximizes all the dimension levels the database includes
more information and there could be the following issues:
• Performance – it may take longer to run reports, especially those
using all the dimension levels. Remember IBM Controller is using
functions to retrieve information from the database and this could
take time to pull the correct data. Aggregation levels for dimensions
used in reports (report generator etc.) will cause the system to add at
runtime.
• Report Building – the more dimensions the more there is a possibility
for more complex reporting. You may decide that this level of detail
can be better handle outside Controller using IBM Cognos BI tools or
TM1.
• Data Import – The data file will be much larger and may take some
time to import into Controller
• Maintenance – The more dimensions, then the more maintenance will
be required around dimension. There are other options that can be
used to handle the dimensions, like Business Viewpoint. However, the
process of dimensionality is still handled by deciding on the level of
detail required for each general ledger account.
2.7
Adding or changing the level of Dimensions
When implementing IBM Cognos Controller it should be considered whether
or not dimensions should be used. If dimension should be used in the near
future but data is not available yet – maybe due to restrictions in the
underlying accounting systems – it is advisable to foresee dimensional split
on the respective accounts from the beginning and to only open one dummy
element for data entry. When the details are available each company can
then be opened for detailed data entry by dimension element. Otherwise a
migration would be necessary because it is not so easy to change an account
from being a non dimension split account to an account split by dimension.
From time to time there may be a business requirement to change the level
of dimensionality.
All information is stored on the dimension element code. As long as this code
remains as data entry element, there is no problem. The key is to remember
where the data is, and always keep it there. For example if the data is on
item 0502 and that remains the same - you can add a level above it without
any harm.
Using Dimensions in IBM Cognos Controller
9
Make sure to not change original data entry elements to be summation
element or vice versa. Make sure to use change tables for changes in codes
to correctly migrate data from one element to another.
Also remember to take care when: updating all the accounts with the correct
level information; and, updating all the forms with the correct level
information.
The system does not track what level the data entry occurred at, but rather
the actual items that the data entry occurred with. The suggestion is to use
single mode until all changes are complete.
You may also want to backup your database prior to making any changes, so
if there are issues, then at least you can restore the backup.
You should also keep in mind that dimensions are really tied up with
accounts. And if you later discontinue using dimensions then you have to
keep that in mind if you are making changes in your account structure. The
account structure gives you a warning when you start or stop using
dimensions on an old account, but if you decide to change or eliminate the
dimensionality, and has prior history with dimensionality, then it will be lost.
You may want to create a new database to migrate the financial information
to the new structure.
3 Consolidation
3.1
Company and Group Journals
When creating journals it is mandatory to assigned dimension codes to
accounts that required them. You will not be able to save the journal unless
you assigned the correct dimension code.
Using Dimensions in IBM Cognos Controller
3.2
10
Currency Conversion
You need to aware of how your accounts and currency translation are
configured as part of using dimensionality.
This example shows the configuration of net income for both the balance
sheet and the income statement. It is important to understand if you have
profit and loss integrated with the net income or if it is a separate account. If
you are using dimensionality on your profit and loss statement then it is
important to understand if dimensionality is on your balance sheet to ensure
that you are matching the accounts at the proper level. You could run into
issues when currency conversion is run, that translated net income on the
income statement may not equal the converted net income on the balance
sheet. The usual cause that you should look at is the level of dimensions on
the accounts. You may need to adjust dimension level on the net income
accounts.
Using Dimensions in IBM Cognos Controller
3.3
3.3.1
11
Intercompany Control Tables
Intercompany Control Tables (without counter dimension)
By default when not using counter dimension, the IC elimination looks at the
dimension elements of the two partners. When they are equal, the difference
is posted on the original dimension element at the partner who should
receive the difference. If the two dimension elements are not equal, there will
be a difference which is posted at the dimension element of the partner who
receives the difference according to the IC rule. This might result in many
differences booked on difference accounts. In this case it would be an option
to book all differences on one dedicated dimension element which is entered
in the difference definition area of the IC definition screen.
Using Dimensions in IBM Cognos Controller
3.3.2
12
Intercompany Control Tables (with counter dimension)
Example:
Acc.
Comp.
Segment
EUR
Acc.
Partner
Partnersegment
Loc.
Curr.
EUR
413200
GDM
CSPA
1,000,000
511300
GDGB
OPER
781,250
1,000,000
413200
GDM
BNPO
6,000,000
511300
LOMO
BNPO
-
6,000,000
Case 1:
In this case segment and partner segment are different. From the point of
view of the segment there should be no elimination.
Case 2:
Segment and partner segment are the same. From the point of view of the
segment there needs to be an elimination.
Using Dimensions in IBM Cognos Controller
13
During the Intercompany setup, select the Use Counter Dimension and
Use Adjustment Dimension check boxes.
Using Dimensions in IBM Cognos Controller
14
When the dimension elements are equal for both partners, the elimination
is done for each of the partners on the dimension element.
When the dimension elements are not equal for both partners, the values
remain on the dimension elements and the elimination takes place on the
adjustment dimension element which has to be defined in the dimension
definition on each level of the hierarchy:
Fly UP