Sample Specification

 

Project_Inventory_Listing -- TIMS Database Link

By Richard Langseth

XXX X, 2002

 

Overview

 

 

This paper outlines the procedures required to more closely integrate the Project_Inventory_Listing (PIL) with the TIMS database.  The overall premise is that the Excel-based PIL data can be transformed into a static SQL Server table using Microsoft COM objects.  Then this table can be feed into TIMS projects and project initiatives.  Since there is the possibility that existing rows of project and project initiative data may be updated in this process, project and project initiative history will be maintained.  Currently this data is not maintained. 

 

This paper dwells on the controlling of the PIL downloading tasks as they occur.  SQL Server-based constraints are proposed.  T-SQL scanning is envisioned through the use of programming cursors to validate relationships among tables.  Reports and SQL Server log file analyses are also proposed that include row counts and “balancing totals” to be maintained.

 

One disadvantage to this approach is that the old, existing TIMS project, initiative and weeks/hours data is de-linked from the new project rows.  To save this history data when it is associated with well-formed projects that can be associated to the new project rows based on PIL identifiers, a pour-in process is proposed as an optional step (Initiative 4 below).  In this process a GUI-based session would be set up to allow the TIMS staff the opportunity to trigger a row-by-row re-linking of existing project data to the new projects created from the PIL data.  Note that it is necessary to build the history tables anyway to provide for an event log for when PIL data causes changes to existing rows of the project and initiative tables.  Using these history tables to facilitate an initial “pour in” is a bonus.

 

 

Here are the major initiatives to consider:

 

1.                    Facilitate the connection between PIL and TIMS using Microsoft DTS (Data Transformation Service) COM techniques to populate a “shadow” Project_Inventory_Listing (PIL) table under SQL Server control.  This connection will probably be short-lived because a major upgrade is planned for the PIL system.

 

2.                    Establish and maintain a process through which TIMS Project table rows are refreshed to reflect the current status of the Master Inventory system project information through a periodic scan of shadowed PIL table via a Transact SQL-based stored procedure.  The potential exists for this to be a long-lived process that is used after the introduction of the permanent PIL upgrade.

 

3.                    Retain a set of TIMS history tables that house projects, initiatives, and week/hours data as they existed at the moment of conversion to the integration of PIL and TIMS.  These history files are also needed to log automatic changes to the Project and ProjectInitiative tables triggered by PIL table triggers and T-SQL cursor scans.

 

4.                    Develop a user session that can be employed to extract data from the initial history tables to be poured into the automatically-generated PIL-fed projects, project initiatives, and or week/hour tables.  This will allow for the continued access to well-structured project, initiative and week/hour data that has previously been entered into TIMS.

 

5.                    Build SQL Server-enforced database consistency checks and TSQL-driven reports that analyze both the user-level tables and the SQL logged events.

Data Model

 

A physical-level data model of the TIMS system was reverse engineered from the XXXX Detail Design Document database specification prepared by Xxxx Xxxx.  Additional tables proposed in this approach that are not in Xxxxx’s document include the Project_Inventory_Listing table, the ProjectHistory table and the ProjectInitiativeHistory table.  The data model has been validated against the Proposed System Flowchart of the Phase XXX system  (TIMS_PhaseXXX_Detail_System_Design_Ver3.doc [Xxxxx Xxxxx] )

 

 

Diagram 1

 

The following diagram shows the proposed dataflow of the Project_Inventory_Listing data to the Project table.  It shows the potential data flow of ProjectHistory data to the Project table.  This diagram also demonstrates the hierarchy of BusinessSegment, Program, and Project.  Note that BusinessSegment is directly connected to Project.  The Program table is also directly connected to the Project table.  This is contrary to the situation presented in the Detail Design Document where the BusinessSegment table is shown to be attached to the Program table.  The reason for this approach is to build a structure that more closely resembles a classic star structure rather than the more complicated snow flake structure.

  

 

 

 

