Data Transformations in EII

From time to time I get asked about Ipedo's data transformation capabilities.  I was asked again a few weeks ago by a prospect, so I decided to put two of our developers to work.   I asked them to write a few posts that explain what Ipedo XIP can do in the area of data transformation.

Now we have two great entries that describe both halves of Ipedo's dual-core query engine: one on transforms using SQL, and one on transforms using XQuery.

Both of these just scratch the surface.  Just like the old Chinese expression "A long journey starts with the first step," these two entries only begin to expose the rich set of transformation capabilities available.  But they do cover some common cases in EII, which are often different from ETL needs. 

Let us know what you think.  If you want more examples, all you need to do is ask.  In the words of another Chinese proverb, "Dig a well before you are thirsty."

Calculating the ROI of EII: Application Maintenance (Example 5/5)

Because the EII layer can easily handle additions or changes to underlying data sources, organizations can focus on maintaining the EII layer and not the maintenance of every application and its connection. If a data source changes, the administrator only needs to update the mapping in the EII layer; all reports remain unchanged as they use the schema defined in the EII layer.

To figure out if this can save you money, think about this question: How much does it cost to maintain point-to-point connections among all your applications and data sources?

                                                                                         

	Example	Formula
Conventional   Approach
Cost to   develop a customized connector between two applications ($)	$10,000	A
Number of   connections required per year	25	B
Application   maintenance cost using conventional approach ($)	$250,000	A * B
Cost to change   a report because a data source changes ($)	$1,000	C
Number of   changes to reports required per year	200	D
Application   maintenance cost using conventional approach ($)	$200,000	C * D
EII   Approach
Cost to   develop a customized connector between two applications ($)	$0	E
Cost to change   a report because a data source changes ($)	$0	F
Cost to change   a data source mapping in the EII layer ($)	$100	G
Number of   changes to reports required per year	200	D
Application   maintenance cost using EII approach ($)	$20,000	G * D
Potential annual   cost savings from EII ($)	$430,000	(A * B) + (C *   D) - (G * D)

In this example, a company that eliminates customized programming for connecting applications and changing reports will save $430,000 by using EII.

• What value-add projects could your IT team complete if they are not changing report formats?
• How much more motivated would your developers be if they spent less time on software maintenance?

That concludes our five-part analysis.  Hopefully you've found some areas where there might be cost savings.  If you did and would like to take things further, here are some suggestions:

1. If you want to use your own numbers in the above examples, you can request the companion Excel spreadsheet from Ipedo.
2. If your business situation requires a customized financial model, one of our analysts can provide assistance with the process, or even build an interactive spreadsheet based on information that you provide.
3.  Once you complete your financial models and convince your team that EII looks good on paper, a logical next step is to develop a proof-of-concept application that demonstrates cost savings and/or productivity improvement in a controlled environment. Ipedo can help with this as part of the sales process.

Calculating the ROI of EII: Data Consolidation (Example 4/5)

Managing data costs a lot of money.  Just ask any CIO.  In any given company, there is a lot of money spent (and wasted) moving and copying data.  Enter EII.

Using EII to create a virtual, centralized database that cuts across disparate systems can reduce the cost of data maintenance, and eliminate the need for additional investment to store replicated data. Even with a conservative estimate of $50K per data mart ($25K hardware purchase and software licensing, $10K development cost, $5 software maintenance license, $10K personnel to manage the data mart), this gets expensive quickly.

The ROI-unlocking question: How much of your IT budget goes towards moving data around the enterprise and storing copies for different applications to use?

                             

Top-Down   Approach	Example	Formula
Annual IT budget   ($)	$10,000,000	A
% of resources   spent on data migration and replication (%)	25%	B
% improvement   with EII (%) (estimate)	10%	C
Potential   annual cost savings from EII ($)	$250,000	(A * B) * C

In this example, a company that spends 25% of its $10 million IT budget on data migration and data replication would save $250,000 (2.5% of overall IT budget) by using EII to complement its other data integration initiatives.

 

                       

Bottom-Up   Approach	Example	Formula
Cost to deploy   single new data mart ($)	$50,000	D
Number of data   marts deployed per year	10	E
Potential   annual cost savings from EII ($)	$500,000	D * E

In this example, a company that can eliminate 10 data marts per year by using EII will save $500,000.

And with all the focus on CDI and MDM these days, these upside benefits are sure to resonate:

• What is the value of having a “single version of the truth” based in the original data sources, rather than copies of data that become obsolete once the source changes
• How much time do you spend reconciling different versions of data residing in different locations?
• What politics are involved in getting permission to replicate data in your local data mart?
• How could you better deploy your IT resources if they did not have to manage a data mart?

