Foundation1 / Development Guide / Br And A / Develop Is / Storage Layer Dev / Develop Dimensions / Special Dim Handling / This page

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.

Contents

• General
• Connecting a Fact with Company Reporting Period
• How to Implement if no Accounting Periods are Available
• How to Implement if Accounting Periods are Available
• Special Dimension Identities
  • Reporting Period Dimension
  • Reporting Company Period Dimension

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_);
   	

4. 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_);
   

4. 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_);
        

       

2. 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_);
      

       

   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.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_);