Strategic and Tactical Information
via Data Warehousing

RP17 - W130

SCT Summit 1999



Remove name badge.  It can be a distraction.



Title Slide


Thank the SCT person.


Hello, my name is David Heise, and I’m from Andrews University.


Firstly, I need to see how well this microphone is working.  Folk in the back rows, would you please put your hand up if you can hear me…


Now, this session is RP17; we'll be talking about Data Warehousing as a way of providing Strategic and Tactical Information.


I want to thank all of you for coming.  This is the last session of an information-packed conference, and I am guessing many of you are reaching information overload.  It can be very difficult to concentrate on the last afternoon of a hectic conference, straight after lunch, with a warm and sunny Orlando right outside.





¨       What this session is about:


§         Saving your managers from the data flood and the information drought

§         Buried deep within the Banner database are the answers to almost any question that managers, administrators or decision-makers might ask.  But those answers are not delivered on a silver platter.  A “feature” of Banner is that individual institutions get to write their own management reports.  Your programming staff are probably being run ragged doing implementations, conversions, and upgrades.  “List” or tabular type reports and data extracts may be the best you can do to satisfy your customers need for information.

§         In this session, I hope to show how it is possible to take the transaction-oriented Banner database and through a data warehouse, provide tools that support tactical operations as well as strategic planning and decision making.


§         But first, a little more about who I am.


¨       Who I am:


§         David Heise, CIO,Andrews University




§         I have worked in software engineering for just short of 30 years.  I have been atAndrews University for nearly 3 years, and before that, I held a similar position in a small college in Australia for 8½ years.

§         Feel free to email me after the conference, visit my personal home page, or the Information Technology Services home page.  These addresses are also on the last slide at the end of the presentation.


About Andrews University


§         Andrews University is a small private university of about 3,000 students divided almost evenly between undergraduate and graduate programs.

§         We are located in the south-western corner of the state of Michigan.

§         We have implemented Banner Finance, HR, Student, Alumni and Financial Aid.  We are in the process of implementing Web for Student and plan to have web registration fully operational by Fall this year.


About You


¨       Who is building, or already using, a data warehouse?

§         Who has a data warehousing project team in place or a data warehouse already in use?

§         At this stage, also put your hand up if you are using any kind of point and click data access tools on:

§         Banner tables / views?

§         separate reporting tables in the Banner database?

§         a separate database on the same machine?

§         a separate database on a different machine?

¨       Is it (or will it be) in a separate database?

¨       Is it (or will it be) on a separate machine?

¨       Who attended Phil Isensee’s presentation? (GN02 on Monday at 10:00)

¨       Remember to complete the evaluation forms


Presentation Outline


1.      What is a Data Warehouse?

§         Firstly, a data warehouse is not a reporting tool, although point and click reporting tools can be used very effectively with data warehouses.

§         And secondly, a data warehouse is not a product you buy out of the box.


2.      Why do we need it?

§         Not only does a data warehouse facilitate end-user reporting, it makes online data analysis possible, so your knowledge workers can not only see what happened, but can interactively search for reasons why it happened.


3.      How do we get it?

§         Many keys for successfully building a data warehouse have been identified.  Here are just a couple:

§         Choosing how and where to start

§         Using a proven methodology, with an architected approach


4.      Data Warehouse Elements



5.      Demonstration of a Business Intelligence Tool

§         I will give a brief demonstration of PowerPlay, the data analysis tool from Cognos.



1. What is a Data Warehouse?                                              1 of 3


¨       The classic 1993 definition by Bill Inmon, “father of data warehousing”


§         A data ware house is a:


§         subject oriented


§         integrated


§         non-volatile


§         time variant


§         collection of data in support of management’s decisions.




1. What is a Data Warehouse?                                              2 of 3


¨       Typical production databases are designed for OnLine Transaction Processing (OLTP)

§         The first relational databases suffered big performance hits over traditional storage methods.  These have largely been overcome through advances in hardware performance, as well as the relational technology itself, including database designs that optimize throughput.

§         Examples: posting general ledger entries, writing and posting checks, calculating and posting student fees, payroll processing.


¨       Data warehouses are designed for a different purpose:

§         to support ad hoc data analysis, inquiry and reporting by end users, without programmers, interactively and online

§         some of those very database design approaches that yield improved OLTP performance get right in the way of easy access and reporting:

§         the data comes from multiple tables that have to be joined correctly, and that does not make much business sense

§         many values are encoded and have to be looked up in other tables


§         A data warehouse is not a reporting tool, although point and click reporting tools can be used very effectively with data warehouses.

§         With appropriate client tools and training, users can navigate a data warehouse in a business context, rather than a transaction context, and run their own reports, even create their own reports and queries.

§         Examples:


¨       This is called OLAP:  OnLine Analytical Processing


1. What is a Data Warehouse?                                              3 of 3


¨       Mostly for performance reasons, a data warehouse is:


§         held in a separate database from the operational database,


§         and usually on a separate machine.


¨       Actually, navigation, ease of use, and relationship with business areas are more common and more powerful reasons for why than performance alone.


