Alvaria WFM (ex Aspect eWFM)
The eWFM resource planning system offers a plethora of reports with various options for extracting and analyzing data, simplifying analysis and trend identification. However, navigating the system's intricacies, especially its complex superstate philosophy, can be daunting for those not deeply familiar with its workings ("eWFM From the Eyes of a WFM Expert").
Unfortunately, the database does not directly provide the combined result of all superstate layers. Therefore, accessing schedule data necessitates utilizing the reporting facility, resulting in numerous export files, particularly in medium-sized contact centers. These export files, referred to as eWFM IDP files, contain data per time interval (15 minutes or 30 minutes) and should not be confused with exports of superstate data per employee/day/superstate.
Our approach involves processing eWFM IDP files using MAIS to build up data in a time series storage.
Staging
The initial step involves importing all files into a shared table (see Appendix A) where the table's column names align as closely as possible with those of the files. The exported files typically consist of forecasts (calls, fc) and schedules (staffing groups, sg). For forecasts, columns such as RVOL, RAHT, and RSL are utilized, while REQ, SCH, and NET represent staff-related data (required, scheduled, and the net difference). Additionally, the name of the file (datasource) and time interval (15 or 30 minutes) for each row are retained.
We opt to store data initially in SQL VARCHAR format to prevent conversion errors in the staging environment, though numeric columns can also be stored as REAL or FLOAT. Some columns, such as PRI_INDEX, ROUTING_SET, and STOP_TIME, may be omitted as they carry non-essential data, provided we retain information on the originating file.
Each data load involves truncating the staging table and inserting rows from all files. While SQL SSIS tools are utilized for this task, any ETL tool can be employed. Several practical maintenance issues are encountered, including:
- The files contain dates relative to the current date, requiring adjustments for certain files to synchronize data further backward without exporting excessive volumes of data.
- Column names in the files may change unpredictably when eWFM export configurations change.
Processing Time Series Data
Moving forward, the staging data can be processed in two main ways:
- Traditional Approach: Accumulating data in a separate table.
- Time Series Approach: Accumulating data in time series, facilitating easier processing of irregular deltas and integration with other data sets directly.
MAIS's DELTA loading facility enables configuring standard A_IMPORT procedures to import data per day and per day/time interval:
- Forecasts Data: Defined by the date field [START_TIME] and four values extracted from the data: sum([RVOL]), sum([RVOL]), sum([RSL]/100 * [RVOL]), and sum([RAHT]*[RVOL]).
- Schedules Data: Defined by two values: SUM(REQ/4.0) and SUM(SCH/4.0), with adjustments made to account for staff per interval.
Once the import procedures are configured, each time series import only requires adding a filter with the file's name, as each file typically corresponds to one time series.