Temporal Join
Use
You use a temporal join to map a period of time.
During reporting, other InfoProviders handle time-dependent master data in such a way that the record that is valid for a pre-defined unique key date is used each time. InfoSets, however, are more flexible. They can be used to map periods of time, as in the following case:
A DataStore object contains a posting date and a time-dependent characteristic, as well as a key figure. You now want the record for the time-dependent characteristic to be determined according to the posting date, which is different in each record of the DataStore object. This is possible with InfoSets using temporal operands.
Features
A temporal join is a join that contains at least one time-dependent characteristic or a pseudo time-dependent InfoProvider.
In most cases, it makes sense to use one temporal operand for each InfoSet. This is because the key date check is carried out for each record of the results set, and for all temporal operands.
Temporal Operands
Temporal operands are time characteristics, or characteristics of type Date, for which an interval or a key date is defined. They influence the results set in the temporal join.
Key Date
In the Key Date column of the display in the join control, you can set an indicator for these fields and attributes of an InfoProvider. If the indicator is set, the field or attribute is used as a temporal operand.
Depending on the type of characteristic, there are various ways to define a key date:
Characteristics of type Date and time characteristic 0CALDAY can be flagged as key dates.
You have multiple options for time characteristics that describe a period of time with a start and end date (0CALWEEK, 0CALMONTH, 0CALQUARTER, 0CALYEAR, 0FISCPER, 0FISCYEAR).
● use first day as a key date
● use last day as a key date
● use a fixed day as a key date (a particular day from the specified period of time)
● key date derivation type: You can specify a key date derivation type that you have defined using Environment → Key Date Derivation Type.
Time Interval
You can set time intervals for time characteristics that describe a period of time with a start and end date. Start and end dates are derived from the value of the time characteristic.
In the context menu of the table display of the InfoProvider, choose Define Time-Dependency. The system adds extra attributes (additional fields) to the relevant InfoProvider. These receive the start and end dates from (0DATEFROM) and to (0DATETO).
Pseudo Time Dependency of DataStore Objects and InfoCubes
In BI, only master data can be defined as a time-dependent data source. Two additional fields/attributes are added to the characteristic.
DataStore objects and InfoCubes themselves cannot be defined as time-dependent. However, they often contain time characteristics from which a time interval can be derived, or date entries with which you can define a time interval so that the corresponding InfoProvider in the InfoSet can be considered as time-dependent. The time characteristics 0CALWEEK, 0CALMONTH, 0CALQUARTER, 0CALYEAR, 0FISCPER, and 0FISCYEAR are considered in time derivation.
You can define pseudo time dependency in the following ways:
● Choose one of the previously mentioned time characteristics contained in the InfoProvider that is to be made time-dependent. Two date attributes are added to the InfoProvider in the InfoSet, and this indicates time-dependency.
Example: If 0CALYEAR is derived with the value 2004, the start date has the value 01/01/2004 and the end date has the value 12/31/2004
● Flag a characteristic of type Date as the start date and another characteristic of type Date as the end date.
You must make sure that the dataset is suitable for this. The value of the attribute that is interpreted as the start date must be smaller than, or equal to, the value of the attribute that is interpreted as the end date. If this is not the case, the data record is interpreted as invalid from the outset and is not taken into account in requests.
As soon as an InfoProvider contained in the InfoSet is made pseudo time-dependent, it is treated as a proper time-dependent data source.
An important difference between pseudo time-dependent InfoProviders and proper time-dependent InfoProviders is that the system cannot prevent gaps or overlaps from occurring in the time stream. This always depends on the dataset of the pseudo time-dependent InfoProvider.
Time Selection with Query Definition
A period of time is usually mapped for a temporal join. When defining queries, the question arises of how to restrict one or more key dates, or a combination of these, to a particular time interval. For technical reasons, it is not possible to define restrictions directly for fields valid from (0DATEFROM) and valid to (0DATETO) for the individual characteristics or the results set. For this reason, a dimension valid time interval (VALIDTIMEINTERVAL) exists for each InfoSet that represents a temporal join. This is only visible in the Query Designer and is used for the time selection.
Note the different ways in which the phrase time interval is used:
The time interval for a time-dependent InfoObject describes the period of time for which the respective record of the InfoObject is valid.
The InfoObjects for the time interval (valid from and valid to) of a time-dependent InfoObject are visible in the join control. If you set the indicator in the column Fields in the Query, these fields are available in the BEx Query Designer to define a query, but cannot be restricted.
More information: Join Control
The valid time interval for a temporal join describes the period of time for which a record of the results set of the join is valid, and contains the following fields:
● Valid from and Valid to: These fields contain the beginning and the end of the valid time interval. They are not visible in the join control, but are available in the BEx Query Designer. These fields can only be used for the output of results in rows or columns. They must not be used with restrictions.
● Time Interval: This field is only used to select the time interval and can therefore only be used in the filter, but not to display results in rows or columns. The runtime system derives the correct selections for the database access from the Time Interval field. You can use multiple key dates and intervals as filters in the query definition. Temporal joins therefore enable you to display statuses for several times or time intervals next to each other in a query.
Note: Restricting the time interval from 01.01.2001 to 31.12.2001 does not mean that the fields valid from and valid to take these values. Instead, this restriction results in every record of the results set having a validity area that lies either entirely or partially within this time interval.
Time Dependency in the Results Set
Time-dependency is assessed when the results set is determined. A record is only included in the results set if the key date or time interval lies within the valid time interval. A time interval is assigned to each record in the results set. The records are valid for the duration of the interval to which they are assigned (valid time interval).
Since a key date or a time interval can only be derived from a time characteristic once the results set has been read, the system checks the validity of the records again after the data has been read from the database. As a result, more data is read than ultimately appears as the query result. You must therefore think about the effect on the system performance before you use time characteristics as temporal operands with derivations.
It is much better for performance to calculate and fill two date fields (start and end date) from the derived time characteristic during data loading. You can then define these fields in the InfoSet as start and end date.
Example: A DataStore object or an InfoCube has the time characteristic 0CALMONTH. This is to be used later in the InfoSet as a time interval, and therefore the InfoCube or the DataStore object should be considered as pseudo time-dependent. You insert two fields of type Date (Date_01, Date_02) into the DataStore object or InfoCube and fill them when loading.
If 0CALMONTH has the value 092004, the fields will be filled as follows:
Date_01 → 09/01/2004, Date_02 → 09/30/2004
If you use Date_01 and Date_02 as interval limits, the SQL statement takes them into account. The result set is therefore much more likely to be smaller than if you were to execute the derivation using 0CALMONTH.
You are however able to use an InfoObject of data type D and InfoObject 0CALDAY as temporal operands without restriction. This is because the corresponding selection conditions are directly relayed to the database.
If only one time-dependent characteristic is contained in the join, note that there are multiple records in the database for a value of this characteristic. For this reason, multiple records can appear in the results set for the join; they can be only be distinguished from one another by time-dependent attributes and the valid time interval of the characteristic. You can filter such records using a time selection. For more information, see the third example in Interpreting Queries Using InfoSets.
If two time-dependent characteristics are contained in the join, only those combinations of InfoObject records that have a common validity area regarding the time period are included in the results set. This also applies if there are more than two time-dependent InfoObjects in a join.
For example, a join contains the following time-dependent InfoObjects (in addition to other objects that are not time-dependent):
InfoObjects in the Join |
Valid From |
Valid To |
Cost center (0COSTCENTER) |
01.01.2001 |
31.05.2001 |
Profit center (0PROFIT_CTR) |
01.03.2001 |
31.07.2001 |
Where the two time intervals overlap, that is, the validity area that the InfoObjects have in common, is the valid time interval of the temporal join:
Temporal Join |
Valid From |
Valid To |
Valid time interval |
01.03.2001 |
31.05.2001 |
You define an InfoSet using the PROFITC (profit center) characteristic. This contains the responsible person (RESP) as the time-dependent attribute and the CSTSNTR (cost center) characteristic, which also contains the person responsible as a time-dependent attribute. These characteristics contain the following records:
PROFITC |
RESP |
DATEFROM |
DATETO |
BI |
John Smith |
01.01.2000 |
30.06.2001 |
BI |
Jane Winter |
01.07.2001 |
31.12.9999 |
CSTCNTR |
PROFITC |
RESP |
DATEFROM |
DATETO |
4711 |
BI |
Sue Montana |
01.01.2001 |
31.05.2001 |
4711 |
BI |
Peter Street |
01.06.2001 |
31.12.2001 |
4711 |
BI |
Dan Barton |
01.01.2002 |
31.12.9999 |
If both characteristics are used in a join and are connected using PROFITC, not all six possible combinations are valid for the above records, but only the following four:
PROFITC |
RESP |
CSTCNTR |
PROFITC |
RESP |
|
BI |
John Smith |
4711 |
BI |
Sue Montana |
(01.01.2001-31.05.2001) |
BI |
John Smith |
4711 |
BI |
Peter Street |
(01.06.2001-30.06.2001) |
BI |
Jane Winter |
4711 |
BI |
Peter Street |
(01.07.2001-31.12.2001) |
BI |
Jane Winter |
4711 |
BI |
Dan Barton |
(01.01.2002-31.12.9999) |
The valid time interval for the combinations, that is, the time period in which the records of both characteristics are valid, is displayed in parentheses. The combinations of responsible persons John Smith and Dan Barton, or Jane Winter and Sue Montana, are not allowed, as their validity areas do not overlap.
More information:
Processing the Time Dependency