Author: Kellyn J. Mitchell, Solution Architect, Prime TSR
I’ve run into this scenario more often than not:
At the end-of-month finance meeting, your team is reviewing the financials from the previous month. John from Finance presents his revenue number, and Jessica interjects because she has a much different number for the same reported month.
Who is correct? Proving who is correct means building a data warehouse that hosts a verifiable single source of truth.
The reality is that both Jessica and John reported correctly, except they got their numbers from two different systems. One was their internal finance system, and the other was an excel spreadsheet with robust formulas that is used for operational purposes. So, in a sense, neither of them are wrong.
They just haven’t decided which number is the single number they are going to trust and take action on. From my experience, here are common issues when it comes to analyzing data:
- You don’t have agreement on the business definition of metrics. For example, what does average sales per day REALLY mean?
- How does one compare February (28 days) to March (31 days)?
- How does one compare February (28 days) and February on a leap year (29 days)?
- You’re getting different results on metrics because you’re pulling information from multiple sources of data.
- You have a lot of data but no central repository to build reports from. A pivot table can only go so far.
The end goal with building a proper Data Warehouse solution is simple: having actionable insights to drive better business decisions.
How do you get to those insights? You might have an analytics/BI system in place already but are still not able to answer this question. Where do you start?
Do you have an Analytics problem or a Data Warehouse problem?
Let’s use a hospital as a real-life example. The hospital administration is looking to track the number of positive and negative experiences with their outpatient surgeries. They will use these insights to make better operational and marketing decisions.
To get that answer, we have to ask deeper questions:
- What constitutes a successful surgery?
- How long was the intake process?
- How many visits did it take to successfully diagnose the patient?
- How long did it take to schedule surgery from the patient’s first call?
- How many interactions did the patient have with the hospital after the surgery?
- How can this experience be improved?
In most cases, companies actually have answers to all of these questions stored in their systems. The problem is that the answer to each question lies in a different, unconnected system.
Let’s say you wanted insights on the following question: “For successful surgeries in which the patient was discharged in under 24 hours, how long did it take to intake the patient, diagnose them, and schedule the surgery from start to finish?”
To get this answer, you would have to look up the information in three different, disconnected systems. And since each system has its own data governance model, it’s difficult to validate that the data in each system is verifiably correct.
In this case, the data is the problem, not the analytics. The analytics system can’t pull in the right information because it doesn’t have access to it in the right context. So, the hospital administration can not, with confidence, answer their original question.
This is when a Data Warehouse solution comes into play. If you have a proper Data Warehouse solution and analytics, these questions can be answered with a single query, with complete confidence that the data is correct.
Now what? Start by asking yourself these 5 questions
Before deciding what to do next, gather an inventory of the following:
- Do you have a standard business model with standard insights or do you have unique insights uncommon in your industry?
- Do you have a data-focused technical team that can help you architect a DW solution?
- Are you going to invest in a technical team or is the standard operation to partner with a vendor for technical talent?
- Does all of your data reside in one system or a series of connected systems that you can easily access?
- Can your current reports answer the right questions with a few tweaks or do they need to be re-structured?
So, the natural next question is, “How do I build my Data Warehouse properly?” You have two options: build it in-house with your team or hire a vendor to partner with. Let us examine the pros and cons of both options.
Should you build your Data Warehouse solution in-house or partner with a vendor?
||Partner with firm
|You have a similar business model as other businesses and only need standardized reports. For example: units sold per day.
|You have unique business metrics. For example: units sold per day when the temperature is below 55 degrees.
|You have a full-time team of ETL developers, architects, and technical leadership.
|You plan on investing in a technical team long-term to maintain the solution.
|The time to build reports is longer than normal.
|You’re starting to see large variances of data accuracy across all of your reporting.
|There isn’t a clear path to self-service for your business users. Report-builders just aren’t cutting it anymore.
The real question is this: Do you trust your data?
If you go through that chart and end up with “DO NOT TRUST THIS DATA,” don’t rely on your data being accurate without building a first-rate data warehousing solution with proper governance.
In summary, if you have a standard business model and the right technical team you will continue to invest in, build it in-house.
If you have unique reporting needs, struggle with reporting on accurate data, and no intention on investing in a technical team long-term, then partnering with a vendor is your best option.