An Oracle Analytics Case Study: Leveraging Agents for Automated Expiration Reports

A customer asked for an automated system to monitor the machinery expiration dates for their own clients. Specifically, they wanted to receive a weekly or monthly report via email showing the remaining days until the expiration of each machine, excluding machines that had already been flagged in previous reports. Each recipient client should receive information only about the machines relevant to them. This seemingly simple requirement led to a series of technical challenges within Oracle Analytics, particularly in configuring agents to send customized reports. This article, describes how I addressed and resolved these issues, ensuring a tailored solution that met the customer’s needs.

Data Analysis

The analysis for the report configuration includes several key columns, essential for identifying and notifying upcoming machinery expirations:

• Machine: the name or unique identifier of the machinery.

• Days until expiration: a dynamic calculation of the time remaining until the machine’s expiration date.

• Expiration date: the specific date when the machinery is due for replacement or maintenance.

• Email recipient: the person or department responsible for managing the machine.

The machines included in the analysis are those with an expiration date within the next 180 days from the current date. To meet this criterion, a filter has been applied to restrict results to only the machinery that falls within this time frame (Figure 1).

Figure1. The machines included in the analysis are those with an expiration date within the next 180 days from the current date.

The filtered column is:

TIMESTAMPDIFF(SQL_TSI_DAY, current_date, "Machine Warranty Info"."Warranty End Extended Date").

This formula calculates the difference in days between the current date and the machine’s expiration date.

The process used to identify which of these machines were not present in previous reports, and will be described in the following sections.

Creation of an ad hoc analysis for comparison with previous reports

A control analysis was created using the set operations (Figure 2) in Oracle Analytics.

Figure 2. The created analysis, that uses set operations, to union the two minus parts

Between the first and the last two parts of the analysis, shown in the image, there is a minus relationship (Figure 3), as the following:

Figure 3. Set operations and their tokens: particularly union and minus operators

Between the two main parts, the relationship is of union type.

Figure 4. A set-theoretic example of the records to remove to obtain only non-repetitive data

The goal (Figure 4) is to identify machines that, for example, were not present in the machinery inventory 30 days ago or that were not included in the 180-day expiration window. Consequently, using the minus function, we will exclude machines that were present in the report during the previous 30 days to avoid receiving redundant information. This intersection must be applied to both machines expiring from 'today' to '180 days from today' and those from '30 days ago' to '180 days from today'. After this, a union will be performed between the two sets to account for both scenarios: when a machine is added to the inventory within the designated time range, and when a machine has already reached its expiration within the considered 30-day period.

At this point, we have created a control analysis that identifies the unique machines that were not flagged in the previous report.

But it doesn't end here; an additional check is needed.

Filtering the analysis to be sent via email

We created an analysis that includes only the machines that were not present in previous reports. However, each machine is associated with a recipient. For example, Machine 1 was purchased by Client 1. Suppose Machine 2 was added to the inventory during the relevant time frame and then purchased by Client 2. It is important that, despite the difference between the machines present in the previous report and the current report, Client 1 does not receive the email regarding their machine, since the difference is not related to the machines of interest to them, but rather to those of Client 2, who should receive the updated report.

Therefore, it is crucial to ensure that, even if the control analysis has highlighted differences from previous reports, the recipient user is among those designated to receive the control email.

Figure 5. Filter in which recipients are matched with those from the control analysis

A filter has been created based on the results of the control analysis, where recipients are matched with those from the control analysis (Figure 5). At this point, we can create our agent.

Creation of the agent

Figure 6. The condition checks that there is at least one row in the analysis based on the same analysis

Let’s create the agent based on the last analysis created. The condition (Figure 6) checks that there is at least one row in the analysis from Section 2. After that, the report is forwarded as a PDF attachment to the associated recipient.

Figure 7. Settings to return rows relevant to the recipient user listed in the analysis column

It is important to remember to send only the rows relevant to the recipient user listed in the analysis column (Figure 7).

Conclusion

In conclusion, we have successfully completed the challenge of ensuring an automated system for sending reports on machinery expirations, customized for each client. Through careful analysis of the differences between previous and current reports, and by implementing targeted filters and controls, we were able to exclude redundant information and send only relevant data to the appropriate recipients.

It is essential that users receive only important information to maintain their attention and reduce the risk of confusion or disinterest. This solution not only improves efficiency in managing expirations, but also ensures that clients receive exclusively the information relevant to their machinery inventory.

Thanks to Oracle Analytics, we have been able to optimize the communication process.