Quick Guide to Creating a New Stat Data Object

Intended Audience: Stat for PeopleSoft Administrators
Intended Subject: Stat Data Object Creation – A Quick Start/Use Guide
Intended Question: Have you ever created a Stat Data Object?
Unintended Question: Do you really want to read all that documentation on Stat Data Objects (even though you know you should)?

Intended Reaction: Happy Stat Administrators and End Users

Introduction:

In this blog, we will review the need, creation and use of Stat Data Objects. The intent of this blog post is to get you using, and benefiting from, a part of Stat which many are not comfortable broaching.

When it comes to versioning, managing, and migrating specific rows of data using a tool other than a standard SQL tool, it can be a bit intimidating. This blog will walk you through the creation of a new
Stat Data Object item.

With a Stat Data Object, you can version these rows of data, and migrate these rows of data as needed. These are great for Developers and Testers to be able to run processes and
recover their testing data quickly with a Drag-&-Drop. These are also good if you have a “Config” type environment where you test out different variations of configuration, such as Chart of Accounts,
and wish to migrate the finished data.

For some, these can be a bit convoluted. However, we are seasoned PeopleSoft DBA’s and PeopleSoft Administrators. We are the Brave! We are the Valiant! We are also the Stat Administrators.

Data Sought:

Here’s the data we want to allow our users to access: Any rows of data within the PSURLDEFN table. Perhaps this data is setup in a CONFIG type environment and we want to be able to Version, Manage
and Migrate it from environment to environment. Or, perhaps when a Database Refresh happens, we want to be able to quickly recover it.

Here’s a sampling of the PSURLDEFN table data that we want:

The first thing you want to do is get the Table Layout for the data you are after.

Here’s the PSURLDEFN DESC Call to see the Table Layout: You can see this by either Loading a SQLPlus Window or a TOAD session.

DESC SYSADM.PSURLDEFN;

URL_ID VARCHAR2(30 BYTE) NOT NULL,

VERSION INTEGER NOT NULL,

DESCR VARCHAR2(30 BYTE) NOT NULL,

URL VARCHAR2(254 BYTE) NOT NULL,

ICLIENT_SERVERFLAG VARCHAR2(1 BYTE) NOT NULL,

OBJECTOWNERID VARCHAR2(4 BYTE) NOT NULL,

LASTUPDDTTM TIMESTAMP(6),

LASTUPDOPRID VARCHAR2(30 BYTE) NOT NULL,

COMMENTS CLOB


Here’s the SQL Statement we will mimic within Stat to capture, manage and migrate our Data:

SELECT ROWID, URL_ID, VERSION, DESCR, URL, ICLIENT_SERVERFLAG,
OBJECTOWNERID, LASTUPDDTTM, LASTUPDOPRID, COMMENTS

FROM SYSADM.PSURLDEFN

Data Object Setup:

Within the Stat Main User interface, go to Maintenance > General > Data Objects.

Select the “PeopleSoft Objects” Tab. Select the “New” Button to Add a New Row to the Data Object Maintenance window:

Enter the name “PSURLDEFN_TBL”

Select “Apply”. When you select the “Apply” button, the Edit button on the top right of the window will be enabled.

Select the “Edit” Button in the top right corner.

Data Object Detail Setup:

When you enter the Data Object Detail Setup screen, you will see the following:

Step I: The Fields: Identifiers:

We will start in the “Identifiers” section on the top right portion of the window.

The first thing you want to do is enter the Field Name that you want the Query to select, or the fields in the table that you want to be able to version, manage and migrate.

The safest way to bring over the field/columns is to do a Cut & Paste from your above query. This way you are sure to enter the proper names as required.
Select the “Type” from the Type Drop-down. There are only three types available in the list; these include Number, Character, and String.

Step Two: The Table Definition:

Here we are working in the bottom section of the Data Object. Start by selecting the “New” button to the right and you are presented with a new row.
Stat will automatically post in the SQL Where Clause for you based on the fields that you entered above.

Enter in the Actual Table Name for the table from the query/desc statement above into the “Table Name” field and select the “Apply” Button.

Step Three: The Create Tables Definition:

Once you select the “Apply” button, the “Create Tables” button on the lower left hand corner will be enabled. Select the “Create Tables” button to continue.
When you select this button, a new window will appear.

From the “Reference Database” Drop-down, select the environment where you want to run the SQL Script for the temp table defined.
When you select the environment from the environment drop-down menu, Stat will display the SQL Script in the below “SQL” section of the screen and the “Run SQL” button is now enabled.

Select the “Run SQL” button. This will compile and run the SQL to create the named Temporary Table required within Stat.

If this is the first time you have selected the “Run SQL” button, it is expected that you will see an error message appear.

It will look similar to “Failure to drop table {Table Name}: Table or view does not exist. “ This is due to the “DROP TABLE” statement in the first line of the SQL. From the above screenshot, that table is named BO_P4_1, yours will be named differently. This is needed in the event that you make changes in the future and need to re-run the build.

When completed, you should see “Table {Table Name} created.” Once the SQL is run, select the “Close” button to close the pop-up. Then select the “OK” button back on the Definition Page to close the Data Object Definition window.

If all is well, you have just created your Stat Data Object. Let’s take it for a ride!

 

Using Our New Data Object:

To use our new and shiny Data Object, let’s create a new CSR, any type you’d like such as a Standard Change, or Emergency Fix. Enter in the required fields to create the CSR and assign it to a Developer so that we can get into the Version Control module.

Within the CSR, go to the PeopleSoft tab on the CSR; Select the “Advanced” button bottom right as you would normally to go into the Version Control module. Select the “Objects” tab; select an “Environment” from the Environment drop-down list.

Select the “Object Type” drop-down and you will now notice that Stat has added a new section. Instead of going directly into the “PeopleSoft Proprietary Objects” listings, you will now see that the first section you see is “PeopleSoft Data Objects”.

Highlighted below in Green is the new “PeopleSoft Data Objects” section added. Then you will see the available Data Objects that have been created and available to you.

Select the “PSURLDEFN_TBL” Data Object as you defined it above and to the right you will see the results of the configured SQL statement execute and returned rows that are now available to: Version, Manage, and Migrate.

You can add these Data Objects to your CSR by either double-clicking on the specific rows, or highlighting them and selecting the “Add Object” button on the right side of the screen.

  

In Closing:

If you have made it this far in the blog and you are not yelling at your computer, or me, then Congratulations! Great Job!!

Although the intent of this Blog Post was to give you a “Quick Start Guide”, please don’t forget about the hard work that went into creating the documentation in the Stat Administrator’s guide.

It’s an excellent resource and it’s my first ‘go-to’ place when I need answers.

Data Objects are covered in their own chapter in the Stat Administrators guide entitled “Data Object Maintenance”.

Thanks for reading!


Respectfully Submitted,

William R. Hart

Solutions Architect

Quest Software, Inc.

 

About the Author
William.Hart
Promoting a positive Stat User Community and enriching the value of the Stat investment. William has worked with the Stat application for over twelve years. He specializes in the areas of PeopleSoft and...