BI Implementation Enabler: Agile Framework for Data Warehousing – Part 1

As part of the Business Intelligence Utopia series, I am going to focus on the implementation enablers in the next few posts. The first implementation enabler is: Agile Framework for Managing Business Intelligence systems

BI systems are complex to manage due to the following reasons:

• Keeps Evolving over time – Enterprise DW can never be completely built
• BI drives business decisions – Needs “Total Alignment” with corporate vision
• Power of BI applications increases exponentially as the number of information consumers increases
• Data Warehouses need to be measured & calibrated against pre-set goals
• Development & Support has to be managed concurrently

Standard process methodologies like the Waterfall model, Spiral & Iterative models are not suitable for managing Business Intelligence systems. One methodology I have seen work very well, having used it in multiple projects at Hexaware, is the “Agile Methodology”. The philosophy of the Agile framework fits in very nicely to alleviate some of the complex issues in managing BI systems.

Agile Methodology – Definition

Agile development is a software development approach that “cycles” through the development phases, from gathering requirements to delivering functionality into a working release.

Basic Tenets:

• Shared Vision and Small Teams working on specific functionality
• Frequent Releases that make business sense
• Relentlessly manage scope – Managing the scope-time-resources triad effectively
• Creating a Multi-Release Framework with master plan and supporting architecture
• Accommodate changes “gracefully”

BI practitioners would appreciate the fact that the basic tenets do provide solutions to some of the critical pain areas when it comes to managing enterprise BI systems.

The ultimate goal of any DW/BI project is to roll out new business functionality on a regular and rapid basis with a high degree of conformance to what is already there –> à Fits in well with the “Agile” philosophy

The next few posts will illustrate the practical application of the Agile Framework to Business Intelligence systems.

BI Information Nugget – One of the recent websites that I really liked is: www.biblogs.com. This is a Business Intelligence blog aggregation site that has blogs written by seasoned BI practitioners. Happy reading!

Data Integration Challenge – Building Dynamic DI Systems – I

One other challenge in a data integration project as it is with any other IT system is to build a data integration environment that is agile and flexible enough to accommodate the system changes related to business rules. The benefit of building a dynamic system is that the code changes are less frequently done or completely avoided in many cases, so it is very much a de-facto process to look for design opportunities to build a DI system that is dynamic.

A dynamic DI system accommodates variation in the incoming data and is able to respond without system failures or mal-data processing, in many such scenarios the DI environment also gets to be controlled by the Business Team with lesser support from the IT team.

Following are some of the design aspects towards getting a DI system dynamic

1. Avoiding hard references, usage of parameter variables
2. 
   
3. Usage of lookup tables for code conversion
4. 
   
5. Setting and managing threshold value through tables
6. 
   
7. Segregating data processing logics into common reusable components
8. 
   
9. Ensuring that the required processes are controllable by the Business team with the required checks built in
10. 
    

Avoiding hard references, usage of parameter variables:
In scenarios like defining simple database connections or to using a “Business Unit Number” for filtering data while extracting required data from a source system can be parameterized through variables. In the case of database parameterization it enables easier code movement across development-test-production environments and in the scenario of the Business Unit Number parameterization it enables running the same DI program for one other Business Unit with same data by just changing the parameter variable of Business unit Number

Usage of lookup tables for code conversion
In scenarios like where the incoming data value has to be converted to one other “standard value”, we usually write a IF..THEN… ELSE… syntax, here again we can bring in dynamism by having a lookup table which would carry the incoming value as one column and the standard value to be replaced as another column. The benefit is if there is any further change to the standard value the lookup table can be updated without opening the code and as well we can insert a new record into the lookup in case we need get the DI process handle a code conversion for a new incoming value.

We shall see more details and other design aspects in the coming days.

You might want to read these awesome related posts Data Integration Challenge

How to setup the Direct Reports for Manager?

One of the Key for Manager Self Service transactions is setting up of Direct Reports for Manager.

For every MSS Transaction, The First page is always Direct Reports Listing Page. Generally this list is determined by Organizational Hierarchy Setup. (For Eg: Departmental Hierarchy or Supervisor driven Hierarchy). If we correlate with PeopleSoft Terminology, I would say this will come under any of these access types

1. By Supervisor Id
2. By Reports To Position
3. By Department Manager Id
4. By Department Security Tree
5. By Part Position Management Supervisor
6. By Part Position Management Department Manager ID
7. By Group Id

If we take access type“By Reports To Position”, it means Organizational Hierarchy is determined by the Employee’s Reporting Position Number.

