Current state (Compose 2022.5):
When creating an aggregate fact, Compose only permits the selection of a transaction date field that is sourced from the tables involved in the creation of the fact and are available as “business supplied” attributes (i.e., hire date, birth date, order date, first sales date, etc.). When a date field is selected from a source table, that date is used to query all the other related tables using the internal FROM_DATE and TO_DATE satellite columns to return the data set. In order to establish an aggregate fact that is based on CURRENT_DATE or any other point in time (i.e., last day of the year or the FROM_DATE from the transaction), this value would have to be materialized in a table before entering the wizard.
Desired state:
Enable the capability for dynamic dates to be input for the transaction date in the absence of a traditional “transaction date” (which may not always be supplied by the source system(s) consistently. Examples such as CURRENT_DATE or CURRENT_DATE -1, CURRENT_DATE -30, etc, alleviating the need to work around the limitation of the transaction date selection.
Business need:
Aggregate fact views that require use cases such as the current state of all transactions (CURRENT_DATE) or the state of all transactions 30 days ago (CURRENT_DATE -30) or yesterday (CURRENT_DATE -1).