DQ 100 Excel UserGuide en
DQ 100 Excel UserGuide en
DQ 100 Excel UserGuide en
(Version 10.0)
Informatica Data Quality for Microsoft Excel User and Developer Guide
Version 10.0
November 2015
Copyright (c) 1993-2015 Informatica LLC. All rights reserved.
This software and documentation contain proprietary information of Informatica LLC and are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright law. Reverse engineering of the software is prohibited. No part of this document may be reproduced or transmitted in any
form, by any means (electronic, photocopying, recording or otherwise) without prior consent of Informatica LLC. This Software may be protected by U.S. and/or
international Patents and other Patents Pending.
Use, duplication, or disclosure of the Software by the U.S. Government is subject to the restrictions set forth in the applicable software license agreement and as
provided in DFARS 227.7202-1(a) and 227.7702-3(a) (1995), DFARS 252.227-7013(1)(ii) (OCT 1988), FAR 12.212(a) (1995), FAR 52.227-19, or FAR 52.227-14
(ALT III), as applicable.
The information in this product or documentation is subject to change without notice. If you find any problems in this product or documentation, please report them to us
in writing.
Informatica, Informatica Platform, Informatica Data Services, PowerCenter, PowerCenterRT, PowerCenter Connect, PowerCenter Data Analyzer, PowerExchange,
PowerMart, Metadata Manager, Informatica Data Quality, Informatica Data Explorer, Informatica B2B Data Transformation, Informatica B2B Data Exchange Informatica
On Demand, Informatica Identity Resolution, Informatica Application Information Lifecycle Management, Informatica Complex Event Processing, Ultra Messaging and
Informatica Master Data Management are trademarks or registered trademarks of Informatica LLC in the United States and in jurisdictions throughout the world. All
other company and product names may be trade names or trademarks of their respective owners.
Portions of this software and/or documentation are subject to copyright held by third parties, including without limitation: Copyright DataDirect Technologies. All rights
reserved. Copyright Sun Microsystems. All rights reserved. Copyright RSA Security Inc. All Rights Reserved. Copyright Ordinal Technology Corp. All rights
reserved.Copyright Aandacht c.v. All rights reserved. Copyright Genivia, Inc. All rights reserved. Copyright Isomorphic Software. All rights reserved. Copyright Meta
Integration Technology, Inc. All rights reserved. Copyright Intalio. All rights reserved. Copyright Oracle. All rights reserved. Copyright Adobe Systems
Incorporated. All rights reserved. Copyright DataArt, Inc. All rights reserved. Copyright ComponentSource. All rights reserved. Copyright Microsoft Corporation. All
rights reserved. Copyright Rogue Wave Software, Inc. All rights reserved. Copyright Teradata Corporation. All rights reserved. Copyright Yahoo! Inc. All rights
reserved. Copyright Glyph & Cog, LLC. All rights reserved. Copyright Thinkmap, Inc. All rights reserved. Copyright Clearpace Software Limited. All rights
reserved. Copyright Information Builders, Inc. All rights reserved. Copyright OSS Nokalva, Inc. All rights reserved. Copyright Edifecs, Inc. All rights reserved.
Copyright Cleo Communications, Inc. All rights reserved. Copyright International Organization for Standardization 1986. All rights reserved. Copyright ejtechnologies GmbH. All rights reserved. Copyright Jaspersoft Corporation. All rights reserved. Copyright International Business Machines Corporation. All rights
reserved. Copyright yWorks GmbH. All rights reserved. Copyright Lucent Technologies. All rights reserved. Copyright (c) University of Toronto. All rights reserved.
Copyright Daniel Veillard. All rights reserved. Copyright Unicode, Inc. Copyright IBM Corp. All rights reserved. Copyright MicroQuill Software Publishing, Inc. All
rights reserved. Copyright PassMark Software Pty Ltd. All rights reserved. Copyright LogiXML, Inc. All rights reserved. Copyright 2003-2010 Lorenzi Davide, All
rights reserved. Copyright Red Hat, Inc. All rights reserved. Copyright The Board of Trustees of the Leland Stanford Junior University. All rights reserved. Copyright
EMC Corporation. All rights reserved. Copyright Flexera Software. All rights reserved. Copyright Jinfonet Software. All rights reserved. Copyright Apple Inc. All
rights reserved. Copyright Telerik Inc. All rights reserved. Copyright BEA Systems. All rights reserved. Copyright PDFlib GmbH. All rights reserved. Copyright
Orientation in Objects GmbH. All rights reserved. Copyright Tanuki Software, Ltd. All rights reserved. Copyright Ricebridge. All rights reserved. Copyright Sencha,
Inc. All rights reserved. Copyright Scalable Systems, Inc. All rights reserved. Copyright jQWidgets. All rights reserved. Copyright Tableau Software, Inc. All rights
reserved. Copyright MaxMind, Inc. All Rights Reserved. Copyright TMate Software s.r.o. All rights reserved. Copyright MapR Technologies Inc. All rights reserved.
Copyright Amazon Corporate LLC. All rights reserved. Copyright Highsoft. All rights reserved. Copyright Python Software Foundation. All rights reserved.
Copyright BeOpen.com. All rights reserved. Copyright CNRI. All rights reserved.
This product includes software developed by the Apache Software Foundation (http://www.apache.org/), and/or other software which is licensed under various versions
of the Apache License (the "License"). You may obtain a copy of these Licenses at http://www.apache.org/licenses/. Unless required by applicable law or agreed to in
writing, software distributed under these Licenses is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
implied. See the Licenses for the specific language governing permissions and limitations under the Licenses.
This product includes software which was developed by Mozilla (http://www.mozilla.org/), software copyright The JBoss Group, LLC, all rights reserved; software
copyright 1999-2006 by Bruno Lowagie and Paulo Soares and other software which is licensed under various versions of the GNU Lesser General Public License
Agreement, which may be found at http:// www.gnu.org/licenses/lgpl.html. The materials are provided free of charge by Informatica, "as-is", without warranty of any
kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose.
The product includes ACE(TM) and TAO(TM) software copyrighted by Douglas C. Schmidt and his research group at Washington University, University of California,
Irvine, and Vanderbilt University, Copyright () 1993-2006, all rights reserved.
This product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit (copyright The OpenSSL Project. All Rights Reserved) and
redistribution of this software is subject to terms available at http://www.openssl.org and http://www.openssl.org/source/license.html.
This product includes Curl software which is Copyright 1996-2013, Daniel Stenberg, <daniel@haxx.se>. All Rights Reserved. Permissions and limitations regarding this
software are subject to terms available at http://curl.haxx.se/docs/copyright.html. Permission to use, copy, modify, and distribute this software for any purpose with or
without fee is hereby granted, provided that the above copyright notice and this permission notice appear in all copies.
The product includes software copyright 2001-2005 () MetaStuff, Ltd. All Rights Reserved. Permissions and limitations regarding this software are subject to terms
available at http://www.dom4j.org/ license.html.
The product includes software copyright 2004-2007, The Dojo Foundation. All Rights Reserved. Permissions and limitations regarding this software are subject to
terms available at http://dojotoolkit.org/license.
This product includes ICU software which is copyright International Business Machines Corporation and others. All rights reserved. Permissions and limitations
regarding this software are subject to terms available at http://source.icu-project.org/repos/icu/icu/trunk/license.html.
This product includes software copyright 1996-2006 Per Bothner. All rights reserved. Your right to use such materials is set forth in the license which may be found at
http:// www.gnu.org/software/ kawa/Software-License.html.
This product includes OSSP UUID software which is Copyright 2002 Ralf S. Engelschall, Copyright 2002 The OSSP Project Copyright 2002 Cable & Wireless
Deutschland. Permissions and limitations regarding this software are subject to terms available at http://www.opensource.org/licenses/mit-license.php.
This product includes software developed by Boost (http://www.boost.org/) or under the Boost software license. Permissions and limitations regarding this software are
subject to terms available at http:/ /www.boost.org/LICENSE_1_0.txt.
This product includes software copyright 1997-2007 University of Cambridge. Permissions and limitations regarding this software are subject to terms available at
http:// www.pcre.org/license.txt.
This product includes software copyright 2007 The Eclipse Foundation. All Rights Reserved. Permissions and limitations regarding this software are subject to terms
available at http:// www.eclipse.org/org/documents/epl-v10.php and at http://www.eclipse.org/org/documents/edl-v10.php.
This product includes software licensed under the terms at http://www.tcl.tk/software/tcltk/license.html, http://www.bosrup.com/web/overlib/?License, http://
www.stlport.org/doc/ license.html, http://asm.ow2.org/license.html, http://www.cryptix.org/LICENSE.TXT, http://hsqldb.org/web/hsqlLicense.html, http://
httpunit.sourceforge.net/doc/ license.html, http://jung.sourceforge.net/license.txt , http://www.gzip.org/zlib/zlib_license.html, http://www.openldap.org/software/release/
license.html, http://www.libssh2.org, http://slf4j.org/license.html, http://www.sente.ch/software/OpenSourceLicense.html, http://fusesource.com/downloads/licenseagreements/fuse-message-broker-v-5-3- license-agreement; http://antlr.org/license.html; http://aopalliance.sourceforge.net/; http://www.bouncycastle.org/licence.html;
http://www.jgraph.com/jgraphdownload.html; http://www.jcraft.com/jsch/LICENSE.txt; http://jotm.objectweb.org/bsd_license.html; . http://www.w3.org/Consortium/Legal/
2002/copyright-software-20021231; http://www.slf4j.org/license.html; http://nanoxml.sourceforge.net/orig/copyright.html; http://www.json.org/license.html; http://
forge.ow2.org/projects/javaservice/, http://www.postgresql.org/about/licence.html, http://www.sqlite.org/copyright.html, http://www.tcl.tk/software/tcltk/license.html, http://
www.jaxen.org/faq.html, http://www.jdom.org/docs/faq.html, http://www.slf4j.org/license.html; http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/License; http://
www.keplerproject.org/md5/license.html; http://www.toedter.com/en/jcalendar/license.html; http://www.edankert.com/bounce/index.html; http://www.net-snmp.org/about/
license.html; http://www.openmdx.org/#FAQ; http://www.php.net/license/3_01.txt; http://srp.stanford.edu/license.txt; http://www.schneier.com/blowfish.html; http://
www.jmock.org/license.html; http://xsom.java.net; http://benalman.com/about/license/; https://github.com/CreateJS/EaselJS/blob/master/src/easeljs/display/Bitmap.js;
http://www.h2database.com/html/license.html#summary; http://jsoncpp.sourceforge.net/LICENSE; http://jdbc.postgresql.org/license.html; http://
protobuf.googlecode.com/svn/trunk/src/google/protobuf/descriptor.proto; https://github.com/rantav/hector/blob/master/LICENSE; http://web.mit.edu/Kerberos/krb5current/doc/mitK5license.html; http://jibx.sourceforge.net/jibx-license.html; https://github.com/lyokato/libgeohash/blob/master/LICENSE; https://github.com/hjiang/jsonxx/
blob/master/LICENSE; https://code.google.com/p/lz4/; https://github.com/jedisct1/libsodium/blob/master/LICENSE; http://one-jar.sourceforge.net/index.php?
page=documents&file=license; https://github.com/EsotericSoftware/kryo/blob/master/license.txt; http://www.scala-lang.org/license.html; https://github.com/tinkerpop/
blueprints/blob/master/LICENSE.txt; http://gee.cs.oswego.edu/dl/classes/EDU/oswego/cs/dl/util/concurrent/intro.html; https://aws.amazon.com/asl/; https://github.com/
twbs/bootstrap/blob/master/LICENSE; https://sourceforge.net/p/xmlunit/code/HEAD/tree/trunk/LICENSE.txt; https://github.com/documentcloud/underscore-contrib/blob/
master/LICENSE, and https://github.com/apache/hbase/blob/master/LICENSE.txt.
This product includes software licensed under the Academic Free License (http://www.opensource.org/licenses/afl-3.0.php), the Common Development and Distribution
License (http://www.opensource.org/licenses/cddl1.php) the Common Public License (http://www.opensource.org/licenses/cpl1.0.php), the Sun Binary Code License
Agreement Supplemental License Terms, the BSD License (http:// www.opensource.org/licenses/bsd-license.php), the new BSD License (http://opensource.org/
licenses/BSD-3-Clause), the MIT License (http://www.opensource.org/licenses/mit-license.php), the Artistic License (http://www.opensource.org/licenses/artisticlicense-1.0) and the Initial Developers Public License Version 1.0 (http://www.firebirdsql.org/en/initial-developer-s-public-license-version-1-0/).
This product includes software copyright 2003-2006 Joe WaInes, 2006-2007 XStream Committers. All rights reserved. Permissions and limitations regarding this
software are subject to terms available at http://xstream.codehaus.org/license.html. This product includes software developed by the Indiana University Extreme! Lab.
For further information please visit http://www.extreme.indiana.edu/.
This product includes software Copyright (c) 2013 Frank Balluffi and Markus Moeller. All rights reserved. Permissions and limitations regarding this software are subject
to terms of the MIT license.
See patents at https://www.informatica.com/legal/patents.html.
DISCLAIMER: Informatica LLC provides this documentation "as is" without warranty of any kind, either express or implied, including, but not limited to, the implied
warranties of noninfringement, merchantability, or use for a particular purpose. Informatica LLC does not warrant that this software or documentation is error free. The
information provided in this software or documentation may include technical inaccuracies or typographical errors. The information in this software and documentation is
subject to change at any time without notice.
NOTICES
This Informatica product (the "Software") includes certain drivers (the "DataDirect Drivers") from DataDirect Technologies, an operating company of Progress Software
Corporation ("DataDirect") which are subject to the following terms and conditions:
1. THE DATADIRECT DRIVERS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT.
2. IN NO EVENT WILL DATADIRECT OR ITS THIRD PARTY SUPPLIERS BE LIABLE TO THE END-USER CUSTOMER FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, CONSEQUENTIAL OR OTHER DAMAGES ARISING OUT OF THE USE OF THE ODBC DRIVERS, WHETHER OR NOT
INFORMED OF THE POSSIBILITIES OF DAMAGES IN ADVANCE. THESE LIMITATIONS APPLY TO ALL CAUSES OF ACTION, INCLUDING, WITHOUT
LIMITATION, BREACH OF CONTRACT, BREACH OF WARRANTY, NEGLIGENCE, STRICT LIABILITY, MISREPRESENTATION AND OTHER TORTS.
Part Number: DQ-XLG-10000-0001
Table of Contents
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Informatica Resources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Informatica My Support Portal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Informatica Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Informatica Product Availability Matrixes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Informatica Web Site. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Informatica How-To Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Informatica Knowledge Base. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Informatica Support YouTube Channel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Informatica Marketplace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Informatica Velocity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Informatica Global Customer Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Chapter 1: Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Data Quality for Microsoft Excel Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Data Quality Service Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Chapter 2: Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Prerequisites for Excel Users. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Prerequisites for Data Quality Developers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Installing Data Quality for Microsoft Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Table of Contents
Input Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Using the Data Quality Service. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Output Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Table of Contents
Preface
The Data Quality for Microsoft Excel Guide is written for Excel users and data quality developers. This guide
assumes that Excel users are familiar with Microsoft Excel. This guide also assumes that data quality
developers are familiar with creating data quality mappings and web services.
Informatica Resources
Informatica My Support Portal
As an Informatica customer, the first step in reaching out to Informatica is through the Informatica My Support
Portal at https://mysupport.informatica.com. The My Support Portal is the largest online data integration
collaboration platform with over 100,000 Informatica customers and partners worldwide.
As a member, you can:
Search the Knowledge Base, find product documentation, access how-to documents, and watch support
videos.
Find your local Informatica User Group Network and collaborate with your peers.
Informatica Documentation
The Informatica Documentation team makes every effort to create accurate, usable documentation. If you
have questions, comments, or ideas about this documentation, contact the Informatica Documentation team
through email at infa_documentation@informatica.com. We will use your feedback to improve our
documentation. Let us know if we can contact you regarding your comments.
The Documentation team updates documentation as needed. To get the latest documentation for your
product, navigate to Product Documentation from https://mysupport.informatica.com.
Informatica Marketplace
The Informatica Marketplace is a forum where developers and partners can share solutions that augment,
extend, or enhance data integration implementations. By leveraging any of the hundreds of solutions
available on the Marketplace, you can improve your productivity and speed up time to implementation on
your projects. You can access Informatica Marketplace at http://www.informaticamarketplace.com.
Informatica Velocity
You can access Informatica Velocity at https://mysupport.informatica.com. Developed from the real-world
experience of hundreds of data management projects, Informatica Velocity represents the collective
knowledge of our consultants who have worked with organizations from around the world to plan, develop,
deploy, and maintain successful data management solutions. If you have questions, comments, or ideas
about Informatica Velocity, contact Informatica Professional Services at ips@informatica.com.
Preface
The telephone numbers for Informatica Global Customer Support are available from the Informatica web site
at http://www.informatica.com/us/services-and-training/support-services/global-support-centers/.
Preface
CHAPTER 1
Overview
This chapter includes the following topics:
An Excel user identifies data quality requirements including information about input data, the types of
data quality operations required, and the expected output.
2.
3.
The developer creates the data quality service and provides the Excel user with a URL or file path for the
web service.
4.
The Excel user adds the URL or file path to Data Quality for Microsoft Excel to store a link to the service.
10
5.
The Excel user selects Microsoft Excel data to use as input for the data quality service and sets the
service output options.
6.
7.
Chapter 1: Overview
CHAPTER 2
Installation
This chapter includes the following topics:
Visual Studio Tools for the Microsoft Office system (version 3.0 Runtime Service Pack 1)
If the installer does not detect these components, it downloads them from Microsoft websites. The computer
must have Internet access for the installer to download the components.
11
12
1.
2.
Download and unzip the Data Quality for Microsoft Excel installer.
3.
Run Setup.exe.
4.
Open Excel.
5.
Chapter 2: Installation
CHAPTER 3
Run a Service, 14
Service Review, 21
Troubleshooting, 21
Action
Use
Add
13
Command
Action
Remove
Settings
Define settings such as default WSDL directory, the number of Excel inputs to send in one batch,
service run-time options. You can also import or export Data Quality for Microsoft Excel properties.
Review
Review the results of the last data quality service that you ran.
Help
You can use the Add command in the Informatica ribbon to add a URL or multiple WSDL files.
In the Settings window, you can add all the WSDL files in a directory that you specify.
You can also remove a data quality service from the list of available services.
Run a Service
When you run a service, you choose the service to run against the data in the current worksheet. Choose to
use columns or rows for inputs or outputs. You can also choose to create a worksheet for output data.
14
Undo
Removes the results of the data quality service operation from the Microsoft Excel worksheet.
Column input format
Reads data from input fields using a column orientation, reading from top to bottom.
Row input format
Reads data from input fields using a row orientation, reading from left to right.
Autofill input fields
Fills the input fields using the fields, rows, and columns selected in the Microsoft Excel worksheet. You
can fill multiple input fields by selecting cell ranges with more than one column or row, or by selecting
multiple areas in the worksheet.
Clear input fields
Resets all input fields.
Column output format
Writes data in output fields using a column orientation, writing from top to bottom.
Row output format
Writes data in output fields using a row orientation, writing from left to right.
Autofill output fields
Fills the output fields using the fields, rows, and columns selected in the Microsoft Excel worksheet. You
can fill multiple output fields by selecting cell ranges with more than one column or row, or by selecting
multiple areas in the worksheet.
Clear output fields
Resets all output fields.
Description
ALT + A
When any field in the Input section has keyboard focus, this shortcut fills all input fields
using the cell ranges highlighted in the Excel worksheet.
When any field in the Output section has keyboard focus, this shortcut fills all output fields
using the cell ranges highlighted in the Excel worksheet.
ALT + S
Populates the currently selected input or output field with the range selected in the Excel
worksheet.
ALT + C
ALT + D
Swaps the value in the currently selected field with the value in the next field.
ALT + U
Swaps the value in the currently selected field with the value in the preceding field.
ENTER
Run a Service
15
Selection Order
The Autofill function fills service fields based on the order that you select ranges in the worksheet.
The following figure shows the correlation between the selection order of Excel ranges and input fields in the
Use Service window:
The highlighted blue areas in the Excel worksheet indicate the cell ranges that you select. The numbers in
the Excel worksheet indicate the order that you select the ranges. The numbers in the Use Service window
indicate the order in which the Data Quality for Excel plug-in populates the input fields.
16
If you click the Autofill button in the Input section of the Use Service window, the plug-in populates the input
fields with the cell ranges indicated by the corresponding numbers. The Autofill button is indicated by the red
circle in the figure.
Unaligned Selections
You can select unaligned cell ranges to populate multiple input and output fields. Selections are unaligned if
the cell ranges do not use the same rows or columns for the start or end of the range.
The following figure shows the correlation between unaligned selections in a worksheet and output fields in
the Use Service window:
The highlighted blue areas in the Excel worksheet indicate the cell ranges that you select. The numbers in
the Excel worksheet indicate the order that you select the ranges. The numbers in the Use Service window
indicate the order in which the Data Quality for Excel plug-in populates the input fields.
If you click the Autofill button in the Input section of the Use Service window, the plug-in populates the input
fields with the cell ranges indicated by the corresponding numbers. The Autofill button is indicated by the red
circle in the figure.
Run a Service
17
The highlighted blue area in the Excel worksheet indicates columns that you select. The numbers in the Excel
worksheet indicate the order that you select the columns. The numbers in the Use Service window indicate
the order in which the Data Quality for Excel plug-in populates the input fields.
If you click the Autofill button in the Input section of the Use Service window, the plug-in populates the input
fields with the column ranges from your selection. The Autofill button is indicated by the red circle in the
figure.
When you select a column or row header, Data Quality for Excel uses the full column range. For example, if
you select the header for Column C and click Autofill, Data Quality for Excel writes C:C into the currently
selected service field. However, when the Data Quality for Excel plug-in runs a service, the plug-in
recalculates the range in service fields that use full columns or rows. The plug-in locates the last populated
cell in the row or column and updates the field so that the range uses the last populated cell as an endpoint.
For example, if the last populated cell is C28, then the plug-in updates the field to read C1:C28.
18
The highlighted blue area in the Excel worksheet indicates the cell range that you select. The numbers in the
Use Service window indicate the order in which the Data Quality for Excel plug-in populates the input fields.
If you click the Autofill button in the Input section of the Use Service window, the plug-in populates the input
fields with the cell ranges from your selection. The Autofill button is indicated by the red circle in the figure.
2.
3.
4.
Enter cell ranges in the data quality service input and output fields. You can click Autofill to fill the fields
with cell ranges that you select in the Excel worksheet.
5.
Click Run.
The data quality service processes the data and writes the results to the Microsoft Excel worksheet.
Run a Service
19
20
Service Review
You can review the service results when you troubleshoot Data Quality for Microsoft Excel.
The Review window displays the results of the last service used by Data Quality for Microsoft Excel. You can
also save a log file that you can send to a developer.
Troubleshooting
I cannot add a data quality service.
To add a data quality service, the service must be a valid Informatica web service that Data Quality for Excel
can run. Contact a developer to verify that Data Quality for Excel can run the service.
A data quality service that performs address validation does not write output data.
When you run an address validation service, the server machine loads address validation reference data.
Loading this reference data can take a lengthy amount of time because the a reference data set can often be
several gigabytes in size.
If the Data Quality for Microsoft Excel plug-in does not receive a response from a data quality service in a
timely manner, the plug-in processes the next batch of records without writing output data. If the service does
not respond before all record batches are sent, Data Quality for Microsoft Excel does not write any output
data.
If an address validation service does not write output data for one or more records, run the service again for
those records.
The computer you are using does not have access to the application service where the data quality
service is deployed.
Service Review
21
22
You populate an input or output field with a range is not available in an Excel worksheet.
The data quality service has multiple input or output fields and you populate the fields with ranges that
contain a different number of cells.
CHAPTER 4
Input Data, 24
Output Data, 25
Street Address
Locality
State
ZIP Code
Country
You inform the developer that you require the output data to be validated and formatted in three address
lines.
The developer creates a data quality service that analyzes addresses and produces validated and formatted
data. The developer provides you with the URL for this service.
You add the service to Data Quality for Microsoft Excel and run the service against customer address data in
Excel worksheets.
The data quality service in this example analyzes address data and generates formatted postal addresses
that the U.S. Postal Service certifies as deliverable addresses.
23
Input Data
The input data records are customer records that contain fields for street address, locality, state, ZIP Code,
and country.
The following table shows the input data records:
Street Address
E1022 COUNTY RD P
2009 N 16TH ST 14
222 Red School Lane
8 Greenway Plaza Suite 910
5757 Underwood Rd
25 Atlantic Ave.
PO Box 20
R3 BX 112
497 E HWY 70
16924 18TH ST SE
Locality
ELMWOOD
BISMARCK
Phillipsburg
Houston
Pasadena
Erlanger
Muncie
MADISON
GRANDSBURG
GARDNER
State
WI
ND
NJ
TX
TX
KY
IN
SD
WI
ND
ZIP Code
54740
58501
8865
77046
77507
41018
61857
57042
54840
58036
Country
USA
USA
USA
USA
USA
USA
USA
USA
USA
USA
Populate the input fields with the corresponding range of data in the worksheet. When you run the service,
the data quality service analyzes the input data and generates validated addresses.
24
Output Data
The output data records are columns of formatted address data. The U.S. Postal Service certifies these
addresses as deliverable postal addresses.
The following table shows the output data records:
Address1
E1022 COUNTY RD P
2009 N 16TH ST APT 14
222 RED SCHOOL LN
8 GREENWAY PLZ STE 910
5757 UNDERWOOD RD
25 ATLANTIC AVE
PO BOX 20
R3 BX 112
497 E HWY 70
16924 18TH ST SE
Address2
ELMWOOD WI 54740
BISMARCK ND 58501-2058
PHILLIPSBURG NJ 08865-2219
HOUSTON TX 77046-0892
PASADENA TX 77507-1031
ERLANGER KY 41018-3151
MUNCIE IL 61857-0020
MADISON SD 57042
GRANDSBURG WI 54840
GARDNER ND 58036-9737
Address3
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
UNITED STATES
Output Data
25
CHAPTER 5
26
The service runs on an Informatica 9.1.0 or later web service or on a PowerCenter 8.6.1 or later Web
Services Hub.
If a web service contains multiple inputs, use a parent element to group the input elements.
Set "Maximum Occurrences" to "unbounded" to allow the web service to process multiple records in one
batch. For web services with multiple inputs, set the "Maximum Occurrences" property for the parent
element to "unbounded." For web services with a single input, set the "Maximum Occurrences" property
for the input element to "unbounded."
27
2.
In the Developer tool, create a data quality mapping in the Model repository service.
3.
4.
5.
6.
7.
Click Finish.
8.
9.
10.
11.
12.
Open the mapping operation tab for the data service. Paste the copied objects into the mapping
operation.
13.
28
1.
2.
Export a data quality mapping from the Developer tool to the PowerCenter repository as a mapplet.
3.
4.
In the PowerCenter Developer, create a web service mapping using the web service source, target, and
the contents of the imported data quality mapping.
5.
In the PowerCenter Workflow Manager, create a workflow for the web service mapping
6.
In the PowerCenter Workflow Manager, configure the web services options for the workflow.
2.
3.
4.
5.
In the Properties pane, copy the URL located in the WSDL URL property.
6.
2.
Click the Service URL in the Properties tab to open the PowerCenter Web Services Hub.
3.
In the Web Services Hub tree view, select a web service type.
4.
5.
6.
7.
29
Index
A
Autofill function
column ranges 17
multiple ranges 18
selection order 16
unaligned cell ranges 17
C
cell ranges
selecting unaligned 17
column ranges
Autofill 17
commands
Informatica ribbon 13
D
data quality services
adding 14, 21
copying web service hub URLs 29
copying web service URLs 29
creating 9
Data Integration service 28
deploying 27
example overview 23
input fields 16
installing 12
optimizing 27
output fields 16
overview 9
removing 14
requirements 27
reusing settings 20
reviewing results 21
running 19, 21
saving WSDL files 29
troubleshooting 21
usage example 24
using 9
using PowerCenter 28
Web Service Hub 28
Web Services option 28
I
Informatica ribbon
commands 13
input data records
example 24
input fields
Autofill 16
30
K
keyboard shortcuts
Use Service window 15
M
missing output
troubleshooting 21
O
output data records
example 25
output fields
Autofill 16
column ranges 17
example 25
multiple ranges 18
unaligned cell ranges 17
P
prerequisites
data quality developers 11
Excel users 11
properties
Review window 21
Settings window 20
Use Service window 14
R
results
reviewing 21
Review window
properties 21
S
selection order
Autofill function 16
column ranges 17
multiple ranges 18
unaligned cell ranges 17
settings
exporting 20
importing 20
Settings window
properties 20
W
web service
overview 9
WSDL files
saving 29
storing 27
U
unaligned cell ranges
Autofill 17
Use Service window
keyboard shortcuts 15
Index
31