Skip to content

Reporting Company Period Dimension

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

General

The Reporting Company Period dimension, DIM_RPD_COMPANY_PERIOD, is the main dimension related to the Reporting Periods functionality, with the main purpose to enable connection between a Financial Information Source and a non-Financial Information Source.

A Financial Information Source in most cases defines its transactions with respect to an accounting period, while a non-Financial Information Source in many cases does not have any information about accounting periods but might instead have a date that is a rather good approximation of an accounting date.

The Reporting Company Period dimension can then acts as the glue between two Information Sources, enabling reports presented per reporting period as a replacement of accounting periods.

Connecting a Fact with Company Reporting Period

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

Note: The CODE and the REPORTING_DATE columns are equivalent, i.e. they contain exactly the same value, the truncated reporting date.

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

How to Implement if no Accounting Periods are Available

Implementation steps when the fact only contains COMPANY and <date_column>, i.e. an Information Source that does not contain accounting periods:

  1. Create the fact items
    1. The dimension ID column
company ||'^'|| TO_CHAR(<date_column>,'YYYY-MM-DD')    dim_rpd_company_period_id
2. The true join column representing date
<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 COMPANY or the <date_column> can be NULL then the DIM_RPD_COMPANY_PERIOD_ID must contain a NULL value identifier, defined as
NVL(COMPANY,'#') || '^' || NVL(<date_column>, '#')
.
In the On Line version a NULL value of one of the key columns 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_COMPANY_PERIOD';  
   rec_.Associated_Fact_Item_Id := '&FACT..DIM_RPD_COMPANY_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_);  

  1. Create the special joins in the metadata file. Note: It is necessary to define joins for both On Line and Data Mart access in this case. For the Data Mart case there is an alternative identity to be used, defined by the item DIM_RPD_COMPANY_PERIOD.ALT_DIM_ID.

    1. On Line

rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.COMPANY';  
item_rec_.fact_item_id      := '&FACT..<company_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  


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

2. Data Mart

rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_PERIOD';  
rec_.Source_Type  := Xlr_Meta_Util_API.DM_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.DM_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.ALT_DIM_ID';  
item_rec_.fact_item_id      := '&FACT..<dim_rpd_company_period_id>;  
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 five columns RPD_ID, RPD_YEAR, RPD_PERIOD, COMPANY and REPORTING_DATE.
  2. The ALT_DIM_ID column is an alternative dimension identity, built by combining company and the reporting date:
company ||'^'|| TO_CHAR(reporting_date,'YYYY-MM-DD')    alt_dim_id
 This means that many rows can returned for the same 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**, **COMPANY**, **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**. Conditions and/or repeaters on **COMPANY**.
2. The other alternative would be repeaters based on **RPD\_ID**, **COMPANY**.

How to Implement if Accounting Periods are Available

Implementation steps when the fact contains accounting periods. For this case we need COMPANY and a <date_column> where the <date_column> represents the start date of the accounting period.

  1. Create the fact items
    1. The dimension ID column
company ||'^'|| TO_CHAR(<period_start_date_column>,'YYYY-MM-DD')   dim_rpd_company_period_id
2. The true join column representing the date (accounting period start date)
<period_start_date>                                         <period_start_date_column_name>

Note: It is important consider NULL values in the Data Mart version of an Information Source. This means that if COMPANY or the <period_start_date_column_name> can be NULL then the DIM_RPD_COMPANY_PERIOD_ID must contain a NULL value identifier, defined as
NVL(COMPANY,'#') || '^' || NVL(<period_start_date_column_name>, '#')
.
In the On Line version a NULL value of one of the key columns 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_COMPANY_PERIOD';  
   rec_.Associated_Fact_Item_Id := '&FACT..DIM_RPD_COMPANY_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_);  

  1. Create the special joins in the metadata file. Note: It is only necessary to define join information for On Line access in this case. But there must be a date column available that represents the start date of the accounting period.

    1. On Line
rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.COMPANY';  
item_rec_.fact_item_id      := '&FACT..<company_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

item_rec_.Fact_Id           := '&FACT';  
item_rec_.Dimension_Id      := 'DIM_RPD_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 2;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.ACCOUNTING_DATE';  
item_rec_.fact_item_id      := '&FACT..<period_start_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 five columns RPD_ID, RPD_YEAR, RPD_PERIOD, COMPANY and REPORTING_DATE.
  2. The ID column is defined according to the following:
company ||'^'|| TO_CHAR(accounting_date,'YYYY-MM-DD')             ID
 This means that many rows can returned for the same 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**, **COMPANY**, **ACCOUNTING\_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**. Conditions and/or repeaters on **COMPANY**.
2. The other alternative would be repeaters based on **RPD\_ID**, **COMPANY**.

Special Dimension Identities

The described implementation so far does not consider the RPD_ID, i.e. the Reporting Period Definition ID. The reason is that in most cases an Information Source does not contain the identity. Thus it is important to supply the RPD_ID when creating reports in e.g. IFS Business Reporter, to avoid duplicated values if there is more than one Reporting Period Definition.

There can however be cases where an Information Source is created by combining transactional data with basic data that leads to a transaction source that contains the RPD_ID. To be able to join the Information Source with one of the available Reporting Period dimensions, there must be an identity in these dimensions that also contains the RPD_ID.

Reporting Period Dimension

The following special identities are available:

  1. ID_WITH_RPD_ID The identity support the case where the source contains the Reporting Period Definition ID and Accounting Periods. In the dimension DIM_RPD_PERIOD the identity is built as follows:
rpd_id || '^' || TO_CHAR(reporting_date,'YYYY-MM-DD')    id_with_rpd_id
 This identity should be matched by the **DIM\_RPD\_PERIOD\_ID** in the Information Source, defined as:
