X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Loading Custom Calendars using the Universal Adaptor

Guest Author

 

Loading Custom Calendars using the Universal Adaptor

Author: Chuan Shi

Introduction

In Oracle BI Apps time dimension, we support fiscal calendars extracted from customers’ OLTP systems. However, there could be situations that customers need to use a custom calendar that does not exist. For example,

  • Siebel customers do not have any fiscal calendars in the OLTP
  • EBS/PSFT/Fusion customers do not have the specific calendar they want to use in their OLTP.

In these cases, the custom fiscal calendar can be seeded in the following csv files (that are shipped OOTB) and be extracted via the UNIVERSAL adaptor:

  • file_mcal_cal_d.csv*
  • file_mcal_period_ds.csv
  • file_mcal_context_g.csv*

* Note that these two files are replaced by file_mcal_cal_ds.csv and file_mcal_context_gs.csv in Oracle BI Apps 11.1.1.10.1 release and beyond.

This blog article provides explanation and example on this.

Load Custom Calendar via Universal Adaptor

A customer can load any custom fiscal calendar via the universal adaptor. There are three SDE tasks for fiscal calendars in this adaptor and they are:

  • SDE_Universal_TimeDimension_Calendar
  • SDE_Universal_TimeDimension_MCalPeriod
  • SDE_Universal_TimeDimension_MCalContext

These tasks load the fiscal calendar data seeded in the corresponding csv files into relevant DW tables. As long as the customer provides the fiscal calendar data correctly for the custom fiscal calendar in those csv files, this calendar will be loaded into the DW.

To demonstrate the idea, we assume that a customer wants to load a fiscal calendar where each fiscal year starts on July 1st of the current calendar year and ends on June 30th of the next calendar year; each fiscal period is a calendar month (i.e., the first period of each fiscal year is from July 1st to July 31st); and date range of this fiscal calendar is from 2015 July 1st to 2017 June 30th (i.e., two fiscal years – FY 2016 and FY 2017, are considered). We discuss how to prepare the data for these three SDE tasks next.

Prepare Data for FILE_MCAL_CAL_D.CSV

FILE_MCAL_CAL_D.CSV is used to register the custom calender in W_MCAL_CAL_D. The granularity of this DW table is at the calendar level and each row of it specifies a unique fiscal calendar. In the csv file, a customer is required to provide necessary information required to populate this calendar. The information includes:

Name

Description

ROW_WID

A unique numerical ID that specifies the calendar

Note that you may have other calendars loaded from OLTP and DW. Each of these calendars together with your custom calender must have a unique ROW_WID

MCAL_CAL_ID

A string that specifies the ID of the custom calendar

MCAL_CAL_NAME

A string that specifies the name of the custom calendar

MCAL_CAL_CLASS

A string that specifies the class of the calendar, e.g., OLTP Sourced or Generated. For custom calendar, OLTP Sourced can be used.

FISCAL_CALENDAR_FLG

Y if this is a fiscal calendar; else N

BUDGETARY_CALENDAR_FLG

Y if this is a budgetary calendar; else N

INTEGRATION_ID

The unique identifier of the calendar; it can be the same as the MCAL_CAL_ID

DATASOURCE_NUM_ID

DSN for this calendar

W_INSERT_DT

Insert date; format is YYYYMMDDHHMISS, e.g., 20150513000000

W_UPDATE_DT

Update date; format is YYYYMMDDHHMISS, e.g., 20150513000000

TENANT_ID

DEFAULT

X_CUSTOM

0

 

No matter if a column is a string or a number, do not use any quotes in the csv file. Suppose that for the customer calendar we are interested, we name it Universal~Custom. Then, the content of the sample csv file for this calendar is:

 

Prepare Data for FILE_MCAL_PERIOD_DS.CSV

FILE_MCAL_PERIOD_DS.CSV is used to load all the fiscal periods of the custom calender in W_MCAL_PERIOD_DS. The granularity of this DW table is at the fiscal period level and each row of it specifies a unique fiscal period. In the csv file, a customer is required to provide necessary information required to populate all the periods of the calendar. The information includes:

