The Data Warehouse What is it good for

1
The Data WarehouseWhat is it good for?

• Michael Wonderlich, Asst Director for Technical
  Architecture
• Aaron Walz, Asst Director for Information
  Architecture
• Decision Support
• University of Illinois

2
Decision Support

• Decision Support is
• A customer service organization
• Focused on data warehousing, business
  intelligence, and information management
• Mission
• Support customers in colleges and departments
• Support management, planning, and strategic
  decision-making
• Supply information solutions and services
• Accomplished by
• Excellence in DW and BI practices
• Integration requirements, data, delivery

3
Decision Support

• Services provided
• Nightly ETL updates
• DW/BI performance
• Capacity planning
• Upgrades
• Security, access, logons
• Data quality process
• Data education
• Tool training
• Metadata
• Web site
• Telephone support
• Project support
• Business Objects management, access
• Query Clearinghouse and Business Solutions
• Report publishing and directory

4
Defining a BICC

• A BICC is a cross-functional team with specific
  tasks, roles, responsibilities and processes for
  supporting and promoting the effective use of
  Business Intelligence across the organization.
• -Gartner Research

5
Why have a BICC?

• If BI is to extend beyond tactical deployments to
  become a broader-based solution, a managed,
  predictable approach is needed. A BICC defines
  the knowledge, standards, and resources needed to
  make this happen.
• Building a Business Intelligence Competency
  Center, Cognos

6
Major Components of a BICC

• Governance
• Information Delivery
• Data Management
• Environment Management
• Employee and Customer Relations

7
(No Transcript)
8
Components of the UI Data Warehouse

• EDW (Enterprise Data Warehouse)
• Data Marts
• Business Objects Universes
• OLAP Cubes
• Dashboards

9

10
What Subject Areas are in the EDW?

• Student Recruiting Admissions
• Student Records
• Student Registration
• Student Catalog Schedule
• Student Financial Assistance
• Finance (GL, AR, AP, FA, etc)
• HR Position Employee
• HR Payroll
• Purchase Card
• Business Operating Statements
• Grants Pre-Award
• Grants Analysis
• Student Web Applications
• Student Application Census
• Student Registration Census
• Payroll Reconciliation

11
Center of the World - Data Warehouse (DW)
Size of Tables (in rows) Rows (rnd) of
Tbls 100M-175M .2 2 10M-99M 3
25 1-9M 16 118 500K-999K 7 52 100K-499K 13
95 10K-99K 16 124 1-9999 43 320

• Total Tables 738

of Rows 1,348,069,592
Enterprise Data Warehouse (EDW) 678
tables Data Mart(s) 60 tables
Code Tables 27 (201) History Tables
20 (149, 31 are code tables)
of Intermediate Tables 44
Truncate/Reload 60-65 Incremental
35-40
of DW Source Tables 640 of Rows 998,180,908
12
How is the Data Accessible?

• Business Objects
• Web Intelligence
• Desktop Intelligence
• Crystal Reports
• Microsoft Analysis Services
• OLAP Cubes
• Corda CenterView
• Dashboards
• Direct Connect
• ODBC
• JDBC

13
DS Web Site

• Metadata
• Data Models
• Data Advisories
• Daily Status
• Release Notes
• Query Clearing House
• Solution Library
• Request Access
• Training
• Online
• Class Registration

14
(No Transcript)
15
Training Available

• Business Objects
• Web Intelligence
• Desktop Intelligence
• Basic and Advanced
• Infoview (viewing standard reports)
• Custom Lab Sessions
• Come Practice Reporting (CPR)
• Data Education
• Subject area specific

16
Usage of the Warehouse

• In 2008
• Sessions                 2,851,377
• Unique Users                1,873
• Query Events     15,229,129

17
How are you connecting?

• Business Objects
• Crystal Reports
• Microsoft Access
• Microsoft EXCEL
• Microsoft Query
• Microsoft SQL Server
• Microsoft Visual Studio
• Microsoft Word
• Oracle database connection
• SAS
• Sybase database connection
• Tableau
• TOAD
• User Built Applications
• PERL
• PHP
• Java
• ASP
• Python

18
Summary of Data Warehouse Usage
19
Users Solutions

• Vet Med Teaching Hospital local accounting
  system using Data Warehouse now
• College of Ed combining local faculty data with
  enterprise data from DS new Consolidated Faculty
  Activity product
• ACES custom developed dashboards and web-reports
  for financial information using DW data
• UIC Public Health local database for Grants
  reporting using new DS Grants products now

20
Whats Next in the Warehouse?

• Change Management
• Data Acquisition
• Business Intelligence Directions

21
What does Change Management Include?

• The Change Management Process
• includes minor changes to the Data Warehouse
  tables views, processing logic, and Business
  Objects universes. 
• is designed for defect corrections and additions
  or enhancements to functionality that are not
  a large enough work effort to be considered a
  project.

22
Change Requests Types

• Defect
• An error resulting in a difference between the
  actual behavior and its expected behavior or
  functionality.
• Enhancement
• A change to a product's originally defined
  behavior or functionality.
• Info Quality
• A data integrity or data quality issue.

23
Examples of Other Production Work

• Security updates, additions, and corrections
• Query performance enhancements, such as new or
  modified indexes
• Production fixes to the Informatica maps
  resulting from ETL failures
• ETL batch window enhancements, such as
  implementing Oracle Streams

24
Upcoming Projects

• Adding Codebook data
• Adding Student Orientation data
• Banner 8 (including multi-year encumbrance)
• Updating Global Campus campus codes
• Adding new race/eth data (OMB-R/E)

25
BI Directions

• Goals
• Make the data easier to use for specific needs
• Better solutions to support decision making
• Approach
• Layered architecture
• Right BI tool for the right information need

26
BI Directions Layered Architecture
27
BI Directions Layered Architecture
28
Right BI Tool

• Reports are the hammer
• but not every information need is a nail

29
Right BI Tool

• Dashboards
• Lay of the land, performance
• Monitoring what
• OLAP Cube
• Explore
• Analysis why
• Reporting
• Catalog findings
• Creating lists look up specific details

30
Right BI Tool
What?

• Dashboards
• Lay of the land, performance
• Monitoring what
• OLAP Cube
• Explore
• Analysis why
• Reporting
• Catalog findings
• Creating lists look up specific details

Why?
Lists
31
BI Directions

• For local solution providers
• Simplified data structures with standardized
  business rules so you dont have to re-invent the
  wheel
• Combine local and enterprise data
• Use your own tools on top of enterprise data
• Easier to use enterprise BI tools for business
  users
• Future goal new enterprise BI tools (e.g.
  Crystal Reports, Corda Dashboards)

32
BI Directions

• Specific projects for FY09/10
• UA Strategic Planning dashboard
• Grants data marts, universes, dashboards,
  OLAP Cube
• Consolidated Faculty Activity data mart,
  universe, OLAP cube, dashboards

33
Discussion

• How are you using the Data Warehouse?
• Reports?
• Applications?
• Downloading data for local systems?
• What challenges are you encountering?
• What would empower you to provide new or improved
  solutions for your unit?

34
More Information

• DS Web Site (http//www.ds.uillinois.edu)
• Practice Labs
• DS Training
• Contact us directly

35
Additional Questions?