PS Query Best Practices

These best practices primarily refer to end users who are running and making slight modifications to queries created by other users. We understand that those who use PS Query extensively may use an alternative set of best practices, namely, they may utilize private queries more heavily to ensure that their queries cannot be erroneously changed by other query editors.

Detailed Information  

There are many details regarding reporting on our data that you might not be aware of which can alter your reporting returns. For the best possible results, consider the following suggestions.

Public or Private?

Queries can either be publicly viewed (public queries) or only viewed by the user (private queries)

Your department will likely have a standard way of naming queries so that you can easily search for them. Queries created by the DARTS team are always made public and begin with UMS_DTS_.

Public Queries

  • Never edit an existing public query. Ever. 
  • Public queries in the Financials database cannot be saved over. (Save as private query with a different name and DARTS team can convert it to a public query.)
  • You can save a public query as private for your use, but keep in mind that if that original public query is adjusted due to changes in tables or fields, your private query will not have those updates and may no longer be valid, depending on what was changed and what your private version looks at.

Private Queries

  • We strongly recommend that you not save private queries for regular use, unless absolutely necessary. Private queries have some utility for testing out data extraction, but routinely used queries made public are generally a better way to go. As noted above, if the query you save is adjusted to ensure data quality, your private copy will not receive these updates and will no longer be valid.
  • If you save a query as private before running it in order to make changes, it is recommended that you return to the public version of the query each time you make changes and SAVE OVER the private copy from when you last ran it. This ensures you are using the most up-to-date version of the query.
  • If you need to save multiple versions of the same query, never have a query that is more than one version away from the public query.
  • If you find a public query that you could use without saving a private copy if some additional prompts were added (e.g., account, department, etc.), please let DARTS know so that we can create a public version for you that is most useful to you or your team.

Before saving a copy of a query and making changes to it, ask yourself these questions:

  1. Did someone create this query for me to do a specific job? 

    1. If the answer is yes, it would be good to refer back to that department first to understand the intricacies of the query. That initial report was validated for a specific use and your private changes may cause the report to lose its validity.

  2. Am I searching for an existing public query that might return results similar to what I need to see? 

    1. Contact the person who created the report to help you find one that is similar, or adjust one to your needs. If the contact information is not in the comments (under Save As), or if you cannot find a report near to what you require, you can contact DARTS@maine.edu and we can help.

Signing Queries

All queries that are created or changed should be signed in the Query Definition section when saving. Name, email address, and date created or changed are always a good idea to include. You can also add notes about the query, what it was created for, or anything else the user should know. This information is extremely helpful to other users who may be shopping for a query to suit their reporting needs.

Sharing Queries

  1. Consider only sharing private queries in very targeted situations; consider making it public instead, and only share it privately if there are any compelling reasons for not having the query be public. The DARTS team is happy to help you sort through the options any time (DARTS@maine.edu).

  2. If you need to modify a public query; save a private copy and make your changes. We recommend never moving more than one version away from the original query. Always go back to the original to make needed changes. If you anticipate repeatedly needing an alternative version, consider creating a modified version and making it, too, public.

  3. Question the validity of private queries that are shared with you. They may have been shared multiple times, and lost some of their validity by changes made along the way.

  4. Consider allowing someone like DARTS to review your query and make it public; during this process, we will work to ensure the validity and reliability of the query. This approach also increases ease of access by others who might be doing a similar job as you, and easy updates and review when necessary.

Keep It Clean

Your private query library gets large easily and it is good to review your queries periodically. Please go to Maintaining a Private Query Library page for the instructions for reviewing and cleaning your private queries.

Where does my data live?

There are four main databases that are used by different functional areas:

  1. CSPRD Campus Solutions Production DB - used by Registrar, Financial Aid, Enrollment, Admissions and Academic Affairs, and known as CS or Student Data
  2. CSRPT Campus Solutions Reporting DB - Reporting database for CS. The data is one day old.
  3. HRPRD Human Resources Employees Production DB - Known as HR
  4. FNPRD Financials Production DB - Known as Finance

Navigate to your database from your myCampus Portal and select either:

  • CSPRD - Campus Solutions Production DB
  • CSRPT - Campus Solutions-Reporting DB
  • HRPRD - Human Resources Employees
  • FNPRD - Financials

You will usually only have access to the database you will be using in your day-to-day work.

Environment

  • PeopleSoft Query

 

 

Submit an Inquiry or Request to DARTS Print Article

Related Services / Offerings (1)

Submit an inquiry about UMS Data (except Data Governance)