Oracle Fusion HCM DBI

DBI:  Single term with many meanings. When I heard for the first time, it referred as Daily Business Intelligence in Oracle as a separate module.  DBI in context with the Perl, it’s a Database Interface. Standard definition from Wiki for Perl OCI referred as “In Computing, the Perl DBI offers a Standardized way for using the Perl Programing language to embed Database communication within their programs. Latest DBI Module for Perl from CPAN, that can run on a range of Operating Systems” (Wikipedia). However, when the same term used in context with the Fusion HCM, it’s a Database Item (DBI) is a SQL Routine used to fetch the data from the Fusion Database, hiding the actual SQL to the end user. DBI can be grouped together to form as a DBI Group.

  
For an example, a predefined DBI such as PER_ASG_JOB_NAME can be used by a fast formula Absence Entry Validation to fetch the JOB NAME to process its (FF) logic.

DBI are of two types and there are more than 36000 DBI delivered by Oracle HCM.

Static: Which includes the standard types of information, such as Sex, Birth Date and Work Location at run time.

Dynamic: Fusion Applications creates the Dynamic DBI’s when end users creates any of the following objects: Elements, Balances, Formulas, Global Values, Element Input Values, Felxfields.

Key Components of DBI:

Routes: They represent the FROM and WHERE Clauses in the SQL Query behind the DBI.A Route is connected to a database item by a User Entity (UE).

Route Parameters: These are the values that are passed to the bind variables present in the route when the DBIs are generated by configuration process (such as DBIs related to element entries or element types).

User Entities (UE): User Entity (Logical Entity) will be name of Query for getting required data, i.e. for getting Departments, Employees, etc. For example, a UE for retrieving Job, Person, Grade, Location or Department,  is PER_EXT_WORKSTRUCTURES_UE (root block). To retrieve the Grade, the business object would be WORKSTCUTURE_UE.ENTITY_ID = PER_EXT_SEC_GRADE_UE.GRADE_ID.

DBI Groups: A database item group allows a database item to execute different route code depending upon the context presented to the DBI at runtime.  This allows for flexibility in that the same DBI may be used at multiple levels within the employment hierarchy.

DBI information can be retrieved from the following tables:

FF_DATABASE_ITEMS_TL – DATAASE_ID, Language, Description (Translated description of the Database Item , contains the Static and Dynamic DBI’s). Datanase Group information can be retrieved from FF_DBI_GROUPS_TL.

HCM Extracts:

HCM extracts is a tool in Fusion used for extracting the complex information from Fusion HCM tables and delivers the output to the third-party destination in required format. HCM Extracts uses the DBI’s to retrieve the data from the HCM tables. A full list of available DBI’s can be found from the Oracle Document ID 1565118.1.

An HCM extract can run in multi-threaded mode only when it uses the threading DBI’s. Threading Database Item is required for implementing the changes only feature. It is the unique ID for that User Entity. Threading database item need to be declared at root data group or child data group level where it requires, changes only data.Generally, for Pay Employee user entity and Assignment user entity it would be DBI with %ASSIGNMENT%ID. For Person user entity, it would be DBI with pattern %PERSON%ID. It is mandatory to have a threading DBI, to enable the multi-threading for extract. Is is not possible to update the existing extract for Multi-threading after running an extract. Instead the following steps are to be followed:

a) extract (definition) has to be copied 
b)In the new extract, modify the required ID as threading DBI and object action as threading type in new extract for root data group. 
c) Compile all Formula

More information on threaded DBI can be found in Chapter 18 of the Oracle HCM Guide

For Payroll and HCM Extracts, the Threads and Chunk Size parameters are set in the Manage Payroll Process Configuration task. Checking and updating the thread information for extracts:

1) Navigate to FSM, Manage Payroll Process Configuration task.
Check/Modify values of below parameters: Parameter Name- Threads Determines the 2) total number of sub processes that run under the concurrent manager. Default: 1; minimum: 1 Modify value to new value such as 8.



Latest DBI's from the instance can be retrieved using the following SQL:

SELECT  fat.module_name          ,
        fdg.base_group_name      ,
        fdg.group_name           ,
        fdi.base_user_name       ,
        fdi.user_name            ,
        fdi.description          ,
        fdi.data_type            ,
        fdi.definition_text      ,
        fue.base_user_entity_name,
        fue.description          ,
        fr.base_route_name       ,
        fr.multi_row_flag        ,
        (
                SELECT  SUBSTR(sys.stragg(','
                        ||
                        base_context_name),2) context
                FROM    fusion.ff_route_context_usages i,
                        fusion.ff_contexts_vl j
                WHERE   i.context_id   = j.context_id
                        AND i.route_id = fr.route_id
        )
        contexts_used,
        (
                SELECT  SUBSTR(sys.stragg(','
                        ||
                        parameter_name),2) context
                FROM    fusion.ff_route_parameters
                WHERE   route_id = fr.route_id
        )
        parameters,
        (
                SELECT  SUBSTR(sys.stragg(','
                        ||
                        base_context_name),2) context
                FROM    fusion.ff_dbi_groups_vl a    ,
                        fusion.ff_dbi_usages b       ,
                        fusion.ff_database_items_vl c,
                        fusion.ff_contexts_vl d
                WHERE   a.context_id         = d.context_id
                        AND a.dbi_group_id   = b.dbi_group_id
                        AND b.dbi_id         = c.database_item_id
                        AND c.user_entity_id = fue.user_entity_id
        )
        contexts_set
FROM    fusion.ff_database_items_vl fdi,
        fusion.ff_dbi_usages fdu       ,
        fusion.ff_dbi_groups_vl fdg    ,
        fusion.fnd_appl_taxonomy_vl fat,
        fusion.ff_user_entities_vl fue ,
        fusion.ff_routes_vl fr
WHERE   fdi.module_id           IS NOT NULL
        AND fdi.database_item_id = fdu.dbi_id(+)
        AND fdu.dbi_group_id     = fdg.dbi_group_id(+)
        AND fdi.module_id        = fat.module_id
        AND fdi.user_entity_id   = fue.user_entity_id
        AND fue.route_id         = fr.route_id
        AND fdi.module_id       IS NOT NULL
ORDER BY module_name,
        fdi.base_user_name;

More References:

NOTE:1559127.1 - Oracle Fusion HCM Extracts Guide
NOTE:2361470.1 - Sample HCM Extracts 
NOTE:1677090.1 - Fusion HCM Extract Step-by-Step Approach and Troubleshooting Guide 
More Information : https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r12/wn/r12-hcm-common-wn.htm#_Toc504629719