Processing Multiple XML Files through Informatica – 1

Problem Statement: Data to be processed in Informatica were XML files in nature. The number of XML files to be processed was dynamic in nature. The need was also to ensure that the XML file name from which data is being processed is to be captured.

Resolution:
Option 1 – Using File list as part of Indirect File Sources in session
Option 2 – Using Parameter File and workflow variable

Implementation Details for option 1: Using File list

XML file names to be processed were read using batch script and file list was created containing XML file. This file list name was set under source properties at session level. XML file were read sequentially and data pertaining to every XML file was processed. Since the number of XML files to be processed was dynamic the need of the hour was to achieve looping in Informatica.

Challenge in using File List – Created in a session to run multiple source files for one source instance in the mapping. When file list is used in a mapping as multiple source files for one source instance, the properties of all files must match the source definition. File list are configured in session properties by mentioning the file name of the file list in the Source Filename field and location of the file list in the Source File Directory field. When the session starts, the Integration Service reads the file list, then locates and reads the first file source in the list. After the Integration Service reads the first file, it locates and reads the next file in the list. The issue using XML file names in file list was further compounded by Informatica grouping records pertaining to similar XML node together. This lead to difficultly in identifying which record belonged to which XML file.

Batch Script – batch scripts controlled over all looping in Informatica by encompassing below mentioned tasks:
• Reading XML file names from staging location and creating file list containing XML file names.
• Moving XML files from staging location to archive location.
• Verifying whether there are any more XML files to be processed and depending on the outcome either loop the process by invoking first workflow or end the process
• Using PMCMD commands invoke appropriate workflows.

Workflow Details –
There were two Informatica workflows designed to achieve looping:
• First workflow –created indirect file to be used as source in session properties and will trigger second workflow. Details of workflow are:
o Command task will execute a DOS batch script which will create indirect file after reading XML filenames from a pre-defined location on server.
o Command task which will execute the second workflow to process data within XML files.

• Second workflow will read process XML files and populate staging tables. Details of workflow are:
o A session will read XML file names using indirect file and load into staging tables.
o A command task will move the XML file just processed in file into an archive folder. Using batch script
o A command task will execute a batch script which will:
 Check whether there are any more XML files to be processed.
 If yes then it will trigger the first workflow. This will ensure all XML files are processed and loaded into staging tables.
 If no then process will complete.

Thanks for reading, pls let me know have you faced any similar situation.

To know more about  XML Files

PeopleSoft HCM 9.1 Succession Planning

How important is it to an organization?

The recession storm seems to have settled down and IT companies are in a hiring spree, much to the trend seen in the Formula one racing, its Schumi’s re-entry and some good show by Force India team and Adrian. It all seems to be drilling down to the race line.. eagerly awaiting to jump on the gas pedals in this 2010 season.

Yes, the software industry too – is looking forward for a jump start and many IT organizations are preparing themselves for the race, the race which will win them new clients in new areas and the world economy seems to get back in its ‘once’ fruitful track.

As many of you are aware, Oracle has come up with the PeopleSoft 9.1 Version with some cool features, Lets see why one of the new features of Peoplesoft 9.1 seems to be interesting as it justifies the HR operations with its functionality. One of the reasons for Indian IT organizations to spend a lot of effort and cost in hiring new employees and especially high profile positions within an organization seems to be the lack of succession planning and career planning.

Succession planning enables an employer to organize its talent pools based on the employees person profile, job code, position and a plan that is unique to it. Initial and continous mapping of employees competency, performance and interests with the employer’s goals (position, jobcodes) makes it easier for the organization to plan and sustain its growth in the event of its new project wins. As stated in

Learning Management System drives a companies growth

Peoplesoft 9.1 brings in easier integration options with the Enterprise Learning Management (ELM) module thereby providing consistent career paths for the employees to pursue inorder to accomplish the succession planning. Both the HR Administrators and Managers are well equipped with the Visualization (user friendly reports) and Self Service  layer in accomplishing the tasks.

Features like tracking an employee’s successor in terms of number of years makes the job even easier for the HR to identify / probably mine through the talent pool of the organization.

Let’s see how IT organizations are quick in implementing the Succession planning feature of PeopleSoft 9.1 and Let’s assure them that its a vital part for a growing organization.

Know More: PeopleSoft HCM 9.1

PeopleSoft 9.1 HCM Compensation and Performance Appraisal Cycle

