A Roadmap for Data Warehouse, Reporting and Analytics at Georgetown

1
A Roadmap forData Warehouse, Reporting and
Analytics at Georgetown

• A Response to the Report of the Data Warehouse
  Working Group
• Ron Allan, Dave Lambert, Matt McNally, Piet
  Niederhausen

2
Overview

• The growing importance of data in the life of the
  institution
• Introduction to key concepts
• ..developing a common vocabulary
• Some important issues facing forward
• What can we learn from the experience of others
• Addressing some short term tools issues
• A proposed roadmap forward
• A discussion about resources

3
Building the information-driven university

• Increasingly our executive leaders are demanding
  information and analysis to support strategic
  decisions.
• To assure efficiency and competitiveness of our
  day-to-day operations, managers and directors
  require a constant flow of reliable information.
• Hardly a month passes that a new reporting
  requirement isnt imposed from an external
  source.
• ..and even more are on the way.
• Georgetown, like the rest of higher education,
  is being hit by a perfect storm of data issues.
• But

4
There are a multitude of complicating issues

• Complexity of cataloging all data fields
• Complexity of authorization the Who sees what?
• Complexity of access the How do I see what?
• No single management or reporting tool meets the
  range of information access needs
• Regulatory issues FERPA, HIPAA G-L-B
• Data security
• Data accuracy issues Sarbanes-Oxley
• Confusing concepts and vocabulary

5
Common concepts and vocabulary

• From the initiation of our first efforts in
  building data warehouses, we have been hampered
  by a confusion in concepts and vocabulary.
• We are not alone in that regard
• There are still major disagreements among thought
  leaders (including academics)
• Vendors have unleashed an array of products with
  conflicting names and capabilities.
• One vendors warehouse is anothers ODS.
• There is every evidence that confusion will get
  muddier rather than clearer with the accelerating
  consolidation in the market.

6
Reporting and analytics
7
Business systems

• Business systems contain transactional data
  updated by staff and end-users via self-service.
• Access control is fine-grained (down to records
  and fields).
• Detailed knowledge of a system is required to
  manage its data.
• How data is collected and stored affects the
  ability to do reporting later.

8
Operational Data Stores

• Each data store contains a copy at a
  point-in-time of transactional data from a
  business domain, updated periodically.
• Data stores are primarily used for production,
  line-of-business reporting.
• Access control is modeled on business systems,
  managed by database not reporting tools.
• Domain knowledge and reporting skills are
  required to create reports.

9
Integrated data environment
Extraction, transformation, and load (ETL)
Consistent access controls
Reporting tools
Data stores
Integrated data environment

• Contains data extracted from many business
  domains, stored over time.
• Some data is transformed to make key data
  consistent across domains.
• Multiple reporting tools should be supported.
• The same access controls should be enforced
  across all reporting tools.
• Access control is inherently less fine-grained
  than underlying business systems to enable
  broader vision.
• Reporting is constrained by how the data was
  originally collected and stored.

10
Analytics
Institutional, program, and project goals
Performance metrics
Dashboards
SVP
Provost
VP
Dean
Research questions
Scenarios
Projections
Analytics

• Goals must be defined to establish measurable
  performance metrics and research questions.
• An analytics environment provides dashboards that
  show current status compared with a defined goal.
• An analytics environment also provides the
  ability to run hypothetical scenarios and
  projections for institutional research.

11
Differentiators
12
Overview
Student
Human Resources
Financials
Benefits
Faculty
Advance- ment
Endowment Mgt
Research Mgt
Space/ Facilities
Service Mgt
Student
Human Resources
Financials
Benefits
Faculty
Data administration
Advance- ment
Endow- ment Mgt
Research Mgt
Space/ Facilities
Service Mgt
Data access policy
Reporting tools
Data governance
Institutional, program, and project goals
Performance metrics
Dashboards
Research questions
Analytics
13
Status
Student
Human Resources
Financials
Benefits
Faculty
Advance- ment
Endowment Mgt
Research Mgt
Space/ Facilities
Service Mgt
Benefits
Faculty
Student
Human Resources
Financials
Data administration
Service Mgt
Research Mgt
Space/ Facilities
Advance- ment
Endow- ment Mgt
Data access policy
Reporting tools
Data governance
Institutional, program, and project goals
Performance metrics
Dashboards
Research questions
Analytics
14
Issues

