Hi @Mark Sollis @karl,
Thanks for the replies, to take the points in order:
1. Occasionally data doesn't go into IC tables. There's a blip in the connection or maybe there's a SQL error (usually from a trigger (which we no longer use)) which rolls the change back. A data refresh will push any missing records from OpenGI into SQL. I have also found a few instances of ghost records, where records are in SQL but not in OpenGI and the data refresh doesn't fix them. Presumably the check is only one way - I can't really see anything reading each row in SQL and checking OpenGI for it. So to get rid of those, I think we're going to have to reinstate a regular full bulk refresh. I don't actually know how long this will take with ICP, but hopefully not the 12 hours it was taking with regular IC towards the end.
2. There isn't one at the moment. We've been quoted 3 days for investigation / spec and 8-11 days dev cost to build it, which seems excessive given the relative simplicity of what's required.
3. Purging is a big problem for us. We're in discussions on the GDPR front now as the functions in OpenGI aren't really any good to us. In a nutshell - you have to purge transactions / documents for specific policies based on term date, but we don't want to do that. We want to delete all clients where their last policy expired over 7 years ago. However we want polices that relate to still live clients to remain, as well as any EL policies. We also want to keep prospects that are being worked on (we use Core for all clients for a bunch of reasons), so we add an extra requirement that the ToB data on the client must be over 3 years old as well. There's another issue in deleting documents that you can't delete a one off letter without confirming them all first (which means that you can no longer read the content of them), which means you can't delete any policies / clients with a one off letter on them. Ideally I'd like to be able to mark clients and policies for deletion and when we press purge it just deletes all records relating to those references, transactions, documents, access history and so on. Then we could all set our own flags based on our personal criteria, via DB enquiry, or via X-Stream.
But for now... purging is out for us. Fair point you make though.
With regards to the file taking 12 hours to process, I'm pretty certain it's because it's doing a data integrity check on the 140 million rows as it's being constantly written to by ICP. The contention on the disks must be crazy.
@karl What bulk import process are you referring to? It can't be the full database refresh in that time, can it?
Kind regards
James