...

IBM Cognos 8 Planning Contributor - Conditional Data Transfer

by user

on
Category: Documents
1

views

Report

Comments

Transcript

IBM Cognos 8 Planning Contributor - Conditional Data Transfer
Tip or Technique
IBM Cognos 8 Planning
Contributor - Conditional Data
Transfer
Product(s): IBM Cognos Planning 7.3 & 8.1
Area of Interest: Modeling
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
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 Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ................................................................................................................ 4
APPLICABILITY ......................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS ..................................................................................... 4
2
DATA TRANSFER PROCESS ........................................................................... 5
3
CONSTRAINTS AND ERROR HANDLING ....................................................... 7
4
ENHANCEMENTS ........................................................................................... 8
5
APPLICATION DETAILS................................................................................. 9
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
4
1 Introduction
1.1
Purpose
This model demonstrates a technique that enables data from one Contributor
model (the Source) to be selectively transferred to another model (the
Target), depending on the workflow state of the eList items in the source
model. This demonstrator model only transfers data for eList items that are
locked, but it would be simple to allow the flexibility for an administrator to
control the workflow states for which source data are transferred.
This technique uses an intermediate Contributor model (the Transfer model)
with a dynamic eList to filter the data that is transferred. It is necessary
because allocation tables and d-cube allocations are not currently available
for use in Administration Links.
For source and target models that are structurally identical, and where the
requirement is always to transfer data from locked eList nodes, a simpler
method would be to use the Publish - View Layout – Advanced macro in
Contributor to generate a text file from the Source application. This would
then be imported into the target application, and committed via a GTP.
.
1.2
Applicability
IBM Cognos Planning versions 7.3 and 8.1.
1.3
Exclusions and Exceptions
Note that this document and supporting demonstrator model is provided as a
sample; it has not been formally tested, nor will it be supported by IBM. IBM
provides no undertaking that the techniques described in this document will
function in future product releases.
.
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
5
2 Data Transfer Process
The Manager report shown above can be found in the Common library.
The core processes in the model are simple; data are transferred from Source to
Transfer and from Transfer to Target applications by means of 2 Admin Links. The
Admin Links use matched descriptions for the eLists in each case. The filtering is
achieved by updating of the Transfer model eList to ensure that it contains only those
items for which you want to transfer data.
Note that for best performance, the intermediate Transfer model only needs to
contain the detail items for the dimensions of the d-cube(s) whose data are being
transferred.
The updating of the Transfer model eList is achieved as follows:
i.
ii.
iii.
The Source application contains a dummy d-cube that always contains
the number 1.
This d-cube is published (using the View publish). Only planner nodes are
published.
A SQL string is used to return the eList Item Names for those nodes in the
Source whose workflow state ID is 4 (i.e. locked). The SQL is as follows:
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
SELECT
FROM
6
cond_data_trans_source_view.dbo.it_2_source_elist.itemname
AS dimension_2_source,
cond_data_trans_source.dbo.nodestate.stateid
AS workflow,
cond_data_trans_source_view.dbo.it_2_source_elist.dispord
er AS disporder
cond_data_trans_source_view.dbo.it_2_source_elist INNER JOIN
cond_data_trans_source.dbo.nodestate ON
cond_data_trans_source_view.dbo.it_2_source_elist.itemid =
cond_data_trans_source.dbo.nodestate.nodeguid INNER JOIN
cond_data_trans_source_view.dbo.et_dummy ON
cond_data_trans_source_view.dbo.it_2_source_elist.itemid =
cond_data_trans_source_view.dbo.et_dummy.elist
WHERE
cond_data_trans_source.dbo.nodestate.stateid = 4
cond_data_trans_source_view: View publish container for the Source
app
cond_data_trans_source: Source application datastore
iv.
v.
vi.
vii.
viii.
A d-list (3 EList Buckets) in the Common library is constructed that uses
the eList item display order (disporder) to uniquely identify each
relevant eList item.
This d-list is a dimension of the d-cube Transfer EList; this d-cube uses an
ODBC link to the query shown above to retrieve the eList Item Names for
the relevant workflow states. Note that the link populates all 3 fields in the
d-cube with the same data; this is because we don’t care about the
distinction between the EListItemName and EListItemCaption; and
because we want each item to be its own parent (i.e. we don’t want any
hierarchy in our Transfer eList).
A d-cube export is performed to generate a file in a suitable format for
import as an eList into the Transfer Contributor model.
The eList of the Transfer model is updated.
The Admin Links are run, with appropriate controls provided through Wait
for Any Jobs macros in Contributor.
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
7
The overall Contributor automation process is as follows:
The contents of the Analyst macro (the 3rd step in the Contributor automation process
above) are as follows:
@ODBCConnect("cond_data_trans_source_view"; "")
@DListUpdate({D-List Conditional Data Transfer - Common.3 EList
Buckets})
@DLinkExecute({D-Link Conditional Data Transfer - Common.Transfer
EList
@DCubeOpen({Selection from D-Cube Conditional Data Transfer Common.Transfer EList})
@DCubeExport("C:\Documents and Settings\brooks\My Documents\Cognos
Planning Models\Analyst Libraries\Conditional Data
Transfer\Common\Ascii\Transfer eList.txt"; {Selection from D-Cube
Conditional Data Transfer - Common.Transfer EList};
["Clipboard=No","Separator=Tab","Single=No","Regional=No","CHeadings=
Top","WriteMode=Overwrite","PipesAsSpaces=No","TextQualifier=none","N
oNumericFormat=No","Default=Yes"])
@Close("Y")
@ODBCClose()
Note: the "Default=Yes" tag at the end of the @DCubeExport ensures that the macro
continues past the ‘overwrite existing file’ warning when the macro is run automatically.
3 Constraints and Error Handling
The following are some possible error conditions that I’ve tried to anticipate in the
model; this demonstrator model should be considered a sample, and has not been
exhaustively tested:
•
•
eList Item Name length. A simpler way of updating the Transfer model eList
would be to update a d-list in Analyst, and then use the D-List > Export as EList functionality. However, eList Item Names and Captions can be up to 100
characters, while d-list items can only be 50 characters. This is why I have
used a d-cube to hold the eList Item details (see step v. above), and why I
have dimensioned that d-cube with a d-list of the display order, rather than
simply using the eList item names themselves.
No locked nodes in the Source. If the process is run when there are no locked
eList items in the source, then you will try and generate an empty eList. This
can be handled in 2 ways; you can add a Dummy item to the 3 EList Buckets
dimension that is kept and never removed; or you can add a dummy eList
item in the source app that is always locked and to which end users never
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
•
•
8
have any access. I opted for the latter approach, for the reasons described
next.
No matching eList items in the Admin Links. Without a Dummy eList item in
the Target, you may find that if there are no locked nodes in the Source, then
the Admin Links raise an error condition because there are no matching eList
items. The use of a Dummy eList item in the Source and Target applications
will prevent this.
Transfer eList Item Import. The file produced from the d-cube export from
Transfer EList contains an additional column (3 EList Buckets) that is not
needed. However, as long as the eList Import is set to use a header row this
additional column is ignored and does not cause any problems. This is a
sample exported eList file for the Transfer model:
3 EList Buckets
27
23
26
EListItemName
Dummy
Finland
Mexico
EListItemParentName
Dummy
Finland
Mexico
EListItemCaption
Dummy
Finland
Mexico
4 Enhancements
The process currently requires that a publish is performed from every planner
node in the model. This would be inefficient for a large model, even if we only
publish a small dummy d-cube. Publishing a single cell from a single d-cube in a
single eList item will cause the it_elist and cy_elist tables to be generated;
an enhanced method would be to compile a SQL string that returns only the
planner nodes from the source application, regardless of the number of levels in
the hierarchy (and hence number of columns in the dimension table) which may
change over time. In this demonstration model, I perform a publish from only the
planner nodes, then perform an inner join to the nodestate table to retrieve the
workflow state. This join weeds out reviewer eList items (because they don’t have
entries in the et_dummy fact table).
In this demonstrator model, no Rights are defined for the eList items in the
Transfer model. The application will function perfectly well like this, but it is
possible that the Administrator may wish to have View access to the eList items
to verify that data are flowing into the Transfer model correctly. The current
process could be enhanced through the addition of another Analyst d-cube
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
9
(similar to the Transfer eList d-cube) designed to export a Rights file that would
be imported into the Transfer Contributor model.
It would be relatively simple to provide an additional Analyst or Contributor
application with a d-cube that allowed an administrator to select the workflow
states for which data should be transferred. The SQL string shown above could
then join in additional tables from this workflow state selector model, to return
nodes for whatever combination of nodes was selected.
For applications where the customer has the IBM Cognos BI suite, this process
will be much simpler to configure and maintain because the source application
and publish container metadata can be modelled in Framework Manager using
the techniques detailed in Terry Bird and Mike Sadler’s Planning Process
Management document. I built this demonstrator in IBM Cognos Planning only to
show that it could potentially be applied for customers with no IBM Cognos BI
capability.
Following from the last point, customers with ETL and/or DBA skills in house
could build a similar process by extracting data from the Source application
publish container, filtering it for the relevant workflow states, and then transferring
data directly to the Target application im_ staging stables. An automated Prepare
job and GTP in the target would then commit the data.
5 Application Details
IBM Cognos Proprietary Information
IBM Cognos 8 Planning Contributor
- Conditional Data Transfer
The Admin Links, Contributor XML and Macros are provided in a sub-folder of the
Conditional Data Transfer - Common library (number 459080001).
The user must set up a System DSN named cond_data_trans_source_view.
This is used as the source for the d-list update for 3 EList Buckets and for d-link
Transfer EList<ODBC ViewPublish.
IBM Cognos Proprietary Information
10
Fly UP