Looking beyond to ETL from SSIS, next door can be EII - Enterprise Information Integration.

Below paragraph is pre-face of the article, and it's mentioned about inspiration of the article, if you wish to skip it please go to next paragraph. :-).. But I would recommend reading it.

Recently many things cooking around Microsoft Data Services, I hope it wills surely include more services which can be much helpful to implement difficult EII scenarios. New term Database as a Service (DaaS, name cloning from SaaS). Frankly speaking these days war between terminologies is far higher then technologies, aaahh forget about it, it's very confusing which do what!!!! nowadays, terminologies are becoming face of product and patterns are became abstract :-). Anyways let's come to core part of article, actually I was working on very nice requirement where system need to provide a merged data view from multiple system (heterogeneous sources and also multi-cultured also :(..). Previously we used to do complete data integration (As EAI Implementation) with moderate latency across whole system into one web application which provides grid view and reports; it was very frequently used by users and very important too. But this integration consumes 200GB of database for just viewing and reporting purpose (we keep only 15 days data and for that we have to invest minimum of 250 GB data storage on SAN and other resources for implementation of EAI like BizTalk server, message box database, traffic, bandwidth etc...). It was clearly an requirement of EII implementation, but previously it hadn't been identified as EII requirement and individual implementation, too. But after massive discussion (fights) we were implement that system as EII with use of SSIS (SQL Server Integration Services 2005) only. From that time I had the inspiration of this article. Thanks for reading it.

Back to Business:

Microsoft.SQLServer.Dts.DtsClient name space, I am SSIS developer too, but was not much familiar about its functionalities earlier. As a problem I described in above paragraph we design few complex SSIS packages with heterogeneous data sources and data views, merge joins, lots of shorting, querying, cleansing, etc. Here for demonstration I am unable to saw original design but will follow one sample design I have developed specially for article.

In below design, I am trying to querying to two different data sources which have entirely different databases and connected through one common key and I am trying to generate single dataset for viewing and reporting purposes. You can see below data table snapshots where first figure shows first names of people and second table shows last name of people. Both tables are in different database. Here data sources could be any kind of supportive sources inside SSIS for the concept.


Fig 1. Database Tree (two different databases - test_1, test_2).



Fig 2. Table 1 (Db1- table contain first name information).



Fig 3. Table 2 (Db2 - table contain last name information).



Now have a look to sample SSIS package, just I am doing merge operation and storing into data reader as a destination.


Fig 4. Sample SSIS package.



I have also developed one console .Net application for testing and demonstration purpose to call SSIS package as single point data connection which can provide me processed and integrated data within one single record set. Below is sample snapshot of coding, in real-time solution we used WCF services to provide external domain and other application as an exposer of the EII implementation. But here I am trying to demonstrate through C#.Net Console Application.


Fig 5. Console Application Code (C# Console Application).



Fig 6. Receiving data into one data reader.


Fig 7. Output.



You can see above snapshot of output that I received integrated query's result in single set.

Isn't it good? There are many benefit of such implementation I can say. The major benefit is shrink EAI landscape which will decrease support team size (Cost... Managers are very happy to listen, additional benefit) and we can free resources to better performance of it. Another important benefit is, we can avoid non-require expose of database and our precious data to other system which only need for viewing purpose, we can archive it by providing and implementing parametrise/on demand EII Data Services (SaaD :-D). I don't want to go inside other benefits we can archive from such implementation but defiantly it could turn into beneficial implementation for every organization which has such requirement.

Please share your views and comments as well as if you have any suggesting please free to suggest me on nilayparikh@gmail.com

Thanks for visiting my blog.

Enterprise Information Integration - A First Look

Nowadays, A trend I noticed in IT Professionals, if any technology start with E** will be on demand, and it’s not absolutely wrong because we have seen in last one decade, “E” runs Enterprises. We have live examples of EAI (Enterprise Application Integration), EDI (Electronic Data Interchange), ETL (Extract Transform Load), ERP (Enterprise Resource Planning), ESB (Enterprise Service Bus), and I can bet about EII (Enterprise Information Integration) that would be not an exception among the group of “E”-Technologies. In this competitive edge, every enterprise wants to best data infrastructure with cost efficiency, and that’s bet “innovations never going to end for this requirement”. In this globalization most of companies are operating form different countries with many cultures, people, languages, and these all brings different applications, different vendors, different standards, different platforms, different technology and systems’ cultures. Now, these things don’t making life easy for CIOs, and they makes our life tuff [:-)] like universal food chain (a big fish eat a small one). Generally to handle such scenarios we are implementing EAI, ETL solutions for a low latency/live data integration and historical data integration respectively but still one unnoticed scenarios need some dedicated effort from the technology community these days which is Information Integration, generally we are fulfilling the purpose of Information Integration by data integration and EAI mostly but such implementations will be not cost competitive compare to dedicated EII implementation because EII is gaining traction for enabling data integration without the need for the physical instantiation of the integration. In other words, EII adds integrated reporting capabilities while minimizing impact on existing systems. But before deciding the integration pattern we must have clear idea what we should integrate with EII patterns and what shouldn’t.

Let’s have look to some situations where EII can be ideal and cost/effort effective solution for any enterprise.

1. Connecting structured data with unstructured data, this can take advantage of EII’s capability of leaving data in place that could not dramatically increase overall storage requirements. (this you can also count as disadvantage of EAI/ETL solutions in such Information Integration scenarios)
2. When immediate data change required in response to the data view. (This requirement will be not easy and cost competitive for EAI/ETL).
3. Some operational and regulatory reporting where the data needed is not completely integrated in one place.
4. When data transformation is relatively light or nonexistent and just getting the data together for integrated query is the biggest challenge.

At bottom line I can state EII is for implementing such scenarios like integrated queries, connecting data without increasing storage requirements and low latency responses.


So, what could be definition of “EII”, I have referred one definition constructed by “Integration Consortium” and I would like to share with you all, EII is the integration of data from multiple systems into a unified, consistent and accurate representation geared toward the viewing and manipulation of the data. Data is aggregated, restructured and relabeled (if necessary) and presented to the user.

Integration Type	Data	Purpose	Audience
Data Integration (ETL)	Historical	Trend Analysis	Decision-Makers
Application Integration	Live Data	Synchronization	IT Organization
Information Integration	Live	Productivity	End Users

Now after understanding of the EII, the oblivious question would come in mind about implementation and the instrument (tool). Many of technology vendors have already initiated and introduce some of tools focusing or additional functionalities in existing tools which support such EII implementations. The instruments like SOA, Web Services, EAI / ETL tools could be the best picks for implementing such requirements. I believe the following requirements are the key of any EII implementation.

• Service Oriented Architecture
• Mata Data Management
• Semantic Information Model
• Dynamic Aggregation

The above key topics I believe will be known to any Integration Architect, so any such program or tool can handle or facilitate the above keys to implement in aggregated manner could be used to impermanent EII solutions.

For BizTalk Server, I see very bright scope for such kind of implementation with other coupled Microsoft Technologies like Windows Communication Foundation, Windows Workflow Foundation, MS SSO, Host Integration Server, SQL Server Analysis Services, and SQL Server Integration Services. Additionally the innovative steps like ESB by Microsoft Patterns and Practices are giving very high level of confidence to such implementation through Microsoft Platform.

If I talk about my experience with EII system then yes, with the grace of God I got such precious opportunity to put the first EII implementation in GO-LIVE state at my organization.

I will try to come up with more articles on such immerging Enterprise Information Integration with BizTalk and Microsoft Technology specific articles. Believe me there is lots of things to share with you about my exciting experience for Enterprise Information Integration.

Thanks for BEAR me during the article. Your valuable feedback and suggestion are welcome to nilayparikh@gmail.com