Loading templates for the Reports SQL Generator

The HCL Unica Insights reports use reporting schemas containing SQL scripts. These SQL scripts 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 creation scripts

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

To create view 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.
    Note: In the Database type drop down list, you can see Oracle, SQLSever, DB2, OneDB, and MariaDB. MariaDB and OneDB are not supported for Collaborate reports OneDB is not supported for Plan reports. See the Install and configure Unica Insights reports for the supported databases for each product.
  7. Under the Generate Type list, select the appropriate option for your database type. Table and XML options are not recommended.
    Database Allowed Options
    SQL Server Views
    Oracle Views, Materialized views
    IBM DB2 Views, Materialized views
    MariaDB Views
    OneDB Views
    For Oracle and IBM® DB2®, it is recommended to use materialized views. The Administrator must schedule them to refresh nightly or hourly as per the requirement.
  8. Ensure that Generate Drop Statement is set to No.
  9. 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.
  10. Click Download.
    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, for example Deliver_Mailing_Performance.sql. If you selected more than one reporting schema, the script name uses the product name, for example Campaign.sql.

    SQL20059W The materialized query table-name may not be used to optimize the processing of queries.

    However, the materialized view is successfully created.

  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.