You have EcoSys and you want to integrate it with your Accounting or Enterprise Resource Planning (ERP) solution, maybe your trying to get your actual costs from J.D.Edwards, SAP, or Oracle Financials. Your IT experts have provided you with a file that should load into EcoSys, and has even tested successfully on the ERP system, but is not loading successfully into EcoSys. To add to your problems the finance system sends you a one-shot file from the previous day so your load failure means a loss of data too.
EcoSys comes with some extensive capabilities for loading data. The client import feature is great for small batch and ad-hoc loading of information. It also comes with batch processes and web services that software engineers can use to integrate with other systems.
EcoSys is great and loading files from all sorts of sources, but it does insist that key values are an exact match. If there are any missing codes or keys then EcoSys will probably reject the record.
The most common causes are:
- Your hierarchical identifier for your Cost Object cannot be found. This happens when the actuals you are loading, for example, are expecting the fully formed project to be in EcoSys but there are differences between EcoSys and your financial systems
- A cost account is missing. Project controls usually uses a subset of the cost accounts, known as cost elements in SAP, to define spend in terms of labor, materials, and equipment, down to the carpenter, re-bar, or backhoe. On the other-hand the finance system will have additional codes used to handle accounting practices, such as depreciation, or corrective journal entries. Consequently, the finance team may well be constantly tweaking values that are not known to EcoSys, perhaps they fall through the filters you created for the extract, and now EcoSys doesn’t know how to deal with them.
So, you need to be able to process the load files, and handle the errors that cause them to fail in a way that does not loose data, and that informs you when a problem exists.
The solution is to leverage tools specifically design to manage complex integrations. These tools are known as Enterprise Application Integration (EAI) tools. The first step is to ask your IT counterparts if they have such a tool, examples are SAP XI, Tibco, Microsoft BizTalk, IBM MQSeries, or Oracle fusion. If they leverage these tools, and have the bandwidth to help, then you can use the sophisticated functionality they provide to:
- Process the exports and imports in formats specific to your needs, e.g. compatible with EcoSys
- Query EcoSys to see if it has all the key information it needs to successfully handle the import
- Route requests based on the values being provided
- Transform data from one application to a format suitable for another, e.g. Oracle Financials to EcoSys
- Handle errors, and save failed data so that it can be resent once the issue is resolved
- Notify you of any problems with the interface
If you don’t have access to these tools then there are some lightweight, open source alternatives that will fulfill the same tasks specifically for your EcoSys instance.
The K2 Approach
At K2, we like to use Apache ActiveMQ together with EcoSys’ Web API. With this toolset, we can develop processes to gather data from a flat file load or make a call directly to the application, transform the data into a format that will load into custom EcoSys spreadsheets or call EcoSys Action Batches.
We can handle errors records, saving them until coding in EcoSys can be fixed, and then resubmitting them without trying to recreate the files from the donor system or keeping an ever-growing directory of past files. We leverage email connectivity to notify you of errors and, because of the nature of our toolset, we can provide the ability to fix problems without having to know how to code or understand software engineering.
We can also be flexible in the way we build these solutions, leveraging local solutions and programming languages where possible, to provide a better chance for your IT team to maintain our solutions after we have solved your problem.