Dynamics Community Forum Thread Details

Data Warehousing - identifying additions

We are importing data from Dynamics GP databases, along with data from other ERP systems, into a central data warehouse.

Data is copied via SQL stored procedures, using a linked server connections.

I am looking for guidance about how to identify new or changed transactions in the key tables.

I have scoured the net for a while but haven't found anything, and our consultants haven't been able to provide many answers.

I would appreciate any guidance that you can offer or any link to reference documents that can assist with this.

Below are some details of what we are currently doing.

Thank you.

Masterfile tables

I am re-loading GL Account, Inventory, Customer and Supplier Masterfile tables and also the open Sales Order tables every day but I can't afford to re-load the transactional tables given the amount of data involved.

GL20000 and GL30000

I am currently using DEX_ROW_ID to identify new transactions, but I need an alternative. I have come to realise that the DEX_ROW_ID values are not reliable, particularly after an upgrade.

Is the PSTGNMBR reliable as a sequencer, even if users post to old periods (re-open closed periods then post?

SEE30303

Is the GLPOSTDT a reliable identifier for this table? If I import transactions with reference to the highest value previously imported, I am not going to pick up transactions backdated, will I?

POP10500, POP30300 and POP30310

I have added these tables most recently, linking them together with reference to the GLPOSTDT field on the POP30300 table.

I am deleting the last 14 days of transactions imported and then importing all transactions with a higher GLPOSTDT than the remaining maximum. I've done this to pick up transactions that are backdated.

Is there a better way?

SOP30200 and SOP30300

I am currently using DEX_ROW_ID as I haven't found a more reliable option. Is there a better way?

IV00102

I am reloading this table every day. Is there a reliable way to pick out changes?

RM30101

I am currently using DEX_ROW_ID as I haven't found a more reliable option.

Quick Links