In PeopleSoft, this Direct Reports Listing is constructed by 2 types of Setups

Direct Reports Setup

 Configure Direct Reports UI

 


Configure” Button is used to setup the Access Type and the fields to be displayed on the Direct Reports Listing page.

Note: Configure Direct Reports UI is the new feature provided in PeopleSoft HCM 8.9 Version onwards.

In order to verify the above mentioned setups, PeopleSoft itself provided options to validate the setups. This is done by using Invoke Direct Reports API – This is used to view the Direct Reports of the Manager

Invoke Direct Reports UI API – This is used to view the MSS Transaction pages for a particular MSS Component

Note: Navigation for these setups:

Setup HRMS >> Common Definition >> Direct Reports for Manager

Read More About  Direct Reports for Manager

SAP HRO Solution

SAP Business Process Outsourcing’ might sound new to many but it is one of the popular service in SAP’s solutions profile. SAP is not a BPO company but SAP solution empowers the companies that provide BPO services. SAP Business Suite, the world’s most comprehensive family of adaptive business applications built on three decades of experience and best practices, is already the choice of many BPO providers and their customers.

Customers benefit from solutions aligned with BPO, such as SAP ERP HCM and SAP SRM, that support human capital management, procurement, and finance and administration. SAP also covers industry-specific processes in the public sector, utilities, and financial services industries using its SAP Business Suite ( SAP Enterprise Resource Planning, SAP Customer Relationship Management, SAP Product Life Cycle Management, SAP Supply Chain Management, SAP Supplier Relationship Management..) These solution built on SAP Netweaver platform which represents as a integration and application platform. It supports SAP solutions evolution to service oriented architecture.

Netweaver the right platform for BPO solution

SAP with its clear road map of upgrade and evolution of technology protects the investment by enabling BPO providers to continuously leverage technological advances for business processes without disruption. As a BPO solution SAP also provides right business-process foundation powered by the SAP Netweaver. It makes it easier to move non-core business processes outside the enterprise while retaining full control of information and workflow in a seamless, tightly integrated fashion. SAP applications are deployed in a cost-effective and standards-compliant way which is critical when you provide service for many client from different places geographically and different nature.

SAP supports BPO providers in setting up services that generate sustainable business benefits by reducing costs, lowering risks, and improving process quality. Using delivery platforms based on SAP solutions.

According to the Everest Research Institute, SAP is the software provider that owns the largest market share (based on contract value) in multiprocess human resources, procurement, and finance and accounting outsourcing.

Hexaware is uniquely positioned to be a strong SAP enabled BPO service provider because of its strong presence in SAP space and BPO space. Because of Hexaware’s domain strength in HRIT and deep knowledge in HR process standardization we lead the pack in providing SAP enabled HRO services.

SAP ERP HCM – Solution functionalities

Talent management

• Support and integrate all talent management processes to attract, hire, and quickly secure qualified candidates
• Educate and develop employees
• Identify and nurture future leaders
• Motivate and align the workforce with corporate objectives to improve organizational performance

HCM service delivery

• Automate and standardize the provision of HR processes and services through a centralized delivery channel or shared-services organization
• Achieve high-quality HR services at low cost
• Perform analytics to measure the performance and effectiveness of HR teams

Workforce analytics

• Gain reporting and analysis options that offer real-time insight into workforce-related data
• >Manage the workforce more effectively
• Predict human-capital investment demands
• Accurately track workforce costs

Workforce process management

• Automate and integrate all core workforce processes for employee administration, organizational management, global employee management, time management, benefits management, payroll, and legal reporting
• Ensure adherence to local regulations and laws
• Global integration
• Consolidate all workforce-related processes and data onto a single global platform
• Leverage country-specific legal compliance guidelines and best practices
• Reduce operational costs
• Ensure better insight into workforce deployment.

Leading human resources outsourcing (HRO) providers deliver services based on the SAP ERP Human Capital Management (SAP ERP HCM) solution and the SAP NetWeaver platform:

Global Providers

• Accenture
• ACS
• ARINSO
• Automatic Data Processing (ADP)
• Convergys Employee Care
• EDS/Excellerate HRO
• IBM

Regional Providers

• LogicaCMG
• T-Systems

Regional Providers – with Country Focus

• BASF IT Services

Germany

• Bayer Business Services
• CDP, Greater

China

• HBS Business Services Group Ltd.

United Kingdom

• Human Resource Management Service & Consulting Co., Ltd., Japan

Mouchel Parkman 

United Kingdom

