Dias nummer 1

1
Peter Havskov Christensen, M.Sc
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

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

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

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

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

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

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

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.

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

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 0800 (when
  people who depend on data start working)

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)