It’s Appraisal Season, Like the leaves which change color in fall and fall off their trees – IT Sector may witness a lot of attrition during and after the appraisal cycle. The reason being the outcome of the appraisal process .

 It is vital for a growing organization to streamline their appraisal process with planning their funding channels, organizing the pay components and administering it.

Existing PeopleSoft features of Tree Manager, Approval Workflow Engine (AWE), HCM Delegation Framework and pagelet wizards are inter woven with a streamlined process to accomplish what Oracle calls PeopleSoft 9.1 Compensation. Interestingly, the interoperability aspects with ePerformance, Core HR and variable compensation adds more value and increased ROI for the organizations which would implement PeopleSoft 9.1 HCM Compensation.

The first thing that an organization eyes is their budget/Funds for an appraisal / Compensation cycle, followed by the planning and allocation of collective Compensation aspects. Embedded analytics and user friendly interface enables Compensation Administrators or Managers to build what peoplesoft terms it as “Compensation Cycle”.Initial Setup would involve defining proration rules, rounding rules, salary plan, action reason,.. and Configuring compensation matrix.

Example Appraisal – Compensation Configurable matrix:
Rating             Funding Pct       Min Percent     Max Percentage
————-     —————       —————     ——————–

6                               42                       42                            42

5                               30                       30                            30

4                               19                       18                            19

3                                8                          8                             8

2                                2                          2                             2

1                                2                          1                             2

For a compensation cycle, the funding overview for salary plans  summarizes the total number of head count which would be involved in the appraisal cycle along with the calculated total salaries, calculated amount, calculated percent, qualified headcount, qualified salaries, funded amount, funded percent, proposed amount, proposed percent.

Once the compensation cycle (01JAN2009 to 31DEC2009) is defined, the appraisal team works with the variable compensation plans and compensation rules to manage the available funding for an appraisal cycle. The Pay out periods as well as the Pay out types are also defined using the cash or available stock options. It is one of the features in which many employers tend to provide stocks instead of a cash hike. Followed by the compensation team, the manager self service of PeopleSoft is used by the managers to update appraisal info of an employee or a group (direct reports / Indirect reports) along with their review ratings. The Approval process plays a vital role in approving the planned / updated compensation details using the delivered roles of “submitter” “reviewer” and “Confirmer”.

On the other hand, the administrator is equipped with the compensation dashboard which constitutes the Process flow Build->Open->Load->Close and status history which manages the manager’s access rules (notification period, default, review period, update period)

PeopleSoft 9.1 Compensation also allows employers to handle exceptional cases during an appraisal cycle by incorporating “Key Resource Bonuses” on the Award Plans present with the compensation module.

In total, PeopleSoft 9.1 HCM Compensation seems to be quiet promising in bring in transparency and streamlined appraisal cycle within a growing organization.

Know More About: Peoplesoft 9.1 HCM

Raising SaaS OnBoarding Solution and Dominant PeopleSoft HR

2010 seems to mark a new begining in both the IT Services and F1 Racing. Cool,.. we are awaiting the growth in SaaS, SOA,… and some great adrenaline rush as Schumacher returns.. For People who still believe that PeopleSoft is leading the HR Services market, Lets remind them of the advent of Kronos Workforce (Application Service Provider), KMS XpressHR (Software as a Service – SaaS Solution), Open Text Recruiting Management Solution for Microsoft Sharepoint 10.0 driven by the Enterprise Content Management Solution like Livelink and Document Center respectively. The Clientele are really happy to spend for what they use unlike the age old model of fixed pricing.

Let’s see how KMS OnBoarding XpressHR SaaS Solution attracts the clientele with their functions when compared to Peoplesoft,

• In PeopleSoft, the SSN entered while adding a new person to the system is not validated or verified with the Department of Homeland Security or Social Security Administration(SSA). This had enabled Clientele to incorporate the XpressHR product for onboarding which constitutes the e-Verify Functionality.
• In PeopleSoft, the unavailability of the features like e-Signature & Digital content management in HR had enabled XpressHR to provide 100% Compliance with the State & Federal data related to the pre-hire process within an organization.
• SaaS based Onboarding Process & Document Center Model would enable clientele to leverage the increased ROI.
• Implementation effort seems to be less with the use of web services.

It’s these few aspects which drive the present day software investments, As posted in Service Oriented Computing Platform for Shared Services Model , the service providers whose verticle solutions(XpressHR) which are built on horizontal solutions (ECM) are undoubtedly growing 

Living with the Enemy

Just imagine two people who hate each other to death, being forced to live in the same house and continue inflicting pain on each other in every possible way. Do you get the picture?  That is what is happening between Oracle and SAP. To Larry Ellison, there is no other enemy more worse than SAP, and for SAP (even though they downplay it)  the number one in their enemy list is Oracle.

