PeopleSoft Audit Reports and Migration

I have always recommended the following reports to determine a clean migration especially when there are a ton of objects to be migrated (PS Upgrade, Application of Maintenance Packs or Bundles, etc).

Prior to application of the bundles execute the following.

1. SYSAUDIT
2. DDDAUDIT
3. ALTER AUDIT
4. UPGCOUNT (just prior to the actual migration)

The first three can be executed a few hours or days before the actual migration as long as you ensure that no changes are migrated or performed in to the target environment which might cause an impact on these reports. However the UPGCOUNT process has to run just prior to the actual migration because this provides a row count of every record in the DB.
You need to re-run the above reports after completion of the migration. The objective should be as noted below.

1. Ensure that the reports match
2. For any mismatch there has to be an explanation

Note 1:
UPGCOUNT might not match for all records after application of Maintenance Pack/Bundle/Upgrade. You need to identify what caused the change. You can denote “Bundle 12 – Data Mover script upgnnnn.dms” in your documentation. This exercise will ensure that you have a clean migration.

Record	Before	After	Comments
PSPRCSRQST	10002	10008	Bundle # 11 – Execution of SQR program in step 21, 22, 29.
BENEFIT_PARTIC	103110	103212	Bundle # 12 – Execution of data mover script upgnnnn.dms

Note 2:

It is a good practice to ensure that you have a clean SYSAUDIT, DDDAUDIT and ALTER AUDIT report.

Read More About  PeopleSoft Audit Reports

Rise of Data Transformation Services – DTS Packages

Interoperability and communication between applications plays a vital role in today’s emerging business needs.One such component which helps the business to transfer data between different system is the Data Transformation Service Packages famously known as DTS packages.This pasage will cover the differences between SQL Server 2000 and SQL Server 2005 with respect to the DTS Packages.Enterprise Manager of the Microsoft SQL Server 2000 provides an inbuilt menu within its IDE and the dts packages are accessed using the SQL Server 2000 DTS Designer IDE.

The DTS Designer 2000 allows us to perform the following tasks over a package such as 1. Designing 2. Executing and 3.Scheduling.

In SQL Server 2005, the DTS packages cannot be accessed the same way as we use to access it in SQL Server 2000. The reason being the advent of Microsoft SQL Server Integeration Services (SSIS) in the 2005 version. The two main IDE of the SQL Server 2005 are

• SQL Server Management Studio, and
• SQL Server Business Intelligence Development Studio.

Unlike the DTS Designer 2000 that was used to access the DTS Packages, the SQL Server Business Intelligence Development Studio supports the packages with its SSIS. Microsoft also provides a backward compatibility method of accessing the DTS Packages that were created in SQL Server 2000. Even after the migration of the database from SQL Server 2000 to SQL Server 2005, the DTS Packages which were originally created in the SQL Server 2000 environment can also be migrated to the 2005 Server. Since the DTS Packages are Server specific, by 1. opening the SQL Server 2000 Enterprise Manager and by 2. accessing the SQL Server Management Studio, the DTS Packages can be opened by DTS Designer 2000 through the SQL Server 2005 environment.

Read More about  Data Transformation Services(DTS)

Business Intelligence Utopia – Enabler 4: Service Oriented Architecture

Service Oriented Architecture (SOA) and its closest identifiable alter-ego “Web Services” is another example of hyped-up, much maligned technology buzzword that takes at least 2 or 3 slides in any “bleeding-edge” technology presentation. Having said that, whatever I have investigated on Service Oriented Architectural concepts till now, is enough to warrant its listing as enabler no. 4 for Business Intelligence Utopia.

There are many powerful ways through which SOA can add significant value to the BI environment. The kind of BI, performance management and data integration artifacts that can be developed and published as web services include: Queries, Reports, OLAP slice services (MDX queries), Scoring and predictive models, Alerts, Scorecards, Budgets, Plans, BAM agents, Decisions (i.e., automated decision services), Data integration workflows, Federated queries and much more. You can get more information at the link: http://www.b-eye-network.co.uk/view-articles/4729

But the idea that fascinates me with respect to Business Intelligence on SOA, is the concept of “Analytical Smorgasbord”. Imagine a scenario where the business user can assemble their own analytical components from a mélange of available ones, resulting in complete customization of information for the user to take his/her decisions. Each of these available analytical components is self-contained and performs a particular piece of BI functionality. These components are ‘Web-Services’ and the SOA in such an enterprise is all about –

