Data Analyzer Calculation and
Function Reference
Informatica PowerCenter
®
PowerCenter Data Analyzer Calculation and Function Reference Version 8.0
December 2005
Copyright © 2001-2005 Informatica Corporation. All rights reserved. Printed in the USA.
This software and documentation contain proprietary information of Informatica Corporation, 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 the prior written consent of Informatica Corporation.
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(c)(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 the software or documentation, please report them to us in writing. Informatica Corporation does not warrant that this product or documentation is error free.
Informatica, Data Analyzer, PowerMart, PowerCenter, PowerChannel, PowerCenter Connect, MX, and Metadata Manager are trademarks or registered trademarks of Informatica Corporation in the United States and in jurisdictions throughout the world. BEA and WebLogic are registered trademarks of BEA Systems, Inc. All other company and product names may be trade names or trademarks of their respective owners. U.S. Patent Pending.
Portions of this software and/or documentation are subject to copyright held by third parties, including without limitation: Copyright © Sun Microsystems. All rights reserved. Copyright © 2001 BEA Systems, Inc. All rights reserved. © Copyright IBM Corp. 2000, 2001. All rights reserved. Copyright © 1991-2000 DataDirect Technologies, Inc. All rights reserved. Isomorphic SmartClient presentation layer © 2002 Isomorphic Software, Inc. All rights reserved. Copyright © Palm, Inc. All rights reserved. Copyright © Research In Motion Limited. All rights reserved. Copyright © Unisys. All rights reserved. Copyright © GoAmerica Communications Corp. All rights reserved. Copyright © Microsoft Corp. All rights reserved. Copyright © 1998-1999 Aandacht c.v. All rights reserved. Copyright © 2001-2004 Panscopic Corporation, Inc. All rights reserved.
This product includes software developed by the Apache Software Foundation (http://www.apache.org/). The Apache Software is Copyright © 1999-2004 The Apache Software Foundation. All rights reserved.
The Qexo software included with this software is Copyright © 1996-1999 Per Bothner. All Rights Reserved. Your right to use such software is set forth in a modified GNU Public License, which may be found at http://www.gnu.org/software/kawa/License.html. The software is 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.
InstallAnywhere is Copyright © 2005 Zero G Software, Inc. All Rights Reserved.
Portions of the Data Analyzer component contain copyrighted material from The JBoss Group, LLC. Your right to use such materials is set forth in the GNU Lesser General Public License Agreement, which may be found at http://www.opensource.org/licenses/lgpl-license.php. The JBoss 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.
DISCLAIMER: Informatica Corporation provides this documentation “as is” without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of non-infringement, merchantability, or use for a particular purpose. The information provided in this documentation may include technical inaccuracies or typographical errors. Informatica could make improvements and/or changes in the products described in this documentation at any time without notice.
iii
Table of Contents
List of Figures . . . xi
List of Tables . . . xiii
Preface . . . xv
New Features and Enhancements . . . xvi
Informatica Distributed Services Architecture . . . xvi
Command Line Programs . . . xvii
Datatypes . . . xvii
Data Profiling . . . xvii
Documentation . . . .xviii
Installation . . . .xviii
Integration Service (PowerCenter Server) . . . .xix
Globalization . . . xx
Performance Improvements . . . xx
Repository . . . .xxi
Transformation Language . . . xxii
Transformations . . . xxii
XML . . . xxii
Usability . . . .xxiii
PowerCenter Data Analyzer (PowerAnalyzer) . . . .xxiii
PowerCenter Metadata Manager (SuperGlue) . . . .xxiii
PowerCenter Connect for IBM MQSeries . . . .xxiii
PowerCenter Connect for SAP NetWeaver mySAP Option (PowerCenter Connect for SAP R/3) . . . xxiv
PowerCenter Connect for SAP NetWeaver BW Option (PowerCenter Connect for SAP BW) . . . xxiv
PowerCenter Repository Reports (PowerCenter Metadata Reporter) . . . xxv
About This Book . . . xxvi
Document Conventions . . . xxvi
Other Informatica Resources . . . xxvii
Visiting Informatica Customer Portal . . . xxvii
Visiting the Informatica Web Site . . . xxvii
Visiting the Informatica Developer Network . . . xxvii
Obtaining Technical Support . . . .xxviii
Chapter 1: Using Calculations in Data Analyzer . . . .1
Overview . . . .2
Types of Calculations . . . 3
Custom Metric Calculations . . . 3
Custom Aggregate Calculations . . . .4
Calculated Metric Calculations . . . 4
Custom Attribute Calculations . . . 5
Precision for a Calculation . . . .7
Currency Decimals . . . 7
Chapter 2: Expression Syntax . . . .9
Overview . . . 10
General Rules of Syntax . . . 10
Nulls . . . 10 Function Syntax . . . 12 Constants . . . 13 Operators . . . 14 Arithmetic Operators . . . 15 Comparison Operators . . . 15 Logical Operators . . . 16 Dates . . . 17 Calendar . . . 17
Date Format Strings . . . 17
Chapter 3: Setting the Context for a Function . . . .21
Overview . . . 22
Types of Context . . . 23
Layout Context . . . 23
Value Context . . . 23
Syntax for the Context Argument . . . 24
Equal (=) Operator . . . 24
Separators . . . 24
Attributes . . . 24
Keywords . . . 26
Table of Contents v
Setting the Context for Functions of Running Category . . . 29
Chapter 4: Functions . . . 31
Function Categories . . . 32
Day Count Basis Argument . . . 32
Supported Units of Measure . . . 34
Abs . . . 35 AccrInt . . . 36 AccrIntM . . . 38 Add . . . 39 AddToDate . . . 40 AmorDegrC . . . 41 AmorLinC . . . 43 And . . . 45 ArcCos . . . 46 ArcCosh . . . 47 ArcCot . . . 48 ArcCoth . . . 49 ArcCsc . . . 50 ArcCsch . . . 51 ArcSec . . . 52 ArcSech . . . 53 ArcSin . . . 54 ArcSinh . . . 55 ArcTan . . . 56 ArcTanh . . . 57 AveDev . . . 58 Avg . . . 59 BinomDist . . . 60 BinomInv . . . 61 Ceiling . . . 62 ChiDist . . . 64 ChiInv . . . 65 ChiTest . . . 66 Clean . . . 68 Concat . . . 69 Confidence . . . 70
Correl . . . 71 Cos . . . 73 Cosh . . . 74 Cot . . . 75 Coth . . . 76 CountAll . . . 77 CountUnique . . . 78 CoupDayBS . . . 79 CoupDayS . . . 80 CoupDaySNC . . . 81 CoupNCD . . . 82 CoupNum . . . 83 CoupPCD . . . 84 Covar . . . 85 Csc . . . 87 Csch . . . 88 CumIPmt . . . 89 CumPrinc . . . 91 DB . . . 93 DDB . . . 94 Date . . . 95 DateString . . . 96 DateValue . . . 97 Day . . . 99 DevSq . . . 100 DfToRate . . . 101 Disc . . . 102 Divide . . . 103 Effect . . . 104 ElapsedDays . . . 105 ElapsedHours . . . 107 ElapsedYears . . . 108 EndOfMonth . . . 109 Equals . . . 110 EqualsIgnoreCase . . . 111 Exp . . . 112 ExponDist . . . 113
Table of Contents vii FInv . . . 115 FV . . . 116 Factorial . . . 117 Find . . . 118 Fisher . . . 119 FisherInv . . . 120 Floor . . . 121 Forecast . . . 123 GammaDist . . . 125 GammaInv . . . 126 GammaLog . . . 127 GeoMean . . . 128 GreaterThan . . . 129 GreaterThanEquals . . . 130 HarMean . . . 131 Hour . . . 132 HypGeomDist . . . 133 IRR . . . 134 If . . . 135 IfNull . . . 136 IntDivide . . . 137 IntRate . . . 138 Kurt . . . 139 Len . . . 140 LessThan . . . 141 LessThanEquals . . . 142 Log . . . 143 Log10 . . . 144 Log2 . . . 145 Lower . . . 146 Max . . . 147 Max2 . . . 148 Median . . . 149 Min . . . 150 Min2 . . . 151 Minute . . . 152 Mod . . . 153 Mode . . . 154
Month . . . 155 MovingAvg . . . 156 MovingSum . . . 158 Multiply . . . 160 NPV . . . 161 NPer . . . 163 Negate . . . 164 Next . . . 165 NextDate . . . 166 Nominal . . . 167 NormDist . . . 168 NormInv . . . 169 NormSDist . . . 170 NormSInv . . . 171 Not . . . 172 NotEquals . . . 173 Now . . . 174 NumberString . . . 175 Or . . . 176 PV . . . 177 Pearson . . . 178 PercentContribution . . . 180 Percentile . . . 181 PeriodsPerYear . . . 183 Plus . . . 184 Pmt . . . 185 Power . . . 186 Previous . . . 187 Price . . . 188 PriceDisc . . . 190 PriceMat . . . 191 Product . . . 193 Rate . . . 194 RateToDf . . . 195 Received . . . 196 Replace . . . 197 Rept . . . 198
Table of Contents ix RoundDown . . . 200 RoundUp . . . 201 RunningAvg . . . 202 RunningSum . . . 203 SLn . . . 204 SYD . . . 205 Sec . . . 206 Sech . . . 207 Second . . . 208 Sign . . . 209 Sin . . . 210 Sinh . . . 211 Skew . . . 212 Slope . . . 213 Sqrt . . . 215 Square . . . 216 Standardize . . . 217 Stdev . . . 218 StdevP . . . 219 Steyx . . . 220 Substitute . . . 222 Substring . . . 223 Subtract . . . 224 Sum . . . 225 TBillEq . . . 226 TBillPrice . . . 227 TBillYield . . . 228 TDist . . . 229 TInv . . . 230 TTest . . . 231 Tan . . . 233 Tanh . . . 234 Time . . . 235 Today . . . 236 TriMean . . . 237 Trim . . . 238 Trunc . . . 239 Units . . . 240
Upper . . . 241 VDB . . . 242 ValueOf . . . 244 Var . . . 245 VarP . . . 246 WeekNum . . . 247 Weekday . . . 248 Weibull . . . 249 Within . . . 250 XIRR . . . 252 XNPV . . . 254 Year . . . 256 Yield . . . 257 YieldDisc . . . 259 YieldMat . . . 260 ZTest . . . 261 . . . 262
Appendix A: Function Quick Reference . . . 263
Aggregate Functions . . . 264
Boolean Functions . . . 269
Comparative Functions . . . 270
Conditional Functions . . . 271
Conversion Functions . . . 272
Date & Time Functions . . . 273
Fetch Functions . . . 275 Financial Functions . . . 276 Mathematical Functions . . . 282 Rounding Functions . . . 283 Running Functions . . . 284 Statistical Functions . . . 285 String Functions . . . 289 Trigonometric Functions . . . 291 Unary Functions . . . 293
Index . . . 295
List of Figures xi
List of Figures
Figure 1-1. Custom Metrics Page in Advanced Mode . . . 3
Figure 1-2. Custom Aggregates Page . . . 4
Figure 1-3. Calculated Metric Properties Page . . . 5
List of Tables xiii
List of Tables
Table 2-1. Expression Syntax . . . 10
Table 2-2. Operators in Expressions . . . 14
Table 2-3. Arithmetic Operators in Expressions . . . 15
Table 2-4. Comparison Operators in Expressions . . . 15
Table 2-5. Logical Operators in Expressions . . . 16
Table 2-6. Date Format Letters in Data Analyzer Functions . . . 18
Table 3-1. Time Attributes Used in Setting Context . . . 25
Table 3-2. Keywords for Specifying the Context Argument . . . 26
Table 4-1. Values of the DayCountBasis Argument . . . 33
Table 4-2. Arguments for the Units Function . . . 34
Table A-1. Functions in the Aggregate Category . . . 264
Table A-2. Functions in the Boolean Category . . . 269
Table A-3. Functions in the Comparative Category . . . 270
Table A-4. Functions in the Conditional Category . . . 271
Table A-5. Function in the Conversion Category . . . 272
Table A-6. Functions in the Date & Time Category . . . 273
Table A-7. Functions in the Fetch Category . . . 275
Table A-8. Functions in the Financial Category . . . 276
Table A-9. Functions in the Mathematical Category . . . 282
Table A-10. Functions in the Rounding Category . . . 283
Table A-11. Functions in the Running Category . . . 284
Table A-12. Functions in the Statistical Category . . . 285
Table A-13. Functions in the String Category . . . 289
Table A-14. Functions in the Trigonometric Category . . . 291
xv
Preface
Welcome to PowerCenter, the Informatica software product that delivers an open, scalable data integration solution addressing the complete life cycle for all data integration projects including data warehouses, data migration, data synchronization, and information hubs. PowerCenter combines the latest technology enhancements for reliably managing data repositories and delivering information resources in a timely, usable, and efficient manner. The PowerCenter repository coordinates and drives a variety of core functions, including extracting, transforming, loading, and managing data. The Integration Service can extract large volumes of data from multiple platforms, handle complex transformations on the data, and support high-speed loads. PowerCenter can simplify and accelerate the process of building a comprehensive data warehouse from disparate data sources.
New Features and Enhancements
This section describes new features and enhancements to PowerCenter 8.0. New features and enhancements are described in the following sections:
♦ Informatica Distributed Services Architecture, xvi
♦ Command Line Programs, xvii
♦ Datatypes, xvii
♦ Data Profiling, xvii
♦ Documentation, xviii
♦ Installation, xviii
♦ Integration Service (PowerCenter Server), xix
♦ Globalization, xx
♦ Performance Improvements, xx
♦ Repository, xxi
♦ Transformation Language, xxii
♦ Transformations, xxii
♦ XML, xxii
♦ Usability, xxiii
♦ PowerCenter Data Analyzer (PowerAnalyzer), xxiii
♦ PowerCenter Metadata Manager (SuperGlue), xxiii
♦ PowerCenter Connect for IBM MQSeries, xxiii
♦ PowerCenter Connect for SAP NetWeaver mySAP Option (PowerCenter Connect for SAP R/3), xxiv
♦ PowerCenter Connect for SAP NetWeaver BW Option (PowerCenter Connect for SAP BW), xxiv
♦ PowerCenter Repository Reports (PowerCenter Metadata Reporter), xxv
Informatica Distributed Services Architecture
♦ Domains and nodes. PowerCenter 8.0 allows you to scale services and share resources across multiple machines. PowerCenter introduces a PowerCenter domain, the primary unit of administration for the PowerCenter environment, and a PowerCenter node, the logical representation of a machine in a domain.
♦ Services. PowerCenter provides application and core services. Application services represent PowerCenter server-based functionality such as the Repository Service, Integration Service, and SAP BW Service. Core services support the domain and application services. Core services include a Log Service, Licensing Service, and Domain Service.
Preface xvii ♦ High availability. You can use the high availability option to eliminate single points of
failure in the PowerCenter environment and reduce service interruptions in the event of failure. High availability provides resilience, failover, and recovery for services.
♦ Administration. PowerCenter provides centralized administration through the
Administration Console. You perform all administration tasks for the domain and services in the Administration Console. This includes administration of the Repository Service.
♦ Authentication Service. PowerCenter uses the Authentication Service to authenticate users for the Administration Console and the repository. For repository authentication, you can use LDAP or native authentication. To use LDAP authentication, you configure
authentication at the domain level and register the module with each repository you want to use LDAP authentication.
♦ Licensing Service. PowerCenter uses the Licensing Service to enable features, such as PowerCenter options and connectivity. You assign each application service to a license to enable the service.
♦ Log Service. The Log Service is a core service of the PowerCenter domain. It captures log events for all services within the domain. Log types include domain, services, workflows, and sessions. You can view logs in the Administration Console and in the Workflow Monitor.
Command Line Programs
♦ infacmd. infacmd is a new command line program that allows you to administer
PowerCenter domains and services. You can perform tasks such as enabling and disabling services and purging log events.
♦ infasetup. infasetup is a new command line program that allows you to configure domains
and nodes. Use infasetup to define domains and nodes, join domains, and configure node passwords, domain licenses, and domain service ports.
♦ pmcmd. pmcmd includes new commands to support Integration Service functionality and
new syntax to connect to a domain.
♦ pmrep. pmrep now includes former pmrepagent commands and new syntax to connect to a
domain.
Datatypes
♦ Flat file definitions. You can configure flat file definitions to use the Integer or Double datatype.
Data Profiling
♦ Functional Dependencies Analysis function. You can use the Functional Dependencies Analysis function to determine exact and approximate dependencies between columns in a source.
♦ Inter-Source Structure Analysis function. You can use the Inter-Source Structure Inference function to determine primary key-foreign key relationships among multiple sources.
♦ Candidate key and redundancy column analysis. Data Profiling can return column sets of up to seven columns for candidate key and redundancy analysis. Also, the column precision to evaluate candidate keys and analyze redundancies is increased.
♦ Domain Inference function. The Domain Inference function can infer the source datatype and categories of inferred patterns. Data Profiling reports show results for the new types of data in this function.
♦ Column Lookup domain. You can use a flat file or relational source column as a domain.
♦ Multiple join columns. You can specify up to six join conditions in the Referential Integrity Analysis and Join Complexity Evaluation functions.
♦ Reporting. Data Profiling provides reporting enhancements such as improved paging capabilities and a consistent layout between custom profile reports and auto profile reports. In reports for the column-level Business Rule Validation function, you can click the Drill Down link on the Verbose Report Summary to view the source rows.
Documentation
PowerCenter documentation includes the following new books:
♦ Administrator Guide. Provides information for administrators to manage the domain and
services.
♦ Command Line Reference. Provides syntax and description for infacmd, infasetup, pmcmd,
and pmrep.
♦ Performance Tuning Guide. Provides information about tuning the performance in
PowerCenter.
♦ Upgrade Guide. Provides information about new and changed functionality in
PowerCenter, including tasks that you might need to perform after upgrading to a new version.
Installation
♦ Installation packaging. The PowerCenter installation DVD installs Informatica Distributed Services and the PowerCenter Client. PowerCenter also includes a documentation CD that you can use to install PowerCenter documentation.
♦ PowerCenter Editions. Based on the license you have, you can install PowerCenter Standard Edition or PowerCenter Advanced Edition.
♦ Configuration. The installation program can create a domain, add a node to an existing domain, and create a Repository Service and Integration Service. For PowerCenter Services, Data Analyzer, and Metadata Manager, you can use an existing repository or create a new repository. It also provides configuration options.
Preface xix ♦ Upgrade. You can manually upgrade an existing PowerCenter Server or Repository Agent
to an Integration Service or Repository Service.
Integration Service (PowerCenter Server)
Architecture
♦ Service-oriented architecture. The Integration Service replaces the PowerCenter Server. If you have the high availability option, you can configure the Integration Service to fail over to another node if the current node becomes unavailable.
♦ Load Balancer. The Load Balancer dispatches tasks. If you run tasks on a grid, the Load Balancer dispatches Session and Command tasks to nodes that can run the tasks.
♦ Resources. PowerCenter requires resources to run tasks. Resources can include plug-ins, connection objects, file directories, and shared libraries. You define resources for nodes and tasks. The Load Balancer dispatches tasks to nodes where the resources are available.
♦ Grid object. The server grid is updated to incorporate a grid object into the PowerCenter domain. You create a grid object in the Administration Console, and you assign one or more nodes to run on the grid.
♦ Run workflow on a grid. You can configure a workflow to run on an Integration Service associated with the grid. When you run a workflow on a grid, the Integration Service distributes the workflow tasks to available nodes.
♦ Run session on a grid. You can configure a session to run on a grid if the parent workflow is associated with a grid. When you run a session on a grid, the Integration Service distributes reader, writer, and transformation threads to available nodes.
Partitioning
♦ Database partitioning. You can use the database partitioning partition type with Oracle and DB2 sources.
♦ Dynamic partitioning. The Integration Service can determine the number of partitions at run time based on the dynamic partitioning session property.
FTP
♦ Partitioned FTP file targets. For partitioned target instances, the Integration Service writes to a remote merge file. For merged targets, the Integration Service creates a local merge file and transfers the merge file to the FTP host.
♦ Indirect FTP file sources. You can use an indirect file list for a flat file source instance located on a remote machine.
Recovery
♦ Workflow recovery. If you have high availability, you can recover an interrupted workflow in the Workflow Manager or Workflow Monitor. When you recover a workflow, the
Integration Service attempts to complete the original workflow run. The Integration Service restores the last good state of the workflow and restarts or resumes the interrupted tasks.
− Session task recovery. You can configure the recovery strategy to recover an interrupted session in a workflow. You can choose to resume, restart, or skip the Session task. When you recover the workflow, the Integration Service uses the recovery strategy to recover the session.
− Command task recovery. You can configure the recovery strategy to restart or skip the Command task when you recover a workflow.
Environment SQL
♦ Transaction environment SQL. You can use transaction environment SQL to execute an SQL statement at the beginning of each transaction. This is in addition to the current connection environment SQL that executes each time the Integration Service connects to the database.
Globalization
♦ Unicode repository. PowerCenter now supports a Unicode repository. You can choose the UTF-8 character set as the repository code page to store metadata from multiple
languages.
♦ Code page validation. With expanded support for Unicode, PowerCenter code page validation rules are now less restrictive.
Performance Improvements
♦ Pushdown optimization. The Integration Service can execute SQL against the source or target database instead of processing the transformation logic within the Integration Service.
♦ Partitioned flat file targets. The Integration Service can create an individual target file for all partitions, a merge file for all partitioned flat file target instances, and a file list that references the individual target files for all target partitions.
♦ Append to flat file targets. The Integration Service can append data to existing flat file target and reject files.
♦ Flat file source and target commands. You can specify a command for any flat file source or target instance in a session. You can use a command to generate source data and you can use a command to process target data.
♦ Data compression. Repository data is compressed during communication among the Repository Service, Integration Service, and PowerCenter Client.
Preface xxi
Repository
This section describes changes to the repository functionality.
Administration
♦ Administration tool. You use the browser-based PowerCenter Administration Console to perform administrative tasks that were formerly done in the Repository Server
Administration Console.
Architecture
♦ Service-oriented architecture. In the Informatica distributed services architecture, each Repository Service manages a single repository. A Repository Service process is an instance of the Repository Service running on a particular node. If you have the high availability option, you can configure the Repository Service to fail over to another node if the current node becomes unavailable.
Meta Integration Model Bridge
♦ Additional import/export functionality. You can use the Meta Integration Model Bridge from Meta Integration Technology, Inc. to exchange data with the following business intelligence and data modeling tools:
− CA AllFusion ERwin Data Modeler 4.x
− CA ERwin 3.x (ERX) and CA ERwin 3.5x (export only)
− Embarcadero ER/Studio
− Oracle Designer
− Sybase PowerDesigner
Object Queries
♦ Non-versioned repositories. You can create and run queries against repositories that are not enabled for versioning.
♦ Label parameter. The following operators are added to the label parameter: Contains, Does Not Contain, Starts With, Does Not Start With, Ends With, and Does Not End With.
Versioned Objects
♦ Checking out and checking in objects. You can explicitly check out versioned repository objects. You can view checked-in objects in the workspace.
Deployment
♦ Assign owner and group to a folder. You can assign an owner and owner group when you copy a folder or deployment group.
♦ Generate a deployment control file. You can generate a deployment control file when you copy a folder or deployment group with the Copy Wizard.
Security
♦ Extended repository privileges. You can assign additional extended repository privileges to manage repository objects. Privileges include Manage Connection, Manage Label, and Manage Folder.
Transformation Language
♦ User-defined functions. You can create user-defined functions in the Designer to use in transformations and workflow tasks. You can include the functions in expressions or other user-defined functions.
♦ Custom Functions SDK. You can develop functions written in C Language and integrate them using a set of API calls that come with PowerCenter. You can include the functions in mapping and workflow expressions.
♦ New functions. The transformation language has 23 new built-in functions such as COMPRESS, DECOMPRESS, and REVERSE.
Transformations
♦ Custom transformation. The Custom transformation has the following enhancements:
− Procedures with thread-specific operations. You can include thread-specific operations in Custom transformation procedures and configure the transformation to process each partition using one thread.
− Partitioning enhancements. You can partition a Custom transformation or any transformation created from a Custom transformation locally or across a grid.
− Java and C++ API. You can use Java and C++ to create the Custom transformation procedures. You can use the new API to access data in block mode and perform datatype conversions at the column level.
♦ Java transformation. You can use the Java transformation to create, compile, and debug Java code snippets that execute in-line Java code in the mapping. You can also use Java to call expressions in a Java transformation.
XML
♦ Filter data with query predicate. You can filter XML source data in a session by including an XPath query predicate in an XML definition. You can use string, numeric, and boolean XPath functions to filter source data.
Preface xxiii ♦ Create a default namespace. You can use a default namespace to eliminate the namespace
prefix in an XML target.
♦ Import part of an XML schema. You can import part of an XML schema when you import an XML file that uses the schema, but only contains some of the schema components.
♦ Use anySimpleType. You can import an XML schema that contains anySimpleType elements.
Usability
♦ Propagate port description. In the Designer, you can edit a port description and propagate the description to other transformations in the mapping.
♦ Link ports in the workspace. In the Designer, you can link ports by dragging all ports from one mapping object to another mapping object in the workspace.
♦ Show background in iconic view. In the Designer and Workflow Manager, you can enable background colors for objects in iconic view.
PowerCenter Data Analyzer (PowerAnalyzer)
♦ PowerAnalyzer renamed. PowerAnalyzer is renamed PowerCenter Data Analyzer.
PowerCenter Metadata Manager (SuperGlue)
♦ SuperGlue renamed. SuperGlue is renamed PowerCenter Metadata Manager.
♦ 64-bit operating systems. Metadata Manager supports 64-bit operating systems.
♦ XConnects. The PowerCenter XConnect extracts additional metadata from the
PowerCenter repository. PowerCenter XConnects now support parameterized connections, and data lineage is displayed for these connections.
♦ Data lineage diagrams. Data lineage diagrams display the following information:
− If the container of the data structure or transformation box is different from the actual parent, the actual parent name of the data structure or transformation displays in the lineage diagram.
− Stored procedures are displayed as object structures.
− Public synonyms are displayed for all database tables across domains.
♦ Metadata Manager Configuration Console. Metadata Manager Configuration Console now works on the PowerCenter 8.0 platform. You can now register multiple source repository instances under the corresponding repository type.
PowerCenter Connect for IBM MQSeries
♦ Improved real-time performance. The Integration Service can now commit source messages to the target at the end of a millisecond flush latency period. By default, the
integer n in the FlushLatency(n) function represents seconds. However, you can configure an MQSeries session to change the value to milliseconds.
PowerCenter Connect for SAP NetWeaver mySAP Option
(PowerCenter Connect for SAP R/3)
♦ PowerCenter Connect for SAP R/3 renamed. PowerCenter Connect for SAP R/3 is renamed PowerCenter Connect for SAP NetWeaver mySAP Option.
♦ Improved performance. The Integration Service creates caches in memory for sessions for inbound IDoc mappings or DMI mappings. You can configure the total cache size and cache directory.
♦ Outbound IDoc syntax validation. You can configure an outbound IDoc session to check for invalid IDocs and write them to a relational or flat file target.
♦ Mandatory segments and groups in IDoc and DMI mappings. In the SAP/ALE IDoc Prepare transformation, SAP/ALE IDoc Interpreter transformation, and SAP DMI Prepare transformation, you can view mandatory segments and groups to help you understand which segments the transformations require in the IDoc or DMI message.
♦ Scheduling Business Content Integration. The listener workflow controls the scheduling of the processing workflows. The listener workflow receives DataSource data from SAP, loads the data to staging targets, and then starts the appropriate processing workflow for the received data.
PowerCenter Connect for SAP NetWeaver BW Option
(PowerCenter Connect for SAP BW)
♦ PowerCenter Connect for SAP BW renamed. PowerCenter Connect for SAP BW is renamed PowerCenter Connect for SAP NetWeaver BW Option.
♦ PowerCenter Integration Server for SAP BW (PCISBW) renamed. PowerCenter Integration Server for SAP BW is renamed to SAP BW Service.
♦ Loading by packet size. PowerCenter Connect for SAP NetWeaver BW Option loads BW data as a packet based on the packet size value you set in the session.
♦ Load balancing. To support load balancing, the SAP BW Service records the BW host requesting data from PowerCenter. The Integration Service loads the data to the same requesting host.
♦ Process chains to load into SAP BW. You can create a BW Process chain to start a PowerCenter workflow that loads data into SAP BW.
♦ Improved logging. The SAP BW Service can capture messages from SAP BW when you run a BW session. You can view these log events in the PowerCenter Administration Console or in the SAP BW monitor.
♦ Recovery. You can enable a BW session for recovery. If the session fails, you can restart it in the Workflow Manager.
Preface xxv
PowerCenter Repository Reports (PowerCenter Metadata
Reporter)
♦ PowerCenter Metadata Reporter renamed. PowerCenter Metadata Reporter is renamed PowerCenter Repository Reports.
♦ Server Grid List report. The Server Grid List report is replaced with the following reports:
− Integration Service List. Displays a list of all Integration Services that run on the grids and nodes configured in PowerCenter.
− Repository Information. Displays a list of all Repository Services that run on nodes configured in PowerCenter. It also provides information about whether a node is a primary node or a backup node.
♦ Metadata Extensions Usage report. The Metadata Extensions Usage report is replaced with the following reports:
− Metadata Extensions Usage (Mapping and Mapplet). Displays a list of all reusable metadata extensions. The report is organized by repository and folder and shows the number of mappings or mapplets that use each metadata extension.
− Metadata Extensions Usage (Source, Target and Transformation). Displays a list of all reusable metadata extensions. The report is organized by repository and folder and shows the number of sources, targets, or transformations that use each metadata extension.
− Metadata Extensions Usage (Workflow, Worklet and Session). Displays a list of all reusable metadata extensions. The report is organized by repository and folder and shows the number of workflows, worklets, or sessions that use each metadata extension.
About This Book
The Data Analyzer Calculation and Function Reference is written for information providers who want to perform advanced calculations on metrics and attributes. The Calculation and
Function Reference provides information on writing mathematical expressions to create metric
and attribute calculations in Data Analyzer. The Calculation and Function Reference also provides information on the syntax and usage of the functions available for the expressions. The material in this book is also available online.
Document Conventions
This guide uses the following formatting conventions:
If you see… It means…
italicized text The word or set of words are especially emphasized.
boldfaced text Emphasized subjects.
italicized monospaced text This is the variable name for a value you enter as part of an operating system command. This is generic text that should be replaced with user-supplied values.
Note: The following paragraph provides additional facts.
Tip: The following paragraph provides suggested uses.
Warning: The following paragraph notes situations where you can overwrite or corrupt data, unless you follow the specified procedure. monospaced text This is a code example.
bold monospaced text This is an operating system command you enter from a prompt to
Preface xxvii
Other Informatica Resources
In addition to the product manuals, Informatica provides these other resources:
♦ Informatica Customer Portal
♦ Informatica web site
♦ Informatica Developer Network
♦ Informatica Knowledge Base
♦ Informatica Technical Support
Visiting Informatica Customer Portal
As an Informatica customer, you can access the Informatica Customer Portal site at http://my.informatica.com. The site contains product information, user group information, newsletters, access to the Informatica customer support case management system (ATLAS), the Informatica Knowledge Base, and access to the Informatica user community.
Visiting the Informatica Web Site
You can access the Informatica corporate web site at http://www.informatica.com. The site contains information about Informatica, its background, upcoming events, and sales offices. You will also find product and partner information. The services area of the site includes important information about technical support, training and education, and implementation services.
Visiting the Informatica Developer Network
You can access the Informatica Developer Network at http://devnet.informatica.com. The Informatica Developer Network is a web-based forum for third-party software developers. The site contains information about how to create, market, and support customer-oriented add-on solutions based on interoperability interfaces for Informatica products.
Visiting the Informatica Knowledge Base
As an Informatica customer, you can access the Informatica Knowledge Base at
http://my.informatica.com. Use the Knowledge Base to search for documented solutions to known technical issues about Informatica products. You can also find answers to frequently asked questions, technical white papers, and technical tips.
Obtaining Technical Support
There are many ways to access Informatica Technical Support. You can contact a Technical Support Center by using the telephone numbers listed the following table, you can send email, or you can use the WebSupport Service.
Use the following email addresses to contact Informatica Technical Support:
♦ [email protected] for technical inquiries
♦ [email protected] for general customer service requests
WebSupport requires a user name and password. You can request a user name and password at http://my.informatica.com.
North America / South America Europe / Middle East / Africa Asia / Australia Informatica Corporation
Headquarters
100 Cardinal Way Redwood City, California 94063 United States Toll Free 877 463 2435 Standard Rate United States: 650 385 5800 Informatica Software Ltd. 6 Waltham Park
Waltham Road, White Waltham Maidenhead, Berkshire SL6 3TN United Kingdom Toll Free 00 800 4632 4357 Standard Rate Belgium: +32 15 281 702 France: +33 1 41 38 92 26 Germany: +49 1805 702 702 Netherlands: +31 306 022 797 United Kingdom: +44 1628 511 445
Informatica Business Solutions Pvt. Ltd. Diamond District Tower B, 3rd Floor 150 Airport Road Bangalore 560 008 India Toll Free Australia: 00 11 800 4632 4357 Singapore: 001 800 4632 4357 Standard Rate India: +91 80 51889000
1
C h a p t e r 1
Using Calculations in Data
Analyzer
This chapter includes the following topics:
♦ Overview, 2
♦ Types of Calculations, 3
Overview
You can perform advanced calculations on metric and attribute data, such as rounding values or calculating the sum of specific metric values in a column. To perform an advanced calculation, you must write a mathematical expression that modifies data and returns a result. An expression can include any combination of the following components:
♦ Functions ♦ Metrics ♦ Attributes ♦ Constants ♦ Variables ♦ Keywords ♦ Operators
The following custom metric expression uses some of the preceding components:
ROUND({Revenue},1) + ABS({Sales Qty})/SUM({Cost})
The expression contains the following components:
♦ Metrics: Revenue, Cost, and Sales Qty
♦ Functions: ROUND(), ABS(), and SUM()
Types of Calculations 3
Types of Calculations
You can write expressions to create the following calculations:
♦ Custom metrics
♦ Custom aggregates
♦ Calculated metrics
♦ Custom attributes
Custom Metric Calculations
You can create custom metrics for reports on the Create Report wizard or the Analyze tab. When you create a custom metric in advanced mode, you can use Data Analyzer functions. In custom metric expressions, you can use metrics, attributes, or constants with functions. Figure 1-1 shows the Custom Metrics page, in advanced mode, where you can enter the custom metric expression:
For more information about custom metrics, see “Adding Calculations to a Report” in the Data Analyzer User Guide.
Custom Aggregate Calculations
You can create custom aggregates for reports on the Create Report wizard or the Analyze tab. When you create a custom aggregate, you can use Data Analyzer functions. In custom aggregate expressions, you can use metrics, attributes, or constants with functions. Figure 1-2 shows the Custom Aggregates page where you can enter the custom metric expression:
For more information about custom aggregates, see “Adding Calculations to a Report” in the Data Analyzer User Guide.
Calculated Metric Calculations
You can create calculated metrics in the Schema Directory on the Administration tab. When you create a calculated metric, you can use Data Analyzer functions. In calculated metric expressions, you can use metrics or constants with functions.
Types of Calculations 5
Figure 1-3 shows the Calculated Metric Properties page where you can create calculated metric expressions:
For more information about calculated metrics, see “Working with Calculated Metrics” in the Data Analyzer Schema Designer Guide.
Custom Attribute Calculations
You can create custom attributes for your reports on the Analyze tab. You can also create custom attributes in the Schema Directory on the Administration tab. When you create a custom attribute in advanced mode, you can use Data Analyzer functions that belong to the following categories:
♦ Boolean
♦ Comparative
♦ Conditional
♦ Date and Time
Figure 1-4 shows the Custom Attributes page, in advanced mode, where you can enter the custom attribute expression:
For more information about custom attributes you can create for a report, see “Working with Metrics and Attributes” in the Data Analyzer User Guide. For more information about custom attributes you can create in the Schema Directory, see “Managing the Schema Directory” in the Data Analyzer Schema Designer Guide.
Precision for a Calculation 7
Precision for a Calculation
Precision is the number of digits after the decimal point in a numeric value. When any of the values in an expression has a precision of two or more, Data Analyzer assigns the return value the same precision as that of the value with the highest precision. For example, you have the metric Sales Price with a value of 402.86 in the custom metric expression:
{Sale Price} + .2586)
Sales Price has a precision of two, while .2586 has a precision of four. The value of the custom metric is 403.1186, with a precision of four.
If none of the values in the expression has a precision of two or more, the custom metric or custom aggregate has two as the default precision.
Calculations that involve multiplication and division may result in higher precision than the values included in the operation. For example, you have the metric Sales Price with a value of 402.86 in the custom metric expression:
{Sale Price} *.258)
Sales Price has a precision of two, while .258 has a precision of three. The result of the calculation is 103.9379. Data Analyzer uses the precision of the value with the highest precision. Data Analyzer rounds the value to the highest precision in the expression. Therefore, the value of the custom metric is 103.938.
Currency Decimals
Certain financial functions return a monetary value. For example, the CumIPmt function returns the cumulative interest paid for a loan between two specified periods of time. When you use a function that returns a monetary value, you can enter a value for the currency digits to the right of the decimal point. The value you enter overrides the precision of the return value. Data Analyzer rounds the return value to the specified value for the currency digits.
9
C h a p t e r 2
Expression Syntax
This chapter includes the following topics:
♦ Overview, 10
♦ Function Syntax, 12
♦ Constants, 13
♦ Operators, 14
Overview
When you write an expression, use the correct syntax and write a valid mathematical
expression. Although Data Analyzer provides syntax validation, you must enter valid values in the expression.
General Rules of Syntax
Table 2-1 lists the general rules of syntax that apply when writing all expressions:
Nulls
NULL indicates that a value is either unknown or undefined. NULL is not equivalent to a blank or empty string (for character columns) or zero (for numerical columns).
If any metric used in the expression has a null value, the expression returns NULL. For example if a custom metric has the expression:
({Sale Price} - ({Sale Price} * .25)) - {Product Price} Table 2-1. Expression Syntax
Syntax Rule Syntax Rule
Enclose metric or attribute names in curly brackets as follows: {metric_name}
{Sales}
When a metric or attribute name occurs more than once in the repository, clarify which to use with the folder name and a pipe, as follows:
{folder_name|metric_name}
Note: Unnecessary use of the pipe may produce unexpected results.
{Sales|Sales}
Use parentheses to define the order of execution. Data Analyzer evaluates the innermost set of parentheses before proceeding to the next.
To find the sum, then the product: (6*(4+8))
Surround arguments in parentheses as follows: function(argument).
When using a metric or attribute, also use curly brackets: function({metric_name}).
ABS({Profit})
When you pass an optional argument to a function, you must also pass any other optional arguments that occur ahead of it in the function syntax.
For the AmorDegrC function, if you pass the CurrencyDecimals argument, you must also pass the DayCountBasis argument.
Data Analyzer does not support nested statistical functions. The following functions are not supported: STDEV(ABS({Sales Qty})) VAR(ROUND({Sales Qty},2)) Do not nest functions within aggregate functions. Functions not supported:
SUM(ABS({Sales Qty})) MEDIAN(ROUND({Profit}))
Overview 11
and the value of Product Price is NULL, the value of the custom metric is NULL.
Null Handling in Functions
If you use a function in the expression, null handling depends on the function being used. For most functions, if you pass a null value, the function returns a NULL.
For aggregate functions, by default, Data Analyzer ignores null values. For example, in the following custom metric expression:
Avg(Sale Price)
if any of the sale price values is NULL, Data Analyzer ignores that value when calculating the average sale price. If you pass an entire row or column of null values, the function returns NULL.
For more information about how specific functions handle null values, see “Functions” on page 31.
Null Values in the Context Argument
You can pass the Context argument to certain functions to set a scope for the function. If a value of the Context argument evaluates to NULL, the function does not select the row or column. If the Context evaluates to NULL for all rows or columns for a metric or attribute, the function returns NULL. The exceptions to this rule are the CountAll and CountUnique functions, which return zero.
Function Syntax
A function manipulates values that you pass to it and returns a result. For example, you might use the Avg function to calculate the average salary of all your employees, or the Sum function to calculate the total sales for a set of metric values.
The values you pass to a function are known as arguments. Data Analyzer uses the following syntax for a function in an expression:
function name(argument1, argument2 ...)
You can also pass optional arguments to many functions. Optional arguments display within square brackets ([ ]) in the syntax:
function name(argument1, argument2 ...[, argument x, argument y ...])
When you enter the function in the expression text box, do not enter the square brackets. Depending on the function, an argument takes any of the following values:
♦ Metric name ♦ Attribute name ♦ Variable ♦ String literal ♦ Numeric literal ♦ Date value
Certain functions take the Context argument. The Context argument is a special type of argument that sets the scope for a function. For more information about the Context argument, see “Setting the Context for a Function” on page 21.
Data Analyzer provides a point-and-click interface for all functions to help you enter expressions. To minimize syntax errors, use the point-and-click method to enter expressions. When you point and click instead of manually entering the expression, Data Analyzer supplies any necessary additional characters, such as brackets and pipes.
When you use the point-and-click method to add a function to the expression, Data Analyzer displays the names of the required arguments in the expression. You can replace the argument names with the values you want to pass to the function. You can also enter values for any optional argument you want to pass to the function.
Constants 13
Constants
Use the following constants anywhere in an expression:
♦ E. E is a mathematical constant that is the base of the natural logarithm. The value of E is equal to 2.718281828459. Use the E constant to calculate the natural logarithm of a numeric value. For example, you have an expression that returns the straight-line depreciation of an asset for one year. You want to calculate the natural logarithm of the depreciation of the asset in each year. Use the following expression where, the cost of the asset is $1,000, salvage value is $175, and the life of the asset is 8 years:
SLn(1000, 175, 8)*E
♦ PI. PI is a real number that is used in many mathematical calculations. The value of PI is equal to 3.14159265359. Use the PI variable in trigonometric functions to covert radians to degrees or degrees to radians. For example, the following expression returns 0.707 radians as the cosine of 45 degrees:
Cos(45*PI/180)
Use the following constants as the arguments for certain functions:
♦ FALSE
♦ TRUE
TRUE and FALSE are logical values that determine the form of the function in which they are being used. For example, in the BinomDist function, you can use TRUE or FALSE as the value of the Cumulative argument. If Cumulative is TRUE, the function returns the
cumulative binomial distribution; otherwise it does not return the cumulative binomial distribution.
Note: The constants are not case sensitive. For example, within an expression, PI is the same as pi.
Operators
Data Analyzer supports the use of multiple operators and the use of operators within nested expressions. If you write an expression that includes multiple operators, Data Analyzer evaluates the expression in the following order:
1. Arithmetic operators 2. Comparison operators 3. Logical operators
Data Analyzer evaluates operators in the order they appear in the following table. It evaluates operators in an expression with equal precedence to all operators from left to right.
Table 2-2 lists the precedence for all operators:
You can also use operators within nested expressions. When expressions contain parentheses, Data Analyzer evaluates operations inside parentheses before operations outside parentheses. Operations in the innermost parentheses are evaluated first.
For example, depending on how you nest the operations, the equation 8 + 5 - 2 * 8 returns different values:
Table 2-2. Operators in Expressions
Operator Meaning
( ) Parentheses
+, - Unary plus and minus
^ Power
*, /, % Multiplication, division, modulus +, - Addition, subtraction
<, <=, >, >= Less than, less than or equal to, greater than, greater than or equal to =, <> Equal to, not equal to
! Logical NOT operator
& Logical AND operator, used when specifying conditions | Logical OR operator, used when specifying conditions
Equation Return Value
8 + 5 - 2 * 8 -3 8 + (5 - 2) * 8 32
Operators 15
Arithmetic Operators
Use arithmetic operators to perform mathematical calculations on numeric data.
Table 2-3 lists the arithmetic operators, in order of precedence, you can use in expressions:
If you perform arithmetic on a null value, the function returns NULL.
When you use arithmetic operators in an expression, all of the operands in the expression must be numeric. For example, the expression 1 + “1” is not valid because it adds an integer to a string. The expression 1.23 + 4 / 2 is valid because all of the operands are numeric.
Note: Data Analyzer provides Date and Time functions that let you perform arithmetic on date/time values. For further information on built-in date functions, see “Dates” on page 17.
Comparison Operators
Use comparison operators to compare character or numeric strings, manipulate data, and return a TRUE (1) or FALSE (0) value.
Table 2-4 lists the comparison operators you can use in expressions:
When you use comparison operators in an expression, the operands must be the same datatype. For example, the expression 123.4 > “123” is not valid because the expression compares a numeric value with a string. The expressions 123.4 > 123 and “a” <> “b” are valid because the operands are the same datatype.
Table 2-3. Arithmetic Operators in Expressions
Operator Meaning
+, - Unary plus, unary minus. Unary plus indicates a positive value. Unary minus indicates a negative value. ^, *, /, % Power, multiplication, division, modulus. A modulus is the remainder after dividing two integers. For
example, 13 % 2 = 1 because 13 divided by 2 equals 6 with a remainder of 1. +, - Addition, subtraction.
The addition operator (+) does not concatenate strings. To concatenate strings, use the Concat function. To perform arithmetic on date values, use the date functions.
Table 2-4. Comparison Operators in Expressions
Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal to <= Less than or equal to <> Not equal to
If you compare a value to a null value, the result is NULL. For information on how to compare dates, see “Dates” on page 17.
Logical Operators
Use logical operators to manipulate numeric data. Expressions that return a numeric value evaluate to TRUE for non-zero values, FALSE for zero, and NULL for NULL.
Table 2-5 lists the logical operators you can use in expressions:
Expressions that combine a null value with a Boolean expression produce results that are ANSI-compliant. For example, Data Analyzer produces the following results:
♦ NULL AND TRUE = NULL
♦ NULL AND FALSE = FALSE
Table 2-5. Logical Operators in Expressions
Operator Equivalent
SQL Operator Meaning
! NOT Negates result of an expression. For example, if an expression evaluates to TRUE, the ! operator returns FALSE. If an expression evaluates to FALSE, the ! operator returns TRUE.
& AND Joins two conditions and returns TRUE if both conditions evaluate to TRUE. Returns FALSE if one condition is not true.
| OR Connects two conditions and returns TRUE if any condition evaluates to TRUE. Returns FALSE if both conditions are not true.
Dates 17
Dates
Data Analyzer provides Date and Time functions that help you perform calculations on dates. With the Date and Time functions you can round, truncate, or compare dates, extract one part of a date, or perform arithmetic on a date. For information on each function, see “Functions” on page 31.
In addition to Date and Time functions, other functions also take dates as arguments. For example, financial functions AccrInt and AccrIntM take date arguments. As a value for a date argument, you can pass a date in one of the following ways:
♦ Pass a date attribute. A date attribute contains date values that are recognized by Data Analyzer functions. You do not need to manipulate the date attribute values before passing them to a function. The following expression is an example of a function that takes date arguments. Begin Date and End Date are date attributes:
ElapsedDays({Begin Date}, {End Date})
♦ Enter a date by using another function that generates a date. Functions that generate a date are Date, DateValue, EndOfMonth, Today, NextDate, and AddToDate. The
following expression is an example of a function that takes date arguments. The expression uses the DateValue function to enter dates for the Disc function:
Disc(DateValue("4/1/2004"), DateValue("6/15/2004"), 95, 100)
You cannot directly enter a date string as the value for a date argument. For example, the following expression is invalid:
Disc("4/1/2004", "6/15/2004", 95, 100)
For more information about function usage and syntax, see “Functions” on page 31.
Calendar
Data Analyzer supports dates in the Gregorian calendar system only. Dates expressed in a different calendar system are not supported. Data Analyzer recognizes leap years and accepts any date in the Gregorian calendar.
Date Format Strings
The date format string defines the structure of a date. Use date format strings to convert input dates into other date formats.
Date format strings used in Data Analyzer functions are derived from the Java programming language. You must enter a date format string using certain date format letters, as specified in Java. Date format strings are case sensitive and must be enclosed within quotation marks.
Using Date Format Strings in Functions
You can pass the DateFormat argument to certain functions. The value of the DateFormat argument is a case-sensitive date format string that defines the structure of a date. Use the DateFormat argument to pass the date format string to the following functions:
♦ DateString. The DateString function creates a string, when you provide date and format values. Use the DateFormat argument to provide the function information about the date you are passing. For example, the following expression returns the string “2003-Jan-13 AD”:
DateString(date(2003, 01, 13), “yyyy-MMM-dd G”)
♦ DateValue. The DateValue function creates a date, when you provide a date string value. Use the DateFormat argument to pass a date string in a format that is unrecognized by the function. For example, the following expression returns the date for 13th day of January, 2003 AD:
DateValue("13/January/2003 AD", "dd/MMMM/yyyy G")
Date Format Letters
Use the date format letters to specify a date format string. You can also use the date format letters to change how date and timestamp attributes display in your reports. For more information, see “Managing Account Information” in the Data Analyzer User Guide.
Table 2-6 lists the date format letters you can use to specify a part of a date. The examples in the table are based on the following date:
August 20, 2004 4:35 PM GMT
Table 2-6. Date Format Letters in Data Analyzer Functions
Format Letter Description Examples
G Era designator. G returns AD.
y Year portion of date. yyyy returns 2004.
yy returns 04. M Month portion of date. If the number of letters is 3 or
more, Data Analyzer interprets the month as text, otherwise, Data Analyzer interprets the month as a number.
M returns 8. MM returns 08. MMM returns Aug. MMMM returns August.
w Week number in year. w returns 34.
W Week number in month. W returns 3.
D Day number in year. D returns 233.
d Day number in month. d returns 20.
E Day of week, returned as text. E, EE, and EEE return Fri. EEEE returns Friday. a AM or PM marker, returned as text. a returns PM.
Dates 19
Note: You must use the correct case for the format letters you use in your expressions. Data Analyzer does not validate the case of the format letters you use in an expression. For example, if you use “mm” instead of “MM” to specify the month portion of a date, you might see incorrect data in your report.
H Hour of day (0 to 23), where zero is 12 AM (midnight). H and HH return 16. h Hour number in AM or PM (1 to 12). hh returns 4.
hh returns 04.
m Minute number in hour. m and mm returns 35.
s Second portion of date. s returns 0.
ss returns 00.
S Millisecond portion of date. S returns 000.
z Time zone, returned as text. z returns PMT.
Table 2-6. Date Format Letters in Data Analyzer Functions
21
C h a p t e r 3
Setting the Context for a
Function
This chapter includes the following topics:
♦ Overview, 22
♦ Types of Context, 23
♦ Syntax for the Context Argument, 24
Overview
You can use the Context argument with functions such as Count and Covar. When you create a custom metric or custom aggregate expression, you can use the Context argument as a filter condition to set a scope for the function. The function performs the calculation on only those values for which the condition is true.
Use the context argument to create a flexible expression by performing the calculation on a set of attribute values. For example, you have a report that displays the Sales Person and Region attributes and the Sales metric. To find out the total number of sales persons, use the Count function and pass a metric or attribute name as the value of the List argument:
Count(List [,Context])
To find out how many sales persons are from the Northern region, use the following syntax to pass a value for the Context argument:
Count({Sales Person}, "{Region}='Northern'")
The preceding expression counts the number of values in the Sales Person attribute for the Northern region.
Note: You cannot use the Context argument when using functions to create a calculated metric expression.
Types of Context 23
Types of Context
Context can be one of the following types:
♦ Layout context
♦ Value context
Layout Context
Use layout context to perform the calculation on rows, columns, or sections in the report. Use layout keywords to set the layout context. For example, you can use the ROW keyword to perform the Sum calculation on each row in a report. For more information about layout keywords, see “Keywords” on page 26.
When you use a layout context in a custom metric expression, the custom metric is known as a dependent custom metric. You cannot create filters or set rankings for layout-dependent metric calculations. For more information about layout-layout-dependent metric calculations, see “Adding Calculations to a Report” in the Data Analyzer User Guide.
Value Context
Use value context to perform the calculation on specified attribute values. Use attributes and value keywords to set the value context. For example, you can use the THIS keyword to get the value of an attribute for the current row. For more information about value keywords, see “Keywords” on page 26.
Syntax for the Context Argument
You must enclose the context within double quotation marks. You must enclose an attribute or metric value within single quotation marks.
The syntax for the Context argument can include any combination of the following elements:
♦ Equal (=) operator ♦ Separators ♦ Attributes ♦ Keywords ♦ $AGGREGATE_BY$ variable
Equal (=) Operator
Use the equal (=) operator when setting the context for a function. The Data Analyzer equal (=) operator is the same as the SQL IN operator. The function performs the calculation on any of the data values that match the condition. The following expression is an example of the equal (=) operator where Sales is a metric in the report and State is an attribute in the report:
Sum({Sales}, “{State}=‘CA’, ‘AZ’”)
The above expression calculates the total of all Sales values where the State attribute value is either CA or AZ.
Separators
Use the comma (,) symbol to separate multiple values within a condition. For example:
Avg({Sales}, “{Category}=‘Food’, ‘Drinks’, ‘Supplies’”)
You can include multiple conditions within the Context argument. Use the semicolon (;) symbol to separate multiple conditions. For example:
Sum({Sales}, “{State}=‘CA’;{Category}=‘Food’”)
Attributes
Use attribute names and attribute values to set the value context. Use attribute names that are part of the report. Use the point-and-click interface to add attribute names and attribute values to the expression. If you manually enter the attribute names in the expression, you must enclose attribute names within curly brackets ({}).
For reports with time settings, you can use time attributes as keywords when you set the value of the Context argument. Time attributes allow you to track time periods in reports. Your Data Analyzer system administrator must set up the time attributes in the time dimension table before you can use them in your expressions. For more information about setting
Syntax for the Context Argument 25
calendar attributes, see “Working with Time Dimensions” in the Data Analyzer Schema
Designer Guide.
For reports with time settings, you can select the applicable time attributes for an expression. Applicable time attributes are the attributes that represent the time period selected for the report and any other time period of a higher granularity. For example, for a report for the Current Quarter, you can select QUARTER_NUM or YEAR_NUM time attributes for your expression.
Use the point-and-click interface to add the time attribute name and attribute values to the expression. If you manually enter the time attribute name in the expression, you must enter the at (@) symbol before the time attribute name.
Table 3-1 lists the time attributes that you can use when passing the Context argument:
Table 3-1. Time Attributes Used in Setting Context
Time Attribute Name Meaning Example
HOUR_NUM Refers to the hour number in day. To calculate the total sales made between 10:00 and 11:00 am, use the following expression: Sum({Sales}, “{@HOUR_NUM}=10”) YEAR_DAY_NUM Refers to the day number in year. To calculate the total sales made on the first day of
the year, use the following expression: Sum({Sales}, “{@YEAR_DAY_NUM}=1”) YEAR_WEEK_NUM Refers to the week number in year. To calculate the total sales made in the fourth
week of the year, use the following expression: Sum({Sales}, “{@YEAR_WEEK_NUM}=4”) YEAR_MONTH_NUM Refers to the month number in year. To calculate the total sales made in the month of
June, use the following expression:
Sum({Sales}, “{@YEAR_MONTH_NUM}=6”) QUARTER_NUM Refers to the quarter number. To calculate the average sales made in the first
quarter, use the following expression: Avg({Sales}, “{@QUARTER_NUM}=1”) To calculate the average sales made in the current quarter, use the following expression:
Avg({Sales},
“{@QUARTER_NUM}=CURRENT”)
YEAR_NUM Refers to the year number. To calculate the total sales made in 2003, use the following expression:
