DEV Community

Aaroophan Varatharajan
Aaroophan Varatharajan

Posted on Originally published at aaroophan.medium.com on

Why Storing Transactions Isn’t the Same as Remembering a Business: Rethinking Business Intelligence #3

A supermarket records every purchase perfectly. Six months later, management wants to understand a long-term customer trend. Why is the transactional database alone not always the right place to answer that question?

Operational systems are built to record and process what is happening. Analytical systems must preserve and organize that information so organizations can understand what happened and why.
Operational systems are built to record and process what is happening. Analytical systems must preserve and organize that information so organizations can understand what happened and why.

A supermarket checkout can record a sale, adjust the inventory, and leave an accurate transaction behind. Finance can reconcile the payment. Procurement can see what needs ✨replenishing✨

So when management wants to understand how customer demand has changed over several years, the obvious engineering answer is to query those records. The business already has a database. Why build another one?

It’s a perfectly reasonable question. It also hides a difference that becomes increasingly expensive to ignore.

Recording what a business is doing and remembering enough to understand what it has done are not the same responsibility.


A Transactional System Is Very Good at Its Job

The first challenge in organizational memory is surprisingly ordinary: Information must survive long enough to be useful. Sales, orders, payroll, inventory, documents, and procedures all contain pieces of what an organization knows. The data model defines how those pieces are represented and accessed; the content may be structured records or unstructured text, audio, and video.

For a long time, many business functions operated through separate applications. Manufacturing Resource Planning (MRP) concentrated on manufacturing and materials. Enterprise Resource Planning (ERP) extended that integration across functions such as finance, human resources, sales, production, and inventory. The Y2K transition added pressure to modernize older systems, but ERP’s lasting contribution was bringing otherwise isolated business processes onto a more coordinated information foundation.

In an integrated ERP environment, an order need not be a completely different reality for the sales, stock, and accounting functions. That shared transactional view is immensely useful. It is also why ERP implementations are demanding: substantial investment, legacy integration, organizational change, and customization can complicate what initially looks like a software installation. Enterprise Application Integration (EAI) middleware can help older applications exchange and transform data without requiring every business process to be replaced at once.

But all this integration is largely serving the business as it operates. IBM’s ERP overview makes the same distinction clear in its emphasis on integrated daily workflows.

And now the question changes. What happens when the workload is no longer about completing an order?


Running the Business Is Not the Same Workload as Understanding It

A transactional database is typically designed for Online Transaction Processing (OLTP): many concurrent, relatively short inserts, updates, and lookups. The checkout cannot politely stop while an analyst calculates several years of product trends.

The analyst needs a different shape of work: comparing regions, grouping products, examining historical periods, and consolidating records from systems that did not necessarily use identical formats. This is the territory of Online Analytical Processing (OLAP).

OLTP: Run the Business

  • Main concern : Record current operational events correctly.
  • Typical access : Frequent small reads and writes.
  • Organizing focus : Applications and transactions.
  • Time perspective : Current operational state, with history as needed.

OLAP: Analyze the Business

  • Main concern : Examine trends and relationships.
  • Typical access : Complex, read-heavy queries and aggregations.
  • Organizing focus : Subjects such as sales, customers, and products.
  • Time perspective : Consolidated history across periods.

The difference is not that OLTP data is bad or that analytical databases are magically smarter. They optimize for different questions. A well-designed operational database may retain history, and modern systems can execute analytics directly against operational or replicated relational data. A separate warehouse is a design choice, not a law of nature.

Still, when analytical queries need substantial history, multiple sources, and transformations that should not interfere with operational work, separation becomes much easier to justify. Microsoft’s OLAP architecture guidance explains that workload boundary.

The database wasn’t failing. We were asking it to serve two different purposes with one design.


A Warehouse Exists Because History Needs Different Rules

A data warehouse is a decision-support store organized for querying and reporting, often constructed from operational data and other relevant sources. Its classic definition uses four characteristics. They sound like labels until we consider what each one protects.

Subject-oriented.

A checkout system cares about completing a sale. A warehouse may organize information around sales, customers, or products so a decision-maker can ask questions across transactions and applications. The business subject becomes the center of gravity.

Integrated.

One source might represent a price in one currency; another may include tax or use a different product code. Copying both records into the same storage does not make them comparable. Names, formats, measures, and meanings have to be reconciled. Data cleaning is part of memory, not an optional polish applied after analysis.

Time-variant.

A current customer record tells us what is true now. Historical analysis may need to know what was true then. A warehouse keeps a longer time horizon so changes can be examined rather than silently flattened into today’s state.

Nonvolatile.

Operational transactions continually update business state. Warehouse data is generally loaded and read for analysis, rather than being edited through ordinary day-to-day transactions. Nonvolatile does not mean never refreshed or never corrected; it means analytical history is managed deliberately instead of behaving like a live order-entry screen.

This is the uncomfortable realization: Saving every transaction is useful, but a trustworthy history also needs consistent meanings, time context, and rules for change.


A Table of Sales Is Still Not a Model of Sales

Suppose the question is now, Which products sold best, in which locations, and during which periods? We could repeatedly join operational tables and build a new query for every report. That works until the same analytical relationships have to be reconstructed again and again.

A multidimensional data model makes those relationships explicit. A fact table holds measurements such as units_sold and dollars_sold, alongside keys to dimension tables. Dimensions describe the context: item, time, branch, and location.

A Table of Sales Is Still Not a Model of Sales

This star schema separates measurements from the descriptions used to filter and group them. An illustrative relational query can now ask for annual sales by city:

SELECT t.year, l.city, SUM(f.dollars_sold) AS sales
FROM sales_fact AS f
JOIN time_dim AS t ON f.time_key = t.time_key
JOIN location_dim AS l ON f.location_key = l.location_key
GROUP BY t.year, l.city;
Enter fullscreen mode Exit fullscreen mode

A snowflake schema normalizes parts of a dimension hierarchy into smaller related tables. A fact constellation uses multiple fact tables, such as sales and shipping, with shared dimensions. Neither is automatically superior; each changes how analytical relationships are represented.

And the grain matters. If each fact row represents a different level of detail, aggregating carelessly can produce a confident-looking answer to the wrong question. Kimball’s explanation of facts and dimensions is useful precisely because it treats measurements and their context as different things.


The Cube Is an Analytical View, Not a Literal Box

Once sales have dimensions, we can view them as a data cube : sales measured across product, location, and time. The same idea extends beyond three dimensions, even though drawing a 4D cube is not exactly a pleasant afternoon.

Different questions then become different operations on that multidimensional view.

Roll-up moves from days to months or years.

Drill-down reverses that move toward detail.

Slice fixes a dimension value; Dice selects a smaller range across dimensions.

Pivot changes how the axes are arranged for inspection.

The underlying sales facts need not change simply because the analyst changes the view.

The model can also contain multiple levels of summary: a detailed base cuboid, higher-level aggregates, and an apex cuboid representing the broadest summary. Some systems precompute aggregations; others calculate them from relational data. Hence the architectural choices among ROLAP (relational storage), MOLAP (multidimensional storage), and HOLAP (a hybrid).

There is one more time problem hiding inside the dimension tables. What if a customer’s region changes? Slowly Changing Dimensions make the policy explicit: Type 1 overwrites a value, Type 2 adds a new versioned row, and Type 3 adds an attribute to retain an alternative value. An overwrite may be perfect for correcting an error and disastrous for a report that needs historical classifications. Kimball’s discussion of changing dimensions explains why the distinction matters.

A business cannot analyze its past reliably without deciding how changes to the past’s descriptions should be handled.


The Warehouse Does Not Clean Itself

By now, the warehouse looks like the obvious answer. There is a catch: its useful consistency does not arrive for free.

Data has to be extracted from operational databases, archives, legacy applications, and sometimes external sources; cleaned and transformed into compatible structures; loaded into the warehouse; and refreshed as source information changes. This is the familiar ETL family of operations.

The Warehouse Does Not Clean Itself

A metadata repository records what the warehouse’s data means, where it came from, how it was transformed, and how its structures relate. Without definitions and lineage, even a technically successful load can produce numbers nobody can confidently explain.

Not every organization uses an identical layout. An enterprise warehouse can serve the whole business; data marts serve selected groups; a virtual warehouse can expose views over existing operational data. A common layered view distinguishes the operational level, warehouse level, departmental or data-mart level, and individual data level. Independent departmental marts may feel faster at first, but can recreate the integration problem when every department develops its own version of reality.

The warehouse has not removed complexity. It has moved that complexity to a place where it can be managed for analytical purposes.


Shared Data Still Does Not Mean Shared Operations

It is tempting to treat enterprise-wide standardization as the inevitable final step: integrate every process, standardize every branch, and make the organization perfectly uniform.

But integration and standardization answer different questions. Integration asks how much information and activity business units need to share. Standardization asks how consistently they need to perform their processes. The two choices produce four operating models :

  • Low integration + Low standardization: Diversification
  • Low integration + High standardization: Replication
  • High integration + Low standardization: Coordination
  • High integration + High standardization: Unification

The important point is not that Unification always wins. Independent units may have good reasons to work differently. Shared customer information may matter even when the underlying processes cannot be identical. MIT CISR’s enterprise architecture research treats these as business choices that shape the IT foundation, not merely database settings.

Enterprise Architecture connects those choices to the design of business processes, data and information, applications, and technology. ERP standardization can make information more dependable and allow faster responses to new opportunities, but forcing uniformity without regard to the operating model can also make implementation costly and inflexible.

The right architecture is not the one with the most shared tables. It is the one that supports the organization’s actual need to share information and coordinate work.


Some of What an Organization Remembers Is Not a Row

Finally, a transaction is only one form of organizational memory. Organizations also retain explicit knowledge : incident reports, alerts, best practices, lessons learned, and records that help locate people with particular expertise like The Origami Software Engineer.

These belong in knowledge repositories , supported by document and digital-content management systems. Their usefulness depends on more than storage capacity. Somebody has to own and share the knowledge; somebody else must be able to search, assess its relevance, and use it.

A perfect transaction history cannot tell us everything a written lesson learned or an expertise locator can tell us. Conversely, a knowledge repository cannot replace reliable transaction processing. Both remember things, but for different future questions.

So the original plan was not foolish. A transactional system should record the business accurately, and querying it directly may be sufficient for some analytical needs. The mistake is expecting today’s operational state, by itself, to preserve the meanings, relationships, and historical context required for every future decision.

Organizational memory is not the amount of data a company stores. It is the ability to recover useful evidence, with its meaning intact, when the next question arrives.

That’s not failure.
That’s evolution.


The “I liked this” Starter Pack:

Don’t let your fingers get lazy now.

  • Like : It tells me this was worth writing.
  • A Comment: Tell me your thoughts, your favorite snack, or a better title for this blog.
  • Boost it: Especially with that one developer who definitely needs this.

Thanks for being here. It genuinely helps more than you know!

— Aaroophan Varatharajan

Find me elsewhere:

Top comments (0)