Oracle® Enterprise Data Quality for Product Data Services for Excel Reference Guide Release 5.6.2 Part Number E23611-02 |
|
|
View PDF |
The Enterprise DQ for Product (EDQP) Services for Excel application is designed to provide enhanced, tailored, spreadsheet-based transformations of your data and is used in conjunction with your Oracle DataLens Server platform. Services for Excel enables you to make your corporate data business-ready using Microsoft Excel.
Services for Excel interfaces directly to the Oracle DataLens Server to execute Data Service Applications (DSAs). This is just one way that DSAs can be used to standardize, match, de-duplicate, and convert enterprise data.
The Services for Excel application is designed to leverage the power of the entire suite of products delivered in Enterprise DQ for Product. It is set of powerful tools and commands installed into Excel and accessed using the custom toolbar and context-sensitive menu. For installation information, see "Installing and Configuring Services for Excel".
The following briefly describes the Services for Excel toolbar buttons and menus from left to right:
Tip:
The tooltips appear when you rest your mouse pointer on a menu item, button, tab, icon, or similar content.The buttons that are on the toolbar are the most often used functions so are available for quick access; each button is also on one of the menus.
The Jobs button and associated menu items provide a set of convenient features for running, monitoring, and managing jobs.
Run a Job and retrieve the results
Executes a synchronous DSA job to immediately process data records, and returns the results. A job progress dialog is displayed during execution and you are notified when the job is complete. For more information, see "Running a DSA to Process Data Records". Submit Job Starts a background job using the configured DSA. After a job has been submitted, you can save the Excel workbook, close Excel, and then reopen the same workbook later to retrieve the job results. You can retrieve job results only once. |
|
Get Results
Retrieves the results from a submitted DSA job. If the job has not run to completion, a pop-up message informs you that the job is still running. If no job is running or is complete, then you are prompted for the job id to use when retrieving results. You can obtain the job id using the Job Status... option. A worksheet is automatically created for each DSA job run. |
|
Job Status…
Use to view the status and associated details of all your completed and currently executing jobs. For more information, see "Viewing the Job Status". |
|
Clear Job Results
Clears the data results across all output spreadsheets. All record data is deleted. The formatting for these spreadsheets is not affected. |
|
Remove All Result Worksheets
Removes all of the result worksheets created by retrieving DSA job results; the input worksheet is not affected. You are prompted to confirm the deletion of the output worksheets. |
|
Fill-in Line Item Ids
Analyzes the values in column A for each record to determine whether there is a valid unique id present. For more information, see "Unique Record Identifiers". |
|
Insert Line Item Id Column
Adds an identifier column to your input data. When selected, a new column A is inserted in front of all other existing columns, the column is titled Id, and a set of unique identifiers is added to this column. For more information, see "Unique Record Identifiers". |
|
Restore
Restores the source data worksheet to its prior state if a hidden backup worksheet is available. For more information, see "Backing Up Source Data". |
|
Reset
Removes all hidden backup worksheets and Data Services for Excel configuration worksheets. You are prompted to confirm the deletion of each these worksheets. For more information, see "Backing Up Source Data". |
The Test button and associated menu items provides a selection of utilities that you can use for regression testing and data comparison.
Create Regression Set
Used to create a regression set worksheet. The active worksheet is copied to a new worksheet with the same name as the active worksheet with a Compare Job Results Removes all regression test set worksheets in the active workbook that contain the |
|
Compare Worksheets
Used to identify the differences between the results of two different DSA jobs. |
|
Clear Regression Set Comparison
Clears all comparison highlighting on the active worksheet that was created by a regression test. |
|
Remove All Regression Sets
Removes all regression test set worksheets in the active workbook. |
|
Sampling
This menu contains the following two options: |
|
Create Key Record Sample
Takes the records found in the active worksheet and creates a new worksheet, with a |
|
Create Baseline Record Sample
Takes the records found in the active worksheet and creates a new worksheet, with a |
The Tools button and associated menu items provides a selection of tools that you can use to open other Enterprise DQ for Product applications, access AutoBuild and create associated reports, and set various Services for Excel options.
Load Meta-Data Files…
Used to consolidate like or similar metadata records from several different files into a single metadata file. For more information, see "Loading Metadata Files". AutoBuild… Used to create a new data lens from customer data schema information that is implicit or found in customer database exports. |
|
Typically, the export information is output in Excel spreadsheets and tab-separated files. This application is a step-by-step wizard that enables you to configure the key schema components: categories that can be converted to item definitions, columns, and labels that can be converted to item definition attributes, and record values that can be converted to valid attribute values captured as data lens phrases and terms. For more information, see "Using the AutoBuild Application". | |
Remove AutoBuild Reports…
Used to remove the three separate reports that provide detailed metric data and the semantic knowledge structure of a data lens created using the AutoBuild Application. For more information, see Oracle Enterprise Data Quality for Product Data AutoBuild Reference Guide. |
|
Set Transform Server...
Identifies the Oracle DataLens Server that will be used to transform data. For more information, see "Changing the Oracle DataLens Server". |
|
Switch User...
Allows you to change the user that is currently logged into the Oracle DataLens Transform Server and you can which server will transform your data. For more information, see "Logging into the Oracle DataLens Server". |
|
Job Options...
Configures how your source data will be processed and by which DSA. For more information, see "Configuring a DSA". |
|
Options...
Allows you to set options for global use in the Services for Excel. For more information, see "Configuring Application Options". |
Product Guide
Opens a list of Enterprise DQ for Product documents for your selection in a browser. |
|
Help About
Provides an informational message about the Services for Excel product. Use the Environment button to view and save (to a text file) information about your environment including versions, locale, application data path, user names, transform server, and the date. |
Services for Excel provides a context-sensitive menu to help automate the task of working with specific subsets of records on a spreadsheet. The following menu appears when you right-click an Excel cell or selection of cells:
Note:
If you select the entire worksheet, the Services for Excel context sensitive menu is not active.This menu is used as follows:
Submit Selected Records
Use this menu item to process the selected records with the currently selected DSA. All records with at least one cell in the active Excel region selected are processed. |
|
Get Job Results
The same as the Get Results option on the Jobs menu. For more information, see "Jobs Menu". |
|
Compare Selected Records
Compares just the selected records to the corresponding records in the regression test set. All records with at least one cell in the active Excel region selection are compared. |
|
Next Difference
Finds the next highlighted difference on the active worksheet. The highlighting indicates a difference in results between the regression base and the recently run data. |
|
Copy Cells to Regression Set
Copies the acceptable values found on the active worksheet to the regression set worksheet. The typical use is to copy the highlighted cells from a regression test to the regression set worksheet as progressions or valid results. All actively selected Excel cells are copied to the corresponding regression set. All comparison highlighting is removed from the copied cells. For more information, see "Using Regression Testing". |
|
Copy Like Cells to Regression Set
Copies values like the selected, highlighted cell found on the active worksheet to the regression set worksheet. If more than one cell is selected, only the first cell is used. All comparison highlighting is removed from the copied cells. For more information, see "Using Regression Testing". |
|
Jump to Worksheet Using Row Id
Finds the corresponding row in the target worksheet using the Id for the actively selected row. You are prompted to select the desired target worksheet. This feature can be very convenient if you need to move between worksheets containing different information about the same record. For example, use this feature when you want to move from a DSA output worksheet back to the original input record on another spreadsheet, or to find the corresponding record on the regression test worksheet. |
|
Clear Comparisons for Selected Cells
Clears all of the comparison highlighting found in the selected Excel cells. |
|
Group Records into Worksheets
Groups your records into a set of separate worksheets based on the values found in one of the input columns. For more information, see "Grouping Records". |
|
Fill in Columns
Completes a column of data based on the data in the column and adjacent cells. For more information, see "Filling Columns with Data". |
|
Character Analysis and Replacement
This menu contains the following two options: Analyze Character Set |
|
Analyzes the characters found in all the selected Excel cells and reports any control characters or Unicode spaces found. For more information, see "Character Analysis and Replacement". | |
Replace Control Characters
Replaces all control characters found in the selected Excel cells with spaces. For more information, see "Character Analysis and Replacement". |
|
Report Character Hex Values
Searches for all hex values in the selected Excel cells and reports them. For more information, see "Character Analysis and Replacement". |
|
Strip Embedded HTML Tags from Selected Cells
Removes all HTML text that are embedded and any control characters in the selected Excel cells. For more information, see"Character Analysis and Replacement". |