Publishing Data to a Datamart from an External Application

You can publish data to a datamart from a source outside the IBM® Controller application using an ETL tool or other reporting solutions. The necessary stored procedures and staging tables are installed with Controller.

Note: The ability to publish data to a datamart from an external application is not available in Controller on Cloud.

For information about which types of data can be published, see Staging Tables in the Controller Database.

From the external application, you can insert rows with information from a GL system into a staging table in the Controller database and place it in the Controller batch queue to be published immediately or later on.

You can also import external data, see Publish Datamarts.

For information about how to check the status of a batch job from an external tool, see Importing Data from an External Application.

Parameters for Publishing data to a Datamart

These are the parameters to use when publishing data to a Controller datamart from another application. To use these parameters, call the stored procedure, named usp_triggerpublishbatchjobs.

Table 1. Parameters for publishing data to a datamart
Number Name Data type Description
1 pPublishId nvarchar(30) The publish ID, which appears as job name in the batch queue.
2 pTemplate varchar(6) The template code, which must exist in Controller.
4 pPublishParams nvarchar(4000) Parameters for the publish to datamart batch job.
5 pCtrlUser varchar(8) The Controller user name.
6 pSchedType Int Type of scheduling:

0 = On hold

1 = Immediately

2 = One time only

3 = Daily

4 = Weekly

5 = Monthly
8 pExecTime Datetime The date and time when the batch job should be run. The date and time format should be the same as the date and time settings of the database.

Return Codes for Publishing to a Datamart

When you publish data to a datamart in Controller from an external application, a return code indicates whether the publish was successful. The following return codes may appear.

Table 2. Return codes for publishing to a datamart
Code Description
>0 If the value is positive, the stored procedure was completed successfully and the value shows the batch ID that was assigned to the import in the Controller database.
-1010 The cube ID or template that you entered is not found or not valid.
-1030 The Controller user that you entered is not found.
-1040 An invalid parameter exists for pSchedType.