I have a digital commerce system that we have built in-house and it has been working fine. We now want to implement OLAP to provide our customers with advanced reporting and BI features.

While looking for the needed components I came across several "immutable" data stores which seem popular choices as data warehouses. Ex : Apache Druid, Clickhouse etc. These seem to work pretty well with a visualization tool like Metabase (or other similar ones).

What I am unable to figure is handling updates to records in such immutable stores. Like in any order management system, orders in our system also get updated (status, line items, quantities etc) over the course of their lifetime. In such a case how would one use these immutable stores?

Even if I were to use something like a star schema, I can see needing updates to both fact and dimension tables. Ex if a sale is recorded as a fact with its total revenue, the amount might need an update or the order might get cancelled, items can change etc.

The only way i can think of is to only push a transaction to the data warehouse after it is CLOSED aka frozen. In this case all metrics and reporting for in-process orders will have to be based on the main DB (aka OLTP DB).

Is that the only way out? Is there a better approach?

  • To only be able to report on CLOSED orders would be a pretty poor user experience for reporting, in most cases. There's nothing wrong with changing the data that's used in an OLAP warehouse. But if your question is how to do that technologically, then it would depend specifically on what your current database system is and which one you choose to use for your data warehouse.
    – J.D.
    Oct 3, 2023 at 12:39
  • @J.D. I agree. Analytics on open orders is essential. My current DB is MongoDB and I am considering Apache Druid (primarily because Metabase supports it) as the data warehouse.
    – brahmana
    Oct 3, 2023 at 14:51
  • Ah, too bad you're not using a RDBMS, some of them have built in features to support OLAP in the same database as OLTP so you don't have to implement a connector and manage the data in two places. Best of luck!
    – J.D.
    Oct 3, 2023 at 15:02
  • @J.D. Metabase supports MongoDB out of the box. I have it running that way on staging. But using the main DB for OLAP doesn't seem right to me. For the kind of explorative queries that our customers will possibly run, a column oriented storage (like in data warehouses) is better suited. Otherwise we will have to create a huge number of indexes on the main DB. Further OLAP queries shouldn't load the DB to the point that OLTP operations are affected. Hence the reason for a separate OLAP data warehouse. Nothing to do with RDBMS v/s NoSQL.
    – brahmana
    Oct 4, 2023 at 13:27
  • "OLAP queries shouldn't load the DB to the point that OLTP operations are affected. Hence the reason for a separate OLAP data warehouse. Nothing to do with RDBMS v/s NoSQL." - Except that's not true when it's possible to re-use the same database for both OLTP and OLAP without having to add a "huge number of indexes", which is possible with the features of some RDBMS. For example, Microsoft SQL Server offers columnstore indexes - literally a single index can stage your OLTP rowstore data in a columnar-like format to make OLAP possible within the same database without having to manage 2 DBs.
    – J.D.
    Oct 4, 2023 at 13:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.