What do you do when you’re one of the global leaders in the beauty industry and your growing data environment is getting out of control? You set up a call with DB Best and start to speak with one of our dedicated Project Managers. We began with just a single developer working with this customer but very soon it grew to include an additional 13. After 5 years, what started as a data environment analysis has grown to a full business intelligence (BI) development service, operational data management, and 24/7 support. In this blog post, we will outline the data infrastructure that we walked into, our assessment, and how we’ve improved the life and business intelligence metrics for our customer.
Overview of the original customer’s system
When our customer came to DB Best, their data environment included a massive SQL Server-based Enterprise Data Warehouse. As with companies as large as this, these reports were coming from the customer’s multiple branches around the world. Moreover, they had a wide variety of different reporting services, including 6 huge OLAP cubes. These cubes suffered from multiple performance issues and led to poor reporting frequency. For example, it took almost 15 hours to provide the users with a daily report and when your daily report takes almost all day to generate, you have a problem. The ripple effect from the over-extended data warehouse meant that our customer was unable to make informed business decisions due to poor reporting. In the ever-changing world of global cosmetics, there was a high-risk that they would lose their leading position in the market to more informed competitors.
In addition to that, the customer’s reporting system lacked flexibility. Particularly, they were seeking mobile access to key reports. Accessing information on the go is very essential for any business today. If your business lacks the ability to access real-time data anywhere anytime, you need to fix this issue as soon as possible. So, our customer was looking for a full-fledged BI solution to take advantage of the crucial business information on the go.
The DB Best process
Our customer knew that they were asking for a lot with their sky-high goals, and DB Best delivered. From the moment we started, even up until this very day, DB Best has been the reliable BI development partner that our customer was searching for. But how did we manage such a successful project?
First of all, we decided to follow the Sprint cycles in the development process. As our customer already adopted the JIRA workflow, we went the same way. This proved to be an effective and efficient way, which allows for easy and simple tracking of our progress.
As we got started, we immediately understood the monumentous task ahead of us. While some of the customer’s initial infrastructure was salvageable, it was crucial to take care of the future-state architecture design of their data infrastructure. Without addressing the foundational issues of their data meant that anything we did was doomed to failure.
Our team took care of the following processes:
- ETL
- Cubes optimization
- Reports development
In addition to that, we set up the real-time monitoring systems. For example, now we can check that all ETL processes are executed on time. And if that doesn’t happen, the system sends the notification emails. According to the customer’s SLA, these and other problems are fixed within 2 – 6 hours.
Configuring the ETL processes
In order to get the reports on time, it’s imperative to take care of the data on all stages. So, we started with the ETL processes (which stands for extract, transform, and load your data to the storage).
Most businesses these days have data coming in from many different sources. You need to transform all of this information into the required format in order to properly analyze the data.
So, we had to standardize and organize all the customer’s data from multiple sources, including core data (sales), peripheral data (customers), and external data (promotions).
We set up a parallel processing of the raw data. This resulted in significantly improved daily reporting.
Our optimization of the ETL processes resulted in a 2X reduction of their execution time.
Also, we tracked all occurring issues and addressed them at the core. Currently, the system operates without issues most of the time, as we eliminated them. In addition to all of this, we also set up the monitoring system to automatically identify delayed jobs and take appropriate actions. So, now we can address the issue when it occurs, before end users identify any delay in reporting.
Optimizing OLAP cubes
For this stage, we leveraged several optimization strategies and best practices. They include removing the unused dimensions’ hierarchies, deleting useless links in the databases, adding data slices, and removing the unused indexes dimensions. Also, we set up an additional monitoring system kills heavy user queries before bringing the system down.
In addition to that, we developed new cubes from scratch. At the end of the day, we managed to speed up cubes processing up to 3 times and user queries up to 100 times.
Working with customer’s OLAP cubes, we found out that SQL Server doesn’t support data synchronization for really big cubes. When we say really big, we mean the size around 1 terabyte or hundreds of gigabytes. So, processing the data for these cubes, we had to run all the operations twice for the primary and secondary replicas.
Historical data is critical as you can’t fully understand your business’ progress unless you monitor its changes over time. So, keeping track of historical data is one of the secrets of a successful strategy.
Developing reports
After a comprehensive treatment of the customer’s ETL processes and OLAP cubes, the reports delivery speed significantly increased. However, we did not settle for that and searched for additional room for improvement. For example, we forbade querying EDW directly, as it was overloaded. We managed to significantly improve daily reporting performance by reducing the database pressure.
Then we insisted on abandoning the usage of third party reporting tools. To replace them, our team leveraged Cognos to develop and optimize most of the critical reports used across organization wide. Currently, we maintain over a thousand of Cognos reports.
Finally, we provided the customer with the critical flash sales reports with hourly refreshing data. Consider hundreds of stores across the globe to emphasize the value of this achievement.
Also, we created new reports leveraging AnalyticsIQ and SAP Roambi. The later allows for consuming data from mobile devices.
Moreover, we updated the customer’s documentation on their existing and newly developed reporting solutions. We also performed the knowledge transfer to their team to simplify support and fail-over procedures.
Customer’s benefits
Currently, we continue working with this customer, providing them with operational data management services. Particularly, we handle over 800 service now incidents every year, make about a hundred of immediate production fix deployments. Over the last year, we decreased the total number of trouble tickets by 50%. Of course, this result highlights our active development activities, which include influencing on the architecture of the customer’s IT infrastructure.
In addition to that, we provide the customer with ongoing development services. Our long-term engagement includes multiple BI development tasks. That’s because you can’t build the business intelligence solution at once. It’s a long-lasting, ongoing process, which corresponds to the selected business intelligence strategy. Actually, we successfully accomplished the main goal, which is to provide the needed data for business analysis on time. But still we have a lot of space for improvement!
We decreased the daily report delivery time by 2X. Also, we deliver the updates on the sales reports on an hourly basis. These reports are instantly distributed to the company top management. This helps them prepare for the holiday sales and make appropriate business decisions on time.
Consider your business intelligence strategy of success
Only after a comprehensive analysis, you can understand what drives revenue for your business. Then you can build an accurate and effective business intelligence strategy that helps take strategic business decisions wisely.
Be sure to contact DB Best to talk about your business intelligence strategy. We can find the right optimization or modernization path of your data infrastructure of any size to meet your business needs and goals.
