If you've taken a look at my work history, I've supported a lot of OLTP (online transaction processing) applications. The latest being ServiceNow. An OLTP's backend is really just a bunch of tables with columns referencing one another to create a relational database. This is exemplified nicely when you tag an order or case to a customer record and you can see all the orders/cases by that customer. The OLTP is not without flaws: it's purpose is to capture transactions and not necessarily report against them. You don't have to worry about duplicates as every record is unique, but you will have to worry about query times. An OLAP (online analytical processing) cube is a database model that inverts the pros and cons of an OLTP.
Yet, all the software products I have supported can do reporting. The customers demand it. For instance, ServiceNow has one of the best reporting engines I have seen providing a "what's happening now" indicator and a "what has happened in the past and are we better off" metric comparison. Technology has also gotten better for OLTPs to do reporting as technology, memory, and processors are pretty cheap and usually maintained (and scaled) by the Saas provider. There really isn't a reason to export data to a data warehouse unless (1) your company set an initiative to centralize all reporting to a Business Intelligence tool such as BusinessObjects or Tableau or (2) the use case I am about to describe. This is the ONE use case where an OLTP does not do well, but an OLAP cube can handle well - it's the mid year organizational change story.
This happens in every organization. You have a hierarchy that rolls up to the CEO or a list of portfolios that house many projects. One day, a re-org occurs so your department doesn't roll up to that region or that project rolls up to another portfolio. You can probably see where I am headed with this -- the second you make this hierarchy change in your OLTP, all the EXISTING associated transactional records will shift as well. That means all existing incidents in your department just got shifted to another department. That also means all your actual costs already incurred in your portfolio just got shifted to another portfolio and you're well below budget! This is the benefit and the curse of the relational database. You can make a change without updating existing records, but your reporting will probably be inaccurate.
Let's take this scenario as an example. A business unit hierarchy has to change:
Above are the tables (Business Unit and Incident) and the record(s) associated with them. There is also a visual diagram outlining the change. To change the department without regard for reporting, you could simply make the changes in Bold in the “New State”. In the Incident Table, nothing changes as the reference identifier doesn’t change even though the displayed value might. This is the basis for a relational DB which most OLTPs (online transaction processing) are built on.
What you could do to fix this reporting issue is to run a process called versioning. So when Portfolio B disappears in the above scenario, you will have to add the text “DNU” (for “do not use”) to the end of each child and re-create the hierarchy under Portfolio A. This way, existing tickets stay under the old Portfolio B so the original roll up is maintained and new tickets will roll up to Portfolio A. No sharp increase or decrease. Here is what you would do to the BU hierarchy to version:
That's a pain. Now imagine you go through multiple restructures and it's usually not just one portfolio that gets bounced around.
Here’s how we can address this with a data warehouse. In the OLTP, we would create a new table that shows the relationship between a parent and a child hierarchy so that when you modify the relationship, the date and relationship are stored. We call this table “Organizational Timestamps”:
All the applications I've supported has some sort of scripting action that can perform this logic - whenever the parent field of the BU is updated, create a new "org timestamp" row with the previous and new values as variables.
Then you will translate all your tables to OLAP – the data warehouse DB – where “incident”, “BU hierarchy”, and “organizational timestamps” tables are bridged together.
This simplified version of the OLAP cube table could be derived from either a SQL query, R or Python script, or Alteryx job which would handle the logic to populate the right hierarchies based on an incident date value with the dates in organizational timestamp table.
Then you can run the report using a BI (business intelligence) application which will capture the historical roll up based on existing incidents (based on dates) and track new incidents under the current hierarchy. An OLTP is not the right tool for this type of reporting. A data warehouse is.