Leverage PeopleSoft EPM Data Lineage Spreadsheets

Not having to do all the leg work necessary to arrive at table-level data lineage in the PeopleSoft EPM Warehouse is a great testimony to good documentation and a great culture back there at big PS, for those of you that enjoy this benefit, here’s some suggestions on how to leverage it:

1) [Re]organize the excel spreadsheet so that it reflects the sequence of your ETL schedule and if time allows add simple job dependencies.

2) Yes, consolidate all the lineage worksheets into a single centralized list (or at least one per business domain [hr, fin, scm]).

3) Add a second worksheet to your consolidated lineage file to track hash file dependencies and read/write operations.
3) Add a third worksheet to track look up operations and their keys.

The benefits of this approach far out weight the initial investment, when you realize how tedious this task is, just keep in mind all those hours that you’ve spent in agony debugging DataStage programs in the past. This spreadsheet is a real time saver when trying to find our where a particular column came from or what jobs perform write operations on the sequential file that is messing up your dimension.

While I work on it I keep my sanity thinking about the new episode of Scrubs next week and being able to make my deadline, I’m looking forward to a big celebration and a weekend in Vegas with my friends…

OBIEE Logical Table vs Logical Table Sources – Best Practice

The difference between Logical Tables, Logical Table Sources (LTS) and the physical tables that compose a Logical Table Source, here are some basic guidelines that have helped me get this concept to seat more clearly in my mind.

BMM Layer - Logical Table and LTS

Logical Tables like organization in the picture on the left are used to create drill down paths or dimensions like Dim_Organizaition.

Usually when you drag and drop a column from a table that is not currently being used in your logical table, the physical table containing such column gets added as a new Logical Table Source (LTS) such as UofA on the image on your left. This most usually is not the result you should be aiming for, in general, when a the column you are adding to a Logical Table comes from the same system of record you have been using you should just rename the LTS to reflect the name of the system of record in the same fashion we renamed ours to UofA and then go edit the sources for this LTS.

The confusion often stems from having what I would call “Logical Table Source” Sources (LTSS). The image below depicts them for UofA, you can acces this dialog by double clicking any LTS.

BMM - Logical Table Source Properties and LTSS

Physical Table FRS_GL_ACCOUNTS is a “Logica Table Source”  Source (LTSS) for Logical Table Source UofA. The rationale again is that UofA represents a system of record (source) that brings in information from more than one physical table to build our organization logical table.

If you still have questions add comments to this post and let’s get the discussion started

Mandating Metadata

Having seen countless hours spent in frustration begging for metadata from transactional systems Subject Matter Experts (SMEs) and DBAs I have two words for those planning a large system implementation “MANDATE METADATA!!!”, this is really easy to do and should be easy to get from either the ERP implementation team or your develpment team if you have decent professionals as counterparts (thanks God that is the case in my current implementation!).

For our PeopleSoft implementation I participated in a rather large group of people that conversed over four meetings about the data conversion process and how to use excel spreadsheets to capture column and table metadata as well as conversion details with the least expense and overhead to those in charge data conversion.

Let’s look at some of how this will work:

For technical metadata we will capture target and source table and column description and comments on excel spreadsheets along with any mappings from code table to code table so that a crosswalk would exist for codes values in the old system (lets say “A”) to code values in PeopleSoft (let’s say ‘A001′).

For conceptual metadata I still think we have a lot of work to do but I’m planning to have a conversation with the awesome HR implementation manager this coming monday so that we can start thinking of conceptual business definitions and update metadata sub categories for our HR subject area.

Drop [oracle] database (and clean after yourself)

Hey I’ve got to document this so I don’t forget how to cleanup after myself:

1. FIRST, find out which files to cleanup after dropping the database thanks to geekinterview.com
   select * from dba_data_files;
   select * from v$logfile;
   select * from v$controlfile;
   archive log list
   initSID.ora
   In addition you can clean the UDUMP, BDUMP, scripts etc
2. Now go ahead and drop that datatabase (the one that’s already backed up):
   ORACLE_SID=<OAS Repository SID>
   export ORACLE_SID
   sqlplus /nolog
   conn / as sysdba
   shutdown immediate
   startup mount exclusive restrict;
   drop database;
   exit
3. Now go and cleanup those files!
   

Uninstall Oracle Application Server

Here’s my latest run on uninstalling Oracle Application Server 10.1.3.0 from a SuSE 10 Enterprise box:

Important: Be sure to backup your OAS instance and the OAS repository database before you proceed with these instructions.

1. Run oracle universal installer from the OAS home directory:

   $ORACLE_HOME/oui/bin/runInstaller

2. Click on “Deinstall products…” and select the oracle home where your application server resides.
3. Click on “Remove” and confirm you want to remove OAS on the dialog that pops up.
   Once the progress bar dialog disappears your application server binaries have been removed.
