Search

What are some best practices for table joins when creating reports in Oracle B2C Service Analytics?

Environment:

Analytics
Oracle B2C Service

Resolution:

Table joins should be configured to return the smallest data set possible. The smaller the data set Oracle B2C Service Analytics analyzes to process a report, the faster the report will run. The following best practices provide guidelines about how to achieve this, and how to avoid typical cases where join configurations can result in unnecessarily large data sets. A sound understanding of relational databases and SQL can benefit here.

a) Only join to tables needed for the report. For various reasons, sometimes report configurations include joins to tables that aren’t actually used in the report. This should be avoided. When possible, eliminate joins and references to tables not used in the report.  Sometimes there is a valid reason why a report references columns from tables not used in the report. Keep in mind this will increase report processing time, and there may be ways to avoid doing this. For example, if you need to filter out rows in the parent table that do not have rows in another table, consider adding a NOT NULL filter on the join field in the parent table.  Sometimes extraneous joins exist because they were inherited from the definition of a report that was copied to build a new report. Make sure that when you re-use one report to build another, that you review the report configuration and eliminate tables, joins, columns, etc. that aren’t required for the newer report.

b) Use as few tables as possible. Design reports to require the fewest number of tables possible. The fewer tables required for a report, the fewer joins will be needed. This will reduce the number of rows to be analyzed to generate the report, and decrease the related processing required, which contributes to faster report
processing. This objective must be balanced however with the insights the report must provide and related filtering needs.

c) Avoid joining large tables that aren’t directly related to each other. Sometimes performance suffers when attempting to join data from very large tables that aren’t directly related to each other (i.e. through a common key). If you join such tables together, add as many filters as possible to limit the result set.
An example is joining the Transactions and Inc_Performance tables in the same report via the Incident record. This will create a very large dataset as the number of rows for a given incident will be equal to the number of transactions multiplied by the number of Inc_Performance records. If such joins are required, make sure it is absolutely necessary. To improve performance, consider adding a filter that provides one or more additional joins between the two tables. An example is adding a filter joining transactions.created to inc_performance.time_start.

d) Configure one-to-many joins properly. If the join between tables is a one-to-many join, use the table with the “one” record as the first table, and then join the table with the “many” records to it.

e) Configure one-to-one joins properly. If the join between tables is a one-to-one join, use the table with the fewer records (based on the filtering you intend to do) as the first table and the table with the more records as the second table.

f) Use inner joins rather than outer joins when possible. Inner joins return less data than outer joins. When possible, use inner joins. Oracle B2C Service Analytics typically defaults to inner joins, but where configuration is applied or required, use inner joins when appropriate.

For example, suppose you need a list of incidents with associated organization information. If you join the Incidents table to the Organizations table using an outer join, the result set returned will be those incidents that have organization information as well as incidents that don’t have organization information. However, if you use an inner join, the data returned will only be those incident records that contain organization information. Therefore, the resulting data set using an inner join will be smaller than with an outer join, and more targeted. If using an outer join, make sure you need it.

For more information on Analytics and creating custom reports, refer to Answer ID 4287: Analytics resources.