• Payoffice

Australia and New Zealand

• SDWorx for Public Sector

Belgium

• TATA Consultancy Services, India

Propelled by Hexaware’s SAP / BPO / HRIT teams energy and enthusiasm. We are determined to capture the significant role SAP HRO arena

How to delete/purge OPRID outside PS?

Peoplesoft provides an online page to delete OPRID. However, this can become cumbersome if you need to purge or delete 10+ OPRID’s as part of your cleanup exercise. I have created the below PL/SQL script which basically mimics what the online purge process executes.

DECLARE

CURSOR C1 IS

SELECT decode(A.SQLTABLENAME,’ ‘,’PS_’||A.RECNAME,A.SQLTABLENAME) RECNAME

FROM PSRECDEFN A

WHERE RECNAME IN (SELECT RECNAME FROM PS_TBLSELECTION_VW)

AND RECNAME != ‘PSOPRDEFN’

ORDER BY 1;

CURSOR C2 IS

SELECT decode(B.SQLTABLENAME,’ ‘,’PS_’||B.RECNAME,B.SQLTABLENAME) RECNAME

FROM PSRECDEFN B

WHERE

RECNAME IN (SELECT RECNAME FROM PS_ROLEUSR_TBLS_VW)

AND RECNAME != ‘PSOPRDEFN’

Read More About OPRID

Workforce Modelling and Intelligence

Login to the url http://helix.hexaware.com/Innovation/ and navigate to the menu HRIT>SOD>Thursday forums to view the presentation as well as the video of Princeton Softec’s Optim and Human Concepts Workforce Modelling and Intelligence tool – Orgplus.

To know More About: Enterprise Solution

Peoplesoft Time and Labor Vs Kronos Workforce Timekeeper Part II

Peoplesoft Time and Labor Vs Kronos Workforce TimeKeeper
Kronos Workforce Timekeeper
The process of making historical edits using Timekeeper 5.0 is much the same as it was in Timekeeper 3.4.
Add Historical Amount (formerly “New Historical Amount” in TK v3.4)
Required fields (note these in the screenshot below):

• Historical Date (formerly “Apply to Date” in TK v3.4)
• Pay Code
• Amount (hh:mm)
• Include in Totals for Effective Date (formerly “Add to selected timeframe” in TK v3.4) Selecting this option ensures the information is sent to payroll for processing and payment.
• Impact Accruals

Peoplesoft Time and Labor: Adjustments to Payable Time
We may need to make an adjustment to payable time that has already been paid by Payroll. This type of adjustment is called a Record Only Adjustment and is made using the Adjust Paid Time page. When you make an adjustment of this type, it is for record keeping purposes only. The time you add / adjust is not processed by Time and Labor nor is the time sent to payroll.

Using the Adjust Paid Time page, one can add / delete time, or change existing payable time that has already been processed by the payroll system and has a payable status of Distributed. All the time reporting fields appear on the Adjust Paid Time page for editing. You can change any field except the Payable Status, and TRC Type fields. After you make adjustments using this page, the system does not process the time further. It does not run it through Time Administration or send the time to payroll. Time and Labor does publish the payable time to your Project Costing application.

For further details please refer the People soft People books.

Please Visit to learn about: Kronos Workforce

Business Intelligence Utopia – Implementation Enablers

In this series of posts on Business Intelligence Utopia, I have presented some thoughts around 5 key technology enablers that in my view will take BI to the next level. They are:

1. Proliferation of transactions systems that support the analytical feedback loop Real-time / Right-time

2.Data Integration

3.Data Governance

4.Service Oriented Architecture

5.Extensible Data Models

All of these put together answer the question of “Where do you want to see Business Intelligence in the future”? I have 5 more to go to complete the discussion on the “Power of Ten” key enablers.

At this point, I felt that the question of “How to make it happen?” is as important as the technology enablers and hence decided to take a bit of digression to focus on some implementation aspects for Business Intelligence Utopia. I call them the “Implementation Enablers”. These enablers focus on process methodologies, specific techniques etc. that helps in managing the evolution of Business Intelligence within your organization.

There are 3 implementation enablers of interest, at this point in time:

1. Agile Framework for BI
2. 
   
3. Business Intelligence Calibration
4. 
   
5. Function Point based Estimation technique for Data Warehousing
6. 
   

You can also visit the following link http://www.hexaware.com/webcastarchive1.html to listen to my recent webinar on “Agile Framework for Calibrating the Enterprise Data Warehouse” to get an idea of the implementation enablers.

