Appendices


Contents:
Appendix A: OLAP and OLAP Server Definitions
Appendix B: Data Warehouse Terminology
Appendix C: OLAP Product Evaluation Rules
Appendix D: Case Studies and Action Research
Appendix E: Data Dictionary: Files & Elements (selected entries)
Appendix F: Data Warehouse Schema Definition
Appendix G: Data Dictionary: Elements Alphabetically (selected entries)
Appendix H: Review Questionnaire


Appendix A: OLAP and OLAP SERVER DEFINITIONS

The OLAP Council Glossary


Appendix B: Data Warehouse Terminology

Definitions from Creative Data, Inc.


Appendix C: OLAP Product Evaluation Rules

This Appendix is reproduced from "Providing OLAP (On-line Analytical Processing) to User-Analysts: An IT Mandate", a paper by E.F. Codd, S.B. Codd and C.T. Salley(41)

The twelve rules for evaluating OLAP products are:

  1. Multi-Dimensional Conceptual View
  2. Transparency
  3. Accessibility
  4. Consistent Reporting Performance
  5. Client-Server Architecture
  6. Generic Dimensionality
  7. Dynamic Sparse Matrix Handling
  8. Multi-User Support
  9. Unrestricted Cross-dimensional Operations
  10. Intuitive Data Manipulation
  11. Flexible Reporting
  12. Unlimited Dimensions and Aggregation Levels

1. Multi-Dimensional Conceptual View
A user-analyst's view of the enterprise's universe is multi-dimensional in nature. Accordingly, the user-analyst's conceptual view of OLAP models should be multi-dimensional in nature. This multi-dimensional conceptual schema or user view facilitates model design and analysis, as well as inter and intra dimensional calculations through a more intuitive analytical model. Accordingly user-analysts are able to manipulate such multi-dimensional data models more easily and intuitively than is the case with single dimensional models. For instance, the need to "slice and dice," or pivot and rotate consolidation paths within a model is common. Multi-dimensional models make these manipulations easily, whereas achieving a like result with older approaches requires significantly more time and effort.

2. Transparency
Whether OLAP is or is not part of the user's customary front-end (e. g., spreadsheet or graphics package) product, that fact should be transparent to the user. If OLAP is provided within the context of a client-server architecture, then this fact should be transparent to the user-analyst as well. OLAP should be provided within the context of a true open systems architecture, allowing the analytical tool to be embedded anywhere the user-analyst desires, without adversely impacting the functionality of the host tool.

Transparency is crucial to preserving the user's existing productivity and proficiency with the customary front-end, providing the appropriate level of function, and assuring that needless complexity is in no way introduced or otherwise increased.

Additionally, it should be transparent to the user as to whether or not the enterprise data input to the OLAP tool comes from a homogeneous or heterogeneous database environment.

3. Accessibility
The OLAP user-analyst must be able to perform analysis based upon a common conceptual schema composed of enterprise data in relational DBMS, as well as data under control of the old legacy DBMS, access methods, and other non-relational data stores at the same time as the basis of a common analytical model. That is to say that the OLAP tool must map its own logical schema to heterogeneous physical data stores, access the data, and perform any conversions necessary to present a single, coherent and consistent user view. Moreover, the tool and not the end-user analyst must be concerned about where or from which type of systems the physical data is actually coming. The OLAP system should access only the data actually required to perform the indicated analysis and not take the common "kitchen sink" approach which brings in unnecessary input.

4. Consistent Reporting Performance
As the number of dimensions or the size of the database increases, the OLAP user-analyst should not perceive any significant degradation in reporting performance. Consistent reporting performance is critical to maintaining the ease-of-use and lack of complexity required in bringing OLAP to the end-user.

If the user-analyst were able to perceive any significant difference in reporting performance relating to the number of dimensions requested, there would very likely be compensating strategies developed, such as asking for information to be presented in ways other than those really desired. Spending one's time in devising ways of circumventing the system in order to compensate for its inadequacies is not what end-user products are about.

5. Client-Server Architecture
Most data currently requiring on-line analytical processing is stored on mainframe systems and accessed via personal computers. It is therefore mandatory that the OLAP products be capable of operating in a client-server environment. To this end, it is imperative that the server component of OLAP tools be sufficiently intelligent such that various clients can be attached with minimum effort and integration programming.

The intelligent server must be capable of performing the mapping and consolidation between disparate logical and physical enterprise database schema necessary to effect transparency and to build a common conceptual, logical and physical schema.

6. Generic Dimensionality
Every data dimension must be equivalent in both its structure and operational capabilities. Additional operational capabilities may be granted to selected dimensions, but since dimensions are symmetric, a given additional function may be granted to any dimension. The basic data structure, formulae, and reporting formats should not be biased toward any one data dimension.

7. Dynamic Sparse Matrix Handling
The OLAP tools' physical schema must adapt fully to the specific analytical model being created to provide optimal sparse matrix handling. For any given sparse matrix, there exists one and only one optimum physical schema. This optimal schema provides both maximum memory efficiency and matrix operability unless of course, the entire data set can be cached in memory. The OLAP tool's basic physical data unit must be configurable to any subset of the available dimensions, in any order, for practical operations within large analytical models. The physical access methods must also be dynamically changeable and should contain different types of mechanisms such as:

  1. direct calculation;
  2. B-trees and derivatives,
  3. hashing;
  4. the ability to combine these techniques where advantageous.

