You’re a data management professional. How do you make sure your decisions are based on complete data?

Step # 1 is defining your requirements. Determine what you are attempting to accomplish. Are there KPIs you need to determine if they have been met? What perspective will this data be looked at from? The same data/report can be looked at from Senior Leadership, Management, the team doing the work, or client facing. As a result, you would produce the same report in 4 difference ways based on who your audience is. The features you offer, slicers, charts, PivotTables, Dashboard, drilldown to data, etc... also depends on the audience and the metrics you are using.

First you gather, handle, or utilize data, make sure you know exactly what data you need. What are you trying to achieve, ask, or solve with this data? Where will you get it, what form will it be in, and what standards should it meet? How will you check if it's good enough in terms of quality, how much there is, and how accurate it is? By stating your data needs clearly from the beginning, you can prevent wasting time and resources on data that doesn't help you.

After you've identified your data sources, it's essential to validate them. This involves confirming that the data sources are dependable, credible, and authoritative. You also need to ensure that they are current, pertinent, and compatible with your data needs. You can employ various methods to validate your data sources, including examining the metadata, documentation, and credentials of the data providers, comparing the data with other sources, and testing it for errors and gaps.

In my experience, ensuring decisions are based on complete data involves thorough validation of data sources. We meticulously reviewed the metadata, documentation, and credentials of providers, ensuring trustworthiness. Comparing data with multiple sources and rigorous testing helped identify errors and ensure relevance. This validation process guarantees that decisions are made with confidence, backed by comprehensive and reliable data.

Data validation is ALWAYS a requirement. You need to know that your data is reliable. You must consider that regardless of the source, at some point there was manual data entry involved. And we all know how easy it is to "fat finger" something and make an error in the data entry process. Use rules, and conditional formatting to define any out of the ordinary data. Some things just need to be reviewed by a human to ensure it is valid data. Create a list of your validation processes, and have it as one of the sheets in your workbook. Include formulas/CF that confirm the validity of specific data sets. You can create a validation sheet, which becomes part of the report/template and will always validate. Plus it is only "created" once.

I am partial to using Power Query for this part of the process. I can create a data map that is part of my process, and add to it as needed. Power Query is also great at the cleansing part of data, which includes summarizing, de-duping and so much more. Plus with PQ when you have updated data files you just select the new file, or add it to the folder (historical data). This is beneficial for multiple data files (months, regions, vendors, etc...) to summarize (Append) the data for review and analysis.