That's it for today.  Next time, our final installment.

EII Haiku

In high technology, we often do things that have never been tried before.  I'm fairly certain this is one of them. 

Since I was recently accused of being too serious with my ROI posts, but not wanting to give way to the senseless prattling of many blogs, I offer you EII Haiku.  I like to think of them as lighthearted but deep.  Enjoy.

Queries federated
Distant rows and columns meet
My warehouse unscathed

Web Services, SOAP
Loosely coupled, lightly known
Join them, EII

Data is scattered
Metadata, SQL meet
All becomes as one

P.S. - If you would like to share your own, please feel free to do so in the Comments section.

Calculating the ROI of EII: Developer Productivity (Example 3/5)

With this entry, we'll move from the business side of the house to the development side of the house.  Yes, EII has something for everyone.

Recent IDC research reports that the average enterprise spends 40% of its IT budget on data integration (ETL, EII, EAI, etc.). Custom programming to connect disparate data sources is expensive to develop and maintain, especially in an environment of continual change. The unified abstraction layer that EII provides allows developers to write to one common interface to access multiple data sources, reducing the total development effort for new applications. The ability to write queries and not code allows departmental control of data integration, which saves the time and effort of going through a corporate IT group. These queries can be reused among different groups, further saving money.

So, What percentage of your IT budget do you spend on data integration tasks?   There are two ways to look at it.

                             

Top-Down Approach	Example	Formula
Annual IT budget ($)	$10,000,000	A
% spent on integration (%)   (ETL, EAI, EII, etc.)	40%	B
% improvement with EII (%)   (estimate)	10%	C
Potential annual cost savings   from EII ($)	$400,000	(A * B) * C

In this example, a company with a $10 million IT budget would save $400,000 annually (4% of overall IT budget) by including EII in their overall data integration strategy.

 

                                                           

Bottom-Up Approach	Example	Formula
Number of developers on   integration projects	20	A
Annual cost (including   benefits) of developers ($)	$120,000	B
% improvement with EII (%)   (estimate)	10%	C
Potential annual cost savings   from EII ($)	$240,000	(A * B) * C
Person-weeks for integration   projects over a year	1,000	D
Annual cost (including   benefits) of developers ($)	$120,000	E
% improvement with EII (%)   (estimate)	10%	F
Potential annual cost savings   from EII ($)	$240,000	(D * (E / 50)) * F

In these examples, a 20-person team would save $240,000 annually using EII as a part of their data integration projects.

And, as usual, some additional upside potential:

• What creative revenue generating or cost reducing functions will your team develop in the time they save by not rewriting software to accommodate changes in infrastructure or data format?
• How much more productive will your developers be if they focus on writing interesting applications rather than on more basic data integration tasks?

All for now.

Calculating the ROI of EII: Analyst Utilization (Example 2/5)

Here's one of my favorites, inspired by a conversation we had with a prospect back in 2005.  It's a great product management moment when the benefits of your product dovetail so nicely with a problem a customer has.

To wit, a vice president at a commercial bank that did not use EII told us that the analysts there spend about 60% of their time on data collection and only 40% on analysis. Moving from a 40%-60% split to a 90%-10% split means more than doubling (125%) individual analyst productivity. Thus, one analyst can do the work of slightly more than two. EII allows analysts to simplify the data collection process and spend more time producing useful, actionable information.

So, here's the question to unlock your potential ROI: How much time do your analysts spend searching for data?

                                   

	Example	Formula
Percentage of   time analysts spend on value-added activities pre-EII (%)	40%	A
Percentage of   time analysts spend on value-added activities post-EII (%)	90%	B
Number of   analysts on your team	10	C
Annual cost   (including benefits) of analyst ($)	$100,000	D
Potential annual   cost savings from EII ($)	$555,556	((B - A) / B)   * C * D

In this example, a team of 10 analysts does about 20 person-days of analysis per week pre-EII and 45 person-days per week post-EII, for a 125% improvement. If the amount of work was fixed at 20 person-days per week, a team of 5 analysts (actually 4.44) could do the job that previously required 10.

Additional Upside

Still need more ammo?  Think about quantifying these:

· What new discoveries will your analysts make when they have more time to focus on analysis?

· How many errors will be eliminated by reducing manual handling of data and what will the cost savings be of eliminating these errors?

· By spending more time using data rather than looking for it, how much more valuable will the analysts become to the organization?

· What is the effect on job satisfaction if people are doing what they enjoy, rather than just collecting data?

That's it for this installment.  Three more to go.  Stay tuned.  If you can't wait, check out the complete ROI guide, which can be found in Ipedo's EII Zone.

