Implementing Near Real-Time Data Warehouse

1. Implementing Near Real-Time Data Warehouse Sutha Thiru sutha@suthathiru.com @suthathiru http://www.beeii.com Commercial in Confidence

2. Agenda • Real-Time DW • Scenario • Data Load • Custom Components • Demo • Real Time Stuff • RT Challenges & Solutions • Best Practices

3. Real Time • What is Real-Time data warehousing? • Why do we need it?

4. Scenario • Global brand • Well known in the UK • Number of customers in Retail Parks • Provides cameras and counting devices • Multi Currency / Language • Multi Time-Zone • Calendar specific to a client • REAL TIME (near)

5. Data Load • Cameras sending files every few minutes • 1000s of devices • Unstructured files • File is unique to a device • Need to load them quickly using SSIS • Data available on dashboard for the controllers • Decisions made before next set of files are produced by the device

6. Custom Components • System Config Reader • Event Handler • XMLify • TRIM All • SHA1 / MD5 Checksum • Inferred Dimension

7. Data Load • Demo

8. Real Time Stuff • Stream Insight • Change Data Capture (CDC) • Service Broker • AbInitio Continuous Flow • Java Messaging Service (JMS) • Others

9. Real-Time Data Warehousing Challenges & Solutions • Enabling Real-Time ETL • Near Real-Time ETL • Trickle Feed • Real-Time Data Cache • Model Real-Time Fact Table • Direct Feed • Real-Time Partition • View

10. Real-Time Data Warehousing Challenges & Solutions • Real-Time Alerting • True Real-Time data monitoring & triggering • Minute cycle schedule • Real-Time Threshold • Reporting • Simplify Real-Time Reporting • Increase Hardware power • Separate Real-Time data cache • OLAP vs. OLTP

11. Best Practices • Implement Correct Database Partitions • Implement ROLAP Partitions (OWN RISK) • Implement Correct Merging Strategy • Handle Early Arriving Facts Efficiently • Use Stream-Insight

12. Thank You

13. Coming up… • #SQLBITS