Direct Access to Decision Support Tables
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.
Direct access is granted to those with a good knowledge of SQL. Decision Support is a large database, and improperly run queries can affect the performance for all users (see
Know Your Joins below). Your request will be reviewed by DaFIS staff, and you should hear in a few days whether it's been accepted.
Note: Access to data in Charts S or H, is granted by specific permission, after your DS Direct Access is approved. To request access to Chart H data, contact
Ann Steele; for Chart S, contact
Linda Dickinson.
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 (this will already be installed on your system if you have DaFIS TP installed). 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 all of the following:
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
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 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
every user. Improperly formed queries are subject to termination without warning.
Note: 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.
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 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.
