Temporal Join
You use a temporal join to map a period of time.
Other InfoProviders in reporting handle time-dependent master data in such a way that the record valid for a pre-defined unique key date is used each time. InfoSets, in contrast, 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. InfoSets enable this using temporal operands.
A temporal join is a join that contains at least one time-dependent characteristic or a pseudo time-dependent InfoProvider.
In most cases, it is useful to use exactly one temporal operand for each InfoSet. This is because the key date check is carried out both for each record of the results set, and for all temporal operands.
Temporal Operands
Temporal operands are time characteristics or characteristics of the date type, which have an interval or a key date defined for them. They influence the results set in the temporal join.
Key Date
For the fieldsor attributes of an InfoProvider, you can set an indicator for the Join Control in the Key Date column. 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 the Date type and the 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 key date
-
use last day as key date
-
use a fixed day as key date (that is, a particular day from the specified period of time)
-
Key Date Derivation Type: You can specify a key date derivation type that you 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 have the start and end dates from (0DATEFROM) and to (0DATETO).
Pseudo Time Dependency of DataStore Objects and InfoCubes
In BW, only master data can be defined as a time-dependent data source. Two additional fields/attributes are added to the characteristic.
In contrast, 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 for which you can define a time interval. This is 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 that are 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 from the value 2004, the start date has the value 01.01.2004 and the end date has the value 31.12.2004
-
Indicate 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. 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 and the request will not be taken into account.
As soon as an InfoProvider that is 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 occuring 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 them to a certain time interval, one or more key dates, or a combination of these. Defining restrictions for the fields Valid from (0DATEFROM) and Valid to (0DATETO) for the individual characteristics or results set is not possible for technical reasons. Hence, there is a dimension Valid Time Tnterval (VALIDTIMEINTERVAL) for each InfoSet that represents a temporal join. This is only visible in the Query Designer and is only used for the time selection.
Note the different ways that the word "time interval" is used:
The time interval for a time-dependent InfoObject describes the duration of time for which a record in 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.
See Join Control.
The valid time interval for a temporal join describes the period in which a record of the join results set is valid, and contains the following fields:
-
Valid from and Valid to: These fields contain the start 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 database access from the Time Interval field. You can use multiple key dates and intervals as filters in the query definition. Temporal joins enable you to display statuses for several periods or time intervals next to each other in a query.
NoteNote: Restricting the time interval from 01.01.2001 to 31.12.2001 does not mean that the Valid from/Valid to fields take these values. Instead, this restriction results in every record for 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 characteristics 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, thus the InfoCube and 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 field will be filled as follows:
Date_01 01.09.2004, Date_02 30.09.2004
If you use Date_01 and Date_02 as interval limits, the SQL statement takes them into account. The results 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 with data type D and the 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. See also the third example in Interpreting Queries Using InfoSets.
If two time-dependent characteristics are included in the join, only those combinations of InfoObject records that have a common area of validity with regard to time 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, the validity area that the InfoObjects have in common is known as 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 |
|
BW |
John Smith |
01.01.2000 |
30.06.2001 |
|
BW |
Jane Winter |
01.07.2001 |
31.12.9999 |
|
CSTCNTR |
PROFITC |
RESP |
DATEFROM |
DATETO |
|
4711 |
BW |
Sue Montana |
01.01.2001 |
31.05.2001 |
|
4711 |
BW |
Peter Street |
01.06.2001 |
31.12.2001 |
|
4711 |
BW |
Dan Barton |
01.01.2002 |
31.12.9999 |
If both characteristics are used in a join and connected using PROFITC then not all six possible combinations are valid for the above records, rather only the following four:
|
PROFITC |
RESP |
CSTCNTR |
PROFITC |
RESP |
|
|
BW |
John Smith |
4711 |
BW |
Sue Montana |
(01.01.2001-31.05.2001) |
|
BW |
John Smith |
4711 |
BW |
Peter Street |
(01.06.2001-30.06.2001) |
|
BW |
Jane Winter |
4711 |
BW |
Peter Street |
(01.07.2001-31.12.2001) |
|
BW |
Jane Winter |
4711 |
BW |
Dan Barton |
(01.01.2002-31.12.9999) |
The valid time interval for the combinations, that is the period in which the records of both characteristics are valid, is displayed in parentheses. Neither the combinations of the persons responsible John Smith and Dan Barton, or Jane Winter and Sue Montana are allowed, as their validity areas do not overlap.
More information: