Dates and Times

When date data is loaded into your project, it is stored in a special object called a Date dimension

The platform uses the Date dimension to represent date in many different ways in your project. The following figure shows how the platform can represent data in the Start Date dimension by attributes:

Your project may include multiple date dimensions, each associated with a different event.



For example, the GoodSales sample project contains information about sales opportunities, including the date each opportunity is created and the date it is closed. Opportunity creation and close dates are distinct dimensions because each represents a different event.

Opportunity creation and closed date events are also distinct from dates that represent the day a sales rep has performed a sales action, such as sending an email, placing a call, or hosting a webinar. These types of events can be considered activities in the sales pipeline.

You can slice metrics only by those date dimensions that relate to that metric.

Date Dimensions in a Logical Data Model

The simplified logical data model below shows how date dimensions (blue) are mapped to various project attributes (green). Each date dimension contains an array of date attributes that are related to one another in a specific hierarchy.

Distinct date dimensions may relate to different project entities.

The date dimension used to slice a project metric significantly affects how your data is categorized and represented in a report.

Metric values categorized by opportunity creation date.

Metric values categorized by opportunity close date.

Although both of the above reports compute the same metric across overlapping time periods, each report measures the metric for a different event (opportunity created versus opportunity closed).

Limitations of the Logical Data Model (LDM)

You cannot slice a metric by every date dimension in your project. You can only slice project metrics by attributes in date dimensions that relate to the metric's underlying facts (or attributes, in the case of a COUNT metric).

In the simplified data model above, notice that the date (activity) dimension does not connect to the Opportunity attribute. It is not possible to slice the Number of Opportunities metric by an attribute of the date (activity) dimension. 

In the Report Editor, attempting to break a metric across an unrelated date dimension results in the following message:

 Report not computable due to improper metric definition

Reading the Data Model for Dates

To understand which date dimensions you can use to slice a particular metric, go to Manage → Model to open the data model.

To access the Manage page, you must be an Editor or Administrator in your project.

As a general rule, a metric that is an aggregation of some fact or attribute can be sliced by a date dimension that points (directly or indirectly) to that fact or attribute.

In the logical data model below, date dimensions (representing entire arrays of date attributes) are shown in blue, attributes in green, and facts in yellow ovals.

A metric aggregated from the Velocity fact could be sliced by attributes within the Date (Created) dimension but not by attributes within the Date (Activity) dimension. 

Logical data model with multiple date dimensions

To learn more about using the concepts of time and dates in your reports, see the following topics: