The Magic of Real-Time Reporting for Retail

While all businesses desire and see the value in near real-time reporting, many still feel “stuck in the dark ages” of having to wait hours or days to get the information they need to act. Blueprint can change that.

When we speak with potential clients, a topic that always seems to come up is the velocity of reporting. While all businesses desire and see the value in near real-time reporting, many still feel “stuck in the dark ages” of having to wait hours or days to get the information they need to act. Whatever the given reasons for reporting delays, there’s no reason any decision-maker should have to wait that long for their data.

To frame our discussions around building real-time reporting infrastructure in difficult environments, we like to reference our work with an existing client in the retail space. 

Like many retailers, our client had Point of Sale (POS) systems running on legacy databases that had not scaled in performance as business grew. Technology that was optimized to record sales from a few hundred locations became sub-optimal when managing data from thousands of locations. The lack in performance capacity coupled with business rules for data cleansing often resulted in delays for end-user delivery. Fixing this problem was non-trivial for several reasons:

1. Over time, other teams and systems became dependent on the sales database, making changes complex and difficult.
2. The system couldn’t go offline so any migration effort would come with a lot of risk.
3. A full architecture rebuild would take a long time and be very expensive.

 As a result, this retailer (like many) delivered sales data by running a batch job when stores were closed. Between the time Hawaii stores closed and East Coast stores opened, an ETL process would copy the previous day’s data, then apply a set of business rules. After that, other teams needing access to sales data would extract what they needed. For example, Supply Chain loaded sales and returns data into SAP, Marketing copied everything into Teradata and Finance pulled revenue data and moved it into TM1. All these jobs made the current sales database “untouchable.”

Luckily, the retailer reached out to Blueprint. We crafted a solution that not only provided real-time reporting, but also didn’t touch any of the existing databases or processes the business was currently running. It met the requirements from every channel and only took a few months to implement.

Here’s the magic:

Most relational databases have a transaction log that runs in parallel with the existing tables in a database. This log captures what happens inside that database; which table got updated with what data at what time. From this log, a replica set of tables can be created that matches the ones in production. Since these duplicate tables are separate, they are not impacted by read, write or updates to the source data. In addition, these tables can be updated in real time from the log.

We leveraged PostgreSQL for its scalability to recreate the data then used our virtualization tool Conduit to Direct Query Power BI against this dataset. Voila! Full visibility to sales data in REAL TIME with no impact to existing systems.

Creative solutions that drive business value, that’s the Blueprint way.

Interested in learning more about how Blueprint can get your numbers to you faster? Check out another piece on Real-Time Data Access here or watch the video below for a more in-depth look at how Conduit can work with your existing infrastructure to help you make better, faster decisions for your organization.