1 Introduction

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

Toolbar and Menus Explained

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.

Jobs Menu

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

Test Menu

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 _RSET suffix. Compare Job Results Removes all regression test set worksheets in the active workbook that contain the _RSET suffix.
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 _KSET suffix, containing a random sample of records. For more information, see "Using Data Sampling".
Create Baseline Record Sample Takes the records found in the active worksheet and creates a new worksheet, with a _BSET suffix, containing a statistically significant random sample of records. For more information, see "Using Data Sampling".

DataLens Tools Menu

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

DataLens Help Menu

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.

Excel Cell Context-Sensitive Menu

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