7

I have several OLTP databases with API's talking to them. I also have ETL jobs pushing data to an OLAP database every few hours.

I've been tasked with building a custom dashboard showing hight level data from the OLAP database. I want to build several API's pointing to the OLAP database. Should I:

  1. Add to my existing API's and call the OLAP database and use a CQRS type pattern, so reads come from OLAP, while writes come from OLTP. My concern here is that there could be a mismatch in the data between reads and writes. How mismatched the data is depends on how often you run the ETL jobs (Hours in my case).
  2. Add to my existing API's and call the OLAP databases then ask the client to choose whether they want OLAP or OLTP data where API's overlap. My concern here is that the client should not need to know about the implementation detail of where the data is coming from.
  3. Write new API's that only point to the OLAP database. This is a lot of extra work.
Muhammad Rehan Saeed
  • 35,627
  • 39
  • 202
  • 311
  • 2
    I would probably go with: >`Write new API's that only point to the OLAP database. This is a lot of extra work.` This Should serve your requirements better and avoid a mixup in the responsibilities and functions of your existing APIs... This approach would land itself to better maintainability and amore robust solution... my 2 cents – Sean Farmar Mar 01 '17 at 12:28
  • 1
    It sounds almost like a text-case example of where CQRS with Event Sourcing and using that to create projections makes a lot of sense. Your OLTP will easily translate to events, and your OLAP databases sound like projections would offer you a considerable performance improvements, that would likely be basically real-time if compared to how often you run the ETL jobs now. – Arwin Mar 03 '17 at 22:02

1 Answers1

1

Don't use #1: when management talk of analyzed reports it don't bother data mismatch between ETL process - obviously you will generate a CEO report after finishing ETL for the day

Don't use #2: this way you'll load transnational system with analytic overhead and dissolve isolation between purpose of two systems (not good for operation and maintenance)

Use #3 as its the best way to fetch processed results, Use modern tools like Excel, PowerQuery, PowerBI to allow you to create rich dashboard with speed instead of going into tables and writing APIs.

SACn
  • 1,862
  • 1
  • 14
  • 29