ETL Vs. EAI

Over recent years, business enterprises relying on accurate and consistent data to make informed decisions have been gravitating towards integration technologies. The subject of Enterprise Application Integration (EAI) and Extraction, Transformation & Loading (ETL) lately seems to pop up in most Enterprise Information Management conversations.

From an architectural perspective, both techniques share a striking similarity. However, they essentially serve different purposes when it comes to information management. We’ve decided to do a little bit of research and establish the differences between the two integration technologies.

Enterprise Application Integration

EAI is an integration framework that consists of technologies and services, allowing for seamless coordination of vital systems, processes, as well as databases across an enterprise.

Simply put, this integration technique simplifies and automates your business processes to a whole new level without necessarily having to make major changes to your existing data structures or applications.

With EAI, your business can integrate essential systems like supply chain management, customer relationship management, business intelligence, enterprise resource planning, and payroll. Well, the linking of these apps can be done at the back end via APIs or the front end GUI.

The systems in question might use different databases, computer languages, exist on different operating systems or older systems that might not be supported by the vendor anymore.

The objective of EAI is to develop a single, unified view of enterprise data and information, as well as ensure the information is correctly stored, transmitted, and reflected. It enables existing applications to communicate and share data in real-time.

Extraction, Transformation & Loading

The general purpose of an ETL system is to extract data out of one or more source databases and then transfer it to a target destination system for better user decision making. Data in the target system is usually presented differently from the sources.

The extracted data goes through the transformation phase, which involves checking for data integrity and converting the data into a proper storage format or structure. It is then moved into other systems for analysis or querying function.

With data loading, it typically involves writing data into the target database destination like data warehouse and operational data store.

ETL can integrate data from multiple systems. The systems we’re talking about in this case are often hosted on separate computer hardware or supported by different vendors.

Differences between ETL and EAI

EAI System

  • Retrieves small amounts of data in one operation and is characterized by a high number of transactions
  • EAI system is utilized for process optimization and workflow
  • The system does not require user involvement after it’s implemented
  • Ensures a bi-directional data flow between the source and target applications
  • Ideal for real-time business data needs
  • Limited data validation
  • Integrating operations is pull, push, and event-driven.

ETL System

  • It is a one-way process of creating a historical record from homogeneous or heterogeneous sources
  • Mainly designed to process large batches of data from source systems
  • Requires extensive user involvement
  • Meta-data driven complex transformations
  • Integrating operation is a pull, query-driven
  • Supports proper profiling and data cleaning
  • Limited messaging capabilities

Both integration technologies are an essential part of EIM, as they provide strong capabilities for business intelligence initiatives and reporting. They can be used differently and sometimes in mutual consolidation.

DataStage – Timestamp from date

This is one of those data type conversions which I occasionally have to do in DataStage, but can never seem to remember. So, I thought I would write this quick post to document  the data type conversion code, which is really easy, once I finally remember how to do it again. 

The TimestampFromDateTime  Function

I use the TimestampFromDateTime function to do this data type conversion. I’m sure there are other ways to achieve the result, but I find this method clean and easy to perform. The TimestampFromDateTimefunction is in the Functions > Date & Time menu.

To populate the function, you need only add your date field on use ’00:00:00’ as your time element


TimestampFromDateTime Function Format


TimestampFromDateTime(%date%,%time%)


TimestampFromDateTime with Time Element example

TimestampFromDateTime(<<Date Field Here>>, ’00:00:00′)

Example transformer code With Date Field

TimestampFromDateTime(Lnk_10.POSTED_DATE, ’00:00:00′)

Related References

End Of Support For IBM InfoSphere 9.1.0

IBM Information Server (IIS)
IBM Information Server (IIS)

End of Support for IBM InfoSphere Information Server 9.1.0

IBM InfoSphere Information Server 9.1.0 will reach End of Support on 2018-09-30.  If you are still on the InfoSphere Information Server (IIS) 9.1.0, I hope you have a plan to migrate to an 11-series version soon.  InfoSphere Information Server (IIS) 11.7 would be worth considering if you don’t already own an 11-series license. InfoSphere Information Server (IIS) 11.7 will allow you to take advantage of the evolving thin client tools and other capabilities in the 2018 release pipeline without needing to perform another upgrade.

Related References

IBM Support, End of support notification: InfoSphere Information Server 9.1.0

IBM Support, Software lifecycle, InfoSphere Information Server 9.1.0

IBM Knowledge Center, Home, InfoSphere Information Server 11.7.0, IBM InfoSphere Information Server Version 11.7.0 documentation

Infosphere Information Server (IIS) – Where you can view DataStage and QualityStage Logs?

