Business Intelligence Implementation
Business Intelligence Implementation
1. Implementation Strategy_____________________________ 3
A. Background ______________________________________ 3
B. Objectives _______________________________________ 4
C. Context __________________________________________ 4
D. Audiences _______________________________________ 5
E. Access and Security _______________________________ 5
F. Common Data and Data Stewardship _________________ 6
G. Education and Training ____________________________ 6
H. Staffing and Sponsorship __________________________ 6
I. Implementation Schedule ___________________________ 7
J. Success Criteria___________________________________ 7
2. Data Conversion and Data Quality Statement of Work ___ 7
A. Objectives _______________________________________ 7
B. Context __________________________________________ 7
C. Goals ___________________________________________ 7
D. Deliverables ______________________________________ 7
E. Scope ___________________________________________ 8
F. Constraints _______________________________________ 8
G. Success Criteria __________________________________ 8
3. Data Warehouse Statement of Work ___________________ 9
A. Objective ________________________________________ 9
B. Context __________________________________________ 9
C. Goals ___________________________________________ 9
D. Deliverables ______________________________________ 9
E. Scope __________________________________________ 10
F. Success Criteria __________________________________ 10
The University of Arizona | Mosaic Implementation Plan
1 of 14
Business Intelligence Implementation
4. Data Consumption Statement of Work ________________ 10
A. Objectives ______________________________________ 10
B. Context _________________________________________ 11
C. Goals __________________________________________ 11
D. Deliverables _____________________________________ 11
E. Scope __________________________________________ 12
F. Constraints ______________________________________ 12
G. Success Criteria _________________________________ 12
5. Business Intelligence Implementation Timeline _________ 14
The University of Arizona | Mosaic Implementation Plan
2 of 14
Business Intelligence Implementation
1. Implementation Strategy
A. Background
Obtaining consistent data and creating meaningful reports has been a major challenge at The
University of Arizona. For various reasons there has been a lack of trust in University data, no
consensus of what is the ‘appropriate’ data for different questions, and only few individuals have
been able to access and make use of data successfully. Decision makers have routinely depended
on IT experts to compile data, sometimes waiting days or weeks for the needed answers. However,
day-to-day University operations demand timely and accurate information. Furthermore, executive
administration and the Arizona Board of Regents are increasingly asking for more information to
make business decisions which at present has been very difficult to provide on short notice. With the
implementation of new transactional systems (Kuali and PeopleSoft) for the University of Arizona,
the realization of Business Intelligence will be the answer to these challenges. Enormous amounts of
integrated data will lead to faster and better decision making.
The objectives of the Business Intelligence (BI) Implementation are to provide a new approach to data
management, presentation and analytics. Implementing Business Intelligence will enable the University
of Arizona to:
•
•
•
•
access and retrieve financial, research, personnel and student data from one single source
give easy and secure access of relevant data to all levels of management or units
spend minimal time on data retrieval, but dedicate significant time to data analysis and decision
making
make data, reports, analysis and models available to a broad user base
Business intelligence will primarily focus on processes and people. To this purpose the BI team will
need to ensure data conversion and data warehousing consistent with the needs of current and future
organizational goals. Within the Mosaic project the Business Intelligence effort encompasses data
conversion and cleansing, data warehousing and data consumption. Only “good” data will be converted
and migrated into the data warehouse; older (non-cleansed) data will be stored for historical purposes
only. The data warehouse will allow for secure storage and access to data from the new transactional
systems (Kuali, PeopleSoft) and will allow additional storage of other University applications (e.g. D2L)
and individual departmental needs. For reporting, a set number of general dashboards and reports will
be made available at roll out time for Kuali and PeopleSoft. Extensive training of the basic BI tool will be
offered in the initial phase. In a later phase tools for data analysis will be added to support analytics.
More broadly, this change in data management is allowing data consumers to ask new types of
questions that previously went unanswered. By structuring data from a University perspective based
on consistency of common data and hierarchies, we can make a leap forward in reporting, predictive
analytics and data modeling.
For University operations to truly benefit from Business Intelligence the user base needs to be
broadened. A concerted effort is planned to engage all levels of decision makers from executives to
payroll representatives to become Business Intelligence users.
Finally, the timing for implementing BI is excellent. Rapid advances in processing speed, innovative
data warehouse design and pioneering concepts of data display such as dashboards are opening up
new opportunities for data management.
The University of Arizona | Mosaic Implementation Plan
3 of 14
Business Intelligence Implementation
B. Objectives
In order for The University of Arizona to increase its operational effectiveness decision makers need
to have easy access to data that is timely, accurate and integrated. Although there is no absolute
dollar value assessed for the BI effort, it is clear that integrated data (1) minimizes the need for
duplicate data entry and reconciliation of inconsistent information thus reducing manpower needs, (2)
drives management decisions to improve operation, and (3) improves the intelligence around student
recruiting, performance and success driving the educational and research experience at the University.
It should be noted as well that BI seeks to minimize the “cost of distrust.” If the integrated data are not
trusted, then users will seek the ability to “massage” that data based on the perception or the actuality
of its inaccuracy, and frequently then to optimize a portion of the overall business framework of the
University. But this optimization of the part does not optimize the whole—if data inaccuracies are not
corrected at their source (within the transactional systems and associated business processes) then
the cycle will continue. Shadow systems will again proliferate and there will not be a canonical version
of the facts. BI seeks fundamentally to address this cycle by providing high-quality data and tools and
associated business processes, so that the BI way is clearly recognized as the superior way.
Goals for BI are at two levels. Firstly, there is the goal to enable a smooth (i.e. non-interrupted)
transition from the old to the new system, so as not to disrupt the current operation. Secondly, a
more challenging goal is to create a data environment that will answer questions that have not been
previously possible.
Much of the implementation plan focuses on data migration, data warehousing and reporting.
However, for the University of Arizona to stay competitive on a national level and to obtain full value,
the implementation of Business Intelligence is not the final destination. As vast amounts of data are
collected, new opportunities to analyze and model data will present themselves. The now ready
availability of statistical tools to perform data mining will allow the exploration of the relationships
among data in order to extract value and ultimately new insights into running the University. Areas of
particular interest will be measuring student success and return on faculty accomplishments.
C. Context
The Mosaic project is divided into four primary initiatives: Human Resources (HR), Financial Systems
(FS), Student Administration (SA), and Research Administration (RA). The BI effort is overarching to all
four of these initiatives and thus has an impact on the perception of success of each initiative.
Historically, The University of Arizona has only offered a limited amount of central reporting capabilities.
Most reporting occurs locally and, because of technology limitations, does not allow sharing of reports
easily. This has led to many problems such as different business logics being applied which result in
different findings and a loss of efficiency since users need to create their own reports.
Moreover, breaking the data flow from the University Information System (UIS – the current UA
operational data store) and other currently used applications will cause major upheaval to the
Campus and its current use of data. A majority of data users download large data sets from UIS into
report templates or into their own applications (both commercial and homegrown). This has led to a
proliferation of ‘local’ systems – frequently redundant - and large amounts of data that may not be wellmaintained. A plan to manage the transition and not interrupt the flow of business reporting is key to the
success of the BI implementation. However, it is important to note that not all transition issues are BI
related, but that close collaboration with the four initiatives and the Integration team is required.
The University of Arizona | Mosaic Implementation Plan
4 of 14
Business Intelligence Implementation
D. Audiences
There are three audiences on campus that stand to benefit from the Business Intelligence project:
1. Power-users / developers
This is a group of 100 or more technicians that predominately use UIS today to produce reports
that they use themselves or that they distribute to others. The project will serve this audience by
providing access to a richer set of transaction data and by providing technological efficiencies that
allow them to spend less time ‘pulling’ information and more time ‘analyzing’ information.
2. Operational Decision Makers
This is a group of managers or leaders in administrative and academic organizations. These
managers, directors, business officers, departmental and college management are typically
dependant upon a dedicated report writer today (see above). This leads to a disparity in access
to and use of information for day-to-day decision making. In many cases, having a technician on
the critical path means that information is not used in decision making. The project will serve this
audience by providing them a set of dashboard reports that they can individually access through an
easy-to-use web self-service reporting portal.
3. Executive Decision Makers
This group of deans, AVPs, VPs, and University management are reliant upon dedicated report
writers today as well. This audience generally does not have ‘direct’ access to information, but their
position means that they generally have programming staff who can provide information to support
their decisions. Unfortunately, this information frequently needs to be ‘cobbled together’ from a
number of different sources, lacks context (relationship to the same number last year or in another
unit), and is generally provided on-demand only (i.e., no notifications or early warnings). The project
will serve this audience by providing self-service dashboard reports that blend current information
with contextual information. The project will also serve this audience by beginning to produce a
scorecard dashboard that quickly displays some of the indicators defined in the UA strategic plan.
While it is useful to describe three distinct audiences, there is in reality an overlap between them.
Generally, the project intends to provide a richer set of information to a significantly broader audience
and to shift the allocation of time away from ‘pulling and maintaining’ information and toward analyzing
and using information.
It is also important to note that self-service reporting provided through the BI project is not just a tool
technology, or project – it is a disruptive change to decision makers at all levels. Through frequent
communications and training we envision making this change a positive experience.
E. Access and Security
From an access and security standpoint the vision of the Mosaic Project is to ensure that decision
makers at all levels within the University have access to data needed to perform their jobs. Any data
directly accessible by the public will be highly controlled.
General security will be provided through encryption for users and for applications that access
information. Authentication will be provided through the already existing WebAuth application the
University utilizes. Access to highly sensitive data such as social security numbers, credit card
numbers, banking information, driver license numbers and benefit information will be granted on
a ‘need only’ basis. The Mosaic project will look to the UA data stewards to ensure that adequate
controls in addition to the above proposed ones are in place so that education records of students are
handled in accordance with FERPA’s privacy protections.
The University of Arizona | Mosaic Implementation Plan
5 of 14
Business Intelligence Implementation
To mitigate the risk of inappropriate use of data, the BI team advocates that a balance be struck
between electronically restricting data access and training users to handle data responsibly. Besides
the enormous cost, if too many technology barriers are built into the system, data users will circumvent
the new data system, and proliferation of different data repositories will continue, thus defeating the
purpose of BI.
The Mosaic project will not design its own access and security policy, but will follow the UA access
policy and implement access controls in the system based on that policy.
F. Common Data and Data Stewardship
Organizational agreement on definitions for common data is facilitated by the Mosaic project in
discussion with functional and technical stakeholders and is important to a successful implementation
of the BI effort. The common definitions for conformed warehouse dimensions and facts have to be
identified and published. Reinforcement of these definitions is expected to be done by data stewards, a
group that resides outside of the Mosaic project.
G. Education and Training
Activities around data migration and data warehousing will be limited to a small team and do not
necessitate broad based training. In contrast, much training effort will be required for the data
consumers.
For data consumers there will be a need to learn the new tool(s) and the new data environment. The BI
implementation plan focuses primarily on enabling data users to be empowered and independent. The
majority of users are expected to be fully served by using the Gartner Group “Magic Quadrant” BI Suite
(Oracle Business Intelligence Enterprise Edition, aka OBIEE). Training will enable users to create their
own queries to produce reports and dashboards specific to their business needs. Additional training
will be provided in the use of Hyperion which is a very advanced tool. Mosaic-provided training is not
mandatory for OBIEE+. However, access to Hyperion will require FERPA and “general” data training.
Throughout the BI implementation, individual help to solve problems will be offered at a daily open
house staffed by the BI team. Additional support will be provided through listservs and blogs.
H. Staffing and Sponsorship
The BI staffing is based on a matrix organization. The BI technical staff is embedded in the various
initiatives, but report to the BI initiative as well. This structure is intended to ensure that best practices
and consistency for BI activities are established. It also directly addresses the different support needs
of the four initiatives.
The core team members are full-time. The technical staff members are placed according to their
previous subject matter expertise and are responsible for the technical configuration of data and tools.
The project manager coordinates, controls and reviews all project activities; the BI consultant ensures
development and reporting standards; and the BI implementation director has the overall responsibility
of the project including communications and ensuring BI activities are aligned with the strategic goals of
the Campus.
The BI Advisory Council’s role is to advise on the implementation of BI and give perspective on the
needs of key UA stakeholders to respond to internal and external market forces.
The University of Arizona | Mosaic Implementation Plan
6 of 14
Business Intelligence Implementation
Sponsorship for the BI implementation lies with Assistant Vice President Karen Filippelli, Dean Ron
Marx, and Vice President Jacqueline Mok.
I. Implementation Schedule
In general, data migration and mapping of data warehousing must precede go-live dates of each the
four implementation efforts. In contrast, availability of a limited set of central BI dashboards and reports
will be concomitant to the go-live dates of the individual transactional system modules. A detailed
project plan is added at the end of the document.
J. Success Criteria
The BI effort is a very visible part of the Mosaic project to most on Campus and hence is very exposed
to user satisfaction levels. Success criteria include:
•
•
•
Positive feedback from users on vastly improved access to data and information
Perfect reproducibility and consistency of data when queried though different channels
Improved communications and decision making at all levels
2. Data Conversion and Data Quality Statement of Work
A. Objectives
The objective of data conversion is to migrate high value information from our legacy systems into our
new PeopleSoft and Kuali transaction systems. Transforming this information from legacy formats and
values into the new formats and values is useful for both transaction processing and for reporting and
analysis.
B. Context
The majority of data to be migrated is currently residing in the UIS database. Since UIS has grown
organically as an operational data store (ODS) with limited consideration to valid, accurate, complete or
consistent values, it does not make sense to migrate all historical data to the new database as is. The
Mosaic project is an ideal opportunity to address data quality improvements.
A standard ETL (extract, transform and load) programming methodology will be followed for the data
conversion, and a variety of technologies will be utilized as appropriate, including: Ascential DataStage,
PeopleSoft DataMover, SQR, PeopleSoft Application Engine, PeopleSoft Component Interfaces, Oracle
PL/SQL and Oracle SQLLoader.
C. Goals
The goals of data conversion are as follows:
• Converting data to the extent that data are accurate, complete and consistent. This will result in
different amounts of data being converted for the different initiatives.
• Converting a limited number of additional tables from UIS available for future dynamic use (e.g.
Space, CatCard)
• Designing an approach to prevent future “dirty” data from entering the data warehouse
• Maintaining ability to access historical data in the new data warehouse
• Capturing meta data from old source systems to new Mosaic systems
The University of Arizona | Mosaic Implementation Plan
7 of 14
Business Intelligence Implementation
D. Deliverables
Overall:
• Source to target mapping (including rules)
• ETL process flow diagrams and design documents
• Standards and guidelines for data quality
• Job scheduler for nightly load schedules (batch process)
• “Error reports” to monitor processes and to take corrective action
• Migration of all historical data residing in UIS to the new data warehouse without any cleansing ,
for reference purposes
• Meta data in meta data repository
Human Resources:
• Conversion and cleansing of workforce (job) information back to 1996
Kuali Financials:
• Conversion of preliminary account balances and FY06 onwards transaction details
Student Administration:
• Conversion of Application data for at least one year. This will include high school transcripts and
test scores as appropriate as well
• Conversion of biographic and demographic data for all students in SIS
• Conversion of Course Catalog and Schedule of Classes data for all courses and sections in
SIS. Additional details, like instructor and meeting details will be converted for at least one
academic year
• Conversion of Student Careers, Programs, Plans, and Degrees
• Conversion of Student Registration and Transcripts data
E. Scope
In scope: All valid operational data collected in FRS, SPINS, PSOS, and SIS/MATRIX
Out of scope:
• Conversion of departmental databases
• Not all legacy data will or can be migrated into the transactional systems thus necessitating
intricate querying methods for some historic data. Blending new transaction data with old
transaction data that was not transformed during the transaction conversion process will be very
difficult and will be undertaken by individuals as they encounter specific needs
F. Constraints
•
•
Resource availability
Quality of existing transaction data and cost to cleanse history
G. Success Criteria
Successful data conversion will be measurable as follows:
• Create, run, and resolve reports that reconcile existing PSOS data to converted PeopleSoft
HCM data
• Create, run, and resolve reports that reconcile existing SIS data to converted PeopleSoft
Student Administration data
• Create, run, and resolve reports that reconcile existing FRS data to converted Kuali FS data
The University of Arizona | Mosaic Implementation Plan
8 of 14
Business Intelligence Implementation
3. Data Warehouse Statement of Work
A. Objective
The objective of the new data warehouse is to create a business intelligence environment and data
models that provide the ability to access information in many different ways without introducing errors.
B. Context
Data integration has been a major challenge for the UA. The tables in UIS are highly normalized.
However, while normalization of data is beneficial to reducing data inconsistencies and data
redundancies, it makes data querying more difficult.
There are a variety of data management approaches that can be taken. However, the Mosaic project
has decided to put heavy emphasis on the ‘data-out’ philosophy, i.e. reporting and querying. Hence,
we have chosen an enriched database design technique called ‘Star Schema’ that stores aggregated
and summarized data in a multidimensional fashion and is based on the Kimball data model.
Multidimensional database design supports quick data retrieval and allows determining the level of data
granularity the users can access.
Documentation on the warehouse content (i.e., metadata) is an integral part of data management.
There has been an effort in the past to capture metadata. However, the Mosaic project emphasis on
providing contextual information on data will ensure consistent usage by users and help clarify some of
today’s vaguer data definitions.
Growth management of data, usage and hardware needs to be considered. While hardware costs are
becoming increasingly negligible, a strategy to manage the various databases is required.
C. Goals
The goals for the data warehouse mapping and implementation are the following:
• Building the EPM configuration consistent with PeopleSoft and Kuali feeds
• Constructing a ‘single version of the truth’ environment by creating a data warehouse that
stores data from the new transactional systems PeopleSoft and Kuali (dynamic), imported
legacy data (static) and a user data area (dynamic)
• No duplication of records in enhanced ODS
• Time stamping of data
• Creating a better understanding of the data that allows an integrated solution to data reporting
and querying
• Building data schemata that allow drilling
• Creating a repository for metadata to capture data definitions
• Developing a data and growth management plan
• Ensuring secure access and storage of data
D. Deliverables
The deliverables are organized by the overall project needs and by the different initiatives.
Overall:
• Creation of ODS, data warehouse and departmental use area that are compatible with
The University of Arizona | Mosaic Implementation Plan
9 of 14
Business Intelligence Implementation
•
•
•
•
•
•
•
•
PeopleSoft DW/BI solutions
Implementation of data warehouse and BI environments that minimize the amount of rewrite
needed for BI content, operational reports, interfaces and ETL jobs
Policy and procedures to ensure consistent data management
End user documentation including translation tables and metadata
Inventory tracking system for unresolved issues
Backup strategy
Error reporting
Secure access to data based on authentication and authorization
Change management plan
Human Resources:
• EPM set up of global workforce, compensation and recruiting mart dimensions
• Data models for critical HR operational reporting and queries
Financial Systems:
• Development of ODS inside EPM containing cross-module tables, chart of accounts, all
ledgers, grants and contracts, accounts payable and receivable, budget, capital assets
•
Data models for critical FS operational reporting and queries
Student Administration:
• EPM set up of student records
• Data models for critical SA operational reporting and queries
Research Administration:
• Data models for critical grants and contracts operational reporting and queries
E. Scope
In scope:
The scope entails the creation of a data warehouse environment that will replace the need to access
information through ISW, IIW or UIS. All PeopleSoft and Kuali transactional data will be accessible in
EPM, plus a selected number of UA enterprise-level databases (e.g. CatCard, Space) will be available
as well
Out of scope:
Departmental and low use databases will not be included in the new data warehouse. No integration to
external databases
F. Success Criteria
Successful mapping and structuring of the data warehouse will be measurable as follows:
• Replaced UIS – % new applications, abandoned, EDS connections
• Number of subject areas available in reporting self-service
4. Data Consumption Statement of Work
A. Objectives
The objective of the BI effort is to transform the UA into a place of “data-informed decision making” by
providing easy and fast access to data. This will be achieved through prefabricated/templated central
dashboards and reports as well as by training users to create their own queries and reports.
The University of Arizona | Mosaic Implementation Plan
10 of 14
Business Intelligence Implementation
In addition to the basic components of BI, over the longer term the power of data analysis will be
enhanced through statistical tools to support data mining and more advanced analysis techniques.
B. Context
The data consumption portion of the BI implementation is to most users the visible part of business
intelligence. Users will invariably judge BI by its ease of access to data, accuracy, consistency, and
completeness of information. However, other tangible benefits lie in providing the foundation for
improved efficiency and quality in decision making.
Exactly who and how many people today are creating reports or downloading data is not clear. In
summer of 2008 there were 627 active UIS users. Responses to a survey were informative (response
rate of 59%), but needed further clarification. In a second round, readiness assessment meetings
are being conducted to gain more detail on the nature, criticality and frequency of data usage. As a
result a list of highly utilized reports and application downloads is being compiled. The latter will be
addressed by the Integration team. Furthermore, not all initiatives will be going live at the same time, so
a staggered implementation is feasible and planned.
There will be two software tools available; Gartner Group “Magic Quadrant” BI Suite OBIEE+ (Oracle
Business Intelligence Enterprise Edition) and Hyperion, that will enable users to query data. On the
easiest level users will be able to access dashboards with drill down capabilities through a web-based
portal. In addition, with a minimal amount of training in OBIEE+, a user can construct ad hoc queries.
Finally, for the power users, Hyperion will allow complex queries across fact areas. However, advanced
training will be required to ensure effective usage of this tool.
C. Goals
The short term goals are to proactively build dashboards and commonly created reports in OBIEE
before disrupting the current data environment. Data presentation and visualization are a key
component of this effort.
Longer term goals are:
• Creating a competency track for BI
• Developing new analyses: for example tuition flow, faculty analysis (peer review, promotion and
tenure)
D. Deliverables
The deliverables are organized by the overall project needs and by the different initiatives.
Overall:
• Facilitator to the Campus to gather reporting needs (January – March 2009, and beyond
depending on the go-live schedules of the individual initiatives)
• Dashboards and reports accessible through an official web portal
• Secure access to data based on authentication and authorization
• Scorecard on UA strategic key measures by March 2010 (to the extent supported by the go-live
status of the initiatives that supply the data)
• Access to the identified top critical number of dashboards and reports with the go-live of each
initiative
• Data and OBIEE training, live or web-based to all who want or need training
• Support for data users to become proficient in OBIEE after initial training through listserv
communications and walk in open house hours
• Competency roadmap for BI
The University of Arizona | Mosaic Implementation Plan
11 of 14
Business Intelligence Implementation
•
User survey after 6 months of going live for each initiative
Human Resources:
• Gap analysis on critical reporting requirements by July 2009
• Executive dashboard for deans and executive level for key employee data by October 2009
• Dashboards and reports for employment metrics to encompass demographics, benefits, leave,
recruitment, retirement, turnover and compensation
Financial Systems:
• Top 10 reports as identified by the readiness assessment team by January 2010
• 5 dashboards by January 2010
Student Administration:
• Reports and dashboards
Research Administration
• Reports and dashboards
E. Scope
In scope: Data access though OBIEE+ and Hyperion. If necessary, downloading of data into excel
spreadsheets will be permitted, but strongly discouraged for data security reasons.
Out of scope:
• During the implementation phase, the creation of non-critical BI queries and dashboards will not
be addressed
• Accommodation for interfaces to applications that use the new warehouse will not be handled
by the BI technical staff (responsibility of the Integration team)
• Querying of unstructured data
F. Constraints
BI activities are constrained by the go-live schedules of the associated transaction system initiatives.
Developing a comprehensive and multifaceted business intelligence enterprise necessitates time and
an iterative approach. The implementation timeline only allows for a framework of BI. Over time BI will,
if provided with the necessary resources, provide a broad-ranging and more inclusive approach.
G. Success Criteria
The success criteria for BI implementation are organized as project level criteria and affect the initiative
in its entirety. We will assess the measurables below during the phased transaction implementations,
and plan to achieve the goals below by June 2011.
•
•
•
•
•
Percentage of Student Administration, Financials, and HR subject areas available through selfservice reporting
Percentage of Student Administration, Financials, and HR database tables available to powerusers through database level connection
Percentage of University executive leadership/deans, academic and administrative
management utilizing self-service reports
Number of individual users utilizing self-service reports each work day
Number of individual reports, dashboards, or other content available on a shared basis in selfservice reporting environment
The University of Arizona | Mosaic Implementation Plan
12 of 14
Business Intelligence Implementation
•
User satisfaction - Users believe
ο That they have access to a sufficient amount of information
ο That the information is useful to their decision making
ο The data is available and timely when queried
ο That data is reliable and consistent
The University of Arizona | Mosaic Implementation Plan
13 of 14
Business Intelligence Implementation
5. Business Intelligence Implementation Timeline
Business Intelligence (BI) — Project Plan Highlights
The University of Arizona | Mosaic Implementation Plan
14 of 14