Transcript pps - Project Users Group

Trns*port
Data Model Unification
Joe Pugh
Virginia Department of Transportation
Peggy Leight
Info Tech Inc
11/06/05
Data Model Unification
Scope
Identify 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 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
Estimator
SitePad
NGT Unified Database
(estimation, proposal preparation, bid
letting, award, construction, payments,
materials, desicion analysis)
Expedite
FieldManager
“Add Project” Function Today
CES
Export Job
GUI function
PES
Add Project
GUI function
PES Database
PES
Import from
Estimator
Batch Job
PES
Import from
Design
Batch Job
“Add Project” Web Service
Cost Estimation
Export
Function
Proposal
Estimate Web
Function
"Add Project"
Web Service
Estimator
"Upload"
Design
System
NGT Database
NGT Conceptual Data Model
Bid Letting
document
pricing package
contract
prime project
project
qualifiers
contracting bid
data
costing group
bid extensions
fund package
item group
bid prices
collaborators
items
funds
activity
collaborator
locations
item com ponent
group
people
item master
fundlist
DOT location
master
j urisdictional and
coordinate location
support info
item
component
item master
components
Attachment
code tables
Conceptual Data Model
Implementation To Date
Bid Letting
document
pricing package
contract
prime project
project
qualifiers
contracting bid
data
costing group
bid extensions
fund package
item group
bid prices
collaborators
items
activity
funds
collaborator
locations
item com ponent
group
people
item master
fundlist
support info
DOT location
master
j urisdictional and
coordinate location
item
component
item master
components
code tables
Attachment
Evaluating Entities
25% Entities
• Fund package
• Items
• Projects
100%
100%
66%
100%
75%
50%
25%
33%
Most of the work is up front
50%
Conceptual Web Services
Architecture
NGT Services-Oriented Architecture
Database
Metadata
External
Systems
Web Services
Report
Services
User Interface
(Handheld)
User Interface
(Web Server)
Web Browser
VDOT Trns•port Data
Integration
Project
Scheduling
Financial
Management
System
Roadway
Network
System
Enterprise
Metadata
Repository
Environmental
System
Trns•port
Data
Warehouse
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)
Data Analysis Observations
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
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”