Temporal Join 

Use

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. Whereas InfoSets 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.

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 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 with a date, that have an interval or a key date defined for them. They influence the results set in the temporal join. 

Key Date

In the Key Date column, you can set an indicator for the Join Controldisplay of 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 the time characteristic 0CALDAY can be indicated 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 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 BI, only master data can be defined as a time-dependent data source. Two additional fields/attributes are added to the characteristic.

Whereas 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 occurring in the time stream. This is always dependent 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. Due to technical reasons, it is not possible to directly define restrictions for the fields valid from (0DATEFROM) and valid to (0DATETO) for the individual characteristics or 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 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 Use Field column, these fields are then made 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 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 may 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 enable you to display statuses for several periods 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/valid to 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  ®  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 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 Interpretation of Reports 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	05/31/2001
Profit center (0PROFIT_CTR)	03/01/2001	07/31/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	03/01/2001	05/31/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	06/30/2001
BI	Jane Winter	07/01/2001	12/31/9999

 

CSTCNTR	PROFITC	RESP	DATEFROM	DATETO
4711	BI	Sue Montana	01/01/2001	05/31/2001
4711	BI	Peter Street	06/01/2001	12/31/2001
4711	BI	Dan Barton	01/01/2002	12/31/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
BI	John Smith	4711	BI	Sue Montana	(01/01/2001-31.05.2001)
BI	John Smith	4711	BI	Peter Street	(06/01/2001-06/30/2001)
BI	Jane Winter	4711	BI	Peter Street	(07/01/2001-12/31/2001)
BI	Jane Winter	4711	BI	Dan Barton	(01/01/2002-12/31/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.