The MPM Data Warehouse extract, translate, and load (ETL) process consists of two components:
A Controller
A collection of SQL Server Integrated Services (SSIS) data transformation packages
The controller is a Windows Service component that polls the MPM system folders on regular intervals and initiates the ETL process. It requires the Pervasive engine to be deployed along with it, if not already installed. The Controller may be installed on the MPM Data Warehouse Server or optionally on any other machine that has full network access to all MPM Data Server locations along with the MPM Data Warehouse Server.
For each MPM system folder the Controller reads an associated Scheduler.dat file and identifies which Projects should be synched and at what interval.
The project data is then copied to a temporary repository on the MPM Data Warehouse server for processing by the SSIS packages.
The SSIS Package component receives the names and locations of the projects as input for processing. It then establishes a connection to Pervasive.SQL V10 Workgroup Engine, extracts data from the different project related files (WBS, RRH, RRD, BOE etc) and transforms the data, as defined by the SQL Server 2005 / 2008 database schema. There is one package per project file—one package for loading WBS, one package for loading BOE, one package for loading Overtime, and so on.
Each SSIS Package deletes the existing related MPM Data Warehouse project data first, and then performs a bulk insert. There is no update operation. The delete logic uses the ProjectKey and GlobalKey to delete the project specific data.
Once the synchronization is complete, the temporary repository project data and related connections are removed from the MPM Data Warehouse server.
Introduction to Data Warehouse
Changing the Project Synchronization Retry Interval Settings