Importing Data from an External Application
For example, you can create batch jobs for importing data or check the status of a batch job. The import is performed through stored procedures and staging tables that are installed with Cognos Controller.
From the external application, you can insert rows with information from a GL system into a staging table in the Cognos Controller database and place it in the Cognos Controller batch queue to be run immediately or later on. For more information on how to set up the report, see Parameters for Importing Data to the Controller Batch Queue.
You can also publish external data to a datamart, for more information see Publish Datamarts.
Because an import is logged as successful if at least one record is imported, always check the log report to see if there are records that were not imported successfully.
Staging Tables in the Controller Database
These tables can be used to import data into Cognos Controller.
| Table name | Description |
|---|---|
| XSTAGEFACT | Used for import of data |
| XSTAGEACC | Used for import of accounts |
| XSTAGECOMP | Used for import of companies |
| XSTAGECURRRATES | Used for import of currency rates |
| XSTAGEDIM1 | Used for import of extended dimension 1 |
| XSTAGEDIM2 | Used for import of extended dimension 2 |
| XSTAGEDIM3 | Used for import of extended dimension 3 |
| XSTAGEDIM4 | Used for import of extended dimension 4 |
Parameters for Importing Data to the Controller Batch Queue
These are the parameters to use when exporting data from another application into the Cognos Controller staging tables. To use these parameters, call the stored procedure named usp_triggerimportbatchjobs.
Note that the Batch Id is set automatically by Controller. For more information, see The Batch Queue.
| Number | Name | Description |
|---|---|---|
| 1 | pImpId | Import ID. This is the same ID that will be written
to the st_id column in the staging table. This process will populate
that column. Data type: nvarchar(30) |
| 2 | pImpSpec | Import specification name. This name must match
a predefined import specification in IBM Cognos Controller. Data type: varchar(12) |
| 3 | pImpSpecType | Import specification type: D = data A= account
R = currency rates C = company 1 = dim 1 2 = dim 2 3 = dim 3 4 = dim
4 Data type: varchar(1) |
| 4 | pImpSpecParams | Import specification parameters. This parameter
is required only if your import specification is defined to require
prompted information. The parameter must include the response required
to the prompt question(s). Data type: nvarchar(255) |
| 5 | pCtrlUser | The IBM Cognos Controller user name. Data type: varchar(8) |
| 6 | pSchedType | Type of scheduling: 0 = On hold 1 = Immediately 2 = One time only Data type: Int |
| 8 | pExecTime | The date and time when the batch job should be
executed. The date and time format should be the same as the date
and time settings of the database. Data type: Datetime |