Lamar Advertising using WhereScape to Quickly Develop Targeted Business Solutions with Agility
Founded in 1902, Lamar Advertising Company (Nasdaq: LAMR) is one of the largest outdoor advertising companies in North America, with displays across the United States, Canada and Puerto Rico. Lamar offers advertisers a variety of billboard interstate logo and transit advertising formats, helping both local businesses and national brands reach broad audiences every day. In addition to its more traditional out-of-home inventory, Lamar is proud to offer its customers the largest network of digital billboards in the United States. Lamar’s portfolio includes:
- 315,000+ advertising displays
- 144,000+ billboard faces
- 2,000+ digital billboard displays
- 40,000+ transit displays
- 130,000+ interstate logo signs
Lamar is now able to provide much better reporting
and analytics to our stakeholders. New insights are also enabling us to identify new business opportunities.
WhereScape talked with Jude Robert, Director of IT Operations, about Lamar’s experiences in re-architecting and managing a company critical Microsoft SQL Server data warehouse with WhereScape RED.
Q: What IT challenges was Lamar Advertising facing?
Lamar Advertising had an existing data warehouse written by former members of the IT group. While functional, the aging warehouse was starting to experience performance and reliability issues as the load on the system increased. Scalability was an issue as Lamar was having trouble getting the job done in a timely way. The system was also hard to document, had data integrity issues and was becoming quite a management challenge. It wasn’t that users were unhappy with their final results—the system worked—it just didn’t always perform very well. In addition, we also had a project underway to upgrade the software on the billboards, which would result in an even richer file being sent back for analysis, further stressing the already taxed system.
Q: Can you describe the Lamar Digital Display Application?
The point solution that really started testing the limits of our previous data warehouse architecture is Lamar’s Digital Display (LDD) application. The LDD solution captures and tracks the play log files
of Lamar’s more than 2,000 digital billboards. Every time an image changes on a billboard, another row and table needs to be inserted into the database containing information such as what image was displayed, the time it was shown and for what duration. Each billboard houses a computer that sends a file once each day to the corporate IT system with its play log file. We import play log files produced off each digital board once each day representing millions of records. In total, the LDD system contains 25 million rows of raw data that is aggregated together. That information helps generate reports that show our customers they got what they contracted for.
We used WhereScape RED to reengineer the existing data warehouse and have also used it to create several
new data marts to address specific business needs.
Q: What are your experiences with WhereScape?
We used WhereScape RED to reengineer the existing data warehouse and have also used it to create several new data marts to address specific business needs. Reengineering the warehouse with WhereScape RED was deemed easier than going back to fix the existing system and processes, which weren’t very well documented and written by developers no longer with the company. With WhereScape RED, we created a process to load all the files—of both new and old formats—into the SQL Server database. A detail fact table was created to hold 90 days of play logs and an aggregate fact table was built to provide all-history analytics.
WhereScape RED generates stored procedures—thus changes are easily made. In our original data warehouse design, stored procedures, triggers, message queues were hand-coded and not optimally written. Going back and changing them was becoming a major issue. WhereScape RED brings a level of consistency to the data warehouse design process. Now that we have our WhereScape-architected processes in place, it is significantly easier to go back and make changes.
The fact WhereScape RED is self-documenting is huge—we can trace a particular object back, easily locating where a particular field came from, for example. WhereScape RED’s change management capability is a key feature for us. We can now quickly go back and make changes as well as pull new data sets out of our ODS or marts for sales, support, the LDD system or any other number of other systems.
WhereScape RED’s automated features and integration
with SQL Server enables our developers to do the job of many.
Q: What Size Team Worked on the Project?
We had a two person team, with one mainly working on the BI side of the implementation. WhereScape RED’s automated features and integration with SQL Server enables our developers to do the job of many. RED enables us to accomplish everything we need, including the polling of data, loading of data, any transforms we need to do, creating dimensions, fact tables and even cubes and aggregate tables. All that and more can be done in WhereScape RED, making our job a lot easier.
Q: What are the results to date?
Lamar has a four terabyte SQL Server data warehouse that is growing rapidly. The old system we had in place could take up to eight hours to process the Lamar Digital Display data. It now takes 1.5 hours since our new process and architecture developed with WhereScape was put in place. That performance gain is even more impressive when you factor in we have significantly more data to process than we did previously. We had a couple of hundred visuals when the system was launched; we now have more than 2,000 visual displays we support today.
That is just one process, or target solution, that Lamar has created with WhereScape RED that on a daily basis supply critical data to Company business units for reporting and analysis. In total, Lamar has deployed a dozen point solutions such as an A/P portal, a proof of performance mart, the Lamar Training Library and an account executive portal, with more under development. Currently, our executives look at static reports. With Tableau, we will be incorporating interactive graphics and visuals to highlight outliers and other critical information.
We have standardized on WhereScape RED as our development platform, meaning all it takes is for someone new to be trained on RED and they can plug into the project at any point in time. No more hunting around for code or depending on documentation that is probably outdated. The system now performs much better and reliability is much improved.
The old system we had in place could take up to eight hours to process the Lamar Digital Display data. It now takes 1.5 hours since our new process and architecture developed with WhereScape was put in place.
Lamar is using WhereScape RED to build target data marts, extracts and executive dashboards to leverage Tableau’s presentation and analytical layer for reporting and analysis. We are also using WhereScape RED to aggregate tables to build cubes for use with Microsoft Excel with pivot tables and Tableau. We plan to roll out Tableau to our 800 account executives so they can consume the data in these new targeted solutions.
Lamar is now able to provide much better reporting and analytics to our stakeholders. New insights are also enabling us to identify new business opportunities.