A BizTalk Administration Solution for Real-Time Data Warehousing

Share |

Administration, isn't that what we developers discuss after we've constructed the solution? Admittedly, administration can be an afterthought in some software development arenas. Enterprise application integration (EAI) with BizTalk is not one of them. For BizTalk EAI, administration and the core solution are symbiotically linked. In fact, oftentimes you rule out a solution simply because it will be hard to administrate.

Given the importance of administration in a BizTalk EAI solution, how do you begin to think about an administration solution? What tools do you employ? Which questions do you ask? I'm going to share an approach my company adopted in a real-time data warehousing project we did, combining BizTalk 2006 and SQL Server 2005.

Data Warehousing Using BizTalk?

Traditionally, extract, transform, and load (ETL) tools are the primary data warehouse development tools. EAI tools have their place, though. A prior article discussed how and when to choose BizTalk for populating a data warehouse (see "When BizTalk Is Just What Your Business Intelligence Solution Needs").

Some leading ETL experts also recognize the role EAI tools can play in data warehousing. An entire chapter in The Data Warehouse ETL Toolkit by Ralph Kimball and Joe Caserta is devoted to real-time ETL systems. EAI and EAI-like solutions are outlined as the options for real-time data warehousing. Our project had requirements similar to those of the scenarios outlined in the book. We needed low latency and integration with some other applications. Here's an overview of the project:

• We receive data from an application hosted by an education and development application service provider.
• We populate an operational data store (ODS) with XML documents we receive from our provider.
• We generate reports that cross common dimensions from our human resource and scheduling systems by aggregating the ODS data with data from other systems.
• We send updates to our scheduling system as users schedule company courses and we receive schedule changes.

With this context for our BizTalk project, the next section explores our administration solution design approach.

Target Audience

I've always felt that administration is a dialog. You're building an application to communicate with your audience. Questions you would ask while writing a speech or book are similar to questions you ask when developing an administrations solution. Here are some example questions:

• What do your users know?
• What skills do they possess? Are they IT people or other types of office workers?
• How frequently will they reference your solution?
• What features do you provide to make referencing your subject (solution) easier?
• How complex is your subject (solution)?
• Do you share all the excruciating details or do you summarize?
• How do you "express" system problems?

Once we knew who would be using our administration solution and knew what sort of functions we needed to expose, we picked a solution platform.

Core Platform—SharePoint

For our project, SharePoint was the ideal platform mainly because it can combine documentation (document libraries) with applications (Web Parts), and it works well with the Microsoft Office tools our users know well (Excel, Word, InfoPath). Most important, though, our users are comfortable with SharePoint configuration and functionality.

So for this example, our users use SharePoint lists to track discussions and keep lists of defects and problem resolutions. With document libraries and tools such as InfoPath, XML documents commonly used in BizTalk solutions can be captured, viewed, and edited. Users were able to run reports, selecting from a SharePoint list of report URLs.

Some other compelling reasons for SharePoint are the new SharePoint adapter bundled with BizTalk 2006 and BizTalk features such as failed message routing (see the article "Exceptional Error Handling with BizTalk 2006 and InfoPath"). Combining the new BizTalk features with InfoPath provides you a platform for trapping, exposing, examining, and even fixing documents that cause problems in BizTalk.

Although SharePoint was our primary administration platform, we supplemented administration duties with other platforms and considered some other features outside of SharePoint.

Supporting Cast on Other Platforms

Microsoft Operations Manager (MOM) is the central nervous system of our application and network infrastructure monitoring. Every tic, itch, and spasm our applications produce is logged in MOM. Important for data warehouse administration are things such as flash totals. (The article "Incorporate Data Warehouse QA Checks into MOM 2005" discusses making flash totals available to MOM.)

Although today's requirements don't demand it, we considered a workflow platform for future requirements. So, for example, if approvals became required before repairing a failed message in BizTalk, workflow is an ideal platform for facilitating the approval. New workflow features in SharePoint and products such as K2.NET are good choices for working with SharePoint and Office.

Metrics was another requirement consideration, but again today's requirements don't demand it. BizTalk Business Activity Monitoring (BAM) is ideal for collecting BizTalk metrics. Here is a BAM article that explains how to log numeric, textual, and chronological data about any step in a BizTalk orchestration.

As we built our administration solution, we applied a healthy dose of pragmatism. Aside from the platform and user community, we considered other issues discussed in the next section.

Today's Solution, Tomorrow's New Opportunity

A wise systems analyst once told me that the solution we build today will one day become the new "opportunity" (read problem). In a changing business, "opportunities" often arrive sooner than we would like. You don't want to replace your administration solution before changing your core solution. So, pay careful attention to the following ideas.

1. Long-term customer satisfaction for a software implementation is often more a function of what you put in place to support a product. Clear documentation is essential, but so are useful application administration features.
2. Many times, the only exposure users have to your integration solution is through the admin feature you provide. A user's opinion of your application may be totally dependent on what he or she thinks of your admin features. So, include adequate time in your estimate for administration. Although we found justifying the time difficult, we believe we have a healthier support relationship because of good administration features.
3. We decided that a completely hands-off administration implementation is also not a good idea. A hands-off solution will be expensive, and changing business needs often require major solution enhancements to support the new business changes.
4. EAI solutions often involve multiple systems and business processes. A good administration solution supplemented by some level of user vigilance is necessary for keeping a solution running. Oftentimes, an automation solution is replacing a manual process, so persuading a client to devote some user time to monitoring the process usually is not difficult.

Plan for Long-Term Success

Designing an administration solution for your real-time data warehouse is important to the long-term success of your BizTalk solution. Picking a platform your users are comfortable with is a good start. Even more important, though, is understanding the skills and needs of your administrative users and building adequate time in the plan to implement the administration solution.