Calculating the ROI of EII: Increased Flexibility (Example 1/5)

As we move into the planning and budgeting season, the time seems right to start talking about ROI.  At Ipedo, we've spent quite a bit of time analyzing how our customers can save money and improve productivity using EII.  In this five-part series, I'll show you how to do it.

What kicked off our study of the financial benefits of EII was a 2005 study by The Data Warehousing Institute (TDWI).  In it, 69% of respondents said that data integration issues are a barrier to implementing new applications. The three main issues they listed were poor data integration infrastructure, lack of a business case and inadequate funding, and data quality and security.

This series will help decision makers responsible for data integration projects develop a business case that quantifies the potential cost savings of an EII project based on their organization’s specific information integration needs.  Each installment will focus on a specific area where EII can generate cost savings and productivity improvements:

• Increased flexibility to respond to changes in IT requirements
• Better analyst utilization
• Higher developer productivity
• Lower data consolidation costs
• Less expensive application maintenance

A few housekeeping items before we start.  In the  scenarios, we provide examples to illustrate the potential cost savings. Each example is for a departmental EII implementation, as opposed to an enterprise-wide deployment. Most organizations start with a single use for EII and then scale it to different departments once they achieve their target results and become comfortable with the technology.

The cost savings numbers represent the ongoing annual savings, after the initial cost of implementing the EII system. This cost of an EII system will vary, depending on your specific needs. For the departmental-scale examples in this document, a 1- or 2-CPU system with matching failover server will be sufficient in most cases. A complete Ipedo deployment for a scenario, including software license, maintenance, training, and some customization through our professional services group will cost around $200,000. A bare-bones EII system can cost as little as $50,000.

Your savings may be higher or lower than what we illustrate, depending on your specific use case and the cost of implementing your EII solution. To simplify testing different scenarios, running your own numbers, and performing “what if” analysis to determine your own cost savings, Ipedo has a companion Excel worksheet. If you are interested, let me know and I can send you a copy.

So, with all that out of the way, let's get down to brass tacks and look at how Ipedo can save you money by increasing flexibility in a reporting application.

Increased Flexibility in Reporting

In today’s information economy, having access to data across the enterprise gives managers and executives a significant advantage in making real-time decisions that affect their business. EII allows companies to respond quickly to changes in market conditions without incurring a major IT investment.

What effort is involved to link new data sources to your existing reports or applications?

	Example	Formula
Conventional Approach
Cost to modify a report to integrate a new data source ($)	$1,000	A
Number of changes required annually	200	B
Cost to modify an application to integrate a new data source ($)	$5,000	C
Number of changes required annually	50	D
Reporting cost using conventional approach ($)	$450,000	(A * B) + (C * D)
EII Approach
Cost to modify a report to integrate a new data source ($)	$100	E
Number of changes required annually	200	B
Cost to modify an application to integrate a new data source ($)	$0	F
Number of changes required annually	50	D
Reporting cost using EII approach($)	$20,000	(E * B) + (F * D)
Potential annual cost savings from EII ($)	$430,000	(A * B) + (C * D) - (E * B) + (F * D)

In this example, a business unit that modifies four reports and one application a week will save $430,000 by using EII.

Additional Upside

These are a few soft benefits.  You can either try to assign a value, or you can just treat these as icing on the cake.

• By getting useful information sooner, what is the value to your organization?
• What new insights can your managers discover by having access to a unified view of disparate information that EII provides?
• As new regulations appear, how quickly can you respond to generate the necessary documentation? What is the burden on your organization to create these reports? Whose budget covers producing regulatory reports?

That's all for now.  I'd be curious to hear back on how people think these numbers match their own situations.

Stay tuned for our next installment.

EII and the Bus

A picture is worth a thousand words, as they say.  Based on all the words that have been written about ESB, I'd say the picture below is worth even more.

Bill Inmon had the following picture of why a bus is not an integration panacea is his recent B-Eye Network article The Bus as a Substitute for Integration, Part I:

