UB InfoSource

Operating System: Windows

Applies To: UB faculty and staff

Last Updated: November 6, 2024

UB InfoSource is physically separated from application data and doesn't impact online operations. The information is read-only and cannot be changed. UB InfoSource is not intended as a replacement for any other systems.  

As a security precaution, UB InfoSource accounts that are inactive for 12 months will be deactivated.

Complete this request form and attach it in an email to ubinfosource@buffalo.edu. All accounts include a subscription to a project Listserv, which is used for communicating InfoSource issues and sending notifications. 

Oracle Client and ODBC (Open DataBase Connectivity) software is a standard that allows each customer to customize a front end client, like Microsoft Access or Excel so that customers can easily retrieve information which can be found in Relational Database Systems, like Oracle.

If you plan to use Siri reports exclusively, you do not require this software. To directly access UB InfoSource data (using MS Access, etc.), it is necessary to install communication software drivers (ODBC & Oracle Client). 

These components are complex by nature. Depending on your individual departmental configuration, the setup options for installation may vary. If you are not familiar with the different aspects of this software, we encourage you to utilize the services available through your departmental node or technical support person to perform your installation.

Close all open programs on your PC. If a previous version of Oracle Client software is on your local machine, please remove it.

After installing Oracle client, append the following connection entry to the appropriate tnsnames.ora located on your hard drive.

After appending this text, make sure the entry is formatted properly and remove any extraneous end line characters. If end line characters are not positioned properly you will receive tns-related connection errors.

# only one of the entries below is needed.
# However, the connect string (adminp0.buffalo.edu or InfoSource) must match the ODBC Administrator Data Source name
adminp0.buffalo.edu =
        (description =
            (address = (protocol = tcp)(host = dw.buffalo.edu)(port = 1521))
            (connect_data =
                (service_name = adminp0.buffalo.edu)))
InfoSource =
        (description =
            (address = (protocol = tcp)(host = dw.buffalo.edu)(port = 1521))
            (connect_data =
                (service_name = adminp0.buffalo.edu)))

Choose Control Panel --> Administrative Tools --> Data Sources (ODBC)
Creating a Data Source (Note: type exactly as listed, this is case sensitive):
Choose Add.
Highlight Oracle Driver
Click Finish

Data Source Name: adminp0.buffalo.edu
Description: adminp0.buffalo.edu

Click OK
Click OK

Choose Control Panel --> Administrative Tools --> Data Sources (ODBC)
Creating a Data Source (Note: type exactly as listed, this is case sensitive):
Choose Add.
Highlight Oracle Driver
Click Finish

Data Source Name: InfoSource
Description: InfoSource

Click OK
Click OK

If you are still unable to connect after the install you may need to edit the sqlnet.orafile.

The Oracle configuration tool may add extraneous code to the sqlnet.ora file (same directory as tnsnames.ora) and this code can cause connection problems.

A code line such as the following should be commented out or deleted:

NAMES.DEFAULT_DOMAIN = itorg.ad.buffalo.edu

The sqlnet.ora file can contain file the following line of code:

NAMES.DIRECTORY_PATH=(tnsnames)

Additional code should not be necessary.

In addition, if you have requirements that your net service name be 'InfoSource', then you can use the connect descriptor below in your tnsnames.ora file. However, 'InfoSource' is an unqualified net service name because it does not contain a domain name, e.g. '.buffalo.edu'. Therefore, you will need to confirm that you do not have the NAMES.DEFAULT_DOMAIN parameter set in your sqlnet.ora file. In most cases your sqlnet.ora file could only contain the "NAMES.DIRECTORY_PATH=(tnsnames)" line. Your tnsnames.ora and sqlnet.ora file contents would be as follows:

tnsnames.ora:
InfoSource =
        (description =
            (address = (protocol = tcp)(host = dw.buffalo.edu)(port = 1521))
            (connect_data =
                (service_name = adminp0.buffalo.edu)))

sqlnet.ora:
NAMES.DIRECTORY_PATH=(tnsnames)

Each individual may request access to any or all of the various subject areas depending on needs. Please review University Access Policy for details on access and acceptable use.

