...

Data Import Guideline Product(s): IBM Cognos Controller 8.2

by user

on
Category:

movies and tv

1

views

Report

Comments

Transcript

Data Import Guideline Product(s): IBM Cognos Controller 8.2
Guideline
Data Import
Product(s): IBM Cognos Controller 8.2
Area of Interest: Modelling
Data Import
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
Data Import
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
2
VELOCITY THROUGH INTEGRATION ............................................................ 4
2.1
2.2
2.3
2.4
SCHEDULE DATA IMPORT .........................................................................................4
POSSIBILITY TO IMPORT MULTIPLE FLAT FILES USING WILDCARDS .........................................4
SERVER AS PROVIDER .............................................................................................5
CLIENT AS PROVIDER ..............................................................................................5
3
IBM COGNOS CONTROLLER 8.2 ARCHITECTURE.......................................... 6
4
IMPORTING – STAGING TABLE .................................................................... 6
4.1
4.2
IMPORT SPECIFICATIONS .........................................................................................7
DEFINE IMPORT SPECIFICATIONS ADDED WITH STAGING TABLE AS SOURCE.............................7
5
EXTERNAL TRIGGER ..................................................................................... 8
5.1
5.2
5.3
ETL TOOL. EXTRACT TRANSFROM LOAD.......................................................................8
PARAMETERS FOR USP_TRIGGERIMPORTBATCHJOBS .........................................................9
ERROR CODES FOR USP_TRIGGERIMPORTBATCHJOBS........................................................9
6
EXAMPLE OF DATA TRANSFORMATION SERVICES..................................... 10
7
FRAMEWORK MANAGER ............................................................................. 11
7.1
7.2
7.3
IMPORT EXTERNAL DATA FROM FRAMEWORK MANAGER .................................................. 11
STEPS.............................................................................................................. 11
WINDOW PARTS ................................................................................................. 12
IBM Cognos Proprietary Information
Data Import
4
1 Introduction
1.1
Purpose
The purpose of this document is to layout the different approaches to loading
data into Controller.
1.2
Applicability
Controller 8.2
2 Velocity through integration
2.1 Schedule data import
Scheduling the import of external data is done in the old menu Transfer\External
Data\Import, but now it is called Import from Flat Files instead. The name change of
the menu is done to separate it from Import from Staging Table which I will get back
to later on.
Worth notice is that is not possible to schedule import of structures. The reason for
this is the lack of requirement and that you have to be in single user mode to
perform such an import. I will get back to import of structures later on as well.
The existing functionality under Transfer\External Data\Import from Flat Files is
added with a schedule function. It is possible to schedule the import to be executed
immediately or more suited to the propose One Time Only.
When an import is scheduled a batch job is created and it is possible to manage and
edit the job from the Manage Batch Queue menu.
The menu Transfer\Log Report is modified so it is possible to see the Batch number;
this makes it easy to follow up the import. It is possible to reach the Log Reports
menu by double click on a row in the menu Manage Batch Queue.
2.2 Possibility to import multiple flat files using wildcards
We have also made it possible to use wildcards when Import External Data and
External Structures.
This is of course useful when you like to import multiple files in one data load.
The allowed wildcards are : ‘*’ and ‘?’
The sign ‘*’: Matches any string of zero or more characters and can be used in the
beginning or at the end.
The sign ‘?’: Matches any single character and can be used at the beginning, in the
middle or at the end.
All the files that should be imported with wildcards have to be placed in the same
folder.
IBM Cognos Proprietary Information
Data Import
5
Of course it is possible to schedule an import that is made by using wildcards
2.3
Server as provider
• When select to have server as provider it is mandatory to select a directory.
The directories in the drop down list must be added in Controller
Configuration. By being forced to specify one or several directories on the
server it makes it possible to decide which directories the users should have
access to and maybe more important, which directories they not should have
access to.
• In the field Import File it is not possible to use the File Open dialog, the file
name has to be written in.
• When having server as provider; the files do not have to exist when
scheduling the import, the check if the files exist is done at runtime. This is
useful if you want to schedule the import before all the files are in place.
2.4
Client as provider
• When select to have Client as provider the Import Directory filed is not active.
• A new database table is created to be used when scheduling an import using
files from the client side. Information about the scheduled file name and path
(no data) will be placed in the new table. The table is called zscheduleinfo.
-NB- When having client as provider; the files to import must exist when scheduling
the import.
IBM Cognos Proprietary Information
Data Import
6
3 IBM Cognos Controller 8.2 architecture
client
C8
BI
application
layer
FM
D/S/T
scheduled
datastore
flat files
scheduled
D
D/T
EP
staging
tables
D/S/T
FM
AAF
odbc
SAP
?
DM
published
?
More info
Other db/server
Flattened structures
4 Importing – Staging Table
4.1 Eight Staging Tables in the Controller database
In the Controller database, 8 staging tables are created. Data (one) and structures
(seven) are the following: The structures are:
•
•
•
•
•
•
•
•
xstagefact = Data
xstageacc = Account Structure
xstagecomp = Company Structure
Xstagedim1 = Ext. Dim 1 Structure
Xstagedim2 = Ext. Dim 2 Structure
Xstagedim3 = Ext. Dim 3 Structure
Xstagedim5 = Ext. Dim 4 Structure
Xstagecurrrates= Currency Rates
These staging tables can be populated with data by an ETL tool. (For example Data
Manager or SQL Server Enterprise Manage or any other third-party ETL tool.) ETL
stands for Extract Transform Load.
The creation of staging tables makes it possible to import data and structures
without using flat files and it is also a step towards improving the possibilities to
transfer data between Controller and Planning.
IBM Cognos Proprietary Information
Data Import
7
By the using of staging tables it is possible to load data directly from a database or a
data warehouse.
The staging table for data (xstagefact) is based on the xdb tables. In addition to the
columns from xdb the staging table includes an ID column and five columns that can
be used to import customer specific data, free1-5.
The staging tables for structures (all started with xstage…) are based on the fields of
the structures used in the Import Specifications (for structures). In addition to these
fields an ID column and five columns that can be used to import customer specific
data, free1-5, are added.
4.1
Import Specifications
The Define Import Specification functions (Data and Structures) in Controller are
added with the option to select to import from Controller Staging Table. The option
to import from flat files is of course remaining.
Default Specification templates are created to be used as guidelines when building
an Import Specification that having staging table as source.
4.2
Define Import Specifications added with Staging Table as source
IBM Cognos Proprietary Information
Data Import
8
5 External Trigger
5.1
ETL tool. Extract Transfrom Load.
It is now possible to create a batch job in the Controller batch queue without log on
to the Controller application. It is possible to create batch jobs for both Publish and
Import
To be able to do that it is necessary to have access to an ETL tool. As I mention
before; ETL stands for Extract Transform Load. This ETL tool can for example be
Data Manager or SQL Server Enterprise Manager.
Stored procedures in the database will be the interface for the ETL tool to trigger the
batch jobs for Import of data and Publish data.
Rows to be imported by the GL import functionality in Controller are inserted in the
staging table by the ETL tool and then the stored procedure is called. It will put the
IBM Cognos Proprietary Information
Data Import
9
import in the Controller batch queue and depending on the parameters; the import
will be scheduled or executed as soon as possible.
A possible scenario could be that rows are exported or aggregated from a GL System
(no 1 in the figure below), then inserted in the staging table (2) and then the stored
procedure will be called, validating the parameters and puts the import in the
Controller batch queue (3). If requested, the stored procedure will return if the
import has completed successfully or not (4).
5.2
Parameters for usp_triggerimportbatchjobs
I) Parameter codes:
• @pImpId nvarchar(30), --importid,
• @pImpSpec varchar(12), --import spec name to be used
• @pImpSpecType varchar(1), --import spec type (D=data,
A=Account, R=Currency rates, C=Compay, 1=Dim1, 2=Dim2,
3=Dim3, 4=Dim4)
• @pImpSpecParams nvarchar(255), --import spec params, (if the
Import Spec demands this)
• @pCtrlUser varchar(8), --Controller user
• @pSchedType int, --0=On hold, 1=Immediately, 2=OneTimeOnly
• @pExecTime datetime –Date and time when the import should
take place
5.3
Error Codes for usp_triggerimportbatchjobs
•
•
•
•
•
•
•
•
•
-1010 = Given import specification is not found/valid
-1020 = Import specification parameter is not found/valid!
-1030 = Given Controller user not found!
-1040 = Invalid value for parameter pScheduleType!
-1050 = Only schedule type 0 is allowed when importing
structures!
-1060 = No rows were updated in staging table, possibly wrong
importid was sent in
-2200 = Error occurred when the batchjob was added to the batch
queue!
-2100 = Error occurred when batch_id was incremented!
-2300 = Error occurred when updating rows in the staging table,
connecting rows to import to a specific batch_id!
IBM Cognos Proprietary Information
Data Import
10
•
-Error
6 Example of Data Transformation Services
Now I will show you a simple example of how this can be manage by SQL Server
Enterprise Manager
What you do is that you create a DTS Package, DTS stands for Data Transformation
Services.
First you create a Connection to the source. That can be a Database, Data
Warehouse or a flat file.
Then you make a connection to the database that you want to make the import to.
You connect these two connections with an arrow that contains information about
how the data should be transformed.
The last thing you do is that you create a SQL execute task. In that you enter the
name of the stored procedure and enter the parameters.
The parameters are:
•
•
•
•
•
•
•
Import ID
The name of the Import Specification to be used
The type of import Specification
Any other parameters, for example if the Import Specification contains
prompts
The controller user
Schedule type. Immediately or One Time Only, or On Hold
Time and date
For data it is possible to schedule the import or put it On Hold. When putting it On
Hold you just fill the staging table with data from the source but an import will not
be performed until you execute it from within Controller, (Menu Transfer\External
Data\Import from Staging Table).
IBM Cognos Proprietary Information
Data Import
11
For structure it is not possible to schedule the import, just put it On Hold and
execute the import from within the Controller application.
Then you execute the package by pressing the execute button.
Unlike the import of data that having two different menus depending on if it is Flat
File or Staging Table the import of structure is made from the same menu regardless
from source.
This menu is changing depending on which source the selected Import Specification
has. When select an Import Specification having Staging Table as source all On Hold
jobs for that structure are visible in the grid.
7 Framework Manager
7.1
Import External Data from Framework Manager
You can import data from, for example, Cognos BI applications to Controller by using the
Import from Framework Manager function in Cognos 8 Controller.
To use this functionality, you have to:
•
•
•
•
•
Have an installation of Cognos 8 BI.
Define a Framework Manager model and publish it to Cognos Connection.
Create a report in Report Studio. The report should be defined as a list report. The
defined columns in the report will be selectable in the Define Import Specification
window.
Define user and password for the connection with Framework Manager in Cognos 8
Controller Configuration.
Create an import specification in Cognos 8 Controller based on Framework Manager.
For information about how to define import specifications, see Define Import
Specification.
Note: You can schedule the import to take place immediately or later using the function
called Scheduling within the SQL tool. For more information about how to schedule imports,
see Schedule Import.
7.2
Steps
1. To import data from Framework Manager, select Transfer/External Data/Import
from Framework Manager. –NB- Security set up must be checked. The
anonymous access must be set to True and Controller is required to be on native
security. This step is required as workaround in 8.2, but has been corrected in 8.3
(Trakker 575457)
2. Select an import specification in the list.
3. Click Run.
IBM Cognos Proprietary Information
Data Import
7.3
12
Window Parts
Part
Function
Import
Specification
Here you select the import specification that you want to use. The list
includes the available specifications that are based on Framework
Manager.
IBM Cognos Proprietary Information
Fly UP