Adding Date and Time entities to your model

Compose provides built-in Date and Time entities that you can add to your model. This facilitates access to all attributes of date and time (such as day of the week, quarter, and so on) both in the BI reports and when creating transformations in the data mart.

The Date entity contains a record for every day. Dates in the Date entity range from January 1st 1900 to December 31st 2099.

The Time entity contains all the hours and minutes in a 24 hour period. When you create the data warehouse tables, the Date and Time entities are automatically populated with relevant data. You can view this data as described in Viewing the data warehouse tables.

Both the date and the time values are presented in multiple formats (e.g. 12 hour format or 24 hour format), allowing you to choose which format will be displayed in your BI reports. Other format include abbreviated forms of date and time, different month/year/day formats (e.g. 12/31/2017 as opposed to 2017-12-31), and so on.

You can either add the entities to a new project (before you create the Data Warehouse tables) or to an existing project. If you add them to an existing project’s model, you will also need to validate and adjust the Data Warehouse as described in Validating the data warehouse.

You can even add custom date and time attributes to the entities from the tables in your landing zone. For example, if one of your source tables lists all the working days and non-working days, you can add an "Is Working Day" attribute to the Date entity and then load it from the relevant source table. Just like regular entities, Compose knows how to merge the incoming data of working and non-working days into the existing Date entity.

For an explanation of how to add attributes to an entity, see Managing attributes.

You cannot add relationships to the Date and Time entities. However, every date and time attribute has an implicit relationship to the Date and Time dimensions, which allows you to select the relevant dimension when creating your star schema in the data mart.

For information on working with Date and Time dimensions in the data mart, see Creating and managing data marts .

To add Date and Time entities to your model:

1. Open the desired Compose project.
2. From the drop-down menu in the top right of the Model panel, select Add Date and Time entities or Add Date Entity if you data source is Oracle (see Note above).
3. When prompted to confirm the action, click Yes.
4. The Date and Time entities will be added to your model. By default, the Date and Time entities are hidden from the model display (as they are not related to other entities in your model). If you want to show them anyway, select the Date and Time model check box in the Data Warehouse Model window.

5. For information about displaying the model, see Displaying the model.

   Information note

   You can also delete the Date and/or Time entities if you no longer require them and add them again later.