The main MPS SQL Server Data Warehouse is the main data repository the MPS Dashboards and is loaded from the intermediate SQLite Database. This article describes the load process.
The main SQL Server MPS Data Warehouse (DWH) contains a number of routines (SQL Server Stored Procedures) which perform the controlled load of the MPS Data into tables in the DWH.
Within the DWH there is a link (Linked Server) to the Intermediate SQLite database (3 Core Tables). Data is loaded from the SQLite database into the SQLServer Data Warehouse by executing (double clicking) the load batch process file which normally resides here:
C:\Intuitive_WorkFiles\MPS\ETL\ETL_LoadData.bat
This batch file in-turn executes the SQL script to execute the Stored Procedures to perform the DWH load:
C:\Intuitive_WorkFiles\MPS\ETL\Load_DWH.sql
The SQL script performs the following tasks as standard (but can be adjusted if required to meet local needs i.e. to force a re-load of existing data etc.).
- Checks to ensure that the Linked Server to the SQLite database is online
- Loads the LOOKUP data (always)
- Loads the VOLUMES data (where new data exists)
- Loads the SERVICE data (where new data exists)
Once the DWH load has completed, a log file is produced which should be reviewed to ensure no errors have been reported during the load:
C:\Intuitive_WorkFiles\MPS\ETL\ETL_Log.txt
The three main tables maintained in the DWH are as follows:
- dbo.OPS_MIF (Volumes data)
- dbo.OPS_SERVICE (Service Data)
- dbo.OPS_LOOKUP (Call Type Lookup data)
The three main SQL views of the data which feed the Intuitive for MPS dashboard datafeeds are as follows:
- viw_KM_Printfleet_Data_SHNewv2 (Volumes Data)
- viw_KM_Printfleet_Data_Summary_Counts (Volumes Data Summary)
- viw_KM_Service_Calls_SHNewv4 (Service Data)