Body
Introduction:
This article is designed for the UMSN Undergraduate Advising team to learn the basics of accessing and navigating Business Objects to view and edit queries in the UMSN Public Query Repository. After reading this article, you will be able to access, update, share, and export reports from the UMSN Business Objects Public Query Repository. Resources for further learning are included at the bottom.
Terminology:
SAP Business Objects: query and reporting software used to access the U-M Data Warehouse.
Universe: a dataset in the Data Warehouse. Ex: SA02: Recruiting and Admissions, SA03: Student Records
Data Dictionary: A document describing the structure, contents, and relationships between data in a database (also referred to as a “Universe” in context of the U-M Data Warehouse). The data dictionaries include diagrams of how tables within a Universe relate to each other, lists of elements within each table, and descriptions of each available element. The descriptions show the element’s data type, table location, description, and examples of valid values.
Dimension: A data field containing descriptive attributes. Most data fields are classified as dimensions. In the Business Objects query builder interface, dimensions are colored blue. Examples include name, email address, Emplid, academic program, etc.
Access:
Log in to Business Objects here: https://businessintelligence.dsc.umich.edu/BOE/BI
You can also find Business Objects by logging into Wolverine Access and choosing the “Data Warehouse” link.
Obtaining Access to the Data Warehouse
Most staff in the UMSN Undergraduate department have access to the Data Warehouse. However, if you’re unable to login, you may need to request access. Access is requested through the Online Access Request System (OARS).
Steps to Request Access
- Access requests are approved by unit liaisons. UMSN’s liaison for Student Administration lists is Bernadette Lis (blis@umich.edu). Notify Bernadette that you need access to the Data Warehouse before submitting an OARS request.
-
Review the OARS information here: https://its.umich.edu/accounts-access/administrative-access/oars
-
Complete the required MyLinc Training (DCE101 Cybersecurity and Data Protection at U-M): https://maislinc.umich.edu/core/pillarRedirect?relyingParty=LM&url=app%2fmanagement%2fLMS_ActDetails.aspx%3fActivityId%3d47169%26UserMode%3d0
-
Consider taking the recommended MyLinc training modules listed in the “Further Resources” section of this document. These offer further training on using Business Objects.
- Submit your OARS request. We recommend copying the roles others in your department have when making your access request.
Access to UMSN Public Query Repository
The UMSN Query Repository is a shared network folder accessible through SAP Business Objects. This folder holds several pre-constructed query files designed for UMSN staff. These files are customizable and can thus serve as a base for many of the more specific data reports needed across the various UMSN offices and departments.
By default, not all UMSN employees have access to the Public Query Repository. Access requires both standard OARS access to Business Objects (and the underlying data universes) as well as further permissioned access to the UMSN Unit-Maintained public folder. To obtain access to the Query Repository folder, send an email to the Records Team. Bernadette Lis and Carrie Bachler are current owners of the Business Objects School of Nursing folder. For questions regarding any of the queries or how to use them, please contact UMSN-RecordsTeam@med.umich.edu.
Data Warehouse - Student Administration
The Data Warehouse includes many data areas, but we’re most concerned with the Student data. This data area is broken down into several “Universes,” or data sets. The three most relevant Universes are listed below:
SA02 - Recruiting and Admissions
The student Recruiting and Admissions data set is available through the SA02 Recruiting and Admissions BusinessObjects Universe. The SA02 data set includes tables that contain all current and future prospects and applicants, as well as historical records for the past three years.
SA02 Data Dictionary: https://drive.google.com/file/d/0B6_bCRP78EjWWC1ranJuU2hEbmc/view?resourcekey=0-61hrIlgc7K2j_V0zDGkPwA
SA03 - Student Records
The Student Records data set is available through the SA03 Student Records BusinessObjects Universe. The SA03 data set stores academic data for students who are matriculated at the University of Michigan, Ann Arbor. Specifically, it includes students’ personal information, enrollment, courses, grades, degrees, transfer credit, degree audit, and more.
SA03 Data Dictionary: https://drive.google.com/file/d/0B6_bCRP78EjWZmVleXJSSkFxQXM/view?resourcekey=0-fEMCPBWtjT82Ja8MSvrirA
SA05 - Third Week Count
The third week count is an official “snapshot” of student enrollments for each school year. The Third Week Count data set is available through the SA05 Third Week Count BusinessObjects Universe, managed by the University IT Services. SA05 captures key data including student personal and demographic information, academic record, class schedule, and course enrollment. The Third Week Count snapshot is taken the night after the end of the third week of classes, and is generally available a few days later. SA05 data is usually reliable and accurate; thus it is used by the Records Team for official reporting purposes, such as the AACN national survey.
SA05 Data Dictionary: https://drive.google.com/file/d/0B6_bCRP78EjWYk4tT0I2UHlfVGs/view?resourcekey=0-46jtLhNGndx5If8Y733Wkw
Navigating Business Objects
Finding the School of Nursing Public Folder
On the Business Objects Home screen, choose “Folders.”
The UMSN Public Query folder is located at this path: Public Folders\Unit-Maintained\School of Nursing\Public Query Repository
Two queries created for the undergraduate team are already in the folder.
- UMSN Undergrad Dean’s List Report (Current Term)
- This query returns all undergraduate nursing students with a current term GPA greater than 3.5.
- UMSN Undergrad Senior Honors Report
- This query returns undergraduates with a current term expected graduation and a cumulative GPA greater than 3.3.
Running a Pre-made Query
Select the query you’d like to run from the list. Business Objects will load a report of the query results.
It’s important to refresh the query to load the most recent results. It’s recommended to Refresh All, as some reports use more than one query.
To export the report, click the download icon.
Business Objects allows you to download the report in several file types. The most useful type is typically Excel.
Ensure your report is selected, then click Export.
The report will be downloaded to your computer in the folder you specify.
Making Changes to a Query
If you want to tweak your report, you can edit a query. If you’re making changes to a query in the Public Query Repository, you must first save it as a new query, then edit the new one. Queries saved in the Public Query Repository cannot be edited.
Use the "Save As" button to save a new copy of the query.
Choose a save location in one of your Personal Folders. Don't save your working copy in the Public Query Repository.
After saving your copy, Business Objects will load the report. To begin editing, click the “Reading” button to switch to “Design” mode.
Use the “Edit” button to make changes to the query.
This brings up the Query Panel. In the Query Panel, you can add result objects (i.e., fields to return) and filters to the query.
- Result Objects become the columns in the report.
- Query Filters filter the report. In this example, we’re returning students with an undergraduate nursing academic career and a current term GPA greater than 3.5.
- Filters use AND/OR logic.
Drag dimensions to either the “Result Objects” area or the “Query Filters” area from the tables (represented by folder icons) on the left.
- It’s important to consider the table you pull your dimensions from.
- Many dimensions are present in multiple tables. However, not all tables contain the same data. The same data points may be in multiple folders, but they can be different in the context of the table. If your query isn’t returning what you expect, check this first! Reference the data dictionaries to understand what information each table contains. It’s recommended to pull as many dimensions from the same table as possible.
After making your changes, click “Run.”
If you added new dimensions to the “Result Objects,” you will also have to drag them into the report view. Your new dimension will not appear as a column until you drag it into the report view.
Click the new dimension in the Dimensions panel and drag it to the desired location in the report. It will become a new column.
Send to BI Inbox
Easily share reports in Business Objects by sending them to others’ “BI Inbox.”
Click the three dots in the “File” section, then choose “Send to.”
Search for the uniqnames of those you’d like to send the query to.
Check the box next to their uniqname and click “Send.”
Checking Your BI Inbox
Check your BI Inbox by choosing “BI Inbox” from the home screen. Documents sent to you will be in the list.
Further Resources:
MyLinc Trainings
ITS Documentation