We use cookies and other similar technologies (Cookies) to enhance your experience and to provide you with relevant content and ads. By using our website, you are agreeing to the use of Cookies. You can change your settings at any time. Cookie Policy.

RFID Database Management Systems Architecture for Retailers

Originally published January 30, 2007

Large retailers, such as the largest – Wal-Mart – are implementing RFID on a grand scale while smaller retailers are struggling with the value proposition, but are likely to be able to derive some momentum from the enormous changes the Wal-Mart mandate has effected on the product manufacturing industry. That mandate says that suppliers to Wal-Mart must equip their pallets with RFID tags. The deadline keeps moving, but it is now believed the deadline is sometime in 2007. However, the initial charter of the mandate has been extended from the top 100 suppliers to include all suppliers. Quite possibly, the top 100 suppliers have already complied.

Obviously, all products are not tagged individually. At current tag prices, it would be difficult to establish a value proposition for low-margin goods. However, much of the physical infrastructure cost has already been expended, or is planned to be expended, for all stores. RFID is already installed in hundreds of Wal-Mart stores. These infrastructure costs include the in-store readers, which were needed once the first product in the store was tagged. The major cost to extending the tags to lower-margin goods is the data storage for the reads. While storage cost is not a prime consideration for most of us doing information management, it surely is for RFID applications due to the high-volume potential.

By considering the number of items on the store shelves of a retailer (say 150,000), you need to arrive at the frequency of item read, which is a factor of the number of readers placed in the store and how well-placed those readers are. Finally, toss in how many bytes are stored for each read plus DBMS page overhead, and that amounts to at least 2 GB DBMS storage per day per store – more for grocers, less for clothiers. A 1,000 store chain would generate about 2 terabytes PER DAY! With its 6,400+ stores, Wal-Mart’s total would be much higher.

There are two primary uses of database management in RFID support. The first is at the store level to tackle the operational issues of theft, shrinkage, temperature sensitivity of certain products, replenishment, etc. From a headquarters perspective, the data for these intra-store issues is housed locally in non-persistent store-level staging databases, from which operational triggers can send appropriate and timely messages to store staff. For example, if an $800 laptop passes through the exit door reader without having gone through the checkout reader, that would be a call to action. If the temperature of the milk drops 5 degrees below its ideal, that would be a call to action.

This database can stage data to the corporate data warehouse (before the store DBMS ages the data out). The corporate data warehouse is, of course, the second major use of DBMS in RFID data flow. A headquarters is enabled to conduct much deeper analysis of trends across all data. And while the previously mentioned operational benefits will provide cost-clearing ROI to get a company involved in RFID, the longer term, larger benefits will eventually accrue from smart business decisions made by looking at product (and customer) movement patterns and learning to appropriately intervene.

There is a design decision to be made here about data to store in the persistent corporate data warehouse – all the detail or summaries only? While the detail data is interesting for the operational issues previously noted, it’s only going to be selectively (i.e., on a spot basis) interesting to headquarters and, therefore, stored in the corporate data warehouse. Headquarters will avail themselves of RFID data to conduct affinity analysis, customer profiling, visit optimization, customer servicing, market basket and all manner of applications that retailers have been doing and would like to do better or have been unable to get off the ground.

The schema structures I have added to databases, both operationally and in the warehouses, include an RFID transaction table supported by dimensions of (at minimum) tagged_item and reader_location. Reader_location demands a robust store_layout dimension so that it can map attribute values such as aisle number, shelf number and cashier number as well as a categorization of the usual type of transaction occurring at that location (item selection, checkout, replenishment, etc.). Tagged_item is interesting because you will have to decide if you are going to use the EPC standard or not. The EPC standard is huge, but what a retailer would use can be scaled back to include just that retailer’s product base.

Depending on the RFID system you use and how you configure it, it could be an issue whether other readers could read your tags. You may care or you may not. The transaction table would contain the interesting measures of the juxtaposition of the product and the reader. Here, the timestamp is not an afterthought measure added at the end of the fact table – it is THE important measure! If customers were also read within seconds of the item read, the customer ID could be an imputed measure to the transaction table. Temperature is another given measure for most readers, although not always interesting to store.

I don’t believe Wal-Mart has implemented readers for its customers, although with the tags many are carrying knowingly or unknowingly on clothes and in wallets, it might become a topic for interesting pilots. Certainly, Wal-Mart could test the reads, although the inconsistency of the data availability promises no systemic findings today. While individual humans are outside the scope of the EPC standard, there’s plenty of room there for future possibilities.

There is also the strong possibility that carts could eventually be tagged and tracked, with or without an operational understanding of the customers who are pushing them. A customer pattern throughout the store is interesting operationally and post-operationally.

  • William McKnightWilliam McKnight
    William is the president of McKnight Consulting Group, a firm focused on delivering business value and solving business challenges utilizing proven, streamlined approaches in data warehousing, master data management and business intelligence, all with a focus on data quality and scalable architectures. William functions as strategist, information architect and program manager for complex, high-volume, full life-cycle implementations worldwide. William is a Southwest Entrepreneur of the Year finalist, a frequent best-practices judge, has authored hundreds of articles and white papers, and given hundreds of international keynotes and public seminars. His team's implementations from both IT and consultant positions have won Best Practices awards. He is a former IT Vice President of a Fortune company, a former software engineer, and holds an MBA. William is author of the book 90 Days to Success in Consulting. Contact William at wmcknight@mcknightcg.com.

    Editor's Note: More articles and resources are available in William's BeyeNETWORK Expert Channel. Be sure to visit today!

Recent articles by William McKnight



Want to post a comment? Login or become a member today!

Be the first to comment!