When SAP hates Oracle this much, SAP can not stop being major sales channel for Oracle’s database. Oracle  is still the number one database in the SAP customer base. It’s also no secret that many SAP customers would love to stop paying a premium price for a database that is functionally underused by the SAP product line.

But now there is a hope to SAP,  a new reason that SAP would like to get rid of the traditional relationship between its software and database leader Oracle. If SAP can develop its application to be less dependent on disk database, that  would be the first  step to reduce the dependency.

Speed is Money

That was what was proclaimed  by Mr. Plattner in the Sapphire conference in his key note address. He said the new world of in-memory computing is the next in-thing in the enterprise software.  In-memory database system (IMDS) is designed explicitly for real-time applications and for embedded systems such as set-top boxes, telecom equipment, consumer electronics and other connected gear. The in-memory database minimizes RAM and CPU demands and offers unmatched performance, reliability and development flexibility.

Enterprise software companies could learn from the techniques used by gaming software developing where the in-memory database  usage is already making big impact to get the maximum output from the multi-core CPUs. Mr. Plattner did not promise that SAP is developing in-memory concept into SAP product but he made it very clear that it is the way forward.

Oracle Killer

The desire to kill Oracle is not new found for SAP. As early as 2005, Shai Agassi, the then president of product technology group and member of SAP executive board, elaborated about the company’s programs to improve the in-memory capability of the software. In-memory capability is a new way to manage the data. Largest databases in the world are data warehouses, and these databases get the most complicated queries that they need to process as fast as possible. This requires enormous amount of CPU power. The testing ground for SAP’s new database strategy can be found in a product the company released few years back – the BI Accelerator or BIA. Among its many attributes, BIA is a hardware appliance that runs SAP Analytics incredibly quickly. The potential “Oracle killer” part of BIA comes from its in-memory database functionality, which processes database queries in RAM with no need to access disk-based storage — or even have a relational database at all — which means no costly database license, no massive data-storage farm, no expensive DBAs, and so on.

The idea of in-memory query management at SAP is hardly new. Back in the late 1990s, SAP unveiled LiveCache, an in-memory processor for what was then called the Business Warehouse. LiveCache was a little ahead of its time for lots of reasons, starting with the fact that CPU and memory costs were still relatively high for what SAP had in mind. In the end, LiveCache failed to live up to expectations. But it still survived as the in-memory query engine for SAP’s Advanced Planner and Optimizer (APO) supply-chain product.

LiveCache made history in SAP benchmarking, giving an indication of the response times that are possible using an in-memory database engine. Based on SAP’s own benchmarking standard — the SAP Standard Applications Benchmark — SAP’s hardware partners have had a glorious time leapfrogging each other in recent years to see which could achieve the best response times with LiveCache.

So, It is more of the question of when the killer will arrive. Someday soon, we will have a choice to choose between the status quo and new radical database approach. What will you choose if the newer approach is cheaper and faster and effective?

Read More about  IMDS

Service Oriented Computing Platform for Shared Services Model