During the course of the week, the discussion happened regarding the different places where a person might read the DataStage and QualityStage logs in InfoSphere. I hadn’t really thought about it, but here are a few places that come to mind:

  • IBM InfoSphere DataStage and QualityStage Operations Console
  • IBM InfoSphere DataStage and QualityStage Director client
  • IBM InfoSphere DataStage and QualityStage Designer client by pressing Ctrl+L

Printable PDF Version of this Article

Related Reference

IBM Knowledge Center> InfoSphere Information Server 11.7.0 > InfoSphere DataStage and QualityStage > Monitoring jobs

IBM Knowledge Center > InfoSphere Information Server 11.7.0 > Installing > Troubleshooting software installation > Log files

Essbase Connector Error – Client Commands are Currently Not Being Accepted

DataStage Essbase Connector, Essbase Connector Error, Client Commands are Currently Not Being Accepted
DataStage Essbase Connector

While investigating a recent Infosphere Information Server (IIS), Datastage, Essbase Connect error I found the explanations of the probable causes of the error not to be terribly meaningful.  So, now that I have run our error to ground, I thought it might be nice to jot down a quick note of the potential cause of the ‘Client Commands are Currently Not Being Accepted’ error, which I gleaned from the process.

Error Message Id

  • IIS-CONN-ESSBASE-01010

Error Message

An error occurred while processing the request on the server. The error information is 1051544 (message on contacting or from application:[<<DateTimeStamp>>]Local////3544/Error(1013204) Client Commands are Currently Not Being Accepted.

Possible Causes of The Error

This Error is a problem with access to the Essbase object or accessing the security within the Essbase Object.  This can be a result of multiple issues, such as:

  • Object doesn’t exist – The Essbase object didn’t exist in the location specified,
  • Communications – the location is unavailable or cannot be reached,
  • Path Security – Security gets in the way to access the Essbase object location
  • Essbase Security – Security within the Essbase object does not support the user or filter being submitted. Also, the Essbase object security may be corrupted or incomplete.
  • Essbase Object Structure –  the Essbase object was not properly structured to support the filter or the Essbase filter is malformed for the current structure.

Related References

IBM Knowledge Center, InfoSphere Information Server 11.7.0, Connecting to data sources, Enterprise applications, IBM InfoSphere Information Server Pack for Hyperion Essbase

Printable PDF Version of This Article

 

What Are The DataStage / QualityStage Join Stages?

Three Stages Which Join Records
Three Stages Which Join Records

While chasing an error to which only applied to join type stages, I thought it might be nice to identify what the InfoSphere Information Server DataStage / QualityStage are.  There are three of them, as you can see from the picture above, which are the:

  • Join Stage,
  • Lookup Stage,
  • And, Merge Stage.

All three stages that join data based on the values of identified key columns.

Related References

IBM Knowledge Center, InfoSphere Information Server 11.7.0, InfoSphere DataStage and QualityStage, Developing parallel jobs, Processing Data, Lookup Stage

IBM Knowledge Center, InfoSphere Information Server 11.7.0, InfoSphere DataStage and QualityStage, Developing parallel jobs, Processing Data, Join Stage

IBM Knowledge Center, InfoSphere Information Server 11.7.0, InfoSphere DataStage and QualityStage, Developing parallel jobs, Processing Data, Merge Stage

https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.ds.parjob.dev.doc/topics/c_deeref_Merge_Stage.html

Parallel jobs on Windows fail with APT_IOPort::readBlkVirt;error

APT_IOPort::readBlkVirt Error Screenshot
APT_IOPort::readBlkVirt Error Screenshot

This a known error for windows systems and applies to DataStage and DataQuality jobs using the any or all the three join type stages (Join, Merge, and Lookup).

Error Message

  • <<Link name>>,0: APT_IOPort::readBlkVirt: read for block header, partition 0, [fd 4], returned -1 with errno 10,054 (Unknown error)

Message ID

  • IIS-DSEE-TFIO-00223

Applies To

  • Windows systems only
  • Parallel Engine Jobs the three join type stages (Join, Merge, and Lookup). It does not apply to Server Engine jobs.
  • Infosphere Information Server (IIS), Datastage and DataQuality 9.1 and higher

The Fix

  • Add the APT_NO_IOCOMM_OPTIMIZATION in project administrator and set to blank or 0. I left it blank so it would not impact other jobs
  • Add the environment variable to the job producing the error and set to 1

What it APT_NO_IOCOMM_OPTIMIZATION Does

  • Sets the use of shared memory as the transport type, rather than using the default sockets transport type.
  • Note that in most cases sockets transport type is faster, so, you likely will not to set this across the project as the default for all job. It is best to apply it as necessary for problematic jobs.

Related References

InfoSphere DataStage and QualityStage, Version 9.1 Job Compatibility

IBM Support, JR54078: PARALLEL JOBS ON WINDOWS FAIL WITH APT_IOPORT::READBLKVIRT; ERROR

IBM Support, Information Server DataStage job fails with unknown error 10,054.