!

Enterprise Business Intelligence

Overview

Computing Services and Systems Development is responsible for Enterprise Business Intelligence and the University Data Warehouse at the University of Pittsburgh.

What is Business Intelligence?

Business Intelligence describes the technologies and processes that transform raw data into meaningful information that can support data-driven decision-making. The University of Pittsburgh supports Tableau for dashboards, data discovery, and analytics; and IBM Cognos for reporting.   

CSSD’s Business Intelligence team is comprised of database specialists, report developers and designers, analysts, and data scientists. The team supports Tableau, Cognos, and the University Data Warehouse. Reporting, analytics and data science consulting is available for the campus community. Further consulting and support options available are as follows:

  • Business Intelligence Practice Group
    Includes hosted quarterly meetings of the Business Intelligence Practice Group. Meetings feature updates on the University’s data and analytics initiatives, guest speakers, tips, and training. 
    The BI Practice Group Web site provides documentation, resources, and discussion for the group. You can join the group by completing a short online form.
    BI Practice Group SharePoint site can be accessed once you join that includes up to date group information, resources,  other training and support information.
  • Consulting in Reporting, Analytics, or Data Science
    Consulting to administrators at the University of Pittsburgh in the areas or reporting, analytics, and data science. If you are interested in dashboards, data analysis, predictive analytics, or have other data-related needs, contact the Technology Help Desk to arrange for a consultation.

Request Access to Supported Tools and Data Services

Supported Tools

 Requesting Access

Cognos Reports and Cognos Query Studio

Cognos is a tool for enterprise reporting and ad hoc data querying. Using Cognos, authorized staff can view standard financial and student reports. Data and operational professionals can use Cognos Query Studio to create ad hoc queries or run existing queries. More about Cognos and Cognos Query Studio>>

**Data from the University data marts are organized into packages within Cognos. These packages provide data for pre-built reports, as well as providing data sources for ad hoc queries via the Cognos Query Studio tool.

By requesting access to either the Student Mart or the General Ledger Mart above, you will be automatically granted access to relevant standard University reports available in Cognos. To request Cognos Query Studio access, make sure to check the “Include Query Studio access box” when submitting the Federated Authorization Request.


Tableau Desktop and Server

Tableau is a tool for visualizing and exploring data, uncovering insights, and sharing those insights via dashboards. CSSD provides Tableau Desktop licenses to data analysts who use the University Data Warehouse at the University of Pittsburgh (see Requesting Access below for details). Tableau Server is provided as a repository for those analysts to publish and share their work within the University, and in some cases, outside of the University. More about Tableau >>

All faculty and staff at Pitt have access to the Tableau Server at http://analytics.pitt.edu . Access to content (dashboards or worksheets) should be directed to the Tableau Desktop author who published the workbook.

Data analysts who use the University Data Warehouse can request Tableau Desktop licenses from CSSD. Visit the Tableau page for details.

The University Data Warehouse

The University Data Warehouse is the central authoritative data source for the University, providing support for reporting, ad hoc data queries, and analytics. The University Data Warehouse is an Oracle-based database that is comprised of data originating in multiple University systems, including PeopleSoft, PRISM, and CourseWeb. The data is extracted from those systems, then optimized for speed and ease of use, and placed into data marts. A data mart is a subset of the University Data Warehouse and is oriented toward a particular type of data, or a particular function. More about the University Data Warehouse >>

Access to the University Data Warehouse and its data marts requires the submission of a Federated Authorization Request. These requests ensure that users are granted access to the data required to successfully complete their work.

  • Student Mart Access
    ​Your access to student data will be governed by your academic center or department. In addition, access to sensitive data (e.g., GPA, ethnicity, birthdate, etc.) requires additional approval. All new users must first request Student Mart access. If restricted access is required, those forms should be submitted as well.

  • General Ledger (GL) Mart Access
    Your access to general ledger data will be governed by your job responsibilities, your responsibility center and department/group, as well as the role you request on your form (e.g., Business Administration, Operations, or Grants).

  • SQL Query Access
    If you have access to the University Data Warehouse via the Student or GL Mart, know how to write SQL queries, and would like to have direct SQL Query Access, then please contact the Technology Help Desk.

Data Collections

Data in the University Data Warehouse are stored in two marts: financial and student. Within a mart, subject area data are grouped in different ways (packages) to allow users to perform analyses.

 Data Dictionary can be accessed for use of packages available in IBM Cognos.

A number of reports are available to users based on the individual user's level of access and role at the University.

Financial Mart

Data Source: PRISM, the University's financial information system
Data Range: 1995 (fiscal year) through today
Refresh Schedule: Refreshed each month following month-end closing