To reiterate, my approach for future posts on this blog is to write about Business Intelligence Utopia along 2 dimensions:

Technology enablers – 10 of them have been identified. 5 of them were discussed already and 5 more to go.

Implementation enablers – 3 of them have been identified and will be elaborated in the next few weeks.

Bit of information – I recently took the TDWI (The Data Warehousing Institute) assessment of Business Intelligence  Maturity done using their online tool and got some very good insights. If you are interested, please use the link- http://tdwi.org/display.aspx?id=8500 to take the assessment for your organization.

How to be any OPRID, if

Here is the scenario…

• You do not have access to PeopleSoft Security Administration pages to reset the password.
• You want to log on to PeopleSoft using your id and you have forgotten your password or you want to log on as some other operator id (without being detected).

Hmmm…

This is possible “IF” (the BIG IF)

• You are the DBA
• You are not the DBA but your generous DBA has provided you SELECT and UPDATE access to PS tools tables

Here is an example.

1.  I want to logon to F89XXXX but I do not remember my password. I sent an email to my security admin who is busy working on other priority issues.

SYSADM@f89xxxx > select oprid, operpswd from psoprdefn where oprid = ‘NPAI’;

OPRID OPERPSWD

—————————— ——————————–

NPAI 2bc+5cdNDqN4to33X9hP98N97+k=

2.  I remember my password in F89NNNN. So I query my operator password in that db.

SYSADM@f89xxxx > select oprid, operpswd from psoprdefn@f89nnnn where oprid = ‘NPAI’;

OPRID OPERPSWD

—————————— ——————————–

NPAI VLmLlgq9QsvCkDvRoqLnvhn8SkM=

3.  Execute the below Update SQL

SYSADM@f89xxxx > update psoprdefn set operpswd = ‘VLmLlgq9QsvCkDvRoqLnvhn8SkM=’ where oprid = ‘NPAI’;

1 row updated.

SYSADM@f89xxxx > commit;

Commit complete.

4.Voilà!! I am now able to log on to F89XXXX using my password that I remember from F89NNNN.

You can follow similar steps to log on using any other operator id. You can update it back to the original value and go undetected unless it’s being audited.

Conclusion

For the DBA:

• Do not provide ANY level of access to PS tools tables to ANY user in ANY environment
• PROTECTPSOPRDEFN and other security tools tables
• Consider HIDING OPERPSWD column using a view or restrict access to PSOPRDEFN using FGAC
• AUDIT all security related tools tables.

For others:

• Do not attempt this in your environment!

Read More About OPRID

How to determine what is being processed?

As a DBA you might be receiving a lot of calls from users asking some of the questions listed below.

- What their process is currently executing?

- How long will it take?

- Should I cancel my process?

In this post, I will cover some steps that will help you quickly respond to the above questions.

I have executed the below SQR Report in my environment and want to know what is happening.

What their process is currently executing?

1.Use the below SQL to correctly identify the Oracle session

SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE ‘NPAI%’ AND STATUS =‘ACTIVE’;

2.The above results identify the session executing the report; in this case it is SID 32.

3.Use this SID to identify the SQL being executed.

SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 32) ORDER BY PIECE;

SQL_TEXT

—————————————————————-

SELECT A.RECNAME, A.FIELDNAME FROM PSRECFIELD A WHERE A.SUBRECO

RD <> ‘Y’ AND NOT EXISTS (SELECT ‘X’ FROM PSDBFIELD B WHERE B.FI

ELDNAME = A.FIELDNAME)

4.The above results will provide you the SQL that is currently being executed. Equipped with this information you SHOULD be able to dig deeper if the session continues to execute the same SQL for more than expected time. Some of the scenarios could be:

a.Locked rows

b.Bad SQL

c.Statistics not updated

d.Bad DB configuration – SQL waiting on some event

e.Missing index, etc

Note 1
Also, there are scenarios where the session might not be executing any SQL because it is busy executing the code logic within the program. If the program is written using good coding standards then you should be able to look at the log file to decipher the progress.

Note 2
It is possible that there are no Oracle sessions created by the process. Some of the scenarios are:
- Process is Queued
- Process has completed and is Posting, etc
How long will it take?In most scenarios the functional support personnel along with the developer should be able to respond to this question based on the SQL or results in log file that you have identified. If you have knowledge about this process based on its history and/or your ability to interpret the program then you can estimate the completion time.
Should I cancel my process?This decision will require approval from the business + functional support personnel unless you have knowledge about the impact of the process.

Read More About  what is being processed?