Tuesday, 22 October 2013

Doing minute wise analysis

The lowest grain of the the built in Day dimension is day. We can do analysis at a lower grain by adding appropriate dimensions. If we want to display sales by hour and we have the transaction timestamp in the sales fact table, we can do this analysis.

First we add a column called "Minutes since midnight" in the fact table. This is a number that converts the timestamp into a number that is equal to the number of minutes since midnight. This conversion improves the join performance.


The Minutes dimension table has these columns.


MINUTES_SINCE_MIDNIGHT

TIME_IN_AMPM_FORMAT
TIME_IN_24_HRS_FORMAT
TIME_IN_HOUR_BUCKET
TIME_IN_30_MINS_BUCKET

Data appear like this:





The entire file is kept here and can be used in any app.


Go ahead and create the measures and dimensions. These kind of analysis can be displayed.




No comments:

Post a Comment