FIS Decision Support

Direct Access

You can request a direct access account on the Decision Support database, for the purpose of designing custom queries or extracting data for use in your department's datamart.

Requesting Direct Access

  1. The user or service custodian must be an authorized KFS and DS user. Please see "KFS Access Information".
  2. The user or service custodian must fill out the "Decision Support Direct Access Request Form" form. The form requires a business reason and experience with SQL database querying. Please note that access will only be granted to those with good knowledge of SQL. FIS Decision Support is a large database, and improperly run queries can affect the performance for all users (see Know Your Joins below).
  3. The request will be evaluated by Admin IT and General Accounting to determine appropriateness and required access needed within 5 business days.

Direct Access Termination and Revocation

FIS DS Direct Access will automatically be terminated under any of the following conditions:

  1. When a user has separated from the University
  2. A user changes job functions and/or switches organizations
  3. The account has remained inactive for over 400 days. FIS DS Direct Access may also be revoked if it is determined the account is used outside the scope of their job duties or the use impacts the FIS DS system performance and availability.

Using Your Direct Access

Once you have your direct access account, you need to use a database tool to access the system. Examples of these are (in increasing order of complexity and capabilities): Oracle SQL*Plus, Microsoft Excel, Microsoft Access, Oracle Discoverer and BrioQuery. You should contact your local Technical Support Provider (TSP) about configuring the tool you want to use to connect to the database. Whatever tool you choose to use, you need to have Oracle SQL*Net installed on your system. Most tools also require an Oracle ODBC driver (available with Microsoft Access and/or by download from Oracle). When setting up the connection, the database name/connect string = fis_ds_prod. Once you have logged in, the schema = finance. To ensure that your queries are well written, and do not degrade system performance, please review the FIS Decision Support Table Documentation and the additional two sections below.

Note:For large data extracts, we ask that you restrict your loads to lower usage periods: 6am-9am, 12pm-1pm, 4pm-7pm.

Know Your Joins

You should have a good knowledge of SQL or attend a class on database querying BEFORE attempting to run queries against the FIS Decision Support database. Understanding the nature of joins is especially important. FIS Decision Support is a very large database, with over 100 tables, several of them having millions of rows. Improperly written joins can severely degrade the performance of the entire system, resulting in long response times for every user. Improperly formed queries are subject to termination without warning. Note: Repeated executions of badly designed queries, after a warning by A&FS Staff, could result in the suspension of your account. Reinstatement of your account will occur only after you provide evidence of completion of a course on database query writing.

Fiscal Year and Fiscal Period Versions of Data

The fiscal_year (FY) and fiscal_period (FP) are used in the reference tables to differentiate between versions of particular reference objects (e.g. a particular organization_account) that were current at the end of a given FY/FP. Most reference data on organizations, accounts and their attributes are kept as 'snapshots' for each fiscal period. Each 'snapshot' shows the data as it stood when the period was closed, or on the most recent processing date of the current fiscal period. With this versioning, reports generated for a closed FP give the same results whenever they are run, even if the data about a particular reference object was changed in a later FP.

The data is only frozen (and the snapshot taken) when the FP is closed. An open FP is kept current as modifications are made, which will affect report contents during the period. There is an overlap period when both the current and the previous FP are open and subject to modification. After an FP is closed, no more changes are made (although manual emergency fixes are applied in extremely rare situations).

The source of the fiscal period identification for a particular processing date is the "UCD_Date_to_Fiscal_Period_Conv" table. When the fiscal period changes based on this table a new fiscal period version of the reference data is initialized. In KFS, the default fiscal period change is usually on the fourth working day of the month. That night will see the creation of the new fiscal period snapshot of reference data. The "UCD_Fiscal_Period" table is used to determine how long a previous fiscal period will continue to be updated in parallel with the current default fiscal period. There are 13 FPs in each FY. Period 13 overlaps period 1 so that Fiscal Close processing can continue for the previous FY while the new FY is open for current processing.

If you query/join against a reference table with just the identifying key of the reference object, and do not specify a FY and FP, you will get a version of the reference object for each FY/FP that occurred since the reference object was defined.

There are also sets of data in the reference tables that are kept current until they are frozen by the closing of an FY. These can be identified by the '--' (two dashes) in the FP field. This set is useful for joins/reports that don't have a particular FP to report for and want whatever was the final version (or current version for the current FY) of the data for the FY (after the FY is closed, this FP '--' version will match the version for FP '13').

There is a set of data in the reference tables that can be used to get what is current, independent of the FY/FP. This set uses 9999 as the FY and '--' as the FP, and is always kept matching the current version of the reference data in the TP database.

Oracle LDAP Service Configuration

Your sqlnet.ora file should contain the following lines:

NAMES.DEFAULT_DOMAIN = ucdavis.edu
NAMES.DIRECTORY_PATH = (LDAP, TNSNAMES)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT =
RC4_256,RC4_128,RC4_56,RC4_40,AES256,AES192,AES128)

Your ldap.ora file should contain the following lines:

DIRECTORY_SERVERS= (oraldap1.ucdavis.edu:389:636,oraldap2.ucdavis.edu:389:636)
DEFAULT_ADMIN_CONTEXT = "dc=ucdavis,dc=edu"
DIRECTORY_SERVER_TYPE = OID

Once configured you can configure your SQL client to use the connect string “FIS_DS_PROD.ucdavis.edu” to connect to the Decision Support Database using your SQL tool of choice.

Oracle OCI JDBC Driver

jdbc:oracle:oci:@FIS_DS_PROD

Oracle Thin JDBC Driver

Example 1:

jdbc:oracle:thin:@fis-dss.ucdavis.edu:1521:DSPROD

Example 2:

jdbc:oracle:thin:@ldap://oraldap.ucdavis.edu:389/FIS_DS_PROD,cn=OracleContext,dc=ucdavis,dc=edu

Changing your DS Direct Password

To change a user's password in Oracle, you need to execute the alter user command.

SYNTAX

The syntax for changing a password in Oracle is:

ALTER USER user_name IDENTIFIED BY "new_password" replace "old_password"; 

PARAMETERS

user_name is your user account.

new_password is the new password to assign.

old_password is the existing password.

EXAMPLE

If you wanted to reset the password for a user named smithj, and you wanted to set the new password to autumn, you would run the following command:

ALTER USER smithj IDENTIFIED BY "autumn" replace "summer";

Please note that passwords must conform to the following criteria:

  • It must be different from your login ID (including login ID reversed).
  • It must be at least 12 characters long.
  • It must contain a mixture of upper and lower case letters and digits.

Supplemental content

FIS Decision Support Menu