It is the need of the hour for business entities to revisit their growth plans and to perform consistent checks on their performance and operational utilization. One of the driving factor for the transformation of Application Service Providers (http://blogs.hexaware.com/pitstop/application-service-provider-vs-software-as-a-service-asp-vs-saas.html )to Software as a service is the change in the Information Technology Trends.

 Human Computer Interaction and Interoperable applications seems to be the cloud where the future software investments would be. Let’s look into the evolving service oriented computing platform which answers a lot of the present day business needs.

The Product based companies were quick to incorporate the service orientation in their product designs by mostly following the agile software development methodology whereas the Service based companies were quick to incorporate the shared resource pool to evolve the Shared Services Support.

Initially, the shared services model was spotted within organizations as specialized groups of system and vendor specific administrators and later evolved as ESSA (http://www.hexaware.com/shared-services.htm). The proposed service oriented computing platform for such shared services model would further enhance the customer satisfaction as the service design principles are followed.

Goin by the tag “If resources are not retainable, Let’s retain their knowledge within the organization” – the service oriented computing platform goes one step further from the traditional vertical solutions(CRM,TAM,.) by providing an SOA based solution utilizing the present day horizontal solutions(Livelink ECM ). This Open technology solution is vendor independant and can accomodate the existing applications of Oracle(Peoplesoft, Siebel), Microsoft(sharepoint),SAP,Kronos Workforce Timekeeper,.. by establishing a meta data driven knowledge solution for the shared services resource pool to work on.

Few Key advantages of this service oriented solution for the shared services model are

a.Reusabilty – the reduntant support (technical/Functional) effort is minimized.

b. Service Governance

c. Discoverability

d. Less Time consumption in performing a support activity

e. Knowledge Service Composition – enabling the maintenance of organization wide service data irrespective of their source department or technology.

Like the Peoplesoft Person Model which was based out of SOA, the service oriented solution design for the shared services model would make a huge difference among the corporate cube warriors. Let’s wait for more such innovative solutions to surface soon.

Merge Rows as Columns / Transpose records

Requirement: Converting rows to columns

Customer	Product	Cost
Cust1	P1	10
Cust1	P2	20
Cust1	P3	30
Cust2	ABC	10
Cust2	P2	25
Cust2	Def	10

Customer	Product1		Cost1	Product2	Cost2	Product3	Cost3
Cust1	P1	10		P2	20	P3	30
Cust2	ABC	10		P2	25	Def	10

The above illustration would help in understanding the requirement. We had to merge multiple records into one record based on certain criteria. The design had to be reusable since each dimension within the data mart required this flattening logic.

1. Approach:

The use of aggregator transformation would group the records by a key, but retrieval of the values for a particular column as individual columns is a challenge, hence designed a component ‘Flattener’ based on expression transformation.

Flattener is a reusable component, a mapplet that performs the function of flattening records.

Flattener consists of an Expression and a Filter transformation. The expression is used to club each incoming record based on certain logic. Decision to write the record to target is taken using the Filter transformation.

2. Design:

The mapplet can receive up to five inputs, of the following data types:

i_Col1 (string),  Customer

i_Col2 (string), Product

i_Col3 (decimal), Cost

i_Col4 (decimal) and

i_Col5 (date/time)

Have kept the names generic trying to accept different data types, so that the mapplet can be used in any scenario where there is a need for flattening records.

The mapplet gives out 15×5 sets of output, in the following manner:

o_F1_1 (string), Customer

o_F2_1 (string), Product1

o_F3_1 (decimal), Cost1

o_F4_1 (decimal) and

o_F5_1 (date/time)

o_F1_2 (string), Customer

o_F2_2 (string), Product2

o_F3_2 (decimal), Cost2

o_F4_2 (decimal) and

o_F5_2 (date/time)

… … and so on

The output record is going to have repetitive sets of 5 columns each (Each set would refer to one incoming row). Based on the requirement the number of occurrence of these sets can be increased. The required fields alone can be used / mapped. For the above example we use just 2 strings and one decimal for mapping Customer, Product and Cost.

The mapplet receives records from its parent mapping. The Expression would initially save each incoming value to a variable and compare it with its counterpart that came in earlier and is held in its cache as long as the condition to flatten is satisfied.

Syntax to store current and previous values:

i_Col2 string i

prv_Col2 string v curr_Col2

curr_Col2 string v i_Col2

The condition/logic to flatten records is parameterized and decided before mapping is called thus increasing codes’ scalability. The parameterized logic is passed to the Expression transformation via a Mapplet parameter. The value is used as an expression to perform the evaluation and the result is a flag value either ‘1’ or ‘2’.

Syntax for port – flag

Flag integer v $$Expr_compare

An example for parameterized expression

$$Expr_compare = iif (curr_Col1 = prv_Col1 AND curr_Col2 !=

prv_Col2, 1, iif (curr_Col1 != prv_Col1,2))

A variable port named “rec_count” is incremented, based on the flag.

Syntax for port – rec_count

rec_count integer v iif (flag=2,0, iif (flag=1,rec_count + 1,rec_count))

The expression transformation now uses the value in ports “flag” and “rec_count” to decide the place holder for each incoming input value, i.e. the column in target table where this data would move into ultimately. This process is an iterative one and goes on until the comparison logic ($$Expr_compare) holds good, i.e. until all records get flattened per the logic. An example of the place holder expression is shown below:

v_Field1 data type v iif(flag=2 AND rec_count=0,curr_Col1, v_Field1)

Port “write_flag_1” is set to 1 when the comparison logic fails (meaning flattening is complete). Filter transformation filters out the record once it is completely transposed.

Filter condition:

write_flag_1 integer v iif (flag=2 AND write_flag>1 ,1,0)

3. Outcome:

After developing the code and implementing the same we found it to be a useful utility, so thought of sharing it and would like to hear suggestions from readers on performing the same functionality in a different way. Please do share your views.

Read More about Transpose Records