Defining Drill Details

In Standard Drill mode you drill from Oracle Fusion Cloud Enterprise Performance Management data form to a landing page and then drill to the source system data. Using this method, you must load data using Data Integration. You must use the Full Workflow mode, which is supported for all business service types. For this mode you can use the auto-created drill region or custom drill region.

Using a Custom Drill Region, You can specify a drill region, which provides an Oracle Essbase drill slice defined using member names and member functions.

Using Direct Drill, you can drill to source data without staging the data in Data Integration. It also provides the flexibility to simplify the drill bypassing the landing page. Additionally, you can customize the scope of the drill region using Essbase member functions, which simplifies maintenance and improves performance.

With Direct Drill you can load the data using Full Workflow mode, Quick mode or even load directly to the Cloud EPM business process. This mode is not supported for Account Reconciliation and Profitability and Cost Management business processes. For this mode you must define a custom drill region.

To define a drill definition:

1. From the Data Integration home page, then Actions, select Applications.

2. From the Application page, select an application by clicking to the right of a local application, and then select Drill Details.

   

3. From the Drill Details page, click .

4. Select the Define Drill Detail page, and then from Plan Type, select the source plan type to which you have an integration.

   

5. In URL Name, specify the name of the drill.

   This is the Drill URL Name in Essbase when you open it in Calculation Manager.

   Essbase name restrictions apply to the name of the direct drill including:

   • Use no more than 8 characters when naming a direct drill for a non-Unicode-mode application.

   • Use no more than 30 characters when naming a direct drill Unicode-mode applications.

   • Do not use spaces in the name.

   • Do not use the following characters:

     Table 15-1 Essbase Naming Restrictions

     Character	Description
     *	asterisk
     []	brackets
     :	colon
     ;	semicolon
     ,	comma
     =	equal sign
     >	greater-than sign
     <	less-than sign
     .	period
     +	plus sign
     ?	question mark
     "	double quotation mark
     ‘	single quotation mark
     /	forward slash
     \	backslash
     |	vertical bars
     	tabs

6. From URL Type, select the type of URL to use for the direct drill.

   Available types:

   • Landing Page—Have the system automatically launch the landing page. From this landing page, you can open the source document or continue to drill-through to the defined source system landing page as shown below.

     In the following example, drill through is available from a data form in Planning:

     

     From this landing page, you can open the source document or continue to drill through to the defined source system landing page.

     

     Note:

     Drill through functionality is not supported for exchange rates data loaded to Planning applications.

     If you map to an alias when loading data to Essbase, then the drill-through might fail because the filter on the landing page uses the member from Essbase. If this case, use the member name instead of the alias.

   • Custom—Use a URL associated with a custom direct drill region definition and display the drill through results either in Custom - Dynamic tab or Custom - Browser tab.

     In the Custom - Dynamic tab, results are shown in a new tab within the Oracle Fusion Cloud EPM.

     In the Custom - Browser tab, results are shown in a new browser tab.

     When you define a custom Drill using this setting for a Plan Type, the default drill creation does not create the drill region for the Plan Type. The create drill region flag is ignored if a custom drill is defined.

     Optional: You can specify a drill region, which provides an Essbase drill slice defined using member names and member functions.

     1. From the Drill Details page, select the Drill Region tab.

     2. Click .

     3. From the Dimension drop-down, select the dimension(s) to add to the drill region.

     4. From Filter, specify a member function for the selected dimension to limit results only to the specified members.

        Specify the member between quotation ("") marks. For example, specify the member July as "Jul".

        Optionally, you can click to display the Member Selector page and navigate to a selected member to drill on. For more information, see Selecting Members from the Member Selector in the Smart View for Office User's Guide 22.100.

        

   • Agent—Use the parameters based on the SQL query and server information. In this case, the server information comes from the agent but the rest of the URL is derived from the parameters specified in the SQL query. To use this URL type, the drill URL must include the data source, query, and the parameters to pass.

7. In Drill URL, specify the URL address for the custom and agent direct drill through definitions.

   For a custom URL Type, the drill URL must include the server, port, and URL parameters, for example, https://server:port/<URL Parameters>.

   The following is a custom drill URL. This is a direct drill URL if you want to bypass the landing page:

   

   For an agent URL Type, the drill URL must include the data source application name, SQL Query, and number column.

   • DATASOURCE—Specify the name of the Data Source Application used to load the data. The source connection information is derived from this application.
   • QUERY—Specify the SQL query to use for performing the drill.
   • NUMERIC—Specify the number column so it is properly aligned.

   

   For more information about drilling through using the EPM Integration Agent, see Drill through to Source using the EPM Integration Agent.

8. Select the Enable Summary Drill option to drill down from summary members in a data form or report and view the detail source data that makes up the number.

   After enabling this option and loading the data with the Drill Region option set to Yes, the Drill icon is enabled at the summary level. Drill is limited to 1000 descendant members for a dimension.

   For a summary drill the 1,000 descendants limit is for each dimension. When there are more than 1,000 descendants, the system behavior is controlled by the Summary Drill Behavior when more than 1000 descendants option in Defining Application Detail Options.

   A drill from parent with more than 1000 descendants can result inconsistent results. Note the data pattern before allowing users to drill at all levels.

   When the option is set to Ignore, then the system completely ignores filtering on that dimension. Filters are applied only for the remaining dimensions with level 0 members or descendants less than 1,000. As a result, it is possible you will get members not under the parent you are drilling. When using the Ignore option the only time it works is when the ignored dimensions intersect with unique members of the other dimensions.

   For example in the following scenario, Location and Account are leaf level members

   Table 15-2 Location and Account Leaf Level Members

   Location	Cost Center	Account
   US	110	Expense
   US	120	Expense
   US	130	Expense
   UK	210	Expense
   UK	210	Expense

   In this case Cost Center intersects with unique rows Location and Account. If you drill on US, Total Cost Center, and Expense and the option is set to ignore the Cost Center, then the drill returns 3 rows of US.

   If on the other hand, lets assume Location is a parent with more than 1000 children:

   Table 15-3 Cost Center Intersect with Unique Location

   Location	Cost Center	Account	Project
   US	110	Expense	ABC
   US	120	Expense	ABC
   US	130	Expense	XYZ
   UK	210	Expense	ABC
   Uk	210	Expse	XYZ

   If you drill on US, Total Cost Center, and Expense, the ABC system ignores Location and Country and returns all rows that match Expense and ABC. This results in rows from US and UK.

   When enabling drill from Parent, it is recommended to enable drill only from parents with less than 1000 descendants. This can be done by defining drill region manually using Application Drill Details page.

   When you select Limit Option, it is possible that all the children do not fall in the 1000 limit resulting in an incomplete result set.

   Note:

   If you select to perform a summary drill on more than 1,000 descendants and include only top-level dimensions but the results are the same as the base-level dimension, then also select base-levels intersections for at least three dimensions.

   Note:

   In order to support a summary drill, your drill query should not include an operator in the WHERE clause of the SQL. For example, your query should be: WHERE COMPANY ~ENTITY~. The system determines appropriate condition (IN, LIKE) based on the number of descendants.
   For more information the drill region, see Defining Drill Regions .

9. Click Save.

   When you save the drill definition, drill regions are automatically created or updated in Essbase. When you delete the Drill Details, they are also deleted in Essbase.

10. Specify a custom drill region on the Drill Region tab if desired.

11. Optional: To edit an existing direct drill definition, from the Drill Details page, select the definition and click .

    To delete an existing direct drill definition, from the Drill Details page, select the definition and click .