In today’s fast-paced business environment, organizations often require real-time or near-real-time movement of data from their operational applications to analytics and reporting environments. This allows them to make informed decisions based on the most up-to-date information. In this article, we will explore different approaches to achieving real-time updates in SQL Server.
Approaches to Real-Time Updates
Based on our discussions, we have identified three possible approaches to solving the real-time update problem:
- Replicate Tables using SQL Server Replication and use reporting tools directly against the operational database.
- Utilize a real-time update process (such as Informatica/SQL Server) to incrementally update tables and use reporting tools directly against the operational database.
- Use an OLAP or Memory-based approach, eliminating the need for additional relational tables and SQL-based query tools.
Vendor Solutions
There are several vendors that offer solutions for real-time updates in SQL Server. Some of the notable ones include:
- Informatica via Informatica PowerExchange: This tool provides change data capture technology for applications without time date stamps of update switches.
- QlikView: A memory-based reporting and querying tool that requires various QlikView servers for processing and publishing.
- SQL Server 2005 Analysis Services: Microsoft’s DBMS and OLAP solution that offers features like snapshot isolation and proactive caching for real-time updates.
Near-Real Time Analytics
In addition to real-time updates, there are also approaches to achieving near-real time analytics. These approaches include:
- Operational Analysis: This approach involves loading the source data into compressed proprietary data stores without transformations. While this provides analytical capabilities, it has limitations such as no trending, history, or complex business rules.
- Traditional Business Intelligence: This approach involves transforming the source data to properly analyze specific business metrics and their associated hierarchies. While this approach requires a more complex extraction and loading process, it offers benefits such as insulating the reporting layer from changes in the source data.
Recommendations
Based on our analysis, we recommend conducting a Proof of Concept (POC) using two scenarios: one using QlikView and the other using SQL Server 2005 Analysis Services. As part of the POC, we suggest validating existing business requirements, defining the metadata-driven extraction architecture, and evaluating the gaps each recommended scenario/tool has against the extraction architecture requirements.
Scalability is an important factor to consider. QlikView claims to load 4 million rows per minute, while SQL Server 2005 Analysis Services can handle 120,000 rows per second or 7 million rows per hour on a Dual-core Xeon.
It is also crucial to involve the Source DB team in the extraction architecture and work towards direct access to their tables in a replicated environment. Each tool has its own requirements for extracting necessary attributes and rows, and may require manual adjustments for field names and joins.
Conclusion
Real-time updates in SQL Server can be achieved through various approaches and vendor solutions. It is important to carefully evaluate the requirements, scalability, and team structure to ensure a successful implementation. By leveraging the right tools and techniques, organizations can gain valuable insights from their data in real-time, enabling them to make informed decisions and stay ahead in today’s competitive business landscape.
References:
- Microsoft SQL Server 2005 Analysis Services Performance Guide
- QlikView Documentation
Remember, as Yoda said, “Do, or do not. There is no try.”