...

Publish Data and Structures

by user

on
Category: Documents
2

views

Report

Comments

Transcript

Publish Data and Structures
Guideline
Publish Data and Structures
Product(s): IBM Cognos Controller 8.1 and IBM
Cognos Controller 8.2
Area of Interest: Modeling
Publish Data and Structures
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. 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. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Confidential Information
Publish Data and Structures
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE .............................................................................................................. 4
APPLICABILITY ....................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS .................................................................................... 4
2
DEFINE DATA MART ...................................................................................... 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
DEFINITION .......................................................................................................... 4
ADDITIONAL TARGET ............................................................................................... 4
TYPE ................................................................................................................... 5
COMPANY ............................................................................................................. 5
ACCOUNT ............................................................................................................. 6
PERIOD ................................................................................................................ 7
ACTUALITY ............................................................................................................ 7
CURRENCY ............................................................................................................ 7
CLOSING VERSION .................................................................................................. 7
CONTRIBUTION VERSION .......................................................................................... 8
EXTENDED DIMENSION 1-4 ....................................................................................... 8
EXPORT OPTION ..................................................................................................... 8
3
DEFINE DATA MART - DEMO ....................................................................... 10
3.1
DEMO ................................................................................................................ 10
4
PUBLISH DATA MART - DEMO..................................................................... 11
4.1
DEMO ................................................................................................................ 11
5
OTHER DOCUMENTS ................................................................................... 12
IBM Cognos Confidential Information
Publish Data and Structures
4
1 Introduction
1.1
Purpose
The goal of this document is to help with setting up of the data mart and
using the published feature within the IBM Cognos Controller application.
1.2
Applicability
The document applies to IBM Cognos Controller versions starting with 8.2
and higher
1.3
Exclusions and Exceptions
This document provides an overview of the data publishment and should be
used with the MFR blueprint, which provides a more details around installing
and configuring the publishment.
2 Define Data Mart
2.1
Definition
The Define Data Mart is where you define the data mart, and a Template has
to be defined. The Template contains the information about what to include
in the data mart, which accounts, forms, companies, actualities, etc. You can
create one Template that contains all the data that you might need, or you
can define multiple Templates that contain different data, e.g. forms.
2.2
Additional Target
The default functionality is that controller will extract data from the Controller
database, transform the data, and then store it in the data mart. Additional
Target enables you to add additional ‘steps’ to the process. Additional Target
MS SSAS will not only publish data to the data mart, it will also create and
process a MS SSAS olap cube. Additional Target IBM Cognos BI is a new
additional target in IBM Cognos Controller 8.2. IBM Cognos BI is optimized
for IBM Cognos BI and you now publish the data mart to a dedicated
reporting database/server. “Optimized for IBM Cognos BI” means that the
structures are stored in a way that IBM Cognos BI wants them to be stored,
e.g. hierarchies (company, ext dim1-4) are “expanded” which means that the
number of columns in the structure table is dependent on levels in the
hierarchy. This means that you can create a report by dragging e.g.
continent, country, city, and store into the report.
The account dimension is not expanded by default, you have to run an
additional step to do this, and this is included in the MFR83 Blueprint.
No additional target and MS SSAS will store the data mart in the actual
Controller database (xocube….tables) and is the same functionality as in IBM
Cognos Controller 8.1. This means that a customer that has implemented the
MFR81 version can upgrade to IBM Cognos Controller 8.2/8.3 and still use
reports created in BI.
IBM Cognos Confidential Information
Publish Data and Structures
2.3
5
Type
IBM Cognos Controller contains a lot of information, and some of the
information is rather complex and not applicable for the end user. Type
Default doesn’t contain all this complex data, and creates a date mart that is
easier to use. Type default doesn’t contain the Contribution Version
dimension and you can’t include intercompany details. Type Statutory
creates a data mart with all the data, e.g. Contribution Version, in the data
base.
The recommendation is to use Type Statutory and then filter the report to
exclude data that is not required in the specific report.
2.4
Company
You can publish specific companies or groups. Company is used if you want
to publish data for a specific company, or a small number of companies.
Group is used when you want to publish all companies in a specific group.
‘Include Subgroups’ will give you the entire structure, not only the level.
Selecting Group means that you also have to specify the Consolidation Type
and Structure Version. –Note- that only one Consolidation Type per template
is supported. This means that you can’t include groups and companies from
different consolidation types in one Template. A workaround is to create one
template per consolidation type and then ‘merge them in the report.
The same is applicable for Structure Version, i.e. only one version of the
company hierarchy is supported. This means that disconnected companies
will not be included in the hierarchy, and you can’t run a report for these
companies. Furthermore; data for these disconnected companies will still
exist in the fact table if you publish data using the Incremental update.
One thing to be aware of is how values are aggregated in the Company
hierarchy. Values will be published on the lowest level, i.e. lowest subsidiary,
and then be aggregated to create the total on group level. This means that
the aggregated total on the group might not be consolidated group total in
IBM Cognos Controller. This situation will occur if you publish data to the data
mart before the data is consolidated. It can also occur if you disconnect
companies from the hierarchy and run a report for a period back in time.
Publishing data on the lowest levels also means that data stored on the group
itself will not be included in the data mart, e.g. journals. This is another
reason why the value on a group in a BI report might be different compared
to a report in IBM Cognos Controller.
Above is applicable when you select subgroups. If you only publish the group
and sublevel, IBM Cognos Controller will then publish the consolidated group
total for groups on the sublevel.
IBM Cognos Confidential Information
Publish Data and Structures
2.5
6
Account
You can publish specific Accounts or Forms. Account is used when you
just want to publish a small number of accounts. Selecting a summation
account means that IBM Cognos Controller will ‘expand’ the accounts and
include all accounts that sums up to this account. Form publishes all
accounts and their details that are included in the selected form. All accounts
are stored on the same level and they will be ordered in the correct sort
order.
This is an example of when account is selected:
IBM Cognos Confidential Information
Publish Data and Structures
7
All the details on the lowest levels to selected accounts are included, i.e. no
summation accounts are included.
This is the way the account dimension looks like when you select a Form:
Al the accounts included in selected Form will be included. All the accounts in
the form are displayed in the correct sort order, and you can drill to see the
details to a summation account.
Using Account or Form is very much dependent on which report to create,
but Form makes it easier to create financial report as it gives you all the
accounts you need in the correct sort order. You don’t have to ‘cherry pick’
the accounts you need.
2.6
Period
You can define how you want the period dimension to be displayed.
2.7
Actuality
Actualities that you want to include.
2.8
Currency
Currencies that you want to include. There is no option to publish ‘Local
Currency’, but that can rather easily solved in the actual report, i.e. include a
prompt in which you can select ‘Local Currency’.
2.9
Closing Version
Closing Versions that you want to include. ‘Include Details’ will also include
Journal Types.
IBM Cognos Confidential Information
Publish Data and Structures
8
2.10 Contribution Version
Contribution Versions that you want to include. ‘Include details’ will also
include Automatic Journal Types. Note that Contribution Version will not
be applicable when selecting Type default.
2.11 Extended Dimension 1-4
The entire dimension structure will by default be published, but you can
select specific e.g. products that you want to publish. Overriding the default
setting means that you will only publish accounts that are defined to require
e.g. a product; all other accounts will be excluded.
The recommendation is to use the default setting, i.e. publish the entire
structure.
2.12 Export Option
Full Export means that structures and data for the current Template will be
truncated before new data is inserted. Incremental Update means that
new data will be added to existing data, only the current period will be
truncated.
Full Export should be used when you want to keep the data mart in sync
with the controller database, i.e. all structural changes are included in the
data mart. This option means that you have to publish all periods every time,
something that might have a negative impact on performance.
Incremental Update should be used when you want to update the data
mart with just one period, or a small number of periods. This option means
that periods back in time might use a different e.g. company structure than
the new period(s).
One thing to have in mind is that if you switch between Full Export and
Incremental Update (or reverse), means that the data mart will be truncated
for the current Template E.g. Publish data to the data mart using Full Export,
change Export option to incremental Update and publish data again. What
will happen is that the data mart current template will be truncated before
new data is inserted.
The data mart will also be truncated if you use Incremental Update and
change which Structure Version to use E.g.: publish data for a period using
incremental update change, change structure version and publish data for a
new period. This data mart will now only contain data for the new period, the
old data is lost.
IBM Cognos Confidential Information
Publish Data and Structures
9
Full Export or Incremental Update is very much dependent on the size of
database and reporting requirements. A recommendation is to use Full Export
as long as it doesn’t have a negative impact on performance, and select
which export option to use already when creating the Template.
A general recommendation on how to define the template is only include data
and structures that will be used. Publishing the entire Controller database to
the data mart will have negative impact on Controller itself, but also when
running a report in BI.
IBM Cognos Confidential Information
Publish Data and Structures
3 Define Data Mart - Demo
3.1
Demo
These are the steps to create a new template:
1. Launch IBM Cognos Controller
2. Open Transfer/Publish Data and Structures/Define Data Mart….
3. Click New to define a new Template
4. Specify a code and name for the Template
5. Select Cognos BI as Additional Target
6. Define which companies, forms, actualities, currencies, etc to include
7. Click Save
IBM Cognos Confidential Information
10
Publish Data and Structures
11
4 Publish Data Mart - Demo
4.1
Demo
This is how to publish to the data mart
1. Open Transfer/Publish Data and Structures/Publish Data Mart….
2. Tick the Generate checkbox for the template you want to publish data
for.
3. Select which periods to publish data for.
4. Click Run, a batch job will now been added to the Controller batch queue
and you can monitor the process from Maintain/Batch
Queue/Manage…
IBM Cognos Confidential Information
Publish Data and Structures
5 Other Documents
1. MFR 82 Blueprint
2. MFR 81 Blueprint
IBM Cognos Confidential Information
12
Fly UP