trns*port data model unification joe pugh virginia department of transportation peggy leight info...

Trns*port Data Model Unification

Joe PughVirginia Department of Transportation

Peggy LeightInfo Tech Inc

11/06/05

ScopeIdentify potential data model changes that will help facilitate a unified data model approach

VDOT Strategic Objectives– Review current processes and identify redundant

data – Identify opportunities for expanding use of Trns*port

in the agency– Facilitate meta-data driven and service oriented

architecture

Data Model Unification

Data Management Concepts

Key Concepts• Conceptual Data Model

• Metadata

• Web Services

Current Data Structures

Database Database Database

Database

Duplicate Data

Data Flow Diagram

Future Data Flow

N G T U nifie d D a ta ba se( es tim atio n , p r o p o s a l p r ep ar a tio n , b id

le t t in g , aw ar d , c o n s tr u c tio n , p ay m en ts ,m ater ia ls , d es ic io n an a ly s is )

E s tim ato r

Exp ed ite F ie ldM a na ge r

Site P ad

“Add Project” Function Today

C E SE x p o r t J o b

G UI f u n c tio n

P E SI m p o r t f r o m

E s tim ato rBatc h J o b

P E SAd d P r o jec t

G UI f u n c tio n

P E SI m p o r t f r o m

D es ig nBatc h J o b

P E S D atab as e

“Add Project” Web Service

N G T D atab as e

E s tim ato r"Up lo ad "

" A dd Pro je ct"W e b S e rv ice

C o s t E s tim atio nE x p o r t

F u n c tio n

D es ig nS y s tem

P r o p o s a lE s tim ate W eb

F u n c tio n

NGT Conceptual Data Model

c o n tr ac t

d o cu m en tp ricin g p ack ag e

b id ex ten s io n s

f u n d lis t

p r o jec t

i t emco m p o n en t

item g r o u p

co l lab o rato rlo cat io n s

f u n d s

b id p r ic es

q u a lif ie r s

c o llab o r a to r s

item s

i t em m as terco m p o n en ts

item m as te r

ac tiv ity

p eo p le

c o d e tab les

s u p p o r t in f oD O T lo cat io nm as ter

ite m c o m p o n e n tg r o u p

co n t ract in g b idd ata

j u r isd ic tio n a l a n dc o o r d in a te lo c a tio n

fu n d p ack ag e

c o st in g gr o up

Bid L ett in g

p r im e p r o je c t

A tta c h me n t

Conceptual Data Model Implementation To Date

c o n tr ac t

d o cu m en tp ricin g p ack ag e

b id ex ten s io n s

f u n d lis t

p r o jec t

i t emco m p o n en t

item g r o u p

co l lab o rato rlo cat io n s

f u n d s

b id p r ic es

q u alif ie r s

c o llab o r a to r s

item s

i t em m as terco m p o n en ts

item m as ter

ac tiv ity

p eo p le

c o d e tab les

s u p p o r t in f oD O T lo cat io n

m as ter

ite m c o m p o n e n tg r o u p

co n t ract in g b idd ata

j u r isd ic tio n a l a n dc o o r d in a te lo c a tio n

fu n d p ack ag e

c o st in g gr o up

Bid L ettin g

p r im e p r o je c t

A tta c h me n t

Evaluating Entities

100%75%50%25%

100%

66%

33%

100%

50%

25% Entities • Fund package • Items• Projects

Most of the work is up front

Conceptual Web Services Architecture

NGT Services-Oriented Architecture

D atab as e

W eb S ervic es R ep o rtS ervic es

W eb B ro w s erU s er Interfac e(W eb S erver)

U s er Interfac e(H and held )

ExternalS ys tem s

M etad ata

VDOT Trns•port Data Integration

Trns•port

Financial Management

System

Project Scheduling

Data Warehouse

Enterprise Metadata Repository

Roadway Network System

Environmental System

Program Planning

Asset Management

Project Dashboard

VDOT Data Issues/ NGT Potential Solutions

Issues:

1. PES Generate Bid Based Prices process to allow different bid history files for estimating.

2. Ability to enter in a quantity on the construction side and have the system generate unit price. Set boundaries for pricing.

3. Ability to generate multiple bid histories for each county, region, cost group, district. Etc.

Potential Solution:• A single estimation engine which can be run at any

point in the workflow from estimation through construction. Estimation would allow for selection of price based on multiple criteria.

VDOT Issues and Potential Solutions cont.

Issue:• The ability to handle generic fields

differently as appropriate to various tables.

Potential Solution:• Allow each agency to add however

many generic fields of any type to any base table.

VDOT Issues and Potential Solutions cont.

Issues:• The ability to generate a totally electronic

proposal.• Improve the interface between PES and

Expedite• Ability to determine vendors by district based

on zip code, address, longitude/ latitude, etc.

Potential Solutions:• Yet to be determined (tell us what you need)

Data Analysis Observations

To improve the structure of the unified data model:

Entity Unification (alignment based on related business rules)

– Functional Data (Project, Item, Attachment, etc.)– Reference Data (Item Master, Fund Master,

People/Vendor, etc.)

Streamlining (Consolidate data reducing number of tables and/or columns)

– Long Text Descriptions– Code Tables– Location Data type attributes– Attribute/Entity Relocation (e.g., Letting Proposal

with Proposal)

Consistency in Attributes– Length and type– Code Tables

Versioning and Status– Snapshots (e.g., improved addendum processing,

change history)– Active/Inactive Status (e.g., Code Tables)– Date/Time/UserID Stamp

General Data Clean Up and Reduction– Crosswalk Tables– Temporary Tables for client/server environment– Duplicate Data (e.g., SiteManager data in

BAMS/DSS)

General Organization, Flexibility and Efficiency– Generic Fields and Attributes– Storage of Calculated Attributes

Data Analysis Observations

Recommendations

Expose NGT working data models to end-users and set-up process to accept feedback throughout development

Expose current metadata to end users and include the ability for this information to be accessed outside of NGT.

Next Steps

1. Expand analysis to other modules

2. Involve other states in the process

3. Propose recommendations for consideration into NGT backlog

https://www.cloverleaf.net/support/forum

Remember

“Virginia is for Lovers”