130 likes | 316 Views
Implementing Near Real-Time Data Warehouse. Sutha Thiru sutha@suthathiru.com @suthathiru http:// www.beeii.com. Agenda. Real-Time DW Scenario Data Load Custom Components Demo Real Time Stuff RT Challenges & Solutions Best Practices. Real Time.
E N D
Implementing Near Real-Time Data Warehouse Sutha Thiru sutha@suthathiru.com @suthathiru http://www.beeii.com Commercial in Confidence
Agenda • Real-Time DW • Scenario • Data Load • Custom Components • Demo • Real Time Stuff • RT Challenges & Solutions • Best Practices
Real Time • What is Real-Time data warehousing? • Why do we need it?
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)
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
Custom Components • System Config Reader • Event Handler • XMLify • TRIM All • SHA1 / MD5 Checksum • Inferred Dimension
Data Load • Demo
Real Time Stuff • Stream Insight • Change Data Capture (CDC) • Service Broker • AbInitio Continuous Flow • Java Messaging Service (JMS) • Others
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
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
Best Practices • Implement Correct Database Partitions • Implement ROLAP Partitions (OWN RISK) • Implement Correct Merging Strategy • Handle Early Arriving Facts Efficiently • Use Stream-Insight
Coming up… • #SQLBITS