The customer I worked with had a business requirement to keep some of the I3 statistics for a very long time. Changing the PW purge retention did not prove to be a good solution as the PW database size grew very fast. My solution for the customer's problem was based on the idea that the PW purge retention should not be changed. Instead, the important metrics from I3 should be copied and saved to a different schema. This solution also solves the problem of reporting against the I3 database. If one wants to create his own SQL against the PW database, one will face the challenge of knowing the internal structure of the PW. With my solution the customer only needs to query one table (no need for SQL statements with complicated joins). In addition there is no need for time conversion as data is stored in the customer local time zone and not in GMT.

The solution was delivered to the customer and is currently in production. The customer is using the solution to extract new data with a scheduled interval of two hours. Read a sample of the functional spec I created for this solution.