Solved

How to prepare a fake database via SQL of a working database for testing Microsoft Dynamics GP & Development

Posted on 2011-09-20
12
477 Views
Last Modified: 2012-05-12
We placed this question on EE but didn't related to what we're working in: Microsoft Dynamics GP.  So if you see it repeated, it was a mistake.  Nevertheless, we'll would greatly appreciate EE opinion of the MS Dynamics Zone.

We would like to test our VB.NET developments on database with real data but not affect the production database.

We've been tweaking with the idea of making a copy of the production database, rearrange all sensitive data as: last names, addresses and invoices, payments amounts.

The problem we're having is the algorithm or logic of relating masters tables and their transactions tables.

What we're looking for is to have the same structure from the production database so we can developed on it.  In addition, for validating the apps, the values of agings, historicals, and also to have it square off with their related tables.

We would liek to go about using SQL script since our DB are in MS SQL.

Here's is an example of what we we're trying to do:

ACTUAL REAL DATABASE CONTENTS:

     Customer                          Address                      Invoice Amt    payment Amt
     ===================  =============== ========== ===========
     Johnny Walker Red Comp. Corner Drive 124, LA       15,000.00         12,520.00
     Big E LTD.                        Alba 9087, NY                    250.00             250.00
     Summers Inc                    Raton Hall, FL                  4,569.00              100.00

FAKE DATABASE CONTENTS FOR DEVELOPING & TESTING:

     Customer                          Address                      Invoice Amt    payment Amt
     ===================  =============== ========== ===========
     Johnny Walker Red Comp. Corner Drive 124, LA       15,000.00         12,520.00
     Big E LTD.                        Alba 9087, NY                    250.00             250.00
     Summers Inc                    Raton Hall, FL                  4,569.00             100.00
0
Comment
Question by:rayluvs
  • 7
  • 5
12 Comments
 
LVL 18

Accepted Solution

by:
Steve Endow earned 500 total points
ID: 36571888
Hi,

I don't fully understand your question, so I'll need you to clarify.

Regarding make a copy of your production database to serve as a "test" database, see KB Article 871973: (login required)

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYQLYTNSUKZPXKMUNVQOLNMMXKOTZMLWVNVSXTSYQPTMPOYSKP

However, regarding your statement:

"rearrange all sensitive data as: last names, addresses and invoices, payments amounts"

Can you explain why you believe you need to do that?  The real and fake data samples you provided appear to be exactly the same.

If the production data is extremely sensitive or confidential, then it is probably easier to develop using the TWO / Fabrikam database.  The data structures are the same, and there is generic sample data to work with.

If you have to have your production data for some reason, you can always use SQL updates to obfuscate the data by changing customer names and addresses.  But it is vastly more complex to try and change invoices, payments, or any transaction amounts because that will inevitiably cause the data to not reconcile properly.  And changing invoice, payments, or any amounts usually isn't as important if you've obfuscated the customer info.

When I have to work with copies of a customer's HR / Payroll data, I simply replace all of the employee names with "Sam Smith", and then update all records with the same address, phone, SSN, birthdate, etc., since those values don't matter for development and testing--only a unique employee ID is required.

If you could clarify what is driving your requirement to obfuscate the data, maybe I can provide more guidance.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:rayluvs
ID: 36571971
To shed some light to our question, we work with sensitive data and we subcontract programmer plus our own.  We don't want them to have access to the owners personas information, and less financial info.

To date, we have only limited to payroll info where we delete all transactions, telephones, address and history data.  Unfortunately when developing/tasting, the programmer has to enter data in order to make it work

You are correct, I didn't change how we want to have it; this is how should be:

     Customer                          Address                      Invoice Amt    payment Amt
     ===================  =============== ========== ===========
     Johnny Walker Red Comp. Corner Drive 124, LA       15,000.00         12,520.00
     Big E LTD.                        Alba 9087, NY                    250.00             250.00
     Summers Inc                    Raton Hall, FL                  4,569.00              100.00

FAKE DATABASE CONTENTS FOR DEVELOPING & TESTING:

     Customer                          Address                      Invoice Amt    payment Amt
     ===================  =============== ========== ===========
     Johnny Walker Red Comp. xxxxxxxxx                            25.00               20.00
     Big E LTD.                        xxxxxxxxx                            25.00               25.00
     Summers Inc                    xxxxxxxxx                             25.00               20.00