In the above diagram the proposed ProjectHistory table is shown to have two columns not included in the Project table.  They are the proj_hist_eff_date and the proj_hist_code.  These columns would allow for the buildup of an array of project history data for a given project.  This item becomes important when one applies PIL changes against a given project.  The ProjectHistory table would show the condition of the project prior to the automatic changes spawned by a changing PIL table.

Diagram 2

 

 

This second diagram is at the Project Initiative level.  The hierarchy presented here is Program, Project, and ProjectInitiative.  A separate thread of hierarchy which includes the BusinessSegment is not shown. (See Diagram 1 above)

 

If there is a reason to pour data from the Project_Inventory_Listing (PIL) to the ProjectInitiative table, this processing would follow the path presented below.  Note that a business case to do so has yet to be totally flushed out.  Under consideration is the potential for a PIL change to impact a ProjectInitiative.  

 

 

 

 

 

 

In the above diagram the proposed ProjectInitiativeHistory table is shown to have two columns not included in the ProjectInitiative table.  They are the proj_init hist_date and the proj_init_hist_code.  These columns would allow for the buildup of an array of project initiative history data for a given project initiative.  This item becomes important if one applies PIL changes against a given project initiative.  The ProjectInitiativeHistory table would show the condition of the project initiative prior to the automatic changes spawned by a changing PIL table.

 

Initiatives

 

Initiative 1 – PIL/TIMS Connection

 

 

1.                   The objective of this initiative is to facilitate a connection between PIL and TIMS using Microsoft DTS (Data Transformation Service) COM techniques to populate a “shadow” Project_Inventory_Listing (PIL) table under SQL Server.  This shadow table then can be used to determine the insertions and updates that are required to keep the PIL system in sync with the TIMS system.  The steps are of two types:  A = Analysis and C = Coding.

 

A.    Analyze requirements and processing overview

 

i.              Conduct a data model-level review.  Determine the appropriate data elements to be brought over to TIMS from PIL.

 

ii.             Establish the logic required to decide on what data to change within TIMS based on new information in the PIL   Scope out the SQL that is required to scan the PIL table and update the TIMS Project and possibly Program tables.

 

iii.            Study the ripple effects to other TIMS tables including the Project_Initiatiave and WeekHr tables – Build a data model-level study of how changes to the PIL affect these other TIMS tables. Basically this task is to determine what SQL is needed to align rows in the other tables to the new Project table rows.

 

 

C.    Develop SQL and COM-level technical approach.  Set up SQL procedures, etc.

 

i.              Scope out the SQL code to be used to generate and maintain the PIL table within the TIMS database.  Update the data model to reflect this new table. Test resulting table generation code and archive all within a test environment.

 

ii.             Determine platform to be used to support the generation and maintenance of the  Project_Inventory_Listing table on TIMS.  Choose between Transact SQL session under the ISQL monitor and a VBA approach under Excel/Access which may be built upon Microsoft Query or ActiveX Data Objects (ADO). Generate the code, test and archive the same under the control of the selected platform.

 

iii.            Maintain logging/audit entries to control the PIL table adds within TIMS.  Set up the stored procedure or VBA code to support this logging process.    Tasks include the capturing of row insertions and row updates by inserting history rows into the appropriate history tables.

 

               

 

Initiative 2:  SQL Table Scans and/or Trigger Actions

 

 

2.                   Establish and maintain TIMS Project table rows that reflect the current status of the Master Inventory system through a periodic scan of shadowed PIL table via a Transact SQL-based stored procedure.  Consider the use of automatic SQL triggers.

 

 

A.    Document logic to

 

i.              Trigger changes to TIMS Project and Program tables based upon a scan of changes in the PIL table.

 

ii.             Launch changes to other TIMS tables based on the new information in the PIL that causes changes to the Project table.

 

iii.            Logging requirements.

 

 

C.    Develop COM-level technical approach.  Set up SQL procedures, etc.

 

i.              Code SQL triggers, stored procedure or VBA code (reflecting the platform decision made above)  that scans the PIL table in TIMS and adds or updates rows in TIMS Project and Program tables based on the scan.  Test resulting code and archive all within a test environment.

 

