Using the Workbench Data Grid

The data grid includes two tabs in the main grid:

Note:

The information in this section is based on the Load Data tab. If you are using the Query Data tab and need specific information on a feature or field, refer to the documentation for the Load Data tab.

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 20. View Options and Descriptions

View OptionDescription
Image shows View drop down.Customizes views. Options include:

  • Table—Selects the source or target data to display in the grid including:

    • Source (All)—Shows both mapped and mapped 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.

  • Detach/Attach—Detaches columns from the data grid. Detached columns display in their own window. To return to the default view, select View, and then click Attach or click Close.

  • 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, if 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:

  1. Select the column to resize.

  2. From the table action bar, select Format, and then Resize.

    The Resize Column screen is displayed.

  3. In the first Width field, enter the value to resize by.

    You can select a column width from 1 to 1000.

  4. In the second Width field, select either pixel or percentage as the measure to resize by.

  5. Select OK.

  To wrap the text of a column:

  1. Select the column with the text to wrap.

  2. 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 and 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 associated with the location POV.

Importing from Source

The Import from Source feature enables the Oracle Data Integrator to import the data from the source system, performs the necessary transformation, such as import, 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 if:

  • You are running a data load rule for the first time.

  • Your data in the source system changed. For example, if 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 if it has not changed.

  To submit the data load rule:

  1. From the table action bar, select the Data Rule drop down, and choose the data load rule.

  2. Click Image shows Execute icon..

    The Execution Mode Option screen is displayed.

  3. 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 on the Import or Export hyperlink in the status region to navigate to the Process Detail page to monitor the ODI job progress.

  4. Click OK.

  5. 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. Select this option after you have reviewed the data in the data grid and are sure you want to export it to the target application.

When exporting data for Planning and Oracle Essbase, you can store, add, and subtract data, For Planning only, you can override all data. For Oracle Hyperion Financial Management, Fusion Edition, you can merge, accumulate and replace data.

  To submit the data load rule:

  1. From the table action bar, select the Data Rule drop down, and choose the data load rule.

  2. Click Image shows Execute icon.

    The Execution Mode Option screen is displayed.

  3. 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).

  4. Click OK.

  5. After you run a data rule, you can check the status of the export on the POV bar.

Viewing the Data Log

You can view a log of the data load or write back execution. Logs show the processes and errors that are created when source files are imported, or when the data is exported to the target application. For example, the log shows the status for each process step. You can troubleshoot a problem by viewing at which point a process failed.

Image shows View Log

  To view the data log, from the table action bar, click Image shows View Log icon.

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 icon.

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, if 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:

  1. From the table action bar, click Image shows Query by Example icon to enable the filter row.

    The filter row must appear above the columns to use this feature.

  2. 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:

  1. Select the column to freeze.

  2. from the table action bar, click Image shows Freeze icon.

  To unfreeze a column, select the frozen column and 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, from the table action bar, click Image shows Detach icon.

The data grid is displayed in a separate window.

  To reattach columns, from the table action bar, select View, and then 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 and click Image shows Wrap icon.