...

Guidelines to importing data into controller using data manager and staging tables

by user

on
Category: Documents
3

views

Report

Comments

Transcript

Guidelines to importing data into controller using data manager and staging tables
Guideline
Guidelines to importing data into
controller using data manager
and staging tables
Product(s): IBM Cognos Controller 8.2
Area of Interest: Modeling
Guidelines to importing data into controller using data manager and
staging tables
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
Guidelines to importing data into controller using data manager and
staging tables
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
STAGING DATA WITH DATA MANAGER ........................................................ 4
2.1
2.2
2.3
2.4
SETUP IN CONTROLLER ...........................................................................................4
SET UP DATA MANAGER BUILD ..................................................................................4
LINE ITEMS IDENTIFICATION AND WHAT IS REQUIRED .......................................................4
CREATE THE SQL COMMAND ....................................................................................6
3
ADDITIONAL DOCUMENTATION ................................................................... 7
IBM Cognos Proprietary Information
Guidelines to importing data into controller using data manager and
staging tables
4
1 Introduction
1.1
Purpose
This document will provide guidelines to loading data into Controller using
staging tables and data manager.
1.2
Applicability
Controller 8.2 or higher
1.3
Exclusions and Exceptions
N/A
2 Staging Data with Data Manager
2.1
Setup in Controller
Create an import specification to import from staging tables for data; this can
include all the normal functions, including lookup tables (where applicable).
2.2
Set up Data Manager Build
a) Locate source data; this can be any source of data, which Data
Manager can access.
b) Map data to controller table “xstagedata” using the usual Data
Manager functionality.
-NB- This needs to include at least the following items for each line.
2.3
Line items identification and what is required
a) st id – identity of the staging import job, this is entered in column st_id,
can be alphanumeric, AND must be unique
b) batch id – this must be empty, it is populated by the system when the
import is schedule, it will give the batch number that system has allocated
c) st amount – this field has to be populated with at least a zero, a null
value is not permitted
IBM Cognos Proprietary Information
Guidelines to importing data into controller using data manager and
staging tables
d) other fields – all other fields are optional, they can be populated if
required or left as null values. Utilize appropriately, considering the titles
and purposes. Five additional fields are available to store any additional
information for the import. –NB- Different jobs can contain different
columns.
IBM Cognos Proprietary Information
5
Guidelines to importing data into controller using data manager and
staging tables
2.4
Create the SQL Command
Create the SQL command to activate the stored procedure for importing
staged data, an example of this is:
SQL('TargetDB',concat('exec [fastnet].[usp_triggerimportbatchjobs]
''',$LoadID,''',''STAGE1EA'',''D'','''',''ADM'',1,'''''))
-NB- The parameters are as follows for this store procedure:
IBM Cognos Proprietary Information
6
Guidelines to importing data into controller using data manager and
staging tables
7
a) @pImpId, this is the value in the st_id column identifying the lines are to
be imported
b) @pImpSpec, this is the code of the import specification that is used for
this import, this must be a spec written to upload data from the staging
tables on the general tab of the definition.
c) @pImpSpecType, this is the code to identify the information that is to be
loaded, it could be any of the following values:
D=Data
A=Account
R=Currency Rates
C=Company
1=Dim 1
2=Dim 2
3=Dim 3
4=Dim 4
d) @ImpSepcParams, this is the list of any additional parameters that need
to be sent to the import spec, these are the same as “ask at run” items
in the spec
e) @CtrlUser, this is the user ID in Controller to be used for the import
f) @SchedType, this is the parameter that decides to import immediately or
schedule for later date, etc., it can include the following values:
0= Hold
1=Immediately
2=One time only
g) @ExecTime, this is the date and time for scheduled import to be carried
out, it is not required if the import is set to run immediately. The format
should match the database server date format.
3 Additional documentation
a) Data Import_Guideline_Controller 8.2
IBM Cognos Proprietary Information
Fly UP