By taking advantage of the service representatives’ familiarity with Excel, Superior Consulting Services (SCS) designed a solution that eliminated programmer involvement in creating a huge number of diverse ETL processes. Instead, SCS created a configurable ETL process that can be controlled by account service personnel.
As clients migrate to online reporting, the account representative looks at their client’s personnel and hierarchy reports and ‘tells’ the ETL where to find the data it needs to properly configure data access restrictions for each employee of that company.
Service representatives add a new company to the ETL process by adding the company’s name and the name of the directory where data to be imported can be found to an Excel spreadsheet that sits in a specific location monitored by the ETL. This is the Master configuration file.
In the directory that the representative named in the Master configuration file, they place a company configuration file along with the files containing employee and hierarchy data from the client. The company configuration file is nothing more than another Excel file where they specify the configuration of the client data on various pre-defined tabs according to SCS supplied documentation.
The SCS-developed ETL solution reads the Master configuration to determine the locations where data to be imported might reside. It uses the company configuration file in each directory to look for new data to import and understand the specific format supplied by that company.
The spreadsheet used to configure how to read the client supplied data files allows the representative to specify the names of the files being supplied and their basic format (for example CSV, TSV or XLSX), including:
-
The columns to be used as input
-
How to recognize rows that should be ignored
-
‘Regular expression’ validations to perform on the data before importing
-
Data transformations—the transformations are needed, for example, to standardize name formats
The ETL archives successful data imports and reports detailed descriptions of trouble when a problem is encountered so the representative can either contact the client to fix the problem or change the configuration as needed.
Dynamic reports allow authorized people to examine the data being imported at each stage in its transformation—from a client-defined format to a ‘standard’ format where a dedicated ETL can easily apply the updates. These reports allow examination of raw ‘customer format’ data, normalized data and transformed data.
