Page tree
Skip to end of metadata
Go to start of metadata

Overview

The interface between Unanet and Deltek GCS Premier (GCS) is implemented through a Microsoft Access database (MSAccess) which utilizes macros, tables and queries to provide master table data from GCS for import into Unanet.  Data from GCS are accessed through the Transoft U/SQL ODBC driver and a Universal Data Dictionary (.UDD).   Data from Unanet are exported first (from Unanet) and imported into existing MSAccess tables with a Saved Import.

Individual queries are written in MSAccess for each required Unanet Import.  A query can pull data from multiple tables concurrently (from GCS, Unanet and MSAccess) and from other queries (sub-queries).  However, too many tables and sub-queries will degrade performance and potentially give both undesired and unexpected results.   With this in mind, the standardized version of GCSConnect is intended to provide the majority of required data without requiring modification of the source queries.

Components of the GCSConnect Interface

IMU (Integration Management Utility)

The IMU (Integration Management Utility) is Unanet-provided utility package that provides management functions around the import/export of data to/from Unanet. The IMU is used by GCSConnect to extract reference data from Unanet as well as to upload GCS master table data into Unanet.  The IMU is invoked by a Windows command-line batch file which can be run manually or scheduled through Windows Task Scheduler.  The IMU also contains several configuration files that determine where and how each process is run.  The IMU process is described below:

  1. UNANET_IMU_EXPORT.BAT

    1. Resides in C:\Unanet_IMU\
    2. Calls three Export Templates in Unanet to provide reference data for GCSConnect.  Note: These Export Templates must be set up in Unanet first:
      1. Unanet_Expense
      2. Unanet_Person
      3. Unanet_Project
    3. Extracts data from Unanet and places the following files into directory C:\UNA_EXPORTS\. 
      1. Unanet_Expense.TXT
      2. Unanet_Person.TXT
      3. Unanet_Project.TXT

  2. UNANET_IMU.BAT
    1. Resides in C:\Unanet_IMU\
    2. Cleans up IMU Data area and deletes data folders more than 30 days old
    3. Opens MSAccess
    4. Calls MAIN macro.  Gives MSAccess 60 minutes to run the macro(s) and provide the output before timing out. 
    5. After MSAccess finishes, the IMU moves the MSAccess-generated output files from C:\UNA_EXPORTS\ into a datetime-stamped IMU Data Folder.  The created files are:
      1. Approval_Group_Expense_Shell.csv
      2. Approval_Group_Expense.csv
      3. Approval_Group_Time_Shell.csv
      4. Approval_Group_Time.csv
      5. Assignments.csv
      6. Curr_ODC_Current_Period.csv
      7. Curr_ODC_Next_Period.csv
      8. NextYear_ODC_Period_P1.csv
      9. Indirect.csv  (not typically used)
      10. Labor_Category_Assignment.csv
      11. Labor_Category.csv
      12. Locations.csv  (not available in this release)
      13. Organization.csv
      14. Person.csv
      15. Project.csv
      16. Project_Administrator.csv
    6. The IMU then imports the CSV files into Unanet using specifications in the IMU configuration files.
    7. Creates resulting log and error files, and writes them to the latest datetime-stamped IMU folder.
    8. Sends email notifications to designated email addresses with error messages and latest results.

MS Access: GCSConnect Macros

There are three Macros used in GCSConnect:

MacroDescription
MAINThis macro is called by the Unanet IMU and calls the other two macros (STEP_1_PREPROCESS and STEP_2_EXPORT_ALL).  In addition, when completed, this macro closes MSAccess.
STEP_1_PREPROCESS

This macro is the first macro called by MAIN.  It does the following:

  1. Imports the PROJECT, EMPLOYEE and EXPENSE data into the B_IMPORTED_UNANET tables.  
  2. Runs the A_CREATE queries and populates the Q_TABLE and Z_TABLE tables.  
  3. Runs the B_APPEND queries and updates the Q_TABLE and Z_TABLE tables.
STEP_2_EXPORT_ALLThis is the second macro called by MAIN.   All Interface tables to be exported to Unanet are created through this macro.  Each table is created as a .CSV file (Comma Separated Variables) and stored in C:\UNA_EXPORTS\.

MS Access: GCSConnect Saved Imports

MSAccess Saved Imports are used to bring Unanet Datafiles into the MSAccess Interface without making a direct connection to the Unanet SQL Database.  The files are first exported from Unanet through the IMU and placed in the C:\UNA_Exports\ directory. There are three Saved Imports used in GCSConnect:

Saved ImportDescription
IMPORT-UNANET_PersonImports file called C:\UNA_Exports\UNANET_Person.txt into the B_IMPORTED_UNANET_Person table.
IMPORT-UNANET_ProjectImports file called C:\UNA_Exports\UNANET_Project.txt into the B_IMPORTED_UNANET_Project table.
IMPORT-UNANET_ExpenseImports file called C:\UNA_Exports\UNANET_Expense.txt into the B_IMPORTED_UNANET_Expense table.

MS Access: GCSConnect Queries

There are four types of queries used in GCSConnect:

Query PrefixQuery Description
A_CREATE_Create queries are used to populate MSAccess tables for repetitively used data.  These queries are run during the STEP_1_PREPROCESS Macro.
B_APPEND_

Append queries are used to add additional records to existing MSAccess tables.  These queries are run during the STEP_1_PREPROCESS Macro.

Q_UTILITY_These are Sub-queries that serve as utilities with the interface, deriving calculated fields that are needed by multiple queries throughout the interface.
Z_OUTPUT_Output queries are used to create the actual data exported to Unanet.  They are formatted using the Unanet Import Template and contain the fields required in the exact order specified in the Unanet Import Template. 
ZZ_...Queries for loading historical data. These queries are not invoked as part of the regular flow of GCSConnect, but are instead meant for 1-time use to flow historical time and expense data.

MS Access: GCSConnect Tables

There are five types of tables used in GCSConnect.  They are prefixed for grouping and ease of identification:

Table PrefixTable Description
A_TABLE_CONFIG_These configuration tables allow for various configuration choices to be made within the interface. They are pre-populated with values applicable to all GCS clients, but the content of these tables can be modified to provide client-specific parameters for import into Unanet. 
B_IMPORTED_UNANET_These tables contain data exported from Unanet to provide referential look-up and validation for existing projects, employees and expenses.  "Saved Imports" are set up in MSAccess to facilitate the import process.  Source data for the import are stored in C:\UNA_EXPORT and are called UNANET_Project.txt, UNANET_Person.txt and UNANET_Expense.txt.  These tables are updated when the STEP_1_PREPROCESS Macro is run.
none_

Data retrieved from GCS (through the ODBC driver) are stored in tables prefixed with none_ followed by the GCS filename.  These MSAccess tables are populated on demand (when opened manually or through a query). Note that data in GCS cannot be updated through MSAccess.

Q_TABLE_

The Q tables are created and updated by MSAccess Queries. They are used to fulfill repetitive data requirements without having to read the same GCS data files multiple times.  These tables are populated when the STEP_1_PREPROCESS Macro is run.

Z_TABLE_The Z tables are created and updated by MSAccess Queries AND intended for output to Unanet.  These are used to fulfill repetitive data requirements without having to read the same GCS data files multiple times.  These tables are populated when the STEP_1_PREPROCESS Macro is run.
  • No labels