Creating and populating reporting tables for Campaign

You can use SQL scripts to create and populate reporting tables for Campaign. Reports uses reporting tables to extract reportable data.

Procedure

Complete the following steps to create and populate reporting tables for Campaign:
  1. Create the reporting database.
  2. Locate the SQL scripts that you generated and saved previously.
  3. Use your database administration tools to run the appropriate script against the appropriate application database(s) for the report package that you are configuring.
  4. For Campaign with a DB2® database, increase the DB2 heap size to 10240 or higher. The default heap size is 2048. Use the following command to increase the heap size:

    db2 update db cfg for databasename using stmtheap 10240

    where databasename is the name of the Campaign database.

    Increasing the heap size ensures that IBM® Cognos® does not display SQL error messages if a user selects all the campaigns when running a report such as the Financial Summary report.

  5. Locate the db/calendar subdirectory in the Marketing Platform installation directory and find the ReportsCalendarPopulate script that is appropriate for the database type.
    The ReportsCalendarPopulate script creates the following tables:
    • UA_Calendar
    • UA_Time
  6. Run the ReportsCalendarPopulate script on the new database that you created with the table creation script.
  7. For DB2 only, complete one of the following tasks:
    • Run the script from the command prompt by using the following command:

      db2 -td@ -vf ReportsCalendarPopulate_DB2.sql

    • If you use the DB2 client interface, change the termination character to the @ character in the Statement termination character field.
  8. Use your database administration tools to populate the new tables with the appropriate data from the production system database.
    Note: You must use your own tools for this step. The SQL Generator does not generate the SQL for you.

What to do next

Setting up data synchronization

Make sure that you use scheduled Extraction, Transformation, and Load (ETL) method, or any custom method to schedule regular data synchronization between the production databases of the IBM EMM application and the new reporting tables.