forward data cache integration pattern ed allison regence september 14, 2010

Forward Data Cache Integration Pattern

Ed AllisonRegence

September 14, 2010

What is a Forward Data Cache?

“The Forward Cache integration pattern allows data to be proactively pushed from the backend data sources to a data cache service that is located closer to the presentation tier. Caching data closer to the presentation services improves performance and can allow continuous access when backend sources are offline.”

– Enterprise Service BusBy David A. Chappell

Copyright © 2004

Why build a Forward Data Cache?

AVAILABILITY

PERFORMANCE

How can we populate an FDC?

DATA FORWARDINGEnterprise Service Bus Oriented

• Publish and subscribe• Itinerary-based routing

ETL APPROACH

What did we consider technically?

TIMELINESSTolerable latency and scalability

BREADTHData subject areas and detail

DEPTHAmount of history

USERead-only query performance over load

performance

5

What are we using our FDC for?

6

• The FDC will be a source of data for enterprise web services that will facilitate HIPAA 5010 transaction processing. For example:– Member Search– Provider Search– 271 Response

How does it fit in our big picture?

7

EDITradingPartners

HIPAA ServicesCommon andInfrastructureServices

ForwardContentValidation

EDI Channel

Read/WriteBusinessServices

Read OnlyBusinessServices

Forward Data Cache

Web Channel

Future Channels(IVR, Text Msg)

Members,Providers,Groups

BackEndSystems

?

5010?

Legacy 4010Pipeline

Y

N

Which technologies did we use?

• Sybase Replication Server Messaging Edition (RSME)

• Sun Java Composite Application Platform– Based on OpenESB

• Oracle Real Application Cluster (RAC) RDBMS

8

Why did we make these choices?

• Sybase RSME– Retention of existing Sybase replication use– Supports non-Sybase sources

• Sun JCAPS– Our enterprise ESB

• Oracle RAC– Our existing highly available RDBMS

9

What are we loading into our FDC?

10

• Facets Claims System– Subscriber / Member– Benefits– Claims– 270/271 Configuration

• Provider Information Management System– Provider

What does our FDC look like?

11

What are our RSME components?

12

How do we populate our FDC safely?

1. Source table changes get created as individual messages

2. Use staging tables to hold full source tables (“reference” tables)– We need to have the full table available locally for

look-ups because the target has been mildly denormalized and update/delete anomalies need to be addressed

3. Carry source primary keys in the target tables

13

How do we populate our FDC safely?

4. Load the staging and target tables incrementally in a trickle feed, near real-time, manner

5. Load targets in parallel, using autonomous transactions

6. Perform simple data standardizations

7. Rely on source system data quality

14

What are the rules for population?

1. Source table changes are applied to the target in the order they were made within the context of a single source table and the source primary key, which allows for parallelism in the load

2. Source “reference” table changes apply to 1+N tables: 1 staging table and N denormalized target tables.

3. Source “reference” table inserts, updates and deletes are applied to target tables as mass updates (modifying 0 to many rows in target tables)– Source “reference” table deletes cause updates to null values

in the target, but only for non-[source]key attributes

4. Denormalized target tables are required to have lookups against staging tables that are denormalized into those targets

15

What are the rules for population?

5. Deletes in source tables will become “soft deletes” in FDC tables

6. Each denormalized target table will have a fix up process to propagate the reference table changes to the target table

7. The fix up processes are self-scheduling based on their previous runtimes plus a minimum wait time

8. An “Out of band” synchronization checker needs to be used occasionally to verify the FDC is reasonably in sync with its sources

16

How do we populate our FDC safely?

17

How to make queries perform?

• An average 500 msec query response time should be achievable with a tuned RDBMS approach

• Oracle RAC provides near-linear scalability• Tuning options include:

– Parallelization

– Partitioning

– Specialized indexes

– Virtual columns

– Workload management

– SQL Tuning (web services will only generate a limited variations of queries)

– Materialized views

18

What alternatives were considered?

• Object database– Considered InterSystems’ Cache

• In-Memory Data Fabric– Oracle Coherence

– Gemstone Gemfire

• These were not pursued because– The time table for the development of the FDC was not conducive to the

introduction of a new data store technology for the FDC

– A data grid (data fabric) would require extensive engineering to provide a robust, production ready system.

– A data grid would require significant coding to leverage.

– Cost due to additional infrastructure required

19

Are there any questions?

Thank you for attending

Ed AllisonSenior Solutions Engineer

Regenceed.allison@regence.com

20