Name

Description

MCAL_CAL_ID

A string that specifies the ID of the custom calendar

MCAL_CAL_NAME

A string that specifies the name of the custom calendar

MCAL_CAL_CLASS

A string that specifies the class of the calendar, e.g., OLTP Sourced or Generated. For custom calendar, OLTP Sourced can be used.

ADJUSTMENT_PERIOD_FLG

Y if the period is an adjustment period; else N

MCAL_PERIOD_TYPE

Something that is meaning to specify the type of the period, e.g., Custom

MCAL_PERIOD_NAME

Name of the fiscal period

MCAL_PERIOD

A number that shows the period number, e.g., 1, 2, etc

MCAL_PERIOD_ST_DT

Period start date; format is YYYYMMDDHHMISS, e.g., 20150701000000

MCAL_PERIOD_END_DT

Period end date; format is YYYYMMDDHHMISS, e.g., 20150731000000

MCAL_QTR

A number that shows the fiscal quarter (e.g., 1, 2, 3 or 4) to which this period belongs

MCAL_YEAR

Four digit number that shows the fiscal year (e.g., 2016) to which this period belongs

MCAL_QTR_START_DT

Start date of the fiscal quarter to which this period belongs; format is YYYYMMDDHHMISS, e.g., 20150701000000

MCAL_QTR_END_DT

End date of the fiscal quarter to which this period belongs; format is YYYYMMDDHHMISS, e.g., 20150930000000

MCAL_YEAR_START_DT

Start date of the fiscal year to which this period belongs; format is YYYYMMDDHHMISS, e.g., 20150701000000

MCAL_YEAR_END_DT

End date of the fiscal year to which this period belongs; format is YYYYMMDDHHMISS, e.g., 20160630000000

INTEGRATION_ID

The unique identifier of the fiscal period; it is suggested that you use the concatenation of MCAL_CAL_ID and MCAL_PERIOD_NAME

PERIOD_CLASSIFICATION_CODE

__NOT_APPLICABLE__

DATASOURCE_NUM_ID

DSN for this calendar

W_INSERT_DT

Insert date; format is YYYYMMDDHHMISS, e.g., 20150513000000. You can leave it empty if you don’t want to provide it.

W_UPDATE_DT

Update date; format is YYYYMMDDHHMISS, e.g., 20150513000000. You can leave it empty if you don’t want to provide it.

TENANT_ID

DEFAULT

X_CUSTOM

0

 

No matter if a column is a string or a number, do not use any quotes in the csv file. It is very important for a customer to provide the correct values to some crucial columns such as the start and end dates of fiscal periods, quarters, and years. If they are incorrect, they will be loaded into W_MCAL_PERIOD_D as bad data and when this table is aggregated to load W_MCAL_QTR_D and W_MCAL_YEAR_D, there could be ETL failures.

As in our example:

  • Each fiscal year starts from July 1st of the current year and ends on June 30th of the next year;
  • Each fiscal period is a month.

Using fiscal year 2016 as an example, the 12 fiscal periods should have the following values (also be careful about the leap year):

The content of the sample csv file for all the periods in our example is:

 

Prepare Data for FILE_MCAL_CONTEXT_G.CSV

Note that not all customers may need this. FILE_MCAL_CONTEXT_G.CSV is mainly used for financial customers where they have different organizations or ledgers and each organization or ledger may use a different fiscal calendar. Therefore the relationship between organization and fiscal calendar is stored in W_MCAL_CONTEXT_G table. (Siebel customers do not need to configure this csv file.)

In the csv file, a customer is required to provide necessary information required to populate this table. The information includes:

Name

Description

ORG_ID

A string that specifies the ID of the organization; either this or the LEDGER_ID should not be null

ORG_CODE

A string that specifies the code of the organization, this can be null

LEDGER_ID

A string that specifies the ID of the ledger; either this or the ORG_ID should not be null

