Tuesday, July 31, 2007

ExcelToCI limitations: when not to use it

Since it's introduction with PeopleTools 8.4x, ExcelToCI has generated excitement particularly among consultants running implementations. Finally there was a tool that allowed consultants with little or no technical knowledge to quickly load data into the system.

Now, as it normally happens in these situations, a good tool may become a bad choice if we are using it in a way it wasn't designed for. Personally, I've seen people trying to use ExcelToCI to run complex data loading resulting in unexpected delays due to the need to build ad-hoc tools for conversion.

What are the most significant ExcelToCI limitations? You may find others, but from my experience, these are quite important:
  • ExcelToCI does not allow to determine the order in which fields are loaded into the application. The order is determined by the tab order defined at the page level in Application Designer.
  • Loading large data volumes becomes unpractical for performance reasons. Excel could easily hang when manipulating spreadsheets with over 10,000 rows.
  • Excel becomes nearly blocked when executing the load. It's difficult to perform any other action on the same workstation, and particularly to load several files in parallel.
  • Can only run in Add or Update mode, but there is not hybrid option to first try to Add and then Update is the row was present.
At BNB, we built a tool called FileToCI (Ok, not much marketing brainstorming here!) that deals with most of these limitations:
  • Lets you choose in which order you want fields to be loaded.
  • Loads from CSV files or database tables, which are much easier to handle than Excel files.
  • Lets you load different files in parallel. Processing is done at the server level, without capturing your local resources.
  • Includes a Hybrid load option, which only adds the rows that were not present before the load and updates the remaining ones.
Again, I'm not saying ExcelToCI is useless. That would be far away from the truth. Reality still is that for setup data load and certain types of conversion, ExcelToCi remains a very intuitive and powerful tool.

What have been your experiences with ExcelToCI? Have you used it for massive data loading?

How BPEL adds value to PeopleTools 8.48?

One of the key enhancements delivered in PeopleTools 8.48 was integration with Oracle BPEL Manager. Oracle's description of BPEL is:

Oracle BPEL Process Manager, a member of the Oracle Fusion Middleware family of products, enables enterprises to orchestrate disparate applications and Web services into business processes. The ability to quickly build and deploy these processes in a standards-based manner delivers critical functionality for developing a Service-Oriented Architecture (SOA).

Now... how this translates into real-world cases?

Recently, we had to design for one of our customers a Web Service based integration of PeopleSoft Financials with a Third Party Billing system. One of the integrations consisted in the Third Party system sending information to PeopleSoft Financials in order to perform the following actions:
  • Create the customer if it was not present.
  • Create the invoice as a pending item in PeopleSoft Receivables module.
  • Run Receivables Update process.
  • Create the deposit and apply it to the item.
  • Run Receivables Update process again to update customer's balance.
Before BPEL, if we wanted to create this integration, a solution would have to create a Web Service in PeopleSoft that encapsulates all these activities. The problem of this approach is that this new Web Service would not easily reusable, as most consumer applications will only require part of the operations performed in it.

With BPEL, we can keep Web Services at the right granularity to help making them reusable. Following with the example, we would just make sure the following Web Services exist (taking into account that some of them may be already available in the delivered product):
  • Customer creation.
  • Pending item creation.
  • Receivables update processing.
  • Deposit creation and application.
BPEL would then be used to orchestrate these Web Services, without requiring to build aggregated and poorly reusable Web Services in PeopleSoft.

In summary, BPEL seems to bring PeopleSoft applications to the next level in what relates to integration with Third Party applications. Also, being BPEL one of the keystones of Oracle Fusion Architecture, it seems a wise decision to gain some BPEL experience as this might be a highly demanded skill in the near future.

Note: Although Oracle ships its BPEL Manager product together with PeopleTools 8.49, this license only covers design-time use. For runtime use, you should purchase Oracle BPEL Manager separately. For more information, please refer to:



Welcome to my Blog!

My expectation is to use this media to communicate and share my personal experiences in the IT field, and more specifically in PeopleSoft implementations.

Many thanks and happy blogging!