Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Peter Havskov Christensen, M.Sc PowerPoint Presentation
Download Presentation
Peter Havskov Christensen, M.Sc

Peter Havskov Christensen, M.Sc

106 Views Download Presentation
Download Presentation

Peter Havskov Christensen, M.Sc

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Peter Havskov Christensen, M.Sc MIS for CarRes User Group Meeting

  2. From a selection of Buzzwords to practical use • Buzzwords: • MIS (Management Information System) • Data Warehouse • Data Mining • OLAP (On Line Analytical Processing) • Practical use • Knowledge is valuable • A lot of knowledge can be derived from existing data inside the CarRes • We need tools/techniques to extract important information • At Bornholmstrafikken use the word MIS for the chosen techniques MIS for CarRes User Group Meeting

  3. Bornholmstrafikken MIS • Data Sources • CarRes MIS (70%) • Information system and electronic logging system for arrivals (10%) • ERP system (10%) • Other sources (10%) • CarRes MIS • Two part solution (ATS part + own part) • Reduced amount of data • Only newest booking version in MIS • Only selected tables included and simplified • Redundancy created on purpose MIS for CarRes User Group Meeting

  4. Why not extract data from production database • Production database advantages: • Avoid almost all redundancy for fast and reliable updates • Optimize speed for production usage • Optimize data security (change logs and transactions) • MIS database advantages: • Contain redundant information for easy querying • Simpler database for easier user queries • Optimize speed for most used queries • Database is independent from production database: • Users query errors does not influence CarRes performance • Heavy queries does not influence CarRes performance • MIS database can be at different location than production MIS for CarRes User Group Meeting

  5. How does local MIS work • Updated every night: • Automated download ZIP-file from CarRes FTP-server • Automated update of tables in local MIS with incremental data contained in ZIP-file • Some (heavy) and often used calculations performed after update. • Creating totals that require some local MIS master tables for special purpose classification of data. • Possible to create even more redundancy for even faster querying MIS for CarRes User Group Meeting

  6. User interface(s) for local MIS • Web interface • Follow up on predefined Key Performance Indicators (KPI) • Color codes • Update of Local MIS master tables for special purpose classifications • ODBC / MS Query • Excel (primary) • Access • Crystal Reports MIS for CarRes User Group Meeting

  7. Update of local master tables LOCAL GROUPING • Linking a group of fields in CarRes to a local classification • Special purpose reporting • Port costs • Taxes MIS for CarRes User Group Meeting

  8. Often used queries on Intranet (web interface) • Number of checked in passengers pr. departure (special calculation that adds 2 CarRes departures into one departure because of the way we handle a connection bus) • Web based data for non CarRes users in the organization MIS for CarRes User Group Meeting

  9. Ad hoc queries in MS Query / Excel • Redundancy makes selecting relevant bookings (negative balance) easy and fast • Purpose: check if customers still pay twice or more because they dont understand when their credit card payment on www has been completed succesfully. MIS for CarRes User Group Meeting

  10. Cost / Benefit • Costs • ATS Costs (??) • MIS was part of our initial contract (others might benefit from development) • Local costs (approx 15.000 EUR + internal hours) • One physical server including MS SQL Server software and backup. • Approx 250 working hours • 200 internal hours • 50 external hours • Benefits • Knowledge • Saved manual working hours MIS for CarRes User Group Meeting

  11. Local MIS maintenance / Is MIS updated ? Screen layout depends on local setup • If all jobs have status ”FINISH” everything should be OK. • Automated notification to IT department if not all jobs have status “FINISH” at 08:00 (when people who depend on data start working) MIS for CarRes User Group Meeting

  12. Further plans for MIS development • ATS part • Minor adjustments to tables DEP and CUSMST to create a little more redundancy by adding some calculated fields (not yet specified by Bornholmstrafikken) • Local part • Create web based follow up for all KPIs (Key Performance Indicators) • Create web based graphics for selected KPIs • Internal user training: How to query the MIS database? • Update local MIS from MS SQL Server 2000 to MS SQL Server 2005 (new and improved analytical features) MIS for CarRes User Group Meeting