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:
- Resides in C:\Unanet_IMU\
- Calls three Export Templates in Unanet to provide reference data for GCSConnect. Note: These Export Templates must be set up in Unanet first:
- Extracts data from Unanet and places the following files into directory C:\UNA_EXPORTS\.
- Resides in C:\Unanet_IMU\
- Cleans up IMU Data area and deletes data folders more than 30 days old
- Opens MSAccess
- Calls MAIN macro. Gives MSAccess 60 minutes to run the macro(s) and provide the output before timing out.
- 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:
- Indirect.csv (not typically used)
- Locations.csv (not available in this release)
- The IMU then imports the CSV files into Unanet using specifications in the IMU configuration files.
- Creates resulting log and error files, and writes them to the latest datetime-stamped IMU folder.
- Sends email notifications to designated email addresses with error messages and latest results.
MS Access: GCSConnect Macros
There are three Macros used in GCSConnect:
|MAIN||This 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.|
This macro is the first macro called by MAIN. It does the following:
|STEP_2_EXPORT_ALL||This 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:
|IMPORT-UNANET_Person||Imports file called C:\UNA_Exports\UNANET_Person.txt into the B_IMPORTED_UNANET_Person table.|
|IMPORT-UNANET_Project||Imports file called C:\UNA_Exports\UNANET_Project.txt into the B_IMPORTED_UNANET_Project table.|
|IMPORT-UNANET_Expense||Imports 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 Prefix||Query 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.|
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 Prefix||Table 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.|
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.
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.|