Sparseness (missing cells as a percentage of possible cells) is but one of the characteristics of data distribution. The inability to adjust (morph) to the data set's data distribution can make fast, efficient operation unobtainable. If the OLAP tool cannot adjust according to the distribution of values of the data to be analyzed, models which appear to be practical, based upon the number of consolidation paths and dimensions, or the size of the enterprise source data, may be needlessly large and/or hopelessly slow in actuality. Access speed should be consistent regardless of the order of cell access and should remain fairly constant across models containing different numbers of data dimensions or varying sizes of data sets.

For example, given a set of input data from the enterprise database which is perfectly dense (every possible input combination contains a value, no nulls), it is possible to predict the size of the resulting data set after consolidation across all modeled data dimensions.

For example, in a particular five-dimensional analytical model, let us suppose that the physical schema size after model consolidation is two-and-one-half times the size of the input data from the enterprise database.

However, if the enterprise data is sparse, and has certain distribution characteristics, then the resulting physical schema might be one-hundred times the size of the enterprise data input. But, given the same size data set, and the same degree of sparseness, but with different data distribution, the size of the resulting physical schema might be only two and-one-half times the size of the enterprise data input as in the case of the perfectly dense example. Or, we could experience anything in between these two extremes. "Eyeballing" the data in an attempt to form an educated guess is as hopeless as is using conventional statistical analysis tools to obtain crosstabs of the data.

