Description of Staging Tables in Controller
Customer wishes to use the 'staging tables' functionality of Controller (introduced since version 8.2). Customer wishes to have information on the schema/tables.
In one real-life example, the customer wanted to integrate Controller with Planning
Resolving the problem
The following information is directly from the official Controller 8.2 'readme' documentation:
(1) Staging Tables in the Controller Database
These tables can be used to import data into Controller.
Parameters for Import of Data to the Controller Batch Queue
These are the parameters to use when exporting data from another application to Cognos 8 Controller into the Controller staging tables. To use these parameters, call the stored procedure usp_triggerimportbatchjobs.
Note that the Batch Id is set automatically by Controller. For more information, see "The Batch Queue" (p. 581).
Table Name Description:
- XSTAGEFACT - Table used for import of data
- XSTAGEACC - Table used for import of accounts
- XSTAGECOMP - Table used for import of companies
- XSTAGECURRRATES - Table used for import of currency rates
- XSTAGEDIM1 - Table used for import of extended dimension 1
- XSTAGEDIM2 - Table used for import of extended dimension 2
- XSTAGEDIM3 - Table used for import of extended dimension 3
- XSTAGEDIM4 - Table used for import of extended dimension 4
(2) Importing Data from Other Applications Using Staging Tables
To facilitate imports from external systems, you can now import data and structures from staging tables. This means that you can perform budgeting and forecasting in IBM Cognos 8 Planning and consolidate the values in IBM Cognos 8 Controller. You import data with the import functionality in the Transfer/External Data/Import from Staging Table.
The following staging tables are included in the installation of Cognos 8 Controller 8.2:
- Account structures
- Company structures
- Extended dimension 1 structures
- Extended dimension 2 structures
- Extended dimension 3 structures
- Extended dimension 4 structures
- Currency rates.
They can be filled with data using an external tool, such as Cognos 8 Data Manager. You set parameters that add the import to the batch queue either with a new status called On Hold or to be executed immediately or later on. You can import data from the Controller staging table by selecting an option from the Define Import Specification window. A default specification template is created and can be used to define an import specification with import from the staging table. Note that you cannot use a staging table as a source when you work with the Import Specification Wizard.
You can import the following types of external structures: accounts, companies, extended dimensions and currency rates. To import structures you use the Define Import Specification functionality to define an import from staging tables and then select the specification in the Transfer/External Structures/Import window.
You can set the number of days before the staging tables are cleared in the General Configuration window. The default is set to 31 days, and we recommend that you clear the staging tables regularly to avoid excessive amounts of data.