Intuitive for MPS - CSV File Import Process

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.

MPS_ETL_Service_Diagram

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:

ETL_Config_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:

ETL_Config_Import_File_Header

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:

ETL_Config_Table_Fields_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>

<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"/> 
<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>


<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"/> 
<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>

<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"/> 
<Fields>
<Field name="LOOKUP_ID" datatype="int"/>

<Field name="LOOKUP_TYPE" datatype="nvarchar(128)"/>

<Field name="LOOKUP_DESC" datatype="nvarchar(128)"/>

<Field name="LOOKUP_GROUP1" datatype="nvarchar(128)"/>

<Field name="FLAG_UPTIME" datatype="int"/>

<Field name="FLAG_FTF" datatype="int"/>

<Field name="FLAG_RESPONSE" datatype="int"/>

<Field name="FLAG_RESTORE" datatype="int"/>

<Field name="FLAG_FIX_TYPE" datatype="int"/>

</Fields>
</Table>
</Tables>
</Database>
</DatabaseFiles>
</ETLData>
</configuration>