If you've ever used a tool such as Power BI...
…and struggled to extract meaningful reports from your databases, you’ll know the frustration of having a system that’s well designed for some tasks but not flexible enough to do everything you need.
You know your system contains the data you want. All you want to do is extract it. Why does it have to be so hard?
It usually comes down to the way your database was designed.
Typical database design is optimised to avoid duplication and maximise the integrity of data. This is a great way to produce a compact, high-integrity database that operates quickly and reliably.
The problem is that this setup doesn’t lend itself well to reporting. And while that might not be an issue in the early stages of running your database, it will become one as your system matures.
As with most businesses, you’re probably collecting more data all the time, and that presents an ever-increasing challenge: if your systems aren’t geared for efficient reporting now (and they probably aren’t, because they were optimised with a different aim in mind) then how will they cope when you try to report on 10× or 100× the data in the future?
The short answer is that you won’t be able to unless you think about reshaping the data in a way that supports better reporting.
When we help customers on reporting projects, we “unpeel” their databases to reveal views of the underlying data.
But popular reporting tools such as Power BI aren’t built to do that unpeeling. When faced with a complex database with a highly normalised form, Power BI and similar tools are likely to struggle.
As with most things in IT, when a task – such as extracting data for reporting – seems particularly hard, that’s often a sign that you’re using the wrong tool.
That’s not to say that there’s anything wrong with Power BI as such, only that it’s not built to do the heavy lifting it would need to do to generate good reports from highly normalised databases.
While the design of a database might aim at avoiding data duplication (as that’s inefficient for operating purposes), such duplication doesn’t matter from a reporting perspective.
Our approach to better reporting
So, how do we handle things when the goals of the original database design aren’t aligned with what’s optimal for reporting? The answer is that we make reporting easier by reshaping the data.
This means that we put in place data pipelines to extract the data and then use a modern scalable data warehouse to support data reporting and analytics to make sense of that data.
In an ideal world, we would do this as early as possible. In practice, we’re often brought in when an application has already been built and our customer wants to start running reports.
As we mentioned above, reporting usually isn’t one of the prime considerations when first creating apps and databases. The customers who do think about such requirements early on tend to have fewer headaches with reporting as their systems mature.
If you’re struggling to extract the data from your systems to run the reports you need for your business, we can help.