Full version  •  Quick installation  •  No spam
Download now

Tech Talk


Initial Implementation Setup Data

Last week's discussion considered a few of the different types of setup data, along with how (and where) they should be created and migrated. We saw that the different types must be handled in different ways, and that it's probably next to impossible to set a policy that covers all types. This week I'd like to talk about handling setup data during the initial implementation.

Before a system is rolled into production for the first time, you have a little more flexibility in how you deal with these issues. Let's look at a few options.

First, we assume that you have (at the very minimum) two environments: A "demo" environment (which I'll call DMO) and a "system" environment (SYS). These are delivered by PeopleSoft. SYS is a "blank" database that contains all of the tables but very little data. What it does contain is generally limited to delivered parameters in a few tables, plus, of course, the PeopleTools metadata. DMO contains the same data plus some sample setup and transactional data. DMO can be used to try out various features to see how the system works. However, you should not make major changes to either the setup or transactional data in this database, because it will be needed for reproducing bugs that you suspect originate in delivered code and that you are reporting to PeopleSoft.

In general, SYS is the starting point for your setup. (I have seen installations where many of the setup tables contain the "demo" sample data, indicating that DMO may have been used as the starting point. This is not the way to go.) SYS could be modified directly but is normally copied to one or more environments instead. It's best to keep DMO maintained and up to date with all patches you apply. Similarly, SYS should be maintained, at least during the early stages of your implementation. Later on you may decide there is no further need for it.

Let's assume that in addition to DMO and SYS, you have created one or more environments. The simplest option might be to have one environment that you use for development and that also will be the future production database. There would be serious drawbacks to this option, however. First, your ongoing custom development could interfere with testing of already-developed features. (An aside: I know that you intend to "go vanilla" with your implementation. Everybody wants to do this, but nobody actually does it.) Second, your testing will litter the database with test data that must be cleaned out before the system goes live.

Instead, let's assume that you create, at minimum, a development environment, a testing environment, and a future production environment. I'll call them DEV, TST and PRD. Custom code and objects would start out in DEV, be unit tested, and then be migrated to TST. There you would perform your various integration, system and regression tests. Finally, the objects and code would be migrated to PRD. When you are ready to go live with the system, you make PRD available to your users. Simple.

But what about your setup data? Our first idea might be to follow the same path, entering the configuration into DEV and then migrating to TST. But Application Designer doesn't migrate setup data. We could enter the same data into DEV, TST and PRD, but that might be difficult if there is much to enter—and how do you know that you have done exactly the same data entry into each environment? If you entered the data correctly in DEV and TST but made a mistake while entering it into PRD, you would not see the problem until the system is live. Also, as we will discuss in future topics, there may be good reasons to have many more environments than the ones assumed here.

Your more functionally oriented team members are probably the ones who are entering most of the setup. Why not let them enter this into TST and then, when all testing is complete, enter the final configuration into PRD? The same issue applies—we don't know if they will accurately enter all of the setup. Could we copy TST to create PRD? Yes, but then all of the testing data must be cleaned out—and how will you know where all of it is?

Could we use Data Mover or SQL scripts to move the setup from one environment to the next? Yes. Under this method, you might want to have the users enter it into TST, conduct testing, and then use these scripts to migrate the data to DEV and PRD. However, there are several issues with this method. The first hurdle is to determine which tables must be moved. Once you do that, there is another major issue: What happens if the scripts are not accurate and complete? TST will be fine—the users will have run many successful tests—but PRD won't work and you won't notice this until the system is live. You could avoid this problem by entering the setup in DEV and then copying it to TST and PRD. The first migration (to TST) will give you a chance to test the actual migration process. However, this may not be the best solution either. In many cases, developers will need to modify the setup to create unit testing scenarios. If this happens, bad setup data could be moved to your future production database. Also, if your system test is not thorough enough, you may not notice that the data migration was not complete.

There is not necessarily one best way to avoid all these problems, but the method I'm going to suggest now has worked well for me in the past. Have your functional team members enter initial setup data into TST, and try out setup scenarios that they may not be sure of. Once the setup has been settled, have them enter the final data in PRD (future production). Then create the SQL and/or Data Mover scripts, and move the data from PRD back to TST and DEV.

What does this accomplish? First, you are using the GUI to enter your setup in PRD. This is the safest way to enter it. Your migration scripts eliminate the need for double or triple data entry. Testing hopefully will uncover any errors in your migration script, but if one or two errors are missed due to an incomplete test, at least the setup in PRD is still complete. (You may never know that there was a bug in the script, but that won't affect production.) You will normally find several problems with the initial script, but these can be cleaned up quickly. You could run this script on a scheduled basis or whenever significant changes have been made to the setup in PRD. Even though your functional users may tend to ignore DEV, the script will continually refresh the setup in DEV with the latest version.

The big question now is how we determine which tables are to be moved. Ask the users for a list, but understand that it will be just a starting point. The users will not necessarily know all of the tables affected, and they will forget to tell you about a few. To get a full list, what I've done is to compare PRD with SYS. Remember that SYS was used to create PRD in the first place. Since you will allow no testing to be done in PRD, the only differences between SYS and PRD will be the setup data, custom objects, and a few run control rows or temporary tables. You can get a good feel for what tables have been affected by running row counts against all tables in each database and then using a file compare tool to find the differences. For a more complete indication, write a tool or use an existing utility that compares the data field by field.

The row-count comparison is simple to write in SQL. The full data compare is about a one-day programming project using SQR; I'll talk about that in a future column. The SQL needed to copy a table depends on your database platform and will be another future topic. (In general, you want a statement similar to INSERT INTO thisdatabase.PS_TABLENAME SELECT * FROM thatdatabase.PS_TABLENAME.) Why not use Data Mover for everything? If you are customizing a table by adding or deleting fields, that won't be possible.

Once you have your list of table differences, look it over. Be careful about unusual situations, such as the INSTALLATION table in HR (which is not only a setup table but also contains transactional data: the last EMPLID assigned). Do not bother with PeopleTools tables, run control data, process monitor tables and such.

Well, that probably sounded more difficult than it is. But as always, if you have a better solution to the problem, I'd like to hear it!

PSSQRWhen 1 > 1 (or even 9999) 10-31-2014
SQRSQR colors 08-11-2014
SQRSQR lines, boxes and images 08-10-2014
SQRSQR one-point coordinate grid 08-09-2014
SQRSQR proportional fonts 08-08-2014
SQRSQR print positioning 08-07-2014
SQRSQR reports: The basics 08-06-2014
PS Fear of customization 07-18-2014
SQRSQR variable names 07-02-2014
PS Concatenating strings in PCode05-12-2014
PS HTML and server communication 05-05-2014
PS JavaScript in HTML areas 04-21-2014
PS Using HTML areas 04-18-2014
PS The HTML object 04-11-2014
PS Types of HTML areas 03-14-2014
SQREmbedded SELECTs in SQR 01-31-2014
PS Exporting all PeopleCode 11-30-2013
PS Disabling autocomplete 09-29-2013
SQRSP Debugger for SQR update 09-12-2013
General Stopping... and restarting 09-02-2013
SQRSQR debugger version 2 09-29-2010
SQRA few SQR items 05-20-2009
PS Ignoring "component changed" 05-07-2008
PS App Engine 04-30-2008
PS Commenting code 04-02-2008
PS The "Powered By" page 03-05-2008
PS Formats in PeopleCode vars 01-30-2008
PS Page layout with group boxes 01-09-2008
PS Meta-SQL wrappers 12-19-2007
PS Derived record PeopleCode 12-05-2007
PS A Cancel button 11-28-2007
General "Leading" the test effort 11-07-2007
SQRTemporary tables in SQR 10-31-2007
SQRUpdating long character data 10-24-2007
PS Record.Field specification 10-17-2007
PS Migrating project definitions 09-26-2007
PS Derived records 09-19-2007
PS Positioning elements on pages 08-29-2007
General Thanks 08-22-2007
SQRSQR "code alerts" 08-08-2007
PS Styles in HTML Areas 08-01-2007
SQRDynamic SQL for performance? 07-25-2007
PS Page Field Name 07-18-2007
PS PeopleCode event order 06-27-2007
SQRFormatting Excel using HTML 06-06-2007
Component-changed state 05-23-2007
PS Selecting styles for page fields05-16-2007
SQRSending email from SQR 05-02-2007
General Contractor networking 04-25-2007
PS Project X--and another project04-18-2007
PS Effective and data entry dates04-11-2007
PS A PeopleSoft home page 04-04-2007
PSSQRHyperion and the future of SQR03-21-2007
General The data doesn't lie 03-14-2007
PSSQRHyperion acquisition 03-07-2007
General Taking ownership 02-28-2007
PS Source control 02-21-2007
PS Sandboxes 02-14-2007
PS Migration instructions 02-07-2007
PS PeopleSoft environment usage 01-31-2007
PS Multiple environment strings 01-24-2007
PS Multiple testing environments 01-17-2007
PS PeopleSoft support environments 01-10-2007
General A single source of truth 01-03-2007
Nothing Smile 12-27-2006
PS Navigating the component buffer 12-20-2006
PS JavaScript and styles 12-13-2006
PS SQL for common HR queries 12-06-2006
SQRSQR program complexity 11-29-2006
SQRFrequent SQR questions 11-22-2006
PS Followup 11-15-2006
PS Expanding subrecords 11-08-2006
PS Bind variables 10-25-2006
PS Attaching objects to projects 10-18-2006
PS Partial transaction completion10-11-2006
PS The multi-process component 10-04-2006
PS Employee rows lost to security09-27-2006
SQRSQR document sections 09-20-2006
PS Empty-string compares in SQL 09-13-2006
SQRNulls, blanks, empty strings 09-06-2006
General Company policy 08-30-2006
PS Auto-lookup exclusion 08-23-2006
PS Effective status 08-16-2006
PS A couple of HRMS/HCM utilities08-02-2006
PS Translate value changes 07-26-2006
General Updating views 07-19-2006
General Set-based updates 07-12-2006
PS SQL set-based processing 07-05-2006
Being happy about bugs 06-28-2006
Busy in the PS marketplace 06-21-2006
General 2-way documentation 06-14-2006
PS Interfaces as views 06-07-2006
PS Hijacked fields 05-31-2006
General Agile software development 05-24-2006
General Working within Waterfall 05-17-2006
General Over the Waterfall 05-10-2006
PS Experimentation in PeopleSoft 05-03-2006
PS Unlimited enhancements? 04-26-2006
PS Testing direct DB updates 04-19-2006
SQRDelayed evaluation of SQL 04-12-2006
PS Database compares 04-05-2006
General Support problem? Call FBI! 03-29-2006
SQRFinding SQR errors 03-22-2006
PS Finding PeopleSoft Queries 03-15-2006
PS PeopleSoft Query support 03-08-2006
PS JOB table and SetIDs 03-01-2006
PS Migrating the project 02-22-2006
PS Compare and Report options 02-15-2006
PS Comparing and migrating projects 02-08-2006
PS Object security in App Designer 02-01-2006
PS Automation of App Designer 01-25-2006
General Contractors on PS projects 01-18-2006
General "Smart coding" 01-11-2006
SQRDebugger tricks 01-04-2006
PS EFFDT and component processing12-28-2005
PS Referential integrity 12-21-2005
PS Menu navigation using SQL 12-14-2005
SQRA coding standards wish list 12-07-2005
PS More App Designer options 11-30-2005
PS Pages in App Designer 11-23-2005
PS Finding objects on the menu 11-14-2005
PS Magic search record fields 10-31-2005
PS Documenting object attributes 10-24-2005
PS Monitoring server performance 10-17-2005
PS Searching PeopleCode 10-10-2005
PS Speeding up App Designer 10-03-2005
PS Using the message catalog 09-26-2005
PS Good ideas that sometimes aren't09-19-2005
PS And now Siebel 09-12-2005
SQRDate comparisons 09-05-2005
PSSQRFTP options 08-29-2005
PSSQRMoving code across environments08-22-2005
PS Spring cleaning 08-15-2005
SQRBuild your own SQR "IDE" 08-08-2005
PS Change control in App Designer08-01-2005
PS App Engine: disadvantages 07-25-2005
PS App Engine: advantages 07-18-2005
SQRCoding Excel file output 07-11-2005
General Creating Excel files 07-05-2005
PS Dave's next move 06-27-2005
SQRSQR variable oddities 06-20-2005
SQRSQR arrays 06-13-2005
SQRCompiling SQRs 06-06-2005
PS 5-character SETIDs 05-30-2005
PS Use all of the keys 05-23-2005
SQRDynamic SQL in SQR 05-16-2005
PS Running the in-place conv 05-09-2005
PS Searching for fields in DB 05-02-2005
PS Converting setup data 04-25-2005
PS Associating effective-dated tables 04-18-2005
PS Upgrading without an upgrade 04-04-2005
PS Scheduling patches 03-28-2005
PS To patch or not? 03-21-2005
PS Basic tools 03-14-2005
SQRWay back with 03-07-2005
PS Meta-SQL 02-28-2005
PS Dropping the user index 02-21-2005
PS User indexes 02-14-2005
PS Page changes in App Designer 02-07-2005
General Generate SQL from SQL 01-31-2005
SQRSQR report viewer 01-24-2005
General Bug or enhancement? 01-17-2005
PSSQRTagging objects and code 01-10-2005
General Issue tracking 01-03-2005
PS PeopleSoft 10? 12-27-2004
PS Purging tables 12-20-2004
PS Acquisition 12-13-2004
PS Exporting PCode as text 12-06-2004
PS Simplify SQR code 11-29-2004
PS New site for SQR info 11-22-2004
PS Comparing objects 11-08-2004
PS Applying SQL-from-SQL 11-01-2004
General Generating SQL with SQL 10-25-2004
General Generating SQL 10-18-2004
PS Scrambling data 10-11-2004
PS What's going on with PS? 10-04-2004
SQRPrint positioning in SQR 09-27-2004
PS Oracle vs. PeopleSoft 09-13-2004
General Word, email, and lava lamps 09-06-2004
PS Custom run control: tips 08-30-2004
PS Custom run control: building 08-23-2004
PS PeopleCode, behind the scenes 08-16-2004
PS Language-specific pages 08-09-2004
SQRUsing substitution variables 08-02-2004
SQRSQR substitution variables 08-02-2004
PS HTML areas 07-19-2004
SQRRecursion in SQR 07-12-2004
SQRSQR code formatting 07-06-2004
SQRDebugger beta release 06-28-2004
PS Application Designer tidbits 06-27-2004
PS Configuration for custom SQR 06-14-2004
PSSQRModify or clone? 06-07-2004
PS Identifying custom objects 06-01-2004
PS EFFDT again 05-24-2004
General Change-only interface: impl 05-17-2004
General Change-only interface: concept05-10-2004
PS Cache troubles 05-03-2004
PS Component PeopleCode 04-25-2004
PS EFFDT and EFF_STATUS 04-18-2004
PS SETID in subselects 04-11-2004
PS Denormalized tables 04-04-2004
PS Documenting retired custom 03-28-2004
PS Sharing login IDs 03-21-2004
SQRGlobal and local SQR variables03-14-2004
SQRDISPLAY and MOVE 03-07-2004
PSSQRRun control in SQR 02-29-2004
SQRCommon sources of SQR bugs 02-22-2004
PS Implementation setup data 02-15-2004
PS Managing setup data 02-08-2004
PS Managing objects 02-01-2004
PS Make a home page 01-01-2004