LEDGER_NAME

A string that specifies the name of the ledger, this can be null

CALENDAR_ID

The ID of the calendar, e.g., Universal~Custom in our example

CALENDAR_NAME

The name of the calendar

INTEGRATION_ID

The unique identifier of the org/ledger and calendar combination; e.g. you can concatenate ORG_ID or LEDGER_ID with CALENDAR_ID, you can also prefix the concatenated string with GL to stand for general ledger. E.g., GL~3142~Universal~Custom

MCAL_CAL_WID

The WID of the calendar. This must be the same as the ROW_WID chosen for this given calendar. E.g., in our example, we use 4999 as the ROW_WID for the calendar Universal~Custom and here we use Universal~Custom as the calendar for this org or ledger, than we need to put 4999 for the MCAL_CAL_WID

DATASOURCE_NUM_ID

DSN

W_INSERT_DT

Insert date; format is YYYYMMDDHHMISS, e.g., 20150513000000

W_UPDATE_DT

Update date; format is YYYYMMDDHHMISS, e.g., 20150513000000

TENANT_ID

DEFAULT

X_CUSTOM

0

 

No matter if a column is a string or a number, do not use any quotes in the csv file. Suppose that for the customer calendar we are interested, we name it Universal~Custom. Then, the content of the sample csv file for this calendar is:

 

Add Universal Adaptor to the Generated Execution Plan

Once these csv files are ready, a customer should generate a load plan that includes the universal adaptor as well for these tasks to be picked up in the generated EP. Then running the EP will load the custom calendar specified in these csv files into the DW, and therefore the customer can use it later in the reporting.

 

Modify or Extend Custom Calendar during Incremental Loads

Before the full load, customers are suggested to prepare data for all the fiscal years they want a fiscal calendar to cover in the file_mcal_period_ds.csv file. However, it is quite possible that customers later want to extend such a calendar loaded using the universal adaptor. Or customers may want to make corrections to existing data loaded via the universal adaptor. In these cases, customer needs to modify the corresponding csv files before the next incremental load.

  • If customer needs to extend a custom calendar, they need to add additional period entries in file_mcal_period_ds.csv for the fiscal years they want to extend the calendar to. Existing entries can be kept in the csv file or they can also be removed. The w_insert_dt and w_update_dt of these newly inserted rows should be correctly provided in the csv files.
  • If customer needs to make correction to a specific entry in the csv file, they should make sure the w_update_dt column of that entry is updated appropriately so that the incremental logic can pick them up during the next incremental load.

Use the Custom Calendar as the Enterprise Calendar

Since the custom calendar loaded via the universal adaptor is a fiscal calendar, it can be used as the enterprise calendar. Recall that the enterprise calendar is nothing but a chosen fiscal calendar. So, it means that customer must first load this custom calendar into W_MCAL% tables via the universal adaptor and then they are free to choose it as the enterprise calendar.

To set this custom calendar as the enterprise calendar, in BIACM, customer should set GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID correctly for the enterprise calendar. For the calendar considered in this article, the calendar customer wants to use is called Universal~Custom and its DSN is 900. Then, in BIACM, customer should set GBL_CALENDAR_ID to Universal~Custom, and GBL_DATASOURCE_NUM_ID to 900. This way, after ETL is run, the W_ENT% tables will be populated. (Note that W_ENT_WEEK_D will NOT be populated as customer is not using the DW generated calendar as the enterprise calendar.)

For more on how to set the enterprise calendar, the readers are referred to the following article:

https://blogs.oracle.com/biapps/entry/configuring_enterprise_calendar_for_oracle

 

Join the discussion

Comments ( 2 )
  • Roy Tuesday, June 30, 2015

    What is in 11.1.1.10.1 and when is it available?


  • Francoise Lawrence Wednesday, July 1, 2015

    Release 11.1.1.10.1 is the next release of BI Apps which will provide compatibility with FA Rel 10 and it is expected to be released soon.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.