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.
About this task
To create view creation scripts, complete the following steps.
Procedure
-
Log in to Unica as the user who has the
ReportsSystem role.
If you used the default JNDI names for JDBC data sources, continue to step 3.
-
If you did not use the default JNDI names for JDBC data sources, complete steps a and b:
-
Select .
-
Change the default values of the JNDI property to match the JNDI names that you used for the
JDBC connections.
Important: If the JNDI data source names are incorrect or have not been configured, the
SQL Generator cannot validate the SQL scripts that create tables.
-
Select .
-
In the Product field, select the appropriate Unica application.
-
In the Schema field, select one or more reporting schemas.
- Select the Database Type.
-
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 |
Note: 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.
- 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.
- 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.
Note: When you run a script that creates materialized views on a DB2 database, you might 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.
-
Specify the location where you want to save the script and click Save.
If you change the name of the file, make sure to use a name that clearly indicates the schemas that
you selected.
-
Repeat steps 5 through 12 for each script that you want to generate.
Note: The Unica Interact reporting
schemas reference multiple data sources. Generate a separate SQL script for each
data source.