How can you design a data warehouse to support real-time reporting?

When starting a project and you hear the requirement "real-time reporting", clarify the understanding of "real-time" with the stakeholders first. For some, switching from a monthly data refresh to a daily refresh is already "real-time", for others a delay of a few minutes or less is the critical requirement. And clarify why and what for it is needed. Some stakeholders ask for "real-time" just because they heard the marketing claim or because it might technically be possible. But often it comes along with so much more costs and side-effects without real value that can be leveraged, so that you can quickly abandon demanding real-time requirements.

What is Real-Time Reporting? Definition: Real-time reporting involves providing up-to-the-minute insights and analytics on business operations based on the most recent data. Immediate Data Availability: Users can access and analyse data as soon as it is generated or updated, enabling swift decision-making.

Real-time reporting involves instantly creating and delivering reports or insights as data is updated, ensuring continuous extraction, processing, and presentation. This immediate access to the most current information empowers organizations to react promptly to changes, make timely decisions, and gain a competitive edge through up-to-the-minute insights.

A data warehouse, also known as an enterprise data warehouse (EDW), consolidates data from various sources into a unified, centralized repository to facilitate data analysis, data mining, as well as applications in artificial intelligence (AI) and machine learning. It empowers organizations to conduct robust analytics on massive volumes of historical data, reaching into the petabytes, surpassing the capabilities of traditional databases. While data warehousing systems have been integral to business intelligence (BI) solutions for more than thirty years, they have recently evolved due to the advent of novel data formats and hosting techniques.

What is a Data Warehouse? Definition: A data warehouse is a centralized repository that integrates data from various sources, transforms it, and stores it in a structured format for efficient querying and reporting. Historical Data: Traditional data warehouses focus on historical data, but for real-time reporting, the emphasis shifts to near-instantaneous data availability.

When constructing the architecture, it's crucial to account for the Real-time Data Warehouse (RTDW), a distinct read-only database primarily intended for reporting, often referred to as the reporting database. Direct access to the Real-time Data Warehouse isn't feasible; instead, it's accessible solely through reporting solutions integrated with it. Additionally, identifying an appropriate ETL (Extract, Transform, Load) tool capable of facilitating real-time data replication in the data warehouse is essential. The modeling approach should be optimized to efficiently process data for consumption in reporting tools.

How to Design a Data Warehouse for Real-Time Reporting? Architectural Considerations: Choose an architecture that supports real-time data processing. Consider architectures like Lambda or Kappa that accommodate both batch and stream processing. Scalability: Design for scalability to handle increasing data volumes and meet growing reporting demands. Flexibility: Ensure flexibility in data modelling to adapt to changing reporting requirements over time.

Data Ingestion Techniques: Stream Processing: Implement stream processing techniques to ingest and process data in real-time. Technologies like Apache Kafka or AWS Kinesis are commonly used for streaming. Change Data Capture (CDC): Utilise CDC mechanisms to identify and capture changes in the source data.

Be aware that real-time reporting might not just be about adding something next to your existing systems. You might might first of all need to enable your source systems to serve these use cases, functionality wise and performance wise. Turning on change data capture, adding triggers, or using service brokers or event queues in your source system adds load and delays to existing data insert processes. So you need to test the performance impact on your source system and potentially invest in tuning them in the first place.

Data Modeling Techniques: Schema Design: Design a schema that accommodates both historical and real-time data. Consider a hybrid schema that incorporates both dimensions and facts relevant to real-time reporting. Temporal Tables: Use temporal tables to manage and query data over time, enabling analysis of historical and current states.

You need to design a data warehouse schema that can support the analysis and reporting requirements of your users. For example, you can use a dimensional model with star or snowflake schemas to enable fast and flexible queries on the data. Denormalizing your data model can improve query performance as it reduces the number of joins required. Real-time reporting often benefits from having data stored in a way that minimizes the need for complex joins during queries. Use a columnar database design where data is stored column-wise rather than row-wise.This optimizes analytics queries as it allows for efficient retrieval of specific columns, which is common in reporting scenarios.

Data Processing Techniques: Event-Driven Architecture: Implement an event-driven architecture to process events as they occur, triggering real-time updates to the data warehouse. Microservices: Use microservices to build modular and independently deployable components that handle specific data processing tasks.

Increasing the frequency of SQL or MDX queries is just classical data warehousing on steroids. Building real-time dashboards can benefit from using streaming or push-notification architectures, which allow for less latency will reducing load compared with high frequency pull queries at the same time. Leveraging these technologies is one of the major differences between classic and real-time reporting.

Many of the concepts mentioned here are not "real-time" reporting specific, like star schema or data vault data modelling, OLAP, MDX and SQL. The real-time specific approaches you want to look for are streaming or micro-batch processing, which is basically traditional batch processing in high intervals, which, other than large batch processing, cannot be put into a time frame with low load on the source systems. Your real-time architecture can even benefit from bypassing the traditional DWH architecture for the real-time reporting requirements, and integrate the same data with a delay into the traditional DWH and reporting, as described in paragraph 6 with the lambda speed layer.