Listed below are the data trustees responsible for granting your access to various subject areas in UB InfoSource. For each subject area for which you are requesting access, an email message must be sent to the responsible data trustee or the linked form completed and submitted. To contact the trustee via email, click on their name and compose a message containing the following:

1. Your UB InfoSource userid
2. Your name, title, department and phone number
3. The reason for requesting access to the data

Any requests received by the trustees which do not contain all the information listed above will not be processed.

The data trustees will respond to your request via email. Enterprise Application Services will establish your access as approvals are received from each trustee.

Any client with a valid InfoSource userid that can demonstrate a legitimate legal or business justification for access to SSN data in InfoSource can request approval. This is limited to data which is required by law or necessary for business transactions, e.g., cases where a person number identifier cannot be used.

1. Print the data access request form
2. Complete a hard copy of this form:
   • The department head of your area (Vice President, Dean, Vice Provost) and User signatures are required for processing
   • All requests must include the legal or business purpose for the request as well as how the SSN will be used
     
   • You must indicate specific subject area and name of the view you are requesting (e.g.,. area = Admissions, view = APPLICATION_V)
     
3. Mail your signed form to a specific Data Trustee based on subject area
4. Data Trustees and the campus Information Security Officer will review your request
5. A decision will be made with respect to whether access is granted
6. You will receive email confirmation as to access decision

1. Select Change Password
2. Authenticate with your UBITName and password
3. Enter your new UB InfoSource password
4. Re-enter your new password to confirm
5. Click Request Reset

UB InfoSource is available for use from 7:00 a.m. until 11:00 p.m., 7 days per week.

Backups, maintenance, and data loads occur from 11:00 p.m. until 7:00 a.m., so usage during this time period is not advised.

The majority of the data in UB InfoSource is extracted (refreshed) on a nightly basis, which means the data is one day old. It is a snapshot of the previous day's activities. This is appropriate for reporting, but should not be used for operational type decision making.

The default value for Social Security Numbers is blank. Access to this information is based on legal necessity or business need.

Inner Join

A join in which related records from two tables are combined and added to a query's result set only if the values of the joined fields meet a specified condition. For example, the default join between tables in Microsoft Access query design is an inner join that selects records from both tables only when the values of the joined fields are equal.

Left Outer Join

An outer join in which all of the records in the left-hand side of the LEFT JOIN operation in the query's SQL statement are added to the result set, even if there are no matching values in the joined field from the right-hand table. Records from the right-hand table are combined with those from the left-hand table only when there are matching values in the joined fields. When a left-side record has no match, a row of NULL values is joined on the right side.

Right Outer Join

An outer join in which all of the records in the right-hand side of the RIGHT JOIN operation in the query's SQL statement are added to the result set, even if there are no matching values in the joined field from the left-hand table. Records from the left-hand table are combined with those from the right-hand table only when there are matching values in the joined fields. When a right-side record has no match, a row of NULL values is joined on the left side.

A pass through query is a special type of query available in Access. As its name implies, it is passed through Access directly into Oracle, without Access processing it. Unlike a typical Access query, a pass through is not created with point-and-click from menus. It involves building a query by writing Oracle SQL (Structured Query Language) into an empty editing window.

• The three main advantages to using a pass through query are speed, flexibility and power. Pass through queries are generally faster since Access doesn't have to interpret or process the query when it is being sent to Oracle. This is possible because a pass through query is already written in a manner that Oracle can understand.
• Pass through queries are also more flexible than regular Access queries. For example, with a typical point-and-click Access query, you have no control over the ordering of the clauses and criteria. This ordering may have a large impact on the performance of a query. A pass through query gives you more flexibility by allowing you to optimize your query by specifying exactly what happens in what order.
• There are some things that just can't be done with a regular Access query, that you need the functionality of a pass through query to accomplish. This makes pass through queries potentially more powerful than typical Access queries.
• The main disadvantage to using a pass through query is that they are more difficult to learn and write than typical queries built in Microsoft Access. Writing pass through queries involves mastering SQL and its syntax. This is essentially programming and may be difficult for someone to do who has no computer programming background. In addition, since it is not a point-and-click interface, you need to know the exact field names and table names you want to include in your query.

For questions of a general nature related to UB InfoSource, send mail to ubinfosource@acsu.buffalo.edu.