Business Intelligence Solution for Leading Beverage Distributor Improves Process Time
Project at a Glance
RDA assisted our client in troubleshooting a business critical data application. The resulting corrective actions led to drastically improved process time and provided our client with the ability to access more timely data throughout the day and grow the data store in the future.
About Our Client
RDA’s client is a marketer, producer, and distributor of products for a global beverage company.
Background
Our client was experiencing an OLAP/cube issue in a sales analysis business critical application utilizing SQL Server. The daily cube rebuild process had increased from an average of 8-9 hours to upwards of 18 hours after a reorganization of the data.
Solution Detail
An RDA Subject Matter Expert (SME) was enlisted to examine the ETL process and the structure of the OLAP cube and determined that the bottleneck was in accessing the data from the underlying relational data store during cube reprocessing.
Our SME implemented a partition scheme on the base tables to more closely align the table structure with the OLAP partitions. This change allowed each partition to process independently from its own data source, thereby decreasing the time necessary for the data load task.
This change resulted in a reduction of the nightly cube processes from 18 hours down to 4 and the mid-day refresh process from 5 hours down to 20 minutes on average.
Our client is planning to upgrade to SQL Server 2005 to take advantage of the enhancements to its Analysis Services component, such as real-time access to the data. In addition, SQL Server 2005 native support for partitioning will simplify the design and reduce maintenance efforts.
Benefits
As a result of this project, our client can now load the cube nightly with fresh data with plenty of room to grow as more data is added to the system in the future.
Reducing the mid-day refresh time will also allow for more frequent daily updates, giving the users access to timely changes throughout the day.
Challenges
The size of the OLAP cubes, which together contain more than 500 million rows of data, combined with the need to rebuild the entire OLAP cube set every night, presented a challenge to meet the target time window.
Partitioning the source data and setting up efficient indexing on the data allowed SQL Server to extract the data, transform and clean it, and process it into the cube well within the given 7-hour nightly window.