Configuring Enterprise Calendar for Oracle BI Apps Time Dimension

Introduction

One of the key common dimensions of Oracle BI Apps is the Time dimension. It contains calendars of different natures to support different types of analysis within various subjects in Oracle BI Apps. Different types of Calendar include:

• Gregorian Calendar
• Fiscal Calendar (multiple)
• Enterprise Calendar (unique enterprise wide)

The Enterprise Calendar (or reporting calendar) enables cross subject area analysis. The Enterprise Calendar data warehouse tables have the W_ENT prefix. Within a single BI Apps deployment by the customer, only one fiscal calendar can be chosen as the Enterprise Calendar. The purpose of this blog is to explain how to configure the Enterprise Calendar.

Configure the Enterprise Calendar

The Enterprise Calendar can be set to one of the OLTP sourced fiscal calendars, or to one of the warehouse generated fiscal calendars (e.g., the 4-4-5 calendar and 13 period calendar supported by Oracle BI Apps). This can be done by setting the following source system parameters in the Business Intelligence Applications Configuration Manager (BIACM):

• GBL_CALENDAR_ID (used to set the ID of the calendar to be used as the Enterprise Calendar)
• GBL_DATASOURCE_NUM_ID (used to set the DSN of the source from which the Enterprise Calendar is chosen)

The following sections show how to set up these two parameters for the Enterprise Calendar in different scenarios.

Scenario 1: Using an Oracle EBS fiscal calendar as the Enterprise Calendar

• GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. In EBS, it should have the format of MCAL_CAL_NAME~MCAL_PERIOD_TYPE. For example, GBL_CALENDAR_ID will be 'Accounting~41' if MCAL_CAL_NAME = 'Accounting' and MCAL_PERIOD_TYPE = '41'.

Note 1: MCAL_CAL_NAME and MCAL_PERIOD_TYPE are sourced from PERIOD_SET_NAME and PERIOD_TYPE of the GL_PERIODS table (an Oracle EBS OLTP table). To see a valid list of combinations of MCAL_CAL_NAME~MCAL_PERIOD_TYPE, run the following query in the OLTP:

SELECT DISTINCT PERIOD_SET_NAME || '~' || PERIOD_TYPE FROM GL_PERIODS;

Note 2: The available EBS calendars are also loaded into the OLAP warehouse table W_MCAL_CAL_D. Therefore, they can be viewed by running the following query in DW:

SELECT MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS, DATASOURCE_NUM_ID

FROM W_MCAL_CAL_D

WHERE DATASOURCE_NUM_ID = <the value corresponding to the EBS version that you use>;

• GBL_DATASOURCE_NUM_ID: For EBS, this parameter should be the DATASOURCE_NUM_ID of the source system from where the calendar is taken. For example, if you are running EBS R11.5.10 and the DATASOURCE_NUM_ID for this source is 310, then you need to set GBL_DATASOURCE_NUM_ID to 310.

GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID are set in BIACM, and this will be covered in a later section.

Scenario 2: Using a PeopleSoft fiscal calendar as the Enterprise Calendar

• GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. In PSFT, it should have the format of SETID~CALENDAR_ID. For example, GBL_CALENDAR_ID will be 'SHARE~01' if SET_ID = 'SHARE' and CALENDAR_ID = '01'.

Note 1: SETID and CALENDAR_ID are sourced from the PS_CAL_DEFN_TBL table (a PeopleSoft OLTP table). To see a valid list of combinations of SETID~CALENDAR_ID, run the following query in the OLTP:

SELECT DISTINCT SETID || '~' || CALENDAR_ID FROM PS_CAL_DEFN_TBL;

Note 2: The available PeopleSoft calendars are also loaded into the OLAP warehouse table W_MCAL_CAL_D. Therefore, they can be viewed by running the following query in DW:

SELECT MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS, DATASOURCE_NUM_ID

FROM W_MCAL_CAL_D

WHERE DATASOURCE_NUM_ID = <the value corresponding to the PeopleSoft version that you use>;

• GBL_DATASOURCE_NUM_ID: For PSFT, this parameter should be the DATASOURCE_NUM_ID of the source system from where the calendar is taken. For instance, if you are running PeopleSoft 9.0 FSCM Instance and the DATASOURCE_NUM_ID for this source is 518, then you need to set GBL_DATASOURCE_NUM_ID to 518.

Note: OLTP sourced calendars are not supported in PeopleSoft HCM pillars. Therefore, should you want to choose an enterprise calendar if you are running PSFT HCM, you need to choose one of the DW generated calendars (i.e., 4-4-5 or 13 period calendar), which is explained in Scenario 3.

Scenario 3: Using a warehouse generated calendar as the Enterprise calendar

