Open Forum

Expand all | Collapse all

Need ideas for tracking deletions of transactions after replication to a data warehouse

  • 1.  Need ideas for tracking deletions of transactions after replication to a data warehouse

    SILVER CONTRIBUTOR
    Posted 12 days ago
    My boss and the vendor currently building our data warehouse have approached me with a problem.  They are replicating SOP transactions to the warehouse before they are posted by accounting. They have not taken into account that transactions are sometimes deleted before posting and they are not seeing the deletion because GP deletes those records from the SOP tables instead of marking them as deleted or removed.

    I have been asked for solutions around this problem and the best I have come up with so far is not to replicate to the warehouse until after posting has occurred. That is not a preferred solution because of the speedy reporting we are being asked for by our operations and sales.  Also, I would like to remove this reporting from my production SQL server.

    So does anyone have any ideas or previous experience with replicating SOP data to a warehouse that may be deleted after the move?  I would love to hear any ideas.  Is there any way to keep track of deletes from the work SOP tables so they can be removed from a previous replication to the warehouse?

    ------------------------------
    Jeff Frye
    Systems Analyst
    Hunt Brothers Pizza, LLC
    Nashville TN
    ------------------------------


  • 2.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    Posted 11 days ago
    My suggestion: Don't delete... VOID!
    Voiding just changes the document type and then moves it to history. Therefore it's a simple sql lookup to see that it's technically deleted. Also it's just good practice in accounting as voiding over deleting will leave a auditable trail so you know that yes the document xxx existed... But was not completed due to x reason explained in the voiding process.

    ------------------------------
    Paul Dyer
    IT Director
    Hoffman Technologies (Dba: Item Grabber)
    www.itemgrabber.com
    ROCKLIN CA
    ------------------------------



  • 3.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    SILVER CONTRIBUTOR
    Posted 11 days ago
    I agree in principal and can do this in the accounting process, but I integrate a couple of thousand invoices per day from our field sales system and sometimes have had issues with the integration which required me to reverse and redo the integration.  It is pain to delete transactions from the six companies but the thought of voiding all these transactions one at a time sends shivers up my spine.

    ------------------------------
    Jeff Frye
    Systems Analyst
    Hunt Brothers Pizza, LLC
    Nashville TN
    ------------------------------



  • 4.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    Posted 11 days ago
    Jeff- You can turn off deleting Sales Documents in the Sales Document Setup by type. This would force the users to void.

    ------------------------------
    Shannon Mullins
    GPUG Academy Instructor
    Microsoft Practice Director
    Kraft Enterprise Systems
    Brentwood TN
    ------------------------------



  • 5.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    Posted 11 days ago
    Whether you want to or should change your GP process or not, you should throw this to the data warehouse process to verify and correct.  If the DW is getting unposted transactions, what process is verifying that it is the correct and final transaction.  The DW should have a flag to indicate which table the SOP was pulled from and if the source table was the unposted table, then it should update the record from the posted table or do something with the deletions.
    I don't know your accounting procedures, so maybe the DW is only pulling over shipped records...  this might be a bigger conversation than just looking for the deleted records.

    Good Luck,

    Douglas

    ------------------------------
    Douglas Meade
    Business Solution Architect
    BrandForces
    Fort Lauderdale FL
    ------------------------------



  • 6.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    GOLD CONTRIBUTOR
    Posted 11 days ago
    Jeff,

    I would keep the open transactions out of the Data Warehouse.  Typically, they are too volatile, as you're seeing.  Your operations reporting, and possibly your sales reporting, should be real-time (either directly connected to your GP databases or, if you have huge GP databases and heavy reporting traffic, connected to a shadow database that is refreshed throughout the day).

    That's why it's called a "warehouse".  It takes some effort to store it and then some effort to retrieve it.  It's not meant to be holding real-time data.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 7.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    SILVER CONTRIBUTOR
    Posted 11 days ago
    This was exactly the point I made in the conversation but I was unable to overcome the inertia of having that youthful data in next-day reports.  I will pass along that someone else agreed with me.

    ------------------------------
    Jeff Frye
    Systems Analyst
    Hunt Brothers Pizza, LLC
    Nashville TN
    ------------------------------



  • 8.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    TOP CONTRIBUTOR
    Posted 11 days ago
    I don't know who built your data warehouse, but this method is just flat out wrong.  The number one concern for reporting MUST be accuracy and that means understanding your data.  I have twenty years under my belt as a DBA and the only way they could have written this type of "data warehouse" was using triggers rather than a standard ETL process.  This puts a lot of load on your database you don't need - aside from creating real data headaches like this one.

    If they want reporting which shows both posted and non-posted transactions and is timely, that can be accomplished by simply reading from the base tables (or reading from the base table for the unposted transactions and the warehouse tables for posted ones).  We do it here all the time.  That way people can see which transactions are still "transient" i.e. unposted.  A data warehouse should be refreshed using an ETL process which can handle changes to the underlying data such as voided records, deleted records, etc., but the data warehouse should NOT be picking up unposted transactions under any circumstance I can think of.

    I agree with you that a data warehouse ideally lives in its own database on its own server with its own resources.  The reason for that being that you want to impinge on your production servers as little as possible - that one time being your nightly data load to the data warehouse.

    I think one of the things I would do is go back and find out just exactly why your salespeople feel they need to be constantly refreshing their reports with fungible data.  All this is really doing is giving them a false sense of where they really are.  Bad data leads to bad decision-making.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



  • 9.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    SILVER CONTRIBUTOR
    Posted 11 days ago
    Agree wholeheartedly, but the warehouse is not built, it is being built and the consulting engineer is the one who pointed out the problem with the strategy my company presented to him.

    ------------------------------
    Jeff Frye
    Systems Analyst
    Hunt Brothers Pizza, LLC
    Nashville TN
    ------------------------------



  • 10.  RE: Need ideas for tracking deletions of transactions after replication to a data warehouse

    TOP CONTRIBUTOR
    Posted 11 days ago
    Then there is still hope that sanity and good design will prevail.  Maybe helping the sales people to understand that the data warehouse will process nightly and give them an up-to-date picture that is at most 24 hrs old...

    Good luck!  Having built a data warehouse, it isn't a trivial process.  I applaud you and your design engineer for doing it right!

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role