ii.             Code SQK Triggers, stored procedure or VBA code to maintain the other tables such as ProjectInitiative and WeekHr tables – Use the platform selected above to align rows in the other tables to the new Project table rows.

 

iii.            Logging requirements - Set up the stored procedure or VBA code to support logging of the above activity.

 

 

Initiative 3:  History Table Maintenance

 

 

3.                   Retain a set of TIMS history tables that house Projects, Initiatives, and WeekHrs data as they existed at the moment of conversion to the integration of PIL and TIMS.  Set up SQL-based routines that populate these tables.

 

 

A.    Document logic to

 

       

i.              Develop methodology to establish and maintain history tables for Project and ProjectInitiative tables.

 

ii.             Pour existing data rows into history tables.

 

iii.            Logging requirements.

 

 

C.    Develop COM-level technical approach.  Set up SQL procedures, etc.

 

        i.              Code ISQL that creates, maintains and clears history tables.

 

ii.             Code bulk copy batch job or Data Transformation Service (DTS) and/or ISQL that populates history tables from working tables.

 

iii.            Logging requirements - Set up the stored procedure or VBA code to support logging of the above activity including the capture of rows just prior to updates triggered by PIL table scans and/or triggers.

 

Initiative 4  Link History to PIL-generated projects

 

 

4.                   Develop a user session that can be employed to extract data from the history tables to the newly initialized PIL-based projects, project initiatives, and or week/hour tables.

 

 

A.    Consider the steps required to:

 

i.              Select Project_History and/or ProjectInitiative_History table rows that should be poured into newly-assigned Project and/or ProjectInitiative rows -- these new rows having been established to shadow the PIL table.

 

ii.             Select WeekHrs_History that could be linked to the newly-established Project and Project_Initiatiave rows, a process that would allow for the continued reporting of history across the conversion from the old Project coding to the new Project coding.

 

iii             Logging requirements.

 

 

C.    Develop COM-level technical approach.  Set up SQL procedures, etc.

 

i.              Following the platform decision established in Section 1 C ii use the same platform to establish a Excel or Access-based “client-server” type session that allows for modifying newly-established Project and/or ProjectInitiative table rows.  The user interface to be presented would include display windows that would show the history tables and allow for the updating of the Project and the updating or expansion of ProjectInitiative tables with the displayed history.

 

ii.             The above step would be expanded to allow for the linking of existing HourWk History rows into the ProjectInitiative table.

 

iii.            Logging requirements - Set up the stored procedure or VBA code to support logging of the above activity.

 

 

 

 Initiative 5 - Consistency checks and SQL table scan reports.

 

 

5.                   Build SQL Server-enforced database consistency checks and TSQL-driven reports.

 

 

A.    Document the business need for:

 

i.              enforced SQL Server rules and consistency constraints.  Refine data model to reflect the impact of setting up constraints.  Plan for bulk copy or other DTS operations being run within the constraints so established.

 

ii.             a T-SQL Scan to detect other inconsistencies or rule violations.

 

iii             other T-SQL or Microsoft Query-driven data base reports including such items as row counts, parent-child inventories, etc.

 

iv.            logging reports to capture the processing of scans and reports.  Include the possibility of analyzing SQL Server logs that are generated by enforced consistency violations at the SQL level.

 

 

C.    Develop SQL technical approach.  Set up SQL procedures, etc.

 

i.              Develop the approach for the SQL Server script generation of Transact SQL code to enforce SQL Server rules and consistency constraints.  Consider the need to drop and regenerate constraints on the fly to allow for bulk copying or other DTS operations.

 

ii.             Following the lead established in (1 C ii) use the same platform to develop scan reports to detect other inconsistencies or rule violations.   These reports may be directly generated using T-SQL stored procedures or other cursor scripts or through a VBA set of routines that use ActiveX Data Objects (ADO).

 

iii             Section (1 C ii)-housed or Microsoft Query-generated T-SQL code to inventory the data base.

 

iv.            Logging requirements - Set up the stored procedure or VBA code to support logging of the above activity.