...

Import External Data from Staging Table Guideline

by user

on
Category: Documents
3

views

Report

Comments

Transcript

Import External Data from Staging Table Guideline
Guideline
Import External Data from
Staging Table
Product(s): IBM Cognos Controller 8.2 or higher
Area of Interest: Modeling
Import External Data from Staging Table
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
Import External Data from Staging Table
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
IMPORTING EXTERNAL DATA FROM STAGING TABLE ................................. 4
2.1
2.2
PREPARATION OF DATA ...........................................................................................5
CREATION OF IMPORT SPECIFICATION FOR DEBUGGING IMPORT FROM STAGING TABLE ................5
IBM Cognos Proprietary Information
Import External Data from Staging Table
4
1 Introduction
1.1
Purpose
This document will provide additional information on using the staging tables
to import data into Controller and will include samples of the import
specification, data files, etc.
1.2
Applicability
Controller 8.2 or higher
1.3
Exclusions and Exceptions
N/A
2 Importing External Data from Staging Table
The functionality for importing external data from a staging table was
introduced in Controller 8.2 and requires data in the table xstagefact, plus the
creation of batch job using a stored procedure and start of batch job. In
Controller 8.2 and 8.3 you do not have the possibility to view any discarded
lines in the log report. Instead, you just receive a message telling “Finished
with error”. The application was not prepared for dealing with discarded
records. This problem is recorded in Trakker log 586902 and has been fixed
in Controller 8.3.
In Controller 8.2 and 8.3 you are supposed to have only valid records in
xstagefact. The term valid records refer to records that are not or will not be
discarded in any way during the import.
There is a way to simplify the debugging of an import specification for staging
tables that will now be explained.
It can also be used when building up an import specification for staging table
since it’s much simpler to import from a flat file.
IBM Cognos Proprietary Information
Import External Data from Staging Table
2.1
5
Preparation of data
The data you are planning to import exist in table xstagefact. Create a flat file
that contains all columns from table xstagefact that you want to import.
Example table xstagefact:
-NB- Not all columns are shown
Example of flat file
This is a csv file with <,> as decimal point and <;> as delimiter. These signs
can differ depending on regional settings and/or tool used to create the file.
2.2
Creation of import specification for debugging import from staging
table
1. Make a copy of the import specification for staging table. In this example
the original specification is called TEST and the copy is called
TEST_DEBUG.
2. Change some properties for TEST_DEBUG
a) Source should be ASCII
b) Make sure the symbols defined in General tab are correct (e.g.
decimal symbol
3. List all references to staging table columns in specification TEST. Staging
table columns starts with ST_
4. All references to staging table columns in specification TEST should be
replaced with item references from file in specification TEST_DEBUG
according to table below.
Staging table column
Item
reference in
csv file
ST_PERIOD
3
ST_ACTUALITY
4
ST_COMPANY
5
IBM Cognos Proprietary Information
Import External Data from Staging Table
ST_CURRENCY
6
ST_ACCOUNTS
7
ST_EXTDIM1
8
ST_EXTDIM2
9
ST_EXTDIM3
10
ST_EXTDIM4
11
ST_JOURNALTYPE
12
ST_C_COMPANY
13
ST_O_COMPANY
14
ST_TRANCURR
15
ST_C_IDIM
16
ST_AMOUNT
17
ST_TRANAMOUNT
18
ST_FREE1
19
ST_FREE2
20
ST_FREE3
21
ST_FREE4
22
ST_FREE5
23
5. Run import from flat file using the flat file and the new import
specification
IBM Cognos Proprietary Information
6
Import External Data from Staging Table
Example 1
Here you can see a simple replace
TEST:
IBM Cognos Proprietary Information
7
Import External Data from Staging Table
TEST_DEBUG
IBM Cognos Proprietary Information
8
Import External Data from Staging Table
Example 2:
Sometimes you need to create a temporary field to store a value
TEST:
IBM Cognos Proprietary Information
9
Import External Data from Staging Table
10
TEST_DEBUG
Since you need 2 operations (item from file and operation template) you have
to create a temporary field to store the account value in and used that for
performing the operation template.
IBM Cognos Proprietary Information
Import External Data from Staging Table
IBM Cognos Proprietary Information
11
Import External Data from Staging Table
AND
3 Other Documents
1. Data Import_Guideline_Controller82
2. Guidelines to importing data into controller using data manager and
staging tables
IBM Cognos Proprietary Information
12
Import External Data from Staging Table
IBM Cognos Proprietary Information
13
Fly UP