1. Administration Guide
  2. Report Best Practices

Report Best Practices

  • All the parameters sent from Oracle Transportation Management to BI Publisher will be strings, so you must account for strings in the where condition for the queries written in data model of BI Publisher. Date strings are sent with a specific date format, and this date format is also sent as a parameter from Oracle Transportation Management. When dealing with dates, you must convert date string using the format, so also define a parameter to receive the date format.
  • Reports executed from Oracle Transportation Management on BI Publisher are always treated as Online Reports for BI Publisher even if they're scheduled in Oracle Transportation Management. This means the query timeouts and memory guard properties, such as Report Size Limit, set in BI Publisher for Online Reports will be applied to the reports requested from Oracle Transportation Management.
  • Reporting within Oracle Transportation Management is meant for running transactional reports that deal with single object records, not aggregate or analytical reports. Any aggregate or analytical reports that deal with huge data sets should be scheduled directly in BI Publisher.
  • When writing queries, keep in mind the VPD that will be used.
    • When logged into BI Publisher, the logged in user's VPD will apply, meaning it will use the VPD of the default role assigned to the user.
    • In Oracle Transportation Management, if switching roles, the VPD will change to the newly selected role, so make sure the queries work for that VPD, too.
  • See the following BI Publisher Documentation:

Analytical Reports

  • Create a report with facts and dimensions from same analysis folder, as using a different analysis folder might yield inconsistent results. For example, creating a report with Shipment Count fact from the Shipment Analysis folder and Dimension from the Shipment Stop Analysis folder will show inconsistent results.
  • Always have at least one fact and one dimension in the analytical report. You might see inconsistent results if ONLY dimension columns or ONLY fact columns are selected in the report.
  • Analytical reports are intended for analyzing at a higher level, meaning they should always be aggregated or summarized with high level dimension. For example: number of shipments and total cost by service provider in a particular lane.
  • It's always recommended to back up custom artifacts (reports and dashboards) on regular basis from Oracle Analytics Server (/analytics) using the archive option. This will be stored as a .catalog file. You can make a backup of BI Publisher reports, Data Visualization workbooks, and analytical dashboards and reports using this option.
  • All catalog folders in Oracle Analytics Server are read-only except the custom folder. To change a default report, copy it to the custom folder and edit it.
  • Caching is enabled in analytics, so that if the same report is run with same parameters, it will get the data from BI server cache instead of from the database. ETL clears the BI server cache as part of the ETL process, so it's recommended to run any analytical report after 30 min of ETL completion as BI server will look for cache table for every 30 min and clear the cache if the cache table has entries.
  • Analytics users should always have any of the following VPD profiles. Or, you can create a custom VPD profile with all the predicates provided in these VPD profiles.
    • FTI_DEFAULT
    • GTI_DEFAULT
    • SERVPROV

ETL

  • ETL is scheduled to run once daily. It is NOT recommended to create multiple ETL schedules on same day.
  • It's recommended to monitor ETL on regular basis and report any issues. This avoids a bulk extract in the event ETL was stuck due to some issues and wasn't running for an extended period of time. This might lead to performance issues when the ETL is run again. ETL can be monitored from Transportation Intelligence > ETL results screen. Select “In Progress” Load Status to monitor the running ETL process.
  • Customers should not run lookback ETL regularly. This might impact the performance as this will load the two weeks of data from the date selected in the OTM. This lookback option is only meant to correct the data if there were any ETL fixes.