a) How are these components created?

b) How do the components interact?

c) How is the information published and consumed, in a secure manner?

The concept of “Analytical Smorgasbord” truly empowers the business users and is a powerful way to enable, what Gartner terms, as “Information Democracy” in the enterprise. It is important to note that the concept of analytical aggregation changes the Data Warehousing paradigm in a profound way – From “Pulling data” to “Seeking data”. In more simplistic terms, the end-user analytics should go and fetch data wherever it is rather than expecting all data to be consolidated into one data repository (typically a data warehouse or data mart). More on this in future posts, under the topic of “Guided Analytics”.

The true intent of this post is to encourage the BI community to start looking at SOA from the end-user analytical standpoint, so that web-services does not remain a mere technology toy but really helps in “Putting the business back in BI” – http://www.tdwi.org/Publications/display.aspx?id=7913

I have intentionally left out the technology details related to SOA. You can find wonderful resources on the web like this one: http://www.dmreview.com/portals/portal.cfm?topicId=1035908 It is becoming increasingly important for BI practitioners to acquire/develop knowledge on Web technologies, XML, SOAP, UDDI, etc. as different domains are converging at a rapid pace..

Enabler 4 in the “Power of Ten” is more precisely defined as – Service Oriented Architecture enabling the creation of BI “Analytical Smorgasbord”.

You might want to read these awesome related posts Business Intelligence Utopia

Creating OPRID clones at the speed of light!!!

In my development environment, I had a request to create 30 new OPRID’s cloning an existing developer id template. I was not interested in logging into PeopleSoft, navigating to PeopleTools > Security > User Profiles > Copy User Profiles, and creating the clone 30 times. Hmm…

So here is what I did.

1.Create a dummy table

create table hx_clone_ids (oprid varchar2(30));

2.Insert all desired OPRID values. For example,

INSERT INTO HX_CLONE_IDS SELECT ‘HX_NPAI_’||ROWNUM FROM PSOPRDEFN WHERE ROWNUM < 31;

3.Execute below SQL’s (OPRID to be cloned is NPAI).

INSERT INTO PSOPRALIAS

SELECT

HX.OPRID,

OPRALIASTYPE,

OPRALIASVALUE,

SETID,

EMPLID,

CUST_ID,

VENDOR_ID,

APPLID,

CONTACT_ID,

PERSON_ID,

EXT_ORG_ID,

BIDDER_ID,

EOTP_PARTNERID

FROM PSOPRALIAS A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSOPRDEFN

SELECT

HX.OPRID,

VERSION,

OPRDEFNDESC,

EMPLID,

EMAILID,

OPRCLASS,

ROWSECCLASS,

OPERPSWD,

ENCRYPTED,

SYMBOLICID,

LANGUAGE_CD,

MULTILANG,

CURRENCY_CD,

SYSDATE,

ACCTLOCK,

PRCSPRFLCLS,

DEFAULTNAVHP,

FAILEDLOGINS,

EXPENT,

OPRTYPE,

USERIDALIAS,

LASTSIGNONDTTM,

LASTUPDDTTM,

LASTUPDOPRID,

PTALLOWSWITCHUSER

FROM PSOPRDEFN A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PS_ROLEXLATOPR

SELECT

HX.OPRID,

DESCR,

HX.OPRID,

EMAILID,

FORMID,

WORKLIST_USER_SW,

EMAIL_USER_SW,

FORMS_USER_SW,

EMPLID,

ROLEUSER_ALT,

ROLEUSER_SUPR,

EFFDT_FROM,

EFFDT_TO

FROM PS_ROLEXLATOPR A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSUSERATTR

SELECT

HX.OPRID,

HINT_QUESTION,

HINT_RESPONSE,

NO_SYMBID_WARN,

LASTUPDDTTM,

LASTUPDOPRID,

MPDEFAULMP

FROM PSUSERATTR A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSUSEREMAIL

SELECT

HX.OPRID,

EMAILTYPE,

EMAILID,

PRIMARY_EMAIL

FROM PSUSEREMAIL A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSROLEUSER

SELECT

HX.OPRID,

ROLENAME,

DYNAMIC_SW

FROM PSROLEUSER A, HX_CLONE_IDS HX WHERE ROLEUSER = ‘NPAI’;

 

Read More About OPRID Clones