image
CHAPTER
1
Understanding Data Management
image ata management is an integral part of maintaining a healthy PeopleSoft ecosystem. Data management consists of building, moving, archiving, and auditing data to ensure consistency and accuracy. A healthy, well-maintained system offers peak performance.
PeopleSoft Database Structure
A PeopleSoft database is partitioned into three layers: a database system catalog layer, a PeopleTools layer, and an application data layer (Figure 1-1).
image
FIGURE 1-1.   The three layers of a PeopleSoft database
Database System Catalog Layer
Relational database management systems (RDBMS) maintain a collection of tables known as the database system catalog (system catalog for short). These catalog tables, or metadata tables, contain significant amounts of information describing table and column definitions, index specifications, procedure instructions, view definitions, as well as many other types of data. Unlike other tables in the RDBMS, we will not interact directly with the system catalog. Rather, we maintain system tables through RDBMS-supplied tools and application programming interfaces (APIs). For example, to insert a new row into the RDBMS system table for storing views, you would execute SQL similar to the following:
image
To list all views in your database system catalog, execute the following SQL:
image
image NOTE
Although it is possible to maintain a PeopleSoft database using system APIs only, this is not recommended. Instead, Oracle recommends using PeopleSoft-specific maintenance tools. Using the correct tools will simplify your upgrade process and help you maintain a healthy system.
PeopleTools Layer
PeopleTools maintains a set of metadata tables very similar, and sometimes redundant, to the system tables. For example, the PeopleTools layer includes a table named PSRECDEFN. This table describes record definitions in a manner very similar to Oracle’s DBA_TABLES. Why the redundancy? PeopleSoft tables exist first as abstract ideas stored as metadata artifacts and second as physical tables managed by the system layer. This abstraction allows developers to model data structures first as field definitions and then as a collection of fields (aka record definition); finally, it allows developers to construct data structures as physical tables within the system catalog layer.
Another great reason for metadata redundancy is database independence. PeopleSoft applications run on several different database platforms. The PeopleTools abstraction layer allows PeopleSoft applications to run on multiple database platforms without maintaining separate designs.
Although the contents may differ, the structure of the PeopleTools layer is consistent across all PeopleSoft application product lines. Oracle maintains this structure through PeopleTools patches, bundles, and updates.
Application Data Layer
The application data layer forms the persistence architecture for application-specific data. Unlike PeopleTools tables, which stay consistent across PeopleSoft applications, application tables differ among applications. For example, the PS_JRNL_HEADER table within PeopleSoft’s Financials module might not make sense in the Human Capital Management (HCM) application, and therefore would not exist in an HCM application data layer.
Part I of this book focuses primarily on the management of this layer, setting aside the PeopleTools layer for Part II, and leaving the system layer to expert DBAs such as David Kurtz.
When reviewing tables with RDBMS-specific tools such as SQL Developer, we can differentiate system, PeopleTools, and application tables by their prefix. All PeopleSoft-managed table names use the prefix PS. Generally speaking, we can distinguish tools tables from application tables by the underscore (_) character; application tables always begin with PS_.
image NOTE
Some tables seem to be non-application tables but have the prefix PS_. Because they aren’t application tables, you might be tempted to think of them as tools tables. There is, however, another small set of records that belong to a category called common components. These are neither application nor tools tables.
Application Data Layer Management Tools
Oracle’s PeopleTools suite contains a handful of tools designed for data management: Application Designer, Data Mover, Data Archive Manager, and auditing tools.
Application Designer
Application Designer is a very important tool for building and maintaining a PeopleSoft database. Oracle’s PeopleTools developers use Application Designer to create and modify layer 2, PeopleTools tables. Application developers use it to define and change layer 3, application tables. Chapter 2 covers the data management aspects of Application Designer.
Data Mover
Data Mover is a tool used for importing and exporting data from PeopleSoft databases. Oracle uses it to deliver important PeopleTools and application updates. System administrators and developers use this program to migrate data between PeopleSoft instances. Chapter 3 will show you how to use this database-independent data management application.
Data Archive Manager
It is the nature of a database to store data. Even though a database has an unlimited appetite, we often work in an environment with limited resources. When databases grow too big for their storage disks and indexing is not improving performance, you have some decisions to make: archive or add more disk space? In Chapter 4, you will learn how to use Data Archive Manager, a tool that helps you effectively move data out of your main transaction processing tables.
Data and Configuration Auditing Tools
PeopleTools offers a variety of ways to maintain your data and data model. For example, Application Designer is a program for maintaining both the PeopleTools metadata and system catalog database layers. Other applications, such as database platform’s data definition language (DDL) tools, interact only with the database system catalog. In this book, you will learn how to use PeopleSoft’s audit features to reconcile the differences that may arise from the improper use of these tools.
Conclusion
This book is about tools—with emphasis on the plural. Even within the first few pages of this book we have identified several tools for your data management toolbox. Each has its purpose. To ignore one and favor another would be the same as a carpenter who uses only a hammer, ignoring his saw, planer, joiner, and other tools.
Often, the best tool is a new tool crafted by you that combines portions of other tools. Here is an example of a tool that joins system metadata and APIs with tools metadata to identify all views that reference the table PSOPRDEFN:
image
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset