Floemdata
BI & Reporting

Report Date Dimension: Choosing Day, Hour, Minute

Should a report's date dimension be day, hour or minute? We explain how granularity, time zones and calendar differences change a number.

Floemdata · · 3 min read
Stacked bars splitting from one year into four quarters, twelve months and seven days, one path highlighted

Time is the dimension used most often in reports and misunderstood most often. If it is not clear from which minute to which minute, in which time zone and by which date field 'yesterday's sales' is meant, two people get different numbers from the same report.

Granularity: as much as you need

  • Day: enough for most finance and sales reports; the document date is kept daily anyway.
  • Hour: meaningful for intraday patterns such as store traffic, a call center or a production line.
  • Minute and below: for monitoring and alarms; rarely needed in reporting and it raises data volume considerably.

Common traps

  • Which date? Document date, entry time, due date and payment date can fall on different days; state which one the report is based on.
  • Time zone: systems may keep time in UTC or local time; the day boundary can shift because of it.
  • Day boundary: transactions near midnight can fall on two different days.
  • Calendar: does the week start on Monday or Sunday? Is the fiscal year the same as the calendar year? Public holidays and the number of working days change comparisons across months.

A fictional example

The following example is fictional. An invoice entered at 01:00 Turkish time falls on the previous day when converted to UTC. If the report shows daily sales by UTC date, this invoice is booked to yesterday's sales and does not match, day by day, the figure of accounting, which works in local time. The problem is not a data error but a difference in the definition of the date.

Where Floemdata stands

In the Gold tables of Floemdata Analytics the date dimension is at day level; we found no hour or minute column in the tables. Alongside the year, quarter, month and date dimensions, a Turkish calendar dimension carries working day and public holiday information, according to the project documentation; this allows values per working day to be calculated. Extraction and load times are kept in the Bronze layer. Hourly reports and time-zone conversion rules are not verified in this article (not verified).

To get started, write one sentence for each of your reports: 'which date field, which time zone, which day boundary'. Questions: info@floemdata.com.

← All guides