• GBL_CALENDAR_ID: This parameter should be the CALENDAR_ID of the DW generated Calendar (so either 4-4-5 or 13 period calendar). By default, the 4-4-5 calendar has a CALENDAR_ID of '10000', and the 13-period calendar has a CALENDAR_ID of '10001'.
• GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is a DW generated Calendar, this parameter should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse). For example, if the DATASOURCE_NUM_ID for your DW is 999, then GBL_ DATASOURCE_NUM_ID should be set to 999.

Note 1: Customers can generate additional warehouse generated calendars which can be picked as the Enterprise Calendar.

Note 2: The available data warehouse calendars are also loaded into the OLAP warehouse table W_MCAL_CAL_D. Therefore, they can be viewed by running the following query in DW:

SELECT MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS, DATASOURCE_NUM_ID

FROM W_MCAL_CAL_D

WHERE DATASOURCE_NUM_ID = <DSN of your DW>

Setting GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID in BIACM

To set GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID, log into BIACM, and click on Manage Data Load Parameters from the navigation bar on the left. Once being directed to the Manager Data Load Parameters page, type in GBL_CALENDAR_ID in the parameter field and choose Code as the parameter type. Then click the Search button after which the parameter with its current value will be returned. Refer to the example below, which shows 10000 as the current value of GBL_CALENDAR_ID.

To change the value of GBL_CALENDAR_ID, click on its current value, and then an edit dialog pops up.

Provide the desired value in the Parameter Value field (note that you do not need to include single quotes in the value, e.g., use Accounting~41 rather than ‘Accounting~41’), and then click Save and Close to save your change. The new value of GBL_CALENDAR_ID has been set.

The setting procedure for GBL_DATASOURCE_NUM_ID is similar. It should be first retrieved by searching for this variable. Once it is returned, click on its current value and then an edit dialog pops up. Change its parameter value there and then save the change.

Setting GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID in a multi-source ETL

In a multi-source ETL run, multiple fiscal calendars from different data sources can be loaded. However, in this case, ONLY ONE calendar can be chosen as the Enterprise Calendar. For example, if you have two data sources, PeopleSoft and Oracle, then you can only choose either a calendar from PeopleSoft or a calendar from Oracle as the Enterprise Calendar. The two parameters GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID should be set in BIACM according to the calendar that you choose. Never provide more than one value to GBL_CALENDAR_ID or GBL_DATASOURCE_NUM_ID in BIACM. That would fail the ETL run.

OBIEE 10G/11G - How to create an Analytics ODBC Data Source (System DSN ODBC connexion)

On Windows Go to Control Panel / Administration Tools and open “Data Sources ODBC”. Go to the “System DNS” Tab and click the “Add…” button. Choose “Oracle BI server”, click the “Finish” button and enter the configuration as the pictures below :

• OBIEE_Repository_Name = A Description of your DSN connection
• DNS_Name_Obi_Server = The name of your machine in the DNS or the IP

• Repository_Name represent the logical name set up in the configuration file.
   
   

Login ID: Weblogic username (BI server username)
Password: Weblogic password (BI server password)
Port: BI server port (usually 9703)

If you want to know the port number configured for your BI server while installing follow below link

http://interestingoracle.blogspot.com/2016/06/obiee11g-find-port-numbers-of-em.html

[OBIEE11g] Find port numbers of EM, Console, Analytics and XMLPServer

Here is how to find port numbers of Enterprise Manager, Console and Analytics from an OBIEE installed server.

1. Go to OBIEE Middleware Home folder (Say MW_HOME)
2.  Go to MW_HOME/Oracle_BI1/Install
3. Open ports.properties file -

BISCHEDULER_SCRIPT_RPC_PORT=9707
OPMN_LOCAL_PORT=9500
BISERVER_PORT=9703
WLS_BIFOUNDATION_SSL_PORT=9804
BISCHEDULER_PORT=9705
BICLUSTERCONTROLLER_MONITOR_PORT=9700
BIJAVAHOST_PORT=9810
WLS_BIFOUNDATION_PORT=9704
BISERVER_MONITOR_PORT=9701
OPMN_REMOTE_PORT=9501
OPMN_REQUEST_PORT=9502
DOMAIN_PORT=7001
BISCHEDULER_MONITOR_PORT=9708
NODE_MANAGER_PORT=9556
BIPRESENTATIONSERVICES_PORT=9710
BICLUSTERCONTROLLER_PORT=9706

WLS_BIFOUNDATION_PORT = 9704 is the port number for Analytics. DOMAIN_PORT=7001 is the port number for Enterprise Manager installation.

You can also open up setupinfo.txt file to get the installation details.

Configure Components
        WebLogic Console
            http://HOSTNAME:7001/console
        Oracle Enterprise Manager
            http://HOSTNAME:7001/em
        Business Intelligence Enterprise Edition
            http://HOSTNAME:9704/analytics
        Business Intelligence Publisher
            http://HOSTNAME:9704/xmlpserver

By default 9704, 7001 and 9804 are the port numbers for Analytics, EM and SSL.

How does TYPE2_FLG Work in ETL

Instruction

TYPE2_FLG is usually used in slowly changing dimensions in BI Applications. This flag indicates if the dimension is type 2, and it determines the data storing behavior in ETL. This blog is to give you a better understanding on how TYPE2_FLG works in ETL.

Background

Slowly Changing dimension

There are many fundamental dimensions such as Customer, Product, Location and Employee in BI application. The attributes in these dimensions are revised from time to time. Sometimes the revised attributes merely correct an error in the data. But many times the revised attributes represent a true change at a point in time. These changes arrive unexpectedly, sporadically and far less frequently than fact table measurements, so we call this topic slowly changing dimensions (SCDs).

Slowly changing dimensions (SCD) entities like Employee, Customer, Product and others determine how the historical changes in the dimension tables are handled and decide how to respond to the changes. There are three different kinds of responses are needed: slowly changing dimension (SCD) Types 1, 2 and 3.

Type 1: Overwrite the attributes with new changes

Type 2: Add a New Dimension Record

Type 3: Add a New Field

We are talking about type 2 in this blog. In the Type 2 SCD model the whole history is stored in the database. An additional dimension record is created and the segmenting between the old record values and the new (current) value is easy to extract and the history is clear. A minimum of three additional columns should be added to the dimension row with type 2 changes: 1) row effective date or date/time stamp (EFFECTIVE_FROM_DT); 2) row expiration date or date/time stamp (EFFECTIVE_END_DT); and 3) current row indicator (CURRENT_FLG).

SRC_EFF_FROM_DT and EFFECTIVE_FROM_DT

The two columns have different concepts though they have similar name. We saw many customers getting confused about the two columns.

SRC_EFF_FROM_DT is extracted from the effective start date of the source (mainly from the main driven source) if the source has the history. If the source doesn’t store history or the history is not extracted, it is hard coded as #LOW_DATE.

EFFECTIVE_FROM_DT is a system column in dimension table to track the history. Remember that we use the knowledge modules (KM) for repeatable logic that can be reused across ETL tasks. Updating the SCD related columns, such as EFFECTIVE_FROM_DT, is usually handled by KM. EFFECTIVE_FROM_DT is modified when inserting a new type 2 record in incremental run, and it is usually modified to the same date as the changed on date from the source. EFFECTIVE_FROM_DT does not always map to the Source Effective Dates.

In type 2 SCD model, EFFECTIVE_FROM_DT is the date used to track the history.

TYPE2_FLG in BI Application

TYPE2_FLG is a flag used to indicate if the dimension is type 2 or not. This flag is used in many dimensions in BI application, such as employee, user, position, and so on. This flag is very important because it determines the history storing behavior.

TYPE2_FLG has two values: ‘Y’ and ‘N’. ‘Y’ means the dimension is a type 2, and ‘N’ means the dimension is type 1. Type 2 dimensions store the history, while type 1 dimensions only store the current record.

For example, if the supervisor is changed from Peter to Susan for an employee on 01/02/2012:

Type 1

EMPLOYEE_ID	SUPERVISOR_NAME	CURRENT_FLG
123	Susan	Y

Type 2

EMPLOYEE_ID	EFFECTIVE_FROM_DT	EFFECTIVE_TO_DT	SUPERVISOR_NAME	CURRENT_FLG
123	01/02/2012	Future	Susan	‘Y’
123	01/01/1999	01/02/2012	Peter	‘N’

 

As shown above, type 1 dimension overwrites the supervisor with the new supervisor, and only stores the current record. Type 2 dimension inserts a new record with the new supervisor name and keeps the old record as a history. The EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT and CURRENT_FLG are modified accordingly: EFFECTIVE_TO_DT is changed to 01/02/2012 and CURRENT_FLG is set as ‘N’ for the old record. The ‘CURRENT_FLG’ is set as ‘Y’ for the new record with the new EFFECTIVE_FROM_DT.

How to Setup TYPE2_FLG

The out of the box code in BI application should have setup the default values. For the type 2 dimensions, it is usually set as ‘Y’.

The TYPE2_FLG can be configured in BIACM. This variable is configured by different dimension groups.

The screenshot above shows that you can configure the value of this flag for difference dimension groups by clicking the parameter value and overwriting it to a different value.

Note: You can only configure the TYPE2_FLG for the dimension groups that are in this BIACM list. The dimension groups that are not in the list cannot be configured.

