In the business world, every organization is looking to get an edge on their competition. One way a company can gain an advantage is to improve its ETL processes for better BI.
Chuck Ezell, performance, tuning, and optimization services with Datavail, covered how key ways to improve ETL processes in a recent DBTA webcast.
Ezell discussed OLTP vs. OLAP, explaining that OLTP is best for relational database transactions with an emphasis on fast query and relational data integrity. OLAP is best for structured redundant data with an emphasis on the ability to aggregate and analyze. “Most often, both OLTP and OLAP systems exist within all ETLs but the tuning of each is different,” stated Ezell.
When going through the ETL process it is important to remember that there are many different layers and components that could potentially be hindering the most efficient ETL process possible. Ezell covered the potential issues that could be slowing the ETL process, including network latency, disk, too much in-memory, poor code, limited RAM, IOP and CPU bottlenecks, file system or cache fragmentation, and limited space.
To deal with many of these tuning issues Ezell performs a five-step check process.
- Check the SQL code
- Statistics
- Indexing and table fragmentation
- Conflicting session or processes during ETL
- Offload or replicate data for better isolation
Ezell calls this the “5 S Approach.” He notes that there are two main goals that everyone wants from their ETLs: speed and consistency. “We know we want them fast but we also want them consistent so that they are dependable,” stated Ezell.
To view a replay of this webcast on demand, go here.