Incorporating time into reporting is one of the most commonly asked questions of the Yellowfin team and thus a best practices article is needed. Virtually all reporting has a time based component to it, and as such the use of a time dimension is vital for enabling this for end users.
What is a Date Dimension?
A Date Dimension is a table that allows you to quickly access time attributes such as month, quarter and year for any date in your reporting view. For example, if your are reporting on sales transactions and want to know the total sales dollars by month then having a look up table that associates the transaction date with the month that the sale occurred in will allow you can aggregate (sum) all sales by that month. For Example:
02/ 01 / 2008 $50,000 February
09/ 01 / 2008 $45,000 February
15/ 01 / 2008 $36,000 February
Why do we need one?
Well, Reporting tools always need an explicit time dimension table. There are many time attributes not supported by the SQL date function, including fiscal periods, seasons, holidays, and weekends.
Rather than attempting to determine these non-standard calendar calculations in a query, we should look them up in a date dimension table. Even for common time elements if a time dimension table was not used then complicated SQL and case statements would be needed to deliver the same outcome for each time based element in your reporting. This results in a high level of redundancy and duplication.
In terms of usability, the typical business user is not versed in SQL date semantics, so he or she would be unable to directly leverage inherent capabilities associated with a date data type. SQL date functions do not support filtering by attributes such as weekdays versus weekends, holidays, fiscal periods, seasons, or major events. Presuming that the business needs to slice data by these non-standard date attributes, then an explicit date dimension table is essential.
What does the table look like?
The time dimension table typically has a date key. So for every date you have a single record in the table. Other standard columns include, week, month, quarter and year additional columns can be custom to your needs such as identifying selling versus non-selling days, Holidays or Financial time periods.
The great thing about the Time Dimension table is that it allows you to define the reporting periods you wish to use that could be outside the standard calendar range, Financial Year being a good example, where your organization reports in a unique financial period.
So a typical Time Dimension table would look something like this:
The table join in your view would look something like this:
Unlike most other dimensions, you can build the date dimension table in advance. You may put 5 or 10 years of rows representing days in the table so that you can cover the history you have stored in your data mart or transaction tables, as well as several years in the future. Even 10 years? worth of days is only about 3,650 rows, which is a relatively small dimension table.
The difference with the standard time dimension design and its implementation in Yellowfin is that in the example above the time dimension table has the Month, Year and Quarter defined as Character fields which do limit how they can be used for reporting. This is because standard reporting tools do not have the capacity to reformat dates dynamically. However, since Yellowfin can do this you would implement the above example table using dates for all values rather than character fields.
As you can see the Year is day 1 of the year, quarter is day 1 of the quarter and month is day 1 of the month. In this way, like the first example, all records can be aggregated by common month or year values.
Why use the Yellowfin Method?
The major benefit of the Yellowfin method is that you can use Yellowfin date formatters to display the year, month etc as either dates or as descriptors. By having the date as the actual value you can also utilize time series functions for charts and drill functions providing far greater flexibility in reporting than would otherwise be available through a character field.
Date Hierarchy for Drill Down:
The next step in making your time dimension table really useful is to define a drill down hierarchy. In this case you want to join the top level of the data hierarchy to the next level down and so on- so from year to quarter etc. This way users can view aggregated results for the year and then drill down to view the quarterly or monthly results.
Part Day Analysis:
If you want to access the time of the transaction for day-part analysis (for example, activity during the evening after-work rush or third shift), you would handle it through a separate time-of-day dimension. Date and time are almost completely independent. If you combined the two dimensions, the date dimension would grow significantly; the date dimension with 3,650 rows to handle 10 years of data would expand to 5,256,000 rows if you tried to handle time by minute in the same table. Obviously, it is preferable to create a 3,650-row date dimension table and a separate 1,440-row time-of-day by minute dimension.
To create a time dimension table with pre-populated data you can download the following zip file and run the create table and insert SQL statement within your database. (This is supported for PostgreSQL, MySQL you may have to tweak it for other DBs)