Integrate HCL Unica with BIRT

Loading templates for the Reports SQL Generator

The HCL Unica reports packages that use reporting schemas contain SQL scripts that load template SQL select statements into the uar_common_sql table. The Reports SQL Generator uses the templates when it generates SQL scripts to create reporting views and tables.

To run the script that loads the templates, complete the following steps.
  1. Browse to the schema directory under your report pack installation and locate the templates_sql_load.sql script.
  2. Run the templates_sql_load.sql script in the Platform database.

Generating view or table creation scripts

When you generate reports, you extract reportable data from the reporting views or tables. You can create reporting views or tables by using the view or table creation scripts. Use the Reports SQL Generator to create view or table creation scripts.

To create view or table creation scripts, complete the following steps.
  1. Log in to HCL Unica as the user who has the ReportsSystem role.
  2. If you have created the default JNDI names for JDBC data sources, continue to step 3. If you did not create the default JNDI names for JDBC data sources, complete the following substeps.
    1. Select Settings > Configuration > Reports > Schemas > ProductName.
    2. Change the default values of the JNDI property to match the JNDI names that you used for the JDBC connections
  3. Select Settings > Reports SQL Generator.
    Note: If the JNDI data source names are incorrect or are not configured, the SQL Generator cannot validate the SQL scripts that create tables.
  4. In the Product field, select the appropriate HCL Unica application.
  5. In the Schema field, select one or more reporting schemas.
  6. Select the Database Type.
  7. In the Generate Type field, select the appropriate option for your database type.
  8. If the database type is Microsoft SQL Server, you cannot select materialized views.
  9. Ensure that Generate Drop Statement is set to No
  10. If you want to examine the SQL script that is generated, click Generate. The SQL Generator creates the script and displays it in the browser window.

    The SQL Generator creates the script and prompts for a location in which to save the file. If you selected a single reporting schema, the script name matches the name of schema. If you selected more than one reporting schema, the script name uses the product name, for example Campaign.sql.

    Note: When you run a script that creates materialized views on a DB2 database, you may see the following error:
    SQL20059W The materialized query table-name may not be used to optimize the processing of
      queries.
  11. Specify the location where you want to save the script and click Save. If you change the name of the file, ensure that you use a name that clearly indicates the schemas that you selected.
  12. Repeat steps 5 through 12 for each script that you want to generate.
    Note: The Interact reporting schemas reference multiple data sources. Generate a separate SQL script for each data source.

SQL scripts by data source

Use separate SQL scripts to create views or materialized views for each data source.

The following table provides information about the scripts that you must generate for each data source, the resulting script name, and the scripts that must be run against the HCL Unica application database for creating views or materialized views:

Note:
  • The table lists the default names for the data sources and generated scripts. Your names may be different.
  • The product reporting schemas reference more than one data source. Generate a separate SQL script for each data source.
Installer placed report design files possess database connection tokens. You must update them for your system database. You must run birtdbutil.sh/bat utility to update the same. You may have one or more data sources configurations for the report. Refer the following table for the same.
Reports Configurations
Campaign Reports CampaignDS
Interact Reports

InteractDTDS

InteractETLDS

InteractLearningDS

InteractRTDS
Plan Reports PlanDS
SQL scripts by data source
Reporting schema Data source and default name Default script name
All Unica Campaign reporting schemas

Unica Campaign system tables

campaignPartition1DS		 Campaign.sql, unless you generated separate scripts for each reporting schema and each script is named after the individual schema.
Unica Interact Deployment History, Interact Performance, and Interact Views

Unica Interact design time database

campaignPartition1DS

 Interact.sql
Unica Interact Learning

Unica Interact Learning tables

InteractLearningDS

 Interact_Learning.sql
Unica Interact Run time

Unica Interact run time tables

InteractRTDS

For Unica Campaign only: Creating and populating reporting tables

You can use SQL scripts to create and populate reporting tables for Unica Campaign. The reports application uses reporting tables to extract reportable data.