4. Backup the directories directly above your ORACLE_HOME directory
   ls $ORACLE_HOME/..
   cd $ORACLE_HOME/..
   mkdir oas_inst_bak1
   #modify accordingly
   mv OracleAS_1 inst_bak_1/
   mv OracleAS_2 inst_bak_1/
   mv OracleAS_3 inst_bak_1/
5. Log in to your  OAS repository database (the one that’s already backed up) and drop it.

P.S.: Complete instructions to be found on Oracle’s OTN at http://download.oracle.com/docs/cd/B32110_01/install.1013/b32198/deinst.htm#CHDHFGDA

PMO Documentation Templates

Yesterday I had the opportunity to take a first pass on desigining documentation and project management templates for our PeopleSoft implementation along with two of my co-workers, here’s some of the lessons we have learned as we started working with the KISS principle in mind:

APPROACH

• We are a state institution beginning a very large ERP implementation with limited resources so project controllership is a key role but we strive to keep resource overhead related to our documentation templates to a minimum.
• We started with a list of documents in mind but we also created a list of sample requiremens and project management activities ranging from “Implement HR Benefits Administration”, “Add XYZ account to chart of accounts” to “Add space to server logical volume”, this helped validate our list of documents could accomodate all kinds of activities and figure out some of the attributes/information that should be captured in all of them.
• Some of our initial documents are:
  • Statement of work / project plan
  • Fastrack Project Charter (for smaller projects)
  • Functional Requirements Speficication
  • Technical Requirements Specification

USABILITY

• We took a requirements gathering class at the TDWI Conference and the instructor provided a neat excel spreadsheet where input to certain columns was done via drop down menus, this is a very nice way to maintain consistency and drive standardization.
• We try no to include any non-essential information in these forms to minimize overhead and allow the form to be as generic as possible.

ACCESSIBILITY

• Becky and Mary have foudn a great way to make these templates available by creating a folder hierarchy that resembles project organization in a file share accessible to all members of the project team.

More to come on this as the project keeps rolling… enjoy  your weekend!

Infrastructure Reference

These are some useful references on PeopleSoft and PeopleSoft EPM Warehouse infrastructure components

OBIEE:

• PeopleSoftBits OBIEE Installation Post:
  Pending…
• Oracle Technology Network OBIEE Home:
  http://www.oracle.com/technology/documentation/bi_ee.html
• OBIEE 10.1.3.2 Infrastructure Setup and Configuration Guide:
  Pending…
• OBIEE 101 by John Minkjan:
  http://obiee101.blogspot.com/

MIDDLEWARE

• Oracle Application Server (OAS) Release 3 Documentation on OTN:
  http://www.oracle.com/technology/documentation/appserver.html
• Mike Lehmann on Middleware:
  http://mike-lehmann.blogspot.com/

PeopleSoft Reference

Compilation of useful PeopleSoft related materials I’ve found over time

PeopleBooks:

• PeopleSoft Enterprise HRMS and Campus Solutions 9.0 PeopleBooks:
  http://download.oracle.com/docs/cd/B40039_02/psft/html/docset.html

Blogs:

• Jim Holincheck (Gartner): http://blogerp.typepad.com/hcm_research/

Professional Organizations:

• PeopleSoft Higher Education Users Group:
  http://www.heug.org/

Presenting at the Oracle BI SIG Fall Desktop Conference 2008 this week

Hi!

For all of you reading some of the posts on the blog I want to take this opportunity and thank you for your visit while I invite you to attend the Oracle BI SIG Fall Desktop Conference 2008, it is an event that brings oracle product managers and BI practitioners to your desktop so that you can get the latest product announcements and see what other companies are doing for a moderate price and without having to spend money on transportation and lodging.

If you attend make sure to check out my two sessions on Strategic Planning and Metadata Management and if you don’t get a chance you can also check some of the slides in this blog’s presentations area.

Have a great day!

Abnormal termination of stage (cast those CLOBs!)

As I started running a bunch of our delivered DataStage ETL jobs I ran into a really undescriptive warning message in several Director job logs.  Most of tem failed on the IPC stage right after the DRS stage that was extracting data out of our source database with an “Abnormal Termination of  Stage” error. After several attempts to turn on tracing and trying to find a rational reason for the error I went into my source database and realized  that there were CLOB columns in every table that was having this issue. After some testing the issue was resolved by casting the CLOB columns into the varchar2 data type:

CAST(my_column AS VARCHAR2(4000))

Now, these were jobs that were delivered with our warehouse, the designers were probably trying to make them as general as possible, after some conversations with the warehouse team I now got authorization to replace the CLOB content with empty strings to save space as the data in those columns is of low value to our BI users.