Case study on data modelling: Creating the FlexSIS data model

1
Case study on data modelling Creating the
FlexSIS data model

• Enterprise Architecture Symposium 2007

2
Objectives of presentation

• Share data modelling experience
• Assist attendees to decide how data modelling
  might be of benefit to them
• Describe an effective method for data modelling
• Present an interpretation of the Data
  Architecture function

3
About FlexSIS

• Suite of applications supporting student
  administration
• Core desktop application
• Reporting portal
• Student self service portal
• Batch processes
• Interfaces
• Built and maintained in-house
• Development started in 1996

4
Rationale for creating the data model

• Document the as is system
• To support
• Strategic planning
• Roadmap development for student administration
• Development
• Ongoing development and maintenance activity
• Ad hoc reporting
• Empower specialist staff

5
Rationale Expected benefits

• Common understanding
• Reduce key-person dependency
• Produce better systems
• Simpler designs
• More aligned to the University needs
• Reduce development timelines, costs and risk

6
Process overview

• Select tools
• Analyse/profile the data
• Create data model
• Reverse engineer
• Group tables logically
• Add table logical names
• Add table relationships
• Create E-R diagrams
• Create data dictionary
• Determine scope
• Add table and column descriptions
• Publish (HTML)

7
The FlexSIS data model (as is)
8
FlexSIS data dictionary
UOS_departments (UOS Session Offering - Teaching
Department)
Table Description
Links a unit of study session offering to a
department which administers/teaches the unit of
study. Most often each UoS is taught by only one
department, but in the case of shared teaching
loads there will be more than one department
linked to the UoS session offering.Example
Unit of study MARS2007 (Marine Science Field
School) offered in academic year 2007, session 6
(S1 Intensive) is taught by the following
departments- K03 (Anatomy and Histology) with a
teaching load of 20- L04 (Biological Sciences)
with a teaching load of 20- L17 (School of
Geosciences) with a teaching load of 60, and
this is the primary teaching department.Note
that the total teaching load equals 100.
Columns
9
Some statistics
Data dictionary
10
Tools Data modelling

• Tool used PowerDesigner
• Key strengths
• Reverse engineering
• Model synchronisation
• Diagramming
• Data dictionary support
• Generating reports
• Metadata extensions
• VB Script to build custom functionality
• Intuitive user interface

11
Tools Data profiling

• Custom built (Sybase stored procedure)
• Runs standard queries and stores information
  about data found

12
Tools Table dependency analysis

• Custom built (PowerDesigner VBScript)
• Determines depth of tables in referential
  integrity hierarchy
• Helps answer where do I start

13
Data dictionary - Approach

• Determine scope
• Do diagram at a time
• Diagrams define high level chunks of work
• Document tables and columns of diagram
• Use template to guide content
• Follow standard style
• Review and sign-off descriptions
• Publish model regularly
• Track progress regularly
• Accept feedback after sign-off
• Engage business analysts and developers

14
Data dictionary Tracking progress
15
Some key points

• Model is source of truth for
• data structure designs
• data element descriptions
• Data dictionary published as report extracted
  from the data model
• Model driven approach to
• the data dictionary problem
• data architecture
• Paves a path to enterprise modelling

16
Enterprise modelling
17
Outcomes

• Model being used by business analysts, developers
  and some specialist staff for
• Development, data migration, data cleansing,
  reporting, training provision
• Model maintained in a single repository
• easily accessible knowledge base
• supporting greater collaboration
• Standard data modelling approach defined
• Model seen as valuable resource for migration to
  future student admin system
• Supporting tactical and strategic needs

18
What next ?

• Conceptual data model
• Application interfaces documentation
• Support SOA initiative
• Wider rollout of standard data modelling practice
• Metadata initiatives
• Other