Presentation Outline - 2


1.      What is a Data Warehouse?

2.      Why do we need it?

3.      How do we get it?

4.      Data Warehouse Elements

5.      Demonstration of a Business Intelligence Tool


2. Why do we need it?                                                          1 of 10


¨       Has a business subject area orientation


§         Retention analysis

§         Who doesn't want to find ways of improving retention?  It is not enough to be very successful at merely attracting students, you want to maximize the proportion of those who stay to graduation.  And "lifelong learning" means you want to keep them interested in further education beyond graduation.

§         But what are the factors that might influence retention?  There are obviously a whole range of academic factors, but financial factors could be very important, so this analysis spans functional areas.  Demographic data and responses to interview questions will also yield useful insights.


§         Dean’s / Chair’s management statistics


§         Student Aid tracking / analysis


§         Student achievement / outcomes


2. Why do we need it? (continued)                                    2 of 10


The material in the next couple of sections has been pulled together from a variety of sources and experiences.  Some Data Warehousing Institute publications played a major role here.


¨       Integrates data from multiple, diverse sources


§         Banner Oracle database


§         legacy systems


§         purchased demographic data


§         manually collected survey data, etc


2. Why do we need it? (continued)                                    3 of 10


¨       Allows for analysis of data over time


§         registration statistics, through registration milestones across years


§         cohort analysis for retention


§         revenue and expense comparisons over time


2. Why do we need it? (continued)                                    4 of 10


¨       Adds ad hoc reporting and inquiry


§         data organized by business subject area makes navigation easier, more intuitive for business users


§         ‘point and click’ reporting without programmers

§         This kind of activity is vital in providing tactical support, and because of its ease of use, even operational needs can be met this way.


2. Why do we need it? (continued)                                    5 of 10


¨       Provides analysis capabilities to decision makers


§         interactive slice and dice, drill down, drill up, etc


§         “what if” capabilities


§         graphical data visualization

§         This helps your knowledge workers and data analysts look for ways of bringing about strategic change.  This can help you to gain and sustain competitive advantage.


2. Why do we need it? (continued)                                    6 of 10


¨       Relieves the development burden on IT


§         end-user reporting tools mean IT does not have to write programs to answer simple inquiries


§         questions are answered more readily, information is put to better use in support of decision making

§         The reduced backlog that results makes for happier customers.  Programmer/analysts are able to concentrate on adding value by continuing to evolve the data models in the data warehouse to meet changing needs.  Happy, satisfied customers who love does wonders for staff morale and job satisfaction.


2. Why do we need it? (continued)                                    7 of 10


¨       Provides improved performance for complex analytical queries


§         de-normalized star schemas used in data warehouses are better designed for analytical queries than databases designed for OLTP


2. Why do we need it? (continued)                                    8 of 10


¨       Relieves processing burden on transaction oriented databases


§         use a specially designed data warehouse, preferably on a separate machine


§         this isolates production processing from the impact of large, inefficient analytical queries


2. Why do we need it? (continued)                                    9 of 10


¨       Allows for a continuous planning process


§         online analysis is available at any time


§         its interactive nature means different questions can be asked immediately, without reprogramming


§         there is no need to wait in the development queue, or even in the report production queue


2. Why do we need it? (continued)                                 10 of 10


¨       Converts corporate data into strategic information


§         improved decision support results in more timely detection of favorable and unfavorable trends


§         favorable trends can be capitalized on


§         early corrective action can be taken for unfavorable trends


Presentation Outline - 3


1.      What is a Data Warehouse?

2.      Why do we need it?

3.      How do we get it?

4.      Data Warehouse Elements

5.      Demonstration of a Business Intelligence Tool


3. How do we get it? (continued)                                         1 of 4


In the next 4 slides, I have sifted out 12 key factors from my reading and experience.


1.      Be Ready for the Data Warehouse
Develop an understanding amongst senior administrators of the potential role of IT and data warehousing in achieving the institution's goals.

§         There are actually 2 almost opposite approaches that are advocated here.  One is top-down, as I am describing here.  Another says that this kind of top-down support is typically difficult to obtain in a higher education environment.  It depends on the inherent value of the concept itself to attract support.  It uses the "if we build it, they will come" approach.


2.      Choose The Right Project Team

§         Set up a Steering Committee or project team

§         Kinds of people to include, diverse cross-section, minimal IT

§         Kinds of people to exclude - stories


3.      Have a Training Strategy
Take appropriate training, and/or hire selected consultants.


3. How do we get it? (continued)                                         2 of 4


4.      Choose the Right Architecture
Start small, using a phased approach, but within the framework of a system-wide architecture.

§         Ralph Kimball has a concept he calls the "Data Warehouse Bus".  I heard him present it in a keynote address at TDWI conference in Chicago last July.  I believe he has published it in a book now,

§         Describe it.


5.      Have a Project “Mission Statement”
Feasibility study - added value, but ROI is difficult to show
Project Charter - you need to know what you are trying to achieve
Project Plan - you need to know how to are going to achieve what you want to do