We need to do what you mention in your post about HR/Payroll data.  

In our case, the Employee ID has the Employees name fully.  That is if the name is JOHN J. SMITH, the employee code would be JOHNJSMITH.  

I like the way you go about it, can you give more info?
0
 

Author Comment

by:rayluvs
ID: 36571977
Reading your post again, I think that is the way to go, changing NAMES, ADDRESS, TELS, etc.  But how can we go about ID codes?
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 36575838
Hi,

If you are using the full employee name for the ID, that is a challenge.  Although you can easily change the employee ID field in the UPR00100 table, the problem is that all transactions linked to that ID would ideally need to be updated also.

If this is something you are serious about, my recommendation would be to purchase the CRG Changer product for payroll.  

http://www.crgroup.com/changer.php

It is an excellent tool that allows you to change employee IDs based on an Excel file template (old ID + new ID), and it changes the master record and all transaction records.

You could then just assign numeric IDs to the employees in your test DB, "0001", "0002", etc. to obfuscate the IDs after you change all identifying info in the test database.  In theory, your payroll data should then be meaningless.

Sure, the contractor might see that someone was paid $150,000, but Employee ID "0052" will have no meaning, and you will have updated the employee name to be "Employee 0052".

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:rayluvs
ID: 36576028
Understood.

We can't make any purchae right now but We are working in the script right and think we have all the pertaining tables.

Based on your comments please advice in the following:

- any consideration we should have when changing ID?
- why scheme, logic or algorithm you used to create the
  different  ID  code?
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 36576151
Hi,

I think the primary consideration is making sure you find all of the tables with the ID and update them with the new ID.  

If you have alot of employee records, I think you might want to create a temp table with the old and new ID values, that way you can do an update using a JOIN, instead of doing a ton of separate update statements.  Obviously delete the temp table when you are done or store it on another server so that the contractor doesn't see it.

I don't think the ID structure really matters--the more arbitrary, the better.  You could even make the ID = DEX_ROW_ID, which makes it very easy to setup, and would allow you to use UPR00100 as your ID temp table.  Just update all other tables first using the UPR00100..DEX_ROW_ID, and make UPR00100 last.

That should work well.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:rayluvs
ID: 36578436
Great idea!  Use dex_row value to replace the employID.  We can do that with all the custs, vendors, etc.  But can you elaborate on your comment "use UPR00100 as your ID temp table."?
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 36578499
Hi,

By 'temp table' I meant a table that will contain your real ID, plus the new fake ID.

But, the benefit of using UPR00100, RM00101, PM00200, etc. along with DEX_ROW_ID, is that it already contains both ID values--so it becomes your 'temp table'.  This is important because you can use it as part of an update statement with an inner join.

So instead of updating every customer ID individually like this:

UPDATE RM20101 SET CUSTNMBR = '12345' WHERE CUSTNMBR = 'SAM SMITH'
UPDATE RM20101 SET CUSTNMBR = '23456' WHERE CUSTNMBR = 'TED JONES'

You can instead use a single update statement for all records and all customers:

UPDATE RM20101
SET RM20101.CUSTNMBR = cust.DEX_ROW_ID
FROM RM20101 rmw
INNER JOIN RM00101 cust ON cust.CUSTNMBR = rmw.CUSTNMBR

You will just need to make sure that you do all of the transaction / data tables first, and then update the master table last.  So your last RM update would be:

UPDATE RM00101 SET CUSTNMBR = DEX_ROW_ID


Make sense?

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:rayluvs
ID: 36578648
Yes it does; great info!

One lat question, why first change the transaction tables and then at the end the master table?
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 36582071
Hi,

The master table serves as your mapping between the current ID and the new fake ID.  If you don't update it last, you will lose that mapping and will be unable to update any transaction tables for that module.

Make sense?

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:rayluvs
ID: 36582795
Yes very, thank you very much... We're working hard in the scripts in all tables.
0
 

Author Closing Comment

by:rayluvs
ID: 36582984
Excellent!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now