Blue Care Network of Michigan
“We use most of the Bradmark Surveillance functionality that is available for Sybase replication monitoring. In addition to repository alerting, we have a number of different alerts configured for all the different issues that may occur, including exception transactions when the server goes down or the connection strength falls off.”
Blue Care Network of Michigan
Blue Care Network of Michigan is an HMO that uses the hosted version of TriZetto’s FACETS application to manage the bulk of their membership and claims processing functions. In order to make a local copy of the daily transaction data for running critical operational reports and to populate an enterprise data warehouse, they turned to Sybase Replication Server. To harden the architecture with high-end monitoring of the replication process, Blue Care Network of Michigan selected Bradmark’s Surveillance monitoring and event management tools.
- Blue Care Network can collect detailed statistics on data
volumes and latency, all while monitoring the replication solution in
easily visualized form.
- Uses an external, hosted third-party solution
- Maintains a corporate operational data store for internal reporting
- Populates a customized, enterprise analytical data warehouse
- Proactive replication monitoring with multi-level alerts
As the lines blur between online/hosted offerings and standalone
products, unique issues arise for organizations. A hosted online
solution has advantages, such as anywhere access and a single point of
update to simplify maintenance. On the other hand, a standalone system
in the corporate server room has the advantage of absolute control over
the software and –even more importantly– its data. To create an optimal
solution, organizations seek blended solutions that embrace the best of
Blue Care Network of Michigan (BCN) is Michigan’s largest health maintenance organization, and is a wholly-owned, nonprofit subsidiary of BlueCross® BlueShield® of Michigan. With data management requirements typical of an HMO, BCN has a large data store encompassing membership information and complex insurance plan options that dictate the amounts paid by BCN and its individual members. BCN uses the hosted version of FACETS. TriZetto’s FACETS is a complete healthcare service solution running Sybase ASE. ASE allows FACETS to meet the high transaction rates and volumes required by a large health care organization like BCN. Blue Care Network of Michigan is based in Detroit, while their hosted FACETS database resides in Denver.
Replication Enables the Best of Hosted Functionality with Corporate Data Storage
To run efficiently, an HMO must constantly monitor and analyze its data for membership trends and carefully track its current financial picture. A quarterly or monthly accounting is insufficient feedback to steer a viable HMO. What makes BCN unique is its innovative use of replication and replication monitoring across its highly distributed data architecture. BCN needed a constant, up-to-date snapshot of its transaction data copied to Detroit to populate both the ASE operational data store and a data warehouse used for corporate analytics. BCN uses Sybase Replication to replicate an operational copy of its production database. Sybase Replication copies volumes of data from the hosted location in Denver to the Detroit headquarters. However, unlike many HMOs, BCN needs to replicate data hosted and maintained by a third party, in this case, TriZetto.
Raj Vunnam, chief architect at BCN, gives an overview of the architecture, “We are a hosted customer of TriZetto’s FACETS software. We use Sybase Replication to replicate our OLTP data from TriZetto’s Denver site to our ASE operational data store in Detroit. Every evening we run operational reports against our ASE database, as well as run ETL scripts that take the data from this operational data store and populate our analytical data warehouse.”
The data being replicated is critical to the organization; decisions are made and trends are identified based on the information. The final piece of the architecture is monitoring and validating the integrity of this unique, hosted replication process. The monitoring is provided by Bradmark Surveillance.
Replicating Important Data Across Distances
While TriZetto’s FACETS application is configured to BCN’s needs and does an excellent job of supplying day-to-day HMO functionality, there are additional, forward-looking tasks that need to be performed at the corporate office. These tasks require the data that resides in the hosted TriZetto ASE database be replicated to BCN’s own database servers.
Raj Vunnam explains, “The data is replicated for two reasons: one is to build our analytical data warehouse because we need to understand what really has changed, and the second reason is we have operational reports that need to be generated. For the analytical data warehouse we have custom triggers that capture any changes and build a delta database. Every night we run ETLs against the changed data that was captured as part of replication and load it into our data warehouse, based on certain business rules. Some business rules discard unimportant data while others flag data –like a change to a group’s medical plan– as being really critical due to financial implications and load it into the data warehouse. Sybase Replication has made it possible to eliminate an existing data warehouse in Denver and move all the analytical aspects of our solutions into an enterprise data warehouse in Detroit. The operational reports are also important to our business. We run key reports against the replicated data. For example, we have a claims payment batch job that runs every weekend that pays claims to providers and tells us the amount we pay to individual providers.”
Bradmark Surveillance – Peace of Mind for Critical Processes
Sybase Replication Server is acknowledged in the industry as an extremely robust solution. The operational data being replicated from FACETS to Detroit is considered so central to BCN’s daily functioning, the IT team wanted an additional, proactive monitoring layer to insure the solution was truly robust.
Chad Shaffer, technical engineer at BCN, explains BCN’s choice and the early stages of using the Bradmark tool, “We started using Sybase replication in pseudo-production mode. As we moved toward real production we wanted to see any replication faults, along with when and where they happen, in real-time. We looked at different options for tools that would provide real-time monitoring and alerting in addition to historical reporting. We asked Sybase and they recommended Bradmark Surveillance. Once we acquired Bradmark’s zipped product files, I read through the documentation. The installation documentation is straightforward and very well put together. I was able to follow the steps, install it on an AIX server, and additionally set up the client.”
Bradmark Surveillance also logged additional performance information that was useful in tuning the replication servers. The critical business function of maintaining an operational data store is now managed by a fully automated and carefully monitored replication process. Shaffer explains, “From my standpoint, replication feeds the whole solution. So if anything fails with replication then all of your downstream applications that update the data warehouse or analytical data store on a daily basis will not work. So it's critical that we have Bradmark Surveillance monitoring and alerting capabilities.” Shaffer and his peers need only to respond to the rare alert; otherwise, the data is tirelessly replicated without human intervention.
Shaffer talks about the utility of the multi-level alerts that are available in Bradmark’s Surveillance offering, “Including test systems, we replicate four databases using Sybase Replication’s multi-site availability (MSA) feature. We have a number of alerts configured for each replication server and the alerts are set up so they will page my phone whenever there is an issue. It will also send emails out to a larger distribution.
That’s extremely useful, especially when I am away or off hours so I don’t always have to be logged on and monitoring the replication log. During the day, the Bradmark Surveillance client is always running on my laptop. It gives me a useful, real-time overview of our replication environment. The overview includes the amount of data in each of our replication threads and it provides a very nice way of using the Bradmark GUI rather than having to type a command prompt and then understand the different statistics. The Surveillance tool records extensive log files. Among other things, I use the logs when we have exceptions to see the actual SQL text.
Otherwise, I would have to go through the exception and copy out the SQL so I could build that transaction manually. This feature is a tremendous help. I can also generate reports on our processing performance and any replication latency.”
Optimal Replication with Help from Sybase Professional Services
Sybase Replication is not only robust, but it absorbs high volume requests with minimal replication latency. To help optimally configure the system, BCN brought in a Sybase Professional Services Replication consultant.
Raj Vunnam was very pleased with the consultant’s input, “The Sybase consultant identified the replication tuning parameters that needed to be modified. Some of the configuration parameters are based on the type of data on the host server and the consultant needed to really understand how the transactions come across from Denver. After applying the recommended configuration parameters we saw a significant improvement in the replication throughput.”
Vunnam is also pleased with the overall performance of the Denver-to-Detroit replication process, “With 5-6 gigabytes we see only about a 10 minute latency. Sybase Replication and the solution are stable and scalable – once the system was tuned we have not run into major performance issues. The largest amount of data that we propagate from Denver to Detroit in a small time period would be 18 gigabytes. After 7.5 hours we basically have all the data in Detroit with a latency of about 1.5 hours.”
The main obstacle in building the architecture was the shared responsibility of replication at the TriZetto site. The issue was not so much one of technical difficulty, as much as forging a roles and responsibilities document. The TriZetto DBAs are in charge of the hosted servers, yet BCN needed the replicated information on a real-time basis. Only after issues like impacts to the hosted software performance were understood and the agreement was hammered out were they able to move into production.
Sybase Replication– A Key Component in a Blended Architecture
As the popularity of third-party hosting solutions increase, the need to maintain a data-rich snapshot of the daily transactions for corporate reporting and analysis becomes increasingly apparent.
Raj Vunnam and Chad Shaffer agree: The challenge of the project was integrating disparate products seamlessly. With ASE at its core, TriZetto provides FACETS as a hosted service running everything from member enrollment through claim adjudication. The corporate office needs the daily data that is created from TriZetto’s service. The unifying technology tying the products together is Sybase Replication. With excellent monitoring of the replication process by Bradmark Surveillance, the team always knows the status of the critical flow of information. For BCN, success is not just a solution that works, it is a solution with measurable results.