9 January 2006

MIS for CarRes User Group Meeting

1

Peter Havskov Christensen, M.Sc

9 January 2006

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

9 January 2006

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

9 January 2006

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

9 January 2006

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

9 January 2006

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

9 January 2006

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

9 January 2006

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

9 January 2006

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.

9 January 2006

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

9 January 2006

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)

9 January 2006

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)