To create and populate reporting tables for Unica Campaign, complete the following steps.
  1. Connect to Campaign system 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 at least 10240. The default heap size is 2048.
  5. 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.
  6. Use your database administration tools to populate the new tables with the appropriate data from the production system database.
  7. Complete the following substeps.
    1. Navigate to <Birt_Home>/Reports/campaign/ddl/<DBtype> installation directory.
    2. Locate and execute sp_whatifofferperf.sql.
      Note: For more than one partition, you must run the script for each partition in Campaign database.
    3. For DB2, set DB2_COMPATIBILITY_VECTOR using following command. You must stop and start db2 server post parameter set: 
      db2set
        DB2_COMPATIBILITY_VECTOR=ORA

Continue with "Setting up data synchronization".

Copy the Unica Campaign reports folder in BIRT connection

Campaign installer places report design folders or files under Campaign installation directory.

Complete the following steps.
  1. Create a folder campaign/partitions/partitionN under <PLATFORM_HOME>/Birt/Reports.
  2. Copy the Affinium Campaign and Affinium Campaign - Object specific Reports folders from Campaign_Home/reports and place it in <PLATFORM_HOME>/Birt/Reports/campaign/partitions/partitionN, where N is your partition number.
  3. Create a folder Unica Dashboard/Campaign/partitions/partitionN under <PLATFORM_HOME>/Birt/Reports.
  4. Copy rpt design files from Campaign_Home/reports/Unica Dashboards/Campaign folder into <PLATFORM_HOME>/Birt/Reports/Unica Dashboard/Campaign/partitions/partitionN.

Continue with “Setting up data synchronization.”

Note: Even if the Campaign install host and BIRT application server host is same, it is recommended to copy report design files from install directory under Platform_Home/BIRT/Reports and the folder structure must be campaign/partitions/partitionN.

For Unica Plan only: Copy the Unica Plan report folder in BIRT connection

While deriving Dashboard report design file name, Platform requires the database type. It is required that the following properties are populated with correct database type.

Affinium|Plan|umoConfiguration|DBType

Complete the following steps:

  1. Create a folder "Plan" under <PLATFORM_HOME>/Birt/Reports.
  2. Copy the folders - Affinium Plan and Affinium Plan - Object specific Reports folders from <PLAN_HOME>/reports/Plan_BIRT_Reports and place it in <PLATFORM_HOME>/Birt/Reports.
  3. Create Unica Dashboard folder under <PLATFORM_HOME>/Birt/Reports, if not already created.
    Note: Ensure that the folder under <PLATFORM_HOME>/Birt/Reports/Unica Dashboard is “Plan”.
  4. Copy the respective DB rpt design files from the Unica Dashboards folder from <PLAN_HOME>/reports/Plan_BIRT_Reports to <PLATFORM_HOME>/Birt/Reports/Unica Dashboard/plan.
  5. Ensure that you possess the execute permissions for the rpt design files.
  6. Run the birtdbutil.sh/bat to update the datasource for the report design files from <PLATFORM_HOME>/Birt/tools/bin. For information on BIRT DB utility, see the Update data source in BIRT report design files using BIRT utility section.
  7. Update the navigation URL and port under Affinium|Birt|navigation. The DBType should be displayed correctly under Affinium|Plan|umoConfiguration.
  8. Navigate to Settings > Configuration > Plan > umoConfiguration > reportsConfiguration and configure the folder where Plan reports are located. In the birt web.xml, the above-mentioned path is appended to the reports path.
  9. Under Affinium|Plan|umoConfiguration|reports, change the following properties. For example:
    reportsAnalysisSectionHome Plan/Affinium Plan
    reportsAnalysisTabHome Plan/Affinium Plan - Object Specific Report
    Note: You must not include a slash (/) in the beginning of the path of these properties.

Continue with “Setting up data synchronization".

For Unica Interact only: Creating views or materialized views

