For an application we have two methods of reporting:
a) We build reports to extract data directly from the application database
b) We send the application data to a data warehouse and we build reports from the data warehouse
I have worked with some companies which did a) as well as b). The advantages for doing b) are:
- It enables us to report over time. We will be able to understand how the numbers are moving from day to day, month to month.
- It enables us to store history. The history of attributes is stored in Slowly Changing Dimensions and the history of measures is stored in Periodic Snapshot Fact Tables.
Note: Point 2 enables point 1.
- It integrates the data from this particular application with data from other application.
- It enables you to do OLAP/cube, e.g. QlikView or SSAS. The main benefit of cube over report is speed. In report the response time is typically 2-5 seconds, but in some areas it could be 30 to 2 minutes. In cube the response time is always under 1 second.
The advantages of doing a) are:
- It enables us to report in real time. As soon as the user enters the data into the application, they can see it in the report. There is no delay. In DW you typically need to wait one day, and in some cases 3 hours.
Note: There are techniques to make it real time, but there are strings attached.
- It is simpler, quicker to build and lower cost. Less than half of b).