If you have ever made a request for a report that was deemed “not possible” and your response was “but, the data’s all in there…” this post is for you. Countless moments like this are what inspired analytic.li years ago.
Which brings us to our first technical topic: Data structure.
There are two ways to structure data.
They are OLTP and OLAP. OLTP stands for Online Transaction Processing and OLAP stands for Online Analytical Processing.
- The OLTP data structure is used to capture and store large amounts of data.
- The OLAP data structure is used for heavy analysis, making it easy to compare data.
OLTP is less complex because its data structure is built to deal with simple, yet high-volume transactions. Examples would be capturing thousands of sales per day in a point of sale (POS) system, processing time punches for a mega-manufacturer or making countless journal entries in an accounting system.
OLAP is more complex because it is built to combine data from disparate sources into one unified decision support framework that better enables in-depth analysis. Think less tracking and more insights.
Most of the software buying decisions you’ve made have been OLTP-structured systems
You’ve likely purchased a number of systems with OLTP data structure before. These are the systems that capture data, streamline workflows and make work lives more efficient. You know the ins and outs of a good accounting package, the types of detailed requirements needed for a labor management system and the stumbling points of an ERP. The data that resides in these systems are typically in OLTP since they were built to efficiently perform large amounts of transactions.
You’ve experienced the benefits of OLTP when your ERP can capture and report on units produced near real-time. But, you’ve also felt the drawback when it’s a herculean effort to compare labor expense per unit this quarter vs. last quarter.
A harsh, but necessary truth about data structures is they can’t be fast… and smart.
OLTP-structured systems are built to quickly store large amounts of data to keep up with the demands of business tracking. But, once OLTP had done its job, you’ll need data in OLAP to begin harvesting insights. Unlike relational databases, OLAP does not store individual transaction records in two-dimensional, row-by-column format, like a worksheet. Instead OLAP uses multidimensional database structures to store arrays of consolidated information.
OLAP is how your data needs to exist to harvest insights. This means that someone will need to meticulously create the data structure anticipating the questions the audience will ask.
An OLAP Data Structure Takes Time
If OLTP is fast, then OLAP is smart. And, storing data smartly takes a bit more time. This means articulating the metrics the business wants to monitor and translating that into the technical work that needs to happen. This requires data engineers, SQL developers and a development environment for them to do their work. This is also where analytics vendors can support your efforts. In an upcoming blog, we’ll explore building vs. partnering. For now, just know that you’ll likely need to consider new amounts and types of investments.
One Down, More To Go
When you think about supporting and fostering a more agile environment, you’ll need insights. And, insights require capturing data (typically done in OLTP structured systems) and making sense of it (in OLAP).
We’ll explore options for data extraction and integration from OLTP-structured systems in the next part of this series. In the meantime, here are a couple of questions to begin asking yourself:
- What are key metrics we track?
- Have any of them changed in light of COVID-19?
- What are the various systems that house the data for those metrics?
- What existing analytics resources do we have (e.g. headcount, tools)?
- How well used are those resources?
- Do we have a return on investment (ROI)?
Want a thought partner on any of this? Drop us a note… we’ll be happy to chat through.