Time Intelligence

The Time Intelligence node is used to calculate and produce various date-time values (stored in new columns), based on a source date-time column. The new columns are organized into different levels of granularity, like year, quarter, month, week, and day. For instance, the grid in the image below shows the quarter, month, and week for each year in the dateKey column.

If the data set includes a date or date-time column, the user can build time intelligence into the model by adding a Time Intelligence node. This powerful feature determines all the necessary calculations and settings for creating date/ time analytics based on the given date/ time column. It generates the specified time intelligence columns (for example, quarters, months, and weeks), enabling users to add those columns to queries later on, and to build advanced time intelligence calculations.

Configuring Time Intelligence at the data flow stage creates additional logical columns and places them into a separate time intelligence display folder. It also enables the building of date hierarchies later on, which is important if you want to enable drill functions for dates.

Other Date Time Functions

The Time Intelligence node differs from the Add Date Time node, which adds a column in which each row lists the exact same date or date/ time. The Time Intelligence node, on the other hand, is used to generate a variety of date-time columns based on different date parts, like year, quarter, month, etc.

  • Click here to learn about the Add Date Time node.

The Time Intelligence node also differs from the Date Range node, which is used to add a column that lists a range of consecutive dates based on an existing date-time column, or based on a given start and end date.

  • Click here to learn about the Date Range node.

Time Intelligence and Time Calculations

While Model offers Time Intelligence logic while building constructing your data model, Discover offers date/ time logic with the Time Calculation wizard and

Custom Column Date Parts. The Time Calculation wizard complements the Time Intelligence columns added to the data model, using those columns to perform a range of complex calculations. Custom Column Date Parts, on the other hand, are generally used in direct querying scenarios.

Time Calculation Wizard

The Time Calculation wizard is used to generate a variety of date-time calculations on the fly, such as year to date, last n number of time periods, and comparisons of given time periods. The output is custom elements including lists and formulas, which can then be saved, shared, and added to queries.

The calculations are performed on a selected date-time column; the granularity of this input column should correspond to that of the required calculation. For instance, if you want to generate a list of the last 3 years, you need to input a 'year' column. There is a range of calculations that can be performed using a date-time column, but to fully leverage the Time Calculation wizard, it's best to add Time Intelligence columns to the model in the Data Flow.

Date Parts

When working with an SQL data source Users can also create custom column date parts on the fly in Discover from a date-time column. This function is used to virtually append granular details to an existing data model. This differs from Time Intelligence, which adds the new columns to the data base schema.

The date part functions extract date-time groupings (like year, quarter, month, week) from raw date-time columns in a SQL database. These columns can be added to the current query but cannot be saved or shared and cannot be used to drive logic from the Time Calculation wizard. Custom column date parts are a good solution for users working with an SQL data source via direct query.

How to Configure Time Intelligence

  1. Connect the Time Intelligence to a table node containing the relevant date or date/ time column.
  2. From the Properties panel, go to the Time Intelligence Node window and select the date/ time column you want to perform time intelligence on, then deselect any columns you don't want to include.
  3. Open the Advanced Options window to set your year and week start dates. Choose the start day and month from the calender of either the current year or next year; the given date will be treated as the first day of the year. This enables you to set the year start from a date other than January 1. Next, choose the week start; select the day that should be treated as the first day of the week.
  4. From the Preview panel, click the preview icon to load a preview of the table with time intelligence columns.

NOTE: You can create this intelligence for more than one date time field per model.

Advanced Options

The advanced options are used to define the year start day and month and the week start day. By default, the first day of the year is 01/01 (01 January) of the first year in the source column, and the first day of the week is Sunday.

You can customize both the year start and the week start, which is important if you want to build time intelligence for the fiscal or tax year, or if your organization defines the week start as Monday, for instance.

  • Year Start: set the first day of the year using the format Day/ Month.
  • Current Year vs Next Year: set the start year to the first year (the current start year) in the source column, or to the second year (the next year) in the source column.
  • Week Start: set the first day of the week.

Time Intelligence Outputs

Progressing to the Data Model component, you'll see under Tables that each column generated by the Time Intelligence node is automatically assigned to the relevant category:

This can also be viewed from Columns:

When the model is opened in Discover, we see the Time Intelligence components in two locations in the Dimensions tree:

Hierarchies: the Time Intelligence automatically creates logical hierarchies from the date/ time columns and places them in the Hierarchies folder (red highlight below).

Time Intelligence: each column generated by the Time Intelligence node is automatically placed in a Time Intelligence folder (green highlight).