3. How do we get it? (continued)                                         3 of 4


6.      Show Early Business Benefits
Choose strategically important subject areas, (i.e. areas that are linked to the Strategic Plan), that have high visibility and fast return. (remember the 80-20 rule).

§         Look for areas that meet the largest needs with the least effort.

§         This somewhat related to #1 "Be Ready for the DW".  If you have been able to take the top-down approach, then you will have developed some pent up pressure for implementing in some areas that are screaming to be delivered via your DW.


7.      Ensure Scalability
Evolve the data marts iteratively, constructing the architected data warehouse as you go.

§         You are not going to design the whole data warehouse before you start your first data mart, but you will design an architecture, using Ralph Kimball's Bus analogy.


8.      Understand the Importance of Data Quality


3. How do we get it? (continued)                                         4 of 4


9.      Be Wary Of Vendor Claims
Choose the data repository, data warehousing tools, and desktop tools with care.

§         A data warehouse is NOT a product.  You do not just buy it off the shelf and install it, or even customize it to fit.  It is true that there are many tools you can buy to help you build a data warehouse, but these are only the tools.

§         This technology is moving from early adopters to mainstream, and while the tools are maturing rapidly, there are still many glaring holes in the technology.  Integrated metadata is probably still the largest missing piece.


10.  Use a Proven Data Warehouse Methodology


11.  Define and Manage Data Ownership Issues


12.  Don’t underestimate the Difficulty of Implementing Change

§         It is natural for humans to resist change.  But data warehousing is a very important piece of the process redesign, workflow model we are moving toward.  Total Quality Improvement (TQI) is all about process improvement. 

§         How can you know you have improved something if you have not identified and been measuring key performance indicators?  This is what data warehousing is.  These indicators are called measures and are held in "fact tables".  The factors by which you may want to analyze these indicators are called dimensions in a multidimensional database and are stored in the dimension tables of your data warehouse.

§         TQI assumes 2 things:

§         you have identified indicators and are measuring them

§         you are making changes that you predict will lead to improvement

§         WOW!  Improvement is change for the better.  No change means no improvement.

§         If there is a serious commitment to improvement, it implies change.  Data warehousing can be a powerful enabler for this kind of change, because it supports the initial decision making, and it provides support for the review and follow-up.


Presentation Outline - 4


1.      What is a Data Warehouse?

2.      Why do we need it?

3.      How do we get it?

4.      Data Warehouse Elements

5.      Demonstration of a Business Intelligence Tool


4. Data Warehouse Elements                                                1 of 2


1.      Conferences

§         TDWI

§         DCI


2.      Consultants


3.      Methodologies

§         Mostly as part of a consulting package, although you can use books and attend training and do it yourself


4.      Design Tools

§         CASE tools need to be DW aware

§         They need to be able to generate the DDL for your data warehouse

§         They need to be integrated with your metadata (but probably won't be)


5.      Metadata Repositories


6.      Databases


4. Data Warehouse Elements (continued)                          2 of 2


7.      ETL – Extract/Transform/Load, including cleanse and schedule


8.      Ad  hoc queries, reports

§         Here we are getting to the client tools.  These ones are for tactical support mostly.

§         You will almost certainly want to provide web interfaces to your DW


9.      OLAP/Multidimensional data analysis, decision support

§         Your strategic support will come mostly from here.


10.  Data mining/Statistics

§         High powered statistical analysis the assists you in finding relationships in your data.


11.  Decision Analysis

§         Analyzes the decisions you made in #9!


Presentation Outline - 5


1.      What is a Data Warehouse?

2.      Why do we need it?

3.      How do we get it?

4.      Data Warehouse Elements

5.      Demonstration of a Business Intelligence Tool


5. Demonstration of a BI Tool


¨       This brief demonstration uses PowerPlay, a business intelligence tool from Cognos


¨       Dimensions and facts relevant to Deans and Chairs, and Retention Analysis


¨       Shows how interactive data analysis suggests additional questions, answers the “why” questions




¨       Some keys to successful data warehousing:

§         highly visible and valuable

¨       Using a proven methodology, with an architected approach

§         have a plan

§         start small, evolve iteratively


Questions and answers




Contact Details


¨       Contact Details:


§         David Heise, CIO, Andrews University





¨       Andrews University Data Warehousing




¨       This presentation:


§ (on local server)




¨       Data Warehousing Buyer’s Guide - TDWI

§         This a comprehensive listing of vendors in all the segments of the data warehousing business


¨       Larry Greenfield

§         (used to be:


¨       The Data Warehousing Institute (TWDI) web page



¨       Data warehousing listserv dwlist


§         This is a very professional list, mostly.  It is quite busy.  You can sign up at the website.


¨       Books, publications, trade journals

§         TDWI and others publish book lists - See Larry Greenfield

§         DM Review, Intelligent Enterprise - See Larry Greenfield


¨       Training

§         Vendors and consultants (of course)

§         TDWI

§         DCI


¨       I would like to thank you again for being here and for your participation.

¨       If you have not already done so, please complete the session evaluation form : RP17