Transcript for local MIS

Peter Havskov Christensen, M.Sc
MIS for CarRes User Group Meeting
9 January 2006
1
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
9 January 2006
2
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
9 January 2006
3
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
9 January 2006
4
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
9 January 2006
5
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
9 January 2006
6
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
9 January 2006
7
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 January 2006
8
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
9 January 2006
9
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
9 January 2006
10
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
9 January 2006
11
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
9 January 2006
12