1 / 10

Migrating 50,000 Miles of Pipeline Data to PODS 5.0

Overview. Strategy. Technical Issues. Conclusions. Migrating 50,000 Miles of Pipeline Data to PODS 5.0. Overview. PODS Implementation. User Base (as of 7/19) 15 Departments 551 Individual Users 42,800 Report Executions 1,020 Data Change/Service Requests Functional Support

ince
Download Presentation

Migrating 50,000 Miles of Pipeline Data to PODS 5.0

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Overview Strategy Technical Issues Conclusions Migrating 50,000 Miles of Pipeline Data to PODS 5.0

  2. Overview PODS Implementation • User Base (as of 7/19) • 15 Departments • 551 Individual Users • 42,800 Report Executions • 1,020 Data Change/Service Requests • Functional Support • Inline Inspection and Repair • HCA Segment Identification • Annual Mileage Reporting • Property (Ad Valorem) Tax • Land and Right of Way • OneCall • Public Awareness • DOT Class Location • Alignment Sheet Generation • Pipeline Risk Assessment • 3rd Party Data Entry (VPN) • Data Content • 50,0000 miles of pipelines • 7,000 Natural Gas Transmission • 13,750 Natural Gas Gathering • 26,300 Liquids Transmission • 2,000 Liquids Gathering • 50 million total records • 9.2m ILI Tables • 8.9m Location • 7.5m Event_Range • 6.7m Station_Point • 6.5m Public Awareness • 2.1m Elevation • 2.2m Coordinate • 362k Pipeline Components • Infrastructure • PODS v. 5.0 • SQLServer2008 • 3 Production Database Servers • Production, Replication, Application

  3. Overview Migration Components • Data Content • Translation via SSIS Package • Repeatable • Database Programs • 415 Stored Procedures • 6 Scheduled Jobs • 20 Database Functions • 70 Triggers • Software • Data Editing • Alignment Sheet Generation • Spatial Overlays • Centerline Generation • Cartographic Production • Asset Data Navigator • Reporting • 200 Reports • 6107 Alignment Sheets • 8910 Atlas Style Maps • Re-Engineering • Business Rule Validation • Linear Intersect/Union • Database “API” • Embedded SQLSpatial • Reporting Interface

  4. Strategy Primary Objectives • Standardize • Improve Performance • Expand Staff Knowledge Base • Enhance Manageability • Increase Modularity • Improve Transaction History

  5. Strategy Strategy • Business Driven • Internal Planning and Execution • Recognize the Scale of the Installation • Frequent Staff Meetings (2 hour weekly) • Planned Testing • Managed Vendor Support • Repeatable Data Translation • Stay on Schedule (6 month window)

  6. Technical Issues I GUID’s! • Implementation • SQLServer uniqueidentifier data type • newsequentialid() system function for default value • Advantages • Seamless primary key acquisition • NO MORE: Msg 2627, Level 14, State 1, Line 1 • Violation of PRIMARY KEY constraint 'PK_EVENT_RANGE'. • Cannot insert duplicate key in object 'dbo.EVENT_RANGE'. • Non-Meaningful – Prevents Developing End-User Dependencies • Negligible Performance Degradation • Bulletproof joins • Identifiers exist only once as a primary key in any database. • Uncovered some invalid joins in previous technology. • Database Integration • Allows for tight integration of similar entities in dissimilar databases • Enhances interoperability between PODS databases

  7. Technical Issues Data Model Changes • Hierarchic Code Lookups • PODS: Non-Extensible combination of type/subtype • EPP:Self-Referencing Lookups w/Unlimited Hierarchy • Non-Standard Primary Key Data Types • PODS: Numeric, varchars, etc. in code tables • EPP: GUID data type (Oracle: RAW, SQLServer: uniqueidentifier) • County/State Boundaries • PODS: Non-standard mixture of FIPS and Postal codes • EPP: Generic (i.e. internationalized) nested boundary structure • Denormalization • PODS: Fully Normalized in the Core Model • EPP: Denormalized to include LINE_GUID, BEGIN_MEASURE and END_MEASURE in the EVENT_RANGE table. • Offline Event Design • PODS: Fragmented into a submodel. (Offline_Event XREF, Offline_Event) • EPP: Fully integrated into Event_Range w/ addition of LOCATION_GUID

  8. Technical Issues Data Model Changes (cont.) • Spatial Database Components • PODS: Bolt-On Extension (via Working Group) • EPP:Embedded in Overall Design • Column Hyper-Normalization • PODS: Multiple code values (grade, smys, specification) • EPP: Single code to lookup multiple values • History • PODS: Offline/Online Discussion • EPP: Transaction History/Auditing per Specific Business Requirement • Table Hyper-Normalization • PODS: Taps, Tees, Branch Connects in separate tables. • EPP: Combined to PipeConnect and typed. • MOP • PODS: Combined in MAOP_Rating • EPP: Separated Between Natural Gas v. Liquids (MAOP, MOP Respectively)

  9. Conclusions Strengths • 7 months to Completion • Significant Increase in Staff Body of Knowledge • Noticeable Improvement in Performance • Highly Standardized Reporting • More Flexible Search Engine • Assessment ID/Seg. Name • Begin Measure/EndMeasure • Classification (NG/Liquids) • Cost Center Code/Name • County/State Name • Facility Name • Legacy Line Name/Number • PODS_ID, Line Name/Number • Operating Boundary Hierarchy • Operating Status • Product Type/Products • System Name

  10. Conclusions Weaknesses • 7 months to Completion • Infrastructure Complexity • Centerline Editing • Deviation from Industry Standard • Line Events (Operating Status, Product Range, etc) • Testing, Testing, Testing

More Related