Using the Workbench Data Grid

The data grid includes two tabs in the main grid:

Load Data/Write-back Load (POV)—Use this tab to import, view and verify and export data from source systems.
Query Data—Use this tab to display historical data based on the Period and Categories POVs. The Period and Category are specified in the in the Table Action.

You perform tasks on the data grid by selecting options on the Table Action including:

Viewing Data

The following View drop-down options provides multiple ways to view data.

Table 35. View Options and Descriptions

View Option	Description
	Customizes views. Options include: Table—Selects the source or target data to display in the grid including: Source (All)—Shows both mapped and unmapped source dimensions (ENTITY, ACCOUNT, UD1, UD2,… AMOUNT). Source (Mapped)—Shows only mapped source dimensions. Target—Shows only target dimensions (ENTITYX, ACCOUNTX, UD1X, UD2X,….AMOUNTX). Source and Target—Shows both source and target dimensions (ENTITY, ENTITYX, ACCOUNT, ACCOUNTX, UD1, UD1X, AMOUNT, AMOUNTX). Columns—Selects the columns to display in the data grid including: Show All Entity Account Version Product Department STAT Amount PTD Amount YTD Amount Freeze/Unfreeze—Locks a column in place and keeps it visible when you scroll the data grid. The column heading must be selected to use the freeze option. To unfreeze a column, select the column and from the shortcut menu, select Unfreeze. Sort— Use to change the sort order of columns in ascending or descending order. A multiple level sort (up to three levels and in ascending and descending order) is available by selecting Sort, and then Advanced. From the Advanced Sort screen, select the primary “sort by” column, and then the secondary “then by” column, and then the third “then by” column. The search fields that display in the advanced search options differ depending on what artifact you are selecting. Reorder Columns—Use to change the order of the columns. When you select this option, the Reorder Columns screen is displayed. You can select a column and then use the scroll buttons on the right to change the column order. Query by Example—Use to toggle the filter row. You can use the filter row to enter text to filter the rows that display for a specific column. You can enter text to filter on, when available, for a specific column, and then press [Enter]. To clear a filter, remove the text to filter by in the text box, then press [Enter]. All text you enter is case sensitive.

View Option

Description

Customizes views. Options include:

Table—Selects the source or target data to display in the grid including:
- Source (All)—Shows both mapped and unmapped source dimensions (ENTITY, ACCOUNT, UD1, UD2,… AMOUNT).
- Source (Mapped)—Shows only mapped source dimensions.
- Target—Shows only target dimensions (ENTITYX, ACCOUNTX, UD1X, UD2X,….AMOUNTX).
- Source and Target—Shows both source and target dimensions (ENTITY, ENTITYX, ACCOUNT, ACCOUNTX, UD1, UD1X, AMOUNT, AMOUNTX).
Columns—Selects the columns to display in the data grid including:
- Show All
- Entity
- Account
- Version
- Product
- Department
- STAT
- Amount
- PTD Amount
- YTD Amount
Freeze/Unfreeze—Locks a column in place and keeps it visible when you scroll the data grid. The column heading must be selected to use the freeze option. To unfreeze a column, select the column and from the shortcut menu, select Unfreeze.
Sort— Use to change the sort order of columns in ascending or descending order. A multiple level sort (up to three levels and in ascending and descending order) is available by selecting Sort, and then Advanced. From the Advanced Sort screen, select the primary “sort by” column, and then the secondary “then by” column, and then the third “then by” column.
The search fields that display in the advanced search options differ depending on what artifact you are selecting.
Reorder Columns—Use to change the order of the columns. When you select this option, the Reorder Columns screen is displayed. You can select a column and then use the scroll buttons on the right to change the column order.
Query by Example—Use to toggle the filter row. You can use the filter row to enter text to filter the rows that display for a specific column. You can enter text to filter on, when available, for a specific column, and then press [Enter]. To clear a filter, remove the text to filter by in the text box, then press [Enter]. All text you enter is case sensitive.

Formatting Data

You can resize the width of a column either by the number pixel characters or a percentage. You can also wrap text for each cell automatically when text exceeds the column width.

To resize the width of a column:

Select the column to resize.
From the table action bar, select Format, and then Resize.
The Resize Column screen is displayed.
In the first Width field, enter the value to resize by.
You can select a column width from 1 to 1000.
In the second Width field, select either pixel or percentage as the measure to resize by.
Select OK.

To wrap the text of a column:

Select the column with the text to wrap.
From the table action bar, select Format, and then Wrap.

Showing Data

You can select the type of data to display in the data grid including:

Valid Data—Data that was mapped properly and is exported to the target application.
Invalid Data—One or more dimensions that was not mapped correctly and as a result, the data is not exported to target.
Ignored Data—User defined explicit IGNORE maps to ignore while exporting to target. IGNORE maps are defined in the member mapping by assigning a special target member of IGNORE.
All Data—Shows all valid, invalid and ignored data.

