Data Warehousing and Business Intelligence

2 October 2011

Two Methods of Reporting

Filed under: Data Warehousing — Vincent Rainardi @ 10:11 pm

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:

  1. 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.
  2. 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.
  3. It integrates the data from this particular application with data from other application.
  4. 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:

  1. 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.
  2. It is simpler, quicker to build and lower cost. Less than half of b).

 

 

 

 

 

 

 

1 Comment »

  1. Title should have been something like Normal Reporting vs DWH based Reporting.. I thought you found some new methods 😀 Jokes apart, nice post. Excellent ref when you are explaining things to a beginner..

    Comment by theSuda — 3 October 2011 @ 5:05 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: