Welcia Yakkyoku is the core company of Welcia Holdings, and is contending for the top spot in Japan’s drug store industry. Welcia Holdings has about 2,000 retail stores overall throughout Japan. Welcia Holdings has established an unique business model that involves dispensing pharmacies, counseling, late-night business hours, and nursing care. The company’s wide range of products and services pertaining to regionally based medical care, lifestyles, and nursing care support its strengths. Welcia is expanding its network at a pace of over 100 to 150 retail stores every year, and has begun building new systems to support mission-critical tasks. At the same time, Welcia created a new information analysis platform that uses point-of-sale (POS) data from approximately 2,000 retail stores to strategically make decisions and plan store layouts. Nihon Unisys provided full support for this project, and used MartSolution (a support tool for building business intelligence (BI)) and Vertica (a high-speed data analysis platform) to create a high-speed BI environment that is easy to use and meets Welcia’s requirements.
Industry
Location
Product
Welcia is contending for the top spot in the drug store industry in a harsh competitive environment with the aim of achieving 1 trillion yen in sales. Active growth strategies represented by new retail store expansions and mergers and acquisitions have driven the company to make innovations in its systems for mission-critical tasks.
Welcia judged that it was important to strengthen its information analysis platform at the same time, and began this project. The competition between supermarkets and convenience stores and Welcia’s retail stores is also becoming more severe, and it is becoming increasingly important to utilize information effectively. Atsushi Sakaki, Manager of the Systems Development Department in the Information Systems Division, told us his impressions.
“We want our 2,500 users from our headquarters and retail stores to strategically use the POS data from approximately 2,000 retail stores to generate policies that increase Welcia’s competitive advantage. One reason we want to do this is so that we can make decisions fast at the management level. Another reason is so that we can continue to improve our product-line-ups and floor plans at retail stores to meet customers’ needs. Our objective is to create a structure in an optimal format that can provide very fresh information when we want it,” Sasaki said.
At Welcia, a work support system that had been developed in-house was being used to summarize the sales records and manage the product masters. However, due to the increase in data and increased number of users associated with business growth, it became difficult to look up information in an timely manner. Kenichi Kato, Assistant Manager of the Systems Development Department in the Information Systems Division, told us his impressions.
“With the work support system that had been developed in-house, it took until noon the following day to summarize the sales. We could not see the previous day’s data before the stores opened, and this was a problem. An even bigger problem was that the response was slow and getting worse. If we tried to extract a wide range of actual sales data, it often took a few hours before we could see it. We needed to drastically revise the information analysis platform.”
They had repeatedly tuned the MySQL database environment. However, Kato said, “When we improved the pre-processing to speed up the response, the overall batch processing time got longer, and it was a vicious cycle.”
Nihon Unisys worked to solve the problems Welcia was facing. Nihon Unisys proposed to use MartSolution (a flexible support tool for building BI) and Vertica (a high-speed data analysis platform) to create an ultra-high-speed information analysis environment that meets diverse search requirements.
MartSolution, which is developed and provided by Nihon Unisys, is a BI tool that can flexibly meet the unique requirements of user companies. MartSolution can build a data warehouse/ BI environment with a semi-customized feel in a short period of time, and uses a license system that doesn’t depend on the number of users or CPUs. The larger the scale of the system, the more the cost performance improves.
Vertica is a high-speed data analysis platform that is specialized for analysis processing, and has gained a reputation for reliability with global companies that use data-driven management, such as Uber and Bank of America. Vertica’s best feature is its extremely strong analysis performance, and it demonstrates its power through multiprocessing, where many users can access large volumes of data at the same time.
The key points of the proposal by Nihon Unisys are as follows.
Sakaki said, “Nihon Unisys proposed Amazon Web Services (AWS) as a flexible and expandable platform. They investigated BI tools and analysis databases based on the assumption that the new information analysis platform would be built on AWS.
MartSolution was evaluated as being simple and easy to handle. Welcia doesn’t have an analysis specialist, so we removed tools that required complicated operations from our list of candidates. We emphasized the ability to be able to intuitively use the system, regardless of whether it would be used at one of our divisions or a retail store.”
Mayu Miyaji in the Logistics Business Services Division at Nihon Unisys was involved in this proposal and told us her impressions.
Miyaji said, “End users made a request to us, saying that the work of customizing screens and forms was taking up a lot of time. If we could create an environment in which users themselves could freely analyze information without difficult operations, then it would be possible to reduce the number of such requests to the IT Department. MartSolution makes it easy to freely analyze complicated conditions for extracting information, so I thought it would be the optimal solution.”
The performance of the back-end analysis database determines the speed of the response and the ease of operation of MartSolution on the front end. Vertica can process Welcia’s 11 TB of data at ultra-high speeds, and fully demonstrates its actual capability as a platform for analyzing big data.
Vertica radically improved performance issues. Kato didn’t hide his surprise, saying, “The batch processing that used to take a maximum of 20 hours was shortened to 3 hours, and now we can see the previous day’s sales data at 7 am. Now it takes less time to get an understanding of information such as top-selling products or sales by category, and we can use this to determine a store layout that day. The average search time has also been shortened to approximately 2 seconds. The speed really has increased dramatically.”
A new function has been incorporated into the new information analysis platform that tracks the sales trends for each day in units of hours.
“We created a structure to summarize POS data every hour and deliver preliminary estimates of sales for each retail store. The day before the consumption tax rate was revised, the Sales Division and the Product Division were able to focus on sales trends that were changing every moment and take specific actions. It became possible for us to use information that we didn’t have a way to find out before we introduced this system, so the departments that use the information can make decisions and policies more quickly now,” Sakaki said.
Vertica has proven its extremely strong performance for analyzing and processing large-scale data. General relational databases read all of the data that is the target of the search, but Vertica only reads the data in the necessary columns. This is why column-type databases are superior. In addition, with Vertica, users can use various technologies that make it more efficient to look up data at higher speeds, such as its unique function called “Projection.”
Kato said, “When we selected Vertica, we also verified the performance compared to Redshift, which was native to AWS. The results clearly showed that Vertica was less likely to deteriorate in performance when users accessed the system at the same time. Vertica’s feature of being ‘Strong for multiprocessing’ is an extremely important point for us, because we have 2,500 users.”
The verification of Vertica’s and Redshift’s performance had very meaningful results. Although the difference in performance wasn’t that big for single summarization processing, when 30-50 users tried to summarize sales volumes and monetary amounts for all stores, the results showed that Vertica could accomplish this in a few minutes, but it took more than an hour for Redshift to complete the processing.
Kato said, “Actual on-site users have different search requirements. Some searches put a heavy burden on the system and some searches put a light burden on the system. With Vertica, the CPU and memory usage rates can be controlled in a very detailed way at the user level, so the fact that specific search requirements did not significantly affect other processing was also a key evaluation point.”
Welcia’s rapid growth is quite amazing, but Miyaji spoke confidently, saying, “The new information analysis platform is creating the ability to respond to the speed and scale of business growth.
The processing capability of MartSolution and Vertica can quickly and easily be expanded by adding nodes to the respective AWS. Vertica also has high performance without tuning, but if its “Projection” function is used, specific searches can be done at even higher speeds. The new information analysis platform provides an infrastructure that evolves to fit the business requirements,” Miyaji said.
Although everyone is aware of the importance of utilizing data, there aren’t many companies that are achieving that in an ideal manner. The new information analysis platform should be a major source of strength in Welcia’s drive to compete for the top spot in the industry. MartSolution and Vertica produced the following business results.
Kato spoke of things he hoped to achieve, saying, “In the future, if we look into using AI predictions to create better store layouts, we will consider Vertica as a candidate for our AI platform.”
The use of Vertica as a platform for utilizing machine learning is expanding, from data storage to preparing predictive models, evaluating models, and implementation.
Yuko Tsuchii at Nihon Unisys said, “I’d like to actively make proposals that make use of our experience and know-how in the logistics industry, such as new sections of information for analysis and analysis of data that hasn’t been used before.”
In closing, Sakaki said, “I think that Nihon Unisys made an optimal proposal that correctly understood our business targets and the issues we were trying to solve. The Welcia Group will probably use the new information analysis platform as a platform that will support our business growth over the next 10 years. MartSolution and Vertica are excellent products, and we appreciate the thorough knowledge that Nihon Unisys has in the logistics industry. We hope Nihon Unisys will continue to support us in the future.”
We created a structure to summarize POS data every hour and deliver preliminary estimates of sales for each retail store. The departments that use the information can make decisions and policies more quickly now.
When we selected Vertica, we also verified the performance compared to Redshift, which was native to AWS. The results clearly showed that Vertica was less likely to deteriorate in performance when users accessed the system at the same time.
