Direct Database Access

Direct Access to Decision Support Tables

Requesting Direct Access
Using Your Direct Access
Fiscal Year and Fiscal Period Versions of Data

Requesting Direct Access

If you require direct access to the database, for the purposes of designing your own queries or extracting data into your own datamart, you will need a direct access account on the Decision Support database. Click on the link below to go to the request form. Your request will be reviewed and responded to by DaFIS staff, usually within a few days.

It is requested that you have a good knowledge of SQL or attend a class on database querying before attempting to run queries against the Decision Support database. Understanding of the nature of joins is especially important. 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 even simple tasks for every user. Improperly formed queries are subject to termination without warning if they may be affecting the performance of the Decision Support system. If you are extracting large amounts of data into a local database, we ask that you restrict your loads to times of the day with lower usage. These are: 6am-9am, 12pm-1pm, 4pm-7pm.

Repeated executions of badly designed queries, after a warning by the DaFIS 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.

Click Here to Request Direct Database Access

Note: Once your request for direct access is approved, if you will need access data in Charts S or H, you must request that access separately. To request direct database access to Chart H data, contact Ann Steele and for Chart S, contact Linda Dickinson.

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, Oracle Query Builder, Microsoft Access, 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/Net8 installed on your system (this will already be installed on your system if you have DaFIS TP installed). Most tools also require an Oracle ODBC driver (which is an option during the DaFIS TP install). When setting up the connection, the database name/connect string = fis_ds_prod. Once you have logged in, the schema = finance.

Decision Support Data Group Table Information

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. While an FP is open, the data is kept current with any modifications made during the period. The data for an open period can frequently change, thus affecting report contents. There is an overlap at the beginning of an FP 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 TP, 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 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 specifying 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.