ERC INTEGRATED DATA SYSTEM ERC IDS

1
ERC INTEGRATED DATA SYSTEM (ERC IDS)

• IT Database Development Group
• Fernie Felix

2
ERC Needs

• Centralized source of company contact
  information
• Share company contact information between ERC
  databases
• Resolve data duplication and conflicts between
  databases automatically or manually
• Query centralized information on any number of
  variables

3
Databases Involved

• ERC Marketing
• MIPS Projects
• TAP IMS
• TES MEIS

4
Core Data Structure
5
Advantages of SQL Server

• Handle multiple large databases
• Robust database engine
• Security uses security built in Windows 2000
  Server
• Transactions recording record what/who/when
  changes
• Triggers launch events based on data changes
• Replication share copies of data with other
  databases

6
Replication in SQL Server

• Components
• Types

7
Replication Components

• Publisher database source
• Subscription a copy of the entire database or a
  subset of the data
• Subscriber other databases that request
  receive subscriptions
• Distributor agent that creates the
  subscriptions and sends it to subscribers

8
Replication Types

• Snapshot read only subscriptions
• Transactional read only subscriptions like
  Snapshot except Subscriber receives only latest
  subscription updates from the Distributor
• Merge updatable subscriptions where data
  conflicts are resolved with Merge Agent

9
Merge Replication Details

• Merge Agent component of the Distributor that
  receives all updates made on subscriptions
• Conflict Resolution scripts used by Merge Agent
  to resolve data conflicts
• Types of data conflict
• Same record any field
• Same record same field

10
Conflict Resolution Options

• Resolve automatically with Merge Agent and
  scripts
• Notify database administrators of data conflict
• ERC IDS administrator resolves conflict manually
• Phone calls
• Duplicate Module

11
Merge Replication In Action

• MIPS student edits record in MIPS Project
  database
• All updated records in Projects subscription is
  sent to ERC Core data
• Merge agent sitting at ERC Core data determines
  if edited record has caused data conflict
• Merge agent resolves conflict with a coded script
  and updates ERC Core data
• ERC Core sends updated Project data subscription
  back to Project database
• ERC Core updates other subscriptions if record is
  shared by other databases i.e.. MIPS Marketing,
  TAP or TES

12
ERC IDS Scheme
13
Building the ERC IDS

• Convert and test database system to use SQL
  Server 2000 as a backend
• Copy company/contact info into ERC Core and
  establish Merge Replication
• Test converted system and ERC Core Data Merge
  Agent
• Switch users to converted system

14
Converting Databases to use SQL Server 2000 as a
Backend

• Examine current database and identify
  enhancements needed
• Import tables and possibly redesign them to
  establish table relationships
• Convert all queries and form code to use SQL
  Server as a backend
• Import forms and reports
• Test and run converted system
• Develop Data Transfer packages

15
Status

• ERC Marketing converted
• TES MEIS
• 80 of modules converted
• 75 of reports converted
• Testing Merge Replication using converted
  Marketing as test bed

16
Future Projects

• Laptop PDA Subscriptions enable laptops and
  PDAs as subscribers
• Natural Language Query System write queries
  such as Who has had projects with MIPS TES?
• XML Data Services display on the web export
  ERC Core Data using XML format
• Data Mining / Analysis Services create n-sized
  data cubes for analysis in MS Excel

17
Q A
18
(No Transcript)