American Tower case study: building a flexible and affordable enterprise data warehouse with expressor American Tower is a leading owner and operator of communications sites for the wireless and broadcast industries, with more than 27,000 towers and 1,400 employees in the US, Mexico, Brazil and India. In October 2008, the company launched an ambitious, multi-year enterprise data warehouse program to consolidate more than 50 single-purpose data marts and improve the accuracy and timeliness of its business reporting. American Tower initially chose Microsoft SQL Server Integration Services (SSIS) as the ETL tool for this project, but is replacing SSIS with the expressor semantic data integration systemtm to improve its data loading and complex transformation performance. challenges and goals As a global organization that expanded through acquisition, American Tower’s reporting challenge was complicated by a number of different currencies, languages, time zones and business processes. Its unique business model, operating as a real estate company leasing space on its towers, required the company to heavily customize its Oracle E-Business and .Net applications to accommodate as many as 100 tables per application. And its outdated reporting environment was not very well documented, often “pulling” the same data multiple times for different reports, which resulted in very long data extracts. American Tower’s goals for its enterprise data warehouse program were to integrate all corporate data so it could be reported on consistently from one place, increase the frequency of data warehouse updates to provide 1 expressortm—redefining data integration CASE STUDY www.expressor-software.com 2 expressortm—redefining data integration www.expressor-software.com up-to-date BI reporting, and to deploy a new and better reporting tool that empowers the business to more easily create reports. choosing tools American Tower’s IT team conducted a rigorous vendor selection process, choosing SQL Server 2005 rather than Oracle for its enterprise data warehouse based on an evaluation of its current and anticipated data volumes as well as the existing reporting team’s familiarity with the Microsoft product. It selected IBM Cognos 8 as its reporting tool based on feedback from the company’s business users. The IT team also investigated ETL solutions from vendors such as Informatica, but their high cost prohibited serious consideration. American Tower ultimately chose Microsoft SQL Server Integration Services in part because they were familiar with it, and because the company’s existing SQL Server licenses meant acquiring SSIS did not require any extra expenditure. A technical evaluation identified several shortcomings with SSIS, but given its price, the IT team believed it could “live with them.” SSIS shortcomings However, these shortcomings quickly affected development. SSIS’ inability to perform bulk updates without landing the data in a table first and then running a SQL statement on that table, for example, resulted in serious performance issues. The company also had performance bottlenecks with SSIS when pulling data from its Oracle database. It reached a point where these data extracts expanded beyond the specified outage windows, forcing the team to make design decisions around the speed of SSIS. Finally, American Tower’s IT team found scripting in SSIS awkward. They tried working in .Net and the SSIS expression language, but neither seemed particularly well integrated into SSIS. 3 expressortm—redefining data integration www.expressor-software.com opportunity for expressor American Tower became aware of the expressor semantic data integration system in the middle of 2009 and installed the product for a proof of concept. Focusing first on the performance of Oracle extracts, American Tower demonstrated that expressor was 8-24 times faster than SSIS, using one to four expressor channels. The company also found expressor’s scripting language – expressor datascript – to be very powerful, providing many capabilities as well as improved ease-of-use not available in SSIS. American Tower especially liked expressor’s ability to perform bulk updates and the fact that it was based on an open metadata repository, since the company had focused on creating metadata to improve debugging and generate better performance statistics. The “clincher” for American Tower was expressor’s low cost of adoption – which made it a very easy decision to acquire, since it solved many of the company’s problems with SSIS. experience with expressor Plugging expressor into American Tower’s ETL process provided benefits in all three stages. In the extract stage, expressor’s improvement in performance pulling data from the company’s Oracle database was significant. This is also where the benefits of expressor’s semantic rationalization capabilities were exposed – the company very much liked the fact that expressor allowed them to create consistent field names for ETL developers. Different parts of American Tower use cellular tower numbers in different ways and store them in different formats – without leading zeroes as integers or with leading zeroes as character fields, for example. Both numbers are valid, but deciding which one gets used is a critical question the ETL developer cannot easily answer. American Tower used expressor’s semantic rationalization to create two different, clearly named fields: “ShortTowerNumber” and “LongTowerNumber,” which eliminated any potential for confusion. This is a simple example, but American Tower found the same type of problem repeated in many different business processes. “American Tower has been very happy with expressor, in particular with the capabilities of expressor datascript, which has given us the ability to do many things that we either couldn’t do or had a hard time doing in SSIS. I encourage you to evaluate expressor and see if it makes sense for your organization.” - Geoff Speare, manager of business intelligence and data warehousing group, American Tower expressor software corporation 1 new england executive park burlington, ma 01803 usa +1 (781) 505-4190 tel +1 (781) 505-4197 fax www.expressor-software.com In the middle step for transform, American Tower found expressor datascript much more powerful and easier to use than SSIS for developing business logic and processing. And in the final load step, expressor’s bulk update capability made it possible for American Tower to accommodate changing data dimensions and types by setting the old rows to be not current and to end-date them. This process required a workaround in SSIS but expressor handled it directly. expressor’s semantic dictionary is also helping American Tower rationalize the variety of terms used throughout the business to describe date-specific actions such as an application submission or survey completion into a set of shared business terms. Before expressor, these terminology differences were making their way into American Tower’s data warehouse and into its ETL code – creating inefficiencies and room for error. And expressor is ready to grow with American Tower. If factors such as increasing data volumes or geographic expansion ever create performance issues, the company can easily scale up with expressor by simply adding additional “channels” of processing power – without changing the underlying applications. lessons learned For many SQL Server customers, SSIS is “free,” but American Tower found that “you get what you pay for.” With several of the functionality limitations American Tower experienced – the company didn’t even know what it was missing because it had not used a comprehensive ETL tool such as the expressor semantic data integration system before. The company also learned that expressor complements SSIS and can easily coexist in the same environment. For process and flow control, for example, the company continues to use a SQL Server job agent and invokes expressor from SSIS to perform the required ETL. Finally, American Tower’s experience demonstrates that expressor is a smart, fast and affordable solution for SSIS users who need to improve the performance of their ETL and data integration applications. © 2010 expressor software corporation, all rights reserved. The following are trademarks of expressor software corporation: expressor, expressor semantic data integration system, smart semantics, intelligent load and go, expressor datascript and redefining data integration. All other trademarks are properties of their respective owners.