Clean Data for BI and BI for Clean Data

Email | Print

If you are responsible for delivering dashboards, reports, metrics, KPIs, or customer insights, you want to be sure you are using data that is accurate, timely, and relevant.  Bad data can result in misguided insights and bad decisions.  There are myriad tools on the market to help ensure your data is complete and accurate—data hygiene tools, data validation tools, duplicate detection tools, data standardization tools, data management tools—and almost as many services to help you in your quest—services to append data, list brokers, services to clean and validate data.  Or if you have the money in your budget, you can forget trying to make sense of all of these tools and services and hire a data consultant.

However, for most SMBs the cost and complexity of these tools can push using them out of the realm of reality.  But a good business intelligence application can help ensure you have clean data.  Think about it—if you have to audit 1,000,000 transactions, you couldn’t possibly look at every transaction.  Instead, you can look at the data for particular attributes of those transactions.  This is exactly where business intelligence excels.

For instance, if you wanted to look at sales data, you might graph the revenue per sale against margin per sale for all of the sales in the month.  Some outliers may pop out immediately.  Then take a closer look at discounts compared to targeted discounts or average price per unit against unit price for all of the sales and you are likely to see other strange data points.  This is because when you aggregate data and look at it graphically, the anomalies appear immediately.  When you investigate those anomalies, you could find that the data is valid but simply undesirable, or you could find errors in the data that need correcting.

With BIO business intelligence, you can look at a field like product unit price to see if there are values outside of the actual unit prices or look at zip codes compared to a purchased table to see if you’ve entered invalid codes.  Records without values in certain fields present another problem.  Some algorithms treat missing data as a zero and some don’t count that record at all.  That could cause problems in calculating values like averages and ratios.  In BIO, data without values in a particular field appear in a category called Missing Member.  How important is it to have a terms code assigned to every vendor or a territory assigned to every customer?  Errors in these fields will be immediately identifiable if you analyze all vendor records by terms code or all customers by territory, respectively.

BIO has some customers that are doing pretty sophisticated error detection with the clever use of BI.  For instance, a not-for-profit group is using BIO to check donor allocations.  Once they enter allocations based on individual donations, they look at the aggregate allocations to determine if they make sense.  If the total allocations do not match the amounts required, they can easily go back and correct the allocations on an individual basis.  Another customer uses BIO to provide feedback about ad placements and impressions.  By aggregating the data, they can look at the number of impressions by website, ad size, or advertiser and quickly spot operational data problems with start date or number of impressions.  A project-based customer looks at aggregate time spent on projects by employee class to identify progress billing errors.

So if you need clean data (and who doesn’t), you might want to start by looking at BIO business intelligence, an affordable, state-of-the-art business intelligence platform that provides dashboarding, reporting, and analytics using advanced visualization.  As a bonus, you can use the product to check the very data you are using in the product.

By Sandi Forman of BIO Analytics, Corp., a Business Intelligence (BI) Microsoft Dynamics ISV

BIO Analytics Corp. provides business intelligence software and services for Microsoft Dynamics. Visit our website to learn more and to view demos.

Related Posts

Ask This Expert a Question or Leave a Comment




Get New Posts:

Distribution Software Logo