Next Generation Reporting Architectures: Part One
The five stages of data preparation and what must change to build the next generation of reporting architectures
Historically, business intelligence (BI) products have focused on glitz, or how well they present data. As a result, BI tools were primarily selected based on their presentation capabilities. Even the Gartner Magic Quadrant focused on established presentation features in the most popular products and included user adoption as part of their scoring, meaning they’re actually rating yesterday’s technology. The result is that BI leaders provide excellent presentations on a very small percentage of a company’s data.
So what’s needed for tomorrow’s technology?
The future of BI
The future of BI is not providing more shades of red to show you that sales are down, but to find out why. And the reason may not actually be a problem in the sales department. A decrease in sales could be due to interdepartmental disconnects, such as the finance department reducing profit in inventory (PII) by not shipping sufficient inventory. Uncovering these root causes requires merging data from multiple departments. The future of BI provides business analysts with fingertip access to much more data, as well as the ability to instantly generate data reports.
Today, tools to support this are readily available. Column store databases and column-oriented cloud data warehouses (CDWs) — like Snowflake — support fast retrievals of large amounts of data. A few BI tools provide the capability to connect to these data warehouses without having to download the data internally. Business analysts can then examine specific rows, columns, and measures using a graphical user interface (GUI) or Google-like search bar instead of SQL, producing new reports in seconds instead of the two months it can take for IT development.
Five stages of data preparation
The diagram below shows the same five stages of data preparation for both traditional and next generation architectures. The stages are:
- ERP systems, public databases, and web scraping — Raw data is collected from enterprise resource planning (ERP) systems and other applications.
- Data lakes and data staging — Raw data is copied to a data lake or other staging area.
- Reporting data model — Raw data is transformed into a useable reporting model, which may include a data lake house.
- SQL snippets or ETL tools — Subsets of the transformed data are extracted to produce reports.
- Dashboards — Reports are presented to business users. Some reports may support limited interactivity, like changing a filter value.
The shift from traditional to next generation architectures take place on the right side of the diagram. Newer tools and technologies give business users better access to the data and allow skillful business analysts to instantly create their own reports. This is compared to the traditional model in which custom requirements are gathered and custom extract programs are created by IT, even for reports that may not be around for very long.
Important changes in architecture
Change 1: The sophistication of the reporting model
In the traditional architecture, a reporting model is built to solve a limited set of use cases and it’s not uncommon for a different reporting model to be created for each use case. However, this simply isn’t scalable.
To support enterprise business analytics, next generation architectures require that reporting models be more robust, holistic, and readily understandable by business users while also providing fast performance, even at the terabyte level. The technology needed to support this was identified back in 1996 but is being used in a limited (or poor) capacity today.
The technology is enterprise star schemas, where multiple fact tables — or business transactions — are connected to a set of shared dimensions, or business objects. In most traditional architectures, enterprise star schemas are never built. Instead, reporting is supported by lots of smaller and remarkably limited subsets of the data. These smaller data models are faster to build in the short term, but don’t lead to an enterprise solution — just thousands of subject-specific data models.
Change 2: The demarcation of responsibility between IT and the business
IT creates star schemas based on their understanding of the data while business users want star schemas that reflect their understanding of the business. These are very different perspectives. The ongoing problem is that IT teams don’t “speak business” and business people don’t “speak IT.” This great divide is the primary reason why star schemas aren’t more widely used and is often the biggest barrier to building next generation architectures.
This same divide has existed for 30 years in the creation of financial planning and consolidation applications, more formally referred to as Enterprise Performance Management (EPM) applications. Many EPM applications use star schemas internally — a sophisticated choice back in the 1990s.
For EPM, the divide was conquered by finance people learning databases, not by IT people learning finance. My observation is that the overwhelming majority of EPM developers were finance people who learned database technology. Only a few were IT people who were willing to learn a little finance. Ask almost any IT person how their finance application separates credits from debits and you can see their eyes slowly roll back in their head. It isn’t practical to expect business users to learn how to build BI star schemas. EPM is very focused while BI is much wider in scope.
The next generation reporting architecture is a quantum step forward. It will allow business analysts to mix data from different departments and build their own reports at the speed of thought. The remaining articles in the series discuss some roadblocks preventing us from taking this step and actions that can be taken to overcome these roadblocks:
- Why Use Cases Take You in the Wrong Direction
- Why Do IT People Hate Star Schemas — Reasons 1 and 2 (coming soon)
- Why Do IT People Hate Star Schemas — Reasons 3 and 4 (coming soon)
- Standard Reporting Models (coming soon)
Slalom is a global consulting firm that helps people and organizations dream bigger, move faster, and build better tomorrows for all. Learn more and reach out today.
