Approach for building a business intelligence platform for retail companies
Let us introduce our new series of blog posts where you can discover how to build a system and a business intelligence platform that consists of streaming, real time analysis, predictive component, batch processing, and reporting. We will dive into our approach to a new challenge where sales data plays the leading part. After winning Croatia osiguranje & Bird data challenge, we started looking for another challenge that we could learn from and have some fun. We targeted challenges with larger datasets from the real world and preferably from industries like supply chain, retail, insurance, and finance. Applicability and meaningfulness were important factors – the challenge has to have some purpose and the potential to turn into a product. After some research we’ve found the M5 data challenge that consists of two related competitions: Estimate the unit sales (we will focus on the first competition) Estimate the uncertainty of sales The competition is very popular, in this edition there were more than 7000 competitors. In the series of blog posts, we will talk about our spin-off project based on this data competition through technology and business topics. The original competition and spin-off The task of the original competition is to estimate the unit sales for 3049 products in 10 Walmart stores based on the 5+ years of sales data. The test set contains 28 days, that is 10 x 3049 x 28 = 853720 predictions. We have modified the task to get the most out of the dataset. The first thing that came into our minds was the streaming of sales events. Each sale can be represented by an event in Kafka. The dataset is static (a CSV file) so we’ll need some kind of generator process that will be used by a Kafka producer. Sales data is grouped by date, nevertheless, we can simulate the real world by applying appropriate distribution of sales to data and generating events w.r.t. that distribution. Stream of sales events can be analyzed and visualized in real-time by Apache Spark or KStream applications and visualization tools. There is also room for real-time notifications and alerts, for instance, if the sales of an item are high we can recommend restocking. The original dataset does not contain warehouse data, but you could generate it and implement warehouse management features. We have some experience in data generation in the insurance domain, so we know that data generation is tricky and requires a lot of time to do properly. That’s why this is on our nice-to-have list. Streamed data is persisted in a data warehouse where additional batch analysis, reporting, and predictions (the original task!) can be done. Here we can use Apache Spark, Apache Airflow, Python ML libraries, and visualization tools. You see how many additional tasks you can get from this dataset. It’s the exact thing that we were looking for! Data overview The purpose of this section is not to analyze data in detail but to give you a quick overview of data so you can follow the rest of the post easily. Data is organized into files: calendar.csv – contains information about the dates on which the products are sold; sales_train_evaluation.csv – contains the historical daily unit sales data per product and store for 1941 days. It contains the same data as sales_train_validation.csv with additional 28 days used for testing purposes; sell_prices.csv – contains information about the price of the products sold per store and date. The figure shows the hierarchical organization of the dataset which is divided into levels: state – store – category – department – item. The calendar contains a list of all 1941 dates and information about special events like sports and cultural events, national holidays, and religious holidays which can have an effect on sales. This is important because some holidays can change dates so forecasting algorithms can not rely just on date information. For instance, Christmas trees and decorations will sell a few weeks before the 25th of December each year, but chocolate Easter bunnies will have peak sales in different weeks or even months depending on the year.Sales data contains daily sales of each item for all stores. The figure below shows the histogram of sales on a store level. The highest sales numbers happen in the CA_3 store, and the same store has the largest sales variance. We can see that stores in Texas and Wisconsin have lower sales variance and that those stores hit comparable sales numbers. Data can be analyzed on a more detailed level, for example per category. The following figure shows the sales of food products. Each line represents a year, and you clearly see the seasonality in the data. Sell prices data contains weekly average prices for each product and store. Prices for the same item vary depending on the store, which makes sense as some states have higher standards than others, and some items have higher shipping costs to certain areas. Here is the example for the first item in the dataset whose price also varies between states, but also within a state (look at the WI_3 store). Store Price CA_1 1276.00 CA_2 1256.84 CA_3 1265.10 CA_4 1277.32 TX_1 1270.10 TX_2 1256.84 TX_3 1265.48 WI_1 1282.60 WI_2 1280.72 WI_3 1248.46 Price comparison for HOBBIES_1_001 item For some items we have detected outliers which we marked as bad data. For instance, there are products for which the price drops to $0.01. It is too cheap for any item, and even if an item of a $0.01 value exists there is another thing – for most of them the sales numbers also drop to zero so we conclude that represents an out of stock situation. However, there are cases when sales numbers do not drop to zero which confuses us a bit, that could be bad data caused by errors in the process. Architecture overview In this section we’ll briefly explain components, data flow, and main tasks. We divided work into three main components: Datasources preparation Streaming and stream processing Batch
Are You Concerned About Starting a Data Science Project?
If you are reading this, there is a high probability that you have a problem and you think that data can be a solution for it. Another option is that the data is your problem — you have lots of it but it is not clean, comes from many sources, and is unorganized and unreliable. The first one relates to Data Science projects and the second one to Data Engineering projects. Some projects combine both fields. Data Engineering projects are more exact than Data Science projects, that’s why you may have the following concerns: You don’t see the value data can bring to your business Not achieving wanted results Too long development associated with the high cost What can you do and how can we help you to overcome those concerns? You don’t see the value data can bring to your business Today, when everyone talks about data and its benefits, very few companies make use of it. With information surrounding us at every point, we sometimes fail to recognize its potential. Often, as a company, you might feel overwhelmed by it and it might seem like clutter. Having an optimal view of data could help you visualize future potentials and opportunities that it brings. On the other hand, a lot of information comes from within and having your business generate data is just a starting point. Our job is to help you turn it into value. Here are some examples of the value you can get from it. If you are not yet utilizing data why not start with simple reports or more sophisticated interactive dashboards that offer you to view and understand your data. Data visualization plays an important role in discovering trends and when paired with your domain knowledge, and business intuition it helps you to make informed decisions based on hard data. Having a visual representation of your data allows you to convey information easier, but having insights and metrics play a leading role in forming your strategic decisions. In case you already have a reporting tool why not enrich those reports with predictions. While reports help you to understand your data it is still up to your gut feeling to estimate what the future holds. Instead of just knowing the statistics for the past, with predictions you get a view into the future. You will be more confident in making decisions if they are backed up by mathematical models and machine learning predictions. Recognition of either customer and client patterns, or patterns and behaviour inside your company, presents an opportunity to make more precise future plans, set clear objectives, and predict trends or threats. Not achieving wanted results Setting high goals is good, but we also have to be realistic. Start with the main issue that’s bothering you and leave other “nice to haves” aside at the beginning. Applying the “SMART” principle when setting goals is a good start and that way you could create a great basis for what you want to achieve. You have to think about those objectives and if the data you want to use can accomplish them or if a data scientist or engineer could help you in creating a solution that will cover them. A clear strategy will help you form a sustainable and accurate solution that will serve you in the future. Often, the main concern of data science projects is if they’ll generate desired results. Companies are worried that if they invest time, money, and energy into something, that ROI will not be at the level they want. What if you set unrealistic goals, or if the data you wanted to implement into the solution won’t so easily bring the wanted value. When talking about desired results it is good, but not required, to have some benchmark solution that is used for tracking the performance. It can be a solution that you already use to solve the problem but you want something better, or it can be some simple but not optimal way of solving the issue. There are ways of maximizing and optimizing what you already have. Or a whole new solution could be created to deliver precise and meaningful insights and results. Don’t worry, you don’t need to have it implemented, we can implement it for you. Too long development associated with the high cost You may be concerned about the long development time and associated costs before you get some value from the solution. Getting from point A to point B takes time and other resources so it’s a valid concern if you have issues you need fixing as soon as possible. If you invest in something, surely you want the results to be speedy but optimal at the same time. You might be hesitant at first to delve into such projects since it’s either an unfamiliar or unsure territory for you, but having solid and trustworthy support will solve that. We address those concerns by starting with an MVP (prototype) that solves the “core issue” in short sprints and fast feedback loops. It is probably not the optimal, or fully automated, nor fully integrated solution but it serves as an outline of the final product and when compared to a benchmark solution shows possibilities and realistic expectations. When you are satisfied with the MVP performance only then the full development process can start. Further development has the goal of improving the prototype’s performance and integrating it into your process. All of the concerns for starting a data science project are valid and something to take into consideration when embarking on such a venture. You have to weigh in what are your goals and what do you want to invest in the process. A reliable partner is what you’ll need to make this journey smoother and in the end, effective. Do you have some other concerns? Take a look at our Data Science project development process and feel free to contact us for more information.
Data Integration Tools vs. Data Processing Frameworks/Libraries/Engines
Introduction For any data engineer, data pipelines are very important. Choosing how to develop one is integral for the person building it since it’s going to determine the complexity of the process. When developing a data pipeline you can use two approaches. One of them is to use a programming language and a data processing framework or libraries like Apache Spark and Pandas. Another one is to use data integration tools that offer out-of-the-box components for data loading and processing. But how do you decide which one to use? How to determine which fits you better? Both approaches have their benefits and drawbacks, it depends on the situation what is best for you. For instance, if you do not expect your data pipeline to be huge and don’t have any experience with such tools, it is probably better to stick with a programming language you already know. If you expect your data pipeline to have many components, data sources, data sinks, and a lot of processing then data integration tools could help you to do that effectively. Also, you should consider how much data is there, do you need to utilize cluster computing platforms such as Apache Spark or you could get away with one server where you installed your tool. In our practice, we have used data integration tools more often than coding the processes. It saved us a lot of time and helped the team to have easier communication and understanding. Data Integration Tools There are many data integration tools on the market, some of them are open source and some require expensive licences. Each one has its advantages and disadvantages, and some are a better choice than others. The idea is not to describe and compare all of them but to mention a few and describe one that you can try for free. Deciding on one is determined by many factors and it’s usually your needs and goals that drive the selection. Some of the popular data integration tools are Pentaho Data Integration, AWS Glue, Hevo, Talend, Informatica PowerCenter, and Oracle Data Integrator (ODI). Pentaho Data Integration (PDI), nowadays known as Lumada Data integrator, is an ETL tool that contains built-in capabilities for extracting data from different sources, transformations, and loading data to target systems. It offers a free community and paid enterprise version that comes with the support. You can install it locally, or on a server hosted in a cloud or on-premise. Data can be extracted from almost all popular databases, input streams, different file formats from local or remote servers, files from cloud and clustering storages, input streams, and APIs. It supports built-in simple and complex transformations for cleaning data, combining data, processing data, and adding metadata. Those transformations are executed by Pentaho’s engine so you should ensure enough resources on your server when executing it on large portions of data. If built-in transformations are not flexible enough, you can write custom Python, JavaScript, Bash, or SQL scripts to achieve your goals. Also, you can execute external scripts and programs for data processing. Pentaho supports loading data to a wide variety of popular databases, files on local or remote servers, cloud and cluster storages, or even data streams. Although Pentaho is an ETL tool, we also use it as an ELT (Extract, Load, Transform) tool. You can achieve that by extracting and loading directly to a data warehouse which should be fast and efficient. The number of used built-in transformations is minimized. That way transformations that define business logic are done in the end with SQL statements which are executed in database engines that are optimized for data processing, especially if you use column-oriented databases like Vertica and Redshift. We could say that in the case of ELT, Pentaho serves as a data loading and workflow management tool (control flow, job order, transformation order). Now, why would you use ETL tools? ETL looks like a simple process, you just have three steps and you are done. But in reality, you have many problems to solve even in the first phase of extracting data from the source. For instance, data structure from input files can change over time, you can have incomplete and wrong data that needs to be filled or be marked for manual checks and not cause the whole process to stop. To sort out those kinds of issues, you’ll have to exchange a lot of emails or calls with your client which takes time. In situations like that, it is good to have a tool in which you can do all those changes without too much code refactoring and messing up the parts that work. The ultimate goal is to deliver a project on time, and utilizing those tools can help you in that. Benefits of data integration tools Drawbacks of data integration tools Code approach Some companies prefer to build their data pipelines by using popular programming languages, frameworks, and libraries. In most cases, the pipeline consists of Python, R, Scala scripts, Apache Spark jobs, MapReduce jobs, Apache Storm, or some other parallel processing frameworks. Scripts and jobs are usually orchestrated by Apache Airflow, Luigi, or other workflow management platforms. We have used Apache Spark with Apache Airflow to support the ETL process in some of our projects where we gained experience in utilizing these solutions. The important thing to remember is that you should choose the most appropriate technology. If there is no need for cluster computing then stick to Python and Pandas. If you need to implement a data pipeline and already have developers on board, they will be familiar with this approach as they know programming languages, documentation, testing, release process, and so on. There is nothing new for them in this approach, besides learning new technologies like Apache Spark. In many cases, it is easier to educate your developers than to find ones that are experienced with specialized data integration tools or have the will to learn one. The main benefit of this approach is performance