reporting from the dataorchestrator ods data models
TRANSCRIPT
Reporting from the DataOrchestrator ODS Data Models
Reissued Manual as of November 10, 2010
This is a new edition of the Reporting from the DataOrchestrator ODS Data Models manual. This edition replaces the previous edition dated March 16, 2010, and incorporates the changes made for the InstallShield for the Datatel SAP Business Objects Custom Setup, and for the certification of Oracle database 11g Release 2 and for SQL Server 2008 R2.
The Primary Changes Made
Section Pages Changes Made
Prerequisites for the DataOrchestrator ODS Data Models
31 Updated Table 7 for the certification of Oracle database11g Release 2 and for SQL Server 2008 R2.
Installing the Business Objects Connector
178 Updated the list of major installation tasks for the Business Objects Connector to include the new InstallShield for the branding files used in Business Objects.
Running the “DataOrchestrator ODS Business Objects Connector” InstallShield
179 Renamed and updated section.
Running the “Datatel SAP Business Objects Custom Setup” InstallShield
181 Renamed and updated section.
Reporting from the DataOrchestrator ODS Data Models
Release 18
November 10, 2010
Datatel Colleague®
For last-minute updates and additional information about this manual, see AnswerNet page 5968.
Reporting from the DataOrchestrator ODS Data Models
© 2010 Datatel, Inc. All Rights Reserved
The information in this document is confidential and proprietary to and considered a trade secret of Datatel, Inc., and shall not be reproduced in whole or in part without the written authorization of Datatel, Inc. The information in this document is subject to change without notice.
Colleague and ActiveCampus are registered trademarks of Datatel, Inc. ActiveAlumni, ActiveAdmissions, MOX, and ILP are trademarks of Datatel, Inc. Other brand and product names are trademarks or registered trademarks of their respective holders.
Datatel, Inc.4375 Fair Lakes CourtFairfax, VA 22033(703) 968-9000(800) DATATELwww.datatel.com
Table of Contents
9 Introduction
11 About This Manual11 In This Chapter11 Who Should Read This Manual12 Terms Used in This Manual14 What This Manual Covers16 How This Manual Is Organized17 Where to Find More Information
19 About the DataOrchestrator ODS Data Models
19 In This Chapter20 Understanding the DataOrchestrator ODS Data Models21 What Data Models Cannot Do22 Facts About Data Models23 Workflow for the Data Models
27 Installation
29 Installing the DataOrchestrator ODS Data Models
29 In This Chapter30 Before You Begin31 Prerequisites for the DataOrchestrator ODS Data Models32 Retrieving the Datatel Software Updates32 Procedure for Retrieving the Software Updates33 Installing the DataOrchestrator ODS Data Models41 Accessing Data Model Help42 Understanding Views43 Naming Conventions for the Data Models and Views45 Optimizing the Data Model Refresh45 Determine the Number of Concurrent Threads to Use
on the DataOrch Refresh (DORE) Form45 Avoid Updating the Previous Years’ File Suite
Instances46 Run Incremental Refreshes for Data Model Updates48 Create Multiple Refreshes
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 5© 2010 Datatel, Inc.
Table of Contents
49 Using the DataOrchestrator ODS Data Models
51 CORE Data Model51 In This Chapter52 CORE Reporting Subject Areas53 Demographics55 Transform Customization Steps56 Academic Credentials and Graduation58 Data Model Diagram
63 Student Data Model63 In This Chapter64 Student Reporting Subject Areas65 Applicants and Applications68 Courses and Faculty72 Enrollment, Academic Programs, Test Scores, and Room
Assignments79 Billing82 Financial Aid (FA)86 Data Model Diagrams
93 HR Data Model93 In This Chapter94 HR Reporting Subject Areas95 Employee Information
102 Payroll Information104 Data Model Diagrams
109 Finance Data Model109 In This Chapter110 Finance Reporting Subject Areas111 Accounts Payable and Purchasing117 GL Transaction Information120 Data Model Diagrams
125 Advancement Data Model125 In This Chapter126 Advancement Reporting Subject Area127 Demographics and Contributions131 Data Model Diagrams
6 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Table of Contents
135 Customizing Data Models135 In This Chapter136 Understanding Customization137 How to Customize the Data Models138 Customizing Transforms142 Customizing SQL Views
143 Using Reports from the Data Models143 In This Chapter143 Examples of Queries and Reports144 Crystal Example Report – Student Enrollment by Term146 Web Intelligence Example Report – HR Earnings Analysis149 Excel Example Report – GL Actuals Analysis Report
151 Using the Business Objects Connector
153 Using the Connector with the Data Models153 In This Chapter154 Overview of the Business Objects Connector156 Universes Delivered in the Business Objects Connector158 Description of the Universes158 Understanding Universe Contexts159 Adding New Tables and Joins to Universes161 Understanding Measures in the Connector’s Universes161 Customizing the Universes162 Colleague Core ODS Universe164 Colleague Student ODS Universe167 Colleague Courses and Faculty ODS Universe169 Colleague Financial Aid ODS Universe172 Colleague Human Resources ODS Universe174 Colleague Finance ODS Universe176 Colleague Advancement ODS Universe178 Installing the Business Objects Connector179 Running the “DataOrchestrator ODS Business Objects
Connector” InstallShield179 Procedure for Running the InstallShield181 Running the “Datatel SAP Business Objects Custom
Setup” InstallShield181 Procedure for Running the InstallShield
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 7© 2010 Datatel, Inc.
Table of Contents
183 Importing the Universes183 Universes Delivered with Business Objects Connector
1.1184 Procedure for Importing Universes from the BIAR File191 Importing the Sample Reports192 Procedure for Importing Sample Reports from the
BIAR File199 Customizing the Universes for your ODS Target Database199 Procedure for Customizing the Universes for Your
ODS Target Database206 Reporting Using the Universes in Web Intelligence206 Filtering Queries Versus Filtering Reports208 Defining Variables to Create Measures with Distinct
Counts209 Using Measures with Distinct Totals and Averages
213 Appendices
215 Checklist for Setting Up the Data Models215 In This Appendix216 Checklist for Setting Up the Data Models
219 Troubleshooting the Data Models219 In This Appendix220 DataOrchestrator ODS Data Model Issues
223 Understanding the GL Account Number Structure
223 In This Appendix224 About the Components of GL Account Numbers226 Understanding Major Components of GL Account
Numbers226 Required Major Components227 Understanding Subcomponents of GL Account Numbers
229 Index
8 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting from the DataOrchestrator ODS Data Models
Introduction
Introduction
About This Manual
In This ChapterThis chapter describes the contents and organization of this manual. Table 1 lists the topics in this chapter.
Who Should Read This ManualThis manual is for system administrators, superusers, system programmers, and end users who set up and support reporting, and create reports at your institution. To understand some of the concepts and instructions in this manual, you should have a basic knowledge of the Envision file structure.
You should also be familiar with the native reporting tools for the database used by your institution, along with industry-standard reporting tools, such as Crystal Reports®, BusinessObjects™ Web Intelligence®, or Microsoft® Excel.
Table 1: Topics in This Chapter
Topic Page
Who Should Read This Manual 11
Terms Used in This Manual 12
What This Manual Covers 14
How This Manual Is Organized 16
Where to Find More Information 17
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 11© 2010 Datatel, Inc.
Introduction: About This Manual
Terms Used in This ManualThe following are the terms and descriptions that are important to understand and use the concepts presented in this manual. These terms are used throughout this manual:
Data model. A data model is the design of a set of tables and views on a target database, specifying all the data elements within each table or view and the relationships between them. The DataOrchestrator ODS data models contain a defined subset of data from Colleague applications.
Operational Data Store (ODS). An ODS is a repository of data extracted from a transactional system about a specific area of knowledge and provides easy access to the data for reporting and analysis. It is a snapshot of data captured at a point in time.
Source Database. This refers to the Colleague environment from which you want to create an ODS.
Target Database. This refers to the database where you want to place an ODS.
Target. A target contains all of the necessary information about an ODS target database including:
Connection information.
The set of source and target transforms that should be executed to create and update the tables in the ODS.
Current update status information of the target.
Source Transform. A source transform allows you to specify the data to move to an ODS target database by selecting a set of fields and/or computed columns from a single Colleague file. The selected data will be structured on the target database in one or more tables in the same way as the standard SQL representation for that data in a Colleague environment. Source transforms do not allow you to make any data transformations when the data is populated.
Target Transform. A target transform allows you to specify the format of the data in a table on the ODS target database, the Colleague source data elements that will populate each column, and any data transformation operations to be run when the data is populated. Target transforms are a powerful tool for creating ODS tables that are optimized for reporting purposes, and provide greater flexibility in defining how the data is structured on the ODS.
Note: All transforms referenced in this manual are target transforms.
12 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Terms Used in This Manual
Refresh. The refresh is the specification for an executable process that refreshes a subset of the transforms associated with a target.
Full Refresh. A full refresh means that all records in the source file for the transform you choose will be processed through the transform and updated on the ODS target database.
Incremental Refresh. An incremental refresh means that only those records in the source file that changed since the last refresh will be processed through the transform.
Reporting Data Access Server (RDAS). A DMI Listener role defined for use with the DataOrchestrator ODS. This DMI Listener role includes a set of transactions for bulk data management.
SQL View. An SQL view combines data from various physical tables where it is stored so that the result appears to be data from a single table. As a result, a report or query is easier to build because the user creating the report does not need to know the tables where data elements are stored or their join relationships.
Universe. Universe is the Business Objects’ term for their semantic layer that defines the complexities of relational database technology. This layer is what enables non-technical users to autonomously query, analyze and report on information using a simple drag-and-drop interface. The report user does not need to know how to get report information from the database, but simply what information they need. The Business Objects’ product used to create universes is called the Designer.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 13© 2010 Datatel, Inc.
Introduction: About This Manual
What This Manual CoversThis manual discusses the data models used with the DataOrchestrator ODS™ available for reporting for the following applications:
Datatel Colleague Core (CORE)
Datatel Colleague Student (ST)
Datatel Colleague Finance (CF)
Datatel Colleague Human Resources (HR)
Datatel Colleague Advancement
To use the data models, you must first install the DataOrchestrator ODS, and then create operational data stores for your application data. For more information, see Using the DataOrchestrator ODS™. The DataOrchestrator ODS data models are created by running DataOrchestrator ODS transforms, which are specifications for extracting and transforming Colleague data to populate each target database table in the data model. Data models also include definitions of reporting views, which are predefined queries on specific subsets of this extracted data.
The information in the operational data stores can be kept up-to-date through periodic refreshes of current data from the Colleague source database. The operational data stores can be used not only as a source for reporting from this Colleague data, but also as a source for data extracts for propagation into data warehouse systems.
The chapters in this manual contain the information and procedures for installing the data models, implementing and using the data models, and building reports against the tables and SQL views in the data models.
In this manual you will find high-level installation and setup information, and references to more detailed installation documentation. This documentation provides information on how to perform the following tasks for implementing the data models.
Use the DataOrchestrator ODS with the data models and reporting views. To install the DataOrchestrator ODS, see Using the DataOrchestrator ODS™.
Set up the parameters on which the data models are based.
Create and populate the data models.
Create and view reports based on the tables and SQL views in the data models.
14 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
What This Manual Covers
In addition, Datatel provides a Business Objects Connector for the DataOrchestrator ODS to enable your institution to utilize Business Objects reporting tools to leverage data in an ODS target database. The Connector consists of a set of universes for Business Objects, pre-configured to match the definition and format of the data models. Your institution gains the ability to quickly and easily implement Business Objects and conduct web-based, self-service ad hoc queries.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 15© 2010 Datatel, Inc.
Introduction: About This Manual
How This Manual Is OrganizedTable 2 shows how this manual is organized.
Table 2: Organization of This Manual
Part Chapter Title Summary
Part 1
Introduction
About This Manual Includes information about who should read this manual, terms used in the manual, what this manual covers, how it is organized, and where to find information on related topics.
About the DataOrchestrator ODS Data Models
Discusses what the DataOrchestrator ODS data models are and how they can enhance your institution’s reporting.
Part 2
Installation
Installing the DataOrchestrator ODS Data Models
Provides information about retrieving the Datatel software updates and performing the installation steps. Additional information about the tables and SQL views that make up the data models is also included.
Part 3
Using the DataOrchestrator ODS Data Models
CORE Data Model
Student Data Model
HR Data Model
Finance Data Model
Advancement Data Model
Customizing Data Models
Using Reports from the Data Models
Provides an overview of all tables and views from the CORE, Student, HR, Finance, and Advancement applications used for reporting from the DataOrchestrator ODS. Also provides information on customizing the data models, as well as examples of queries and reports created from the data models.
Part 4
Using the Business Objects Connector
Using the Connector with the Data Models
Provides information about using the Business Objects Connector with the DataOrchestrator ODS data models, and detailed information about the universes included. Also provides information about the optional sample reports for Web Intelligence.
Appendices Checklist for Setting Up the Data Models
Provides a quick checklist of the activities to perform to set up and create the data models.
Troubleshooting the Data Models
Provides suggestions for items to check or steps to take if you encounter issues in using the data models.
Understanding the GL Account Number Structure
Provides an overview of the structure of General Ledger (GL) account numbers.
16 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Where to Find More Information
Where to Find More InformationTable 3 lists additional resources for finding more information.
Table 3: Additional Resources
Type of Information Resource
Information on installing the DataOrchestrator ODS
Using the DataOrchestrator ODS
How to set up batch processes to run at scheduled intervals
Envision Runtime Administration
How to activate and calculate stored computed columns
Stored Computed Columns
Business Objects’ documentation http://help.sap.com/
Under the heading “Popular Documentation,” click SAP BusinessObjects Solution Portfolio Knowledge Center
Recommended Web Intelligence documentation
Howson, Cindi. BusinessObjects XI (Release 2): The Complete Reference. New York, NY: McGraw-Hill, 2006.
Assistance with implementing SQL-based reporting solutions
Datatel Consulting Services. Contact Services Scheduling:
1-800-DATATEL (328-2835)
Technical Support The Datatel Solution Center. Contact the Tools and Technology Team:
1-800-DATATEL (328-2835)
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 17© 2010 Datatel, Inc.
Introduction: About This Manual
18 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Introduction
About the DataOrchestrator ODS Data Models
In This ChapterThis chapter provides information that you should understand about the DataOrchestrator data models before you use the product to create and use them for your institution. It addresses the following questions:
What are the DataOrchestrator ODS data models, and what value do they bring to reporting?
What are the limitations of the data models?
What are your initial and ongoing workflows in your use of the data models and the DataOrchestrator ODS?
Table 4 lists the topics in this chapter.
Table 4: Topics in This Chapter
Topic Page
Understanding the DataOrchestrator ODS Data Models 20
Facts About Data Models 22
Workflow for the Data Models 23
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 19© 2010 Datatel, Inc.
Introduction: About the DataOrchestrator ODS Data Models
Understanding the DataOrchestrator ODS Data Models
The DataOrchestrator ODS data models were developed to help provide increased reporting capabilities to your institution’s staff, managers, and executives to support their institutional effectiveness strategies and long-range planning efforts.
The data models are a powerful tool that allows you to export and transform data from Colleague applications to create operational data stores that encompass specific application areas of knowledge.
Ease of access and usability, as well as fast processing of data, are some of the advantages of operational data stores. Also, operational data stores are easy to use and convenient because they are not restricted to the same server or location as the source database. Data can be exported to a target SQL-based (SQL Server or Oracle) database.
The following are some of the benefits derived from the DataOrchestrator ODS data models and views:
The data models, as discussed in this manual, enhance your reporting capability. By utilizing data models to build your operational data stores, Datatel provides a better means for you to report across Colleague applications while using a variety of reporting tools appropriate for your institution.
When extracting data from Colleague, the DataOrchestrator ODS specifications for the data models transform the data using DataOrchestrator ODS operations so that the data is available in a form that best suits your reporting purposes.
The data models are defined subsets of Colleague data that are created in the ODS target database to optimize reporting. This means that you no longer have to search through thousands of elements in Colleague to select information for reporting. Instead, you are able to simply utilize the tables and views in the DataOrchestrator ODS data models for your reporting purposes.
For the decision makers at your institution, who need consistent and accurate data on a daily basis, the data models provide a snapshot of information, or operational picture, from a particular point in time. For example, all the reports created from an operational data store today will have consistent information from one report to another, irrespective of who created the report.
You have complete flexibility in determining how often operational data stores should be refreshed. A decision may be reached between the
20 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Understanding the DataOrchestrator ODS Data Models
technical staff and the various administrative staff at your institution to determine how fresh the data needs to be for reporting from each data model.
You have the ability to do full or incremental refreshes of data as appropriate within the data models, and you can also set up the process handler to automatically refresh this data.
By using the data models for your operational reporting, you can improve the performance of the transactional database by reducing the load on that database, because information for this reporting is derived from data on another database server.
A Business Objects Connector is provided as an optional module that delivers a set of universes to facilitate a broad range of reporting capabilities using the DataOrchestrator ODS data models.
What Data Models Cannot Do
In addition to understanding what the data models can do for your institution, it is also important to understand what they are not designed to do.
The operational data store you create using the data models is not a data warehouse. The information in a data warehouse is highly optimized and aggregated for analytic reporting, and contains a complete historical record of institutional data over time. However, the data model information is not aggregated, but retains the level of detail of the data structures in the transactional database. This information is also defined to focus on the current values of information from the source Colleague files, and does not contain all historical data from the transactional database.
In addition, although the data in the operational data store is intended to be used for operational purposes; it is not intended to be used for transactional purposes, such as up-to-the-minute reporting. Operational data store information is taken at a point in time, and the information may not be the most current. Therefore, when you use the data models, it is important to remember these limitations.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 21© 2010 Datatel, Inc.
Introduction: About the DataOrchestrator ODS Data Models
Facts About Data Models
To further understand the nature of reporting using data models, and to maximize their utility, consider the following facts:
The operational data stores you create with these data models for reporting include static data, not dynamic or live data. The data is captured at the time the data is exported to the operational data store. Thus, it is a snapshot of data at a point in time. The operational data store is updated with new data each time the refresh is run. Therefore, the resulting reports will consistently reflect the data existing at the time of the operational data store’s update.
The latest transactional data will be selected from the database whenever you export data to the operational data store.
You can set up the Process Handler to export data model information to an operational data store automatically on a preset schedule. For more information, see Runtime Administration.
22 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Workflow for the Data Models
Workflow for the Data ModelsA high-level workflow for using the DataOrchestrator ODS to create and maintain the data models on the ODS database is shown in Figure 1 on page 24 and Figure 2 on page 25.
Figure 1 shows the initial setup and population of the ODS target database. This workflow is an overview of the process that will be followed in “Installing the DataOrchestrator ODS Data Models” beginning on page 29.
One of the key activities of the initial setup workflow is handling data errors in the source Colleague environment, which can cause failures in loading data records to the ODS target database tables. These can be handled in one of the following two ways:
Correcting the source data elements, based on the specific error record information provided by the DataOrch Error Analysis (DOEA) form in the DataOrchestrator ODS.
Modifying the transforms for the models.
The second approach will be effective only for dealing with data length errors, which can be addressed by increasing the output column length of the column identified in the DataOrch Error Report, or by setting the Truncate String flag on the DataOrch Target Transform (DOTT) form for the transform encountering the error.
Figure 2 on page 25 shows the process for monitoring and correcting a regularly scheduled refresh of an ODS target database using the data models. Again, a key activity is the need to continually monitor the refresh statistics and evaluate the errors encountered in the previous run. These can be handled by either correcting the source Colleague data elements or by adjusting the transform as described for the initial setup workflow.
Note: In the workflows shown in Figure 1 on page 24 and Figure 2 on page 25, you need to run the Update Stored Computed Column (USCC) process only if you are running the Student (ST) data model. The other data models do not contain any references to stored computed column fields that need to be refreshed.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 23© 2010 Datatel, Inc.
Introduction: About the DataOrchestrator ODS Data Models
Figure 1: Workflow for Initial Population of Data Models to the ODS Target Database
24 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Workflow for the Data Models
Figure 2: Workflow for Refreshing Data Models
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 25© 2010 Datatel, Inc.
Introduction: About the DataOrchestrator ODS Data Models
26 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting from the DataOrchestrator ODS Data Models
Installation
Installation
Installing the DataOrchestrator ODS Data Models
In This ChapterThis chapter provides information about retrieving software updates for the DataOrchestrator ODS data models that contain tables and views in the following Colleague reporting subject areas:
CORE. Demographics, and Academic Credentials and Graduation.
Student. Applicants and Applications; Courses and Faculty; Enrollment, Academic Programs, Test Scores, and Room Assignments; Billing; and Financial Aid (FA).
HR. Employee Information and Payroll Information.
Finance. Accounts Payable and Purchasing, and GL Transaction Information.
Advancement. Demographics and Contributions.
This chapter also includes the installation steps you will need to perform and instructions on incorporating the installed data model objects into your ODS target database.
Table 5 lists the topics in this chapter.
Table 5: Topics in This Chapter
Topic Page
Retrieving the Datatel Software Updates 32
Installing the DataOrchestrator ODS Data Models 33
Understanding Views 42
Optimizing the Data Model Refresh 45
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 29© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Before You BeginTable 6 lists the tasks that must be complete before you can continue with the procedures in this chapter.
Table 6: Before You Begin
Task Reference
Install the DataOrchestrator ODS. Using the DataOrchestrator ODS
Create a SQL Server or Oracle target database for the ODS.
Set up a target in the DataOrchestrator ODS and perform configuration to allow data extraction to the ODS target database.
30 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Prerequisites for the DataOrchestrator ODS Data Models
Prerequisites for the DataOrchestrator ODS Data Models
Table 7 lists the prerequisites that your system must meet before you install the software updates for the DataOrchestrator ODS data models.
Table 7: Prerequisites for the Data Models and Views
Prerequisite Comment
Colleague R18 Make sure Colleague is current on all software updates. If you need help, contact the Datatel Solution Center.
UniData 7.1 For UniData, you should have the source Colleague database on UniData version 7.1 or later. For information about installing UniData, see the installation procedures provided by IBM.
SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2
If you are using SQL Server as your source or target database, you must have SQL Server 2005 or SQL Server 2008 on your source and target database servers. Other releases are not supported.
Oracle Database11g Release 2, 11g, or 10g Release 2
If your source or target database are Oracle, you must have Oracle Database 11g or 10g Release 2 on both database servers. Other releases are not supported.
Datatel Messaging Interface (DMI) with Data Access Server (DAS)
Use the DMI_DAS installed in the Colleague application environment for the source (Colleague) database.
Ports used by the DMI Listeners must be open between the Colleague and ODS database servers.
Open ports are required so that the Colleague database server DMI_DAS can communicate with the ODS database server DMI RDAS.
The port used by the Datatel daemon on the ODS target database server must be open to the client PC using SA Valet in order to install software updates.
The port for the daemon must be open in order to manage and update the DMI RDAS through SA Valet.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 31© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Retrieving the Datatel Software Updates
To use the DataOrchestrator ODS data models for reporting, you must download and install software updates.
Procedure for Retrieving the Software Updates
Use the Colleague release system to retrieve and load the appropriate software updates. See Updating Colleague Software (available for downloading from the Documentation section of the Datatel website) for information on retrieving and installing software updates.
32 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
Installing the DataOrchestrator ODS Data Models
You can structure your operational data stores as best suits your institution, defining separate ODS target databases and targets for different reporting subject areas and controlling user access to the data on the target database. However, these instructions assume a single target is used for all delivered content.
To install the software updates for the DataOrchestrator ODS data models, follow these steps.
Step 1. (Colleague UniData Windows only). Change the default setting of the MAX_TRANS_FIELD environment variable in your Windows UniData environment.
a. To change this default, the variable MAX_TRANS_FIELD must be added to the System variables in the Environment Variables tab of the Windows Server System Properties, with a value of 64. Figure 3 on page 34 shows how this would appear on the Windows environment variable update form.
Technical Tip: In a Windows UniData Colleague environment, there is a default system setting of 12 UniQuery TRANS statements that a single query can contain without negatively affecting performance. Datatel recommends changing this default in order to improve the refresh performance for the DataOrchestrator ODS data model. The setting cannot be implemented as a udtconfig parameter change, but instead must be added to the Windows Environment System variables.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 33© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Figure 3: Default Setting of the MAX_TRANS_FIELD Environment Variable
b. Restart UniData, including UniRPC, on the Windows server in order for this setting to take effect.
Step 2. In the UT application, access the DataOrch Transform Maint (DOMA) form. In the Operation field, select Copy.
Step 3. Enter the following information on the DOMA form:
a. In the From Target ID field, enter the following:
COLL_ODS_CORE_1_0
This selects the Datatel-delivered target template containing the CORE data model transforms.
b. In the Transform List field, use LookUp to select all transforms in the data model.
c. In the To Target ID field, enter the target to which you want to copy all transforms. (This is the target you are using to build your operational data store.)
34 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
Step 4. Save from the DOMA form to copy all transforms to the specified target. Verify that no errors occurred during the process.
Step 5. In the UT application, access the DataOrch View Maintenance (DOVM) form. Enter the following information on this form:
a. In the Operation field, select Copy.
b. In the From Target ID field, enter the following:
COLL_ODS_CORE_1_0
This selects the Datatel-delivered target template containing the CORE data model view definitions.
c. In the SQL Views field, use LookUp to select all views in the COLL_ODS_CORE_1_0 target.
d. In the To Target ID field, enter the target to which you want to copy all view definitions. (This is the target you are using to build your operational data store.)
Step 6. Save from the DOVM form to copy all views to the specified target. Verify that no errors occurred during the process.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 35© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Step 7. Datatel recommends that you perform a refresh of the CORE data model transforms to your specified target database, in order to test the performance of the installation in your environment. To do this, follow these steps:
a. Access the DataOrch Refresh (DORE) form. Create a new Refresh and associate it with the specified target.
b. In the Transform Selection Option field, select All Target Transforms.
c. In the Create SQL Views (Y/N) field, enter Yes. This runs all the SQL view definitions associated with the target and creates the data model views on the ODS target database.
d. Select the Number of Concurrent Threads for the refresh to use. The more threads you use, the more system resources are utilized. This may affect the performance of other processes.
e. Run the refresh by saving from the DORE form, and then verify that it finishes successfully.
Step 8. Copy the rest of the data model transforms and SQL view definitions by using the DataOrch Target Copy (DOTY) form.
a. In the Copy from Target IDs field, enter COLL_ODS... and then select the target templates to insert them in the correct order listed below. (Copy only the target templates associated with the applications that are installed in your Colleague environment.)
COLL_ODS_CA_1_0
COLL_ODS_CF_1_0
COLL_ODS_CF_1_1
COLL_ODS_CORE_1_1
COLL_ODS_HR_1_0
COLL_ODS_HR_1_1
COLL_ODS_HR_1_2
COLL_ODS_ST_1_0
COLL_ODS_ST_1_1
Note: There may be source data errors that you can resolve later.
ALERT! The Datatel-delivered target templates must be in the order listed above for your institution’s applications.
36 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
b. In the Copy to Target ID field, enter the target to which you want to copy all target templates. (This is the target you are using to build your operational data store.)
Step 9. If you copied the CF transforms to your target, you must customize two of these transforms with your institution-specific information.
The first customization is to add GL subcomponents to the ODS_GL ACCTS transform in order to provide reporting by these attributes. In order to do this, you need to know your institution’s GL account number structure.
Step 10. To proceed with this customization, access the DataOrch Target (DOTA) form. In the Target Transforms list, detail on the ODS_GL_ACCTS transform to access the DataOrch Target Transform (DOTT) form. Detail on the Column Definitions field to access the Transform Columns (DOTC) form.
Step 11. Using the scroll bar on the right of the DOTC form, scroll to the bottom of the list. In the Target Column field, perform a LookUp in the first blank entry available.
Step 12. From the list of fields presented, select fields for the GL subcomponents defined at your institution.
For each subcomponent that you select, you may also want to select the XXXX_DESC and XXXX_WITH_DESC fields. XXXX_DESC contains the textual description of the subcomponent, and XXXX_WITH_DESC contains the subcomponent value with its description.
For example, if your site has a GL subcomponent called Fund, you can select the following fields to add the columns to the transform:
FUND
FUND_DESC
FUND_WITH_DESC
Technical Tip: If you don’t know your institution’s GL account number structure, access the Account Structure Maintenance (GLAS) form in the Colleague Finance application and check for the information in the Subcomponent External Names field. For more information, see “Understanding the GL Account Number Structure” beginning on page 223.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 37© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Step 13. Save your entries on the DOTC form to complete the first customization, and then save from the DOTT form.
Step 14. The second customization involves the ODS_GEN_LDGR transform. On the DOTA form, in the Target Transforms list, detail on the ODS_GEN_LDGR transform to access the DOTT form. Detail on the Column Definitions field to access the DOTC form.
Step 15. In the Target Column field, in the first blank entry available, enter the following:
FISCAL_YEAR_START_MONTH
This adds the predefined column for the fiscal year start month for your institution.
Step 16. Save from the DOTC form to complete the second customization, and then save from the DOTT form to return to the DOTA form.
Step 17. For any transforms that are based on Colleague file suites, you must add a filter criteria for file suite instances. To do this, follow these steps:
a. In the Target Transforms list, detail on the file suite transform you need (the file suite transforms are listed at the end of Step 17) to access the DOTT form. Detail on the Filters/File Suite Instances field to access the Filter Criteria (DOFC) form.
b. Using wildcard selection, enter one or more file suite instances in the File Suite Instances list.
c. Save from the DOFC form to complete the filter criteria. Save from the DOTT form to return to the DOTA form.
Technical Tip: To improve refresh performance, Datatel recommends that you set the current year file suite to “Yes” in the Update Instance (Y/N) field and set previous years to “No” because the previous years’ data is static. The initial refresh of the transform will include all file suite instances specified on the DOFC form, and subsequent refreshes will include only instances set to “Yes.” If the transform’s structure changes, then the next refresh will include all instances specified on the DOFC form.
38 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
d. Do steps 17a - 17c for the following transforms:• ODS_ENC_PO• ODS_ENC_REQ• SPT_CS_ACYR• SPT_GLA_FYR• SPT_GLS_FYR• SPT_GLS_M_ACTUALS• SPT_GLS_M_ALOC_BUDGET• SPT_GLS_M_APPR_BUDGET• SPT_GLS_M_CONT_BUDGET• SPT_GLS_M_ORIG_BUDGET• SPT_GLS_M_PO_ENCS• SPT_SL_ACYR• SPT_TA_ACYR
Step 18. If you have installed the ST data model transforms but do not have the HR application in your Colleague environment, you must customize the ODS_FACULTY and ODS_COURSE_SEC_FACULTY transforms. You
Note: Depending on the transforms you have copied to your target, you may not see all these files in the Target Transforms list.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 39© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
must remove references to HR subroutines that do not exist on your source database. To do this:
a. Access the DOTA form and select your target.
b. Locate the ODS_FACULTY transform in the Target Transforms list and detail on it to access the DOTT form.
c. Detail on the Column Definitions field to access the DOTC form.
d. Set the Export (Y/N) flag to No for the TENURE_STATUS and TENURE_STATUS_DESC fields.
e. Save from the DOTC and DOTT forms to return to the DOTA form.
f. In the Target Transforms list, locate the ODS_COURSE_SEC_FACULTY target and detail to access the DOTT form.
g. Detail on the Column Definitions field to access the DOTC form.
h. On the DOTC form, scroll down to the PAID_AMOUNT field and set the Export (Y/N) flag to No.
i. Save from the DOTC, DOTT, and DOTA forms.
Step 19. You must activate and update the stored computed column used by the Colleague Student data model. Otherwise, this stored computed column is not available in the operational data store.
To do this, see “Calculating Stored Computed Columns” in Using the DataOrchestrator ODS. You need to:
Use the Define Stored Computed Column (DSCC) form to activate and update the STA.STORED.CUM.GPA stored computed column.
Use the Update Stored Computed Column (USCC) form in the ST application to update the STU.ACAD.LEVELS.CC file.
Step 20. In the UT application, access the DORE form. In the Transform Selection Option field, select All Target Transforms. In the Create SQL Views (Y/N) field, enter Yes to create the SQL views for the target. Run the refresh to complete the initial refresh of the DataOrchestrator ODS data model.
Note: If you need to improve the performance of the refresh for your institution, see “Optimizing the Data Model Refresh” on page 45.
40 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
Accessing Data Model Help
A documentation transform is delivered with the data models, so that you can view descriptions of the data model columns and better understand the data in the operational data store. This transform includes the column name, the table the column is a part of, and the description of the column. This transform is meant to be refreshed to the ODS target database so that it is available to include in queries and reports.
The documentation transform (ODS_DATA_MODEL_HELP) is delivered as part of the COLL_ODS_CORE_1_0 target template. This transform will be copied to your target with the rest of the CORE transforms and will be executed as part of the overall refresh of your target if you follow the standard installation instructions in this manual. The documentation information will be available in the ODS_DATA_MODEL_HELP table on your target database, and can be queried using your SQL reporting tool of choice.
The ODS_DATA_MODEL_HELP transform has a predefined filter that includes only the documentation of the columns of transforms in the Datatel-delivered target templates. This filter can be customized manually to include additional target IDs to refresh user-defined target documentation to the ODS. This filter is defined on the Filter Criteria (DOFC) form and is accessible by detailing on the ODS_DATA_MODEL_HELP transform to the DOTT form, and then detailing on the Filters/File Suite Instances field.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 41© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Understanding Views
SQL view scripts are delivered for each application in the target template for that application. (For example, SQL view scripts for CORE are in the target template COLL_ODS_CORE_1_0.) Two versions of each view are delivered, one for a SQL Server target database and another for an Oracle target database.
When you installed the data models, you copied the view definitions from the standard target templates to your defined targets using the DataOrch View Maintenance (DOVM) form. To create the views automatically whenever a refresh is run, set the Create SQL Views field to “Yes” on the DataOrch Refresh (DORE) form. In this case, there is nothing further that you need to do. Note, however, that after views are created successfully, they do not need to be recreated when refreshing a target unless any of the views change.
Alternatively, you can create individual views using the DataOrch Target Views (DOTV) form. See Using the DataOrchestrator ODS for further information on the DOTV form and on managing SQL views.
If there are target ODS tables that are not yet created on the target database, there may be a number of error messages generated indicating that views cannot be created. These error messages are written to a record in the _HOLD_ directory which contains the complete view script error. You can access them by using the Sequential File Browse Shell (UTFB) form. The view error record ID is displayed at the end of the refresh in the form of refreshId’_view’.
View names are prefixed with ODS_ and are meant to be reported against directly, rather than reporting against the physical tables, prefixed with SPT_, that the views reference.
42 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
Naming Conventions for the Data Models and Views
When data is extracted from the source Colleague database and created on the ODS target database, the tables and SQL views created in the operational database contain three different prefixes. Understanding these prefixes is important when setting up reporting at your institution.
ODS_
Physical tables and SQL views have the ODS_ prefix to signify that direct reporting should occur against these objects.
SPT_
The SPT_ prefix signifies that the table is a support table, which typically means that an SQL view references this table. (For information on exceptions to this, see “SPT_ Tables Not Referenced by Views” on page 44.) These supporting tables are not meant for direct access by your reporting tools. The SQL views created on supporting tables are named with the prefix ODS_.
DO_
DataOrchestrator ODS metadata is stored in tables with the DO_ prefix. The status of a refresh is updated at various intervals in the process and can be queried through these tables as the refresh is running.
For more information on tables and views, see Part 3, “Using the DataOrchestrator ODS Data Models.”
Technical Tip: Datatel recommends that reporting occur against the view that references this table and not against the physical table itself. Datatel may change the content and format of SPT_ tables in future releases; however, the structure of the ODS_ reporting tables and SQL views will be kept as stable as possible to avoid impacting previously developed reports.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 43© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
SPT_ Tables Not Referenced by Views
Some SPT_ tables are not referenced by views. This is because the data elements in the data models are used for a variety of reporting purposes. The tables and views in the data models serve as a data source for direct client reporting, whether using SQL statements or an SQL-based reporting tool such as BusinessObjects 3.1 Crystal Reports. They are also used by the:
Datatel Business Objects Connector reporting solution
Datatel Application Dashboards solution
iStrategy Higher Ed Analytics data warehouse solutions (for Colleague Student, Finance, and Human Resources).
The reporting needs of these solutions largely overlap. In general, the data elements in tables and views prefaced with “ODS_” are a superset of the data required for the other reporting solutions.
However, there are two cases where data elements are required specifically for one of these other solutions. These data elements exist only in support tables (prefaced with “SPT_”) and are not referenced by any view. This is because either the data is provided in a different form elsewhere in the data models, or the data contains historical information that the data models were not designed to maintain and which would complicate queries.
An example of the first case is the SPT_EARNTYPE table, which contains:
All the earnings type codes.
An indicator flag as to whether the code is currently active.
The text description corresponding to the code.
This information is included in the HR data model's ODS_PAYTODAT_EARN view as part of each payroll record. However, the iStrategy Higher Ed Analytics Colleague Human Resources solution needs the definition of all earnings types (for efficient creation of a data warehouse dimension table). Therefore, the SPT_EARNTYPE table is included in the HR data model, but is not referenced by any view.
An example of the second case is the SPT_PERSTAT table, which contains all the historical status records for each employee. This information is needed by the iStrategy Higher Ed Analytics Colleague Human Resources solution for historical reporting purposes. However, the HR data model is designed to provide current employee status only (in the ODS_HRPER table). Therefore, the historical employee status information in the SPT_PERSTAT table is not referenced by any view.
Note: Minimal time is added to the overall refresh schedule for your client site because of these SPT_ tables. Therefore, Datatel provides no specific guidance to exclude them.
44 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
Optimizing the Data Model Refresh
To improve the performance of the data model refresh, there are a number of suggestions that Datatel recommends.
Determine the Number of Concurrent Threads to Use on the DataOrch Refresh (DORE) Form
You will need to perform multiple refreshes in order to establish the optimal number of threads to use to refresh the data models. Using more threads will process more transforms concurrently and can speed up the refresh, but at the cost of additional system resources.
The number of threads to use depends on the size and performance of the source and target database servers and by the other processes running on those servers.
Avoid Updating the Previous Years’ File Suite Instances
Transforms based on Colleague file suites use a filter criteria to determine which years in the file suite will be updated. This filter is set on the Filter Criteria (DOFC) form.
To access this filter, use the DataOrch Target (DOTA) form and detail from the Target Transforms list on the file suite transform to the DataOrch Target Transform (DOTT) form. Detail on the Filter Criteria field to access the Filter Criteria (DOFC) form.
ALERT! Using multiple threads for your refresh can significantly affect the system resources available to your Colleague environment. Datatel recommends that you carefully monitor the system resource usage of your Colleague database server while running your ODS refresh and adjust the scheduling of your refresh and the number of threads you assign it accordingly.
Note: When more threads are used, not only are more system resources used as well, but also more UniData licenses, and both of these can affect the performance of other processes.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 45© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Datatel recommends that you set the current year file suite to “Yes” in the Update Instance (Y/N) field and set previous years to “No” because the previous years’ data is static. The initial refresh of the transform will include all file suite instances specified on the DOFC form, but subsequent refreshes will include only instances set to “Yes.” The transforms based on file suites included in the data models are:
ODS_ENC_REQ
SPT_CS_ACYR
SPT_GLA_FYR
SPT_GLS_FYR
SPT_GLS_M_ACTUALS
SPT_GLS_M_ALOC_BUDGET
SPT_GLS_M_APPR_BUDGET
SPT_GLS_M_CONT_BUDGET
SPT_GLS_M_ORIG_BUDGET
SPT_GLS_M_PO_ENCS
SPT_SL_ACYR
SPT_TA_ACYR
Run Incremental Refreshes for Data Model Updates
Incremental refreshes update only data that has changed since the last successful refresh of a given transform, instead of refreshing all of a transform’s source data. In addition, an incremental Filter Criteria can be specified on the DOFC form per transform to include additional data in an incremental refresh and lessen the need for frequent full refreshes.
Incremental Update of DataOrchestrator Transforms
When you are refreshing data from your Colleague source database to an operational data store using DataOrchestrator transforms, you can usually improve the overall performance of the refresh by running transforms as incremental updates. However, there are limits to the dependency checking in DataOrchestrator ODS used to identify the changed records in the source database that need to be included in an incremental update.
Specifically, only the non-computed column data elements in the source file will be checked for updates since the last refresh. This means that if the transform references data elements from other Colleague files (either directly
46 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the DataOrchestrator ODS Data Models
or through the use of computed columns), no dependency checking is done on these elements. Updates to their values will not automatically trigger the inclusion of these input elements in the incremental update.
Most of the transforms making up the DataOrchestrator data models can be incrementally updated and do not violate these conditions. As you identify particular transforms that are running slowly in your environment, you can choose to run these transforms in an incremental mode in order to improve performance.
However, there are a number of transforms in the data models that should not be run as incrementals. These transforms are delivered already set up so that each of these transforms will run as a full refresh. This is achieved by having the Refresh as Full Only (Y/N) field default as “Yes” for these transforms on the DataOrch Target Transform (DOTT) form. This is done because incremental execution of these transforms would affect the data integrity of the resulting tables on the target database, as they depend on the results of computed columns or dynamic data elements from tables other than the source Colleague file. Changes to this information would not necessarily be reflected on the operational data store using an incremental update method.
The specific transforms within the model that cannot be run in an incremental mode without risking the data integrity of the target ODS are listed in Table 8.
Table 8: Transforms Requiring a Full Refresh
Transform Name Dependent View
ODS_ACAD_CREDENTIALS None
ODS_APPLICANTS None
ODS_AR_ACCTS None
ODS_COURSE_SEC_FACULTY None
ODS_COURSE_SECTIONS None
ODS_FACULTY None
ODS_PER_AC_LP_ASGMTS None
ODS_PERBEN None
ODS_STUDENT_PLAN_STUDIES None
ODS_STUDENT_TERMS None
ODS_STUDENTS None
ODS_WAIT_LIST None
SPT_PERLVDTL ODS_PERLVDTL
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 47© 2010 Datatel, Inc.
Installation: Installing the DataOrchestrator ODS Data Models
Create Multiple Refreshes
Multiple refreshes can be associated to a single target (specified on the DOTA form). If specific transforms take longer to process than others, consider creating multiple refreshes to run at different scheduled times. Schedule the longer running transforms during off-peak operating hours or during weekends to lessen the refresh time for other transforms in the data model.
SPT_PERPOS ODS_PERPOS
SPT_PERSON_CORP ODS_PERSON
SPT_PERSON_NON_CORP ODS_PERSON
SPT_STUDENT_PROGRAM_MAJORS ODS_STUDENT_PROGRAM_MAJORS
SPT_STUDENT_PROGRAM_MINORS ODS_STUDENT_PROGRAM_MINORS
SPT_STUDENT_PROGRAM_SPECS ODS_STUDENT_PROGRAM_SPECS
SPT_STUDENT_PROGRAMS ODS_STUDENT_PROGRAMS
Table 8: Transforms Requiring a Full Refresh (cont’d)
Transform Name Dependent View
48 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting from the DataOrchestrator ODS Data Models
Using the DataOrchestrator ODS Data Models
Using the DataOrchestrator ODS Data Models
CORE Data Model
In This ChapterThis chapter provides an overview of all tables and views containing information from the Colleague Core (CORE) application used for reporting from the DataOrchestrator ODS.
Table 9 lists the topics covered in this chapter.
Table 9: Topics in This Chapter
Topic Page
CORE Reporting Subject Areas 52
Demographics 53
Academic Credentials and Graduation 56
Data Model Diagram 58
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 51© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: CORE Data Model
CORE Reporting Subject AreasIn CORE, there are two subject areas available for reporting:
Demographics
Academic Credentials and Graduation
For these subject areas, the following information is provided for the associated tables and views:
The name of the table or view in the ODS target database.
The transform in the target which must be run to extract data for the table or view. For tables, the transform name is the name of the table. For views, one or more transforms create supporting tables on which the view is built. These supporting tables are indicated by a prefix of SPT_; however, they are not used for reporting. Datatel recommends that only tables or views that begin with the prefix of ODS_ be used for reporting.
A description of the table or view, its source in Colleague, and its reporting use.
In addition, a data model diagram is provided for the CORE application showing the relationship of tables within the subject areas and their individual fields.
Technical Tip: The supporting tables are necessary for improved performance of transforms or because of restrictions on the data transformation of the DataOrchestrator ODS. Datatel may restructure or eliminate these supporting tables in future ODS releases. Therefore, use only the tables or views with the ODS_ prefix for reporting.
52 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
CORE Reporting Subject Areas
Demographics
Description
This subject area provides basic demographic data about an individual or organization. The types of questions you can answer from the information in this subject area are:
What is the name or preferred name, address, gender, phone, e-mail, and contact information for a person or organization?
What addresses are available for a person and of what type? What is the preferred address?
What is the emergency contact information for a person?
What is the race/ethnic classification of a student or institution employee that is required to be reported in federal IPEDS reports?
You have the ability to filter this information by the following criteria:
Address type
Ethnicity
Marital status
Gender
Alien status
Citizenship
Age
Primary Colleague Source Files PERSON
ADDRESS
FOREIGN.PERSON
PHONE
CORP.FOUNDS
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 53© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: C
OR
E D
ata Mo
del
54R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and view for demographics are provided in Table 10.
scription
SON file, the ODS_PERSON view has with a person or organization in
nd e-mail information. Whenever a query at has a Person ID, a join can be done to mation. Queries pertaining to gender, sing ODS_PERSON.
iation of the Colleague PERSON file. It particular address, such as phone e would be primarily used for phone S_ADDRESS table.
sociated to a person rather than an ONE association of the Colleague ones with those in would give the complete list of phone
e, this table provides the attributes of a e/province, postal code, country, etc. N file ID, it would typically not be queried DS_PERSON_ADDRESS_INFO. or date, it may not be a current address; are provided on the table.
Table 10: Demographics Tables and View
Table/View Transforms De
ODS_PERSON (View) SPT_PERSON_CORP
SPT_PERSON_NON_CORP
SPT_CORP_FOUNDS
SPT_FOREIGN_PERSON
Primarily from the Colleague PERdemographic attributes associatedColleague, in addition to spouse ais done from a table in the ODS thODS_PERSON to get name inforethnicity, etc., can also be done u
ODS_PERSON_ADDRESS_INFO (View)
SPT_PERSON_ADDRESS_INFO This is from the PSEASON assochas the attributes of a person at anumber or address type. This tablnumbers and for joining to the OD
ODS_PERSON_PHONE Same as table name. These are the phone numbers asaddress, and are from the PERPHPERSON file. Combining these phODS_PERSON_ADDRESS_INFOnumbers for a person.
ODS_ADDRESS Same as table name. From the Colleague ADDRESS filstreet address, including city, statBecause this table has no PERSOdirectly, but rather by a join with OBecause an address has no type the types and dates for addressesODS_PERSON_ADDRESS_INFO
CORE Reporting Subject Areas
Transform Customization Steps
The following customization steps may need to be taken before exporting the transforms in the demographics subject area.
Output column HIERARCHY_ADDRESS in the ODS_PERSON view is included to identify the appropriate Address records, based on an institution’s address hierarchy configuration. By default, however, this output column is disabled in the delivered transforms due to the potential performance cost of executing the computed columns to determine this hierarchy address. If enabled, this column will call the PERSON.MAIL.ADDRESS computed column, which invokes the S.GET.ADDR.INFO.ORACLE subroutine to determine the standard hierarchy address. To enable this field, you will need to take the following steps:
Using the DataOrch Target Transform (DOTT) form, detail on the Column Definitions field to the Transform Columns (DOTC) form and enter Yes as follows:• In the Export (Y/N) field on the HIERARCHY_ADDRESS column in the
SPT_PERSON_NON_CORP target transform. • In the Export (Y/N) field on the HIERARCHY_ADDRESS column in the
SPT_PERSON_CORP target transform.
Using the DataOrch View Spec (DOVS) form, edit the ODS_PERSON view. Remove the “null as” string on the two lines of this view definition that define the HIERARCHY_ADDRESS output fields of the view.
If you want to use a different method of choosing an address to associate with a person or organization, you may replace the reference to this computed column. To do this, change the Source Field column on the DataOrch Transform Columns (DOTC) entry for the HIERARCHY_ADDRESS output column in the SPT_PERSON_CORP and SPT_PERSON_NON_CORP transforms. When making this change, do not change the name of the HIERARCHY_ADDRESS transform output column, as this will cause an error with the view creation for ODS_PERSON.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 55© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: CORE Data Model
Academic Credentials and Graduation
Description
This subject area provides information about the academic credentials for the faculty, graduates, and applicants of the institution. The types of questions you can answer from the information in this subject area are:
Who graduated and from what institutions? What was their age? Gender? Name? Location? Residency state or preferred address state?
What did they graduate in? What program? Certificate? Degrees? Majors? Minors? Academic level? Location?
What honors or awards were received?
What was the graduate’s GPA? Total credits? What was the graduating class or cohort’s average GPA?
How many years (or terms) did it take for them to graduate?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
School, division, and department
Location
Cohort
Degrees, certifications, and majors
Classification of Instructional Programs (CIP) code
Alien status
Citizenship
Major GPA
Primary Colleague Source Files ACAD.CREDENTIALS
ACAD.PROGRAMS
STUDENT.PROGRAMS
INSTITUTIONS.ATTEND
ROOMS
56 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
CO
RE
Rep
ortin
g S
ub
ject Areas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
57©
2010 Datatel, Inc.
in Table 11.
It could be used to query faculty and of Ph.D.s in the faculty, or in all staff. ny students received what types of institution that awarded the uation.
league ACAD.CREDENTIALS file to
league ACAD.CREDENTIALS file to
file, this is used to get the ulty, staff, alumnus, etc., at another plicants and prospects from a awarded, then the rank could come
r educational rooms at the institution, where they are located. It also capacity.
The reporting tables for Academic Credentials and Graduation are provided
Table 11: Academic Credentials and Graduation Tables
Table Transforms Description
ODS_ACAD_CREDENTIALS Same as table name. This is from the Colleague ACAD.CREDENTIALS file. graduate credentials to view, for example, the number Graduate credentials could be queried to view how madegrees or certificates. This table also has the ID of thecredential, as well as GPA and rank information at grad
ODS_ACAD_CRED_MAJORS Same as table name. One row per entry in the ACAD.MAJORS list in the Colallow query on credentials by major.
ODS_ACAD_CRED_MINORS Same as table name. One row per entry in the ACAD.MINORS list in the Colallow query on credentials by minor.
ODS_INSTITUTIONS_ATTEND Same as table name. Primarily from the Colleague INSTITUTIONS.ATTENDattendance date or years of any applicant, student, facinstitution. The rank is available, mostly for use with apsecondary school. If an academic credential has been from ODS_ACAD_CREDENTIALS instead.
ODS_ROOMS Same as table name. Provides information on all the assignable residential oproviding information about the building, floor, or wing provides information about the type of the room and its
Using the DataOrchestrator ODS Data Models: CORE Data Model
Data Model Diagram
A data model diagrams provides structural views of the reporting tables and views. This diagram shows each table or view in the operational data store containing Colleague data to be used for reporting.
Each table or view is represented by a rectangle, labeled with the table or view name, and with a list of the columns of the table or view, in addition to the data type of each. Also, the logical relationships between database tables and views are shown by arrows pointing from the referencing object to the referenced object. Each arrow is annotated with the column from the source table or view that equates to the key field of the referenced table or view.
Although these relationships are shown in the data model diagram, they are not implemented as foreign key relationships in the database in all cases. Users accessing these tables from SQL queries or SQL-based reporting tools will need to include these join conditions in their reports.
Figure 4 on page 59 and Figure 5 on page 60 show the data model diagram for the CORE application.
58 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
CORE Reporting Subject Areas
Figure 4: Data Model Diagram for the CORE Application (Demographics)
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 59© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: CORE Data Model
Figure 5: Data Model Diagram for the CORE Application (Academic Credentials)
60 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
CORE Reporting Subject Areas
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 61© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: CORE Data Model
62 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models
Student Data Model
In This ChapterThis chapter provides an overview of all tables and views containing information from the Colleague Student application used for reporting from the DataOrchestrator ODS.
Table 12 lists the topics covered in this chapter.
Table 12: Topics in This Chapter
Topic Page
Student Reporting Subject Areas 64
Applicants and Applications 65
Courses and Faculty 68
Enrollment, Academic Programs, Test Scores, and Room Assignments
72
Billing 79
Financial Aid (FA) 82
Data Model Diagrams 86
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 63© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Student Data Model
Student Reporting Subject AreasIn Colleague Student, there are five subject areas available for reporting:
Applicants and Applications
Courses and Faculty
Enrollment, Academic Programs, Test Scores, and Room Assignments
Billing
FA
For these subject areas, the following information is provided for the associated tables and views:
The name of the table or view in the ODS target database.
The transform in the target which must be run to extract data for the table or view. For tables, the transform name is the name of the table. For views, one or more transforms create supporting tables on which the view is built. These supporting tables are indicated by a prefix of SPT_; however, they are not used for reporting. Datatel recommends that only tables or views that begin with the prefix of ODS_ be used for reporting.
A description of the table or view, its source in Colleague, and its reporting use.
In addition, data model diagrams are provided for Colleague Student showing the relationship of tables within the subject areas and their individual fields.
Technical Tip: The supporting tables are necessary for improved performance of transforms or because of restrictions on the data transformation of the DataOrchestrator ODS. Datatel may restructure or eliminate these supporting tables in future ODS releases. Therefore, use only the tables or views with the ODS_ prefix for reporting.
64 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Applicants and Applications
Description
This subject area provides information about prospects and applicants and their applications to programs of study at the institution. The types of questions you can answer from the information in this subject area are:
How many prospects are there? How many applicants?
For what programs have they applied or expressed interest? For what majors and minors? For what given term, level, school, department, or location?
What is the age, ethnicity, gender, state, ZIP code, application status, level, and feeder institution breakdowns?
Who are the admissions representatives, and what are the recruitment territories, for these prospects, applicants, and applications?
What is the origin of this application? How was it received, or how did the applicant hear about the institution?
What are the applicant’s GPA, test scores, or other measures of academic performance?
What are the types of applications received? (That is, are students applying to the school through an electronic application or online?
What statuses did each application go through and when did the application change from one status to the next? What was the life cycle of each application?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
Program, majors, and minors
Current application status
School, division, and department
Ethnicity, gender, state, ZIP code, and feeder institution
GPA and test scores
Application type
Admit Status
Alien status
Citizenship
Age
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 65© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Student Data Model
Primary Colleague Source Files APPLICANTS
APPLICATIONS
ACAD_PROGRAMS
PERSON
66 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
67R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables for applicants and applications are provided in Table 13.
tion
ICANTS file, the ODS_APPLICANTS tative or current recruiter.
CATIONS file, the ODS_APPLICATIONS c program in which an applicant or ed information is also included, such as rce, etc. Using the person’s Colleague ID joins with the ccesses major and minor information.
ncluded. Unknown statuses are defined TATUS field that point to a record in the ose s null.
.TO.APPLY list in the Colleague what influenced an applicant’s interest in ith an unknown status are not included.
t in the Colleague APPLICATIONS file to en application has gone through. This
ion funnel.
Table 13: Applicants and Applications Tables
Table/View Transforms Descrip
ODS_APPLICANTS Same as table name. Extracted primarily from the Colleague APPLtable allows querying by admissions represen
ODS_APPLICATIONS Same as table name. Extracted primarily from the Colleague APPLItable contains information about the academiprospect is interested. Other application-relatadmission information, status, application souand the application academic program allowsODS_STUDENT_PROGRAMS table, which aApplications with an unknown status are not ito be those values of the APPL.CURRENT.SAPPLICATION.STATUSES Colleague file whAPPS.SPECIAL.PROCESSING.CODE field i
ODS_APPLICATION_INFLUENCES Same as table name. One row per entry in the APPL.INFLUENCEDAPPLICATIONS file to allow you to query on the institution. Influences from applications w
ODS_APPL_STATUS_HISTORY Same as table name. One row per entry in the APPL.STATUSES lisallow query of the series of statuses that a givallows basic historic reporting on the applicat
Using the DataOrchestrator ODS Data Models: Student Data Model
Courses and Faculty
Description
This subject area provides information on student enrollment from the course sections perspective. This offers an efficient way to query and do reports that are focused on the section rather than the student.
The types of questions you can answer from the information in this subject area are:
What courses are being offered? By term, school, division, and department?
How many sections of a given course are being offered?
How many sections have been canceled? By term, school, division and department?
What percentage of the sections offered were canceled?
How many students are in the courses? Average number in a course?
What is the percentage of seat capacity utilization?
What sections are over-enrolled? And by how much?
What sections have waitlists?
When and where does each course section meet and what instructional methods are used?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
Current status
School, division, and department
Location
Major and minor of the student
Classification of Instructional Programs (CIP) code
Faculty and instructors
68 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
This subject area also provides basic demographic and segment data about faculty and their institutional obligations. The types of questions you can answer from this information are:
How many faculty are teaching this term?
What is their load?
What is their status?
How many courses and what courses are they teaching?
How many students are they teaching?
How many students are they advising?
What other non-teaching engagements do they have?
How much were they paid for their teaching?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
School, division, and department
Location
Tenure status
Qualifications, certifications, etc.
Primary Colleague Source Files COURSE.SEC.FACULTY
COURSE.SEC.MEETING
COURSE.SECTIONS
FACULTY
PERSON
TERMS
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 69© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
70R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and view for Courses and Faculty are provided in Table 14.
escription
primarily from the Colleague lows querying by course section ow many course sections are offered by nts are actively enrolled in the course
r entry in the SEC.COURSE.TYPES list CTIONS file. Used in conjunction with the lows querying of course section ype.
ague COURSE.SEC.MEETING file. It scheduling attributes, such as finding ill have meet in, the instructional ill be taught, etc.
primarily from the Colleague This Colleague file has a record for each r, and instructional method combination. see how many course sections a faculty
by course section must be done if faculty structional methods per course section.
ow much a faculty member is paid for an
arily from the Colleague FACULTY file. It uch as number of advisees, special man Resources (HR) application is o be queried.
Table 14: Courses and Faculty Tables and View
Table Transforms D
ODS_COURSE_SECTIONS Same as table name. The information in this table is COURSE.SECTIONS file. It alattributes, such as finding out ha department, how many studesection, etc.
ODS_COURSE_SECTION_TYPES Same as table name. This table contains one row pein the Colleague COURSE.SECOURSE.SECTIONS file, it alinformation by course section t
ODS_COURSE_SECTION_MTG Same as table name. This is primarily from the Colleallows query by course sectionout which building the course wmethods by which the course w
ODS_COURSE_SEC_FACULTY Same as table name. The information in this table is COURSE.SEC.FACULTY file. course section, faculty membeEach record becomes a row. Tomember is teaching, grouping members can teach multiple in
This also allows querying on hassignment.
ODS_FACULTY Same as table name. Information in this table is primallows querying on attributes sstatus, etc. If the Colleague Huavailable, tenure status can als
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
71R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
mes from two Colleague files: d CAMPUS.ORG.MEMBERS. It has one .LOAD association of ose CMPA.LOADS is not null, and one
ORG.ROLES association of ose CMPM.LOAD field is not null. View
MT can be used to query the non-ber, etc. To see how much a faculty signment, the contract assignment d to join with the table to get the paid amount.
e Colleague WAIT.LIST file and can be tc.
ERM file, this can be used to determine ssociated reporting term, and term
Table 14: Courses and Faculty Tables and View (cont’d)
escription
ODS_NON_TEACHING_ASGMT (View)
SPT_NON_TEACH_ASGMT_CMPA
SPT_NON_TEACH_ASGMT_CMPM
The information in this view coCAMPUS.ORG.ADVISORS anrow per entry in the ADVISORCAMPUS.ORG.ADVISORS whrow per entry in the CAMPUS.CAMPUS.ORG.MEMBERS whODS_NON_TEACHING_ASGteaching load of a faculty memmember is being paid for an aspointer in this table can be useODS_PER_AC_LP_ASGMTS
ODS_WAIT_LIST Same as table name. The data in this table is from thused to query waitlist counts, e
ODS_TERMS Same as table name. Primarily from the Colleague Tterm start and end dates, the adescriptions for reports.
Table Transforms D
Using the DataOrchestrator ODS Data Models: Student Data Model
Enrollment, Academic Programs, Test Scores, and Room Assignments
Description - Enrollment
This subject area includes enrollment and provides basic demographic data about the students and their courses. The types of questions you can answer from the information in this subject area are:
How many students do we have actively taking a course? In a given term?
What courses are being taken? In a given term, level, school, department, or location?
How many credit hours are being taken by a student, and what are the credit hour calculations across the student population or a selected population?
What are the characteristics of the students in the selected courses? Their age, gender, location, state, name, class level, enrollment status, major?
What grades did students get for their courses? Averages by term, age, gender, location, class level, enrollment status, major?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
Current status
School, division, and department
Credit type
Student type
Major and minor
Alien status
Citizenship
Student standing
Age
72 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Description - Academic Programs
This subject area also includes academic programs and provides the ability to get breakdowns of students and the course of study in which they are currently enrolled. The types of questions you can answer from this information are:
How many students are active in a given academic program?
Which of the programs students are in are degree granting? Non-degree granting? Certificate?
How many active academic programs are offered by a given school, division, department, or location, and what are they?
How many students are in academic programs managed by a given school, division, department, or location?
What is the average GPA of students in a given program?
What is the total number of credits or average number of credits a student has taken in a given academic program?
You have the ability to filter this information by the following criteria:
Date range of when the program was active or not
Current status or active program
Academic level
School, division, and department
Location
Program
Type
Degree granting
Classification of Instructional Programs (CIP) code
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 73© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Student Data Model
Description - Test Scores
This subject area also includes test scores and provides the ability to view what tests (with results) a student has taken. This area also provides for general analysis of test results across student demographic segments. The types of questions you can answer from this information are:
Which students have taken or provided a given test, placement, portfolio?
What tests has a student taken and what are the status and score results?
How many times did they take the test?
What is the average score of a given segment of students (age, gender, program, class level, academic level, location, cohort, or other demographics) for a given test or selection of tests?
You have the ability to filter this information by the following criteria:
Date ranges of when tests were taken and when reported
Score ranges
Academic level
School, division, department
Location
Non-course or test
Academic standing
Demographic characteristics of age, gender, enrollment status, program, class level, academic level, location, admission status, cohort, citizenship, alien status, etc.
74 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Description - Room Assignments
This subject area also includes information on residential student room and building assignments. This allows analysis of the capacity and occupancy rates of campus residential buildings. The types of questions you can answer from this information are:
How many students currently reside in the residence halls?
How many students have been assigned space or been waitlisted for the upcoming term?
How many empty beds are there?
You have the ability to filter this information by the following criteria:
Start and end dates of resident hall room assignments
Types of residential rooms (number of beds)
Buildings, floors, and wings where rooms are located
Primary Colleague Source Files STUDENT.ACAD.CRED
STUDENT.COURSE.SEC
COURSE.SECTIONS
COURSES
PERSON
STUDENTS
STUDENT.PROGRAMS
ACAD.PROGRAMS
STUDENT.NON.COURSES
NON.COURSES
STUDENT.PLAN.STUDIES
ROOM.ASSIGNMENT
ROOMS
BLDGS
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 75© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
76R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and views for Enrollment, Academic Programs, Test Scores, and Room Assignments is
Description
table is primarily from the Colleague le. It allows querying by academic ch as finding out how many academic or what degrees are available by data for currently active academic n this table.
e row per entry in the ACPG.LOCATIONS CAD.PROGRAMS file to allow querying ocations of currently active programs are
table is primarily from the Colleague table can be used to query by attributes esidency status, student type, etc., or ction with the ODS_PERSON view to ation such as name, address, etc.
view is primarily from the Colleague D file, but only includes records for course section. Dropped, withdrawn, or t included. Also, transfer credit or any
c credit not linked to a course section are sed to derive enrollment statistics, or to queries against active enrollment
provided in Table 15.
Table 15: Enrollment, Academic Programs, Test Scores, and Room Assignments Tables and Views
Table Transforms
ODS_ACAD_PROGRAMS Same as table name. The information in thisACAD.PROGRAMS fiprogram attributes, suprograms are offered department, etc. Only programs is included i
ODS_ACAD_PROGRAM_LOCS Same as table name. This table contains onlist in the Colleague Aby location. Only the lincluded in the table.
ODS_STUDENTS Same as table name. The information in thisSTUDENTS file. This of a student, such as rcan be used in conjunextract personal inform
ODS_STUDENT_ENROLLMENT (View)
SPT_STUDENT_ACAD_CRED
SPT_STUDENT_ACAD_LEVELS
SPT_STUDENT_COURSE_SEC
The information in thisSTUDENT.ACAD.CREactive enrollments in adeleted records are noother type of academinot included. This is usupport other kinds ofinformation.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
77R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
table is primarily from the Colleague RSES file, and can be used to obtain test . Withdrawn noncourse work is not
(Withdrawn work is signified in the RSES file by a record whose value in the as a special processing ENT.NON.COURSE.STATUSES .
ormation primarily from the Colleague DIES file, which contains information on tional plans. It can be used for querying are planning on taking in a given term.
view is primarily from the Colleague S file. Information for students who have the program, or who are otherwise
ic program, is not included in the view. to get counts of students by academic students, academic standing for the r the program are provided to support ndings by academic programs and view also contains data for prospects or
the majors of an GRAMS row. The majors come both
ACAD.PROGRAMS record associated am, and also with any current majors in f the Colleague STUDENT.PROGRAMS conjunction with the GRAMS view to query by major.
Table 15: Enrollment, Academic Programs, Test Scores, and Room Assignments Tables and Views (cont’d)
Description
ODS_STUDENT_NON_COURSES (View)
SPT_STUDENT_NON_COURSES The information in theSTUDENT.NON.COUor portfolio informationincluded in this table. STUDENT.NON.COUSTNC.STATUS field hcode of 1 in the STUDvalidation code table.)
ODS_STUDENT_PLAN_STUDIES Same as table name. This table contains infSTUDENT.PLAN.STUcurrent student educawhat courses students
ODS_STUDENT_PROGRAMS (View)
SPT_STUDENT_PROGRAMS
SPT_ACAD_PROGRAMS
SPT_STUDENT_STANDINGS
The information in thisSTUDENT.PROGRAMalready graduated fromdone with the academThis view can be usedprograms. For currentacademic level and foquery of academic staacademic levels. Thisapplicants.
ODS_STUDENT_PROGRAM_MAJORS (View)
SPT_STUDENT_PROGRAM_MAJORS
SPT_STU_PROG_ADDNL_MAJORS
This view provides all ODS_STUDENT_PROfrom the majors in thewith the student progrthe additional majors ofile record. It is used inODS_STUDENT_PRO
Table Transforms
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
78R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
minors of an GRAMS row. The minors come both
Colleague ACAD.PROGRAMS file, minors in the minors list of the Colleague S file, and can be used to perform PROGRAMS data by minor.
ializations of an GRAMS row. The specializations come s in the Colleague ACAD.PROGRAMS rent specializations in the specializations TUDENT.PROGRAMS file.
s of an ODS_STUDENT_PROGRAMS from the CCDs in the Colleague le, along with any current CCDs in the gue STUDENT.PROGRAMS file.
he STA.OTHER.COHORTS list in the ACAD.LEVELS file to allow querying by cohort groups.
TUDENT.TERMS file, MS allows querying of term-based current academic standing, load, etc.
OOM.ASSIGNMENTS file, this table n the assignment of students to ause this data is used to provide m utilization, all current and future room
re included.
Table 15: Enrollment, Academic Programs, Test Scores, and Room Assignments Tables and Views (cont’d)
Description
ODS_STUDENT_PROGRAM_MINORS (View)
SPT_STUDENT_PROGRAM_MINORS
SPT_STU_PROG_ADDNL_MINORS
This table provides theODS_STUDENT_PROfrom the minors in thealong with any currentSTUDENT.PROGRAMqueries of STUDENT_
ODS_STUDENT_PROGRAM_SPECS (View)
SPT_STUDENT_PROGRAM_SPECS
SPT_STU_PROG_ADDNL_SPECS
This provides the specODS_STUDENT_PROfrom the specializationfile, along with any curlist of the Colleague S
ODS_STUDENT_PROGRAM_CCDS (View)
SPT_STUDENT_PROGRAM_CCDS
SPT_STU_PROG_ADDNL_CCDS
This provides the CCDrow. The CCDs come ACAD.PROGRAMS fiCCD list of the Collea
ODS_STUDENT_OTHER_COHORTS Same as table name. One row per entry in tColleague STUDENT.institutionally-defined
ODS_STUDENT_TERMS Same as table name. From the Colleague SODS_STUDENT_TERattributes, such as the
ODS_ROOM_ASSIGNMENTS Same as table name. From the Colleague Rprovides information oresidential rooms. Becreporting on future rooassignment records a
Table Transforms
Student Reporting Subject Areas
Billing
Description
This subject area provides the ability to list the charges and types to students and their balances. The types of questions you can answer from the information in this subject area are:
How much was billed to students for a given AR.CODE? How many students were charged for a given code and how much? What is the total?
What were the student’s total charges for a given term?
What payments were made by a student and for what charges were they applied? What is the total of these payments?
What is the account balance for students for a given term as of now?
What deposits have students made for what purposes?
Which students are on payment plans, and what is the current balance of those plans?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
School, division, and department
Location
AR type
AR code
Primary Colleague Source Files AR.INVOICES
AR.INVOICE.ITEMS
AR.PAYMENTS
AR.PAYMENT.ITEMS
AR.ACCTS
AR.PAY.PLANS
AR.DEPOSITS
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 79© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
80R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and views for Billing are provided in Table 16.
escription
S file, this allows querying on AR ance includes all charges and payments results in the balance as of the time of that have either invoices or payments not
ct this table could take a long time. If , then ODS_AR_ACCTS is not needed, n, because a balance can be calculated MS and ODS_AR_PAYMENT_ITEMS.
.INVOICE.ITEMS file. It allows query by . If payment plans are not involved, then etermined by summing the charges and subtracting the payment amounts and mounts from allocated to the invoice item. Only posted
item description is broken out into its n billing item, that second line of the e section name, which might be useful in ROLLMENT by using the Colleague
nd the section name.
R.PAYMENT.ITEMS file, can be used to query payment located payment items. Only posted
Table 16: Billing Tables and Views
Table/View Transform(s) D
ODS_AR_ACCTS Same as table name. From the Colleague AR.ACCTbalances by AR type. The balregardless of time period, andextraction. Only AR accounts yet archived are included.
Running the transform to extrapayment plans are never usedand the extract need not be rufrom ODS_AR_INVOICE_ITE
ODS_AR_INVOICE_ITEMS (View)
SPT_AR_INVOICE_ITEMS
SPT_AR_INVOICES
This is from the Colleague ARcharge (AR) codes and termsan invoice balance could be dcredits by invoice number, andadding the payment reversal aODS_AR_PAYMENT_ITEMS invoice items are included.
The second line of the invoiceown field. If this is a registratiodescription would be the coursjoining to ODS_STUDENT_ENperson ID, the term (if any), a
ODS_AR_PAYMENT_ITEMS (View)
SPT_AR_PAYMENT_ITEMS
SPT_AR_PAYMENTS
Primarily from the Colleague AODS_AR_PAYMENT_ITEMS characteristics, including unalpayment items are included.
Stu
den
t Rep
ortin
g S
ub
ject Areas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
81©
2010 Datatel, Inc.
m. ODS_AR_TERM_BAL does not ut simply reports the overall AR
OICES file. It supports querying on s access to AR invoice-level n added to Colleague to simplify
OSITS file. It allows querying on e and the academic term to which ser who entered the deposit is also termine if the deposit was made
.PLANS file. It allows querying of s.
r a student is stored as a calculated
ription
ODS_AR_TERM_BALANCE (View)
SPT_AR_INVOICE_ITEMS
SPT_AR_INVOICES
SPT_AR_PAYMENT_ITEMS
SPT_AR_PAYMENTS
SPT_AR_CODE_TAX_GL_DIST
This is a balance by student by tertake into account payment plans, bbalance on a term basis.
ODS_AR_INVOICES (View)
SPT_AR_INVOICES This is from the Colleague AR.INVindividual AR invoices and providesummary information that has beequeries.
ODS_AR_DEPOSITS Same as table name. This is from the Colleague AR.DEPstudent AR deposits by deposit typthe deposit applies. The ID of the uincluded, as this can be used to deover the web.
ODS_AR_PAYMENT_PLANS Same as table name. This is from the Colleague AR.PAYpayment plan by AR type and term
The balance of the payment plan fovalue.
Table 16: Billing Tables and Views (cont’d)
Table/View Transform(s) Desc
Using the DataOrchestrator ODS Data Models: Student Data Model
Financial Aid (FA)
Description
This subject area provides the ability to list the students who get financial aid, describe their basic demographic characteristics, count them, and list the awards, types, and amounts. The types of questions you can answer from the information in this subject area are:
Which students receive aid?
How many students receive aid?
What are the awards (scholarships, loans, grants) and types of financial aid received and the amounts?
Who received scholarships and for how much?
Who received loans and for how much?
How much for each loan category has been awarded/offered to students? How much has been disbursed to student accounts?
For which loans has a student had an entrance interview?
How many loans have been sent to the lenders for certification or origination? How many loans have not completed processing and need attention?
How many ISIRs have been received for a given year?
What is a student’s satisfactory academic progress (SAP) status?
How many correction ISIRs have been sent to the CPS and how many updates received? How many have been selected for verification?
How many students are Pell eligible?
Who is a student’s financial aid counselor?
You have the ability to filter this information by the following criteria:
Term and date range
Academic level
School, division, and department
Location
Award
Award category
82 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Primary Colleague Source Files TA.ACYR
CS.ACYR
AWARDS
AWARD.PERIODS
FA.LOCATIONS
FA.OFFICES
SL.ACYR
ISIR.FAFSA
ISIR.DEMO
FIN.AID
SAP.RESULTS
FA.INTERVIEW
DLI.AWARD
DLE.AWARD
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 83© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: S
tud
ent D
ata Mo
del
84R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and view for FA are provided in Table 17.
cription
YR file suite file, this table includes ncluding award details, awarded information.
selected for the TA_ACYR file suite (in e combined into a single target table in ey and academic year fields. This is also e SPT_CS_ACYR transform). You must et of academic years to include for both t data in the ODS.
each ISIR record received from the CPS les its data from the collection of g ISIR information is stored.
information about a student that is in des information about the student’s SAP counselor.
information about a student which is in des the student's official dependency year, as well as the student's ISIR
official ISIR of record for the student for
Table 17: FA Tables and View
Table/View Transform(s) Des
ODS_FA_TERM_AWARDS (View)
SPT_TA_ACYR
SPT_CS_ACYR
Primarily from the Colleague TA_ACinformation on FA awards by term, iamounts, and FA location and office
Note that all the file suite instances the SPT_TA_ACYR transform) will bthe ODS with an additional unique ktrue of the CS_ACYR file suite (in thmake sure you select a consistent sfile suites in order to have consisten
ODS_ISIR_FAFSA (View)
SPT_ISIR_FAFSA This ODS view contains a record forby the institution. This view assembColleague files in which the incomin
ODS_CURRENT_FA_INFO (View)
SPT_FIN_AID
SPT_FIN_AID_COUNSELORS
This ODS view assembles basic FAeffect at the current time. This inclustatus and the student’s current FA
ODS_YEARLY_FA_INFO (View)
SPT_CS_ACYR The ODS view assembles basic FAeffect for a single FA year. This inclustatus and family contribution for theverification status and the ID of the the year.
Stu
den
t Rep
ortin
g S
ub
ject Areas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
85©
2010 Datatel, Inc.
his ODS view provides the most ual interview type as columns of a s ODS users to access (with simple
particular type of interview has taken
student loans, both CommonLine t loan for each year. Primarily from
s columns with data common to both ta specific to CommonLine loans, formation specific to Direct Loans, n award record.
tion
ODS_FA_INTERVIEWS (View)
SPT_FA_INTERVIEW Primarily from the FA.INTERVIEW file, tinterview dates of record for each individsingle record for each student. This allowjoins) information as to whether or not a place.
ODS_STUDENT_LOANS (View)
SPT_SL_ACYR
SPT_DLI_AWARD
This transform provides detailed data onand Direct, with a record for each studenthe SL.ACYR file, this transform providetypes of loans, and also columns with daincluding lender information, and other inincluding information from the Direct Loa
Table 17: FA Tables and View (cont’d)
Table/View Transform(s) Descrip
Using the DataOrchestrator ODS Data Models: Student Data Model
Data Model Diagrams
Data model diagrams provide structural views of the reporting tables and views. These diagrams show each table or view in the operational data store containing Colleague data to be used for reporting.
Each table or view is represented by a rectangle, labeled with the table or view name, and with a list of the columns of the table or view, in addition to data type of each. Also, the logical relationships between database tables and views are shown by arrows pointing from the referencing object to the referenced object. Each arrow is annotated with the column from the source table or view that equates to the key field of the referenced table or view.
Although these relationships are shown in the data model diagrams, they are not implemented as foreign key relationships in the database in all cases. Users accessing these tables from SQL queries or SQL-based reporting tools will need to include these join conditions in their reports.
86 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Figure 6 shows the data model diagram for the Applicants and Applications reporting subject area.
Figure 6: Data Model Diagram for the Applicants and Applications Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 87© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Student Data Model
Figure 7 shows the data model diagram for the Courses and Faculty reporting subject area.
Figure 7: Data Model Diagram for the Courses and Faculty Reporting Subject Area
88 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Figure 8 and Figure 9 on page 90 show the data model diagram for the Enrollment, Academic Programs, Test Scores, and Room Assignments reporting subject area.
Figure 8: Data Model Diagram for Enrollment and Test Scores
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 89© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Student Data Model
Figure 9: Data Model Diagram for Academic Programs and Room Assignments
90 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Student Reporting Subject Areas
Figure 10 shows the data model diagram for the Billing reporting subject area.
Figure 10: Data Model Diagram for the Billing Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 91© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Student Data Model
Figure 11 shows the data model diagram for the FA reporting subject area.
Figure 11: Data Model Diagram for the FA Reporting Subject Area
92 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models
HR Data Model
In This ChapterThis chapter provides an overview of all tables and views containing information from the Colleague HR application used for reporting from the DataOrchestrator ODS.
Table 18 lists the topics covered in this chapter.
Table 18: Topics in This Chapter
Topic Page
HR Reporting Subject Areas 94
Employee Information 95
Payroll Information 102
Data Model Diagrams 104
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 93© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: HR Data Model
HR Reporting Subject AreasIn Colleague HR, there are two subject areas available for reporting:
Employee Information
Payroll Information
For these subject areas the following information is provided for the associated tables and views:
The name of the table or view in the ODS target database.
The transform in the target which must be run to extract data for the table or view. For tables, the transform name is the name of the table. For views, one or more transforms create supporting tables on which the view is built. These supporting tables are indicated by a prefix of SPT_; however, they are not used for reporting. Datatel recommends that only tables or views that begin with the prefix of ODS_ be used for reporting.
A description of the table or view, its source in Colleague, and its reporting use.
In addition, data model diagrams are provided for Colleague HR showing the relationship of tables within the subject areas and their individual fields.
Technical Tip: The supporting tables are necessary for improved performance of transforms or because of restrictions on the data transformation of the DataOrchestrator ODS. Datatel may restructure or eliminate these supporting tables in future ODS releases. Therefore, use only the tables or views with the ODS_ prefix for reporting.
94 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
HR Reporting Subject Areas
Employee Information
Description
This subject area provides basic information about employees, where they work in the institution, and what roles they have. The types of questions you can answer are dependent on what features you are using in Colleague. For example, contract information is best pulled from the data defined when using Assignment Contracts. Examples of questions you can answer from the information in this subject area are:
How many employees are there in the institution?
What current positions do employees have? Where and in what departments, divisions, and campuses of the institution do they work?
Who is the employee’s supervisor?
How can these employees be contacted? What is their physical work location?
How long have they worked for the institution and when did they start?
What type of contract does the institution have with the employee?
What is the status of each employee, primary position, primary position location, primary position department, name of employee, and e-mail address?
You have the ability to filter this information by the following criteria:
Date and year
Department, division, campus, school, and location
Position ID and Position Title
Supervisor Name and Supervisor Position ID
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 95© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: HR Data Model
This subject area also includes benefits and provides information on the elected benefits of each employee and their associated amounts paid by the employee and employer. The types of questions you can answer from this information are:
General Benefits Questions
Who is enrolled in each benefit?
Who is enrolled in a specific benefit?
What benefits does a specific employee have?
Who is the benefit provider and what is the amount remitted to the vendor in a given month, quarter, and year?
What is the portion of the current benefit cost paid by the employer and what portion by the employee?
What employee benefit plan changes have been made from one date to another (that is, did a new benefit plan get added for a specific employee and did the employee drop a benefit plan during the time frame elected?)
Which employees don’t have a specific benefit?
In a specific quarter of the preceding year, who was on the health plan and how does that compare to a specific quarter of this year?
Who are the new employees this month? Have they enrolled in benefits?
Which employees don't have any benefits?
What new benefit enrollments are there for employees during the date range with effective enrollment dates?
What new benefit cancel dates are there for employees during the date range with effective cancel dates?
COBRA Questions
COBRA Participant report: Who is currently enrolled in COBRA coverage, what was their COBRA Qualifying Event, Event Date, Election Coverage Date, and Eligibility Notification Date?
COBRA Decline report: Who had a COBRA Qualifying Event and what date was the Eligibility Notification Date and Decline Coverage Date? What was the COBRA Qualifying Event?
Who is on the list of all COBRA notifications sent (with dates) for a full year (which would be the Eligibility Notification Date)? What are the names of the employees, COBRA Qualifying Event, and Eligibility Notification Date? Did the persons elect or decline coverage, and what was the date they elected or declined?
96 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
HR Reporting Subject Areas
Which employees have COBRA dependents, and what are the names of the employees, COBRA qualifying events, election coverage dates, and names of COBRA dependents?
Which employees have COBRA coverage?
Who is eligible for COBRA coverage?
Who has declined COBRA coverage?
Leave Questions
How much family and medical leave has a particular employee taken over the past twelve months?
What is the vacation leave balance of all employees as of the end of the last year?
What employees are in a particular department, and what is their respective leave plans and leave plan accrual rate, as well as leave plan balances as of a specific date?
How much leave does an employee have?
How much leave has an employee taken?
What is the total outstanding leave for all employees for a particular leave plan?
You have the ability to filter this information by the following criteria:
Date and year
Department, division, campus, school, and location
Position and pay grade
Benefit providers
Benefit type
Primary Colleague Source Files HRPER
PERPOS
PERBEN
PERPOSWG
PERSTAT
PERLEAVE
PERLVDTL
POSITION
PAC.LP.ASGMTS
PAC.LP.POSITIONS
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 97© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: H
R D
ata Mo
del
98R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and views for Employee Information are provided in Table 19.
Description
eague HRPER file, this table includes ated employees, as well as current can be filtered on the effective nation date fields to report information on of the institution. It can be used to count , or get an average length of service, etc.
eck address on the Payroll Other rm, this table contains information from file for currently active employees. It ddress type and effective dates, and is _HRPER table in order to allow unions _PERSON_ADDRESS_INFO table to
a person's addresses.
eck address on the Payroll Other rm, this is from the Colleague HRPER file ployees. This table represents the check te from the ODS_HRPER table in order s with ODS_ADDRESS to get a full view resses.
view is primarily from the Colleague querying by employees by their positions rrent employee positions are included.
Table 19: Employee Information Tables and Views
Table/View Transforms
ODS_HRPER Same as table name. Primarily from the Collboth future and terminemployees. This table employment and termithe current employeesemployees by location
ODS_HR_CHECK_ADDRESS_INFO Same as table name. If you maintain your chInformation (CHCK) fothe Colleague HRPERrepresents the check aseparate from the ODSand joins with the ODSget a full view of all of
ODS_ HR_CHECK_ADDRESS Same as table name. If you maintain your chInformation (CHCK) fofor currently active emaddress, and is separato allow unions and joinof all of a person's add
ODS_PERPOS (View)
SPT_PERPOS
SPT_POSITION
SPT_ALL_POSITION_WAGES
SPT_PERPOSWG
The information in thisPERPOS file. It allowsand by wages. Only cu
HR
Rep
ortin
g S
ub
ject Areas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
99©
2010 Datatel, Inc.
P.ASGMTS file, this table is intended tion for a person’s assignments in
lication. If Colleague Student is not ersonnel interface is not used, then he ODS_PER_AC_LP_ASGMTS ncluded in the ODS refresh; overall ODS_PAYTODAT_EARN table can
e PAC.LP.POSITIONS file, this view ssignment contract load period tracts are not used, the transform to eed to be included in the ODS
e PERBEN file, this table allows yee's benefits by benefit type, ider, etc. This table contains ent active employee benefit nefits that have been canceled.
per entry in the COBQUAL e PERBEN file and allows querying ts of current benefits.
per entry in the f the Colleague PERBEN file for verage to allow querying on COBRA
is primarily from the Colleague querying on leave, leave balances, the comment field from the leave
e reason for leave adjustment
escription
ODS_PER_AC_LP_ASGMTS Same as table name. From the Colleague PAC.Lto provide payment informathe Colleague Student appavailable, or if the faculty/pthe transform that creates ttable does not need to be isalary information from thebe used instead.
ODS_PER_AC_LP_POSITIONS Same as table name. Primarily from the Colleaguallows querying based on apositions. If assignment concreate this table does not nrefresh.
ODS_PERBEN Same as table name. Primarily from the Colleaguquerying on all of an emploCOBRA dates, benefit provinformation both about currelections and also about be
ODS_PERBEN_COBQUAL Same as table name. This table contains one rowassociation of the Colleaguby COBRA qualifying even
ODS_PERBEN_COBRA_DPNDNTS Same as table name. This table contains one rowPERBEN.DEPEND.ID list oemployees with COBRA codependents.
ODS_PERLVDTL (View)
SPT_PERLVDTL
SPT_PERLEAVE
The information in this viewPERLVDTL file and allows etc. This view also providestransaction to determine thtransactions.
Table 19: Employee Information Tables and Views (cont’d)
Table/View Transforms D
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: H
R D
ata Mo
del
100R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
table is primarily from the Colleague a logical view of the PERPOSWG file; records whose STP.TYPE is “M”, e included. This allows queries on ot be reflected in the ODS_PERPOS n receiving a stipend may not have a
eague PERBENCS file, this table allows f the current benefit deduction costs for is table supports querying on the factors the benefit cost, including fixed oss salary percentage amounts, d amounts deducted based on AR
row per current entry in the BENDEDCS nformation for the benefits currently n. This table can be used to report on r not any employees currently have
ble provides all the cost factors for each ncluding the fixed deduction amounts, e amounts, and employee salary multiple vels.
the EMPLYRBASE and EMPLYEBASE lleague BENDEDCS file, this view d to calculate both employer and based on employee gross salary
be used in conjunction with the _CURRENT table (for reporting on an
benefit costs) or with the table (for benefits costs in general).
Table 19: Employee Information Tables and Views (cont’d)
Description
ODS_STIPEND Same as table name. The information in thisSTIPEND file, which ishowever, only current signifying a stipend, arstipends, which may ntable because a persoformal position.
ODS_PERBEN_COST_CURRENT Same as table name. Primarily from the Collquerying on the cost ospecific employees. Ththat go into calculatingdeduction amounts, grinsurance amounts, anaccount balances.
ODS_BENDED_COST Same as table name. This table contains onefile and provides cost ioffered by the institutiobenefit costs whether oselected them. This tabenefit as applicable, igross salary percentagfactors for insurance le
ODS_BENDED_COST_TABLES SPT_BENDED_TABLES_EMPLYR
SPT_BENDED_TABLES_EMPLYE
Primarily extracted fromassociations of the Coprovides the tables useemployee benefit costsranges. This table canODS_PERBEN_COSTindividual employee's ODS_BENDED_COST
Table/View Transforms
HR
Rep
ortin
g S
ub
ject Areas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
101©
2010 Datatel, Inc.
e REMITTANCES file, this table ts made by the institution to benefit
rts querying against this table over provides the ability to compare ent time periods.
Colleague PERLVACC and allows querying against employees' , including the current leave accrual nce.
specific skills that individual mented as having, and also
the dates of formal licenses , if any.
escription
ODS_REMITTANCES Same as table name. Primarily from the Colleaguallows querying on paymenproviders. The table suppothe institution's history, andthese payments over differ
ODS_LEAVE_ACCRUAL SPT_LEAVE_ACCRUAL
SPT_PERLEAVE
Primarily extracted from thePERLEAVE files, this view currently active leave plansrate and current leave bala
ODS_EMPLOYEE_JOB_SKILL Same as table name. Provides information on theemployees have been docuprovides information aboutassociated with those skills
Table 19: Employee Information Tables and Views (cont’d)
Table/View Transforms D
Using the DataOrchestrator ODS Data Models: HR Data Model
Payroll Information
Description
This subject area provides information about the earnings paid to employees and related tax information. The types of questions you can answer from this information are:
How much was paid to employees in a given pay period, month, quarter, calendar year, and year-to-date?
How much was paid by department, division, location, and school?
What types of compensation were paid: regular, bonus, etc.?
What taxes were paid by the employee and what types?
What was the benefit amount processed by payroll in a given pay period, month, quarter, calendar year, and year-to-date?
How much was paid by department, division, campus, location, and school?
You have the ability to filter this information by the following criteria:
Date and year
Department, division, campus, school, and location
Supervisor and Supervisor ID
Position, position ID, and pay grade
Pay cycle
Earning type
Tax type
Primary Colleague Source Files PAYTODAT
102 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
HR
Rep
ortin
g S
ub
ject Areas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
103©
2010 Datatel, Inc.
20.
scription
r entry in the PTDEARN association file to allow querying on what has and replaced records are not
r entry in the PTDBNDED AYTODAT file to allow querying on
or benefits. Voided and replaced
r entry in the PTDTAXES AYTODAT file to allow querying on ed and replaced records are not
r entry in the PTDBDEXP AYTODAT file to allow query on on behalf of employees for
records are not included.
r entry in the PTDTAXEXP AYTODAT file to allow query on behalf of employees. Voided and ded.
The reporting tables and views for Payroll Information are provided in Table
Table 20: Payroll Information Tables and Views
Table/View Transform(s) De
ODS_PAYTODAT_EARN (View)
SPT_PAYTODAT_EARN
SPT_PERPOSWG
SPT_POSITION
This view contains one row peof the Colleague PAYTODAT been paid to a person. Voidedincluded.
ODS_PAYTODAT_EMPLOYEE_BEN_DED (View)
SPT_PD_EMPLOYEE_BEN_DED
SPT_BENDED
This view contains one row peassociation of the Colleague Pamounts paid by employees frecords are not included.
ODS_PAYTODAT_EMPLOYEE_TAXES (View)
SPT_PD_EMPLOYEE_TAXES This view contains one row peassociation of the Colleague Ptaxes paid by employees. Voidincluded.
ODS_PAYTODAT_EMPLOYER_BEN_EXP (View)
SPT_PD_EMPLOYER_BEN_EXP
SPT_BENDED
SPT_POSITION
This view contains one row peassociation of the Colleague Pamounts paid by the institutionbenefits. Voided and replaced
ODS_PAYTODAT_EMPLOYER_TAXES (View)
SPT_PD_EMPLOYER_TAXES
SPT_BENDED
This view contains one row peassociation of the Colleague Ptaxes paid by the institution onreplaced records are not inclu
Using the DataOrchestrator ODS Data Models: HR Data Model
Data Model Diagrams
Data model diagrams provide structural views of the reporting tables and views. These diagrams show each table or view in the operational data store containing Colleague data to be used for reporting.
Each table or view is represented by a rectangle, labeled with the table or view name, and with a list of the columns of the table or view, in addition to the data type of each. Also, the logical relationships between database tables and views are shown by arrows pointing from the referencing object to the referenced object. Each arrow is annotated with the column from the source table or view that equates to the key field of the referenced table or view.
Although these relationships are shown in the data model diagrams, they are not implemented as foreign key relationships in the database in all cases. Users accessing these tables from SQL queries or SQL-based reporting tools will need to include these join conditions in their reports.
104 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
HR Reporting Subject Areas
Figure 12 and Figure 13 on page 106 show the data model diagram for the Employee Information reporting subject area.
Figure 12: Data Model Diagram for the Employee Information Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 105© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: HR Data Model
Figure 13: Data Model Diagram for the Employee Information Reporting Subject Area (continued)
106 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
HR Reporting Subject Areas
Figure 14 shows the data model diagram for the Payroll Information reporting subject area.
Figure 14: Data Model Diagram for the Payroll Information Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 107© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: HR Data Model
108 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models
Finance Data Model
In This ChapterThis chapter provides an overview of all tables and views containing information from the Colleague Finance application used for reporting from the DataOrchestrator ODS.
Table 21 lists the topics covered in this chapter.
Table 21: Topics in This Chapter
Topic Page
Finance Reporting Subject Areas 110
Accounts Payable and Purchasing 111
GL Transaction Information 117
Data Model Diagrams 120
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 109© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Finance Data Model
Finance Reporting Subject AreasIn Colleague Finance, there are two subject areas available for reporting:
Accounts Payable and Purchasing
GL Transaction Information
For these subject areas, the following information is provided for the associated tables and views:
The name of the table or view in the ODS target database.
The transform in the target which must be run to extract data for the table or view. For tables, the transform name is the name of the table. For views, one or more transforms create supporting tables on which the view is built. These supporting tables are indicated by a prefix of SPT_; however, they are not used for reporting. Datatel recommends that only tables or views that begin with the prefix of ODS_ be used for reporting.
A description of the table or view, its source in Colleague, and its reporting use.
In addition, data model diagrams are provided for Colleague Finance showing the relationship of tables within the subject area and their individual fields.
Technical Tip: The supporting tables are necessary for improved performance of transforms or because of restrictions on the data transformation of the DataOrchestrator ODS. Datatel may restructure or eliminate these supporting tables in future ODS releases. Therefore, use only the tables or views with the ODS_ prefix for reporting.
110 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Finance Reporting Subject Areas
Accounts Payable and Purchasing
Description
This subject area provides information about payables current and outstanding to aid in planning and cash flow analysis. The types of questions you can answer from the information in this subject area are:
How much money is due to be paid in the next day, week, and month?
How much money is owed to which vendors? Which are the vendors owed the most or above a given amount?
How many times has a vendor been paid in the last x number of months or years?
What has been the total amount paid to a vendor in a given month or year?
What is the total value of discounts received from a vendor?
What has been purchased?
You have the ability to filter this information by the following criteria:
Date, year, and term
Department, fund, location, and parts of the GL account number
Transaction type
Approver and cost center owner
Project (if your institution uses Project Accounting)
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 111© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Finance Data Model
This subject area also provides information about the goods being requested and ordered and the value of those goods. The types of questions you can answer from the information in this subject area are:
What are the outstanding amounts in requisitions?
What is the total value of outstanding requisitions in a given time period; for example: week, month, and year?
What is the total amount requested that has been approved? Not approved?
What is the value approved or not approved by department, cost center, location, campus, and in a given time period of week, month, and year?
What are the outstanding amounts on order?
What orders are partially filled? Complete? Backordered?
What is the total amount and value of what has been received by department, cost center, location, campus, and in a given time period of week, month, and year?
Which requisitions or orders have been paid?
What items are associated with a recurring voucher and what is the current balance of the voucher?
You have the ability to filter this information by the following criteria:
Date, year, and term
Department, fund, location, and parts of the GL account number
Transaction type
Approver and cost center owner
Project (if your institution uses Project Accounting)
Primary Colleague Source Files VOUCHERS
VENDORS
REQUISITIONS
PURCHASE.ORDERS
ITEMS
112 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Fin
ance R
epo
rting
Su
bject A
reas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
113©
2010 Datatel, Inc.
vided in Table 22.
escription
file, this has the single-valued be used to query by commodity how much of an item is desired, the etc. Recurring voucher and blanket y, because blanket purchase orders ot in the ODS. Items from unfinished d.
e BPO file, this allows query on ributes. Unfinished and unapproved e not included.
PO.GL association of the Colleague GL account. It also has the balance, een the GL encumbered amounts
o the GL account for a blanket unfinished and unapproved blanket cluded.
or address information from the parate from ODS_BPO to allow DS_ADDRESS table for querying by . Miscellaneous vendor information roved blanket purchase orders is not
e PURCHASE.ORDERS file, this order attributes. Unfinished and rs are not included.
EM.PO association of the Colleague y GL account. Entries for unfinished orders are not included.
The reporting tables and views for Accounts Payable and Purchasing are pro
Table 22: Accounts Payable and Purchasing Tables and Views
Table/View Transforms D
ODS_ITEMS (View)
SPT_ITEMS From the Colleague ITEMSattributes of an item. It cancodes and quantity, to see total amount on backorder,purchase order items (if ando not require items) are ndocuments are not include
ODS_BPO Same as table name. Primarily from the Colleagublanket purchase order attblanket purchase orders ar
ODS_BPO_GL Same as table name. One row per entry in the BBPO file to allow query by which is the difference betwand the expenses posted tpurchase order. Entries forpurchase orders are not in
ODS_BPO_MISC_VEN_ADDRESS Same as table name. This is miscellaneous vendColleague BPO file. It is seunions and joins with the Ogeographic characteristicsfrom unfinished and unappincluded.
ODS_PURCHASE_ORDERS Same as table name. Primarily from the Colleaguallows query on purchase unapproved purchase orde
ODS_PO_ITEMS_GL (View)
SPT_PO_ITEMS_GL One row per entry in the ITITEMS file to allow query band unapproved purchase
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: F
inan
ce Data M
od
el
114R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
he PO.GL.TAXES association of the to allow query on taxes by GL account. t to the amount in ODS_PO_ITEMS_GL the full amount for the GL account for
m. Entries for unfinished and unapproved ot included.
vendor address information from the E.ORDERS file. It is separate from RDERS to allow unions and joins with the for querying by geographic laneous vendor information from oved purchase orders is not included.
gue PROJECTS file. It provides basic capital projects set up within your
table is from the Colleague This provides basic information about the our CF application, including the current
voucher and basic vendor information. re not included in this ODS table.
he ITEMS file, from records associated er. This provides the remaining balance e recurring voucher allocated to specific and also to any associated capital udes the rebate and refund amounts for item.
he ITEMS file, from records associated er. This provides the remaining tax e recurring voucher allocated to specific and also to any associated capital
Table 22: Accounts Payable and Purchasing Tables and Views (cont’d)
Description
ODS_PO_ITEMS_GL_TAX (View)
SPT_PO_ITEMS_GL_TAX One row per entry in tColleague ITEMS file Adding the tax amounfor a GL account givesthe purchase order itepurchase orders are n
ODS_PO_MISC_VEN_ADDRESS Same as table name. This is miscellaneous Colleague PURCHASODS_PURCHASE_OODS_ADDRESS tablecharacteristics. Miscelunfinished and unappr
ODS_PROJECTS Same as table name. This is from the Colleainformation about the accounting system.
ODS_RECURRING_VOUCHERS (View)
SPT_RECURRING_VOUCHERS The information in thisRC_VOUCHERS file. recurring vouchers in ystatus of the recurringUnfinished vouchers a
ODS_RECUR_VOU_ITEMS_GL (View)
SPT_RECUR_VOU_ITEMS_GL This is primarily from twith a recurring vouchand item quantity for thGL account numbers, projects. This also inclthe recurring voucher
ODS_RECUR_VOU_ITEMS_GL_TAX (View)
SPT_RECUR_VOU_ITEMS_GL_TAX This is primarily from twith a recurring vouchpayment balance for thGL account numbers, projects.
Table/View Transforms
Fin
ance R
epo
rting
Su
bject A
reas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
115©
2010 Datatel, Inc.
e REQUISITIONS file, this allows tes. Unfinished and unapproved d.
EM.REQ association of the ow query by General Ledger (GL) hed and unapproved requisitions are
EQ.GL.TAXES association of the ow query on taxes by GL account. e amount in ODS_REQ_ITEMS_GL
full amount for the GL account for for unfinished and unapproved d.
or address information from the file. It is separate from llow unions and joins with the querying by geographic ous vendor information from requisitions is not included.
ORS file, this allows query on aken from vendors. If the vendor the name can be retrieved from
olleague VOUCHERS file. It has ts to allow query on how much has aid, either in total or by vendor. d vouchers are not included.
L association in the Colleague ueries by GL accounts. Entries from vouchers are not included.
escription
ODS_REQUISITIONS Same as table name. Primarily from the Colleaguquery on requisition attriburequisitions are not include
ODS_REQ_ITEMS_GL (View)
SPT_REQ_ITEMS_GL One row per entry in the ITColleague ITEMS file to allaccount. Entries for unfinisnot included.
ODS_REQ_ITEMS_GL_TAX (View)
SPT_REQ_ITEMS_GL_TAX One row per entry in the RColleague ITEMS file to allAdding the tax amount to thfor a GL account gives thethe requisition item. Entriesrequisitions are not include
ODS_REQ_MISC_VEN_ADDRESS Same as table name. This is miscellaneous vendColleague REQUISITIONSODS_REQUISITIONS to aODS_ADDRESS table for characteristics. Miscellaneunfinished and unapproved
ODS_VENDORS Same as table name. From the Colleague VENDdiscounts that have been tname does not have data, ODS_PERSON.
ODS_VOUCHERS (View)
SPT_CHECKS
SPT_VOUCHERS
This is primarily from the Ccheck and payment amounbeen paid or is due to be pUnfinished and unapprove
ODS_VOUCHER_ITEMS_GL (View)
SPT_VOUCHER_ITEMS_GL This is from the VOUCH.GVOUCHERS file to allow qunfinished and unapproved
Table 22: Accounts Payable and Purchasing Tables and Views (cont’d)
Table/View Transforms D
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: F
inan
ce Data M
od
el
116R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
L.TAXES association in the Colleague w queries on taxes by GL accounts, or to mounts by joining the tax amounts with OUCHER_ITEMS_GL. Taxes from
oved vouchers are not included.
vendor address information from the S file. It is separate from allow unions and joins with the for querying by geographic laneous vendor information from oved vouchers is not included.
Table 22: Accounts Payable and Purchasing Tables and Views (cont’d)
Description
ODS_VOUCHER_ITEMS_GL_TAX (View)
SPT_VOUCHER_ITEMS_GL_TAX This is from the VOU.GVOUCHERS file to alloget total GL account athe amounts in ODS_Vunfinished and unappr
ODS_VOUCHER_MISC_VEN_ADDRESS Same as table name. This is miscellaneous Colleague VOUCHERODS_VOUCHERS to ODS_ADDRESS tablecharacteristics. Miscelunfinished and unappr
Table/View Transforms
Finance Reporting Subject Areas
GL Transaction Information
Description
This subject area provides information about the transactions posted to the general ledger across all years. The types of questions you can answer from the information in this subject area are:
What money has been spent in what areas (accounts and segments)?
What is the encumbered balance?
What is the budget for the accounts and what is the remaining balance?
What type of transaction and from what sub-ledger is it from?
You have the ability to filter this information by the following criteria:
Date, year, and term
Department, fund, location, and parts of the GL account number
Transaction type
Primary Colleague Source Files GLA.FYR
ENC.FYR
GLS.FYR
GL.ACCTS
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 117© 2010 Datatel, Inc.
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: F
inan
ce Data M
od
el
118R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
The reporting tables and views for Transaction Detail are provided in Table 23.
cription
this has the single-valued attributes of query by GL component.
YR file suite, this allows querying on GL e opening balance for actuals and l year is open, it will perform the timated opening balance for the selected accurate if more than two fiscal years are
GLA.FYR file suite; it allows querying on mation for the account, including open/officer assigned to the account.
al associations of the GLS.FYR file suite. encumbrance, and budget metrics by ach GL account.
Table 23: Transaction Detail Tables and Views
Table/View Transforms Des
ODS_GL_ACCTS Same as table name. From the Colleague GL.ACCTS file,each GL account. It can be used to
ODS_GL_ACCTS_FY_OPEN_BAL (View)
SPT_GLS_FYR
ODS_GEN_LDGR
ODS_GL_ACCTS
Primarily from the Colleague GLS.Faccounts by fiscal year to find out thencumbrances. If the previous fiscaestimation logic to determine the esaccount. This calculation will not be open.
ODS_GL_ACCT_FY_STATUS (View)
SPT_GL_ACCTS_LDGRHIST
SPT_BUD_OFCR
SPT_GL_ACCTS_MEMOS
This is primarily from the Colleague all fiscal year dependent status inforclose/frozen status, and the budget
ODS_GL_TRANSACTION_SUMM (View)
SPT_GLS_M_ACTUALS
SPT_GLS_M_PO_ENCS
SPT_GLS_M_ALOC_BUDGET
SPT_GLS_M_APPR_BUDGET
SPT_GLS_M_CONT_BUDGET
SPT_GLS_M_ORIG_BUDGET
This data is from various monthly totIt provides a monthly total of actual,fiscal year and calendar month for e
Fin
ance R
epo
rting
Su
bject A
reas
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
119©
2010 Datatel, Inc.
h provides lowest-level data about al Ledger. This information supports nd allows the breakdown of ation by GL transaction. Only GL
R table is included; no archived d. However, the fact that the detailed ed is indicated by a value in the es information on the mapping of GL your Colleague Finance application.
he encumbrance file suite, able identifies the purchase order luding PO number, vendor
f the ENC.FYR file suite. The e requisition information for a given
tion
ODS_GL_TRANSACTION_DETAIL (View)
SPT_GLA_FYR
ODS_GEN_LDGR
This is from the GLA.FYR file suite, whiceach GL transaction posted to the Generquerying of GL amounts by GL source, aODS_GL_TRANSACTION_SUMM informtransaction information from the GLA.FYinformation from the GAA.FYR is includeinformation for a record has been archivarchive suffix field. This view also providtransactions to capital projects defined in
ODS_ENC_PO Same as table name. This is from the ENC.PO association of tENC.FYR. The information in this ODS tinformation for a given encumbrance, incinformation, etc.
ODS_ENC_REQ Same as table name. This is from the ENC.REQ association oinformation in the ODS table identifies thencumbrance.
Table 23: Transaction Detail Tables and Views (cont’d)
Table/View Transforms Descrip
Using the DataOrchestrator ODS Data Models: Finance Data Model
Data Model Diagrams
Data model diagrams provide structural views of the reporting tables and views. These diagrams show each table or view in the operational data store containing Colleague data to be used for reporting.
Each table or view is represented by a rectangle, labeled with the table or view name, and with a list of the columns of the table or view, in addition to the data type of each. Also, the logical relationships between database tables and views are shown by arrows pointing from the referencing object to the referenced object. Each arrow is annotated with the column from the source table or view that equates to the key field of the referenced table or view.
Although these relationships are shown in the data model diagrams, they are not implemented as foreign key relationships in the database in all cases. Users accessing these tables from SQL queries or SQL-based reporting tools will need to include these join conditions in their reports.
120 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Finance Reporting Subject Areas
Figure 15 and Figure 16 on page 122 show the data model diagram for the Accounts Payable and Purchasing reporting subject area.
Figure 15: Data Model Diagram for the Accounts Payable and Purchasing Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 121© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Finance Data Model
Figure 16: Data Model Diagram for the Accounts Payable and Purchasing Reporting Subject Area (continued)
122 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Finance Reporting Subject Areas
Figure 17 shows the data model diagram for the GL Transaction Information reporting subject area.
Figure 17: Data Model Diagram for the GL Transaction Information Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 123© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Finance Data Model
124 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models
Advancement Data Model
In This ChapterThis chapter provides an overview of all tables and views containing information from the Colleague Advancement application used for reporting from the DataOrchestrator ODS.
Table 24 lists the topics covered in this chapter.
Table 24: Topics in This Chapter
Topic Page
Advancement Reporting Subject Area 126
Demographics and Contributions 127
Data Model Diagrams 131
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 125© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Advancement Data Model
Advancement Reporting Subject AreaIn Colleague Advancement, there is one subject area available for reporting: Demographics and Contributions.
For this subject area, the following information is provided for the associated tables and views:
The name of the table or view in the ODS target database.
The transform in the target which must be run to extract data for the table or view. For tables, the transform name is the name of the table. For views, one or more transforms create supporting tables on which the view is built. These supporting tables are indicated by a prefix of SPT_; however, they are not used for reporting. Datatel recommends that only tables or views that begin with the prefix of ODS_ be used for reporting.
A description of the table or view, its source in Colleague, and its reporting use.
In addition, data model diagrams are provided for Colleague Advancement showing the relationship of tables within the subject area and their individual fields.
Technical Tip: The supporting tables are necessary for improved performance of transforms or because of restrictions on the data transformation of the DataOrchestrator ODS. Datatel may restructure or eliminate these supporting tables in future ODS releases. Therefore, use only the tables or views with the ODS_ prefix for reporting.
126 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Advancement Reporting Subject Area
Demographics and Contributions
Description
This subject area provides information about the personal, biographic, and demographic attributes of a donor. The types of questions you can answer from the information in this subject area are:
What is this individual’s relationship to the institution?
What is the preferred contact information for this constituent?
What interests are associated with this donor’s giving?
Does this individual belong to one or more reunion classes for the institution?
You have the ability to filter this information by the following criteria:
Chapters
Reunion classes
Mail codes
Sources
Interests
This subject area also includes contributions and provides the characteristics and detailed information for each contribution to the institution. The types of questions you can answer from the information in this subject area are:
What individual or giving group has made this contribution?
To what campaign is this contribution associated?
Which recognition program does the donor qualify for as a result of this contribution?
Is the donor behind on installment payments for a pledge?
How many contributions were received to a particular designation?
You have the ability to filter this information by the following criteria:
Contribution date, year, and fiscal year
Donor information including reunion classes
Recognition programs and levels
Contribution source and giving area
Campaign information including overall campaign cost and campaign start and end dates
Payment and solicitation methods
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 127© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Advancement Data Model
Primary Colleague Source Files PERSON
ACTIVITY
DESIGNATION
CONTRIB.DONOR.DESIG
CONTRIBUTION
CONTRIB.DONOR
RECOG.PROGRAM.MEMBER
128 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Ad
vancem
ent R
epo
rting
Su
bject A
rea
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
129©
2010 Datatel, Inc.
vided in Table 25.
scription
MPAIGN file, which is a logical view vided for query on campaign also be used to provide the name of from ODS_CONTRIB_INFO.
SIGNATION file and allows query an also be used to provide the name ying from ODS_CONTRIB_INFO.
CONTRIB.DONOR.DESIG file, this a Colleague Advancement (CA) y contribution attributes, such as vide totals by campaign,
valid, posted contributions are
rs, campaigns, or designations, the ear on multiple rows. Distinct
rying to get a grand total of pledge
D.SOURCES list in the Colleague w query by sources associated with
from valid, unvoided, posted use the sources associated with the _SOURCES can be used instead, or if only the primary source is desired.
D.REUNION.CLASSES list in the file to allow query by reunion tribution. Only reunion classes from
ibutions are included. To use the ith the donor,
CLASSES can be used instead.
The reporting tables and views for Demographics and Contributions are pro
Table 25: Demographics and Contributions Tables and Views
Table/View Transform(s) De
ODS_CAMPAIGN Same as table name. This is from the Colleague CAof the ACTIVITY file. It is proexpenses and revenue. It canthe campaign when querying
ODS_DESIGNATION Same as table name. This is from the Colleague DEby designation attributes. It cof the designation when quer
ODS_CONTRIB_INFO (View)
SPT_PLEDGE
SPT_CONTRIB_DONOR_DESIG
SPT_CONTRIB
Primarily from the Colleague is the lowest level of detail ofcontribution. It allows query bcategory or type, and can prodesignation, donor, etc. Onlyincluded.
If a pledge has multiple donosame pledge balance will apppledges must be selected if tbalances.
ODS_CONTRIB_SOURCES Same as table name. One row per entry in the CONCONTRIB.DONOR file to alloa contribution. Only sources contributions are included. Todonor, either ODS_PERSONODS_PERSON can be used
ODS_CONTRIB_REUNION_CLS Same as table name. One row per entry in the CONColleague CONTRIB.DONORclasses associated with a convalid, unvoided, posted contrreunion classes associated wODS_PERSON_REUNION_
Usin
g th
e DataO
rchestrato
r OD
S D
ata Mo
dels: A
dvan
cemen
t Data M
od
el
130R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
gue RECOG.PGM.MEMBER file, this ion programs and recognition program ithin them.
PERSON.CHAPTERS list in the to allow query by chapters not associated
INTERESTS list in the Colleague ery by a person's interests.
MAIL.RULES list in the Colleague also known as mail codes, are often s of people or organizations. This allows es.
REUNION.CLASS list in the Colleague ery by reunion classes, also known as
PERSON.MULTIPLE.SOURCES list in file. The primary source is in ERSON_SOURCES is used to query es a person or organization might have, rce.
Table 25: Demographics and Contributions Tables and Views (cont’d)
Description
ODS_RECOG_PGM_MEMBER (View)
SPT_RECOG_PGM_MEMBER Primarily from the Colleaallows query on recognitlevels, and the donors w
ODS_PERSON_CHAPTERS Same as table name. One row per entry in theColleague PERSON file to an address.
ODS_PERSON_INTERESTS Same as table name. One row per entry in thePERSON file to allow qu
ODS_PERSON_MAIL_RULES Same as table name. One row per entry in thePERSON file. Mail rules,used to categorize groupquery by the mailing cod
ODS_PERSON_REUNION_CLASSES Same as table name. One row per entry in thePERSON file to allow qureunion years.
ODS_PERSON_SOURCES Same as table name. One row per entry in thethe Colleague PERSONODS_PERSON; ODS_Pusing the full list of sourcincluding the primary sou
Table/View Transform(s)
Advancement Reporting Subject Area
Data Model Diagrams
Data model diagrams provide structural views of the reporting tables and views. These diagrams show each table or view in the operational data store containing Colleague data to be used for reporting.
Each table or view is represented by a rectangle, labeled with the table or view name, and with a list of the columns of the table or view, in addition to as the data type of each. Also, the logical relationships between database tables and views are shown by arrows pointing from the referencing object to the referenced object. Each arrow is annotated with the column from the source table or view that equates to the key field of the referenced table or view.
Although these relationships are shown in the data model diagrams, they are not implemented as foreign key relationships in the database in all cases. Users accessing these tables from SQL queries or SQL-based reporting tools will need to include these join conditions in their reports.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 131© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Advancement Data Model
Figure 18 shows the data model diagram for the Demographics reporting subject area.
Figure 18: Data Model Diagram for the Demographics Reporting Subject Area
132 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Advancement Reporting Subject Area
Figure 19 shows the data model diagram for the Contributions reporting subject area.
Figure 19: Data Model Diagram for the Contributions Reporting Subject Area
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 133© 2010 Datatel, Inc.
Using the DataOrchestrator ODS Data Models: Advancement Data Model
134 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Introduction
Customizing Data Models
In This ChapterThis section provides details for customizing the DataOrchestrator ODS data models and their associated views, so that you can add or remove information in the data models as appropriate for your institution’s reporting.
Table 26 lists the topics in this chapter.
Table 26: Topics in This Chapter
Topic Page
Understanding Customization 136
How to Customize the Data Models 137
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 135© 2010 Datatel, Inc.
Introduction: Customizing Data Models
Understanding CustomizationThe DataOrchestrator ODS data models are provided to answer specific reporting questions for each major Colleague application. In order to answer additional questions for your institution’s reporting needs, or to further limit the amount of data in your operational data stores, the data models can be customized. To customize the information in the operational data stores, you can create additional transforms for a data model or modify existing transforms.
Some of the reasons you may want to modify the data models are:
To add additional columns to predefined transforms.
To modify data characteristics, including the length of transform columns.
To remove columns from a predefined transform that are not used in your Colleague environment or for your institution’s reporting.
To remove columns referencing computed columns that you do not use. Removing unneeded computed columns also improves your refresh performance.
136 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Understanding Customization
How to Customize the Data Models
Customizing the data models may involve modifying both transforms and SQL views. Before you can customize data models, you must first determine if what you want to modify is a physical table (created by a transform) or a view. You can determine this by using your target database management tool; for example, SQL Server Management Studio.
To customize a physical table used for direct reporting, you modify the transform that creates the table. The transform has the same name as the physical table you want to customize.
To customize a view, you modify the transforms that create the supporting tables (prefixed with SPT_) that the view references. You determine the supporting tables either by opening the SQL view definition (using your target database management tool), or by referencing the SQL view in this manual. For example, if you were modifying the ODS_PERSON view, you could reference Table 10 on page 54. Again, the transform has the same name as the table you want to customize.
After you have determined the transforms to customize, you use several forms in the DataOrchestrator ODS to add, remove, or modify the columns of the transforms. See “Customizing Transforms” on page 138 for more information.
You may also need to customize the definition of the SQL view to make your added transform columns available in the view or to remove references to any columns you have deleted. See “Customizing SQL Views” on page 142 for more information.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 137© 2010 Datatel, Inc.
Introduction: Customizing Data Models
Customizing Transforms
When customizing transforms, you can add or remove columns using the forms in the DataOrchestrator ODS. These forms are:
DataOrch Target (DOTA)
DataOrch Target Transform (DOTT)
Transform Columns (DOTC)
In the UT application, use these forms to enter a target transform to be included in the ODS target database or to edit an existing transform.
Adding Columns
To add additional information to the data models, follow these steps.
Step 1. Access the DOTA form. Use the Target Transforms list to add a transform, or to detail on an existing transform. You will access the DOTT form.
Step 2. On the DOTT form, if you are modifying an existing transform, detail on the Column Definitions field to access the DOTC form.
If you are adding a transform, you must first enter the source file. After you enter a source file, you automatically detail to the DOTC form.
Step 3. On the DOTC form, specify the output columns for the target transform.
For detailed information on the DOTA, DOTT, and DOTC forms, see the Using the DataOrchestrator ODS manual.
If the transform is referenced by an SQL view, then the view will have to be customized to include the changes to the data columns. See “Customizing SQL Views” on page 142 for further information.
Note: If you customize a transform, you must take this customization into account when future software updates are delivered that modify that transform. If you copy the updated transform to your target using the DataOrch Transform Maint (DOMA) form, most of your customized changes will be retained; however, any filters you’ve modified for the transform will be overwritten. For this reason, you may want to rename the transform on your target using the DOMA form before you customize the transform.
138 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Understanding Customization
Any changes to the transforms must conform to the transform’s cardinality checking, which is enforced when you save from the DOTC form. For example, only single-valued columns should be added or additional columns from an association that is already referenced in the transform. Datatel does not recommend that you add a multivalued column to a transform with only single-valued columns, because it will change the cardinality of the transform and will affect views that reference the transform.
Removing Columns
If there is data being exported that is not needed by your institution, you can remove the columns using the same DataOrchestrator ODS forms that you use to add columns.
Datatel recommends that you do not delete columns from a transform, but instead set the column’s export flag to “No” on the DOTC form. This will remove the data column from the ODS target database after the next refresh of the transform.
If any columns that you remove are referenced by SQL views, then you can change the transform operation in order to export a null value into the target column. The null values in the target column will allow the view to be created successfully without errors being reported by the refresh. If you do this, you do not need to customize the view to remove the reference to the column.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 139© 2010 Datatel, Inc.
Introduction: Customizing Data Models
To export a null value to a column, use the DOTC form to choose the String Concatenation operation for the column as shown in Figure 20.
Figure 20: Choosing the String Concatenation Operation on the DOTC Form
After you choose the operation, you automatically detail to the String Concatenation (DOCA) form.
140 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Understanding Customization
On the DOCA form, enter a blank string (empty double quotes) on the first two lines of the Concatenation Operand List as shown in Figure 21.
Figure 21: Entering a Blank String on the DOCA Form
When you save from the DOCA form, a null value will be exported for the column, and you do not need to customize the view to remove the reference to the column.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 141© 2010 Datatel, Inc.
Introduction: Customizing Data Models
Customizing SQL Views
When customizing SQL views, use the following forms in the DataOrchestrator ODS:
DataOrch Target Views (DOTV)
DataOrch View Spec (DOVS)
In the UT application, access these forms to enter an SQL view to be included in the ODS target database or to edit an existing view. For more information, see the following chapter in Using the DataOrchestrator ODS: “Defining and Creating SQL Views.”
Note: If you customize an SQL view, you must take this customization into account when future software updates are delivered that modify that SQL view. If you copy the updated view definition to your target using the DataOrch View Maintenance (DOVM) form, your customized changes will be overwritten. For this reason, you may want to rename the SQL view on your target using the DOVM form before you customize the view.
142 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Introduction
Using Reports from the Data Models
In This ChapterThis chapter provides examples of queries and reports created from the DataOrchestrator ODS data models.
Table 27 lists the topics in this chapter.
Examples of Queries and ReportsThis section includes examples of reports that you can create using the DataOrchestrator ODS data models to answer your specific business questions. The queries used to create these example reports are also included. You can create similar reports for your institution by using these queries and reports as examples.
Table 27: Topics in This Chapter
Topic Page
Examples of Queries and Reports 143
Crystal Example Report – Student Enrollment by Term 144
Web Intelligence Example Report – HR Earnings Analysis 146
Excel Example Report – GL Actuals Analysis Report 149
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 143© 2010 Datatel, Inc.
Introduction: Using Reports from the Data Models
Crystal Example Report – Student Enrollment by Term
A report answering the question “Which students took what courses in a given term, and what was their achievement in those courses?” might look like the following Crystal report. Fields in this report were selected from the ODS_STUDENT_ENROLLMENT and ODS_PERSON views, which were joined on STC_PERSON_ID for the query.
The SQL statement to extract data for the report is shown in Figure 22. The report, as a result of that statement, is shown in Figure 23 on page 145.
Figure 22: SQL Statement to Extract Data for the Student Enrollment by Term Report
SELECT DISTINCT "ODS_STUDENT_ENROLLMENT"."STC_TERM",
"ODS_STUDENT_ENROLLMENT"."STC_PERSON_ID", "ODS_PERSON"."FIRST_NAME", "ODS_PERSON"."LAST_NAME", "ODS_STUDENT_ENROLLMENT"."STC_ACAD_LEVEL", "ODS_STUDENT_ENROLLMENT"."STA_CLASS", "ODS_STUDENT_ENROLLMENT"."STA_ENROLL_STATUS",
"ODS_STUDENT_ENROLLMENT"."ENROLLMENT_STATUS_DESC", "ODS_STUDENT_ENROLLMENT"."STC_COURSE", "ODS_STUDENT_ENROLLMENT"."STC_COURSE_LEVEL", "ODS_STUDENT_ENROLLMENT"."STC_COURSE_NAME", "ODS_STUDENT_ENROLLMENT"."STC_GRADE", "ODS_STUDENT_ENROLLMENT"."GRADE_VALUE", "ODS_STUDENT_ENROLLMENT"."SCS_COURSE_SECTION", "ODS_STUDENT_ENROLLMENT"."STC_SECTION_NO", "ODS_STUDENT_ENROLLMENT"."CURRENT_STATUS_DESC" FROM "example_database"."dbo"."ODS_STUDENT_ENROLLMENT" "ODS_STUDENT_ENROLLMENT" INNER JOIN "example_database"."dbo"."ODS_PERSON" "ODS_PERSON" ON
"ODS_STUDENT_ENROLLMENT"."STC_PERSON_ID"="ODS_PERSON"."ID" WHERE ("ODS_STUDENT_ENROLLMENT"."STC_TERM"='01/FA' OR "ODS_STUDENT_ENROLLMENT"."STC_TERM"='01/SP' OR "ODS_STUDENT_ENROLLMENT"."STC_TERM"='01/SU' OR "ODS_STUDENT_ENROLLMENT"."STC_TERM"='01/WI') ORDER BY "ODS_STUDENT_ENROLLMENT"."STC_TERM", "ODS_STUDENT_ENROLLMENT"."STC_PERSON_ID"
144 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Examples of Queries and Reports
Figure 23: Crystal Report – Student Enrollment by Term
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 145© 2010 Datatel, Inc.
Introduction: Using Reports from the Data Models
Web Intelligence Example Report – HR Earnings Analysis
If you want to perform analysis on actual employee earnings as reported on employee paychecks, you can create a Business Objects’ Web Intelligence report against the ODS_PAYTODAT_EARN view in the HR data model. This view provides organizational and payroll information in a single reporting structure. The view can be easily queried to allow analysis by a selection of dimensions pertinent to the HR business process of the actual dollar amounts paid to employees. The following example shows an SQL query and an example presentation of the information in a Web Intelligence crosstab report. This report can be used to perform dimensional OLAP-type analysis on this data set.
Note: If your institution has the optional module for the Business Objects Connector, you can use the Connector to facilitate reporting using the data models with Web Intelligence. This Connector consists of universes and sample reports built to access information in the data models. For more information, see “Using the Connector with the Data Models” beginning on page 153.
Note: Before viewing this report, you must create a Business Objects’ universe to access the ODS_PAYTODAT_EARN view.
146 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Examples of Queries and Reports
The SQL statement to extract data for the report is shown in Figure 24. The report, as a result of that statement, is shown in Figure 25 on page 148.
Figure 24: SQL Statement to Extract Data for the HR Earnings Analysis Report
SELECT Example_database.dbo.ODS_PAYTODAT_EARN.PTD_PAY_CYCLE, Example_database.dbo.ODS_PAYTODAT_EARN.PTD_EMPLOYEE_ID, sum(Example_database.dbo.ODS_PAYTODAT_EARN.PTD_AMOUNTS), Example_database.dbo.ODS_PAYTODAT_EARN.BARGAINING_UNIT_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.DEPARTMENT_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.DIVISION_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.EARNING_TYPE_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.PTD_GL_NOS, Example_database.dbo.ODS_PAYTODAT_EARN.LOCATION_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.PAY_CLASS_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.PAY_GRADE, Example_database.dbo.ODS_PAYTODAT_EARN.PAY_STEP, Example_database.dbo.ODS_PAYTODAT_EARN.SHORT_TITLE, {fn year(Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE)},
datepart(qq,Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE),
{fn month(Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE)}, Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE
FROM Example_database.dbo.ODS_PAYTODAT_EARN
GROUP BY Example_database.dbo.ODS_PAYTODAT_EARN.PTD_PAY_CYCLE, Example_database.dbo.ODS_PAYTODAT_EARN.PTD_EMPLOYEE_ID, Example_database.dbo.ODS_PAYTODAT_EARN.BARGAINING_UNIT_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.DEPARTMENT_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.DIVISION_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.EARNING_TYPE_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.PTD_GL_NOS, Example_database.dbo.ODS_PAYTODAT_EARN.LOCATION_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.PAY_CLASS_DESC, Example_database.dbo.ODS_PAYTODAT_EARN.PAY_GRADE, Example_database.dbo.ODS_PAYTODAT_EARN.PAY_STEP, Example_database.dbo.ODS_PAYTODAT_EARN.SHORT_TITLE, {fn year(Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE)},
datepart(qq,Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE),
{fn month(Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE)}, Example_database.dbo.ODS_PAYTODAT_EARN.PTD_CHECK_DATE
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 147© 2010 Datatel, Inc.
Introduction: Using Reports from the Data Models
Figure 25: Web Intelligence Report – HR Earnings Analysis
148 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Examples of Queries and Reports
Excel Example Report – GL Actuals Analysis Report
You can prepare an Excel crosstab report to answer the question, “What are my areas of actual income and expenditure in a given fiscal year and month in terms of the GL Components set up in my institution’s chart of accounts?” This report accesses the ODS_GL_TRANSACTION_SUMM view in the ODS Finance data model to obtain the actual credits and debits for each fiscal year month for each account, as well as accessing the ODS_GL_ACCTS table to map these accounts into the defined GL Components as defined at your site.
The SQL statement to extract data for the report is shown in Figure 26. The report, as a result of that statement, is shown in chart form in Figure 27 and in table form in Figure 28 on page 150.
Figure 26: SQL Statement to Extract Data for the GL Actuals Analysis Report
SELECT ODS_GL_ACCTS.FUND_WITH_DESC, ODS_GL_ACCTS.GL_CLASS_WITH_DESC, ODS_GL_ACCTS.UNIT_WITH_DESC, ODS_GL_ACCTS.GL_SUBCLASS_WITH_DESC, ODS_GL_TRANSACTION_SUMM.FISCAL_YEAR, ODS_GL_TRANSACTION_SUMM.FISCAL_YEAR_MONTH, SUM(ODS_GL_TRANSACTION_SUMM.MCREDITS - ODS_GL_TRANSACTION_SUMM.MDEBITS) AS 'ACTUALS'
FROM example_database.dbo.ODS_GL_ACCTS ODS_GL_ACCTS, example_database.dbo.ODS_GL_TRANSACTION_SUMM ODS_GL_TRANSACTION_SUMM
WHERE ODS_GL_ACCTS.GL_ACCTS_ID = ODS_GL_TRANSACTION_SUMM.GL_ACCT_ID
GROUP BY ODS_GL_ACCTS.FUND_WITH_DESC, ODS_GL_ACCTS.GL_CLASS_WITH_DESC, ODS_GL_ACCTS.UNIT_WITH_DESC, ODS_GL_ACCTS.GL_SUBCLASS_WITH_DESC, ODS_GL_TRANSACTION_SUMM.FISCAL_YEAR, ODS_GL_TRANSACTION_SUMM.FISCAL_YEAR_MONTH
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 149© 2010 Datatel, Inc.
Introduction: Using Reports from the Data Models
Figure 27: Excel Report – GL Actuals Analysis Report (Chart)
Figure 28: Excel Report – GL Actuals Analysis Report (Table)
150 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting from the DataOrchestrator ODS Data Models
Using the Business Objects Connector
Using the Business Objects Connector
Using the Connector with the Data Models
In This ChapterThis chapter provides the information needed to understand and use the Business Objects Connector with the DataOrchestrator ODS data models. Table 28 lists the topics in this chapter.
Table 28: Topics in This Chapter
Topic Page
Overview of the Business Objects Connector 154
Universes Delivered in the Business Objects Connector 156
Description of the Universes 158
Colleague Core ODS Universe 162
Colleague Student ODS Universe 164
Colleague Courses and Faculty ODS Universe 167
Colleague Financial Aid ODS Universe 169
Colleague Human Resources ODS Universe 172
Colleague Finance ODS Universe 174
Colleague Advancement ODS Universe 176
Installing the Business Objects Connector 178
Reporting Using the Universes in Web Intelligence 206
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 153© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Overview of the Business Objects Connector
The DataOrchestrator ODS solution offers a Business Objects Connector as an optional module (BOBC). This Connector delivers a set of universes for Business Objects that facilitates a broad range of reporting capabilities using the DataOrchestrator ODS data models. With this Connector, you can utilize the Business Objects reporting platform, including Web Intelligence, Dashboard Manager, and Crystal Reports.
If your institution uses Business Objects’ reporting and analysis tools, the universes provide you with a metadata layer (information about the data) on top of your ODS target database. This metadata layer gives you a number of distinct advantages over simply using direct access to the native database tables, and can greatly enhance the ability of the data models to accomplish the reporting strategy at your institution. These advantages include:
All join relationships are specified between ODS tables and views, including whether each join should be an inner or outer join. When you build reports, you do not need to specify these joins in your report definitions.
Reporting users can access the tables and columns in the data models using names that reflect business-oriented terms, rather than the technically oriented Colleague field names used in the data models.
For numeric quantities in the data models, the universes specify how individual data elements are aggregated when multiple source records are grouped in reports. For example, the universes detail that certain numeric measures are summed, such as individual transaction dollar amounts, while other measures are not summed, such as account opening balances.
Additional reporting data elements are provided. These data elements are calculated from information in the data models to simplify reporting and to capture business rules that should be reflected in all user-developed reports. These additional data elements can be the result of applying mathematical or logical expressions to one or more of the database elements.
Information is contained in the metadata layer about which sets of data elements are compatible with respect to cardinality. This specifies which data elements can be used in a single query without causing results that can lead to inaccurate or confusing reports.
The universes provide hierarchical relationships between reporting fields when the data in these fields contains a natural hierarchy that can be useful, especially when drilling into detail in interactive reports. For example, for the most prominent date values in reports, additional fields are provided
154 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Overview of the Business Objects Connector
that specify the year, quarter, and month for the date as derived fields and are related in the natural hierarchy.
Each data element in the universes is documented with descriptive information that is available to reporting users.
Several Business Objects’ reporting tools require a universe in order to access the data from a relational database. The universes provided in the Connector means that you do not need to develop universes to use these tools.
Reporting users will be more autonomous and less reliant on your IT staff for generating reports.
In general, the universes provide an advanced level of additional business knowledge about the data in the data models, beyond the basic data definitions expressed in the SQL Server or Oracle database system. These universes provide an extensive set of “information about the data” in the data models directly to your reporting users. This is especially the case for those users at your institution who need to develop complex and evolving reports using the Colleague data made available though the data models.
To use the universes in the Business Objects Connector, your institution must also have licensed BusinessObjects Enterprise XI 3.1 or higher. The universes can be used as data sources by Web Intelligence and Crystal Reports for report development, and also by more specialized tools such as Dashboard Builder and Performance Manager.
Business Objects Enterprise XI 3.1 (or higher) also includes BusinessObjects Designer to develop and maintain universes. You can use Designer to customize and extend universes to adapt them for your institution’s specific reporting strategy.
The Business Objects Connector also contains a set of sample reports that have been built using these universes. To use the sample reports, you must have BusinessObjects Enterprise XI 3.1 or higher. Sample reports are included for each of the applications and reporting subject areas in the data models. The reports are provided as templates and are intended to be used as a reference when you start report development. When you customize the reports for your institution’s requirements and data, you will need to verify the report logic and output in your environment. For more information on the sample reports, including a list of the reports provided and a description of these reports, see Support Solution 6582.
Note: After you have customized and tested a report for your institution, you can remove the disclaimer at the top of the report before deploying the report in production.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 155© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Universes Delivered in the Business Objects Connector
The universes delivered with the Connector are shown in Table 29.
Table 29: Universes Delivered with the Business Objects Connector
Universe Purpose
Colleague Core ODS Supports reporting using the CORE data model’s tables and view, providing demographic and academic credential data for persons and organizations in Colleague.
Colleague Student ODS Supports reporting using the CORE and Student data models’ tables and views, providing demographic and academic credential data for students in Colleague, and supporting reporting for enrollment, billing, academic programs, and financial aid.
Colleague Courses and Faculty ODS Also supports reporting using the CORE and Student data models, but from a course and faculty perspective, providing demographic and academic credential information for faculty, and information on course section offerings and faculty assignments.
Colleague Financial Aid ODS Supports reporting using the CORE and Student data models’ tables and views, providing financial aid data including term award data, student loan information, student financial aid status, and ISIR FAFSA data.
Colleague Human Resources ODS Supports reporting using the CORE and Human Resources data models, providing demographic information about employees, and position, benefit, and payroll information about the institution’s employees.
156 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Universes Delivered in the Business Objects Connector
Each universe contains a set of field-level data objects that can be used to create reports. The objects are organized into classes that provide related sets of data elements to help the reporting user navigate through the many data elements in each universe.
Colleague Finance ODS Supports reporting using the CORE and Finance data models, providing demographic information on the institution’s vendors, and reporting for the accounts payable and purchasing areas, and detailed and summary reporting for GL accounts activity.
Colleague Advancement ODS Supports reporting using the CORE and Advancement data models, providing demographic data about the individuals and organizations donating to your institution, and information about contributions and pledges.
Table 29: Universes Delivered with the Business Objects Connector (cont’d)
Universe Purpose
ALERT! After installing the universes, you must set up security controls in your BusinessObjects environment to ensure that access to your institution’s information is restricted to the appropriate users. The Business Objects Connector does not provide any security classes or security controls for data access.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 157© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Description of the UniversesIn the next sections, a description is given for each of the universes, including:
Subject areas used for each universe.
The classes designed for each universe, with the main data source and the data source’s description.
Any specific instructions needed for using the universe.
The reporting questions used to design the universes are the same ones answered by the subject areas in the data models. To review those questions, refer the subject areas for each Colleague application in Part 3, “Using the DataOrchestrator ODS Data Models” beginning on page 51.
Understanding Universe Contexts
In each universe, certain data objects can used together in reports, while others are incompatible because inaccurate or misleading information would result. To prevent this problem, universes have a mechanism for defining compatible data objects, called a context.
Contexts are used in the Business Objects Connector to simplify the structure of universes, and also to limit the scope of queries in Web Intelligence, so that only those queries that generate accurate results are produced.
When using the universes in Web Intelligence, your reporting users may notice that they are prevented from dragging a particular data element onto a report that contains an element in a different context. To see which elements are compatible, users can click on an object in the list of available data objects in a report. All incompatible objects in the list will be grayed out, so these objects cannot be selected.
Technical Tip: The best way to explore the contents of each universe is by using BusinessObjects Designer. This will help you to understand both the use of source database tables in the universe, and also the data objects and classes that are exported from the universe. All data objects in the universes are documented with descriptions that are available to Web Intelligence users when they build their reports.
158 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Adding New Tables and Joins to Universes
When you customize a data model by adding new transforms or views, you must also customize the associated universe. To do this, access Universe Designer and perform these steps:
Step 1. Open the universe.
Step 2. In the Structure pane on the right, add the new tables or views.
Step 3. In the Universe pane on the left, add the new objects.
This makes the new data available to WebIntelligence reporting users. However, this may also require creating joins between the new tables or views and existing objects, so all of these objects can be used together in your WebIntelligence reports. If you need to add joins, you must first add them to the universe, and then add them to one or more of the universe contexts. In Universe Designer, perform these steps:
Step 4. In the Structure pane, click the two objects to be joined, and then right-click on the background. Select Join from the popup menu.
Step 5. In the Edit Join window, enter the properties for the join, and then click OK.
Step 6. In the Standard toolbar, click the View List Mode button to show the Contexts window.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 159© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 7. Are you using an existing context or adding a context?
Existing context. Select an existing context by double-clicking it. When the Edit Context window appears, click one or more of the joins listed in the Current context join list to add any new joins you have created to the context.
Adding a context. To create a new context, right-click on an existing context, and then select Insert Context. When the New Context window appears, add the Context Name and Description. Next, click each of the joins listed in the Current context join list to add any new joins you have created to the context and click on any of the currently selected joins that you no longer want to include. Finally, click OK to add the join to the context.
Determining which context to modify or to add requires careful analysis and may require trial-and-error testing. One of the key considerations is how the cardinality of the new tables relates to the cardinality of the tables in the context. You must consider whether combining records from the set of tables in the context could result in a Cartesian product that causes inaccuracies in the measures defined in the universe.
For example, consider a context that contains GL Transactions and GL Accounts, where the Debits and Credits in the GL Transactions are measures in the universe. Suppose that you want to add a new table to this context that provides information that also relates to GL Accounts, such as Voucher Items. However, this new table does not have a direct join to individual GL Transactions. Therefore, selecting fields from all the tables will result in a Cartesian combination of records. The Debit and Credit measures will be inaccurate, because individual records will be counted multiple times in totals.
As a strategy to prevent this, you may be able to identify an existing context that will help you. The universe may already contain joins between tables and views that are similar to what you need, and which has a cardinality consistent with the new tables. In addition, most of the BO Connector universes contain a context named as a Comprehensive context. These comprehensive contexts contain most of the joins in the universe, with the exception of joins to tables containing Distinct measures provided by the universe. If the tables you add do not contain any measures, then Datatel recommends that they be added to the comprehensive context.
Note: Be sure that when you work with dimension data from two or more tables in the same WebIntelligence report, that the joins between these tables exist together in at least one context.
160 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Understanding Measures in the Connector’s Universes
When a universe has a numeric measure, you may see two versions of that measure. This is done to give you a choice in how measures are aggregated when used with multivalued dimensions. The two versions are named the same, except that one version has the keyword Distinct appended to it.
The first version (without the keyword) provides a sum or average broken out by dimension, whether or not the dimension is multivalued. However, note that when measures are broken out by multivalued dimensions, the totals may reflect double counting of measure values.
The second version (with the Distinct keyword) prevents double counting. These measures cannot be sliced or filtered by any multivalued dimensions. However, all totals created will be accurate, as only distinct values of the measure being aggregated are included.
For more information, see “Using Measures with Distinct Totals and Averages” on page 209.
Customizing the Universes
You may need to customize the universes in the Connector for your institution. In this case, you must keep track of these customizations so that when software updates are issued for the Connector, you can reapply your changes.
One technique you can use to track your changes is to create a new customization class in each universe that you customize. Make this new class hidden from report users. When you create a new customized object to export from a universe, create the object in the hidden class, and then copy it to the class where you want it to appear to your report users.
You can also group the objects you create in this hidden class into subclasses, according to the classes where you want the objects to appear. Then, when a new version of the Connector is delivered, you can simply copy the customization class to the new universe, and then copy your customized objects to the classes where you want them to appear.
When software updates are issued for the Connector, you also need to incorporate any tables you created into the new universes. Any changes you made to joins for tables or modifications to universe contexts must also be reapplied.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 161© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Colleague Core ODS Universe
This universe supports reporting for the following subject areas in the Colleague Core (CORE) application:
Demographics
Academic Credentials and Graduation
For more information about these subject areas, see “CORE Data Model” beginning on page 51.
The classes exported from this universe are described in Table 30.
Table 30: Classes for the Colleague Core ODS Universe
Classes Description
Academic Credentials
Academic Credentials – Major Information
Academic Credentials – Minor Information
Contains information on the academic credentials of applicants, graduates, and institutional staff, with all majors and minors associated with these credentials listed. Academic credentials for your institution’s graduates provide the permanent academic record of their history at the institution.
Institution Contains information on institutions; for example, educational institutions, companies, or vendors. The source is the records in ODS_PERSON that represent institutions (rather than individuals).
Institution Attendance and Achievement
Contains information on the prior institutions attended by individuals and their academic achievements (degrees, grades) at those institutions. This class relates elements in the Person class to institutions represented by elements in the Institution class.
Institution Address
Person Address
Contains address and phone number information for institutions and individuals.
Person Contains information on individuals and their addresses. The source is the records in ODS_PERSON that represent individuals (rather than institutions or companies).
Rooms Contains information about the campus rooms that can be assigned as student residences or other purposes. Information is included about the building the room is in, its type, and capacity.
162 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
There are three different join relationships between the Person and the Person Address tables:
Based on the preferred address recorded for the person.
Based on the hierarchy address recorded for the person.
Based on all the addresses recorded for the person.
When you select data elements for a person’s address, the universe will need to determine which of these relationships the reporting user wants to use for their reporting purposes. Web Intelligence will resolve this by prompting the reporting user for which of these relationships to use for the current query.
Similarly, there are three relationships between an institution and its address, and this, too, will be resolved by prompting the user at report execution time.
Classes in the Colleague Core ODS universe are referenced by other universes using universe linking. Linking is done when information in the CORE classes is used with classes in another universe to give a complete view of a subject area.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 163© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Colleague Student ODS Universe
This universe supports reporting for the following subject areas in the Colleague Student application:
Applicants and Applications
Enrollment, Academic Programs, Test Scores and Room Assignments
Billing
Financial Aid
For more information about these subject areas, see “Student Data Model” beginning on page 63.
The classes exported from this universe are described in Table 31.
Table 31: Classes for the Colleague Student ODS Universe
Classes Description
Academic Credentials
Academic Credentials – Major Information
Academic Credentials – Minor Information
In this universe, provides the academic credentials of students and applicants.
Academic Programs
Academic Program Locations
Contains information on the current set of academic programs offered by the institution and the campus locations of each.
Applicants
Applications
Application Influences
Contains information on the institution's applicants and their applications.
Application Status History Provides the record of the status changes that each application has gone through.
AR Account Holder
AR Accounts
AR Invoice Items
AR Invoices
AR Payment Items
Provides both account-level and payment and charge-level detail on each student's institutional accounts.
164 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
AR Deposits Provides information on the deposits that each student has made to the student’s AR accounts, including the type, date, and term to which the deposit applies.
AR Payment Plans Provides the current state of student payment plans for the student’s AR accounts, including the current status and latest balance.
AR Term Balances Provides a term-level summary of student account balances derived from current and past terms.
FA Term Awards Provides information on each student's financial aid awards.
Institution
Institution Address
In this universe, provides detailed information about the institutions attended by the applicants and students described in the other classes.
Institution Attendance and Achievement
In this universe, provides the prior institutions attended by the applicants and students described by the other classes.
Person
Person Address
In this universe, provides demographic and address data for students, and can be used in conjunction with other student-level classes in the Colleague Student ODS universe.
Students Contains information on individual students.
Student Cohort Groups Provides information on the cohort groups of students identified at your institution.
Student Education Plans Provides information on the planned enrollment for students in future terms.
Student Enrollment Provides information on each current and completed student course enrollment, including information about the enrolled course and section, and the student's performance in the course.
Student Programs
Student Program Majors
Student Program Minors
Student Program Specializations
Student Program CCDs (Credentials, Certificates, or Diplomas)
Provides current program-level information about a student’s status and performance.
Table 31: Classes for the Colleague Student ODS Universe (cont’d)
Classes Description
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 165© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Included in the Colleague Student ODS universe are the classes from the Colleague Core ODS universe. This makes the CORE tables available for use in conjunction with student information.
The central table from CORE, ODS_PERSON, has been joined to the ODS_STUDENTS table. Therefore, referencing person demographic data and academic achievement data in the Colleague Student ODS universe returns information on students.
Student Terms Provides term-level summary information about the performance and status of students.
Student Room Assignments Provides information on the assignment of students to campus rooms, including the start and end dates of the assignment, and the current status of the assignment.
Tests and Non-course Activities
Contains information from the ODS_STUDENT_NON_COURSES view, giving test scores on non-course related tests, such as standardized tests, and credit-earning activities not associated with specific courses.
Table 31: Classes for the Colleague Student ODS Universe (cont’d)
Classes Description
166 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Colleague Courses and Faculty ODS Universe
This universe supports reporting for the Courses and Faculty subject area in the Colleague Student application. For more information about this subject area, see “Courses and Faculty” beginning on page 68.
The classes exported from this universe are described in Table 32.
Table 32: Classes for the Colleague Courses and Faculty ODS Universe
Classes Description
Academic Credentials In this universe, provides information on the academic credential of your institution's faculty members.
Course Sections
Course Section Types
Provides information about the courses offered at your institution and how they are categorized by section type.
Course Section Meeting Provides information on the specific meeting times and dates for course sections, including the instructional method (lecture, lab) for each.
Course Section Waitlist Entries
Provides the list of students on the waitlist for specific course sections.
Faculty
Faculty Course Section Assignments
Provides information about the faculty of your institution and the course sections they teach.
Faculty Non-teaching Assignments
Provides information about all non-teaching academic activities by faculty members, including student advising and student organization participation.
Institution
Institution Address
In this universe, provides detailed information about the institutions attended by the faculty described in the other classes.
Institution Attendance and Achievement
Contains information on the prior institutions attended by individuals and their academic achievements (degrees, grades) at those institutions. This class relates elements in the Person class to institutions represented by elements in the Institution class.
Person
Person Address
In this universe, provides demographic and address information about faculty members.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 167© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Included in the Colleague Courses and Faculty ODS universe are the classes from the Colleague Core ODS universe. This makes the CORE tables available for use in conjunction with faculty information.
The central table from CORE, ODS_PERSON, has been joined to the ODS_FACULTY table. Therefore, referencing person demographic data and in the Colleague Courses and Faculty ODS universe returns information on faculty.
168 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Colleague Financial Aid ODS Universe
This universe primarily supports reporting for the Financial Aid subject area within the Colleague Student application. It also supports reporting on the following subject areas in conjunction with Financial Aid information:
Applicants and Applications
Enrollment, Academic Programs, and Test Scores
Billing
For more information about these subject areas, see “Student Data Model” beginning on page 63.
The classes exported from this universe are described in Table 33.
Table 33: Classes for the Colleague Financial Aid ODS Universe
Classes Description
Student Current FA Information
Provides current information on Financial Aid recipients including their counselors, academic progress assessments, and interviews.
Student Yearly FA Information
Provides academic year-specific information on Financial Aid recipients including family contribution information and details of the ISIR FAFSA used for the students’ FA awards.
Student Loans Provides detailed information on the subset of the awards for a student that are CommonLine or Direct loans.
FA Term Awards Provides information on each student’s financial aid awards by term. Includes information about all FA awards and award-specific information.
ISIR FAFSA Provides detailed information on the ISIR FAFSA records sent for each student for each calendar year.
Academic Credentials
Academic Credentials – Major Information
Academic Credentials – Minor Information
In this universe, provides the academic credentials of students and applicants.
Academic Programs
Academic Program Locations
Contains information on the current set of academic programs offered by the institution and the campus locations of each.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 169© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Applicants
Applications
Application Influences
Contains information on the institution’s applicants and their applications.
AR Invoice Items Provides charge-level detail on each student’s institutional billing accounts.
Institution
Institution Address
In this universe, provides detailed information about the institutions attended by the applicants and students described in the other classes.
Institution Attendance and Achievement
In this universe, provides the prior institutions attended by the applicants and students described by the other classes.
Person
Person Address
In this universe, provides demographic and address data for students, and can be used in conjunction with other student-level classes in the Colleague Student ODS universe.
Students Contains information on individual students.
Student Enrollment Provides information on each current and completed student course enrollment, including information about the enrolled course and section, and the student’s performance in the course.
Student Programs
Student Program Majors
Student Program Minors
Student Program Specializations
Student Program CCDs (Credentials, Certificates, or Diplomas)
Provides current program-level information about a student’s status and performance.
Student Terms Provides term-level summary information about the performance and status of students.
Tests and Non-course Activities
Contains information from the ODS_STUDENT_NON_COURSES view, giving test scores on non-course related tests, such as standardized tests, and credit-earning activities not associated with specific courses.
Table 33: Classes for the Colleague Financial Aid ODS Universe (cont’d)
Classes Description
170 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Included in the Colleague Financial Aid ODS universe are the classes from the Colleague Core ODS universe and a subset of the classes from the Colleague Student ODS Universe. This makes the CORE and ST tables available for use in conjunction with student Financial Aid information.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 171© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Colleague Human Resources ODS Universe
This universe supports reporting for the following subject areas in the Colleague HR application:
Employee Information
Payroll Information
For more information about these subject areas, see “HR Data Model” beginning on page 93.
The classes exported from this universe are described in Table 34.
Table 34: Classes for the Colleague Human Resources ODS Universe
Class Description
Benefit Deduction Costs Provides cost information for the current configuration of the institution's benefits. This class provides reporting on all the cost factors for each of the institution's benefits as applicable, including the fixed deduction amounts, gross salary percentage amounts, and employee salary multiple factors for insurance levels.
Benefit Deduction Cost Tables
Provides cost information for benefits determined by gross salary ranges.
COBRA Qualifying Events and COBRA Dependents
Provides detailed information about the employee's eligibility for COBRA and their qualified dependents.
Contract Load Period Positions
Provides the contract positions that employees are assigned.
Employee Benefit Deductions
Contains information about the benefit enrollment of the institution's employees, including coverage and enrollment dates, and basic COBRA information. This information is provided for all current and canceled benefit enrollments for both current and former employees.
Employee Current Benefit Costs
Provides cost information for the current benefits for specific employees. This includes only information for current employees, and provides costs as configured for each employee, including the override flags indicating that the standard costs have been overridden for particular employees.
172 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Employee Current Positions Provides information about all positions currently held by each employee, including current position salary information, and the employee administrative and supervisory reporting chain. Historical information about positions formerly held by each employee is not kept in the operational data store.
Employee HR Info Provides current information about an employee's employment with the institution, including primary position and location, overall tenure information, and current employment status. This class supports reporting both on current employees and those who are no longer active with the institution.
Employee Job Skill Provides information on the specific job skills recorded for each employee, including licensing information.
Employee Leave Accrual Provides current information about employees' leave plans, including the current leave accrual rate and current leave balance.
Employee Leave Transactions
Includes all leave taken by all employees and their leave balances over time.
Employee Payroll Information
Provides paycheck earnings information for all employees. Information is included for all past paychecks for active employees, including the earnings types and paid amounts. This also includes the amounts deducted from the employee's paycheck for the employee's benefits and taxes, respectively. This class also contains paycheck amounts paid by the employer for enrolled benefits and taxes, including the department, division, and school for which the employee is working, and the GL account information for the source of funding.
Employee Stipend Information
Provides detailed information about the stipends being paid to current employees.
HR Check Addresses Provides the address information entered for employee paycheck distribution. This is distinct from the standard personal address information recorded in CORE.
Person and Person Address Provides basic demographic information about employees and their addresses.
Remittances Provides reporting on the remittances that have been generated to pay benefit providers. This class can be used to analyze the actual amounts paid to vendors for the benefits for specific employees.
Table 34: Classes for the Colleague Human Resources ODS Universe
Class Description
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 173© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Colleague Finance ODS Universe
This universe supports reporting from the following subject areas in the Colleague Finance application:
Accounts Payable and Purchasing
GL Transaction Information
For more information about these subject areas, see “Finance Data Model” beginning on page 109.
The classes exported from this universe are described in Table 35.
Table 35: Classes for the Colleague Finance ODS Universe
Classes Description
Blanket Purchase Orders
BPO Vendor Address
BPO GL Expenses
Provides information on Blanket Purchase Orders (BPO), including overall BPO information such as status, buyer, vendor, and dates. Gives the breakout of BPO charges by GL Account and the running balance for the BPO. Also includes the addresses of vendors associated with the BPOs.
Document Items Provides information on the document items that move through the Purchasing and Accounts Payable process. Gives detailed document item information, including line item descriptions, commodity information, and the various quantities associated with the line item.
Encumbrance Purchase Orders
Provides information on the Purchase Orders for specific encumbrances, including the PO number, vendor information, etc.
Encumbrance Requisitions Provides information on the requisitions for specific encumbrances.
GL Accounts Provides information on each GL Account defined at the institution. This supports querying using specific GL Account information that is not specific to a particular fiscal year, including the GL Account components as defined at your site.
GL Account Fiscal Year Status Info
Provides information on each GL Account that is specific to a particular fiscal year, including account opening balances, account status, the responsible budget officer, and account ledger status.
GL Account Monthly Summary
Provides a monthly summary of GL transaction activity for each GL Account, including actual, budget, and encumbrance dollars, and calendar and fiscal year month information.
174 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
GL Transaction Detail Provides detailed information about each GL transaction in the ODS, including transaction dates, fiscal year month, transaction posted dollar amounts, and GL classification information.
Projects Provides basic definitional information about the accounting projects referenced by Requisitions, Purchase Orders, and Vouchers.
Purchase Orders
PO Vendor Address
PO Item GL Expenses
Provides information about Purchase Orders (PO), including overall document-level information such as PO status, PO date, and vendor information. Gives information about the breakout of purchase order expenses and taxes by GL Account, and vendor address information.
Recurring Vouchers
Recur Voucher Item GL Expenses
Provides information about the institution’s recurring vouchers, including the status and balance of each, as well as the individual line items and their allocation to GL accounts.
Requisitions
Requisition Vendor Address
Requisition Item GL Expenses
Provides information about requisitions, including overall document-level information such as requisition date and status, and vendor information. Gives information about the breakout of requisition expenses and taxes by GL Account, and also vendor address information.
Vendors
Vendor Contact Info
Provides information about vendors including name and contact information for the vendor. Also provides the discounts the institution has taken for the vendor.
Vouchers
Voucher Vendor Address
Voucher Item GL Expenses
Provides information about vouchers, including overall document-level information such as the voucher due date, check date, and posting date. Gives status and currency information, and the overall voucher actual amounts. Also provides the breakout of voucher expenses and taxes by GL Account, and vendor address information.
Table 35: Classes for the Colleague Finance ODS Universe (cont’d)
Classes Description
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 175© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Colleague Advancement ODS Universe
This universe supports reporting from the Demographics and Contributions subject area in the Colleague Advancement application. For more information about this subject area, see “Demographics and Contributions” beginning on page 127.
The classes exported from this universe are described in Table 36.
Table 36: Classes for the Colleague Advancement ODS Universe
Classes Description
Academic Credentials Provides information about the academic background of constituents, including the institution's own graduates.
Campaign Provides information about each of the institution's campaigns, including the campaign cost and revenue raised information, and the overall umbrella campaign.
Constituent
Constituent Address
Provides demographic data about the constituents in the source Colleague environment. This information comes from the PERSON file in the source database, and provides information about people and institutions, whether or not they have made contributions to the institution.
Constituent Multivalued Attributes (subclass)
This subclass of the Constituent class contains attributes of constituents who are of specific concern for institutional advancement reporting. These attributes are multivalued and so must be used with care in reports to avoid duplication of output report rows.
Contribution Info Provides information about individual contributions by type (New Pledge, Pledge Payment, Gift, etc.) and by Donor and Designation. The measures of this class are the contribution Hard, Soft, and Matching Credit dollar amounts. This class also includes information about the pledge associated with the payment.
Designation Provides information about the designations of contributions.
Institution Provides institutional demographic information about specific institutions attended by constituents.
176 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Description of the Universes
Institution Attendance and Achievement
Provides detailed information about the institutional attendance of constituents and their overall performance at these institutions, including the institution's own graduates and their academic performance while students.
Recognition Program Membership
Provides information about the membership of donors in institutional donor recognition programs.
Table 36: Classes for the Colleague Advancement ODS Universe (cont’d)
Classes Description
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 177© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Installing the Business Objects Connector
The installation process for the Business Objects Connector can be categorized into the following five major tasks:
1. Run the “DataOrchestrator ODS Business Objects Connector” InstallShield from the Datatel website to download the following two BusinessObjects Business Intelligence Archive Resource (BIAR) files:
• do_bo_connector_1_1.biar. This file contains the universes for Business Objects.
• do_sample_reports_1_1.biar. This file contains sample reports for the universes.
2. If your institution has licensed the Datatel Reporting and Operating Analytics solution, run the “Datatel SAP Business Objects Custom Setup” InstallShield from the Datatel website to replace the files used for branding in Business Objects.
3. Import the universes from the do_bo_connector_1_1.biar file to your BusinessObjects repository.
4. (Optional) Import the sample reports from the do_sample_reports_1_1.biar file to your BusinessObjects repository.
5. Customize the universes for your ODS target database environment as follows:
a. Modify each universe to point to the data source where you built your operational data store.
b. Customize the universe used to access the Colleague Finance data to add General Ledger components.
c. (Only for an Oracle ODS target database) Modify the universes to use Oracle-specific objects.
These tasks are described in the next sections.
ALERT! If you are migrating from BusinessObjects Enterprise XI R2 to XI 3.1 and have previously installed universes from the Business Objects Connector 1.01, see Support Solution 7136.
178 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Running the “DataOrchestrator ODS Business Objects Connector” InstallShield
The first major task in the installation process is to run the InstallShield to download the BusinessObjects Business Intelligence Archive Resource (BIAR) files which contains the universes and sample reports.
Procedure for Running the InstallShield
Perform the following procedure to run the InstallShield.
Step 1. Access the Software Downloads area of the Datatel website.
a. Select the link for “Datatel Reporting and Operating Analytics” on the left-hand side of the page.
b. Select the link at the top left of the page for “Datatel Business Objects Connector for DataOrchestrator ODS.”
c. Click on the download link for the “DataOrchestrator ODS Business Objects Connector” InstallShield to save the InstallShield file to your computer.
Step 2. After the file is downloaded to your computer, open the folder where the file resides, and double-click the setup.exe file icon to start the InstallShield wizard. You see a message that tells you the InstallShield wizard is being prepared.
Step 3. On the Welcome window, click Next to continue.
Step 4. On the Customer Information window, enter the Organization Name, Organization Code, and Organization Password provided to you by Datatel. Click Next to continue.
Note: If you are upgrading from a previous version of the Business Objects Connector, after you double-click the setup.exe file icon, you first see a message that asks you to confirm that you want to continue. Click Yes to install the current version.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 179© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 5. The InstallShield automatically contacts Datatel and uses the Organization Code and Organization Password you entered to confirm the identity of your institution and to check your licensing status.
Step 6. On the License Agreement window, click I accept the terms in the license agreement to accept the license agreement. Click Next to continue.
Step 7. On the Destination Folder window, accept the default path for installation or click Change to specify another folder.
Step 8. You see a message that the InstallShield wizard is ready to install the program. Click Install to begin the installation.
Step 9. You see a message that the Business Objects Connector is being installed, that is, the BIAR files containing the universes and sample reports are being downloaded to your computer.
Step 10. When the installation is finished, you see the InstallShield Wizard Completed window. Click Finish to exit the wizard.
180 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Running the “Datatel SAP Business Objects Custom Setup” InstallShield
The second major task in the installation process is to run the InstallShield to download the branding files and install them in the appropriate directory on the Business Objects server.
Procedure for Running the InstallShield
Perform the following procedure to run the InstallShield.
Step 1. Access the Software Downloads area of the Datatel website.
a. Select the link for Business Objects XI 3.1 on the left-hand side of the page.
b. Click on the download link for the “Datatel SAP Business Objects Custom Setup” InstallShield to save the InstallShield file to the Business Objects server.
Step 2. After the file is downloaded to your computer, open the folder where the file resides, and double-click the setup.exe file icon to start the InstallShield wizard. You see a message that tells you the InstallShield wizard is being prepared.
Step 3. On the Welcome window, click Next to continue.
Step 4. On the Customer Information window, enter the Organization Name, Organization Code, and Organization Password provided to you by Datatel. Click Next to continue.
Note: Run this InstallShield only if your institution has licensed the Datatel Reporting and Operating Analytics solution.
Note: The InstallShield must be run on the Business Objects server. Also, this server must be able to connect to the Datatel licensing server.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 181© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 5. The InstallShield automatically contacts Datatel and uses the Organization Code and Organization Password you entered to confirm the identity of your institution and to check your licensing status.
Step 6. On the License Agreement window, click I accept the terms in the license agreement to accept the license agreement. Click Next to continue.
Step 7. On the Destination Folder window, you see the directory where the branding files will be copied. The InstallShield identifies the appropriate location for your Business Objects installation. This location cannot be modified.
Step 8. You see a message that the InstallShield wizard is ready to install the program. Click Install to begin the installation.
Step 9. You see a message that the custom setup is being installed on your computer. When the installation is finished, you see the InstallShield Wizard Completed window. Click Finish to exit the wizard.
182 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Importing the Universes
The third major task in the installation process is to import the universes from the do_bo_connector_1_1.biar file to your BusinessObjects repository.
The instructions provided here are not intended to be a comprehensive guide to managing universes, such as those contained in the Connector, or to configuring and managing a Web Intelligence reporting environment. Therefore, topics that are not covered here include:
Setting up and maintaining appropriate security controls to restrict access to the universes.
Maintaining an appropriate test and production reporting environment for your BusinessObjects installation.
For more information, you may want to refer to the BusinessObjects documentation at the following link:
http://help.sap.com/
Under the heading “Popular Documentation,” click SAP BusinessObjects Solution Portfolio Knowledge Center.
Universes Delivered with Business Objects Connector 1.1
The Business Objects Connector 1.1 delivers the Colleague Financial Aid universe and updated versions of all previous universes (except Colleague Advancement). Each universe is named with the release level of that universe. These universes are delivered in a different repository folder than prior versions: Datatel BO Connector 1.1.
Note: This section assumes that you are familiar with the operation of the BusinessObjects Import Wizard and BusinessObjects Designer. Using the Business Objects Connector requires that your institution have a qualified administrator for your BusinessObjects reporting environment.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 183© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
The contents of the do_bo_connector_1_1.biar file is as follows:
Repository folder Datatel BO Connector 1.1
Universes: • Colleague Advancement ODS R1• Colleague Core ODS R1_1• Colleague Courses and Faculty ODS R1_1• Colleague Finance ODS R1_1• Colleague Financial Aid ODS R1• Colleague Human Resources ODS R1_1 • Colleague Student ODS R1_1
Procedure for Importing Universes from the BIAR File
Perform the following steps to import the universes from the do_bo_connector_1_1.biar file to your BusinessObjects repository.
Step 1. From the Start menu on your computer, access Business Objects XI 3.1 or higher.
a. Under BusinessObjects Enterprise, start the Import Wizard.
b. The Welcome window is displayed. Click Next to continue.
Note: Loading these universes into your BusinessObjects repository will not overwrite any universes you have loaded in previous releases of the Business Objects Connector.
Note: For clients who have installed and customized the 1.02 version of the universes, Support Solution 7797 describes a method of manually incorporating the newly delivered updates into your customized universes.
184 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 2. The Source environment window is displayed, as shown in Figure 29.
Figure 29: Specifying the Source Environment
Step 3. In the Source field, select Business Intelligence Archive Resource (BIAR) File from the drop-down list.
Step 4. In the BIAR file field, enter the following:
do_bo_connector_1_1.biar
Note: Optionally, you can click the ... button at the end of the field to navigate to the folder where you downloaded the BIAR files, and then select the BIAR file to import.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 185© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 5. Click Next to continue. The Destination environment window is displayed, as shown in Figure 30.
Figure 30: Specifying the Destination Environment
Step 6. Enter the destination environment as follows:
a. CMS Name. Name of the destination BusinessObjects server to which content is being copied.
b. User Name. Name of the user on the destination server with access to all objects being copied, including the folders, connections, and universes.
c. Password. Password of the user specified in the User Name field.
186 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 7. Click Next to continue. The Select objects to import window is displayed, as shown in Figure 31.
Figure 31: Choosing Objects to Import
Step 8. Uncheck all boxes except the following:
Import universes
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 187© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 9. Click Next to continue. The Incremental import window is displayed, as shown in Figure 32.
Figure 32: Choosing Incremental Import Options
Step 10. Uncheck the last box: Overwrite object rights. Click Next.
Step 11. Click Next on the warning note on importing object rights.
188 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 12. Click Next to continue. The Import options for universes and connections window is displayed, as shown in Figure 33.
Figure 33: Selecting the Import Options for Universes and Connections
Step 13. Select the first option, Import all universes and all connection objects.
Step 14. Click Next to continue. The Ready to import window is displayed. Click Finish to import the universes.
Technical Tip: If you want to import only individual universes instead of the entire set of universes, select the third option in Figure 33 “Import the universes and connections that the selected Web Intelligence and Desktop Intelligence documents use directly.” This will present you with a list from which you can select only the universes you want to import. If you import the Colleague Courses and Faculty ODS universe, the Colleague Student ODS universe, or the Colleague Human Resources ODS universe, you must also import the Colleague Core ODS universe. Otherwise, the universe import will encounter errors.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 189© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 15. When the import process is complete, verify that the information window indicates that there were no errors for the import.
Step 16. If there any indications of problems in the import, click View Detail Log. To see detailed information regarding the import process for a specific item, select the line for that item in the log.
Step 17. Click OK to continue. The Import Wizard window appears again.
Step 18. Click Done to exit the Import Wizard.
Note: After this point you cannot go back in the process using the Back button. To correct any errors, you must exit and restart the Import Wizard, and then enter the values from the beginning.
190 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Importing the Sample Reports
The fourth major task in the installation process is optional: you can import sample reports from the do_sample_reports_1_1.biar file to your BusinessObjects repository.
Importing the contents of the file will create a new folder called Datatel Sample Reports - Release 1.1 on your BusinessObjects server containing a set of subfolders, one for each of the universes. Sample reports for each universe are stored within these subfolders.
The instructions provided here are not intended to be a comprehensive guide to managing reports, such as those contained in the Connector, or to configuring and managing a Web Intelligence reporting environment. Therefore, topics that are not covered here include:
Setting up and maintaining appropriate security controls to restrict access to the reports.
Maintaining an appropriate test and production reporting environment for your BusinessObjects installation.
For more information, you may want to refer to the BusinessObjects documentation at the following link:
http://help.sap.com/
Under the heading “Popular Documentation,” click SAP BusinessObjects Solution Portfolio Knowledge Center.
Note: This section assumes that you are familiar with the operation of the BusinessObjects Import Wizard and BusinessObjects InfoView. Using the Business Objects Connector requires that your institution have a qualified administrator for your BusinessObjects reporting environment.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 191© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Procedure for Importing Sample Reports from the BIAR File
Perform the following steps to import the sample reports from the do_sample_reports_1_1.biar file to your BusinessObjects repository.
Step 1. From the Start menu on your computer, access Business Objects XI 3.1 or higher.
a. Under BusinessObjects Enterprise, start the Import Wizard.
b. The Welcome window is displayed. Click Next to continue.
Step 2. The Source environment window is displayed, as shown in Figure 34.
Note: The new sample reports folder delivered with Business Objects Connector 1.1 contains the complete set of sample reports, including: - New reports - Reports that have been updated with new data elements - Previously released reports not affected by updates to the universes After installing this folder, you no longer need the folder named Datatel Sample Reports which contained previous releases of the reports.
192 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Figure 34: Specifying the Source Environment
Step 3. In the Source field, select Business Intelligence Archive Resource (BIAR) File from the drop-down list.
Step 4. In the BIAR file field, enter the following:
do_sample_reports_1_1.biar
Step 5. Click Next to continue. The Destination environment window is displayed, as shown in Figure 35.
Note: Optionally, you can click the ... button at the end of the field to navigate to the folder where you downloaded the BIAR file, and then select the BIAR file to import.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 193© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Figure 35: Specifying the Destination Environment
Step 6. Enter the destination environment as follows:
a. CMS Name. Name of the destination BusinessObjects server to which content is being copied.
b. User Name. Name of the user on the destination server with access to all objects being copied, including the folders, connections, and universes.
c. Password. Password of the user specified in the User Name field.
194 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 7. Click Next to continue. The Select objects to import window is displayed, as shown in Figure 36.
Figure 36: Choosing Objects to Import
Step 8. Uncheck all boxes except the following:
Import folders and objects
Click Next to continue.
Step 9. Click Next on the warning note that you have chosen to import no universes from the source environment.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 195© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 10. The Incremental import window is displayed, as shown in Figure 37.
Figure 37: Choosing Incremental Import Options
Step 11. Uncheck the last box: Overwrite object rights. Click Next.
Step 12. Click Next on the warning note on importing reports.
196 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 13. Click Next to continue. The Folders and objects window is displayed, as shown in Figure 38.
Figure 38: Selecting the Sample Reports
Step 14. Select the following option: Datatel Sample Reports.
Step 15. Click Next to continue. The Ready to import window is displayed. Click Finish to import the universes.
Step 16. When the import process is complete, verify that the information window indicates that there were no errors for the import.
Step 17. If there any indications of problems in the import, click View Detail Log. To see detailed information regarding the import process for a specific item, select the line for that item in the log.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 197© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 18. Click OK to continue. The Import Wizard window appears again.
Step 19. Click Done to exit the Import Wizard.
Note: After this point you cannot go back in the process using the Back button. To correct any errors, you must exit and restart the Import Wizard, and then enter the values from the beginning.
198 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Customizing the Universes for your ODS Target Database
The fifth major task in the installation process for universes is to customize each of the six universes to provide reporting access to your ODS target database.
Procedure for Customizing the Universes for Your ODS Target Database
Step 1. From the Start menu on your computer, access BusinessObjects XI Release 2 or higher. Under BusinessObjects Enterprise, start the Designer. The User Identification window is displayed, as shown in Figure 39.
Figure 39: Logging On to the Designer
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 199© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 2. Enter login information including the following:
a. System. Enter the name of the BusinessObjects server. For example, in Figure 39, the system is named “sdcertw3app.”
b. User Name. Enter the administrator user ID.
c. Password. Enter the administrator password.
Step 3. Click OK to continue. A blank Designer window is displayed.
Step 4. Select Import from the File menu. The Import Universe window is displayed, as shown in Figure 40.
Figure 40: Selecting a Universe to Import
Note: You do not need to change the value in the Authentication field. Leave the default value as it is.
200 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 5. From the Available Universes list, select the universe you want to customize. Make sure the Open the selected universes check box is selected. Click OK to continue.
Step 6. A message indicating that the import was successful is displayed. Click OK.
Step 7. Verify that the appropriate universe is now displayed, as shown in Figure 41.
Figure 41: Selected Universe Displayed in the Designer
Step 8. From the File menu, select Save As. Navigate to the folder on the server where backups of deliverables are stored, and click Save to save a copy of the delivered version of this universe file.
Note: Step 5 to Step 20 must be repeated for each of the universes.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 201© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Step 9. From the File menu, select Parameters. The Universe Parameters window is displayed, as shown in Figure 42.
Figure 42: Setting Up Universe Parameters
Step 10. Change the connection for the universe from Delivery Null Connection to a universe connection object in your BusinessObjects environment pointing to the ODS target database. If you do not have a universe connection defined, click New to use the New Connection Wizard.
Step 11. (Optional) Check the Control tab and adjust the following parameters as appropriate for your institution:
Limit Size of Result Set
Limit Execution Time
Step 12. On the SQL tab, verify that the following check box is selected: Multiple SQL statements for each context. This setting must be selected for the universe contexts to function correctly.
Step 13. Click OK to save the parameters for the universe.
202 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 14. Are you using an Oracle database?
No. Skip to Step 15.
Yes. Modify the query statements used by objects in the universe so that they use Oracle syntax. To do this:
1. From the Edit menu, select Replace.
2. In the Find what field, enter SQL Srvr.
3. In the Replace field, enter Oracle.
4. In the Look Also In section, ensure that only SQL is selected.
5. Click Replace All and close the Replace window.
Step 15. Are you customizing the Colleague Finance ODS universe?
Yes. Continue with Step 16.
No. Continue with Step 17 on page 204.
Step 16. Add components to the Colleague Finance ODS universe for your GL Account Number. To do this, perform the following steps in Designer:
a. Refresh the ODS_GL_ACCTS table in order to make the columns containing the GL component code and description available to Designer. To do this, highlight the ODS_GL_ACCT table on the right-hand panel of Designer. From the Views menu, select Refresh Structure.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 203© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
b. Add objects in the GL Accounts class for each GL component that you want to make accessible to your reporting users. For each GL component, the following columns are available from the ODS_GL_ACCTS table:
The code for the subcomponent in a field called XXX, where XXX is the name of the GL component.
The description of the subcomponent in a field called XXX_DESC.
The code and description concatenated together in a field called XXX_WITH_DESC.
For example, a component called FUND will have a column in the ODS_GL_ACCTS table called FUND, FUND_DESC, and FUND_WITH_DESC.
For each of the columns you want available to reporting users, click on the column name in the right-hand pane of Designer and drag it into the GL Accounts class in the left-hand pane.
Optionally, you can rename the object for the GL component as you want users to see it when they build reports. For example, if the source field is FUNC_DESC, you may enter Fund Description.
Step 17. Save and export the universe to your BusinessObjects server to make the reporting content available to your Business Objects users. From the File menu, select Save As. Select the folder with the BusinessObjects server name under the Universes file system folder.
Step 18. Click Save to save the modified version of the universe file.
204 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Installing the Business Objects Connector
Step 19. From the File menu, select Export. The Export Universe window is displayed, as shown in Figure 43.
Figure 43: Selecting the Universe to be Exported
Step 20. Select the appropriate file name from Universes: File Name and then click OK. Verify that a message is displayed indicating that the universe has been successfully exported.
Note: Step 5 to Step 20 must be repeated for each of the universes.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 205© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Reporting Using the Universes in Web Intelligence
The universes delivered with the Business Objects Connector can be used to facilitate reporting with Web Intelligence. The design of these universes assumes that report developers have a basic understanding of queries and reports using the techniques described in the following manuals from Business Objects:
Performing On-Report Analysis with Web Intelligence
Building Reports Using the Web Intelligence Java Report Panel
Building Reports Using the Web Intelligence HTML Report Panel
This section points out three specific techniques for more effective reporting from the universes:
Filtering queries versus filtering reports. How to improve the efficiency of reports by defining filters in the query panel so that they are evaluated on the database server, rather in the report.
Defining variables to create measures with distinct counts. How to easily get distinct counts of entities by creating report variables using the Web Intelligence Count function.
Using measures with distinct totals and averages. How to use the measures tagged with the Distinct keyword to get unduplicated aggregation calculations for report values.
Filtering Queries Versus Filtering Reports
Creating a report in Web Intelligence is a two-stage process:
Define a query for the report. To include data, report developers use the Java or HTML Report Panel to select objects from a universe and drag them onto the query panel.
Manipulate the data returned from the query. Report developers drag and drop the returned data objects into the configuration needed, and then add any summary elements or formatting for the report.
The data for a report can be filtered in either of these two stages:
As part of the query.
Within the report itself.
206 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting Using the Universes in Web Intelligence
It is important that report developers understand the difference between these two choices in order to create the most efficient reports. In general, as much processing as possible involved in running reports should be performed on the database level, rather than in the Web Intelligence report server. Filter conditions are usually best processed in the SQL statements generated and executed to get data for the report. This is accomplished in Web Intelligence by defining filters in the query panel, as opposed to within the report itself as shown in Figure 44.
Figure 44: Defining Filters in the Query Panel
If filtering is done as the query is executed, a much smaller data set is returned and displayed. This can save network transmission time, and provides more efficient processing by Web Intelligence as the report user works with the report’s data.
However, there are situations in which it may be advantageous to define the filter in the report, and bring in all data to Web Intelligence. Doing this allows the report user to work with the data without executing another database query, which can increase the effectiveness of ad hoc manipulation for reports. Your report developers should be aware of these trade-offs when designing queries and reports.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 207© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Defining Variables to Create Measures with Distinct Counts
Another simple technique that report developers can use in Web Intelligence is defining variables to create measures that perform distinct counts. This is useful because report users often need to answer questions about Colleague data that involve counting distinct entities, whether these are students, course sections, or donors. In the universes, these counts are not included as data objects, because these counts can result in elements being double counted when using multivalued dimensions.
The best way for report developers to ensure that the counts are exactly what is needed for a report is to define a variable that can be used as a report measure. To do this, report developers:
Access the variable editor and define the variable, with a name appropriate to the variable’s report context.
Select Measure from the Qualification drop-down list.
Enter the formula to calculate the variable by selecting the Count function from the Function tab, and then selecting the field to be counted in the Data tab.
Save from the variable editor. The default behavior of the count function is to count distinct values of the column in the categories selected for a report.
Count measures defined this way will cause only the distinct values for the specified data object to be included in the overall count returned as the value of any row.
Note: To count every record, including duplicate values of a data object, the report developer can specify the ALL parameter for the count function in the variable.
208 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting Using the Universes in Web Intelligence
Using Measures with Distinct Totals and Averages
In the universes, measures with distinct totals and averages are provided. When a universe has these measures, you see two versions of the measure. The two versions are named the same, except that one version has the keyword Distinct appended to the name. This section provides an overview of these measures and how they can be used in Web Intelligence.
When you use measures, a common problem that can arise is how they total and average if your query includes multivalued dimensions or dimensions from classes in the universe with more than one record matching each database record containing the measure.
For example, consider the Contribution Hard Credit Amt measure in the Colleague Advancement ODS universe. In this universe, the Contribution Sources dimension categorizes the constituent population that is the source of the donation (alumni, parent, faculty, etc.). However, more than one of these may apply (a specific donor can be a parent and an alumnus). Therefore, including the Contribution Sources dimension in a query will cause individual contributions to be included more than once (once for each source for the contribution).
In this case, the report will correctly categorize the overall contributions in each source category, but the overall total of the donation amounts will be inflated, as individual contributions will be included in more than one category. However, report users may want not only to analyze contributions by donation source, but also want to accurately report the total of contributions. Therefore, the universes provide additional measures with distinct totals and averages to allow report users to get these accurate totals.
The distinct measures provide a way to work with unduplicated measure aggregations. These measure are defined so that they cannot be sliced by multivalued dimensions. Summing a column that contains a distinct measure will always show the accurate total of unduplicated values.
These distinct measures can be used in reports in conjunction with the regular measures to support report users both in analyzing numeric data by multivalued dimensions, and also in preparing reports that show accurate,
Technical Tip: Any attempt to use a distinct measure with a multivalued dimension will result in a meaningless report in which the totals of the measure amounts are repeated for each value of the incompatible multivalued dimension. Similarly, applying a filter by a multivalued dimension to the report will not limit the distinct measure values that are displayed.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 209© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
unduplicated totals. Report developers can use both versions of the measures as shown in the examples below to create reports that support a variety of reporting requirements.
Using Measures without Distinct Totals and Averages
Report users may want to analyze data by multivalued dimensions, and yet are not concerned with overall totals. In this case, they build the report using the regular measures, and ignore the distinct measures.
This allows them to slice measures by all dimensions, whether or not the dimensions are multivalued. However, if they place totals on the report, they must be aware that these totals could include duplicate values for the measures if the query includes multivalued dimensions.
Using Distinct Measures
Report users may not need to perform analysis of measures by multivalued dimensions, but do need all totals to be accurate, unduplicated totals. In this case, they can use the distinct measures. If any multivalued dimensions are used in the query, they will not affect the measures shown on the report. If these dimensions are included in the report, the measures will simply not show a breakdown by these dimensions, but will just show the total across all the dimensions’ category values.
Using Both Types of Measures
Report users may want to both perform analysis using multivalued dimensions and also show unduplicated totals. In this case, they use both the regular measure and the distinct measure.
When building the report, they use the regular measures in the report table. However, wherever they want to show report totals, they use the distinct measures. For example, in the Colleague Advancement scenario above, report developers could use this technique to break out contribution dollars by donation source, but also provide a sum of the overall contributions that reflects only unduplicated dollar amounts. This gives an accurate total that would match the amount of the contributions received, but is not the sum of the values shown in the columns. This is because the individual contributions have more than one donation source and are included on more than one report line.
210 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting Using the Universes in Web Intelligence
Figure 45 shows an example of how this report would look.
Figure 45: Report with Both Regular and Distinct Measures
Technical Tip: To build the report this way, report developers select both versions of the measure in the query. In the report, they would use the regular measure within the body of the report. However, in the summation line in the report footer, the report developer would drag the distinct measure into the summation cell.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 211© 2010 Datatel, Inc.
Using the Business Objects Connector: Using the Connector with the Data Models
Figure 46 shows an example of the query for this report.
Figure 46: Query for a Report Using Both Regular and Distinct Measures
212 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Reporting from the DataOrchestrator ODS Data Models
Appendices
Appendices
Checklist for Setting Up the Data Models
In This AppendixThis appendix provides a quick checklist of the activities that the system administrator and other users perform to set up and create the data models.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 215© 2010 Datatel, Inc.
Appendices: Checklist for Setting Up the Data Models
Checklist for Setting Up the Data Models
Table 37: Checklist for Setting Up the Data Models
Step User Description Comment
1. System administrator
Before you set up the data models, you must complete the following:
• Install the DataOrchestrator ODS.
• Create a SQL Server or Oracle target database for the operational data stores.
• Set up a target in the DataOrchestrator ODS and perform configuration to allow data extraction to the ODS target database.
See Using the DataOrchestrator ODS™.
2. System administrator
Check the prerequisites that your system must meet before you install the software updates for the data models.
See “Prerequisites for the DataOrchestrator ODS Data Models” on page 31.
3. System administrator
Use the Colleague release system to retrieve and load the appropriate software updates.
See “Retrieving the Datatel Software Updates” on page 32.
See Updating Colleague Software for information on retrieving and installing software updates.
216 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Checklist for Setting Up the Data Models
4. System administrator
Install the data models. Note the following:
• If you copy the CF transforms to your target, you must customize two of these transforms with your institution-specific information.
• For any transforms that are based on Colleague file suites, you must add a filter criteria for file suite instances.
• If you install the ST data model, but do not have the HR application in your Colleague environment, you must customize the ODS_FACULTY and ODS_COURSE_SEC_FACULTY transforms.
You must also activate and update the stored computed column used by the Colleague Student data model.
In the UT application, access the DataOrch Refresh (DORE) form. In the Transform Selection Option field, select All Target Transforms. Run the refresh to complete the initial refresh of the DataOrchestrator ODS data model.
See “Installing the DataOrchestrator ODS Data Models” beginning on page 33.
5. All users Understand how to view descriptions of the data model columns to better understand the data in the operational data stores.
Understand views, and the naming conventions for data models and views.
See “Accessing Data Model Help” on page 41.
See “Understanding Views” on page 42.
See “Naming Conventions for the Data Models and Views” on page 43
6. System administrator
If needed, check tips for improving the performance of the refresh for your institution.
See “Optimizing the Data Model Refresh” on page 45
7. System administrator
Check if you need to take customization steps for the ADDRESS_HIERARCHY field in the CORE data model, before exporting transforms in the demographics subject area.
See “Transform Customization Steps” on page 55.
8. Users with product knowledge
If needed, customize the data models. See “Customizing Data Models” beginning on page 135.
Table 37: Checklist for Setting Up the Data Models (cont’d)
Step User Description Comment
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 217© 2010 Datatel, Inc.
Appendices: Checklist for Setting Up the Data Models
9. System administrator
If your institution has licensed the optional module for the Business Objects Connector, install the Connector.
See “Installing the Business Objects Connector” on page 178.
10. Database report writer
Create reports using industry-standard reporting tools with the data models. Note: The Business Objects Connector includes sample Web Intelligence reports that you can use for reference when you start report development.
See “Using Reports from the Data Models” beginning on page 143.
See “Overview of the Business Objects Connector” beginning on page 154.
Table 37: Checklist for Setting Up the Data Models (cont’d)
Step User Description Comment
218 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Appendices
Troubleshooting the Data Models
In This AppendixThis appendix provides suggestions for items to check or steps to take if you encounter issues in using the DataOrchestrator ODS data models.
Note: For the most up-to-date information about the troubleshooting issues listed in this appendix, as well as any additional issues reported since the publication of this manual, see AnswerNet page 6064. If you do not have access to AnswerNet at your institution, consult with your system administrator.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 219© 2010 Datatel, Inc.
Ap
pen
dices: Tro
ub
lesho
otin
g th
e Data M
od
els
220R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
esting.
Resolution
taOrch Target (DOTA) form. From the rms list, detail on the specified transform. the Target Transform (DOTT) form. From , detail on the Filter Criteria field to
er Criteria (DOFC) form.
form, use the wildcard selection in the nces list to select the instances that you for this transform to the ODS target
DOFC, DOTT, and the DOTA forms. sh.
DataOrchestrator ODS Data Model IssuTable 38 lists the issues, possible causes, and resolutions for troubleshoo
Table 38: Troubleshooting Issues
Issue Possible Cause
1. The following error message is received when performing a refresh:
No file suite instances specified for 'XXXX'
Note: XXXX denotes the transform for which there are no file suite instances specified.
File suite instances were not selected for a transform that has a file suite as its primary source file.
Access the DaTarget TransfoThis accesses the DOTT formaccess the Filt On the DOFC File Suite Instawant to export database. Save from the Rerun the refre
DataO
rchestrato
r OD
S D
ata Mo
del Issu
es
Reporting from
the DataO
rchestrator OD
S D
ata Models, N
ovember 10, 2010
221©
2010 Datatel, Inc.
rm. From the Target Transforms list, GEN_LDGR transform. This t Transform (DOTT) form.
n Definitions field to access the
n field, in the first blank entry following:
ART_MONTH
C, DOTT, and DOTA forms. Rerun
Resolution
2. The following error message is received when performing a refresh:
• Error(s) when executing VIEW script(s) for <ODS_GL_TRANSACTION_DETAIL>
• Error(s) when executing VIEW script(s) for <ODS_GL_TRANSACTION_SUMM>
The FISCAL_YEAR_START_MONTH column has not been added to the ODS_GEN_LDGR transform.
Access the DOTA fodetail on the ODS_accesses the Targe Detail on the ColumDOTC form. In the Target Columavailable, enter the FISCAL_YEAR_ST Save from the DOTthe refresh.
Table 38: Troubleshooting Issues (cont’d)
Issue Possible Cause
Ap
pen
dices: Tro
ub
lesho
otin
g th
e Data M
od
els
222R
eporting from the D
ataOrchestrator O
DS
Data M
odels, Novem
ber 10, 2010©
2010 Datatel, Inc.
ed the views cited in the error ed on the information you expect to perational data store, ignore the errors.
need the information in the views, then sforms from the target templates using ransform Maint (DOMA) form. When A form, be sure that you select all the tained in each target template when r target.
ed the views cited in the error ed on the information you expect to perational data store, ignore the errors.
need the information in the views, be clude all the transforms needed by each d in the “Using the DataOrchestrator els” part of this manual) in your refresh,
the refresh.
ed the views cited in the error ed on the information you expect to perational data store, ignore the errors.
need the information in the views and e Export flags in the transforms you target templates, you need to reset
Y.” A simple way to do this is to recopy from the target templates for the data
Table 38: Troubleshooting Issues (cont’d)
Resolution
3. You receive view creation errors when running the refresh specifying views other than those listed in 1 or 2 above.
All the transforms from a target template were not copied when installing the DataOrchestrator ODS data models in your target. OR All the transforms defined in your target were not included in the refresh that encountered the view creation issues. OR The Export flag for a transform column required by the view was changed from “Y” to “N.”
If you do not nemessages, basquery in your o However, if yourecopy the tranthe DataOrch Tusing the DOMtransforms concopying to you OR If you do not nemessages, basquery in your o However, if yousure that you inview (as defineODS Data Modand then rerun OR If you do not nemessages, basquery in your o However, if youyou modified thcopied from thethese flags to “the transformsmodel.
Issue Possible Cause
Appendices
Understanding the GL Account Number Structure
In This AppendixThis appendix provides an overview of the structure of General Ledger (GL) account numbers.
Table 39 lists the topics covered in this chapter.
Table 39: Topics in this Appendix
Topic Page
About the Components of GL Account Numbers 224
Understanding Major Components of GL Account Numbers 226
Understanding Subcomponents of GL Account Numbers 227
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 223© 2010 Datatel, Inc.
Appendices: Understanding the GL Account Number Structure
About the Components of GL Account Numbers
The Colleague Finance (CF) application uses a chart of accounts General Ledger (GL) account number structure. This account number structure consists of major components and subcomponents. See Using General Ledger for a detailed description of GL account numbers.
Figure 47 illustrates an example GL account number that includes four major components. Some of the major components include subcomponents that allow you to roll up data for reporting purposes. Table 40 on page 225 defines the entities that these components and subcomponents represent. Refer to these examples as you read the following description of the GL account number structure.
Figure 47: Example GL Account Number Structure
10-1110-51010-01 Example GL account number
10-1110-51010-01
FUND major component
UNIT major component
OBJECT major component
LOCATION major component
224 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
About the Components of GL Account Numbers
For the purposes of this example, these major components and their subcomponents represent the entities described in Table 40.
Table 40: Meaning of Example GL Account Number
MajorComponent
Value Description Includes subcomponent?
FUND 10 Current, unrestricted funds Yes
• “1” – the FUND.GROUP “Current Funds”
• “10” – “Current Unrestricted” funds
UNIT 1110 Instruction, College of Arts and Sciences, Biology Department
Yes
• “1” – the FUNCTION “Instruction”
• “11” – the PROGRAM “College of Arts and Sciences”
• “1110” – The UNIT “Biology”
OBJECT 51010 Full-time faculty, Expenses, Payroll, Faculty
Yes
• “5” – the GL.CLASS “Expenses”
• “51” – the GL.SUBCLASS “Payroll”
• “510” – the CATEGORY “Faculty”
• “51010” – the OBJECT “Expenses, Payroll, Faculty, Full-time Faculty”
LOCATION 01 Main campus No
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 225© 2010 Datatel, Inc.
Appendices: Understanding the GL Account Number Structure
Understanding Major Components of GL Account Numbers
Colleague Finance permits you to define up to six major account components for the structure of GL account numbers. Colleague requires that you define at least three major account components. Within major components, you can also define subcomponents. Each institution creates its own unique account structure. When Colleague General Ledger is first set up, an institution defines its account structure in Colleague.
This gives each institution the ability to define the following:
1. The number of major components that the institution will use.
2. The number of subcomponents included in each major component.
3. The names of the components and subcomponents.
Required Major Components
Colleague Finance requires that you define at least these three major account components of the structure of the GL account numbers that your institution uses:
FUND (FD). A nonprofit accounting entity that identifies a self-balancing group of accounts based on the source and uses of that money. You cannot use the FUND component to identify the type or class of account number, such as revenue, expense, assets, or liabilities.
UNIT (UN). An entity that is often used to group accounts for business purposes for reporting. For example, an institution may want to group departments or cost centers. Some institutions prefer to use the term “DEPARTMENT” in place of “UNIT.”
OBJECT (OB). An entity that categorizes the account as an asset, liability, fund balance, revenue, or expense. Subcomponents of the object typically identify subsets within these categories.
The other available components are FUNCTION, SOURCE, and LOCATION.
Note: The component names above are the internal Colleague names for the major components. The external names seen by users and used for reports will be defined by your institution. You can see your institution’s GL account structure and components by accessing the Account Structure Maintenance (GLAS) form in Colleague Finance.
226 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
About the Components of GL Account Numbers
Understanding Subcomponents of GL Account Numbers
Colleague Finance permits you to define multiple subcomponents within each of the major account components. Using subcomponents enables you to group accounts with similar characteristics and then report on those groups of accounts.
Note: All subcomponent numbers begin with the left-most digit in the major component identifier. Subcomponents do not follow each other consecutively. The final subcomponent in a GL account number is always the major account component.
Reporting from the DataOrchestrator ODS Data Models, November 10, 2010 227© 2010 Datatel, Inc.
Appendices: Understanding the GL Account Number Structure
228 Reporting from the DataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Index
From this index you can click on any entry to access the information about the topic.
AAcademic credentials and graduation reporting
subject area 56, 162
Accessing data model help 41
Account Structure Maintenance (GLAS) form 37
Accounts payable and purchasing reporting subject area 111, 174
Activating stored computed columns 40
Addingfilter criteria for file suite instances 38GL subcomponents 37new tables and joins to universes 159
Advancement data modelreporting subject areas 126
data model diagrams 131demographics and contributions 127
AlertsBusiness Objects Connector does not provide
security controls 157using multiple threads can affect the system
resources 45
Applicants and applications reporting subject area 65, 164, 169
BBIAR files 179
Billing reporting subject area 79, 164, 169
BOBC optional module 154
Branding filesrunning InstallShield for 181
Business Objects Connectoradvantages 154customizing universes 199importing sample reports from BIAR file 191importing universes from BIAR file 183installing 178installing sample reports 178installing universes 178overview 154running InstallShield for 179
Reporting from the DataOrchestrator ODS Data Models, © 2010 Datatel, Inc.
BusinessObjects Business Intelligence Archive Resource (BIAR) files 179
BusinessObjects Web Intelligenceexample queries and reports 146
CCardinality checking 139, 154
CF transformscustomizing 37customizing ODS_GEN_LDGR 38
Colleague Advancement ODS universe 157, 176classes 176
Colleague Core ODS universe 156, 162classes 162
Colleague Courses and Faculty ODS universe 156, 167
classes 167
Colleague Finance ODS universe 157, 174classes 174customizing 203
Colleague Financial Aid ODS universe 156, 169classes 169
Colleague Human Resources ODS universe 156, 172
classes 172
Colleague reporting subject areas 29
Colleague Student ODS universe 156, 164classes 164
Computed columns, activating 40
Connectoradvantages 154customizing universes 199importing sample reports from BIAR file 191importing universes from BIAR file 183installing 178installing sample reports 178installing universes 178overview 154running InstallShield for 179
November 10, 2010 229
Index
CORE data modelreporting subject areas 52
academic credentials and graduations 56data model diagram 58demographics 53
Courses and faculty reporting subject area 68, 167
Crystal Reportsexample queries and reports 144
CustomizingCF transforms 37CF transforms, ODS_GEN_LDGR 38Colleague Finance ODS universe 203data models
cardinality checking 139determining supporting tables for a view 137determining what to customize 137export a null value to avoid customizing view
139forms to use 138, 142reasons to customize 136
demographics reporting subject area 55sample reports 155ST transforms 39universes for new tables and joins 159universes from Connector 199
DData model diagrams
Advancement data model 131CORE data model 58Finance data model 120HR data model 104Student data model 86
Data modelsaccessing help 41activating stored computed columns 40adding filter criteria for file suite instances 38adding GL subcomponents 37avoid customizing view by exporting a null value
139benefits 20cardinality checking 139customizing CF transforms 37customizing CF transforms, ODS_GEN_LDGR
38customizing ST transforms 39definition 12
230 Reporting from the D
Data models (cont’d)determining supporting tables for a view 137determining what to customize 137facts about 22forms to use for customizing 138, 142installation steps 33installing 29limitations 21naming conventions 43overview 14pre-installation tasks 30prerequisites 31reasons to customize 136removing references to HR subroutines 40reporting subject areas 29understanding 20workflow for initial population of data models 23,
24workflow for refreshing data models 23, 25
DataOrch Error Analysis (DOEA) form 23
DataOrch Target Transform (DOTT) form 23
DataOrch View Maintenance (DOVM) form 42
Datatel SAP Business Objects custom setuprunning InstallShield for 181
Datatel software updates 32
Define Stored Computed Column (DSCC) form 40
Definingsource account parameters 93, 109, 125variables 206
Demographics and contributions reporting subject area 127, 176
Demographics reporting subject area 53, 162transform customization steps 55
Distinct counts for measures 206
Distinct measures 210
Distinct totals and averagesmeasures 206
DO_ prefix 43
DOEA form 23
DOFC form 41
DOTT form 23
DOVM form 42
DSCC form 40
ataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Index
EEmployee information reporting subject area 95,
172
Enrollment, academic programs, test scores, and room assignments reporting subject area 72
Enrollment, academic programs, test scores, and rooms reporting subject area 164, 169
Example queries and reportsBusinessObjects Web Intelligence 146Crystal Reports 144Excel 149
FFilter Criteria (DOFC) form 41
Filter criteria for file suite instances, adding 38
Filtering queries 206
Finance data modelreporting subject areas 110
accounts payable and purchasing 111data model diagram 120GL transaction information 117
Financial aid reporting subject area 82, 164
Forms, Datatel softwareAccount Structure Maintenance (GLAS) 37DataOrch Error Analysis (DOEA) 23DataOrch Target Transform (DOTT) 23DataOrch View Maintenance (DOVM) 42Define Stored Computed Column (DSCC) 40Filter Criteria (DOFC) 41Update Stored Computed Column (USCC) 23, 40
GGL account number structure 223, 224
major components 226required major components 226subcomponents, understanding 227
GL subcomponents, adding 37
GL transaction information reporting subject area 117, 174
GLAS form 37
HHelp, accessing for data models 41
Reporting from the DataOrchestrator ODS Data Models, © 2010 Datatel, Inc.
HR data modelreporting subject areas 94
data model diagram 104employee information 95payroll information 102
HR subroutines, removing references 40
IImporting
sample reports from BIAR file 191universes from BIAR file 183
Improving performance 38, 45avoid updating previous years’ file suite instances
45change MAX_TRANS_FIELD environment
variable 33run incremental refreshes 46run multiple refreshes 48using more threads 45
InstallingBusiness Objects Connector 178data models 29Sample reports 178Universes 178
InstallShield for branding files 181
InstallShield for Connector 179
JJoins, adding to universes 159
MMeasures
defining variables 206distinct counts 206distinct totals and averages 206using distinct 210using without distinct totals and averages 210
NNaming conventions
data models 43SQL views 43
OODS_ prefix 43
Optional module BOBC 154
November 10, 2010 231
Index
PPayroll information reporting subject area 102, 172
Performance, improving 38, 45avoid updating previous years’ file suite instances
45change MAX_TRANS_FIELD environment
variable 33run incremental refreshes 46run multiple refreshes 48using more threads 45
Prerequisites for data models 31
Procedurescustomizing the universes for your ODS target
database 199importing sample reports from the BIAR File 192importing universes from the BIAR File 184retrieving software updates 32running the InstallShield 179, 181
QQueries
filtering 206
RRecommendations
change default setting for MAX_TRANS_FIELD environment variable 33
do not report against a supporting table of a view 43
monitor system resource usage when running a ODS refresh 45
not recommended to add a multivalued column to a transform with only single-valued column 139
not recommended to delete columns from a transform 139
only tables or views with the prefix of ODS_ be used for reporting 52
perform a refresh of the CORE data model transforms to test performance 36
rename the view before customizing 138, 142set the current year file suite to "Yes" and set
previous years to "No" 38to improve performance 45
232 Reporting from the D
Reporting subject areas 29Advancement data model 126
data model diagrams 131demographics and contributions 127, 176
CORE data model 52academic credentials and graduation 56, 162data model diagram 58demographics 53, 162
Finance data model 110accounts payable and purchasing 111, 174data model diagram 120GL transaction information 117, 174
HR data model 94data model diagram 104employee information 95, 172payroll information 102, 172
Student data model 64applicants and applications 65, 164, 169billing 79, 164, 169courses and faculty 68, 167data model diagrams 86enrollment, academic programs, test scores, and
room assignments 72enrollment, academic programs, test scores, and
rooms 164, 169financial aid 82, 164
SSample reports 155
importing from the BIAR file 191installing 178
Software updates 32
Source accountdefining parameters 93, 109, 125
Source transform, definition 12
SPT_ prefix 43
SPT_ tables not referenced by views 44
SQL view, definition 13
SQL viewsexport a null value to avoid customizing 139naming conventions 43understanding 42view definition 137
ST transforms, customizing 39
ataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.
Index
Student data modelreporting subject areas 64
applicants and applications 65billing 79courses and faculty 68data model diagrams 86enrollment, academic programs, test scores, and
room assignments 72financial aid 82
System prerequisites for data models 31
TTables, adding to universes 159
Target templates 34
Target transform, definition 12
UUnderstanding, GL account number structure 223
UniversesColleague Advancement ODS 157, 176Colleague Core ODS 156, 162Colleague Courses and Faculty ODS 156, 167Colleague Finance ODS 157, 174Colleague Financial Aid ODS 156, 169Colleague Human Resources ODS 156, 172Colleague Student ODS 156, 164customizing from Connector 199importing from BIAR file 183installing 178
Update Stored Computed Column (USCC) form 23, 40
USCC form 23, 40
VVariables
defining 206
View definition 137
Viewsnaming conventions 43understanding 42
WWeb Intelligence
example queries and reports 146
Workflowfor initial population of data models 23, 24for refreshing data models 23, 25
Reporting from the DataOrchestrator ODS Data Models, © 2010 Datatel, Inc.
November 10, 2010 233
Index
234 Reporting from the D
ataOrchestrator ODS Data Models, November 10, 2010© 2010 Datatel, Inc.