• Establishing a culture and organizational focal
  point for information-based analytics in a
  consensus system
• Performance analysis
• Institutional research
• Assuring current and future systems support
  DW/Analytics.
• The Financial system is a particular focus
• In reference to the model, the max above is
  constrained by the min below
• Establish mechanism(s) to address
• Data access policy
• Data governance
• Data administration
• Building a long-term funding strategy
• Some foundational elements are in the investment
  plan
• Missing the dedicated resources per application
  area
• In both UIS and Functional areas.

15
Experience of Peer Institutions

• MIT
• George Washington (GW)
• University of Pennsylvania (Penn)
• Yale

16
Sample of Peers
17
Sample of Peers
18
Conclusions from peer analysis

• Peer institution beset by the same demands and
  challenges.
• Many have launched data-related initiatives
  similar to those at GU.
• Most have made more progress on data
  administration and access policy.
• Most have moved further up the integrated DW
  layer..
• ..but with selected data
• We could not find a really good exemplar of an R1
  university that has a comprehensive solution

19
Status of our current toolset

• ETL tool Informatica
• Top of the line
• Query Tool Cognos
• Version out of date
• Web reports successful
• Security is administered in Cognos rather than
  underlying databases
• This is a significant constraint
• We have no standard tool for Analytics at this
  point
• SAS is often used for statistical processing
• Nothing available for true analytics

20
Open tools issues

• ETL tool Stay with Informatica or move to
  Oracles Warehouse Builder
• This is Banners tool
• Query Tool Cognos
• Given the market is in flux we can
• Stand pat for awhile (3 years?)
• This is what SunGardHE decided to do
• Would require upgrade to latest release
• Tool choice for analytics
• SAS is the industry leader, but there are others
  we should look at
• Hyperion, Banner Analytics, etc.

21
Addressing tools issues

• Establish Analytics (business intelligence) tools
  evaluation committee.
• Make recommendations on ODS and IDE tools
• Investigate Analytics tools options.
• Lay the ground work for next generation query
  tool choice
• As market stabilizes.
• Members from UIS and user communities.

22
Proposed Calendar (1)
2007 2008 2009 2010 2011
Business Systems Context

• Replace SIS with Banner
• Define FMS then Replace
• Stabilize Enhance Space
• Migrate HRIS off Mainframe

2007 2008 2009 2010 2011
Refresh or Build ODSs

• Review interim ETL and reporting tools
• Establish Secure Reporting Environment
• Deploy 2nd Generation ODSs for
• Student, Financials, HR, and Space

23
Proposed Calendar (2)
2007 2008 2009 2010
Data Administration

• Establish Data Administrator
• Build and Hire Data Admin team
• Develop 1st generation data inventory
• Establish Data Governance committees
• Develop Data Access policies

UIS and Business Division Staffing

• Hire IA and Reporting Vacancies

Integrated data environment Analytics

• Develop 1st generation IDEs
• Develop 1st generation Analytics

24
Organizational ModelData Access, Analytics, Self
Service, Enterprise Web
Director, EETS
AVP, NCS
Associate Director, Information Access
DBAs
Applications, Managers Financials, HR, Space,
Student
DBA
Web Data Architect
Technical Manager, Information Access
Application Teams
Business Divisions
Business Divisions
Business Analyst
Data Administrator
Data Engineer
Application Programmer
Business Reporting Analyst
Business Reporting Analyst
Data Access Policy Working Group Data
Governance Committee
Business Reporting Analyst
Data Engineer
Application Programmer
Business Reporting Analyst
Business Reporting Analyst
Business Reporting Analyst
Application Programmer