To show a type of data, select Show.

Select Show.
Choose either Valid Data: Invalid Data, Ignored Data, or All Data.

Selecting Write-Back Rules

To select another write-back rule:

From the Write-Back Rule drop-down, select another write-back rule associated with the location POV.
Click OK.

Importing from a Planning or Essbase Cube Source

The Import from Source feature enables the Oracle Data Integrator to import the data from a Planning or Essbase Cube source system, performs the necessary transformation, such as import, and map and validate the data. The Import from Source features also allows you to import the source either online (immediate processing) or offline (runs in background).

Select this feature only when:

You are running a data load rule for the first time.
Your data in the source system changed. For example, when you reviewed the data in the staging table after the export, and it was necessary to modify data in the source system.

In many cases, source system data may not change after you import the data from the source the first time. Therefore, it is not necessary to keep importing the data when it has not changed.

To submit the data load rule:

From the table action bar, select the Data Rule drop-down, and choose the data load rule.
Click .
The Execution Mode Option screen is displayed.
From the Execution Mode drop-down, select the mode of importing the source.
Execution modes include:
- online—ODI processes the data in sync mode (immediate processing).
- offline—ODI processes the data in async mode (runs in background).
  Click to navigate to the Process Detail page to monitor the ODI job progress.
Click OK.
After you run a data rule, check the status of the import on the POV bar.

Exporting to Target

Use the Export to Target feature to export data to a target application, which is the ERP application. Select this option after you have reviewed the data in the data grid and need to export it to the target application.

Export options:

Current Dimension
All Dimensions
Export to Excel

When exporting data for Essbase, you can store, add, and subtract data.

When exporting data for Planning, the following options are available:

Store Data—Inserts the data from the source or file into the target application. This replaces any value that currently exists.
Add Data—This adds the value from the source or file to the value that exists in the target application. For example, when you have 100 in the source, and 200 in the target, then the result is 300.
Subtract Data—This subtracts the value in the source or file from the value that exists in the target application. For example, when you have 300 in the target, and 100 in the source, then the result is 200.
Override All Data—Clears all data in the target, and then loads from the source or file. For example when you have a year of data in your planning application, but are only loading a single month, this option clears the entire year before performing the load.

To submit the data load rule:

From the table action bar, select the Data Rule drop-down, and choose the data load rule.
Click .
The Execution Mode Option screen is displayed.
From the Execution Mode drop-down, select the mode of exporting to the target.
Execution modes include:
- online—ODI processes the data in sync mode (immediate processing).
- offline—ODI processes the data in async mode (runs in background).
  Click to navigate to the Process Detail page to monitor the ODI job progress.
From the Export drop-down, select the export method: either Current Dimension or All Dimensions.
Export options:
- Current Dimension
- All Dimensions
- Export to Excel
For Current Dimension and All Dimensions export methods, in Select file location, navigate to the file to export and click OK.
For the Export to Excel method, mappings are export to a Microsoft Excel spreadsheet.
Click OK.
After you run a data rule, you can check the status of the export on the POV bar.

Opening Write Back Rules

When reviewing data in the workbench, users can drill down from the amount to ERP source system. In the source system the data is displayed in the granularity with which it was loaded.

You can open the data load rules for a source system application in Microsoft Excel and review how the data is defined.

To open the data load rules, from the table action bar, click Image shows Open Rule button .

You are prompted to open the rule in Microsoft Excel. You can specify another program in which to open the rule by selecting the “Open with” option and selecting the program.

Querying by Example

Use the Query by Example feature to filter rows that display for a specific column. You can enter text to filter on, when available, for a specific column, and then press [Enter]. To clear a filter, remove the text to filter by in the text box, then press [Enter]. All text you enter is case sensitive.

To query by example:

From the table action bar, click to enable the filter row.
The filter row must appear above the columns to use this feature.

Enter the text by which to filter the values in the column and press [Enter].

Note:

When entering text to filter, the text or partial text you enter is case sensitive. The case must match exactly. For example, to find all target applications prefixed with “HR,” you cannot enter “Hr” or “hr.”

Freezing Data

Use the Freeze feature to lock a column in place and keeps it visible when you scroll the data grid.

To freeze a column:

Select the column to freeze.
from the table action bar, click .

To unfreeze a column:

Select the frozen column.
On the shortcut menu, select Unfreeze.

Detaching Data

Use the Detach feature to detach column from the data grid, When you detach the grid, columns display in their own window. To return to the default view, select View, and then click Attach or click the Close button.

To detach columns:

Select the column.
From the table action bar, click

To reattach columns

Select the table.
From the table action bar, select View, and then, select Attach.

Wrapping Text

You can wrap text for each cell automatically when text exceeds the column width.

To wrap text for a column:

Select the column.
Click .