Because conventional statistical analysis tools always compare only one dimension against one other dimension, without regard for the other, perhaps numerous, data dimensions, they are unsuitable to multi-dimensional data analysis. Even if such tools could compare all dimensions at once (which they can't), the resulting crosstab would be the size of the product of all the data dimensions, which would be the maximum size of the physical schema itself.

By adapting its physical data schema to the specific analytical model, OLAP tools can empower user-analysts to easily perform types of analysis which previously have been avoided because of their perceived complexity. The extreme unpredictability and volatility in the behavior of multi-dimensional data models precludes the successful use of tools which rely upon a static physical schema and whose basic unit of data storage has fixed dimensionality (e.g., cell, record or two-dimensional sheet). A fixed, physical schema which is optimal for one analytical model, will typically be impractical for most others. Rather than basing a physical schema upon cells, records, two dimensional sheets, or some other similar structure, OLAP tools must dynamically adapt the model's physical schema to the indicated dimensionality and especially to the data distribution of each specific model.

8. Multi-User Support
Oftentimes, several user-analyst's have a requirement to work concurrently with either the same analytical model or to create different models from the same enterprise data. To be regarded as strategic, OLAP tools must provide concurrent access (retrieval and update), integrity, and security.

9. Unrestricted Cross-Dimensional Operations
The various roll-up levels within consolidation paths, due to their inherent hierarchical nature, represent in outline form, the majority of 1:1, 1:M, and dependent relationships in an OLAP model or application. Accordingly, the tool itself should infer the associated calculations and not require the user-analyst to explicitly define these inherent calculations. Calculations not resulting from these inherent relationships require the definition of various formulae according to some language which of course must be computationally complete.

Such a language must allow calculation and data manipulation across any number of data dimensions and must not restrict or inhibit any relationship between data cells regardless of the number of common data attributes each cell contains.

For example, consider the difference between a single dimensional calculation and a cross-dimensional calculation. The single dimensional calculation: Contribution = Revenue - Variable Cost defines a relationship between attributes in only one data dimension, which we shall call D_ACCOUNTS. Upon calculation, what occurs is that the relationship is calculated for all cells of all data dimensions in the data model which possess the attribute Contribution

A cross-dimensional relationship and the associated calculations provide additional challenges. For example, given the following simple five-dimensional outline:

D(42)_Accounts
	Sales
	Overhead
	InterestRate
	et cetera
D_Corporate
	United Kingdom
		London
		York
		et cetera
	France
		Paris
		Cannes
		et cetera
D_FiscalYear
	Quarter1
		January
		February
		March
	Quarter2
		April
		May
		June
		et cetera
D_Products
	Audio
	Video
	et cetera
D_Scenario
	Budgeted
	Actual
	Variance
	et cetera
Sample Five-Dimensional Outline Structure

The formula to allocate corporate overhead to parts of the organization such as local offices (Paris, Cannes, et cetera) based upon their respective contributions to overall company sales might appear thus:

Overhead equals the percentage of total sales represented by the sales of each individual local office multiplied by total corporate overhead

Here is another example of necessary cross-dimensional calculations. Suppose that the user-analyst desires to specify that for all French cities, the variable InterestRate which is used in subsequent calculations, should be set to the value of the BUDGETED MARCH INTERESTRATE for the city of Paris for all months, across all data dimensions. Had the user-analyst not specified the city, month and scenario, the attributes would alter and stay consistent with the month attributes of the data cell being calculated when the analytical model is animated. The described calculation could be expressed as :

If the value within the designated cell appears within the consolidation path D_Corporate, beneath the consolidation level France, then the global interest rate becomes the value of the interest rate for the month of March which is budgeted for the city of Paris

10. Intuitive Data Manipulation
Consolidation path re-orientation, drilling down across columns or rows, zooming out, and other manipulation inherent in the consolidation path outlines should be accomplished via direct action upon the cells of the analytical model, and should neither require the use of a menu nor multiple trips across the user interface. The user-analyst's view of the dimensions defined in the analytical model should contain all information necessary to effect these inherent actions.

11. Flexible Reporting
Analysis and presentation of data is simpler when rows, columns, and cells of data which are to be visually compared are arranged in proximity or by some logical grouping occur ring naturally in the enterprise. Reporting must be capable of presenting data to be synthesized, or information resulting from animation of the data model according to any possible orientation. This means that the rows, columns, or page headings must each be capable of containing/displaying from 0 to N dimensions each, where N is the number of dimensions in the entire analytical model.

Additionally, each dimension contained/displayed in one of these rows, columns, or page headings must itself be capable of containing/displaying any subset of the members, in any order, and provide a means of showing the inter-consolidation path relationships between the members of that subset such as indentation.

12. Unlimited Dimensions and Aggregation Levels
Research into the number of dimensions possibly required by analytical models indicates that as many as nineteen concurrent data dimensions (this was an actuarial model) may be needed. Thus the strong recommendation that any serious OLAP tool should be able to accommodate at least fifteen and preferably twenty data dimensions within a common analytical model.

Furthermore, each of these generic dimensions must allow an essentially unlimited number of user-analyst defined aggregation levels within any given consolidation path.


Appendix D: Case Studies and Action Research

This data warehousing project was conducted in the form of a case study, and many of the methods of action inquiry or research are applicable. A statement from the Action Inquiry Network (ActNet) makes the point that "the action inquiry model focuses primarily on identifying and resolving difficult, complex, real-life problems critical to organizations and society. This includes the formidable challenges of leadership, innovation, informed participation, and prejudice."(43)

Although the action research model is applied more commonly in the fields of education (teaching and learning), health services, social sciences and economics, this statement is a very close fit for the issues that are confronted when developing a data warehouse. The success of the project is very dependent on the support and input of the senior leadership. Their informed participation is crucial. The project is primarily for the purpose of improving the decision making process. There also needs to be a willing open-mindedness to consider and suggest new ways of doing things, including organizational and social changes. An attitude of pre-judgment and prejudice is almost certain to deprive the organization of some of the best ideas for improvement.

"A crucial issue in action inquiry is whether a problem is routine or non-routine, trivial or difficult. The difference between routine/trivial and non-routine/difficult problems is, admittedly, not always easy to discern. Nevertheless, the action inquiry model focuses primarily on identifying and resolving difficult, complex, real-life problems critical to organizations and society. This includes the formidable challenges of leadership, innovation, informed participation, and prejudice. Bewildering problems often develop when members of a group try to formulate and carry out a new strategy and evaluate their work together.

"Effective groups can resolve difficult problems by taking innovative action relatively soon. As the participants question their underlying programs or the credibility of ideas, they maintain high levels of interpersonal openness. They accept that while openness is actually or potentially embarrassing, threatening, or frustrating, it is simultaneously necessary to increase trust and individuality in their group. They may deny these difficulties but rarely do such instances not get noticed, challenged, and corrected. As they improve action inquiry skills, their minimally defensive interpersonal and group relations enable them to innovate and become productive."(44)


Appendix E: Data Dictionary - Files & Elements (selected entries)

CI_ADDRESS
Addresses held as part of Common Information about persons

Item               Type Size             Description
name_number           C   6  CHAR     6  Computerised record number assigned to this person
address_number        C   1  CHAR     1  Address number of this address record
date_address_modified D   8  ZONED-U  8  Date this address was modified
good_address          C   1  CHAR     1  Indicates the validity of this address
address1              C  30  CHAR    30  1st line of address, eg number and street
address2              C  30  CHAR    30  Line 2 of address (optional)
town                  C  30  CHAR    30  Town or suburb
state                 C  10  CHAR    10  State part of address
postcode              C   7  CHAR     7  Post code
country               C  20  CHAR    20  Country part of address
phone                 C  15  CHAR    15  Telephone number part of person address

    PRIMARY KEY:   address_key   : name_number
                                 : address_number

SR_STUDENT_SUBJECT
Permanent record of student subjects taken, with results

Item               Type Size             Description
name_number           C   6  CHAR     6  Computerised record number assigned to this person
year_yyyy             C   4  CHAR     4  Year (YYYY) for this record
semester              C   1  CHAR     1  E353: Semester in which the load occurs code
subject_code          C   5  CHAR     5  Alphanumeric code assigned to this subject
attendance_status     C   1  CHAR     1  Type of attendance in this class - eg, Auditing, Normal
date_started          C   4  CHAR     4  Date (DD/MM) student commenced this subject
completion_status     C   1  CHAR     1  Final status of this subject for this student
date_sub_terminated   C   4  CHAR     4  Date (DD/MM) student quit doing this subject
grade                 C   3  CHAR     3  Grade assigned to a subject
unit_status           C   1  CHAR     1  E355: Unit of study completion status
checksheet_category   C   2  CHAR     2  Checksheet category to assign this advanced standing to
checksheet_subject    C   5  CHAR     5  Subject code being granted advanced standing status

    PRIMARY KEY:   subject_key   : name_number
                                 : year_yyyy
                                 : semester
                                 : subject_code

Appendix F: Data Warehouse Schema Definition

create domain YEAR_DOM char(4);
comment on domain YEAR_DOM is 'standard definition for year with century';

 CREATE TABLE DW_STUDENT
 (
    ref_year                YEAR_DOM,
    course_code             CHAR(5),
    student_id              CHAR(10),
    semester                CHAR(1),
    aou                     CHAR(3),
    course_name_abbrev      CHAR(30),
    graduating              CHAR(1),
    fos                     CHAR(6),
    date_of_birth           DATE,
    age_in_ref_year         INTEGER,
    sex                     CHAR(1),
    aborig_torres           CHAR(1),
    citizen_resident        CHAR(1),
    term_location           CHAR(4),
    home_location           CHAR(4),
    admission               CHAR(2),
    commencement_date       DATE,
    attendance_type         CHAR(1),
    country_birth           CHAR(4),
    language_home           CHAR(2),
    tert_ent_score          CHAR(3),
    total_eftsu_half        INTEGER,
    hecs_prexmt_tot         INTEGER,
    hecs_exmt_status        CHAR(2),
    hecs_amt_paid           INTEGER,

    PRIMARY KEY ( ref_year, student_id, course_code, semester ) deferrable);
 CREATE UNIQUE INDEX PKDW_STUDENT ON DW_STUDENT
    ( ref_year ASC, student_id ASC, course_code ASC, semester ASC );

 CREATE TABLE DW_SUBJECT
 (
    ref_year                YEAR_DOM,
    semester                CHAR(1),
    student_id              CHAR(10),
    course_code             CHAR(5),
    unit_study              CHAR(10),
    aou                     CHAR(3),
    discipline_group        CHAR(4),
    eftsu                   INTEGER,
    unit_status             CHAR(1),
     
    PRIMARY KEY ( ref_year, semester, student_id, course_code, unit_study ) deferrable);
 CREATE UNIQUE INDEX PKDW_SUBJECT ON DW_SUBJECT
    ( ref_year ASC, semester ASC, student_id ASC, course_code ASC, unit_study ASC );

 CREATE TABLE DW_PP_COHORT
 (
    student_id              CHAR(10),
    course_code             CHAR(5),
    analysis_year           YEAR_DOM,
    semester                CHAR(1),
    course_name_abbrev      CHAR(30),
    cohort_year             YEAR_DOM,
    age_in_anal_year        INTEGER,
    age_group               CHAR(7),
    sex                     CHAR(1),
    aou                     CHAR(3),
    course_type_name        CHAR(10),
    course_status           CHAR(10),
    spu_annual              INTEGER,
    spu_cumulative          INTEGER,
    headcount               INTEGER,
    eftsu                   INTEGER,

    PRIMARY KEY (course_code, student_id, analysis_year, semester ) deferrable);
CREATE UNIQUE INDEX PKDW_PP_ANALYSIS ON DW_PP_COHORT
  (course_code, student_id, analysis_year, semester );

Appendix G: Data Dictionary: Elements Alphabetically (selected entries)


Item                 Type Size         Description                                              Location
aborig_torres          C   1  CHAR     E316: Aboriginal/Torres Strait Islander code             SR_STUDENT_PERSONAL
acad_award             C   1  CHAR     Academic Excellence Award given on graduating            SR_STUDENT_COURSE
acad_bridging_course   C   1  CHAR     Bridging course required for academic reasons            SR_STUDENT_PERSONAL_SEM
acad_prob_code         N   1  ZONED-U  Reason for granting Academic Probation Status            SR_STUDENT_COURSE
acceptance_status      N   1  ZONED-U  Stage of acceptance or otherwise                         SR_STUDENT_PERSONAL_SEM
accredited             C   1  CHAR     Is the course accredited?                                SR_CODE_COURSE_MASTER
address_number         C   1  CHAR     Address number of this address record                    CI_ADDRESS
address1               C  30  CHAR     1st line of address, eg number and street                CI_ADDRESS
address2               C  30  CHAR     Line 2 of address (optional)                             CI_ADDRESS
admission              C   2  CHAR     E327: Basis for admission to current course              SR_STUDENT_PERSONAL
advanced_standing      C   1  CHAR     Is advanced standing being sought?                       SR_STUDENT_PERSONAL
aou                    C   3  CHAR     E333: Academic organisational unit code                  SR_CODE_COURSE_MASTER
application_status     N   1  ZONED-U  Application and supplementary form received, etc         SR_STUDENT_PERSONAL_SEM
application_type       N   1  ZONED-U  1=New student, 2=Returning, 3=Returning after absence    SR_STUDENT_PERSONAL
attendance_status      C   1  CHAR     Type of attendance in this class - eg, Auditing, Normal  SR_STUDENT_SUBJECT
attendance_type        N   1  ZONED-U  Full-time or Part-time                                   SR_STUDENT_COURSE
baptised               C   1  CHAR     B=Baptised SDA, U=Unbaptised SDA, O=Other                SR_STUDENT_PERSONAL
bridging_course        C   1  CHAR     Y=taking chemistry bridging course                       SR_STUDENT_PERSONAL_SEM
bursary_amt            N   1  ZONED-U  Amount of bursary granted to this student                SR_STUDENT_PERSONAL_SEM
bursary_provider       C  20  CHAR     Name of provider of this student bursary                 SR_STUDENT_PERSONAL_SEM
campus                 C   1  CHAR     C=Cooranbong, W=Wahroonga                                SR_STUDENT_PERSONAL_SEM
campus_taught          C   1  CHAR     C=Cooranbong, W=Wahroonga                                SR_CODE_SUBJECT
cert_credit            N   1  ZONED-U  Total certificate credit points taken                    SR_STUDENT_PERSONAL_SEM
checksheet_category    C   2  CHAR     Checksheet category to assign this advanced standing to  SR_STUDENT_SUBJECT
checksheet_subject     C   5  CHAR     Subject code being granted advanced standing status      SR_STUDENT_SUBJECT
citizen_resident       C   1  CHAR     E358: Citizen/resident indicator                         SR_STUDENT_PERSONAL
college_sship          C   1  CHAR     Y=college scholarship available                          SR_STUDENT_PERSONAL_SEM
completion_status      C   1  CHAR     Final status of this subject for this student            SR_STUDENT_SUBJECT
conference             C   5  CHAR     SDA conference of this person's home church              SR_STUDENT_PERSONAL
contact_address_code   N   1  ZONED-U  Address code holding this person's contact address       SR_STUDENT_PERSONAL
country                C  20  CHAR     Country part of address                                  CI_ADDRESS
country_birth          C   4  CHAR     E346: Country of birth code                              SR_STUDENT_PERSONAL
course_code            C   5  CHAR     Course code                                              SR_CODE_COURSE_MASTER
course_code            C   5  CHAR     Course code                                              SR_STUDENT_COURSE
course_load            N   2  ZONED-U  E350: Course load                                        SR_CODE_COURSE_MASTER
course_name            C  50  CHAR     Course name in full for Avondale                         SR_CODE_COURSE_MASTER
course_name_abbrev     C  30  CHAR     E309: Course name - abbreviated                          SR_CODE_COURSE_MASTER
course_name_full       C  72  CHAR     E308: Course name - full                                 SR_CODE_COURSE_MASTER
course_record_number   C   3  CHAR     Pointer for course record                                SR_STUDENT_COURSE
course_record_number   C   3  CHAR     Pointer to semester 1 course record                      SR_STUDENT_PERSONAL_SEM
course_type            N   2  ZONED-U  E310: Course type code                                   SR_CODE_COURSE_MASTER
credit_lev1_sem1       N   3  ZONED-U  Standard course credit points for level 1, semester 1    SR_CODE_COURSE_MASTER
credit_lev1_sem2       N   3  ZONED-U  Standard course credit points for level 1, semester 2    SR_CODE_COURSE_MASTER
credit_lev2_sem1       N   3  ZONED-U  Standard course credit points for level 2, semester 1    SR_CODE_COURSE_MASTER
credit_lev2_sem2       N   3  ZONED-U  Standard course credit points for level 2, semester 2    SR_CODE_COURSE_MASTER
credit_lev3_sem1       N   3  ZONED-U  Standard course credit points for level 3, semester 1    SR_CODE_COURSE_MASTER
credit_lev3_sem2       N   3  ZONED-U  Standard course credit points for level 3, semester 2    SR_CODE_COURSE_MASTER
credit_lev4_sem1       N   3  ZONED-U  Standard course credit points for level 4, semester 1    SR_CODE_COURSE_MASTER
credit_lev4_sem2       N   3  ZONED-U  Standard course credit points for level 4, semester 2    SR_CODE_COURSE_MASTER
current_level          N   1  ZONED-U  Current level in course                                  SR_STUDENT_COURSE
current_semester       N   1  ZONED-U  Current semester in course                               SR_STUDENT_COURSE
currently_offered      C   1  CHAR     Y=course is currently offered                            SR_CODE_COURSE_MASTER
date_acad_prob         C   6  CHAR     Date that academic probation status was given            SR_STUDENT_COURSE
date_address_modified  D   8  ZONED-U  Date this address was modified                           CI_ADDRESS
date_applic_rcvd       C   6  CHAR     Date application was received                            SR_STUDENT_COURSE
date_commenced         C   6  CHAR     Date this student first commenced this course            SR_STUDENT_COURSE
date_full_accept       C   6  CHAR     Date full acceptance was given                           SR_STUDENT_COURSE
date_prov_accept       C   6  CHAR     Date provisional acceptance was given                    SR_STUDENT_COURSE
date_recommenced       C   6  CHAR     Date this student recommenced this course                SR_STUDENT_COURSE
date_registered        C   6  CHAR     Date this student registered                             SR_STUDENT_COURSE
date_started           C   4  CHAR     Date (DD/MM) student commenced this subject              SR_STUDENT_SUBJECT
date_sub_terminated    C   4  CHAR     Date (DD/MM) student quit doing this subject             SR_STUDENT_SUBJECT
date_suppl_rcvd        C   6  CHAR     Date supplimentary form was received                     SR_STUDENT_COURSE
date_terminated        C   6  CHAR     Date this student ceased being enrolled in this course   SR_STUDENT_COURSE
department             N   3  INTEGER  Avondale department code                                 SR_CODE_SUBJECT
dependent_disc_flag    C   1  CHAR     Override flag for denominational employee discount       SR_STUDENT_PERSONAL_SEM
dip_coor_credit        N   1  ZONED-U  Credit points for Cooranbong degree subjects             SR_STUDENT_PERSONAL_SEM
dip_wah_credit         N   1  ZONED-U  Credit points for Wahroonga degree subjects              SR_STUDENT_PERSONAL_SEM
disability             C   8  CHAR     E386: Indicates responses to 3 questions about disabilities SR_STUDENT_PERSONAL
discipline_group       C   4  CHAR     E336: Discipline group code                              SR_CODE_SUBJECT
dorm_room_code         C   4  CHAR     Room code of residence hall room allocated to this student SR_STUDENT_PERSONAL
edu_type               C   1  CHAR     P=Primary, S=Secondary, N=Non-teaching                    SR_CODE_COURSE_MASTER
eftsu                  N   1  ZONED-U  Equivalent full-time student unit                         SR_STUDENT_PERSONAL_SEM
equivalent_subject     C   5  CHAR     Other name for this subject (Same as subject code if none) SR_CODE_SUBJECT
fee_student            C   1  CHAR     E349: Fee paying student indicator                        SR_STUDENT_PERSONAL
fees_address_code      N   1  ZONED-U  Address code of address record containing fees address    SR_STUDENT_PERSONAL
fees_business_phone    C  15  CHAR     Fee payer's business phone number                         SR_STUDENT_PERSONAL
fees_initials          C   5  CHAR     Fee payer's initials                                      SR_STUDENT_PERSONAL
fees_relationship      C   1  CHAR     Relationship of fee payer to student                      SR_STUDENT_PERSONAL
fees_surname           C  20  CHAR     Surname of fee payer                                      SR_STUDENT_PERSONAL
fees_title             C   4  CHAR     Title of fee payer                                        SR_STUDENT_PERSONAL
fin_status             C   1  CHAR     Financial status                                          SR_STUDENT_PERSONAL_SEM
first_aid_cert         C   1  CHAR     Indicates if student has a first aid certificate          SR_STUDENT_PERSONAL
fos                    C   6  CHAR     E311: Field of study code                                 SR_CODE_COURSE_MASTER
good_address           C   1  CHAR     Indicates the validity of this address                    CI_ADDRESS
govt_funded            C   1  CHAR     Indicates if this is a Government funded course           SR_CODE_COURSE_MASTER
grade                  C   3  CHAR     Grade assigned to a subject                               SR_STUDENT_SUBJECT
graduating             C   1  CHAR     Y=Student graduates in this year                          SR_STUDENT_COURSE
handbook_year          C   4  CHAR     This student course is based on the Handbook for this year SR_STUDENT_COURSE
hecs_amt_paid          N   1  ZONED-U  E381: HECS amount paid - semester                         SR_STUDENT_PERSONAL_SEM
hecs_exmt_status       C   2  CHAR     E380: HECS exemption status                               SR_STUDENT_PERSONAL_SEM
hecs_prexmt_tot        N   1  ZONED-U  E382: HECS amount prior to exemption - total              SR_STUDENT_PERSONAL_SEM
home_location          C   4  CHAR     E320: Location code of permanent home residence           SR_STUDENT_PERSONAL
in_absentia            C   1  CHAR     Graduated in absentia                                     SR_STUDENT_COURSE
instructed_english     C   1  CHAR     Prior education was given in English                      SR_STUDENT_PERSONAL
kin_address_code       N   1  ZONED-U  Address number of next of kin address                     SR_STUDENT_PERSONAL
kin_business_phone     C  15  CHAR     Telephone number for next of kin                          SR_STUDENT_PERSONAL
kin_given_names        C  30  CHAR     Full given names of next of kin                           SR_STUDENT_PERSONAL
kin_initials           C   5  CHAR     Initials of next of kin                                   SR_STUDENT_PERSONAL
kin_occupation         C  30  CHAR     Occupation of next of kin                                 SR_STUDENT_PERSONAL
kin_surname            C  20  CHAR     Surname of next of kin                                    SR_STUDENT_PERSONAL
kin_title              C   4  CHAR     Title of next of kin                                      SR_STUDENT_PERSONAL
language_home          C   2  CHAR     E348: Language spoken at home indicator                   SR_STUDENT_PERSONAL
late_application       C   1  CHAR     Application was late                                      SR_STUDENT_PERSONAL_SEM
late_registration      C   1  CHAR     Student registered late                                   SR_STUDENT_PERSONAL_SEM
ma_credit              N   1  ZONED-U  Total Masters credit points taken                         SR_STUDENT_PERSONAL_SEM
major                  C  10  CHAR     Standard code for major                                   SR_STUDENT_COURSE
major_code             C  10  CHAR     Standard code for major                                   SR_CODE_COURSE_MASTER
marital_status         C   1  CHAR     Marital status (single, married, etc)                     SR_STUDENT_PERSONAL_SEM
minor                  C  10  CHAR     Standard code for minor                                   SR_STUDENT_COURSE
misc_course            C   1  CHAR     This is a miscellaneous course                            SR_CODE_COURSE_MASTER
name_code              C   6  CHAR     First 4 letters of surname + first 2 letters of given name SR_STUDENT_PERSONAL
name_number            C   6  CHAR     Computerised record number assigned to this person        CI_ADDRESS
name_number            C   6  CHAR     Computerised record number assigned to this person        SR_STUDENT_COURSE
name_number            C   6  CHAR     Computerised record number assigned to this person        SR_STUDENT_PERSONAL
name_number            C   6  CHAR     Computerised record number assigned to this person        SR_STUDENT_PERSONAL_SEM
name_number            C   6  CHAR     Computerised record number assigned to this person        SR_STUDENT_SUBJECT
name_number_sibling    C   6  CHAR     NAME_NUMBER of a sibling (for family discount)            SR_STUDENT_PERSONAL_SEM
name_record_number     N   3  ZONED-U  Name history record number of person's name for this course SR_STUDENT_COURSE
number_attended        N   8  INTEGER  Total number of students attending this subject            SR_CODE_SUBJECT
number_grades          N   8  INTEGER  Total number of numeric grades for this subject            SR_CODE_SUBJECT
nz_non_res_fee         C   1  CHAR     This student is a NZ non-residential for fee payments      SR_STUDENT_PERSONAL
offered                C   1  CHAR     This course is currently offered                           SR_CODE_SUBJECT
other_field            C  10  CHAR     Study field in addition to 1st major and minor             SR_STUDENT_COURSE
overseas_sship_code    N   1  ZONED-U  Override code for overseas scholarship                     SR_STUDENT_PERSONAL_SEM
permanent_resident     C   1  CHAR     E390: Permanent resident status                            SR_STUDENT_PERSONAL
phone                  C  15  CHAR     Telephone number part of person address                    CI_ADDRESS
postcode               C   7  CHAR     Post code                                                  CI_ADDRESS
prior_qualification    N   1  ZONED-U  Highest qualification prior to application for enrollment  SR_STUDENT_PERSONAL
ref_semester           C   1  CHAR     Reference semester for this record                         SR_STUDENT_PERSONAL_SEM
ref_year               C   4  CHAR     Reference year for this record                             SR_STUDENT_PERSONAL
ref_year               C   4  CHAR     Reference year for this record                             SR_STUDENT_PERSONAL_SEM
religion_father        C  15  CHAR     Religion of student's father                               SR_STUDENT_PERSONAL
religion_mother        C  15  CHAR     Religion of student's mother                               SR_STUDENT_PERSONAL
residence_type         C   1  CHAR     Day or residential                                         SR_STUDENT_PERSONAL_SEM
sda_church_mship       C  20  CHAR     SDA church where membership is held                        SR_STUDENT_PERSONAL
secondary_institution  C  30  CHAR     Institution where prior qualification was received         SR_STUDENT_PERSONAL
semester               C   1  CHAR     E353: Semester in which the load occurs code               SR_CODE_SUBJECT
semester               C   1  CHAR     E353: Semester in which the load occurs code               SR_STUDENT_SUBJECT
semester               C   1  CHAR     Semester for this record                                   SR_STUDENT_COURSE
single_room_request    C   1  CHAR     Student is requesting a single room                        SR_STUDENT_PERSONAL_SEM
starting_semester      N   1  ZONED-U  Indicates in which semester (0/1/2) student will commence  SR_STUDENT_PERSONAL
state                  C  10  CHAR     State part of address                                      CI_ADDRESS
student_account        C   8  CHAR     Student ledger account number                              SR_STUDENT_PERSONAL
student_religion       C  15  CHAR     Religion of this student                                   SR_STUDENT_PERSONAL
student_status         N   1  ZONED-U  Registration status (registered, on leave, withdrawn, etc) SR_STUDENT_PERSONAL_SEM
subject_code           C   5  CHAR     Alphanumeric code assigned to this subject                 SR_CODE_SUBJECT
subject_code           C   5  CHAR     Alphanumeric code assigned to this subject                 SR_STUDENT_SUBJECT
subject_credit_points  N   2   ZONED-U Number of credit points, for academic credit and fees      SR_CODE_SUBJECT
subject_name           C  50  CHAR     Full description of this subject                           SR_CODE_SUBJECT
subject_name_abbrev    C  15  CHAR     Abbreviated name of this subject                           SR_CODE_SUBJECT
subject_type           C   1  CHAR     Degree/Certificate type of subject                         SR_CODE_SUBJECT
sum_grades             N   9  INTEGER  Sum of all numeric grades for this subject                 SR_CODE_SUBJECT
sum_grades_squared     N  12  INTEGER  Sub of the squares of all numeric grades                   SR_CODE_SUBJECT
te_score_nsw           N   1  ZONED-U  NSW equivalent Tertiary Entrance Ranking                   SR_STUDENT_PERSONAL
te_score_source        C   1  CHAR     State where TE score was obtained                          SR_STUDENT_PERSONAL
term_location          C   4  CHAR     E319: Location code of semester/term residence             SR_STUDENT_PERSONAL
termination_code       N   1  ZONED-U  Code giving reason why student is no longer doing course   SR_STUDENT_COURSE
tert_ent_score         N   1  ZONED-U  E369: Tertiary entrance score                              SR_STUDENT_PERSONAL
town                   C  30  CHAR     Town or suburb                                             CI_ADDRESS
typing_competency      C   1  CHAR     Indicates whether typing competency has been achieved      SR_STUDENT_PERSONAL
unit_status            C   1  CHAR     E355: Unit of study completion status                      SR_STUDENT_SUBJECT
vac_address_code       N   1  ZONED-U  Address number of vacation address                         SR_STUDENT_PERSONAL
year_achieved          C   4  CHAR     Year (YYYY) in which prior qualification was achieved      SR_STUDENT_PERSONAL
year_arrival           C   4  CHAR     E347: Year of arrival in Australia                         SR_STUDENT_PERSONAL
year_first_offered     N   4  ZONED-U  Year (YYYY) this course was first offered                  SR_CODE_COURSE_MASTER
year_last_offered      N   4  ZONED-U  Year (YYYY) this course last offered                       SR_CODE_COURSE_MASTER
year_yyyy              C   4  CHAR     Year (YYYY) for this record                                SR_CODE_COURSE_MASTER
year_yyyy              C   4  CHAR     Year (YYYY) for this record                                SR_CODE_SUBJECT
year_yyyy              C   4  CHAR     Year (YYYY) for this record                                SR_STUDENT_COURSE
year_yyyy              C   4  CHAR     Year (YYYY) for this record                                SR_STUDENT_SUBJECT

Appendix H: Review Questionnaire

Note: Responses to questions are given in italics.

1. The EIS/Data Warehousing concept

  1. Were the concepts sufficiently well presented?
    "I thought concepts were reasonably well presented."
  2. How did management regard the importance of the project?
    "It was probably not regarded with sufficient importance by Senior Management. The middle levels would probably be more involved in the actual use of the system - at least if we had a broader middle level. We are too small."
  3. What level of commitment did management give to the project?

2. Needs Analysis

  1. Was the process used to determine needs effective?
    "Analysis of needs was OK but probably idealistic. The pressures are such just coping with other things that taking time to work with the EIS and utilize the benefits at least for me was not and I doubt will be time-efficient."
  2. Was it thorough, detailed enough?
  3. Comments on what was done well, or what could have been done better.

3. Design & construction

Was there adequate:

  1. user involvement,
  2. feedback, communication, progress reports
  3. consulting over issues,
  4. demonstration of prototypes, etc?

4. Training

  1. Was training relevant, appropriate, timely, useful?
    "Training was unfortunately irrelevant and at the wrong time. I think the day we spent trying to learn the software was wasted. This part of the exercise should have come after the data warehouse was complete and in place so that we could get an introduction to its use and then move straight into its use."

5. Consulting

  1. Please comment on your impressions value of the consultants to the project, from your own experiences with them.
    "I thought the consulting process was adequate but we probably did not have a clear strategic plan in place to help us zero in on the critical performance indicators. We are still wrestling with the problem of trying to develop a strategic plan."

6. Desktop tools

  1. Impromptu - will you regularly find a use for this product to write your own ad hoc queries and reports?
  2. PowerPlay - will you regularly use this tool to answer questions that support decision making?
  3. Please comment on these tools in terms of ease of use, ease of learning to use, power and functionality.
    "I have used neither Impromptu nor PowerPlay since the training day."

7. Impact

  1. Please describe your understanding of the impact that the pilot project is having and will have on decision making at Avondale that it affects.
  2. Estimate the impact the data warehouse will have when fully implemented.
  3. Describe the kinds of changes you think these 'management improvement' technologies will suggest and facilitate.
    (ie, to do something better, you will do it differently)
    "No impact yet."

8. The Future

  1. What subject areas do you think should be the focus of the next level of development after the pilot project is complete?
    "If I am to benefit from the data warehouse I will need training again."

Section Endnotes

(41) Codd E.F., Codd S.B. and Salley C.T.: E. F. Codd & Associates. Providing OLAP (On-line Analytical Processing) to User-Analysts: An IT Mandate. p18.

(42) "D_" is used each time to indicate that this top most aggregation level is the dimension.

(43) Brown T.C.: Action Inquiry Network (ActNet). Action Inquiry, Problem Types

(44) Brown T.C.: Action Inquiry Network (ActNet). Action Inquiry, Problem Types


That concludes this paper. Previous section or return to table of contents


Last Modified: Monday, September 2, 2002 12:12 PM