Subject Area

Examples of Available Reports

The General Ledger (GL) Aggregate query package contains general ledger information aggregated by fiscal month and account string. It is used by people who manage responsibility center, departmental, or project income, expenses, and balances. Both actual and budget amounts are included for the month, fiscal year to date, project to date, and prior fiscal year.

The GL Transaction query package contains detailed general ledger information for each transaction. It is used by people who manage responsibility center, departmental, or project income, expenses, and balances. Actual and budget debit, credit, and net amounts are included for the transactions. Various revenue and expense calculations are included in the package. Various organizational hierarchies can be used to group the transactions.

Level II Balance Sheet
Level II Balance Sheet Department Summary
Level II Financial Report Area Summary
Level I Balance Sheet
Level I Financial Report
Level I Financial Report - Masters/Subs

The Payroll Transaction query package contains detailed labor distribution transaction information by fiscal month. It could be used by people who manage responsibility center, departmental, or payroll expenses. Adjustment and regular pay amounts are included for each month, along with a fiscal year to date and project to date amount.

Labor Distribution by Account
Labor Distribution by Account (unformatted)
Labor Distribution by Account - Masters/Subs

Student Mart

Data Source: PeopleSoft, the University's student information system
Data Range: 1990 (fall term) through today
Refresh Schedule: Refreshed daily, Monday through Saturday

Subject Area

Examples of Available Reports

The Active Student package contains personal and academic data for any student who is enrolled or eligible to enroll as of today's date.

The Active Student package is a query-only area.

The Admissions package contains personal, academic, and application information for each student application to the University. It also contains high school and test score (e.g. SAT and ACT) information. The package could be used to identify all students that have applied to a particular school with a math SAT test score of > 500, for instance.

 

The Class Enrollment package contains students' personal and academic enrollment data as well as detailed class information.

Veteran Services Reports

Consumers Report by Fiscal Year

Producers Report by Fiscal Year

The Course Catalog package contains information about courses offered at the University of Pittsburgh. It is used to generate a list of active courses by campus or by school for an academic term; course enrollment requirements summary, etc.

The Class Schedule package contains data pertinent to the class, such as schedule, location, instructor, prerequisites, class credit, textbook, and fee information. In addition to generating a class schedule report, it can be used to produce online course listings, room usage reports, lists of classes for the school by instructor, etc.

Schools Offering Classes – Attribute/Attribute Value

Class Schedule All Columns Report

Class Section Update Report

GSPIA Reports > GSPIA Course Schedule by Instructor

The Enrollment & Tuition package contains personal, academic, and tuition data for any student enrolled for the specific term within a snapshot. It could be used to trend the in-state and out of state student enrollment and tuition dollars over the years, broken down by demographics and academic information.

Enrollment, Tuition, and Credits by Academic Plan Code

FTE Headcounts and Tuition

Grades for Repeated Classes

IMPACT Reports > PA degrees awarded by county

Pitt-Greensburg > GBG Education Minors

There are two Financial Aid packages: Financial Aid Award and Financial Aid Need. The award and need packages are separate since need amounts are by aid year and award amounts are by term. These packages contain personal, academic, and financial aid need and award data for enrolled students. The packages could be used to analyze scholarship retention statistics, for example.

Net Tuition Division Reports for Selected Campus and Academic Center

The Freshman Retention package contains personal, academic, and class enrollment data for each first-time student for each term of the student’s first year plus fall term of their second year in college. Financial aid need information is also available.

Fall Freshman Retention Action Report

Fall Freshman Retention Action Report by Campus

The Graduation package contains students' first graduation and degree information, as well as demographic and graduation GPA data. The most recent data for degrees is in this package. It can be used to calculate graduation rates, average time to graduate, etc.

The Graduation Census Query contains a snapshot of the same data as of the degree census date for each year.

Graduation and Retention Reports Containing Counts and Rates > Graduation Counts and Rates Report by Term and Grouping – Regional Campuses Graduation and Retention Reports > Number of Freshmen in Cohort by Demographic Grouping – Pittsburgh Campus

First Enrollment and Graduation of Online Students

Degree Reports > Certification and Degree Awarded by Academic Year and CIP

There are six packages of Student Financial data divided into two separate sets.

One set is for student billing information; the second set pertains to third party billing information. The three packages in each set are Accounting Line, Item Line, and Item.

The Accounting Line package can be used to query debits and credits posted to PRISM. The Item Line package can be used to query the details of each transaction, such as the date it occurred and the item type of the transaction. The Item package can be used to query the account information at a higher (less detailed) level of the account, such as the account term or the item term amounts.

Aging report – By Student

Aging report – By third Party