SharePoint360 Business Intelligence: SSIS Event Log Business Intelligence

1
SharePoint360 Business Intelligence SSIS Event
Log Business Intelligence

• SharePoint360
• 710 B Street Suite 1601
• San Diego, CA 92104
• 877.819.6360
• www.sharepoint360.com

2
Agenda

1. Background
2. The Problem
3. The Solution
4. Discussion

3
Background

• ETL jobs run nightly to load the Data Mart
• BI required for LOB operations
• Outsourced processing dependent on BI
• Delays had financial implications

4
The problem

• Data Mart ETL did not delete staging data
• Some jobs took over 8 hours to complete
• Onshore operations were affected
• Offshore processing was delayed
• High potential for financial impact to customers

5
The solution

• Objectives
• Identify troublemaker ETL jobs
• Make performance improvements to ETL jobs
• Track changes over time to validate ROI
• Monitor jobs daily for any failures
• How
• A Data Mart built on logs from the ETL jobs
  (SSIS)
• OLAP cube for slicing and dicing log data (SSAS)
• Detailed reporting into ETL jobs (SSRS)
• Dashboard(s) for immediate check on ETL jobs
  (PPS)

6
References

• http//ssisbi.codeplex.com Source for project
• http//bensullins.com Shameless self promotion
  ?
• http//sharepoint360.com MOSS/BI/CRM hosting
  and consulting