Manage Key Flexfield Data in the Global Views
In Noetix global views that leverage the key flexfield views, the relevant segment values or descriptions related to a key flexfield code combination defined at your site are retrieved by using the data cache or no data cache option. In the data cache option, data is retrieved using the key flexfield data cache tables that are maintained by Noetix. In the no data cache option, data is retrieved directly from the Oracle key flexfield tables.
In the data cache option, the key flexfield data cache tables are created, loaded, and indexed during Stage 4. However, these tables need to be refreshed so that they reflect the changes in the segment values or descriptions for the key flexfield code combinations defined at your site. The data cache tables can be refreshed fully or incrementally through Concurrent Manager jobs. When the data cache tables are used, there will be some delay between when a segment value or description is created or modified and when the value or description can be seen through the views.
In the no data cache option, the key flexfield views retrieve the new or modified segment values directly from the key flexfield tables of Oracle database. As a result, the segment values corresponding to a key flexfield code combination are available in real time in the key flexfield views. However, only the segment values are updated in real time and not the segment descriptions corresponding to the code combination. The segment descriptions are updated only when data cache tables are used.
Comparison of Data Flow
The process of retrieving the key flexfield segment values vary depending on the option used. The following diagrams illustrate the process by which the key flexfield views retrieve data using the data cache tables and using the key flexfield tables.
By default, key flexfield views use data cache tables to retrieve key flexfield data. However, key flexfield data can be retrieved directly from the Oracle key flexfield tables in the following situations:
Need to avoid latency between a key flexfield code combination is created or modified and a transaction using this code combination is retrievable from the global views.
Need to access key flexfield segment values in real time from the global views provided a delay in the availability of segment descriptions is acceptable if descriptions are available.
Using Noetix Key Flexfield Data Cache Tables
Key flexfield data cache tables can be refreshed fully or incrementally through Concurrent Manager jobs. When the data cache tables are used, there will be some delay between when a segment value or description is created or modified and when the value or description can be seen through the views.
Full Refresh
In the full refresh process, the existing information in the data cache tables is replaced with the information from the source key flexfield tables. When the data cache tables are fully refreshed, reports based on the global views that use these data cache tables are unavailable until the full refresh is completed. In the full refresh process, a Concurrent Manager program is automatically created and scheduled for each available data cache table. This program is created during Stage 4. By default, the program is scheduled to run on a monthly basis. You can change this schedule when required.
Note: Some key flexfields may also have a data cache description table created in addition to the data cache value table. In such cases, a request set is also created to refresh the data cache description table and data cache value table. For example, a request set named N KFF GL ACCT-RSET(UID-5035) is created to refresh the N KFF GL ACCT and N KFF GL ACCT D tables.
The Concurrent Manager program names created for full refresh use the <data cache table name>-Initial Upload (<NOETIX SYS>[UID-<NOETIX SYS internal identifier>]) format. For example, the Concurrent Manager program created for the N KFF GL Acct data cache table will be named as N KFF GL Acct-Initial Upload (HG B911 PO BL SSY[UID-5035]).
The following query can be used to list all the Concurrent Manager programs that are created for the full refresh process in a NOETIX SYS schema:
select program name from n f kff flex source pgms where program type = 'INITIAL'
Important: To run this query, you have to use a database query tool, such as Toad to connect to the NOETIX SYS schema.
To verify the Concurrent Manager programs in Oracle E-Business Suite
Log on to Oracle E-Business Suite as the XXNAO user. The XXNAO user is a super user that is automatically created for handling all the application objects corresponding to the NOETIX SYS schema. The default password for this user is set as welcome#1.
Select the key flexfield responsibility corresponding to the NOETIX SYS schema at your site. This responsibility is required for viewing, scheduling, or submitting the jobs used to refresh data cache tables. By default, the responsibility name uses the Noetix Administration Objects.KFF(<NOETIX SYS>[UID-<NOETIX SYS internal identifier>]) format. For example, the key flexfield responsibility corresponding to the HG B911 PO BL SSY schema will be named as Noetix Administration Objects.KFF(HG B911 PO BL SSY[UID-5035]).
Verify the Concurrent Manager programs created for the full refresh process. If required, modify the default schedule for the Concurrent Manager programs, and then resubmit the programs.
The source data changes are reflected in the global views after the data cache tables are refreshed. For more information about data cache tables, see Forms of Noetix Views in “Forms of Noetix Views.”
Incremental Refresh
In the incremental refresh process, data cache tables are updated only with the changed data from the source key flexfield tables. The incremental refresh process uses triggers to detect and capture data changes from the source key flexfield tables between refreshes. The incremental refresh process can be used if the data cache tables have to be refreshed frequently. When compared to the full refresh process, the existing data in the data cache tables is available for report generation during the incremental refresh process. However, the changed data will be available for report generation only after the incremental refresh process is completed.
By default, the incremental refresh infrastructure is installed and not activated to detect the source data changes. A Concurrent Manager program is available to enable the incremental refresh process. You will have to manually run this Concurrent Manager program to enable the incremental refresh process. The Concurrent Manager program uses the Enable Incremental Refresh-(<NOETIX SYS>[UID-<NOETIX SYS internal identifier>]) format for the name. For example, the Concurrent Manager program created for the HG B911 PO BL SSY schema will be named as Enable Incremental Refresh-(HG B911 PO BL SSY [UID-5035]).
The Concurrent Manager program performs the following when run:
Activates the triggers to detect and capture data changes from the specified source key flexfield tables.
Submits the individual full refresh Concurrent Manager program for the data cache tables corresponding to the specified key flexfield.
Cancels the individual full refresh Concurrent Manager program schedule for the data cache tables corresponding to the specified key flexfield.
Schedules the incremental refresh request set for the specified key flexfield. By default, the request set is scheduled to run automatically on every day to update the data cache tables. You can change this schedule when required. The request set created for incremental refresh uses the INCREMENTAL REFRESH-<NOETIX SYS>[UID-<NOETIX SYS internal identifier>]) format for the name. For example, the request set created for the HG B911 PO BL SSY schema will be named as INCREMENTAL REFRESH-HG B911 PO SSY[UID-5035]).
To enable incremental refresh
Log on to Oracle E-Business Suite as the XXNAO user. The XXNAO user is a super user that is automatically created for handling all the application objects corresponding to the NOETIX SYS schema. The default password for this user is set as welcome#1.
Select the key flexfield responsibility corresponding to your NOETIX SYS schema that is required for viewing, scheduling, or submitting the jobs used to refresh data cache tables. By default, the responsibility name uses the Noetix Administration Objects.KFF(<NOETIX SYS>[UID-<NOETIX SYS internal identifier>]) format. For example, the key flexfield responsibility corresponding to the HG B911 PO BL SSY schema will be named as Noetix Administration Objects.KFF(HG B911 PO BL SSY[UID-5035]).
Click Submit Requests. The Submit a New Request dialog box appears.
Click Single Request, and then click OK. The Submit Request dialog box appears.
In the Name box, search for the Concurrent Manager program, and then select the program.
In the Parameters dialog box, type the flexfield code or search for the flexfield code corresponding to the key flexfield for which you want to enable incremental refresh. By default, the parameter value is set to ALL. insigihtsoftwarerecommends enabling incremental refresh for all the key flexfields available in the NOETIX SYS schema at your site. Click OK to save the changes and close the Parameters dialog box.
insightsoftware recommends using either full or incremental refresh for all the key flexfields. If you enable incremental refresh for only some key flexfields, data corresponding to some key flexfields that are included in global views may not be synchronized.
Click Submit. If you have enabled incremental refresh for only one key flexfield in step 6 and if you want to enable incremental refresh for another key flexfield, click Yes, or else, click No. The incremental refresh will be enabled for the data cache tables corresponding to the specified key flexfields.
Using Oracle Key Flexfield Tables
When you use Oracle key flexfield tables to retrieve key flexfield data, you need to first configure your NoetixViews instance to retrieve key flexfield data directly from Oracle key flexfield tables using the no data cache option. The wnoetx gseg flex kff cols.sql script that is available with the product needs to be used to enable the no data cache option. This script is then called during Stage 4.
After you configure the no data cache option, you may notice some issues with the performance of the global views. To mitigate these performance issues, you need to create a function-based index for the key flexfield segments.
Enabling No Data Cache Option
To enable the no data cache option
Open wnoetx gseg flex kff cols.sql using a text editor.
On a Windows-based computer, the default path to the file is <installation folder>\Noetix Corporation\NoetixViews\Installs\<NOETIX SYS TNS>.
On a computer that is running Linux or UNIX, the wnoetx gseg flex kff cols.sql will be available under the directory where you have copied all the NoetixViews installation files and scripts.
Locate the following statement:
UPDATE n f kff flex sources fs
SET fs.zero latency = 'Y'
WHERE fs.id flex code = 'GL#'
AND fs.source type = 'DC';
COMMIT;The ID Flex Code column in the statement indicates the flex code value of the key flexfield. For example, the flex code value for the Accounting Flexfield will be “GL#”.
Copy the statement to the end of the file. In the place of “GL#” add the code of the key flexfield for which the no data cache option has to be enabled.
Note: To enable the no data cache option for multiple key flexfields, copy the statement multiple times and provide the code of the key flexfield in the place of “GL#”.
Save the changes, and then close the file.
Run Stage 4 to generate the views and complete the process of enabling the no data cache option.
Performance Tuning Considerations
When the no data cache option is enabled, the performance of Noetix views may be affected. To mitigate the performance issues, on the key flexfield source table, create a function-based index for the key flexfield segments that are frequently used as report filters.
To create function-based index
Depending on the operating system, do one of the following to start an SQL*Plus session:
On a Windows-based computer:
Log on to the NoetixViews Administrator.
On the Tools menu, click Support > SQL*Plus Prompt from the NoetixViews Administrator main window. The window for the SQL*Plus session will appear.
On a UNIX-based computer:
Change the present working directory to the server directory that contains the NoetixViews generation scripts.
To connect to the database as the Noetix System Administration User and to start an SQL*Plus session, run the following command at the command prompt.
sqlplus <username>/<password>@<dbconnectstring>
In the SQL*Plus session, run the i create dc value index.sql script. The list of key flexfields enabled with no data cache option will be listed as follows.
Note: The flexfield names listed will depend on the key flexfields for which the “no data cache” option is enabled.
Provide the sequence number corresponding to the key flexfield, and press ENTER. Then, the valid segments will be listed as follows.
Note: The columns listed will vary based on the selected key flexfield.
Type the name of the column for which the index has to be created, and press Enter. A function-based index script will be generated at <hard disk drive>:\ProgramFiles\NoetixCorporation\NoetixViews\
Installs\<NOETIX SYS TNS> and a message similar to the following will be displayed.Note: The name of the function-based index will depend upon the selected key flexfield. For example, the Accounting Flexfield will create the XXNTX GL ACCT FBI1 index.