For any users who have a successful data warehouse extraction pipeline with Acumatica, I’m curious how you’re doing it? We’re running into issues with OData V4 excluding deleted records, so if they’ve previously been extracted from Acumatica into our data warehouse, we have no way of knowing they were deleted using OData V4 and they remain untouched in the data warehouse, which causes inaccurate data. So far, the best solution we’ve been given is to create a customization package to preserve deleted records on the affected DACs (such as SOOrder, ARInvoice, etc.), then create a GI to retrieve the deleted records into their own table in our data warehouse, then join the actual tables in our data warehouse with the deleted records tables to exclude them from downstream data models. This feels really clunky, does anyone else have experience with this issue? And how have you solved it?
+13Assuming you’re connecting to OData via a Generic Inquiry, does OData exclude deleted records even if you specify that the GI include them?
For some more context, the issue is that we’ve built our data pipelines using OData V4 which reads directly from the DACs and not from a generic inquiry. This is our preferred method since we don’t need to maintain 50+ generic inquiries for all the tables we need to extract from, particularly since columns might be added through customization packages and it would be very cumbersome to keep track of all those changes.
With OData V4, we can dynamically retrieve all columns each time and new columns are automatically handled in our data pipelines, however the issue is that OData V4 doesn’t support deleted records at all. We’ve been told that this is how the application layer of Acumatica is designed, so there’s not an easy fix for it.
With all of that, I figured I’d post here and see how others are handling extracting data to their data warehouses. Is everyone using OData V3 and generic inquiries exclusively? Or are people using a VPC instance of Acumatica where they have direct database access? Is anyone using OData V4, and if so, how are you handling these types of issues?
+13We’ve been told that this is how the application layer of Acumatica is designed, so there’s not an easy fix for it.
Understandable, but seems like it would be a commonly encountered issue.
Agreed, we’ve been given a workaround to use a combination of OData V4 and OData V3 as mentioned above but it just seems like there has to be a better way so I’m curious to see what others are doing.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.