Data Cleaning and Preprocessing to Ensure Accurate Analysis
In our fifth post we highlight the importance of preparing raw data for accurate and reliable analysis. As we have touched on in previous posts, actionable data insights can only be derived from trustworthy data. In most cases, the raw data extracted from an ERP system will have some issues, such as missing data, data in the wrong fields and outliers that can distort the aggregate values.
Here we asked our team to explain how they take crucial steps to prepare the data they extract so that it tells an accurate story when used in reports and dashboards.
1. Bob Corr (Managing Director DBI)
Identifying Data Issues
Usually we extract the data from ERP systems using data integration tools from our BI Platform, Diver. As we can extract all the raw data tables or at least the ones we need, we can map them together and output a first-pass data model. When we dive into the dimensions we can quickly determine where data is missing, where text needs cleaning and standardising. We can often see where there are value errors and outliers by sorting the data by each value column and diving into further detail when anything looks supiciously large or small. Some of the discoveries can be passed back to the orignating database manager to correct, others can be corrected in data tranformation or reformat scripts. For more detailed techniques read on…..
2. John Spillane (Technical Director DBI)
Missing Data
As an example, if the missing data occurs in fields such as Sales Rep or Product Group or Customer Type, it is most likely that the field has not been populated correctly in one of the maintenance tables. Notifying the database manager of this can help them fix and improve the data at source, so that at the next extract these gaps are filled. Transactional data usually holds only the codes of Products and Customers. Much more information on these codes is available for looking up on their maintenance tables. Sometimes maintenance records are created in a hurry and only the mandatory fields get populated, but often many other fields need to be completed for reliable reporting.
Other missing data might be due to extra special characters having been entered into a record, which can cause the record to split into 2 lines and then the fields become misaligned. Going back to examine the raw data in the script or in text files can help to identify these and we can use reformatting calculations to remove the extra characters as the files are read in. This type of misalignment can also affect some of the numeric fields and it’s crucial that these issues are spotted and rectified so that values are accurate.
3. Paul Duggan (Software Development Manager DBI)
Structural Errors
Sometimes the data is not missing but the structure is inconsistent or non-standardised. For example there may be many free-entry fields in the original database so that categories are entered with incorrect labels, or the same label with different formatting. E.g. Abbreviated versions of the same label, or labels with the same words hyphenated and non-hyphenated.
Depending on how many of these inconsistencies there are in any table, they can be handled in a reformat script either by reformatting in a calculation (if X-X, then make X X) or by creating a lookup table to list all the inconsistencies and map to them to a standard format.
Mapping tables of this type are also frequently used to create new hierarchies that are not even present in the original source, making the analytics model far more flexible to cater for new reporting requirements than the original database.
4. Anthony Wright (Senior BI Consultant DBI)
Normalising Data
Another way of preparing the data for efficient, speedy and accurate analysis is to reduce it down to only what is required for reporting. We can also add further fields that will help to speed up filtering, once the data model is ready to be used by analysts and other users.
We would ensure the date formats of all the date fields are consistent so that date comparisons are possible. We create time-based dimensions like Years, Quarters, Periods and Weeks using the date dimensions to derive these extra contextual fields.
We can drop fields from the source tables that we know will not be used in analysis, streamlining the data model and avoiding clutter. We can drop records that will not need to be included – for example we can remove all voided transactions from an EPOS system or create a separate model for them in case they need to be reviewed. We can restrict the model to shorter time periods than what is available in the database – typically just the last 3 years of data so that year-on-year comparisons can be carried out. These steps will all improve performance when querying the data model.
5. YY Tsang (Senior BI Consultant DBI)
Text Data Cleaning
Like the structural errors that skew the ability to view the data within standardised categories, there will often be many free-entry text fields within a database that cause inaccuracies for reporting. These inconsistencies can include different spellings of names, capitalisations or upper/lowercase differences, spaces, punctuations. These are in effect duplicates and result, for example, in data being split between more than one customer record if there are slight differences in the spelling or format of the name (a problem if you are creating a debtors’ report). Cleaning this type of data usually involves a set of calculation steps in the scripts, cleaning or standardising the case, the number of spaces between words, removing erroneous characters and scanning for spelling errors like the names of towns.
6. Lorenzo Lumanlan (BI Developer/Consultant DBI)
Standard Data Definitions
When creating an analytics model from ERP data it’s important to ensure that summary data fields have standard definitions and it’s great if you have a way of recording these. We can do this in our Measure Factory interface – each measure can be described in detail – how it was derived and what it measures.
If you don’t have such an interface, then the minimum you should do is to ensure that the calculations you make on the data are known and agreed with all those using the data. E.g. what fields are included/excluded from a calculation.
If you create fields that are going to show averages then it’s important to know what type of average is being used, e.g a mean, median or weighted average. Also if there are many missing values such as the cost of an item or product (see Missing Data above) then, any calculations done to create a profit, margin or average cost on that field are going to be very misleading if not completely inaccurate.
7. Debbie Lonsdale (Senior BI Consultant DBI)
Apart from the use of statistical methods to detect outliers, which are not always available, there are some simple steps you can take to find anomalies and genuine outliers in data.
Once you have the data model you can start sorting the data by the various summary columns and display by different dimensions to see if any of the views show extremely high values for any rows, or extremely low values. E.g. if there are multiple stores of a similar size, do any have values that are not in keeping with the others. Or if you look by period – does any period stand out? You could also create some simple charts to help you review the data in this way. In our tools we can calculate and include max values and min values which helps identify anomalies.
Sometimes a GS1 code might have been entered into the value field of an item, thereby inflating the sales enormously. Or a decimal point might have been forgotten. Once identified, these can either be fixed at source or fixed using calculations in the reformat scripts.
Genuine outliers such as Black Friday sales day will be important to track campaigns and will need to be kept visible in the data, but they can be aggregated in reporting or banded using scripting.
8. Elaine Breen (Senior BI Consultant DBI)
Validating Data Accuracy
Further to all of the techniques mentioned already, there are some final sanity checks that can be made before handing the data model over to the users for UAT and validation of the data against the values they expect to see.
Using the data model again we can dive into fields like dates to check if there are any inconsistencies such as start and end dates being back to front, or dates that are from a time period outside of the period to be included.
They can be dates that are used in maintenance tables as default if there was none available at the time the record was written and never corrected – this might be an epoch date like January 1st, 1970, that was used in the past by multiple systems. These can be swapped with more appropriate dates e.g. Customer Setup Date could be swapped from 1970-01-01 to the date of the first sales transaction.
Other fields to check are those with negative entries that cannot be true, such as ages or elapsed time.
Summary
During UAT there may be other inconsistencies that might be discovered and dealt with – they generally fall into one of the types described in this blog or could also be mapping issues with the wrong fields from different tables.
All these investigations can lead to corrections, either in the original database or in the reformatting scripts. They help to improve data quality so that decision-making is based on the most reliable data possible.
