Solving Data Problems: One Retailer’s Story
By Niklas Bjorkman, firstname.lastname@example.org
In retail we all struggle with data regardless if it is related to chain store management, logistic solutions, business intelligence at the head office, or ERP.
Data is our friend, but when we hit performance walls or run out of storage space, data is no longer our most appreciated friend. Once upon a time the database options were very limited but today the landscape is completely different with literally hundreds of databases to choose from. This is fantastic, but it can also be a catastrophe if the wrong database is selected and thousands of development hours are spent to then have to change the solution.
Let’s take a look at how the largest single retail superstore in the Nordic region — Gekas — made its new database selection.
The Gekas story
Gekas actually started as an outlet in the woods in the middle of nowhere. But the Gekas store became a popular excursion for shoppers with chartered busses lined up every day. Within a few years the shop had an average of 15,000 unique visitors per day and the amount of data grew, and grew fast. Its popularity is proven by the fact that the average trip customers make is 143 miles, one way. The longest queue of shoppers to get into the shop is marked to 0.9 miles!
Its popularity created a problem of an increasing amount of data and the need for extreme data throughput to process all the information, which began the hunt for a solution that could get it to work. To get the point of sales to be able to handle the pressure, each and every POS got a separate database installation. Everything was written locally and then sent to a main server in due time. The cost for this in hardware and licenses was prohibitive. The reports and financial figures simply had to wait, not just until everything was sent to this main database but also until the nightly batch-processing was completed. The processing started directly after closing hours at 8 p.m. and was barely completed when the business opened again the next day. Any failure during the nightly batches could make the processing difficult to catch up again.
On top of this, Gekas wanted to get better control over their inventory. There is a truckload of products delivered every 10 minutes, 24/7 and all year round to the central warehouse. The ordering and replenishment was controlled by a few skilled purchasers that had everything written manually in a notebook. This was not a good strategy. So a decision was made to digitize the central warehouse and integrate all the data to the same database as the point-of-sales.
Not only would this result in more data to the central database, but also several new points of inputted data such as ordering, delivery tracking, arrival of goods including every stock movement. It became obvious that traditional solutions could not provide all these features at those extreme levels of throughput and volume.
Gekas turned to a retail software vendor that used a NoSQL (or rather NewSQL) database as foundation for their software. (NoSQL databases are great at storing and analyzing huge amounts of data, but are not good for handling operational and secure transactions. NewSQL databases provide the best performance for operational, true secure data.)
The first task was to implement a solution for the central stock and warehouse followed by replacement of the point of sales. The workstations are always connected to a single central database with no replication or batch processing needed. In case of a network failure the POS will continue to use a local database that will automatically synchronize when connectivity is restored.
Once the solution was in place the management could see all financial figures, all receipts and every stock move in real time at their head office. Now, the same tasks completed before in the earlier nightly batch processing could be done in a few minutes. No more costly extra database licenses and no need for extreme hardware in every workstation.
Operational and analytical data
What are the options when you start searching for a solution for your data problems? We need to, when it comes to large volumes, consider if the data is operational or if it is analytical. Is it possible to select a database, include all your data and then trust that the database will support all your needs? The short answer is no. To get the best performance for operational, true secure data you need to look into the NewSQL spectrum of databases. These databases will support your operational performance data needs, basic reports and analytical processing.
But when the analytical business intelligence (BI) functionalities expand and you need to be able to join massive data on the fly, create drill down selections, and collect years of historical data then you need to check out a proper data mining database for big data. There is a selection of these in the NoSQL market that focus on analytical processing. These databases, however, cannot be used for your operational data, since they lack performance in creating new information and updating data while keeping the integrity of the data. Therefore, in extreme cases today we do need two different types of databases: one for operational data and one for analytical needs.
All these abbreviations
It is easy to be confused by all the new concepts defining a database type. What are the differences and what does it all mean? Traditional relational databases we’ve already discussed. They have tables with columns and rows with data that relate to each other using primary and foreign keys.
A NoSQL database does not mean “No”- SQL. It is an abbreviation for “Not Only” - SQL to indicate that we can expect more than from traditional databases. The way data is stored is different from traditional databases and is great for analyzing huge amounts of data. They are not good for handling operational and secure transactions.
This is where NewSQL databases fit in. They store data in a modern architecture, often in memory. I recommend these kinds of databases for operational data in all modern retail applications.
Is traditional database the safe option?
It always feels safe in a boardroom to suggest going with a traditional database vendor, but I urge you to look into the new database technologies. Look at what your business needs in terms of performance and functionality and select the technology thereafter that can address the business problems of today and not last year’s problems!
Niklas Bjorkman is VP of Technology of Starcounter, a database development company. He has more than 17 years of experience developing advanced business systems and working with object oriented programming related to persistent storage. He can be contacted at email@example.com. To learn more, visit Starcounter.com.