Now you could read the article, but this picture sums it up, doesn't it?  That's how it struck me.  Creating a powerful, yet simple, picture of a complex issue is hard, and takes a foundation of expertise in a topic area, and still then some sweating over. (For some great examples of excellence in infographics, I highly recommend The Visual Display of Quantitative Information, with my favorite Napoleon's March chart.)

Bill Inmon certainly knows a thing or two about data integration, which is perhaps why this picture is so poignant.  And while it is not about EII per se, it reminded me of many conversations I've had with ESB proponents over the past year.  The point I always try to impress on people is that ESBs are good at reliably moving data, and they are (thankfully) more open than some of the older bus technologies, but they still don't help you integrate the data.  The reply to which - and I always love this one - is "All you need to do is write some code..."  If I had a dollar for every time I heard that one...

Here's a better alternative.  Create a parameterized View of what you want in an EII product like Ipedo's.  A View is essentially a virtual representation of the data you want, joined, cleaned, and - importantly - semantically correct.  Then, instead of the typical bus flow of Get A [invoke custom code] then Get B [invoke custom code] then Get C [invoke custom code] and so on, the View queries multiple data sources and returns the information your application needs to the bus.  It's not magic, just a cleaner, easier approach with a more suitable tool.

So before you jump on the bus bandwagon, take another look at that picture and think about how EII can help.

 

Data Integration in a Software as a Service World – Time for Data Services

What does data integration look like when you’re inside a Software as a Service (SaaS) world? Some things look very much the same, and some things look quite a bit different.

What looks the same are the issues. Simply put, how do I get data into the application, how do I get information out of the application, and how do I make sure that it’s the right information? The last item can often be the trickiest, dealing with semantics and data quality issues.

The ways you get data in and out, and how you deal with data quality, look quite a bit different. Most applications offered as a service have a set of APIs available for integration, and most are available as some kind of Web Service. Though Web Services have been around for a few years now, the average IT department does not have the same level of experience with them. So we have a whole new paradigm for dealing with applications that looks a lot different than using an ETL tool, building a data warehouse, or using the TIB.

The other thing to think about is that SaaS applications do not live in a vacuum. The main driver is to connect existing applications (Software as Itself?) with SaaS applications. So now we have to combine old school integration (ETL, EDW, TIB, SQL) with the newer SOA-based integration. Not as easy as it may sound. (In fact, one customer mentioned that the group dynamics among Salesforce.com admins, SOA gurus, and the EDW crew is a challenge in itself.).

All of this got me to thinking that this may be the perfect time for ‘Data Services.’ The term has been gaining momentum since Gartner started talking about it a while back. It’s what we do here at Ipedo.  The basic idea is to take integration functions and make them available as a service via a platform. So the goal is not to move bulk data in batch, but to expose information and make it accessible on demand.  Included in the data service are security, performance optimization, semantic mediation, and potentially even data quality.

To make things more concrete, here are a few examples. You want to combine information from Oracle, DB2 and an order management application available as a Web Services, apply some logic, and update a customer record in Salesforce.com. Right there you have two kinds of SQL, SOAP/WSDL, and the Salesforce.com API (SOQL) to deal with. Or go the opposite direction. Your company is moving from Siebel to Salesforce.com, and you still need to create those weekly reports in Crystal. So you need to deal with the Siebel API or data mart, the Salesforce.com API and somehow get it to output in SQL (and it would be nice if you didn’t have to start from scratch).

There are more examples, to be sure. But one thing becomes clear to me. In the future, there may be no way stations for data. It’s needed inside the firewall, outside the firewall, in the applications you own and the ones you rent. So it would seem data services offer the perfect solution.

Software as a Service requires ‘Integration as a Service,’ aka Data Services. But unlike SaaS, Data Services also need to deal with the past twenty years of application data inside your company. So while I’ll never bet against there someday being a new startup called Integration.com, for the foreseeable future a Data Services platform inside your firewall that can deal easily with both internal applications and rented SaaS applications seems like just the ticket.

I’ll be digging into this idea in more depth. Watch this space.

EII for the Data Warehousing Crowd

I was happy to see a new article in this month's Teradata Magazine titled "EII, ETL or EAI: Making sense of the alphabet soup." Written by well-regarded BI expert Claudia Imhoff, the article does a nice job describing when to use each. 

I also like the subhead: "Here is your common-sense guide to modern-day integration."  What's not-so-subtly suggested by the subhead is that integration technologies continue to progress, so anyone with the words 'data' or 'architect' who does not know about EII (the newest of the three) is, to put it gently, out of date.

Perhaps the best thing is that the article was published in Teradata Magazine.  As I've written in the past, EII is not the enemy of the EDW.  Lots of companies are using the two in conjunction.  Even more are considering using the two together.  Anyone who works with Teradata needs to know the advantages EII can bring to their deployments.

I've been following Claudia's writing on this topic for a while now, and I can say there are few who know it better.  Plus she's well known to the BI and EDW crowd, so she's got street cred.  Which is good, because Teradata users need to hear this kind of stuff from people whose opinion they trust.

One last thing.  While I agree with her statement that no one should consider building a data warehouse using EII, one thing not specifically mentioned in the article that may be of interest is the idea of using EII as a prototyping tool for new EDW deployments.  It can be very cost effective.  Once the utility is proven, move up to something more scalable.  It's something we've had success with in our work as a Teradata partner.