Skip to content

Reporting Period Dimension

This page provides detailed information about the common period related dimension Reporting Period. The dimension typically supports Cross Product Reporting.

General

The Reporting Period dimension, DIM_RPD_PERIOD, can be used as a common dimension for any of the dates in the fact part of an Information Source. The result will be that the date can be represented by reporting periods, thus enhancing the reporting possibilities.

Connecting a Fact with Reporting Period

The connection between an Information Source and DIM_RPD_PERIOD can be visualized as follows:

If the <date_column> in the fact, used as true join column or dimension id column, contains timestamps it is required to truncate it to, [TRUNC(<date_column>)], in order to make the join work as expected.

Implementation Steps

  1. Create the fact items
    1. The dimension ID column
TRUNC(<date_column>)    dim_rpd_period_id
2. The true join column
TRUNC(<date_column>)    <date_column_name>

Note: It is important consider NULL values in the Data Mart version of an Information Source. This means that if the <date_column> can be NULL then the DIM_RPD_PERIOD_ID must contain a NULL value identifier, defined as TO_DATE('1900-01-01', 'YYYY-MM-DD', 'NLS_CALENDAR=Gregorian'). In the On Line version a NULL value should lead that the dimension id is NULL. 2. Add the above two columns as fact items to the fact using the meta data file. 3. Metadata to connect the dimension


   rec_.Fact_Id                 := '&FACT';  
   rec_.Dimension_Id            := 'DIM_RPD_PERIOD';  
   rec_.Associated_Fact_Item_Id := '&FACT..DIM_RPD_PERIOD_ID';  
   rec_.Has_Zoom_In_Details     := <define>  
   rec_.Has_Drill_Down_Details  := <define>  
   rec_.Display_Order           := <define>  
   XLR_META_UTIL_API.Install_Fact_Relation(rec_);  

 The above information is sufficient to handle the Data Mart access.
  1. Create the special joins in the metadata file
    1. On Line
rec_.Fact_Id       := '&FACT';  
rec_.Dimension_Id  := 'DIM_RPD_PERIOD';  
rec_.Source_Type   := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
rec_.Exact_Join    := <define>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join(rec_);  

item_rec_.Fact_Id           := '&FACT';  
item_rec_.Dimension_Id      := 'DIM_RPD_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_PERIOD.CODE';  
item_rec_.fact_item_id      := '&FACT..<date_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);   

Some remarks:

  1. The primary key of this dimension is the composite key of the four columns RPD_ID, RPD_YEAR, RPD_PERIOD, REPORTING_DATE
  2. The dimension ID column consists of only one column:

   REPORTING_DATE                 ID
 i.e. many rows may be returned for a given date. The result can be wrong if the same date exists in multiple **Reporting Period Definitions**.
 The result is however correct if the unique key combination **RPD\_ID**, **REPORTING\_DATE** is considered. In e.g. **IFS Business Reporter** this would e.g. mean:

1. A filter criteria defining one specific **Reporting Period Definition ID**.
2. A repeater based on **DIM\_RPD\_PERIOD.REPORTING\_DATE**