rpd_id || '^' || TO_CHAR(<date_column>,'YYYY-MM-DD')    dim_rpd_period_id

Note: It is important consider NULL values in the Data Mart version of an Information Source. This means that if RPD_ID or the <date_column> can be NULL then the DIM_RPD_PERIOD_ID must contain a NULL value identifier, defined as
NVL(RPD_ID,'#') || '^' || NVL(<date_column>, '#')
.
In the On Line version a NULL value of one of the key columns should lead to that the dimension id is NULL. It is necessary to supply special join information for both On Line and Data Mart access.

1. On Line join is handled according to the following
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.RPD_ID';  
item_rec_.fact_item_id      := '&FACT..<rep_def_id_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_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           := 2;  
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_);  

2. Data Mart join is handled according to the following:

rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_PERIOD';  
rec_.Source_Type  := Xlr_Meta_Util_API.DM_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.DM_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_PERIOD.ID_WITH_RPD_ID';  
item_rec_.fact_item_id      := '&FACT..<dim_rpd_period_id>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

Reporting Company Period Dimension

The following special identities are available:

  1. ID_WITH_RPD_ID The identity support the case where the source contains the Reporting Period Definition ID, COMPANY and Accounting Periods. In the dimension DIM_RPD_COMPANY_PERIOD the identity is built as follows:
rpd_id || '^' || company ||'^'|| TO_CHAR(accounting_date,'YYYY-MM-DD')    id_with_rpd_id
 This identity should be matched by the **DIM\_RPD\_COMPANY\_PERIOD\_ID** in the Information Source, defined as:
rpd_id || '^' || company ||'^'|| TO_CHAR(<accounting_period_from_date>,'YYYY-MM-DD')    dim_rpd_company_period_id

Note: It is important consider NULL values in the Data Mart version of an Information Source. This means that if RPD_ID or COMPANY or the <accounting_period_from_date> can be NULL then the DIM_RPD_COMPANY_PERIOD_ID must contain a NULL value identifier, defined as
NVL(RPD_ID,'#') || '^' || NVL(COMPANY,'#') || '^' || NVL(<accounting_period_from_date>, '#')
.
In the On Line version a NULL value of one of the key columns should lead to that the dimension id is NULL.

 It is necessary to supply special join information for both On Line and Data Mart access.


1. On Line join is handled according to the following:
rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.RPD_ID';  
item_rec_.fact_item_id      := '&FACT..<rep_def_id_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

item_rec_.Fact_Id           := '&FACT';  
item_rec_.Dimension_Id      := 'DIM_RPD_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 2;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.COMPANY';  
item_rec_.fact_item_id      := '&FACT..<company_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

item_rec_.Fact_Id           := '&FACT';  
item_rec_.Dimension_Id      := 'DIM_RPD_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 3;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.ACCOUNTING_DATE';  
item_rec_.fact_item_id      := '&FACT..<period_start_date_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

2. Data Mart join is handled according to the following:

rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_PERIOD';  
rec_.Source_Type  := Xlr_Meta_Util_API.DM_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.DM_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.ID_WITH_RPD_ID';  
item_rec_.fact_item_id      := '&FACT..<dim_rpd_company_period_id>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

  1. ALT_ID_WITH_RPD_ID The identity support the case where the source contains the Reporting Period Definition ID, COMPANY and a date, i.e. no accounting periods are available. In the dimension DIM_RPD_COMPANY_PERIOD the identity is built as follows::
rpd_id || '^' || company ||'^'|| TO_CHAR(reporting_date,'YYYY-MM-DD')    alt_id_with_rpd_id
This identity should be matched by the **DIM\_RPD\_COMPANY\_PERIOD\_ID** in the Information Source, defined as:
rpd_id || '^' || company ||'^'|| TO_CHAR(<date_column>,'YYYY-MM-DD')    dim_rpd_company_period_id

Note: It is important consider NULL values in the Data Mart version of an Information Source. This means that if RPD_ID or COMPANY or the <date_column> can be NULL then the DIM_RPD_COMPANY_PERIOD_ID must contain a NULL value identifier, defined as
NVL(RPD_ID,'#') || '^' || NVL(COMPANY,'#') || '^' || NVL(<date_column>, '#')
.
In the On Line version a NULL value of one of the key columns should lead to that the dimension id is NULL.

 It is necessary to supply special join information for both On Line and Data Mart access.

1. On Line join is handled according to the following:

rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.RPD_ID';  
item_rec_.fact_item_id      := '&FACT..<rep_def_id_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

item_rec_.Fact_Id           := '&FACT';  
item_rec_.Dimension_Id      := 'DIM_RPD_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.ONLINE_SOURCE_TYPE_;  
item_rec_.Join_No           := 2;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.COMPANY';  
item_rec_.fact_item_id      := '&FACT..<company_column_name>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);  

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

  1. Data Mart join is handled according to the following:

rec_.Fact_Id      := '&FACT';  
rec_.Dimension_Id := 'DIM_RPD_COMPANY_PERIOD';  
rec_.Source_Type  := Xlr_Meta_Util_API.DM_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_COMPANY_PERIOD';  
item_rec_.Source_Type       := Xlr_Meta_Util_API.DM_SOURCE_TYPE_;  
item_rec_.Join_No           := 1;  
item_rec_.dimension_item_id := 'DIM_RPD_COMPANY_PERIOD.ALT_ID_WITH_RPD_ID';  
item_rec_.fact_item_id      := '&FACT..<dim_rpd_company_period_id>;  
XLR_META_UTIL_API.Install_Fact_Dim_Join_Item(item_rec_);