Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
479 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Dynamics Partnership 2 73
CRM, GP and SL 5 93
CRM 2011 MultiLine Field Problem 3 67
CRM 2011 Ribbon Workbench vs RibbonDiffXml 44 345
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

861 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