Prior to loading data into the main SQLServer Data Warehouse, the Intuitive for MPS system imports data from the three main CSV import files into an intermediate SQLite Database via the Intuitive ETL Service.
The three CSV import files are as follows:
The ETL Service imports the three CSV files based on an XML configuration file. Appendix A below shows a sample template for the ETLService.Config file which normally resides in the folder:
C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboards\Tools\ETL Service
The ETL service for MPS creates three tables in the BI_Combined_Database_SQLite.db database as follows:
- ops_mif
- ops_service
- ops_lookup
Note: If the BI_Combined_Database_SQLite.db file does not exist, it will be created by the process when the ETL service is first started.
The ETL Service can also be run as a console application (C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboards\Tools\ETL Service\ETLService.Console.exe).
Once the ETL Service is running, it will load data according to the XML configuration file and the schedule that has been defined.
ETLService.config XML Configuration File explained:
Header Section:
This section defines system information and defines the paths where different system and log files reside.
The location and name of the SQLite database to be created and the initial SQL script (init.sql here) that should be executed on first creation of the database is defined here (this script can create custom views etc.). (Line 12)
When the "Intuitive Dashboards ETL service" is started for the first time (under Windows Services), it will create a file in the 'Initialised' folder (Line 8) to indicate that the initial creation of the database and load has been undertaken.
Note: If you wish to re-initialise the system for any reason this file and the BI_Combined_Database_SQLite.db database must be deleted and the service restarted.
Table definition Section:
This section defines the name of the table to be created in the SQLite database (ops_mif) and whether the table is to be cleared of data before loading new data (Line 3)
The source data file and delimeter is defined here too (line 4)
The ETL service will load data according to the schedule that is defined here (Line 7). In this example, the table will be loaded from the CSV file every day at 6:00am. There are also other options (commented in green) to run the schedule at intervals throughout the day and to end the schedule at a certain time each day.
Table field definition Section:
This section defines how the fields present in the CSV file are to be named/typed and imported into the SQLite database table.
Note: There are many configuration options available when defining the ETLService.config file. There is a document availabel from Intuitive which describes the ETL Service in full detail.
Next Load Step:
Once the CSV files have been loaded to the intermediate SQLite database via the ETL Service, the MPS Data Warehouse can now be loaded by following the Data Warehouse Load Process.
Appendix A: Example ETLService.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="ETLData" type="IBI.ETLService.Library.Utils.Configuration, ETLService.Library" />
</configSections>
<ETLData>
<FileSystem>
<InitFiles Path="C:\Intuitive_WorkFiles\Initialised" />
<LogFiles Name="Scheduler" Path="C:\Intuitive_WorkFiles\Logfiles" />
</FileSystem>
<DatabaseFiles>
<Database name="C:\Intuitive_WorkFiles\MPS\Database\BI_Combined_Database_SQLite.db" init="file:MPS\init.sql">
<Tables>
<!-- OPS MIF table-->
<Table name="ops_mif" clear="true" >
<Source type="file" format="delimited" delimiter="," name="c:\intuitive_workfiles\OPS_MIF.csv">
<OnStartup runschedule="true"/>
</Source>
<Schedule type="explicit" days="m,t,w,th,f,sa,su" start="06:00"/> <!-- interval="60" end="17:00"/>-->
<Fields>
<Field name="Reporting_Month" datatype="datetime"/>
<Field name="Reporting_Hierarchy" datatype="nvarchar(255)" />
<Field name="Account_Name" datatype="nvarchar(1024)" allownull="false"/>
<Field name="SDM" datatype="nvarchar(255)" />
<Field name="Site" datatype="nvarchar(1024)" />
<Field name="Location" datatype="nvarchar(255)" />
<Field name="Unique_Reference" datatype="nvarchar(255)" />
<Field name="Equipment_No" datatype="nvarchar(255)" />
<Field name="Serial_No" datatype="nvarchar(255)" />
<Field name="Equipment_Desc" datatype="nvarchar(255)" />
<Field name="Address_Line_1" datatype="nvarchar(255)" />
<Field name="Address_Line_2" datatype="nvarchar(255)" />
<Field name="Address_City" datatype="nvarchar(255)" />
<Field name="Postcode" datatype="nvarchar(128)" />
<Field name="Uptime_SLA_Pc" datatype="Real" />
<Field name="FTF_SLA_pc" datatype="Real" />
<Field name="Response_Mins_SLA" datatype="int" />
<Field name="Restore_Mins_SLA" datatype="int" />
<Field name="Rec_Monthly_Vol" datatype="int" />
<Field name="Device_Type" datatype="nvarchar(255)" />
<Field name="Mono_Or_Color" datatype="nvarchar(128)" />
<Field name="Click_Billing" datatype="real" />
<Field name="Rental_Billing" datatype="real" />
<Field name="Total_Billing" datatype="real" />
<Field name="Page_Total" datatype="int" />
<Field name="Mono_Total" datatype="int" />
<Field name="Color_Total" datatype="int" />
<Field name="Copy_Mono" datatype="int" />
<Field name="Copy_Color" datatype="int" />
<Field name="Print_Mono" datatype="int" />
<Field name="Print_Color" datatype="int" />
<Field name="Fax" datatype="int" />
<Field name="Scan" datatype="int" />
<Field name="Duplex" datatype="int" />
</Fields>
</Table>
<!-- OPS Service table-->
<Table name="ops_service" clear="true" >
<Source type="file" format="delimited" delimiter="," name="c:\intuitive_workfiles\OPS_SERVICE.csv">
<OnStartup runschedule="true"/>
</Source>
<Schedule type="explicit" days="m,t,w,th,f,sa,su" start="06:00"/> <!-- interval="60" end="17:00"/>-->
<Fields>
<Field name="Equipment_No" datatype="nvarchar(128)"/>
<Field name="Notification_No" datatype="nvarchar(128)"/>
<Field name="Notification_Date" datatype="datetime"/>
<Field name="Notification_Time" datatype="datetime"/>
<Field name="Reporting_Month" datatype="datetime"/>
<Field name="Sv_Address_Line_1" datatype="nvarchar(128)"/>
<Field name="Sv_Address_Line_2" datatype="nvarchar(128)"/>
<Field name="Sv_Address_City" datatype="nvarchar(128)"/>
<Field name="Sv_Postcode" datatype="nvarchar(128)"/>
<Field name="Problem" datatype="nvarchar(2048)"/>
<Field name="First_Time_Fix" datatype="int"/>
<Field name="Return_to_Fit" datatype="int"/>
<Field name="Working_Days" datatype="int"/>
<Field name="Total_Downtime_Mins" datatype="real"/>
<Field name="Response_Mins" datatype="int"/>
<Field name="Response_Mins_Cat" datatype="nvarchar(128)"/>
<Field name="Restore_Mins" datatype="int"/>
<Field name="Restore_Time_Cat" datatype="nvarchar(128)"/>
<Field name="Call_Out_Type" datatype="nvarchar(128)"/>
</Fields>
</Table>
<!-- OPS Lookup table-->
<Table name="ops_lookup" clear="true" >
<Source type="file" format="delimited" delimiter="," name="c:\intuitive_workfiles\OPS_LOOKUP.csv">
<OnStartup runschedule="true"/>
</Source>
<Schedule type="explicit" days="m,t,w,th,f,sa,su" start="06:00"/> <!-- interval="60" end="17:00"/>-->
<Fields>
<Field name="LOOKUP_ID" datatype="int"/>
<!-- Static Value = 1 -->
<Field name="LOOKUP_TYPE" datatype="nvarchar(128)"/>
<!-- Static Value = "Call_Out_Type" -->
<Field name="LOOKUP_DESC" datatype="nvarchar(128)"/>
<!-- Variable Value = OPS_SERVICE.Call_Out_Type -->
<Field name="LOOKUP_GROUP1" datatype="nvarchar(128)"/>
<!-- Optional. Free Text to group LOOKUP_DESC-->
<Field name="FLAG_UPTIME" datatype="int"/>
<!-- 0/1 Flag. Use this Call Out Type in Uptime SLA calculation? -->
<Field name="FLAG_FTF" datatype="int"/>
<!-- 0/1 Flag. Use this Call Out Type in First Time Fix SLA calculation? -->
<Field name="FLAG_RESPONSE" datatype="int"/>
<!-- 0/1 Flag. Use this Call Out Type in Response Time SLA calculation? -->
<Field name="FLAG_RESTORE" datatype="int"/>
<!-- 0/1 Flag. Use this Call Out Type in Restore Time SLA calculation? -->
<Field name="FLAG_FIX_TYPE" datatype="int"/>
<!-- 0/1 Flag. Show this Call Out Type in Fix Type 'Charts'? -->
</Fields>
</Table>
</Tables>
</Database>
</DatabaseFiles>
</ETLData>
</configuration>