You can use SQL scripts to create views or materialized views for Interact. The reports application uses views or materialized views to extract reportable data.

Before you create views or materialized views for Interact, verify that the language setting for the computer from where you run the lookup_create SQL script is enabled for UTF-8 encoding.

To create views or materialized views for Interact, complete the following steps.

  1. Locate the SQL scripts that you generated and saved previously.
  2. Use the database administration tools to run the appropriate script against the appropriate application database(s) for the report package that you are configuring.
Note: When you run a script that creates materialized views on a DB2 database, you may see the following error:
SQL20059W The materialized query
        table-name may not be used to optimize the processing of queries.

However, the materialized view is successfully created.

  1. Under the Interact installation directory, in the tools subdirectory of report folder, find the uari_lookup_create_<db_type>.sql script for your database. For example, for SQL Server, script is available at <INTERACT_HOME>/Interact/reports/tools/uari_lookup_create_MSSQL.sql.
  2. Run the lookup_create script on the Interact design time database. Ensure that the database tool that you use commits the changes. For example, you may require to set the auto-commit option of the database to true.

Continue with "Setting up data synchronization".

Creating and populating reporting tables

You must import following views in design time database and runtime database. You must use your own tools for this step. The SQL Generator does not generate the SQL for you.

  • Execute the views on Campaign database. Interact installer lays down database scripts under the Interact installation location which contains these views. Scripts are available at <INTERACT_HOME>/reports/ddl/<dbtype>/InteractDT.sql.
  • Execute the views on Interact Runtime database. Interact installer lay down database script under the Interact installation location which contains these views. Scripts are available at <INTERACT_HOME>/reports/ddl/<dbtype>/InteractRT.sql.
    Note: In case if you face any issue while running script through CLI, then you must use IBM Data Studio Client or before running the script you may require to remove leading or trailing spaces from the SQL statement given in the file and close all statements with semicolon.

Setting up data synchronization

Ensure that you use the database administration tools to schedule regular data synchronization between the production databases of the HCL Unica application and the materialized views.

To set up data synchronization, use the following guidelines depending on your application and database type.

  • For Unica Campaign, use the scheduled Extraction, Transformation, and Load (ETL) method, or any custom method to schedule regular data synchronization between the production databases and the new reporting tables.
  • For Unica Interact on Oracle or DB2 databases, use the scheduled Extraction, Transformation, and Load (ETL) method or any custom method to schedule regular data synchronization between the production databases and the new reporting tables.
  • For Unica Interact on a SQL server, use the scheduled Extraction, Transformation, and Load (ETL) method, or any custom method to schedule regular data synchronization between the production databases and the new reporting tables.

Copy the Interact reports folder in BIRT Connection

The Interact installer places report design folders/files under Platform installation directory. You must copy reporting folders for each product reports manually to the server where hcl-birt.war is deployed. Even if the Interact install host and BIRT application server host is same, it is recommended to copy report design files from install directory to a new directory of application server host. Unica Interact reports are placed under partition_home directory.

Complete the following steps.

  1. Create a folder campaign/partitions/partitionN under <PLATFORM_HOME>/Birt/Reports.
  2. Copy the Affinium Campaign and Affinium Campaign - Object specific Reports folders from Interact_Home/reports and place it in <PLATFORM_HOME>/Birt/Reports/campaign/partitions/partitionN, where N is your partition number.
  3. Create Unica Dashboard/Interact/partitions/partitionN folder under <PLATFORM_HOME>/Birt/Reports.
  4. Copy rpt design files from Interact_Home/reports/Unica Dashboards/interact folder into <PLATFORM_HOME>/Birt/Reports/Unica Dashboard/Interact/partitions/partitionN.

Continue with “Setting up data synchronization.”

Note: Even if the Campaign install host and BIRT application server host is same, it is recommended to copy report design files from install directory under Platform_Home/BIRT/Reports and the folder structure must be campaign\partitions\partitionN.