Informatica Tranformation Example For Peoplesoft ERP HR data.
1. Informatica Expression (Source to Target)
2. Source Qualifier transformation
3. Expression Transformation
4. Informatica Language example use of IFF syntax.
5. performance optimisation using Oracle DECODE function inside Expression transformation
6. Hexadecimal to Decimal Conversion Logic
READ part 1:
This debate of SAP Vs Oracle or last 2 yrs buzz SAP HANA vs Oracle Exalytics
Every year in Enterprise Software space Competition of SAP Vs Oracle Hots up with new announcements and New technology comparisons of SAP new Tech Vs Oracle New Tech.
The Enterprise Software stack built up by No.1 and No.2 world’s top most valued companies in Enterprise Technology Space.
So SAP Vs Oracle comparison will never go out of picture only every year it will up the ante.
In Memory Technology: SAP Vs Oracle (New tech now 2 yrs old).
Now a day In-Memory Technology is the Hottest area especially in Business Intelligence (BI). But its not limited to BI it goes into ERP, Application development of any Kind.
The processing infrastructure in form of in Memory Systems is much faster than any other form. As Cost of RAM is coming down and capacity of server is expanding So most data to be processed can be pulled inside memory at once instead of using Locality of reference to pull and process from secondary memory.
Also Server can hold lot more data to processed in memory at once.
SAP HANA Vs Oracle Exadata
So SAP Came with SAP HANA and Oracle has its own in memory systems. At same time Oracle released high performance machine Exadata which brook many performance records. So There was running comparisons of SAP Vs Oracle in Enterprise Technology space.
innovation at SAP : ABAP, BSP and BAPI
SAP customized by using Advanced Business Application Programming (ABAP) Language.
Using which reports are customized, forms are modified, business processes are written to reflect business Logic. ILE, BAPI, IDOC can be used to interface with external software or integration or developing Adaptor. Custom Exits and User Exits are written to customize forms and reports.
For SAP datawarehouse technology SAP BIW or SAP BI datawarehouse is created using
Extraction Legacy System Migration Workbench (LSMW) or Cost and profitability Analysis (COPA) Extrations then transformation can be applied using ABAP user exits.
Problem with ABAP is compared to mordern langauges it has not evolved much over time.
Most of ABAP constructs are similar to COBOL constructs. There is Object oriented ABAP also like JSP or ASP it came up with BSP (Business Server Pages) to Expose ABAP code directly to Web like JSP does for Java or ASP does for Microsoft Technology.
SAP Netweaver Vs SAP ABAP BSP
SAP began adopting Java in 2003 and came up with Netweaver product which was J2EE server for ABAP code. Now you can code using either java or ABAP in netweaver. For Application requiring functionality to exposed to Web Java was natural choice.
But Oracle having acquired Java in year 2010 Since then there was continuous Effort on part of SAP to move away from its dependence of Java. So BSP came into picture as first step.
Benefits of In-memory in SAP HANA
next was paradigm shift using Columar database instead of Row oriented databases which consumed less storage (on account of reduction in repetitions of similar column values as well compression of data).
Also it integrates:
BI and datawarehousing system or OLAP with
Operational systems or OLTP Systems as one.
Even Analytic requiring lowest level of granularity can be queried on same server.
Effect of which was Whole data can be pulled and kept in In-memory system offering faster response time to multiple user connected at same time rather than
Regular database Query processing logic
parsing request , making parse tree,
comparing with already fetched query parse tree in cache
if not available
fetching data from secondary memory
when depending on the request into
Improvement in data processing in SAP HANA and column oriented database
Now since whole data can be kept in-memory So Every query can directly fetch data quicker.
Future Technology Like SAP HANA
Enhancement over this Technology are Probabilistic databases and Graph databases.
Graph databases are available commercially since long time.
Index free storage. Every element has direct pointer to adjacent element, hence no lookup needed.
Here is list:
Probabilistic databases : Are active area of research as discussed above as well.
Depending on ERP there can be many modules inside..
Major modules of peoplesoft ERP are :
1. Human Resource Management Systems HRMS
Submodules: (payroll, Core HR, benefits, recruitment, performance management)
(Account payable AP, Account Receivable AR, General Ledger GL etc..)
3. Supply chain Management SCM
4. Customer Relationship Management CRM
Functional consultant major work is during GAP Analysis To find As-IS state and identify work required to achieve TO-BE state.
Find all those forms which needs to be modified, and all those reports which needs to be modified And Exactly what field to change, What button to remove, What functionality to be disabled, What pre-delivered business logic behind components to be modified and What formulae to be used during modification.
There is Another Module which is consider Techno-functional Peopesoft EPM. EPM is datawarehouse part which has four major components for Each of four above there is a data mart for each area of finance, HR, SCM, CRM.
Each contains data from its own module transferred using ETL tools and reporting using BI Tools and analytics can be applied over formulae in BI systems.
-Also functional consultant decide based on feedback from local management to modify field like in HRMS a rejoining of employee should get ID as previous from old record or new ID based of policy in management.
if Payroll of local market like irish payroll or indian payroll does not exits then it should existing global payroll should be modified to achieve local labour laws and saving rules , income tax based customisation.
There are 5 major streams for Peoplesoft technical consultant
1. Reporting Tool: Hyperion SQR, nvision, Crystal,
2. Server Tool: peopesoft internet Architecture, Tuxedo, JOLT, Webserver, Appserver
3. Application development: peoplecode, application designer, App Engine, PS integration Broker,
4. Installation and Upgrade : Upgrade Assistant,
5. Peoplesoft DBA: all issues related to Database on top of which peoplesoft ERP is sitting.
Depending of Each of these streams The a day in life of Peoplesoft technical consultant will differ.
Life cycle of Peoplesoft implementation from :
4 major areas:
1. GAP Analysis,
3. stabilization phase of ERP ,
4. and support for already developed
Each phase has its own daily routine Like during support phase
3 major type of request which can come from user is ICE:
Incident, Customization, and Enhancement request.
During gap analysis: The As-IS state is studied and its difference made out from TO-BE state. The Gap between AS-IS and TO-BE state is implementation which needs to done.
As you know peoplesoft is ERP package comes with its pre-delivered tables, forms and reports Which needs customisation depending on company, country , laws of land, company processes.
During implementations phase according to gap analysed forms , reports are customized using peoplecode language and application designer, 9 steps process is applied to each component being developed, which include security settings.
SQR banned reports are being customized with header footer for display and for business logic of processing data fetched from database and displaying in format required using option like BREAK options.
Support Phase ICE request for each change is being accepted for Incident, enhancement and Customisation for Each Change Request raised and Accepted or any errors in existing code.
Read Previous part
Normal Schedule for development role :
9:00-9:30 Check all mail communications of late night loads Etc.
9:30-10:30 Attend Scrum meeting to discuss update status of completed task mappings and mapping for New user stories requirements, understand big picture of work completed by other staff status.
10:30 am -1:30 pm Look at LLD, HLD to create source to target transformations after understanding business logic and coding that in transformations available with tool.
1:30-2:00 Lunch break
2:00-3:00 Unit test data set to validate as required between source and target.
3:00-3:30 Documentation requirements of completed work.
3:30-4:30 Attend defect Call To look into new defects in code and convey back if defects not acceptable as out of scope or not according to specifications.
4:30-5:00 Status update daily work to Team Lead.
5:00-5:30 sit with Team lead, architect code walk through and update with team.
5:30-6:30 Take up any defects raised in Defect meting and Code walk through.
Cisco, Microsoft and Neapp Jointly produced a system called opalis (Workflow) in 2012.
Data centre System process interactions can be configured depending on user need on Opalis and rules can be set up for those interactions. Read previous blog more about BPM and internet of things:
Opalis, Which essentially provide a workflow to dynamically create,monitor,deploy a Machine instance , allocate OS instance, (just like in Nebula, or Eucalyptus ) and User also can request (specific machine with RAM, CPU, storage space).
Microsoft provide all OS /software instance, Neapp provide SAN or and storage required on , Cisco provide Server , Nexus switches boxes.
Its integrated with Microsoft SCMM System centre Manager (used to creating private cloud on Microsoft technologies and a single User Interface to administer whole
Orchestration are discussed in previous blog in case of opalis its architecture llooks like this
Read: opalis blog
If all exist then they can be configured using BPM workflow of opalis for a user.
Collaboration tools integrated offering (course grain integration using ) integration tools like TIBCO, Oracle BPEL, : Components to be integrated:
1. Content management system CMS (SharePoint, Joomla, drupal) and
2. Document Management system like (liferay, Document-um, IBM file-net) can be integrated using flexible integration tools.
3. Communication platform like Windows Communication Foundation ,IBM lotus notes integrated with mail client and Social network like Facebook using Facebook API, LinkedIn API, twitter API ,skype API to direct plugin as well as data Analysis of Social networking platform unstructured data captured of the collaboration for the project discussion.
soft-phone using Skype offering recording conversation facility for later use.
Oracle Web centre:
4. Integrated Project specific Wikki/Sharepoint/other CMS pages integrated with PMO site Artefacts, Enterprise Architecture Artefacts.
5. seamless integration to Enterprise Search using Endeca or Microsoft FAST for discovery of document, information, answers from indexed,tagged repository of data.
6. Structured and Unstructured data : hosted on Hadoop clusters using Map-reduce algorithm to Analyse data, consolidate data using Hadoop Hive, HBase and mining to discover hidden information using data mining library in Mahout for unstructured data.
Structured data kept in RDBMS clusters like RAC rapid application clusters.
7. Integrated with Domain specific Enterprise resource planning ERP packages the communication, collaboration,Discovery, Search layer.
8. All integrated with mesh up architecture providing real-time information maps of resource located and information of nearest help.
9. messaging and communication layer integrated with all on-line company software.
10.Process Orchestration and integration Using Business Process Management tool BPM tool, PEGA BPM, Jboss BPM , windows workflow foundation depending landscape used.
11. Private cloud integration using Oracle cloud , Microsoft Azure, Eucalyptus, open Nebula integrated with web API other web platform landscape.
12. Integrated BI system with real time information access by tools like TIBCO spotfire which can analyse real time data flowing between integrated systems.
Data centre API and virtualisation plaform can also throw in data for analysis to hadoop cluster.
External links for reference: http://www.sap.com/index.epx
SAP XI: http://help.sap.com/saphelp_nw04/helpdata/en/9b/821140d72dc442e10000000a1550b0/content.htm
Map reduce: http://hadoop.apache.org/docs/stable/mapred_tutorial.html
facebook API: https://developers.facebook.com/docs/reference/apis/
Linkedin API: http://developer.linkedin.com/apis
Twitter API: https://dev.twitter.com/
These requirement are going to stay for next decade:Where can Software services/product firms lay emphasis for next stage of development. Or the areas which will see maximum amount of work coming in future..
Or What areas of knowledge should software companies develop manpower on:
1. Game development and Gamification:
2-7. Each of the Seven areas in development:
As you read you realize software which can take advantage of multiple processor available on the devices None of sotware present in market today is written to take advantage of this fact. It may be possible an new language may come up to take benefit of this fact of we can still use old java/C++ threads more offen or we can distribute load on server by more specific COM/ DCOM or Distributed Common Request broker Architecture CORBA to processor level at server.. We have virtual switches and VM ware or Zen virtualisation which can exploit maximum benefit from it.
8. More virtualised network stack: this I wrote 2 yrs back still valid to quote here:
private and public cloud new API will emerge: https://sandyclassic.wordpress.com/2011/10/20/infrastructure-as-service-iaas-offerings-and-tools-in-market-trends/
9. from SDLC V model to Agile and now to lean Agile ..use of six sigma to control process is just one part of mathematics being used for quality control but there would be new data model which will be tested based to mathematical modelling like probability distributions new model industry specific models would keep emerging.
like how for security project how security user stories are plugged into model
or read https://sandyclassic.wordpress.com/2012/11/12/do-we-really-need-uml-today/
10. BI would be Everyware:
parallelism , map reduce algorithm and cloud
In BI system Fan trap and chasm trap are common problem When designing a Universe (semantic Layer) which is used by BI reports to dynamically generate query.
The query information is passed to other system using CWXML (common warehouse XML files) this makes query data inter-operable to desperate systems requiring various different software landscape.
Fan Trap: happen when Object A representing table is mapped to another object B in 1:m cardinality. Which is also mapped to Object C in 1:m cardinality.
This WHEN this happens it lead to double or triple values in summary numerical attributes measures when query generated over them.
A (5) –> B(5,5) = may have value 10 then B –> C may have over calculated summaries c (5,5,5)=15.
A (Emp, salary) now B(dept, emp), C(section, dept).. now you can clearly see 1:m relationship between tables. as explained above the Emp,dept,sum(salary) would have 3 times or more over calculated values when dynamic query is genrated by semantic Layer. this is Called Fan Trap:
This can be solved by using Aliases, aggragate awareness functions @object, @ functions..
Chasm trap: A –> B (1:m) and B –> C (m:1) when relationship between objects is of this type it leads to Chasm trap.
There Chasm trap can also be solved by using aggregate awareness functions @userObjects etc…
Along with this loops are common problem in BI designing ?
Loops are resolved by setting context, which actually defines the path query is going to takes thus resolving ambiguity of join suppose A,B,C are joined but there are in web of tables which join happens dynamically by BI system it can take path like
join path 1: A, B, D1, D2, C
join path 2: A, B, D3, D4, C
Which introduces loops in BI semantic layer is systems like SAP Business Objects, IBM cognos or Micro strategy. Because as report utilizes the semantic objects directly and tool creates query as defined by semantic layer by BI architect it leads to loops.
the two intermediary routes available to tool creates two different types of query and hence two different types of results which leads to distortions in values and duplicate values on reports.
There are resolved by defining path by setting context so suppose we say always when A,B,C join use path 1 by using context.
Here are reasons Why next generation app will be totally different:
1. – In few years we will be seeing ending dominance of physical routers, switches , firewall to virtual soft switches, virtual routers , software defined routers and switches. More open routing technology would be program driven rather than configuration on boxes.
Companies like application firewall maker Palo Alto Networks and virtual programmable router maker nicira have huge role to play.
its also affected by trends in Network technology
2. – in next year we will see 20+ processors on single machine making parallel processing one of important requirement. Huge software would be re written to meet this requirement.
3. The changes in business and systems are occurring very fast as system and getting more understood and cross functional due to intense competition Where only innovation can make you stay ahead of curve: Read more reasons why?
4. Cloud will increase innovation to change way we think about software:
Software As service SAAS, PAAS, IAAS going to make more deeper innovation as defined in above article (https://sandyclassic.wordpress.com/2013/07/02/data-warehousing-business-intelligence-and-cloud-computing/).
How innovation on cloud will be much quicker read :
5. Laptop will never go (large screen requirement) but Mobile will be mass platform:
As we can move we can see virtually wearable shirts made of graphene with storage and data streamed on walls .. as when we want we can just grab wall data to graphene shirts..
Read more about Graphene: https://sandyclassic.wordpress.com/2013/01/18/graphene-the-wonder-material-foldable-cell-phones-wearable-computerbionic-devices-soon-reality/
surfaces will keep emerging we would see virtually display in air without any device but what it would be added with augmented reality and virtual reality.
we can in future just stream data to wall and program on wall outside our house.
6. Internet of things : where Machine to machine transfer of information and data and semantic web will make possible more intelligent feedback to user by all devices based on user need. so when you pick up milk from shelf next time. your fridge will search for you and alert you on latest offer of cheapest milk from various retailer.
And it will be displayed on fridge itself.. not only that it would order for you when its empty if you configure so. it will calculate you calorie consumed by family of fridge item and send updates to doctor monitoring you and display return messages from doctors.
7. Sensors will be everywhere and huge and Ubiquity will rule :
Activity based accounting has changed the accounting system where even cost centre inputs to bottom line is also appreciated , calculated and accounted and apportionment is run not only to profit centre but also to cost centre.
This led to renewed influence of new cost centre based new module reporting like Human resource Accounting/Analytic ( Profit centre based system were preferred early and coast centre were neglected )which not only introduced new module in the Enterprise Resource planning ERP also changed the interlinking between modules such as Human resource management system , human resource accounting influence to General ledger and to profit and loss account.
– as each activity is apportioned into management accounting there are changes which are happening in the Analytics as more deeper ,cross functional analytic measure are used last 5 yrs leading to huge changes in business thinking for top line and bottom line growth.
– as BI becomes pervasive and ubiquitous it leads to deeper granular analysis to system thinking by lower level staff leading to bottom up innovation.
– cloud and mobility has introduced pay per use model which influenced more pervasive BI and ERP usage by all staff giving fillip to bottom up thinking. Capital expenditure changed to operating expenditure leading to more acceptability to mid size companies as well large scale companies.
– real time updates using sensor based tracking of supply chain items , stock keeping unit SKU in Retail and in-memory system (SAP HANA, Oracle Exadata, IBM Cognos TM1) making update faster and possibility of including more compressed data into primary memory for analysis.
Gamification/AJAXifying of ERP:
Adobe forms and increasing replaced SAP forms and even Oracle apps forms in AJAXified ERP systems. Augmented reality on AJAX making possible Gamification of ERP.