You should set the value of TYPE2_FLG carefully. If you override the TYPE2_FLG to ‘N’ for a type 2 dimension, you may meet some issues. I will describe more details in the next session.

Possible Issues Related to TYPE2_FLG

As mentioned earlier, sometimes for some reason, the value of TYPE2_FLG may be set to ‘N’ for the type 2 dimension. This may cause some issues.

In BI application, SDE mapping brings the history from the source in the initial full load in some adapters, such as EBS. TYPE2_FLG affects the storing behavior for these historic records. Here compares the different behaviors when setting TYPE2_FLG to ‘Y’ and ‘N’ for a type 2 dimension.

Case 1-TYPE2_FLG = ‘Y’

Let’s take employee dimension (type 2 dimension) as an example

Source

EMPLOYEE_ID	SRC_EFF_FROM_DT	SUPERVISOR_NAME	ROW #
123	01/01/1999	Peter	1
123	01/02/2012	Susan	2

When loading the data into data warehouse in the initial full run, both the rows (including the historical record #1) will be loaded. TYPE2_FLG is ‘Y’ in this case, KM, which will handle the loading behavior, uses this value to determine the type of employee dimension, and accordingly the storing method.

KM will modify EFFECTIVE_TO_DT and CURRENT_FLG for the two records as 

TYPE2_FLG=’Y’ in this case.

EMPLOYEE_ID	EFFECTIVE_FROM_DT	EFFECTIVE_TO_DT	SUPERVISOR_NAME	CURRENT_FLG
123	01/02/2012	Future	Susan	‘Y’
123	01/01/1999	01/02/2012	Peter	‘N’

 

Case 2 - TYPE2_FLG =’N’

This time, the TYPE2_FLG is set as ‘N’ for employee dimension (type 2 dimension), which is incorrect. KM will treat it as type 1 rather than type 2.

Source

EMPLOYEE_ID	SRC_EFF_FROM_DT	SUPERVISOR_NAME	ROW #
123	01/01/1999	Peter	1
123	01/02/2012	Susan	2

When loading the data into data warehouse, both the rows will be loaded because the history from the source is stored. However, because TYPE2_FLG is ‘N’, KM won’t modify EFFECTIVE_TO_DT and CURRENT_FLG accordingly, and this will cause issues.

Employee Table in Data warehouse

EMPLOYEE_ID	EFFECTIVE_FROM_DT	EFFECTIVE_TO_DT	SUPERVISOR_NAME	CURRENT_FLG
123	01/02/2012	Future	Susan	‘Y’
123	01/01/1999	Future	Peter	‘Y’

As shown above, the two records are in an overlapping time range, and both have CURRENT_FLG as ‘Y’. It may give duplicates when resolving the employee from the facts. For example, the transaction date 02/04/2013 will fall into the time range of the two records, so both will be extracted, thus causing the duplicates in the facts.

How to Debug TYPE2_FLG Issues

As discussed in the previous session, in order to avoid this kind of issues, you should set the value of TYPE2_FLG carefully, and set it as ‘Y’ for out of the box TYPE2 dimensions.

In addition, when you get the duplicates in the fact, you can do the following checks.

• Check where the duplicates come from in the fact, and find out the problematic dimension if they are from the dimension.
• Check the data in the dimension for the duplicates to see if you see the similar loading behavior as the one in use case 2 of the previous session. You can first simply see if multiple records having CURRENT_FLG=’Y’.
• Check the value of the TYPE2_FLG in ODI repository.

1. Open the session log of the task

2. Open ‘Definition’

3. Expand ‘Variable and Sequence Values’

4. Find TYPE2_FLG and check the value

5. If the value is ‘N’ but the dimension is type 2, you may hit the issue described in the previous session.

I also would like to provide you some tips to find out the type of a dimension here. You can find out this information in ODI repository.

• For one dimension, such as employee dimension, you should first know the dimension table name, for example, W_EMPLOYEE_D
• Go to ODI repository->’Designer’->’Models’
• Find out the dimension table and open it by double clicking it
• Go to ‘Definition’ and check the OLAP type. The type of slowly changing dimension tells you that this dimension is type 2

• You can also find out which attributes are type 2 by checking the column attribute

1. Expand the dimension table, for example, W_EMPLOYEE_D and then expand Columns

2. Open the attribute of a column by double clicking it

3. Go to ‘Description’ and check ‘Slowly Changing Dimension Behavior’

As shown above, ‘Add Rows on Change’ option tells you that this attribute is type 2.

Conclusion

This blog helps you understand how TYPE2_FLG works in ETL and recognize the importance of this flag. It also gives you a way to debug the possible TYPE2_FLG issue.