Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

Enterprise

PeopleTools 8.49

PeopleBook

: PeopleSoft Analytic

Calculati

on Engine

March 2007

Summary of content (326 pages)

PAGE 1
Enterprise PeopleTools 8.
PAGE 2
Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Analytic Calculation Engine SKU PT849ACE-B 0307 Copyright © 1988-2007, Oracle. All rights reserved. The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws.
PAGE 3
This product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit (http://www.openssl.org/). THIS SOFTWARE IS PROVIDED BY THE OpenSSL PROJECT “AS IS” AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
PAGE 4
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, provided that the above copyright notice(s) and this permission notice appear in all copies of the Software and that both the ab
PAGE 5
Contents General Preface About This PeopleBook ............................................................................. . . . . . . . .xv PeopleSoft Enterprise Application Prerequisites... ........................................................ . . . . . . . .xv Application Fundamentals..................................................................................... . . . . . . . .xv Documentation Updates and Printed Documentation..................................................... ........
PAGE 6
Contents Chapter 2 Understanding PeopleSoft Analytic Calculation Engine..................................... ..........7 PeopleSoft Analytic Calculation Engine Components..................................................... ..........7 PeopleSoft Analytic Calculation Engine Architecture.. .................................................... . . . . . . . .10 PeopleSoft Analytic Calculation Engine Development Process. ......................................... . . . . . . . .
PAGE 7
Contents Definition of a Data Cube................................................................................... . . . . . . .31 Input Data Cubes............................................................................................ . . . . . . .32 Calculation Data Cubes..................................................................................... . . . . . . .33 Association Data Cubes.................................................................................... . . . . . . .
PAGE 8
Contents Mapping a Cube Collection to Main and Aggregate Records.......................................... . . . . . . .58 Mapping Data Cubes and Dimensions to Fields........................................................ . . . . . . .59 Defining Additional Cube Collection Dimension Properties............................................ . . . . . . .60 Chapter 8 Creating Explicit Dimension Sets................................................................. . . . . . . . .
PAGE 9
Contents Chapter 10 Creating Rules, Formulas, and User Functions................................................ . . . . . . .111 Understanding Rules, Formulas, and User Functions... .................................................. . . . . . . .111 Common Elements Used in This Chapter................................................................ . . . . . .111 Rules, Formulas, and User Functions..........................................................................112 Filter User Functions..........
PAGE 10
Contents AT....................................................................................................................141 ATAN................................................................................................................143 BREAK..............................................................................................................143 CASE................................................................................................................144 CHANGE...............
PAGE 11
Contents INTERCEPT........................................................................................................167 IRR..................................................................................................................167 LN....................................................................................................................168 LEFT................................................................................................................168 LEN...................
PAGE 12
Contents SLOPE..............................................................................................................189 SQRT................................................................................................................191 STDEV..............................................................................................................192 SYD..................................................................................................................192 TAN....................
PAGE 13
Contents Setting Analytic Grid General Properties.................................................................... .......220 Inserting and Manipulating Analytic Grid Columns......................................................... .......222 Inserting Analytic Grid Columns.................................................................................223 Deleting Analytic Grid Columns..................................................................................
PAGE 14
Contents Importing Analytic Instances.....................................................................................248 Part 6 Converting Analytic Models Chapter 16 Converting BAM 8.8 Models to Analytic Models............................................... .......253 Understanding the Conversion Process..................................................................... .......253 Understanding Part Conversion Details... .................................................................. .......
PAGE 15
About This PeopleBook PeopleSoft Enterprise PeopleBooks provide you with the information that you need to implement and use PeopleSoft Enterprise applications from Oracle. This preface discusses: • PeopleSoft Enterprise application prerequisites. • Application fundamentals. • Documentation updates and printed documentation. • Additional resources. • Typographical conventions and visual cues. • Comments and suggestions. • Common elements in PeopleBooks. Note.
PAGE 16
General Preface The application fundamentals PeopleBook consists of important topics that apply to many or all PeopleSoft Enterprise applications. Whether you are implementing a single application, some combination of applications within the product line, or the entire product line, you should be familiar with the contents of the appropriate application fundamentals PeopleBooks. They provide the starting points for fundamental implementation tasks.
PAGE 17
General Preface Additional Resources The following resources are located on Oracle’s PeopleSoft Customer Connection website: Resource Navigation Application maintenance information Updates + Fixes Business process diagrams Support, Documentation, Business Process Maps Interactive Services Repository Support, Documentation, Interactive Services Repository Hardware and software requirements Implement, Optimize + Upgrade; Implementation Guide; Implementation Documentation and Software; Hardware and S
PAGE 18
General Preface Typographical Conventions and Visual Cues This section discusses: • Typographical conventions. • Visual cues. • Country, region, and industry identifiers. • Currency codes.
PAGE 19
General Preface Description Typographical Convention or Visual Cue [ ] (square brackets) Indicate optional items in PeopleCode syntax. & (ampersand) When placed before a parameter in PeopleCode syntax, an ampersand indicates that the parameter is an already instantiated object. Ampersands also precede all PeopleCode variables. Visual Cues PeopleBooks contain the following visual cues.
PAGE 20
General Preface Region Identifiers Regions are identified by the region name. The following region identifiers may appear in PeopleBooks: • Asia Pacific • Europe • Latin America • North America Industry Identifiers Industries are identified by the industry name or by an abbreviation for that industry. The following industry identifiers may appear in PeopleBooks: • USF (U.S. Federal) • E&G (Education and Government) Currency Codes Monetary amounts are identified by the ISO currency code.
PAGE 21
General Preface Once, Always, and Don’t Run Select Once to run the request the next time the batch process runs. After the batch process runs, the process frequency is automatically set to Don’t Run. Select Always to run the request every time the batch process runs. Select Don’t Run to ignore the request when the batch process runs. Process Monitor Click to access the Process List page, where you can view the status of submitted process requests.
PAGE 23
PeopleSoft Analytic Calculation Engine Preface This chapter provides an overview of the PeopleSoft Analytic Calculation Engine and lists common elements. PeopleSoft Analytic Calculation Engine This PeopleBook is written for application developers working with PeopleSoft applications, and provides a foundation for developing and administering analytic models, which calculate and send data to PeopleSoft applications for the purposes of multidimensional reporting, analysis, and data entry.
PAGE 25
PART 1 Getting Started with PeopleSoft Analytic Calculation Engine Chapter 1 Getting Started with PeopleSoft Analytic Calculation Engine
PAGE 26
PAGE 27
CHAPTER 1 Getting Started with PeopleSoft Analytic Calculation Engine This chapter provides an overview of PeopleSoft Analytic Calculation Engine and discusses PeopleSoft Analytic Calculation Engine implementation.
PAGE 28
Getting Started with PeopleSoft Analytic Calculation Engine Chapter 1 Step Reference Establish a user profile that gives you access to PeopleSoft Application Designer and any other processes that you will use. See Enterprise PeopleTools 8.49 PeopleBook: Security Administration. Configure the application for which you are creating or changing an analytic model. The appropriate PeopleBook for your application.
PAGE 29
PART 2 Designing and Editing Analytic Models Chapter 2 Understanding PeopleSoft Analytic Calculation Engine Chapter 3 Understanding Analytic Models Chapter 4 Creating Analytic Model Definitions Chapter 5 Creating Data Cubes Chapter 6 Creating Dimensions Chapter 7 Creating Cube Collections Chapter 8 Creating Explicit Dimension Sets Chapter 9 Creating Hierarchies
PAGE 30
Chapter 10 Creating Rules, Formulas, and User Functions Chapter 11 Using Built-in Functions in Analytic Models
PAGE 31
CHAPTER 2 Understanding PeopleSoft Analytic Calculation Engine This chapter discusses: • PeopleSoft Analytic Calculation Engine components. • PeopleSoft Analytic Calculation Engine architecture. • PeopleSoft Analytic Calculation Engine development process. • PeopleSoft Analytic Calculation Engine security. PeopleSoft Analytic Calculation Engine Components This section discusses these PeopleSoft Analytic Calculation Engine components: • Analytic model. • Analytic type. • Analytic instances.
PAGE 32
Understanding PeopleSoft Analytic Calculation Engine Chapter 2 • Map individual record fields to data cubes and dimensions within the cube collections. Analytic Type Both PeopleSoft Analytic Calculation Engine and PeopleSoft Optimization Framework use analytic types.
PAGE 33
Chapter 2 Understanding PeopleSoft Analytic Calculation Engine See Enterprise PeopleTools 8.49 PeopleBook: PeopleCode API Reference, “Analytic Calculation Engine Classes”. Analytic Calculation Engine Metadata Classes The Analytic Calculation Engine Metadata classes are application classes that PeopleSoft applications use to create and change analytic model metadata. For example, using the Analytic Calculation Engine Metadata classes you could modify a calculation rule.
PAGE 34
Understanding PeopleSoft Analytic Calculation Engine Chapter 2 PeopleSoft Analytic Calculation Engine Architecture This diagram illustrates how the various PeopleSoft Analytic Calculation Engine architecture components work together: Application Designer Analytic Server Instance Analytic Server Instance Analytic Model Analytic Server Instance Analytic Type SQL (2-Tier) PeopleSoft Database SQL Analytic Calculation Engine Analytic Instance Pages with Analytic Grids BEA Tuxedo PSAPPSERV PSAPPSERV SQ
PAGE 35
Chapter 2 Understanding PeopleSoft Analytic Calculation Engine PeopleSoft Analytic Calculation Engine Development Process You should follow the development process outlined here for your PeopleSoft application to correctly employ the features of PeopleSoft Analytic Calculation Engine.
PAGE 36
Understanding PeopleSoft Analytic Calculation Engine Chapter 2 Note. You can also embed analytic instance loading functionality into PeopleSoft Pure Internet Architecture pages with analytic grids. Development Process Using Existing Record Structures If you have existing record structures on which the analytic model should be based, perform this iterative process: • Create a new analytic model.
PAGE 37
Chapter 2 Understanding PeopleSoft Analytic Calculation Engine • Create filter functions to restrict the data that appears in the analytic grid. See Chapter 10, “Creating Rules, Formulas, and User Functions,” Filter User Functions, page 113. • Create filter functions that filter data by userID. See Chapter 11, “Using Built-in Functions in Analytic Models,” OPRID, page 177. Copyright © 1988-2007, Oracle. All rights reserved.
PAGE 39
CHAPTER 3 Understanding Analytic Models This chapter discusses: • Analytic models. • Relationship of parts. • Tools. • Naming conventions for analytic models and parts. Analytic Models An analytic model is an information workshop. Just like an ordinary workshop, it contains parts that you use to build your projects, and tools to put the parts together. But instead of building a cabinet or a chair, you organize data by building analytic models of information.
PAGE 40
Understanding Analytic Models Chapter 3 Data Cubes and Dimensions The primary parts in an analytic model are data cubes (cubes) and dimensions: • A data cube is like a sheet of paper that contains one and only one kind of data. When you build an analytic model, you create a data cube for each kind of information in the analytic model. For example, an analytic model of a business might contain a data cube for sales, a data cube for rent, a data cube for salary, and so on.
PAGE 41
Chapter 3 Understanding Analytic Models Dimensions are separate objects that can be used independently of data cubes. For this reason, even though you originally created the MONTHS and PRODUCTS dimensions for the SALES cube, you can reuse these dimensions with other data cubes. For example, imagine taking a new sheet of paper and writing COST_OF_GOODS at the top of the page, and then attaching the existing dimensions to the new sheet.
PAGE 42
Understanding Analytic Models Chapter 3 SALES 2004/01 2004/02 2004/03 010 020 070 090 COST_OF_GOODS 2004/01 2004/02 2004/03 010 020 070 090 Adding the 090 product code to the PRODUCT_CODES dimension on the SALES cube When updating an analytic model, you can use a combination of existing and new dimensions when you define a data cube. For example, suppose you create a cube called SALARY. You want to track monthly data, so you attach the existing MONTHS dimension to the data cube.
PAGE 43
Chapter 3 Understanding Analytic Models SALARY 2004/01 2004/02 2004/03 2004/01 2004/02 2004/03 (Existing MONTHS dimension) Carlos Chang John Linda Carlos Chang John Linda (New EMPLOYEES dimension) SALARY data cube with attached MONTHS dimension and new EMPLOYEES dimension Formulas and User Functions You create formulas to define the relationships among the data cubes in an analytic model.
PAGE 44
Understanding Analytic Models Chapter 3 Cube Collections A cube collection is a collection of related data cubes. You create cube collections to load data from the database into the analytic model, save data back to the database, and display calculated data to the end user at runtime. Some cube collections contain data cubes that receive user input, and other cube collections calculate data cubes and display calculated data to the end user. See Chapter 7, “Creating Cube Collections,” page 53.
PAGE 45
Chapter 3 Understanding Analytic Models Menu Bar Tool Bar Formula Bar Rule Bar Part Browser Part Property Editor Notes Bar Output Window Status Bar PeopleSoft Application Designer interface for creating analytic model definitions Menu Bar Provides access to commands and features that you use to create analytic model definitions. See Chapter 3, “Understanding Analytic Models,” Menu Bar, page 22.
PAGE 46
Understanding Analytic Models Chapter 3 Output Window Contains the output text from PeopleSoft Application Designer operations, such as Build (SQL Create and Alter), Find Definition References, Upgrade, Results, Validate, and PeopleCode Log. Status Bar Contains descriptions of buttons and menu commands. Behavior of Bars All of the bars—except for the menu bar and status bar—are dockable. You can drag the bars to the top, bottom, left, or right sides of the PeopleSoft Application Designer interface.
PAGE 47
Chapter 3 Understanding Analytic Models This table shows the analytic model definition specific commands in the View menu: View Menu Commands Usage Notes Bar Active and deactivate the notes bar. Rule Bar Active and deactivate the rule bar. This table shows the analytic model definition specific commands in the Part menu.
PAGE 48
Understanding Analytic Models Part Menu Commands Chapter 3 Secondary Menu Commands Usage Quick Keys Detach Data Cubes... Detach one or more data cubes from the selected cube collection. None Detach Dimensions... Detach one or more dimensions from the selected data cube. None Delete Part None Delete the selected parts.
PAGE 49
Chapter 3 Understanding Analytic Models Tools Menu Commands Secondary Menu Commands Tertiary Menu Commands Usage Quick Keys Analytic Model Effects Direct Effects Display the direct effects of the selected data cubes. ALT + > Analytic Model Effects All Effects Display all effects of the selected data cubes. None Analytic Model Circular Formulas Show Circular System Show the data cubes involved in the circular system.
PAGE 51
CHAPTER 4 Creating Analytic Model Definitions This chapter provides overviews of the analytic model definition creation process and the conventions for naming analytic models and parts and discusses how to: • Create a new analytic model definition. • Open an analytic model definition. • Create organizers. • Enter notes for an analytic model definition’s parts. • Find parts. • Validate analytic models.
PAGE 52
Creating Analytic Model Definitions Chapter 4 • Define formulas and user functions to calculate the data cubes. Define a formula for each data cube that you want to calculate. If you want to reuse the formula in more than one data cube, create a user function and reference the user function in the formula for each of the data cubes. See Chapter 10, “Creating Rules, Formulas, and User Functions,” Defining and Editing Data Cube Formulas, page 119. • Attach dimensions to the data cubes.
PAGE 53
Chapter 4 Creating Analytic Model Definitions Creating a New Analytic Model Definition To open a new analytic model definition: 1. Select File, New in PeopleSoft Application Designer. The New Definition dialog box appears. 2. Select Analytic Model. 3. Click OK. The new analytic model definition appears. Opening an Analytic Model Definition To open an analytic model definition: 1. Select File, Open in PeopleSoft Application Designer. The Open Definition dialog box appears. 2.
PAGE 54
Creating Analytic Model Definitions Chapter 4 The Edit Part Name dialog box appears. 3. Enter a name for the organizer. 4. Click OK. See Also Chapter 3, “Understanding Analytic Models,” Cube Collections, page 20 Entering Notes for an Analytic Model Definition’s Parts Use the notes bar to create notes for the analytic model definition or its parts. To create a note: 1. In the part browser, select the analytic model definition or the part for which you want to create a note. 2. Click the notes bar. 3.
PAGE 55
CHAPTER 5 Creating Data Cubes This chapter provides overviews of data cubes and the relationship between field definition attributes and data cube formats and discusses how to: • Create input data cubes. • Create calculation data cubes. • Create association data cubes. • Create virtual data cubes. • Define data cube properties. • Audit data cubes at design time. Understanding Data Cubes This section provides overviews of: • Definition of a data cube. • Input data cubes. • Calculation data cubes.
PAGE 56
Creating Data Cubes Chapter 5 • Association data cubes. • Virtual data cubes. The four types of data cubes are not mutually exclusive, but certain combinational restrictions apply. For example, consider that all calculation data cubes contain formulas, and association data cubes may or may not contain formulas. When an association data cube does contain a formula, it is considered to be a type of calculation data cube.
PAGE 57
Chapter 5 Creating Data Cubes Note. Even though an input cube that uses either the INPUT built-in function is considered to be a type of calculation data cube, it would not serve a purpose in an intermediate/calculation cube collection. See Chapter 11, “Using Built-in Functions in Analytic Models,” INPUT, page 165.
PAGE 58
Creating Data Cubes Chapter 5 DEPTID Dimension Members REGION Dimension Members Note. In the application, the end users group or categorize these members by categories that are defined by the members of the REGION dimension. Note. In the application, the end users select members from this dimension to group members of the DEPTID dimension.
PAGE 59
Chapter 5 Creating Data Cubes DEPT_TO_REGION association data cube in the Analytic Model Viewer See Also Chapter 5, “Creating Data Cubes,” Creating Association Data Cubes, page 39 Chapter 7, “Creating Cube Collections,” Types of Cube Collections, page 54 Virtual Data Cubes A virtual data cube is a type of calculation data cube whose values are not saved to the database. Virtual data cubes can exist in intermediate/calculation and presentation cube collections.
PAGE 60
Creating Data Cubes Chapter 5 Characteristic Benefit The analytic calculation engine does not recalculate the virtual data cube unless the virtual data cube has nonvirtual dependents. Reduces recalculation time. The analytic calculation engine neither allocates memory nor calculates virtual data cubes until it receives a request for recalculation of the virtual data cube. Reduces memory consumption and recalculation time.
PAGE 61
Chapter 5 Creating Data Cubes PeopleSoft recommends that you create virtual data cubes when you expect the cubes to be large, sparse, and output-only, especially when a relatively small slice of the ordinary cubes is used in any given analytic instance ID. The analytic calculation engine takes a long time to recalculate nonvirtual cubes that are large, sparse, and output-only.
PAGE 62
Creating Data Cubes Chapter 5 Understanding the Relationship Between Field Definition Attributes and Data Cube Formats Because data cubes receive data from fields, it is important to correctly set both the attributes of field definitions and the formats of data cubes to ensure compatibility. The following table describes compatibilities between field definition attributes and data cube formats. Cells marked Yes indicate compatibility. Cells marked No indicate incompatibility.
PAGE 63
Chapter 5 Creating Data Cubes 2. Enter the data cube name. 3. Click OK. Note. Do not create formulas for input data cubes. See Also Chapter 5, “Creating Data Cubes,” Input Data Cubes, page 32 Creating Calculation Data Cubes To create a calculation data cube: 1. Select Part, New, Data Cube. The Edit Part Name dialog box appears. 2. Enter the data cube name. 3. Click OK. 4. Create a formula for the calculation data cube.
PAGE 64
Creating Data Cubes Chapter 5 See Chapter 6, “Creating Dimensions,” Attaching a Dimension to a Data Cube, page 50. See Also Chapter 5, “Creating Data Cubes,” Association Data Cubes, page 33 Creating Virtual Data Cubes To create a virtual data cube: 1. Select Part, New, Data Cube. The Edit Part Name dialog box appears. 2. Enter the name of the data cube. 3. Click OK. 4. On the General tab of the data cube’s properties, select Virtual Cube (doesn’t store data).
PAGE 65
Chapter 5 Creating Data Cubes General tab Data Cube Displays the name of the data cube. Format Number: Select to format the data cube’s values as numbers. Date: Select to format the data cube’s values as a date in the format YYYY-MM-DD. For example, 2004/03/18 for March 18, 2004. Note. Although the values are saved in the database using this date format, end users can use My Personalizations to select a different display format in PeopleSoft Pure Internet Architecture. See Enterprise PeopleTools 8.
PAGE 66
Creating Data Cubes Chapter 5 Note. In the analytic grid, data cubes formatted as members should have a field type of Edit Box. Text: Select to format the data cube’s values as text. This option is useful for entering names, addresses, and other textual data. Virtual Cube (doesn’t store data) Select to set the data cube as a virtual data cube. Clear to set the data cube as a nonvirtual data cube. See Chapter 5, “Creating Data Cubes,” Virtual Data Cubes, page 35. Note.
PAGE 67
Chapter 5 Creating Data Cubes Dimensions tab Name Displays the names of the dimensions that are attached to the data cube. See Chapter 6, “Creating Dimensions,” Attaching a Dimension to a Data Cube, page 50. Aggregate Function Select a cube dimension override user function to calculate the aggregates for the dimension as it is attached to the data cube. See Chapter 9, “Creating Hierarchies,” Understanding Override Order of Precedence, page 82.
PAGE 68
Creating Data Cubes Chapter 5 Note. This section discusses auditing data cubes in design time. Use the Analytic Model Viewer to audit cube collections and data cubes in runtime. See Chapter 14, “Viewing and Debugging Analytic Models,” Viewing and Debugging Cube Collections, page 233. Understanding Causes and Inputs Any data cube that affects another data cube is a cause—or precedent—of that data cube. A data cube can be a direct cause or an indirect cause of another data cube.
PAGE 69
Chapter 5 Creating Data Cubes See Chapter 5, “Creating Data Cubes,” Displaying Effects, page 45. Displaying Causes and Inputs To display the causes or inputs of a data cube: 1. Select a data cube whose causes or inputs you want to display. • To select several consecutive data cubes, hold down the SHIFT key and select the data cubes. • To select a series of nonconsecutive data cubes, hold down the CTRL key and select the data cubes. 2. Select Tools, Analytic Model, Causes. 3.
PAGE 70
Creating Data Cubes Chapter 5 See Also Chapter 5, “Creating Data Cubes,” Using the Causes and Effects Tool, page 46 Using the Causes and Effects Tool Select Tools, Analytic Model, Causes and Effects Tool to browse through the cube collections and data cubes of your analytic model to view the causes, effects, and inputs of data cubes. Causes and Effects Tool Cube Collection Select the cube collection to display a list of its data cubes. Note.
PAGE 71
CHAPTER 6 Creating Dimensions This chapter provides an overview of dimensions and discusses how to: • Create a new dimension. • Define dimension properties. • Attach a dimension to a data cube. • Change the order of dimensions in the part browser. Understanding Dimensions A dimension is a collection of people, places, events, or things for which you want to keep data. Each member of the dimension is called a dimension member. See Chapter 9, “Creating Hierarchies,” Understanding Dimension Members, page 73.
PAGE 72
Creating Dimensions Chapter 6 Creating a New Dimension To create a new dimension: 1. Select Part, New, Dimension. The Edit Part Name dialog box appears. 2. Enter the dimension name. 3. Click OK.
PAGE 73
Chapter 6 Creating Dimensions Note. This aggregate function does not apply to leaf members or detail members. See Chapter 9, “Creating Hierarchies,” Understanding Override Order of Precedence, page 82 and Chapter 9, “Creating Hierarchies,” Example: Creating a Hierarchy with Mixed Aggregate and Detail Members, page 109. Interpreting the Total Member Name Field This table describes the state of the dimension based upon whether: • A tree is attached to the dimension.
PAGE 74
Creating Dimensions Chapter 6 Tree Attached to Dimension? Yes Has Value? Resulting State of Dimension Yes One of these resulting states applies: • If you apply an aggregate function to this dimension, the value that you enter into the Total Member Name field serves as an alias to the hierarchy root member. This alias only appears to the user functions within the analytic model; the actual value of the hierarchy root member appears to the end user.
PAGE 75
Chapter 6 Creating Dimensions Changing the Order of Dimensions in the Part Browser To change the order of dimensions in the part browser: 1. Select one dimension that you want to move up or down in relationship to the other dimensions in the part browser. 2. Perform one of these steps: • Right-click the dimension member and select Move Up or Move Down. • Hold down the left mouse button, drag the dimension to the desired location, and release the left mouse button. 3.
PAGE 77
CHAPTER 7 Creating Cube Collections This chapter provides overviews of cube collections and types of cube collections and discusses how to: • Create cube collections. • Define cube collection properties. Understanding Cube Collections A cube collection is a collection of related data cubes. You create cube collections to load data from the database into the analytic model, receive user input, persist data back to the database, and display calculated data to the end user at runtime.
PAGE 78
Creating Cube Collections Chapter 7 Note. In the analytic type definition, you do not have to select the derived/work records that you want to map to presentation cube collections. However the analytic type definition must include all derived/work records that are mapped to cube collections on which you use the NetChanges parameter of the GetData CubeCollection class method. Note.
PAGE 79
Chapter 7 Creating Cube Collections Allowed in Read/Write Cube Collection? Data Cube Type Yes Input data cubes See Chapter 5, “Creating Data Cubes,” Input Data Cubes, page 32. Calculation data cubes Yes See Chapter 5, “Creating Data Cubes,” Input Data Cubes, page 32. Note. Initial data values for calculation data cubes are loaded from the database. Calculated values are written back to the database. Association data cubes Yes See Chapter 5, “Creating Data Cubes,” Association Data Cubes, page 33.
PAGE 80
Creating Cube Collections Chapter 7 Allowed in Intermediate/Calculation Cube Collection? Data Cube Type Association data cubes Yes See Chapter 5, “Creating Data Cubes,” Association Data Cubes, page 33. Yes Virtual data cubes See Chapter 5, “Creating Data Cubes,” Virtual Data Cubes, page 35. Presentation Cube Collections Use presentation cube collections to present data to the end user for the purposes of reporting and forecasting.
PAGE 81
Chapter 7 Creating Cube Collections • SALES_RW read/write cube collection. On the General tab, map this cube collection to the SALES main record to receive and persist raw sales data. This record must exist in the analytic type definition. This cube collection contains these data cubes: - UNIT_COST data cube. Map this data cube to the UNIT_COST field. - UNIT_SOLD data cube. Map this data cube to the UNIT_SOLD field. • SALES_PRES presentation cube collection.
PAGE 82
Creating Cube Collections Chapter 7 See Also Chapter 7, “Creating Cube Collections,” Types of Cube Collections, page 54 Defining Cube Collection Properties This section discusses how to: • Map a cube collection to main and aggregate records. • Map data cubes and dimensions to fields. • Define additional cube collection properties.
PAGE 83
Chapter 7 Creating Cube Collections analytic model. For a cube collection that is used to calculate data and display the calculated data to the end user at runtime, select a derived/work record. Aggregate Record Select a record to store the cube collection’s aggregate data. Records that are used as aggregate records should be read after records that are used as main records. See Chapter 12, “Understanding the Relationship of Analytic Types to Analytic Models,” Synchronization Order, page 208.
PAGE 84
Creating Cube Collections Chapter 7 Note. You can map a data cube or dimension to one field within one record. Once you have mapped a data cube or dimension to a particular record field, you cannot use that record field in another mapping. You can, however, reuse the same field if that field is from a different record. When mapping dimensions and data cubes, you may want to map dimensions to the key fields in the main record and data cubes to the non-key fields in the main record.
PAGE 85
Chapter 7 Creating Cube Collections Aggregate data is persisted to the aggregate record that you select on the General tab. ALL: Select to persist all of the dimension member aggregate values to the database. NONE: Select to persist none of the dimension member aggregate values to the database. ROOT: Select to persist only the value of the hierarchy root member to the database. See Chapter 9, “Creating Hierarchies,” Understanding the Persistence of Aggregate Data, page 79.
PAGE 86
Creating Cube Collections Chapter 7 Dimensions tab 62 Dimension Displays the name of the dimension. Persist Aggregate Displays any aggregates that are persisted for the dimension. Filter Displays the filter formula that is applied to the dimension. Sort Type Displays the sort type that is applied to the dimension’s values. Sort #1 If the dimension values are sorted by key, displays the first key field by which the dimension member values are sorted.
PAGE 87
CHAPTER 8 Creating Explicit Dimension Sets This chapter provides overviews of explicit dimension sets, and implicit tuples and explicit tuples, and discusses how to define explicit dimension sets. See Also Chapter 9, “Creating Hierarchies,” Understanding the Calculation of Aggregate Data, page 78 Understanding Explicit Dimension Sets You create an explicit dimension set to form a distinct group of an analytic model’s dimensions.
PAGE 88
Creating Explicit Dimension Sets Chapter 8 • CHANNELS • CUSTOMERS • TAXES • EXPENSES • MONTHS • PRODUCTS • REGIONS The analytic model contains these explicit dimension sets: • SET_1: MONTHS, REGIONS (first priority) • SET_2: TAXES, PRODUCTS (second priority) • SET_3: CHANNELS, CUSTOMERS, PRODUCTS (third priority) • SET_4: CHANNELS, CUSTOMERS (fourth priority) • SET_5: TAXES, EXPENSES (fifth priority) The analytic calculation engine iterates through the explicit dimension sets—beginning with SET_1—and then
PAGE 89
Chapter 8 Creating Explicit Dimension Sets If you do not create an explicit dimension set to calculate these data cubes, the analytic calculation engine calculates the sales for all products in all regions during all months, even if some of these combinations are not valid. In other words, the analytic calculation engine calculates the sales for french fries for all months in Asia, even though the company does not sell french fries in Asia.
PAGE 90
Creating Explicit Dimension Sets Chapter 8 • SET_1: PRODUCTS, REGIONS, ORDER_ID. • SET_4: DEPT_ID, EMPL_ID. • BUSINESS_UNIT dimension. Understanding Implicit Tuples and Explicit Tuples Implicit tuples are the combinations of members that are used to calculate a single data cube but do not comprise an explicit dimension set. Explicit tuples are the valid combinations of members that are instantiated from an explicit dimension set and are instantiated from these sources: • Data loaded from the database.
PAGE 91
Chapter 8 Creating Explicit Dimension Sets (US001, (US001, (US002, (US002, (US002, (US002, DEPT3000) , DEPT4000), DEPT1000), DEPT2000) , DEPT3000) , DEPT4000) Example: Explicit Tuples This example uses the same fields as the implicit tuples example.
PAGE 92
Creating Explicit Dimension Sets Chapter 8 Explicit Dimension Set tab Explicit Dimension Set Displays the name of the explicit dimension set. Dimensions Displays the dimensions that are included in the explicit dimension set. Editing or Adding New Explicit Dimension Sets Perform one of these actions: • To edit a preexisting explicit dimension set, double-click the name of the explicit dimension set on the Explicit Dimension Set tab.
PAGE 93
Chapter 8 Creating Explicit Dimension Sets Edit Explicit Dimension Set dialog box Explicit Dimension Set Enter or edit the name of the explicit dimension set. Dimensions Displays the dimensions in the explicit dimension set. Add If a row is currently selected, click this button to add a blank row beneath the currently selected row. You can then click the blank row to select a new dimension to add to the set.
PAGE 95
CHAPTER 9 Creating Hierarchies This chapter provides overviews of the relationship of PeopleSoft trees to analytic models, BAM total members, dimension members, the calculation of aggregate data, and the persistence of aggregate data, and discusses how to work with overrides. See Also Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Tree Manager Understanding the Relationship of PeopleSoft Trees to Analytic Models This section discusses: • The purpose of PeopleSoft trees and analytic model hierarchies.
PAGE 96
Creating Hierarchies Chapter 9 See Chapter 6, “Creating Dimensions,” Defining Dimension Properties, page 48. You can use the Analytic Model Viewer to view the properties of the trees that you are using with your analytic model. See Chapter 14, “Viewing and Debugging Analytic Models,” Viewing Dimension Properties, page 239. PeopleCode Usage with PeopleSoft Trees and Analytic Models Use the AttachTree and DetachTree methods to work with PeopleSoft trees and analytic models.
PAGE 97
Chapter 9 Creating Hierarchies Note. Be aware of the details start level and tree discard level before making any changes to the tree. See Chapter 9, “Creating Hierarchies,” Purpose of Node Levels in Creating Hierarchies, page 76. 4. Reload the analytic model. See Also Enterprise PeopleTools 8.49 PeopleBook: PeopleCode API Reference, “Analytic Calculation Engine Classes,” AttachTree Enterprise PeopleTools 8.
PAGE 98
Creating Hierarchies Chapter 9 Detail Members and Leaf Members If a tree is not attached to a dimension, PeopleSoft Analytic Calculation Engine creates detail members for each value of the field to which the dimension is mapped. If a tree is attached to a dimension, PeopleSoft Analytic Calculation Engine creates detail members out of the tree’s detail values to establish a dimension’s parent and child relationships (in a tree, detail values can serve as children and parents). See Enterprise PeopleTools 8.
PAGE 99
Chapter 9 Creating Hierarchies GBL Asia India Bangalore Calcutta Delhi Mumbia China Beijing Kashi Lianyungang Europe Example of a tree’s parent-child relationships Even though the highest level node is GBL, which is the root node of the tree, you can select the India node to serve as the hierarchy root member for this dimension. When you create the hierarchy root member out of the India node, only the children of India exist in the hierarchy.
PAGE 100
Creating Hierarchies Chapter 9 • Adds each orphan member’s value to the hierarchy root value’s member. • Generates a message with the ID of 123 and stores it in the Messages property for the analytic instance. You must write PeopleCode to iterate over the messages in the analytic instance and search for message 123, and then take any necessary further action. See Enterprise PeopleTools 8.49 PeopleBook: PeopleCode API Reference, “Analytic Calculation Engine Classes,” Error Handling.
PAGE 101
Chapter 9 Creating Hierarchies Details Start Level Specified? Value > 0 Leaf Members PeopleSoft Analytic Calculation Engine creates leaf members out of any detail values that are at the far right of the tree. Detail Members Aggregate Members PeopleSoft Analytic Calculation Engine creates detail members out of any detail values or nodes that are located either within the specified details start level, or within a level that is lower (higher number) than the details start level.
PAGE 102
Creating Hierarchies Chapter 9 Creation of New Members at Runtime PeopleSoft Analytic Calculation Engine can create new dimension members during runtime by using: • Data from the main record. • Application data that is added at runtime.
PAGE 103
Chapter 9 Creating Hierarchies Note. This section describes the member order as it exists within the analytic calculation engine. This internal order may differ from the member order that is ultimately displayed to the end user. • If the dimension is mapped to a tree, the analytic calculation engine first determines the member order by the order of the detail values in the tree. Next, the analytic calculation engine determines the member order from the order of the values as they exist in the database.
PAGE 104
Creating Hierarchies Chapter 9 • Tree data. Tree data includes: - Aggregate data: PeopleSoft Analytic Calculation Engine persists aggregate data in the aggregate record. See Chapter 9, “Creating Hierarchies,” Understanding the Persistence of Aggregate Data, page 79. Note. Records that are used as aggregate records should be read after records that are used as main records. See Chapter 12, “Understanding the Relationship of Analytic Types to Analytic Models,” Synchronization Order, page 208.
PAGE 105
Chapter 9 Creating Hierarchies See Also Chapter 11, “Using Built-in Functions in Analytic Models,” PARENT, page 179 Chapter 11, “Using Built-in Functions in Analytic Models,” CHILDCOUNT, page 145 Chapter 11, “Using Built-in Functions in Analytic Models,” FORCHILDREN, page 156 Data Type Considerations Aggregate members can have different data types than their child detail members, even though they both display data for the same dimension.
PAGE 106
Creating Hierarchies 2003 Chapter 9 (170) Q1 (80) Jan (10) Feb (20) Mar (50) Q2 (90) Apr (20) May (30) Jun (40) In this example, 2003, Q1, and Q2 are non-leaf members—that is, aggregates. The numbers in parentheses to the right of the aggregate members represent their aggregate values. The numbers in parenthesis to the right of the nonaggregate members represent their nonaggregated values.
PAGE 107
Chapter 9 Creating Hierarchies • Use the member override user function. This override operates on specific members of a dimension. You create the member override user function in the analytic model. However, because members are often instantiated at runtime, you use the PSACETREEOVRD subrecord to assign the member override user function to the dimension rather than assign the member override user function to specific members within the analytic model.
PAGE 108
Creating Hierarchies Chapter 9 See Also Chapter 9, “Creating Hierarchies,” Example: Using Default Aggregation, page 84 Chapter 9, “Creating Hierarchies,” Example: Creating a Hierarchy with Mixed Aggregate and Detail Members, page 109 Understanding the PSACETREEOVRD Subrecord You use the PSACETREEOVRD subrecord to assign an override to a tree. To use the subrecord, you must first create a record definition. This record definition can contain additional fields.
PAGE 109
Chapter 9 Creating Hierarchies DEPT field Data Type: Number SOMEDATE field Data type: Date NUM_SALES field Data Type: Number PRICE_PER_UNIT field Data Type: Number 101 2004–01–01 1 10 102 2004–01–02 2 14 103 2004–01–01 4 15 201 2004–01–01 8 20 202 2004–02–01 16 23 You want the analytic model to: • Calculate aggregates for the NUM_SALES field. • Save the aggregates for the NUM_SALES field. • Establish hierarchies for the DEPT and SOMEDATE dimensions.
PAGE 110
Creating Hierarchies Chapter 9 Data Cube or Dimension to Map SOMEDATE dimension Field in Main Record SOMEDATE field Data type: Date SALES data cube NUM_SALES field Data type: Number PRICE_PER_UNIT data cube PRICE_PER_UNIT field Data type: Number 8. Select the AGGRDATE record as the aggregate record. The AGGRDATE database record currently contains no data.
PAGE 111
Chapter 9 Creating Hierarchies • DEPT_TREE Parents Children (no parent —root) GBL GBL US GBL LAT AM US 101 US 102 US 103 LAT AM 201 LAT AM 202 • DATE_TREE Parents Children (no parent — root) CAL2004 CAL2004 JAN CAL2004 FEB JAN 2004–01–01 JAN 2004–01–02 FEB 2004–02–01 11.
PAGE 112
Creating Hierarchies Chapter 9 Fully Qualified Member Name GBL.US DEPARTMENT TREE_DATE NUM_SALES US 2004–01–01 5 US 2004–01–02 2 US 2004–02–01 0 LAT AM 2004–01–01 8 LAT AM 2004–01–02 0 LAT AM 2004–02–01 16 101 CAL2004 1 102 CAL2004 2 103 CAL2004 4 201 CAL2004 8 202 CAL2004 16 101 JAN 1 102 JAN 2 103 JAN 4 CAL2004.JAN.2004–01–01 GBL.US CAL2004.JAN.2004–01–02 GBL.US CAL2004.FEB.2004–02–01 GBL.LAT AM CAL2004.JAN.2004–01–01 GBL.LAT AM CAL2004.JAN.2004–01–02 GBL.
PAGE 113
Chapter 9 Creating Hierarchies Fully Qualified Member Name GBL.LAT AM.201 DEPARTMENT TREE_DATE NUM_SALES 201 JAN 8 202 JAN 0 101 FEB 0 102 FEB 0 103 FEB 0 201 FEB 0 202 FEB 16 GBL CAL2004 31 GBL JAN 15 GBL FEB 16 US CAL2004 7 US JAN 7 US FEB 0 LAT AM CAL2004 24 CAL2004.JAN GBL.LAT AM.202 CAL2004.JAN GBL.US.101 CAL2004.FEB GBL.US.102 CAL2004.FEB GBL.US.103 CAL2004.FEB GBL. LAT AM.202 CAL2004.FEB GBL. LAT AM.203 CAL2004.FEB GBL CAL2004 GBL CAL2004.
PAGE 114
Creating Hierarchies Chapter 9 Fully Qualified Member Name GBL.LAT AM DEPARTMENT TREE_DATE NUM_SALES LAT AM JAN 8 LAT AM FEB 16 CAL2004.JAN GBL. LAT AM CAL2004.FEB • If you select to persist all aggregates of the DEPT_TREE dimension and to persist none of the aggregates of the DATE_TREE dimension on the Dimensions tab of the cube collection’s properties, the following rows are persisted in the aggregate record: Note. Italicized values are the actual persisted aggregate members.
PAGE 115
Chapter 9 Creating Hierarchies • If you select to persist only the root aggregations of the DEPT_TREE dimension and to persist none of the aggregates of the DATE_TREE dimension on the Dimensions tab of the cube collection’s properties, the following rows are persisted in the aggregate record: Note. Italicized values are the actual persisted aggregate members. The Fully Qualified Member Name field is not a database value.
PAGE 116
Creating Hierarchies Chapter 9 Parent Member Override User Function Child 100 120 110 111 (leaf) 110 112 (leaf) 120 121 (leaf) The following table describes the hierarchy of the TRANS_DATE dimension that is associated with the QrtrlyTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child. Note.
PAGE 117
Chapter 9 Creating Hierarchies Parent Member Override User Function Child USA TX USA IL (leaf) Assume that a SALES data cube exists in the cube collection, and the three dimensions of this example are attached to this data cube. Read the instructions carefully before analyzing the following table; the table describes two methods that the analytic calculation engine can use to calculate hierarchies.
PAGE 118
Creating Hierarchies Row 3 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Q1 NY NA Q1 TX NA Jan USA NA Jan CA NA Jan NY NA 100 Use member override user function: USA 4 Winning aggregate: 100 Use member override user function: USA 5 Winning aggregate: 100 Use member override user function: USA 6 Winning
PAGE 119
Chapter 9 Creating Hierarchies Row 8 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Jan TX NA Feb USA NA Feb CA NA Feb NY NA Feb TX NA 100 Use member override user function: USA 9 Winning aggregate: 100 Use member override user function: USA 10 Winning aggregate: 100 Use member override user function: USA 11 Winn
PAGE 120
Creating Hierarchies Row 13 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Q1 USA NA Q1 CA NA Q1 NY NA Q1 TX NA Jan USA NA 110 Use member override user function: 14 Winning aggregate: 110 Use member override user function: 15 Winning aggregate: 110 Use member override user function: 16 Winning
PAGE 121
Chapter 9 Creating Hierarchies Row 18 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Jan CA NA Jan NY NA Jan TX NA Feb USA NA Feb CA NA 110 Use member override user function: 19 Winning aggregate: 110 Use member override user function: 20 Winning aggregate: 110 Use member override user function: 21 Winni
PAGE 122
Creating Hierarchies Row 23 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Feb NY NA Feb TX NA Q1 USA NA Q1 CA NA 110 Use member override user function: 24 Winning aggregate: 110 Use member override user function: 25 Winning aggregate: 120 Use the dimension override user function because a m
PAGE 123
Chapter 9 Creating Hierarchies Row 27 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Q1 NY NA Q1 TX NA Jan USA NA Jan CA NA 120 Use the dimension override user function because a member override user function does not exist for this member: 28 Winning aggregate: 120 Use the dimension override user function because a
PAGE 124
Creating Hierarchies Row 31 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Jan NY NA Jan TX NA Feb USA NA Feb CA NA 120 Use the dimension override user function because a member override user function does not exist for this member: 32 Winning aggregate: 120 Use the dimension override user function because
PAGE 125
Chapter 9 Creating Hierarchies Row 35 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function Feb NY NA Feb TX NA Winning aggregate: USA CA 120 Use the dimension override user function because a member override user function does not exist for this member: 36 Winning aggregate: 120 Use the
PAGE 126
Creating Hierarchies Row 39 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 111 Winning aggregate: DEPT Dimension Members Priority 3 Cube Dimension Override User Function NY TX Winning aggregate: NA Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension.
PAGE 127
Chapter 9 Creating Hierarchies Row 43 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 DEPT Dimension Members Priority 3 Cube Dimension Override User Function 111 Jan NY NA 111 Jan TX NA 111 Feb Winning aggregate: NA Note. No winning aggregate. Because this row does not contain any aggregates, use the data cube’s rule for calculating values. 44 Note. No winning aggregate.
PAGE 128
Creating Hierarchies Row 48 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 DEPT Dimension Members Priority 3 Cube Dimension Override User Function 111 Feb TX NA 112 Winning aggregate: USA CA NY TX Note. No winning aggregate. Because this row does not contain any aggregates, use the data cube’s rule for calculating values.
PAGE 129
Chapter 9 Creating Hierarchies Row 53 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 112 Jan DEPT Dimension Members Priority 3 Winning aggregate: Cube Dimension Override User Function NA USA Use the dimension override user function because a member override user function does not exist for this member. 54 112 Jan CA NA 112 Jan NY NA 112 Jan TX NA 112 Feb Winning aggregate: NA Note. No winning aggregate.
PAGE 130
Creating Hierarchies Row 58 Chapter 9 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 DEPT Dimension Members Priority 3 Cube Dimension Override User Function 112 Feb CA NA 112 Feb NY NA 112 Feb TX NA 121 Winning aggregate: USA CA NY TX Note. No winning aggregate. Because this row does not contain any aggregates, use the data cube’s rule for calculating values.
PAGE 131
Chapter 9 Creating Hierarchies Row 65 TRANS_DATE Dimension Members Priority 2 ACCT Dimension Member Priority 1 121 Jan DEPT Dimension Members Priority 3 Winning aggregate: Cube Dimension Override User Function NA USA Use the dimension override user function because a member override user function does not exist for this member. 66 121 Jan CA NA 121 Jan NY NA 121 Jan TX NA 121 Feb Winning aggregate: NA Note. No winning aggregate.
PAGE 132
Creating Hierarchies Row 70 Chapter 9 ACCT Dimension Member Priority 1 TRANS_DATE Dimension Members Priority 2 DEPT Dimension Members Priority 3 Cube Dimension Override User Function 121 Feb CA NA 121 Feb NY NA 121 Feb TX NA Note. No winning aggregate. Because this row does not contain any aggregates, use the data cube’s rule for calculating values. 71 Note. No winning aggregate. Because this row does not contain any aggregates, use the data cube’s rule for calculating values. 72 Note.
PAGE 133
Chapter 9 Creating Hierarchies Example: Creating a Hierarchy with Mixed Aggregate and Detail Members In this example, the analytic model contains a BONUS cube collection that calculates the bonus for a group of employees.
PAGE 134
Creating Hierarchies Chapter 9 1. Create a data cube called TOTAL_BONUS. 2. Attach the EMPLOYEE_BONUS dimension to the TOTAL_BONUS data cube. 3. Add the TOTAL_BONUS data cube to the BONUS cube collection. In this example’s hierarchy, all the members are detail members except for the hierarchy root member. Because aggregate user functions are only used to calculate aggregate members, you should create a regular formula for the TOTAL_BONUS data cube to calculate its aggregates.
PAGE 135
CHAPTER 10 Creating Rules, Formulas, and User Functions This chapter provides overviews of rules, formulas, and user functions, filter user functions, and design time rule error messages and discusses how to: • Define and edit data cube formulas. • Define and edit user functions. • Work with the elements of rules. • Perform exceptions to the rule. • Work with circular formulas and circular systems.
PAGE 136
Creating Rules, Formulas, and User Functions Chapter 10 Insert an equal symbol into the rule. Insert an AND operator into the rule. Insert an OR operator into the rule. Insert a NOT operator into the rule. Paste a built-in function and its arguments into the rule. Paste a data cube name into the rule. Paste a dimension name into the rule. Paste a member reference into the rule. Paste a user function into the rule. Exit the formula without canceling the changes or validating the formula.
PAGE 137
Chapter 10 Creating Rules, Formulas, and User Functions Filter User Functions You apply a filter user function to a specific dimension, on the Dimensions tab of the cube collection’s properties. See Chapter 7, “Creating Cube Collections,” Defining Additional Cube Collection Dimension Properties, page 60. This section provides overviews of: • Data filters. • Dimension member filters.
PAGE 138
Creating Rules, Formulas, and User Functions Chapter 10 Dimension Member Filters You can create filter user functions to display only the dimension members that are referenced in the filter function. For example, this is the formula for the FILTER_DIGITAL_CAMERAS filter user function, which is applied to the PRODUCTS dimension: IF(MEMBER(PRODUCTS) = [PRODUCTS:Digital Cameras], RETURN(1), RETURN(0)) In this example, the end user only has access to the Digital Cameras member of the PRODUCTS dimension.
PAGE 139
Chapter 10 Creating Rules, Formulas, and User Functions Note. When %1 or %2 appears in this table, it denotes that the actual error message includes context-specific information. For example, the Invalid Dimension %1 error message would yield the error Invalid dimension PRODUCTS in the Output window if a rule referred to a nonexistent PRODUCTS dimension. Error Message Description A dimension argument cannot be used here. An invalid argument was passed to the function.
PAGE 140
Creating Rules, Formulas, and User Functions Error Message Duplicate dimensions in member references. Chapter 10 Description Two or more member references in a data cube slice use the same dimension. A data cube slice can refer to only one member from each dimension. For this reason, you must remove one of the clashing member references. See Enterprise PeopleTools 8.49 PeopleBook: Using PeopleSoft Applications, “Working With Pages,” Slicing Analytic Grid Data. Error in ARGUMENTS of user function %1.
PAGE 141
Chapter 10 Creating Rules, Formulas, and User Functions Error Message Invalid member reference. Description You incorrectly entered a member reference. When this error message occurs, check for one of these problems: • The dimension name in the member reference is not spelled correctly. • The member name in the member reference is not spelled correctly. Invalid member reference syntax. Valid syntax is [DIMENSION:Member]. A member reference uses invalid syntax.
PAGE 142
Creating Rules, Formulas, and User Functions Chapter 10 Circular Reference If a data cube’s formula refers directly or indirectly to a current value of the same data cube, the analytic calculation engine generates a circular reference error. Following are some examples of circular references: Case 1 A = A + B When the analytic calculation engine evaluates the formula A + B, the analytic calculation engine changes the value of A.
PAGE 143
Chapter 10 Creating Rules, Formulas, and User Functions Syntax Error When you receive a syntax error, the current rule does not follow the basic guidelines for a rule. This is often the result of a typographical error. Possible violations of the rule guidelines include: • An arithmetic operator does not have a value on both sides. For example, the expression A+B+ generates a syntax error because the second plus operator does not have a value on both sides.
PAGE 144
Creating Rules, Formulas, and User Functions Chapter 10 1. Perform one of these actions: • To define a new user function, select Part, New, User Function. The Edit Part Name dialog box appears. Enter the name for the user function and click OK. • To edit an existing user function, select the user function whose formula you want to edit. 2. Click inside the rule bar. 3. Enter a new rule or edit the existing rule.
PAGE 145
Chapter 10 Creating Rules, Formulas, and User Functions Some functions do not take any arguments because they do not require additional information to calculate a result. For example, the PI function returns the mathematical constant pi. Because this function does not require any information, it does not take any arguments. Nevertheless, you must still follow the function name with parentheses.
PAGE 146
Creating Rules, Formulas, and User Functions Chapter 10 Comparison Operators You can compare the values of two expressions using one of PeopleSoft Analytic Calculation Engine’s comparison operators. A comparison returns either a True value (1) or a False value (0), depending on the values of the two expressions. Note. The analytic calculation engine always interprets a nonzero value as True and a zero value as False.
PAGE 147
Chapter 10 Creating Rules, Formulas, and User Functions Logical Operators A logical operator determines whether a condition is true. This table describes the logical operators: Logical Operator Meaning Syntax .NOT. Condition is not True. .NOT. Condition .AND. Condition1 is True and Condition2 is True. Condition1 .AND. Condition2 .OR. Condition1 is True or Condition2 is True. Condition1 .OR.
PAGE 148
Creating Rules, Formulas, and User Functions Predefined Constant #ALL Chapter 10 Definition Use this predefined constant as the last argument of the CHILDCOUNT or FORCHILDREN functions to return all of a dimension member’s children, including grandchildren. If you do not specify a dimension member, this constant returns all of the children and grandchildren of the dimension member that is attached to the data cube that is currently being calculated. Note.
PAGE 149
Chapter 10 Creating Rules, Formulas, and User Functions Predefined Constant Definition Use this predefined constant as the second argument in the FORMEMBERS function to loop through the dimension members in a forward direction. #FORWARD See Chapter 11, “Using Built-in Functions in Analytic Models,” FORMEMBERS, page 157. #N/A Use this predefined constant to test whether a value in a data cube is not available, or to return N/A as a result. #PI The value of Π (3.
PAGE 150
Creating Rules, Formulas, and User Functions Chapter 10 You can use parentheses to override the precedence of operators. For example, the rule (3 + 2) * 4 evaluates as 20, because the analytic calculation engine first evaluates the operation within parentheses. You can nest parentheses to exercise more control of precedence; the operations within the inner sets of parentheses are evaluated first.
PAGE 151
Chapter 10 Creating Rules, Formulas, and User Functions If an aggregate member and detail/leaf member share the same name, use the following syntax to reference the desired member: • [DIMENSION_NAME:NODE.Member] Access an aggregate member. • [DIMENSION_NAME:DETAIL.Member] Access a detail or leaf member. Note. Navigation related functions such as PREV, NEXT, and PREVSELF operate on detail member names that are persisted in the main record. These functions do not use trees to determine the order of members.
PAGE 152
Creating Rules, Formulas, and User Functions Chapter 10 INC(&Profit, REVENUE); DEC(&Profit, EXPENSE) The lifetime of a variable is a single evaluation of the rule; the value of a variable is not preserved across multiple evaluations. Inserting a Built-in Function into a Rule To insert a built-in function into a rule: 1. Select the place in the rule where you want to add the function. 2. Click the Paste Built-in Function button. The Choose Built-in Function drop-down list box appears. 3.
PAGE 153
Chapter 10 Creating Rules, Formulas, and User Functions Note. PeopleSoft recommends that you do not enter an assumption directly into a rule. Instead, you should create a data cube for the assumption and refer to the data cube in the formula. For example, do not calculate TAXES by multiplying INCOME by 0.38. Instead, create a data cube called TAX_RATE and enter 0.38 as its value. Then calculate TAXES by multiplying INCOME by TAX_RATE.
PAGE 154
Creating Rules, Formulas, and User Functions Chapter 10 1. Place the cursor at the position in the rule where you want to enter a member reference. 2. Select Edit, Paste Member Ref... The Choose Member Reference dialog box appears. 3. Click the dimension for which you want to enter a member reference. The dimension and a generic member reference appears in the rule bar. 4. Highlight the word member. 5. Replace the word member with the name of the dimension member.
PAGE 155
Chapter 10 Creating Rules, Formulas, and User Functions • Create different calculations for different dimension members. • Create different calculations for different groups of members. Understanding Exceptions to the Rule A typical rule contains a formula for an entire data cube that the analytic calculation engine uses to calculate every value in the data cube. If you want some values of a data cube to calculate in a different manner than other values, you must create an exception to the rule.
PAGE 156
Creating Rules, Formulas, and User Functions Chapter 10 Understanding the Calculation of More Than One Member The following formula provides an example of a calculation for more than one member.
PAGE 157
Chapter 10 Creating Rules, Formulas, and User Functions d. Enter a comma. 4. Perform these steps to enter a final Condition:Result pair to return a result for all other members in the dimension: a. Enter #DEFAULT as the condition. #DEFAULT instructs the function to return the final result for all other members. b. Enter a colon to separate the condition from the result. c. Enter the result for all other members in the dimension. d. Enter a closing parenthesis.
PAGE 158
Creating Rules, Formulas, and User Functions Chapter 10 The CASE function evaluates multiple conditions and returns the result for the first true condition. Each Condition:Result pair is separated by a comma. See Chapter 11, “Using Built-in Functions in Analytic Models,” CASE, page 144. In the preceding formula, the CASE function compares the invest type for an investment to each member in the TYPES dimension. The formula uses a member reference (for example, [TYPES:Stock] ) to refer to each member.
PAGE 159
Chapter 10 Creating Rules, Formulas, and User Functions In this example, none of the data cubes refer directly to themselves. However, each data cube refers indirectly to itself by means of the other data cubes: • BONUS refers to NET_INCOME. • NET_INCOME refers to EXPENSE. • EXPENSE refers to BONUS.
PAGE 160
Creating Rules, Formulas, and User Functions Chapter 10 • Maximum change in values: When the analytic calculation engine has calculated the cells within the circular system for the specified maximum number of times, the circular system is considered to be resolved.
PAGE 161
Chapter 10 Creating Rules, Formulas, and User Functions General tab Description Enter a description of the analytic model. Note. This field pertains to the analytic model as a whole, not to circular formula options. Resolve circular system through iteration Select to attempt to resolve all of an analytic model’s circular systems through iteration. By default, this option is disabled.
PAGE 163
CHAPTER 11 Using Built-in Functions in Analytic Models This chapter provides reference information about the functions that are delivered with PeopleSoft Analytic Calculation Engine. Built-in Function Reference Use the following built-in functions in an analytic model’s rules and user functions: ABS Syntax ABS(Data) Description The ABS function returns the absolute (positive) value of Data. Returns The absolute (positive) value of Data.
PAGE 164
Using Built-in Functions in Analytic Models Chapter 11 Returns The arc-cosine of Data. Example The following examples employ the ACOS built-in function: • ACOS(0.5) returns 1.0471975512 (angle in radians). • ACOS(0.5) * 180 / PI( ) returns 60 (angle in degrees). • ACOS(SQRT(2) / 2) returns 0.7853981634 (angle in radians). • ACOS(SQRT(2) / 2) * 180 / PI( ) returns 45 (angle in degrees). ARGUMENTS Declaration Syntax ARGUMENTS(argument1), argument2...
PAGE 165
Chapter 11 Using Built-in Functions in Analytic Models Description The ASC function returns the first character of the Text argument to its ASCII equivalent number (for example, a number between 0-255). Use this function to convert a character into its ASCII value. Returns The first character of the Text argument to its ASCII equivalent number. Example For a cube formatted as a number, ASC("ABC") returns the 65. ASIN Syntax ASIN(Data) Description The ASIN function returns the arc-sine of Data.
PAGE 166
Using Built-in Functions in Analytic Models Chapter 11 • You can associate members in one dimension with members in another dimension, and then look up an associated value for each member in the first dimension. For example, you can associate each employee with a job, and then look up the job salary for each employee. Looking Up a Value at a Position To look up a value at a particular position, use the position number for the member argument.
PAGE 167
Chapter 11 Using Built-in Functions in Analytic Models Pushing Down Parent Member Data The following are examples of user functions that push down parent member data: • AT (DIMENSION, Parent(DIMENSION), THISCUBE() * 0.2) • AT (DIMENSION, Parent(DIMENSION), THISCUBE() / CHILDCOUNT(DIMENSION, #DIRECT)) ATAN Syntax ATAN(Data) Description The ATAN function returns the arc-tangent of Data. The result is the angle (in radians) whose tangent equals Data.
PAGE 168
Using Built-in Functions in Analytic Models Chapter 11 You normally use the BREAK function within an IF function to break out of a loop when a specified condition is achieved. To return Just right from the formula, ENOUGH_ALREADY must contain a value from the sequence 2, 6, 14, 30, and so on. CASE Syntax CASE(Condition A : Result A, Condition B : Result B {,...
PAGE 169
Chapter 11 Using Built-in Functions in Analytic Models Example Suppose you wish to calculate the monthly and yearly change in a data cube called SALES. If SALES uses a dimension called MONTHS, use the following formula to calculate the MONTHLY_CHANGE data cube: CHANGE(MONTHS, SALES) Because the Count argument is omitted, the program assumes it to be 1. Consequently, the program calculates the change in SALES from the previous month to the month being calculated.
PAGE 170
Using Built-in Functions in Analytic Models Chapter 11 Description The CHR function returns the equivalent ASCII character of the number argument. The number must be in range from 0 to 255; otherwise, an invalid type error with be thrown. Returns The equivalent ASCII character of the number argument. Example For a cube formatted as text, CHR(65) returns the character A. CONSOL Syntax CONSOL(Dimension, Data) Description The CONSOL function returns the value of Data for the total member of Dimension.
PAGE 171
Chapter 11 Using Built-in Functions in Analytic Models CUBEID Syntax CUBEID(Cube) Description The CUBEID function returns the internal ID of the cube in the analytic calculation engine. Note that the actual ID for the cube may vary in the analytic calculation engine when the model has been changed. For example, when a part is added or deleted. Do not use absolute numbers to compare the return of the CUBEID function.
PAGE 172
Using Built-in Functions in Analytic Models Chapter 11 CUMAVG(MONTHS, SALES, 6) Note that for the first five months, the CUMAVG function cannot look back six months, because this would go back before the first month in the MONTHS dimension. The program solves this problem by averaging the sales for all months up to the month being calculated. After the first five months, the function averages the sales for the six months up to the month being calculated.
PAGE 173
Chapter 11 Using Built-in Functions in Analytic Models Example 1 Suppose an analytic model contains a data cube called ADVERTISING_BY_PRODUCT and a data cube called UNITS_SOLD. Both data cubes use a dimension called PRODUCTS. Use the following formula to calculate the average units sold for all products: DAVG(PRODUCTS, UNITS_SOLD) The DAVG function does not include a condition, so the function averages UNITS_SOLD for all members in the PRODUCTS dimension.
PAGE 174
Using Built-in Functions in Analytic Models Chapter 11 Following is a more useful example of the DAY function: suppose you define a data cube called DAILY_RECEIPTS that uses a dimension called DAYS. You want to calculate the average receipts for each day of the month. In other words, you want to know the average receipts for the first day of each month, the average receipts for the second day of each month, and so on. To do this, create a dimension called DAY_NUM that contains members numbered 1 to 31.
PAGE 175
Chapter 11 Using Built-in Functions in Analytic Models Description The DDB function returns the depreciation on an asset using the Double Declining Balance method. This is an accelerated depreciation method. Parameters Parameter Description Cost The cost of the asset. Salvage The worth of the asset at the end of its useful life. Life The number of periods in the asset’s useful life. Period The period for which you wish to determine the depreciation.
PAGE 176
Using Built-in Functions in Analytic Models Chapter 11 See Also Chapter 11, “Using Built-in Functions in Analytic Models,” FOR, page 156 and Chapter 11, “Using Built-in Functions in Analytic Models,” WHILE, page 197. DLOOKUP Syntax DLOOKUP(Dimension, Data, Condition, {Direction}) Description The DLOOKUP function returns Data for the first Member in Dimension where Condition is true. If Direction is omitted or zero, the function scans forward from the first member.
PAGE 177
Chapter 11 Using Built-in Functions in Analytic Models Returns The maximum of Data for the members in Dimension where Condition is True. If Condition is omitted, DMAX returns the maximum of Data for all members in Dimension. If Data is omitted, DMAX returns the maximum of the data cube being calculated, for all members up to the current member in Dimension. Example Suppose that an analytic model contains a data cube called ADVERTISING_BY_PRODUCT and a data cube called UNITS_SOLD.
PAGE 178
Using Built-in Functions in Analytic Models Chapter 11 MIN(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= 10000) In this case, the function finds the minimum units sold only for the products where ADVERTISING_BY_PRODUCT is greater than or equal to 10000. For an example of how to tabulate data for a series of ranges, see the entry for the DAVG function.
PAGE 179
Chapter 11 Using Built-in Functions in Analytic Models Returns The value of e. Example These examples employ the E built-in function: • E( ) returns 2.7182818285. • E( ) ^ 5 returns 148.4131591026 (e raised to the 5th power). FIND Syntax FIND(Text Original String, Text Sub String, Number Starting Position) Description Use the FIND function to find a substring in the original string passed in starting from a specified start position in the original string.
PAGE 180
Using Built-in Functions in Analytic Models Chapter 11 Parameters Parameter Dimension Description The dimension to test. Returns The FIRST function returns the first detail member regardless if the detail member is created out of a tree that is attached to the dimension. The FIRST function also returns True if the first member of Dimension is being calculated; otherwise, it returns False. FOR Syntax FOR(Index, Start, Finish, Loop Body) Description The FOR function loops through a series of values.
PAGE 181
Chapter 11 Using Built-in Functions in Analytic Models Note. The third and fourth arguments are optional. Description The FORCHILDREN function loops through all child members of a dimension’s parent member, unless you interrupt the FORCHILDREN function with a BREAK function. Parameters The FORCHILDREN function takes two required arguments and two optional arguments. The first and second arguments are required. The third and fourth arguments are optional.
PAGE 182
Using Built-in Functions in Analytic Models Chapter 11 Parameters Parameter Description Dimension The dimension to use. Direction The direction to loop through the members (#FORWARD or #REVERSE). Expression The expression to evaluate for each iteration.
PAGE 183
Chapter 11 Using Built-in Functions in Analytic Models Description The FV function returns the Future Value of an investment with a present value of PV, where Pmt is invested for NPer periods at Rate per period. If Type is omitted or zero, FV assumes the investment is an ordinary annuity. If Type is nonzero, FV assumes the investment is an annuity due. Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.
PAGE 184
Using Built-in Functions in Analytic Models Chapter 11 To calculate group averages of all members that meet a condition, use an IF function as the expression, with #N/A as the third argument. For example, to calculate average officer salaries by department, you could use IF(IS_OFFICER, EMPLOYEE_SALARY, #N/A) instead of EMPLOYEE_SALARY in the formula above.
PAGE 185
Chapter 11 Using Built-in Functions in Analytic Models Example Suppose that you want to maximize sales information by product. Create an analytic model definition that contains the following dimensions: 1. TRANSACTIONS, which contains a series of sales transactions. 2. PRODUCTS, which contains a dimension of products. Define the following data cubes: 1. SALE_AMOUNT, which uses the TRANSACTIONS dimension. This data cube contains the amount of each sale. 2.
PAGE 186
Using Built-in Functions in Analytic Models Chapter 11 3. MINIMUM_SALES_BY_PRODUCT, which uses the PRODUCTS dimension. Calculate this data cube with the following formula: GROUPMAX(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD) You can read this formula as follows: Find the maximum transactions’ sale amounts by product. To calculate group maximums of all members that meet a condition, use an IF function as the expression, with #N/A as the third argument.
PAGE 187
Chapter 11 Using Built-in Functions in Analytic Models GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_BRANCH, EMPLOYEE_DEPT) You can read this formula as follows: Sum the employees’ salaries by branch and department. As this example demonstrates, you can summarize detail information for a combination of dimensions by using an association for each dimension. Example 3 In many cases, it is useful to summarize information by date. In these cases, use a data cube with a Date format instead of an association.
PAGE 188
Using Built-in Functions in Analytic Models Chapter 11 GROW(MONTHS, SALES_START, ANNUAL_GROWTH / 12) Note that you must divide ANNUAL_GROWTH by 12, because the GROW function expects a growth rate per member, and the members in this case are months. Note. For the GROW function to return meaningful results, the Start Value and Growth Rate arguments should not use the dimension indicated by the dimension argument.
PAGE 189
Chapter 11 Using Built-in Functions in Analytic Models INCDATE Syntax INCDATE(Date, Months, Years) Description The INCDATE returns the value of Date incremented by Months and Years. Returns The value of Date incremented by Months and Years. Example If Date contains the date 2001/04/18, INCDATE(Date, 3, 2) returns the date 2003/07/18. If Date falls on the last day of a month, INCDATE returns a date that falls on the last day of a month, even if it has to change the day.
PAGE 190
Using Built-in Functions in Analytic Models Chapter 11 When a formula uses the INPUT function, the analytic calculation engine evaluates the formula for a particular cell to determine whether it should be an input cell. As long as the input condition in the formula refers to input cubes and member references, no recalculation is necessary to ensure that the correct cells are treated as input cells.
PAGE 191
Chapter 11 Using Built-in Functions in Analytic Models ISINPUT(Cube with formula) returns False. The ISINPUT function provides an easy way to filter tables so that they show input cells. The ISINPUT function takes a single argument, which must be a cube. To work well with filter functions, the function maps totals to the first member of the dimension if a first member exists.
PAGE 192
Using Built-in Functions in Analytic Models Chapter 11 The initial values in the cash flow should be negative to represent a cash outflow. The remaining values may be all positive (representing cash inflows) or a combination of positive and negative. Returns The Internal Rate of Return for Cash Flow. Guess can be omitted (or zero) in most cases. If IRR is unable to find the Internal Rate of Return, it returns an error value.
PAGE 193
Chapter 11 Using Built-in Functions in Analytic Models Example LEFT("StringFun", 6) returns String. LEN Syntax LEN(Text) Description The LEN FUNCTION returns the number of characters in text string. Returns The number of characters in text string. Example LEN("StringFun") returns 9. LOWER Syntax LOWER(Text) Description The LOWER FUNCTION returns text converted to lower case. Returns Text converted to lower case. Example LOWER("StringFun") returns stringfun.
PAGE 194
Using Built-in Functions in Analytic Models Chapter 11 If Case Sensitive is omitted or zero, the function ignores case. If Case Sensitive is nonzero, the function performs a case sensitive match. Match Type can be one of these values: • 0: Text contains Pattern. • 1: Text matches Pattern exactly. • 2: Text begins with Pattern. • 3: Text ends with Pattern. If the Match Type argument is omitted, it is assumed to be zero (text contains Pattern). Returns True if Text Expression or Text Cube matches Pattern.
PAGE 195
Chapter 11 Using Built-in Functions in Analytic Models MAX(A, B, C, D) returns A. You can sometimes simplify formulas by using the MAX function instead of the IF function. For example, suppose an analytic model contains data cubes called CASH_BALANCE and CASH_MINIMUM.
PAGE 196
Using Built-in Functions in Analytic Models Chapter 11 Example Suppose that a cube collection contains a data cube called SALES that uses dimensions called PEOPLE and MONTHS.
PAGE 197
Chapter 11 Using Built-in Functions in Analytic Models See Also Chapter 11, “Using Built-in Functions in Analytic Models,” IF, page 164 and Chapter 11, “Using Built-in Functions in Analytic Models,” NUMMEMBERS, page 177. MID Syntax MID(Text, Start, {Count}) Description The MID function returns Count characters from text, beginning with Start. If Count is omitted, returns all characters to the end of text. Example MID("StringFun", 6, 3) returns Fun. MIN Syntax MIN(X, Y) MIN(arg1, arg2, . . .
PAGE 198
Using Built-in Functions in Analytic Models Chapter 11 MOD Syntax MOD(X, Y) Description The MOD function returns the remainder of X divided by Y. If Y is zero, MOD returns an error value. Returns The remainder of X divided by Y. If Y is zero, MOD returns an error value. Example The following examples employ the MOD built-in function: • MOD(10, 4) returns 2. • MOD(15, 10) returns 5. • MOD(15, 5) returns 0. • MOD(15, 0) returns an error value.
PAGE 199
Chapter 11 Using Built-in Functions in Analytic Models See the entries for DAVG and MEMBER if you are unfamiliar with these functions. For each MONTH_NUM member in AVG_SALES_BY_MONTH, the formula averages all Monthly Sales for which the month of the year equals the index of the MONTH_NUM member.
PAGE 200
Using Built-in Functions in Analytic Models Chapter 11 The condition of the IF statement ensures that the month being calculated is after the first six months and before the last six months of the analytic model. If the condition is true, the IF function returns the centered moving average calculated by the second argument; otherwise, the IF function returns zero.
PAGE 201
Chapter 11 Using Built-in Functions in Analytic Models The initial values in the cash flow should be negative to represent a cash outflow. The remaining values may be all positive (representing cash inflows), or a combination of positive and negative values.
PAGE 202
Using Built-in Functions in Analytic Models Chapter 11 Description The OPRID function returns the userID of the user who currently has the analytic instance checked out. Use the OPRID function within a filter user function whose purpose is to limit userID access to only certain rows of data. Example IF(AT(USERID, TXT2MBR(USERID, OPERID()), DEPT_CUBE) = RETURN(0)) MEMBER(DEPT_DIM),RETURN(1),⇒ This filter user function restricts user access to bonus amount data.
PAGE 203
Chapter 11 Using Built-in Functions in Analytic Models If the userID matches the member in the USERID dimension, the AT function searches for the coordinates of the userID member that is returned by TXT2MBR and returns the corresponding value of DEPT_CUBE. On the right-hand side of the equation, the MEMBER function returns the corresponding member of DEPT_DIM. 3.
PAGE 204
Using Built-in Functions in Analytic Models Chapter 11 See Also Chapter 9, “Creating Hierarchies,” Pushed Down Data, page 80 PCT Syntax PCT(Dimension, Data, {Count}) Description The PCT function returns the percentage change between the value of Data for the Member being calculated and the value of Data for Count members back. If Count is omitted, it is assumed to be 1. Returns The percentage change between the value of Data for the Member being calculated and the value of Data for Count members back.
PAGE 205
Chapter 11 Using Built-in Functions in Analytic Models This formula calculates the 50th percentile (also knows as median) of the first 10 students for each test. PI Syntax PI( ) Description The PI function returns the value of PI (3.1415926536), the ratio of a circle’s circumference to its diameter. The following formula calculates the area of a circle: PI( ) * RADIUS ^ 2 Returns The value of PI (3.1415926536), the ratio of a circle’s circumference to its diameter.
PAGE 206
Using Built-in Functions in Analytic Models Chapter 11 Example If you take out a loan for 50,000 USD at a rate of 14 percent per year and 120 monthly payments, you can create a PAYMENT cube and compute the payment required to repay the loan as follows: PMT(0.14 / 12, 120, 50000, 0) = -776.33 If the loan has a balloon payment of 30,000 USD at the end of the 120 months, compute the payment as follows: PMT(0.14 / 12, 120, 50000, -30000) = -660.
PAGE 207
Chapter 11 Using Built-in Functions in Analytic Models PCT_RECV_IN_2_MONTHS * PREV(MONTHS, SALES, 3) • TOTAL_MONTHLY_RECEIPTS data cube formula: RECV_IMMEDIATELY + RECV_IN_1_MONTH + RECV_IN_2_MONTHS + RECV_IN_3_MONTHS RECV_IMMEDIATELY contains the amount received from the current month’s sales, RECV_IN_1_MONTH contains the amount received from the previous month’s sales, and so on. Add all of these amounts together to calculate the total receipts for the month.
PAGE 208
Using Built-in Functions in Analytic Models Chapter 11 PV Syntax PV(Rate, NPer, Pmt, FV, {Type}) Description The PV function returns the Present Value of an investment with a future value of FV, where Pmt is received for NPer periods and is discounted at the rate of Rate per period. If Type is omitted or zero, PV assumes that the investment is an ordinary annuity. If Type is nonzero, PV assumes that the investment is an annuity due. Note.
PAGE 209
Chapter 11 Using Built-in Functions in Analytic Models The following formula calculates the third quartile of the scores for each test. QUARTILE(STUDENTS, SCORES, 3) The following formula calculates the second quartile (also known as the median) of the first ten students: QUARTILE(STUDENTS, SCORES, 2, MEMBER(STUDENTS) <= 10) RAND Syntax RAND() Description The RAND function returns a random decimal number greater than or equal to zero and less than one.
PAGE 210
Using Built-in Functions in Analytic Models Chapter 11 Example Suppose that you wish to invest 5,000 USD at the end of each year for 10 years. You can create a data cube called RATE_REQUIRED and calculate the rate of return required to earn 100,000 USD as follows: RATE(10, -5000, 0, 100000) = 14.69% Now suppose that you initially invest 15,000 USD in addition to the yearly payments. Use the following formula: RATE(10, -5000, -15000, 100000) = 7.
PAGE 211
Chapter 11 Using Built-in Functions in Analytic Models Returns The right most Count characters of Text. Example RIGHT("StringFun", 3) returns Fun. ROUND Syntax ROUND(Data, Integer) Note. The Integer argument is optional. Description The ROUND function—when you use only the first argument—returns the value of Data rounded to the nearest whole number.
PAGE 212
Using Built-in Functions in Analytic Models Chapter 11 Example Suppose that you would like to update your sales forecast on a monthly basis, but you also would like to save the original forecast. If the current forecast is stored in a data cube called SALES_FORECAST, you can calculate the ORIGINAL_SALES_FORECAST cube as follows: IF(UPDATE_ORIGINAL, SALES_FORECAST, SELF( )) (See the entry for the IF built-in function if you are unfamiliar with this function.
PAGE 213
Chapter 11 Using Built-in Functions in Analytic Models • SIN(PI( ) / 2) returns 1 (sine of PI / 2 radians). • SIN(45 * PI( ) / 180) returns 0.7071067812 (sine of 45 degrees). SLN Syntax SLN(Cost, Salvage, Life) Description The SLN function returns the depreciation on an asset by using the straight line method, which is a single programming statement. This function returns the same depreciation for each period. Parameters Parameter Description Cost The cost of the asset.
PAGE 214
Using Built-in Functions in Analytic Models Chapter 11 Analyzing a Historical Trend To analyze a historical trend: 1. Calculate the slope for the trend line with this formula for the TREND_SLOPE cube: SLOPE(DATE_DIMENSION, HISTORICAL_DATA, MEMBER(DATE_DIMENSION), MEMBER(MONTHS) <=⇒ LAST_ACTUAL_DATE) HISTORICAL_DATA is the data cube that you want to analyze. DATE_DIMENSION is the dimension used by the data cube, which is normally a date dimension.
PAGE 215
Chapter 11 Using Built-in Functions in Analytic Models Example The following sections provide examples of analyzing a historical trend and analyzing a relationship between data cubes. Example 1: Analyzing a Historical Trend Suppose that you want to analyze the trend in historical sales to forecast future sales. The historical sales are stored in a data cube called ACTUAL_SALES that uses a dimension called MONTHS. The date of the last actual value is stored in a data cube called LAST_ACTUAL_DATE.
PAGE 216
Using Built-in Functions in Analytic Models Chapter 11 Description The SQRT function returns the square root of Data. If the value of Data is negative, SQRT returns an error value. Returns The square root of Data. If the value of Data is negative, SQRT returns an error value. Example These examples employ the SQRT built-in function: • SQRT(25) returns 5. • SQRT(2) returns 1.4142135624. • SQRT(-25) returns an error value.
PAGE 217
Chapter 11 Using Built-in Functions in Analytic Models Parameters Parameter Description Cost The cost of the asset. Salvage The worth of the asset at the end of its useful life. Life The number of periods in the asset’s useful life. Period The period for which you wish to determine the depreciation. Example Suppose that you purchase a machine for 6,000 USD , and you plan to sell it for 500 USD after five years.
PAGE 218
Using Built-in Functions in Analytic Models Chapter 11 TEXT2MBR Syntax TEXT2MBR(Dimension, Text) Description Converts text to the member with that name in Dimension. If there is no member with that name, returns 0. Note. This is essentially a linear lookup function, so be careful when using it with dimensions that have a lot of members. Example TEXT2MBR(MONTHS, "January") returns a new member, January, in the MONTHS dimension. TEXT2NUM Syntax TEXT2NUM (Text) Description Converts Text to a number.
PAGE 219
Chapter 11 Using Built-in Functions in Analytic Models To understand the THIS function, you need to understand how database functions work. A database function scans the members in a dimension to calculate a result. For example, suppose that you define the following formula: DAVG(PRODUCTS, ADVERTISING, SALES > 50000) This formula calculates the average advertising for products with sales over 50,000 USD.
PAGE 220
Using Built-in Functions in Analytic Models Chapter 11 • CHANGE(MONTHS, GROSS_MARGIN) TRUNC Syntax TRUNC(Data) Description The TRUNC function returns the value of Data with the decimals truncated. Returns The value of Data with the decimals truncated. Example The following examples employ the TRUNC built-in function: • TRUNC(14) returns 14. • TRUNC(14.3) returns 14. • TRUNC(14.7) returns 14. UPPER Syntax UPPER(Text) Description The UPPER function returns Text converted to uppercase.
PAGE 221
Chapter 11 Using Built-in Functions in Analytic Models Example Suppose that an analytic model contains a data cube called SCORES that uses dimensions called STUDENTS and TESTS. Use the following formula to calculate the variance of the tests for each student: VAR(TESTS, SCORES) Use the following formula to calculate the variance of scores over 75 percent for each test: VAR(STUDENTS, SCORES, 0, SCORES > 0.
PAGE 222
Using Built-in Functions in Analytic Models Chapter 11 Now suppose that you plan to build a new building in 2006, and you want to spread the building costs over the quarters of that year. On the other hand, you do not want to allocate the building costs to any other years.
PAGE 223
PART 3 Working with Analytic Types Chapter 12 Understanding the Relationship of Analytic Types to Analytic Models
PAGE 224
PAGE 225
CHAPTER 12 Understanding the Relationship of Analytic Types to Analytic Models This chapter discusses: • Purpose of analytic type definitions. • Relationship of record attributes to data caching behavior. Purpose of Analytic Type Definitions You create analytic type definitions for use with PeopleSoft Optimization Framework and PeopleSoft Analytic Calculation Engine.
PAGE 226
Understanding the Relationship of Analytic Types to Analytic Models Chapter 12 5. In the cube collection properties, map the fields in the record to data cubes and dimensions. See Chapter 7, “Creating Cube Collections,” Mapping Data Cubes and Dimensions to Fields, page 59. When updating an analytic model definition, create an analytic type definition during this developmental sequence: 1. Update the records in the analytic type definition. See Enterprise PeopleTools 8.
PAGE 227
Chapter 12 Understanding the Relationship of Analytic Types to Analytic Models Data Cube SALARY input data cube Attached Dimensions These dimensions are attached to the SALARY data cube: • BUSINESS_UNIT • DEPTID • EMPLID • JOBCODE EXPENSE input data cube These dimensions are attached to the EXPENSE data cube: • BUSINESS_UNIT • DEPTID • EMPLID • JOBCODE BONUS_PERCENT input data cube These dimensions are attached to the BONUS_PERCENT data cube: • BUSINESS_UNIT • DEPTID • EMPLID • JOBCODE Note.
PAGE 228
Understanding the Relationship of Analytic Types to Analytic Models Chapter 12 Example of mapping the QE_ACE_EMPLOYEE1_IN cube collection to the QE_ACE_EMPL1 main record Note. This cube collection does not use an aggregate record. On the Field Map tab, you map the fields in the QE_ACE_EMPL1 record to the data cubes and dimensions, as shown in this example: 204 Copyright © 1988-2007, Oracle. All rights reserved.
PAGE 229
Chapter 12 Understanding the Relationship of Analytic Types to Analytic Models Example of mapping data cubes and dimensions to the fields of the QE_ACE_EMPL1 record When mapping dimensions and data cubes, you may want to map dimensions to the key fields in the main record and data cubes to the non-key fields in the main record. The PeopleSoft system, however, does enable you to map dimensions to non-key and data cubes to key fields.
PAGE 230
Understanding the Relationship of Analytic Types to Analytic Models Chapter 12 • SQL tables. • SQL views. • Dynamic views. • Query views. Note. Data cubes that exist in a cube collection that is mapped to a main record with a Read Once attribute cannot exist in any other cube collection that is mapped to a main record with the Read Once attribute.
PAGE 231
Chapter 12 Understanding the Relationship of Analytic Types to Analytic Models • Each time data is updated using the CubeCollection class SetData method. The CubeCollection class is one of the Analytic Calculation Engine classes. See Enterprise PeopleTools 8.49 PeopleBook: PeopleCode API Reference, “Analytic Calculation Engine Classes,” SetData. You can specify the Read Once attribute for the SQL table record type.
PAGE 232
Understanding the Relationship of Analytic Types to Analytic Models Chapter 12 See Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Optimization Framework, “Designing Analytic Type Definitions,” Scenario Management. Records based on dynamic views can be Scenario Managed. For these records, the associated SQL must contain a meta string for qualifying the analytic instance.
PAGE 233
PART 4 Working with Analytic Grids Chapter 13 Creating Analytic Grids
PAGE 234
PAGE 235
CHAPTER 13 Creating Analytic Grids This chapter provides an overview of analytic grid design and discusses how to: • Insert and resize analytic grid controls. • Set analytic grid analytics properties. • Set analytic grid label properties. • Set analytic grid use properties. • Set analytic grid general properties. • Insert and manipulate analytic grid columns. • Set column properties for analytic grids. • Manipulate the analytic grid at runtime.
PAGE 236
Creating Analytic Grids Chapter 13 Producing an analytic grid involves many of the same tasks as generating a regular grid. These include inserting and resizing grid controls, inserting and manipulating grid columns, and setting column properties. In addition, you set certain analytic grid properties by using the Analytics tab, Use tab, Label tab, and General tab in the Analytic Grid Properties dialog box. See Chapter 13, “Creating Analytic Grids,” Setting Column Properties for Analytic Grids, page 224.
PAGE 237
Chapter 13 Creating Analytic Grids You use these PeopleSoft Application Designer areas to create analytic grids: Analytic Grid button Analytic Grid Analytic grid in PeopleSoft Application Designer Analytic Grid button Select and then drag to insert an analytic grid into the page. analytic grid Contains the grid itself. You can modify the grid’s properties by double-clicking to display the Analytic Grid Properties dialog box. (Click anywhere except the column headings.
PAGE 238
Creating Analytic Grids Chapter 13 Navigation Bar Slice Bar Column Axis Data Cubes Dimensions Row Axis Analytic grid at runtime Navigation Bar Enables end users to navigate through the displayed data set. Also contains a link to drag and drop instructional text. Slice Bar Enables end users to view selected portions, or slices, of the data—for instance, the sales of one category of product or the sales from a single region.
PAGE 239
Chapter 13 Creating Analytic Grids Setting Analytic Grid Analytics Properties Access the Analytic Grid Properties dialog box by double-clicking anywhere on the analytic grid other than on the column headings, and use the Analytics tab to set analytic model association and axis display properties: Analytic Grid Properties dialog box, Analytics tab Freeze Column Mode Select this check box if you want to freeze the columns of the grid when it’s displayed to the end user.
PAGE 240
Creating Analytic Grids Chapter 13 See Chapter 7, “Creating Cube Collections,” Presentation Cube Collections, page 56. Record Name Select either a main record or an aggregate record from the cube collection. The aggregate record is selected by default, if applicable. If there’s no aggregate record, this field is populated with the main record. If you’ve selected Freeze Column Mode, only work/derived records are displayed.
PAGE 241
Chapter 13 Creating Analytic Grids See Chapter 13, “Creating Analytic Grids,” Inserting and Manipulating Analytic Grid Columns, page 222. Setting Analytic Grid Label Properties Access the Analytic Grid Properties dialog box by double-clicking the analytic grid, and then click the Label tab: Analytic Grid Properties dialog box, Label tab Display Header Select if you want to display Preferences and the link to download to Excel to the end user in the header.
PAGE 242
Creating Analytic Grids Chapter 13 Note. You can also modify this title at runtime by using the AnalyticGrid classes. Display Navigation Bar Select if you want to display navigational elements for the grid in the header, such as First, Last, View All, and so on. Preferences and the link to download to Excel still display to the end user. Display Slice Bar Select for the slice bar to appear in the analytic grid. By default, this check box is selected.
PAGE 243
Chapter 13 No Auto Select Creating Analytic Grids Select to suppress the system from automatically retrieving data from the analytic calculation engine. If you select No Auto Select, you must use the LoadData method to load the analytic grid with data. See Enterprise PeopleTools 8.49 PeopleBook: PeopleCode API Reference, “Analytic Grid Classes,” LoadData. This item is not available if Freeze Column Mode has been selected.
PAGE 244
Creating Analytic Grids Chapter 13 Collapsible Data Area Select this option so that the data area for your analytic grid can be collapsed into a header bar with an icon that the end user must click to expand it. Selecting Collapsible Data Area enables the Default Initial View to Expanded State check box. Default Initial View to Expanded State Select whether the initial view of the grid is expanded or collapsed. It is expanded by default. Note.
PAGE 245
Chapter 13 Creating Analytic Grids Analytic Grid Properties dialog box, General tab Page Field Name Specify a grid name consisting of any combination of uppercase letters, digits, and the symbols #, $, @, and _. The default is the name of the main record for the analytic grid; however, you can rename the grid as long as you use a unique name for the page or component. This name is used by the PeopleCode GetAnalyticGrid function to create a grid definition. See Enterprise PeopleTools 8.
PAGE 246
Creating Analytic Grids Unlimited Occurs Count (rows) Chapter 13 Sets the occurs count to unlimited, which means that the end user sees all rows of data. (Note that selecting this check box disables the Occurs Count option, because it is no longer applicable.) Note. In addition to setting an occurs count, the developer can set a threshold (by using ACEGRDROWS in PeopleSoft Personalizations) to limit how many rows of data are displayed in the grid.
PAGE 247
Chapter 13 Creating Analytic Grids Note. The analytic grid supports a minimum of 2 columns (one for row axis and one for data) and a maximum of 101 columns (one for column axis and 100 for data). The default maximum number of columns is 41. You can also use the ACEGRDCOLS option in PeopleSoft Personalizations to set the number of columns displayed in the analytic grid. If necessary, the analytic grid provides a scroll bar that enables end users to scroll through all columns.
PAGE 248
Creating Analytic Grids Chapter 13 2. Press DELETE. Moving Analytic Grid Columns on the Layout Tab To move analytic grid columns on the Layout tab: 1. Select a column by clicking its column heading. 2. Drag the column to its new location. 3. Release the mouse button over the column that is to the left of the new location. Note. Remember, the order of columns here determines the order in which they display to end users.
PAGE 249
Chapter 13 Creating Analytic Grids In addition, related display fields and related edit fields behave the same for analytic grids as they do for ordinary grids. See Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Application Designer, “Creating Page Definitions,” Creating Display Control and Related Fields. Manipulating the Analytic Grid at Runtime Your job as an application developer is not finished at design time.
PAGE 251
PART 5 Debugging Analytic Models Chapter 14 Viewing and Debugging Analytic Models Chapter 15 Capturing Analytic Instances
PAGE 252
PAGE 253
CHAPTER 14 Viewing and Debugging Analytic Models This chapter provides an overview of the Analytic Model Viewer and discusses how to: • View analytic model properties. • Use Analytic Model Viewer alongside PeopleSoft Application Designer. • Use the application log fence to provide additional debugging information. Understanding the Analytic Model Viewer The Analytic Model Viewer is a debugging tool with which you can view intermediate results from calculations and modify data when testing calculations.
PAGE 254
Viewing and Debugging Analytic Models Chapter 14 Note. The Analytic Model Viewer utility is only available to system administrators. You cannot change its security settings to make it available to other users. Viewing Analytic Model Properties This section provides an overview of the Analytic Model Viewer and discusses how to : • View analytic models. • View and debug cube collection properties. • View and debug data cube properties. • View cell properties. • View dimension properties.
PAGE 255
Chapter 14 Viewing and Debugging Analytic Models Part Properties Dialog Part Browser Analytic Grid Analytic Model Viewer, Properties tab Part Browser Contains hierarchies that you use to view and debug the parts of the current analytic instance. This part browser is similar to the one in PeopleSoft Application Designer. For example, if you drill into a cube collection it expands to display the specific cube collections in the current analytic instance.
PAGE 256
Viewing and Debugging Analytic Models Chapter 14 Browser, a Part Property Dialog, and an analytic grid. In addition, it displays an audit grid that displays the audited data when you click the View button. Pages Used to View Analytic Model Properties Page Name Object Name Navigation Usage Analytic Model Viewer Properties Tab PTACEMDLVWR PeopleTools, Utilities, Administration, Analytic Model Viewer Enables you to view the properties and data of the selected part.
PAGE 257
Chapter 14 Viewing and Debugging Analytic Models Analytic Model Viewer, model properties Resolve circular formulas Indicates whether circular formulas will be resolved. See Chapter 10, “Creating Rules, Formulas, and User Functions,” Working with Circular Formulas and Circular Systems, page 134. Maximum iteration in value Indicates maximum iteration in value. Maximum change in value Indicates maximum change in value. Warning circular formulas Specifies whether the model contains circular formulas.
PAGE 258
Viewing and Debugging Analytic Models Chapter 14 Viewing Cube Collections and Cube Collection Properties Access the Analytic Model Viewer and select a cube collection whose properties you want to view. You see a Cube Collection Properties panel displaying the properties of that cube collection. The analytic grid underneath the Cube Collection Properties panel displays the cube collection data itself. You can drag and drop data cubes and dimensions within this grid.
PAGE 259
Chapter 14 Save Recalculate Viewing and Debugging Analytic Models Recalculates the results of your analytic instance, if you modified it. Debugging Cube Collections Access the Debugging tab of the Analytic Model Viewer. The part browser, filtering and sorting features, and main grid on the Debugging tab work just like those features on the Properties tab. However, this tab also enables you to audit the data.
PAGE 260
Viewing and Debugging Analytic Models Chapter 14 Filter/Sort Criteria window Members are only filtered when the filter condition is met. In addition, if an aggregate member is filtered, all its children are also filtered. Select Dimension Select the dimension upon which you want to base the filter or sort. Select Filter Select the filter. Apply Filter Apply the selected filter. Clear Filter Clear the selected filter. Sort Select whether to sort by key, by name, or by neither.
PAGE 261
Chapter 14 Viewing and Debugging Analytic Models Analytic Model Viewer, data cube Properties tab Format Type Displays the format type of the data cube—such as Number, Char, Date, and so on. These format types are attributes of the cube and are defined within PeopleSoft Application Designer. See Chapter 5, “Creating Data Cubes,” Understanding the Relationship Between Field Definition Attributes and Data Cube Formats, page 38. Virtual Cube Indicates whether the selected data cube is a virtual cube.
PAGE 262
Viewing and Debugging Analytic Models Chapter 14 See Chapter 14, “Viewing and Debugging Analytic Models,” Viewing Cell Properties, page 238. Save Recalculate Recalculates the results of your analytic instance if you modified it. Debugging Data Cubes Access the Analytic Model Viewer, select a data cube whose properties you want to view, and click the Debugging tab: You can also audit data cubes at design time. See Chapter 5, “Creating Data Cubes,” Auditing Data Cubes at Design Time, page 43.
PAGE 263
Chapter 14 Viewing and Debugging Analytic Models Cell Properties page Select Cube Choose which data cube in the selected cube collection you want to view. Note. The Select Cube option is enabled only if you activated the Cell Properties page while viewing a cube collection. Dimension Name Displays the names of dimensions attached to the selected cube. Member Enter the member in the selected dimension whose properties you want to view.
PAGE 264
Viewing and Debugging Analytic Models Chapter 14 Access the Analytic Model Viewer and select a dimension whose properties you want to view: Analytic Model Viewer, dimension properties When you select a dimension in the part browser, you see a Dimension Properties panel displaying the properties of that dimension: Total Member Name Displays a different value depending on a fairly complex set of factors. See Chapter 6, “Creating Dimensions,” Defining Dimension Properties, page 48.
PAGE 265
Chapter 14 Viewing and Debugging Analytic Models a tree-like structure. In this case, you open and close each branch and leaf, and see each member of this hierarchy. Tree Properties Click to display a secondary page that displays additional properties of the selected tree, including: • Tree name. • Node name. • Start level. • Discard level. • SetID. • Record name. • Effective date. Viewing User Function Properties This section discusses how to view user functions.
PAGE 266
Viewing and Debugging Analytic Models Chapter 14 See Chapter 4, “Creating Analytic Model Definitions,” Creating Organizers, page 29. Access the Analytic Model Viewer and select an organizer whose properties you want to view: Analytic Model Viewer, organizer properties Using Analytic Model Viewer Alongside PeopleSoft Application Designer It can be profitable to use the Analytic Model Viewer side by side with PeopleSoft Application Designer.
PAGE 267
Chapter 14 Viewing and Debugging Analytic Models 6. From within the Analytic Model Viewer, click the Reload Model button. Using the Application Log Fence In addition to the model viewer, you can also use the application log fence settings to cause error messages created on the analytic server to be written to the analytic server log file.
PAGE 269
CHAPTER 15 Capturing Analytic Instances This chapter provides an overview of the Analytic Instance Capture Utility and discusses how to: • Capture analytic instance data. • Import analytic instance data. Understanding the Analytic Instance Capture Utility When customers report a problem in an application that uses PeopleSoft Analytic Calculation Engine or PeopleSoft Optimization Framework, they often need to send the relevant data and metadata to PeopleSoft engineers who can then review the problem.
PAGE 270
Capturing Analytic Instances Chapter 15 By default, the Analytic Instance Capture Utility exports the analytic instance to a directory that it creates entitled \appserv\\LOGS\. For example, if you export an analytic instance named ACEINST, the resulting export directory is named something like \appserv\\LOGS\ACEINST_20041113_015912. A valid export directory will by default include the following files.
PAGE 271
Chapter 15 Capturing Analytic Instances Export Instance page To export an analytic instance: 1. Load the analytic instance. See Enterprise PeopleTools 8.49 PeopleBook: System and Server Administration, “Managing Analytic Servers,” Loading and Unloading Analytic Instances. 2. Access the Export Instance page. 3. Optionally, select whether to filter the loaded analytic instances, either by analytic type or by model name. 4. Click Search to display the designated loaded analytic instances. 5.
PAGE 272
Capturing Analytic Instances Chapter 15 7. Retrieve the exportResults.txt file for specific details on how to export this analytic instance. 8. In PeopleSoft Application Designer, create a project that has the same name as the export directory that was created during the export procedure. Remember, the export process creates a directory whose name consists of the analytic instance name followed by the date and timestamp.
PAGE 273
Chapter 15 Capturing Analytic Instances 5. Access the Create Analytic Instance page and create an analytic instance named —for example, ACEINST. See Enterprise PeopleTools 8.49 PeopleBook: System and Server Administration, “Managing Analytic Servers,” Creating, Deleting, and Copying Analytic Instances. 6. Access the Analytic Instance Load/Unload page and perform these steps: See Enterprise PeopleTools 8.
PAGE 275
PART 6 Converting Analytic Models Chapter 16 Converting BAM 8.
PAGE 276
PAGE 277
CHAPTER 16 Converting BAM 8.8 Models to Analytic Models This chapter provides overviews of the conversion process and part conversion details and discusses how to: • Export BAM 8.8 models. • Run the PTAEACECONV Application Engine Program. • Examine the PTAEACECONV log file. Understanding the Conversion Process PeopleSoft Business Analysis Modeler (BAM) is a standalone application that enables developers to create multidimensional models for the purposes of reporting and analysis.
PAGE 278
Converting BAM 8.8 Models to Analytic Models Chapter 16 See Chapter 16, “Converting BAM 8.8 Models to Analytic Models,” Exporting BAM 8.8 Models, page 262. 3. Run the PTAEACECONV Application Engine program. See Chapter 16, “Converting BAM 8.8 Models to Analytic Models,” Running the PTAEACECONV Application Engine Program, page 262. 4. Examine the Application Engine log file. See Chapter 16, “Converting BAM 8.8 Models to Analytic Models,” Examining the PTAEACECONV Log File, page 264. 5.
PAGE 279
Chapter 16 Converting BAM 8.8 Models to Analytic Models See Chapter 16, “Converting BAM 8.8 Models to Analytic Models,” Understanding Part Conversion Details, page 255. • Timelines. • Roles. • Chart views. BAM 8.8 Circular Formula Options That Can Be Converted These BAM 8.8 circular formula options can be converted into analytic models: • Resolve circular formulas through iteration. • Maximum number of iterations. • Maximum change in values.
PAGE 280
Converting BAM 8.8 Models to Analytic Models Chapter 16 BAM 8.8 Part Name Attribute Attribute Change Upon Conversion Non alphanumeric characters Non alphanumeric characters are removed from the part name. For example: Cost$ of delivery converts to COST_OF_DELIVERY. Part names with more than 27 characters Characters exceeding the 27 character limit are truncated.
PAGE 281
Chapter 16 Converting BAM 8.8 Models to Analytic Models CASE(&RevenueMethod = "Data Entry": Do_Something; &RevenueMethod = "Repeat Value": Do_Something_ELSE; ) 3. Replace all references to original dimension names with converted dimension names. 4. Replace all references to original data cube names with converted data cube names. Note.
PAGE 282
Converting BAM 8.8 Models to Analytic Models Chapter 16 • These data cube formats: - General. The General format is converted to the Text format. - Currency. The Currency format is converted to the Number format. - Option List. The Option List format is converted to the Text format. - Percent. The Percent format is converted to the Number format. Note. Digit and Decimal properties are not converted. - Yes/No. The Yes/No format is converted to Text format. • Code in data cube rules.
PAGE 283
Chapter 16 Converting BAM 8.8 Models to Analytic Models - Center. - Right. • Formatting function names. Dimensions PTAEACECONV converts all dimensions and most dimension attributes. The conversion does not change the notes for dimensions. These dimension attributes are converted but are changed during the conversion process: • Dimension names: - Original dimension names are converted into new dimension names using the part names conversion method.
PAGE 284
Converting BAM 8.8 Models to Analytic Models Chapter 16 • Formatting Function. Expression Modifiers PTAEACECONV converts all expression modifiers. Converted expression modifiers exist as user functions in the analytic model. PTAEACECONV does not affect rules that are defined for expression modifiers. These expression modifier attributes are converted but are changed during the conversion process.
PAGE 285
Chapter 16 Converting BAM 8.8 Models to Analytic Models • All table view-related properties, including: - Coordinates. - Positions of dimensions in table views. - Table header cells. - Sections of table views. • Table data. Import Maps PTAEACECONV converts all import maps into cube collections. Note that BAM 8.8 import maps lack important information needed to complete cube collections, including: • Mapping between cube collections to main and aggregate records.
PAGE 286
Converting BAM 8.8 Models to Analytic Models Chapter 16 PTAEACECONV converts organizer names but changes them in the following manner: • Original organizer names are converted into new organizer names using the part names conversion method. See “Part Names Conversion Method” • Original organizer names are also converted into new organizer descriptions PTAEACECONV does not convert organizer references to these parts: • Expression modifiers. • Prefix modifiers. • Styles. • Chart views. Exporting BAM 8.
PAGE 287
Chapter 16 Converting BAM 8.8 Models to Analytic Models See Also Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Application Engine, “Managing Application Engine Programs,” Using the Command Line to Invoke Application Engine Programs Enterprise PeopleTools 8.
PAGE 288
Converting BAM 8.8 Models to Analytic Models Chapter 16 Examining the PTAEACECONV Log File Use the log file to determine whether the BAM model successfully converted to an analytic model, or whether there are conversion errors that you must resolve. If the BAM model converted successfully to an analytic model, the message Application Engine program PTAEACECONV ended normally appears at the bottom of the PTAEACECONV log file.
PAGE 289
Glossary of PeopleSoft Enterprise Terms absence entitlement This element defines rules for granting paid time off for valid absences, such as sick time, vacation, and maternity leave. An absence entitlement element defines the entitlement amount, frequency, and entitlement period. absence take This element defines the conditions that must be met before a payee is entitled to take paid time off.
PAGE 290
Glossary Administration, PeopleSoft Enterprise Stock Administration, and the COBRA Administration feature of the Base Benefits business process. action template In PeopleSoft Enterprise Receivables, outlines a set of escalating actions that the system or user performs based on the period of time that a customer or item has been in an action plan for a specific condition.
PAGE 291
Glossary for satisfying a requirement but that are rejected. It also contains information on courses captured by global limits. An analysis database is used in PeopleSoft Enterprise Academic Advisement. Application Messaging PeopleSoft Application Messaging enables applications within the PeopleSoft Enterprise product family to communicate synchronously or asynchronously with other PeopleSoft Enterprise and third-party applications.
PAGE 292
Glossary budget check In commitment control, the processing of source transactions against control budget ledgers, to see if they pass, fail, or pass with a warning. budget control In commitment control, budget control ensures that commitments and expenditures don’t exceed budgets. It enables you to track transactions against corresponding budgets and terminate a document’s cycle if the defined budget conditions are not met.
PAGE 293
Glossary catalog partner In PeopleSoft Enterprise Catalog Management, shares responsibility with the enterprise catalog manager for maintaining catalog content. categorization Associates partner offerings with catalog offerings and groups them into enterprise catalog categories. category In PeopleSoft Enterprise Campus Solutions, a broad grouping to which specific comments or communications (contexts) are assigned.
PAGE 294
Glossary collection To make a set of documents available for searching in Verity, you must first create at least one collection. A collection is set of directories and files that allow search application users to use the Verity search engine to quickly find and display source documents that match search criteria. A collection is a set of statistics and pointers to the source documents, stored in a proprietary format on a file server.
PAGE 295
Glossary 3C access groups so that you can assign data-entry or view-only privileges across functions. In PeopleSoft Enterprise Incentive Management, a mechanism that is used to determine the scope of a processing run. PeopleSoft Enterprise Incentive Management uses three types of context: plan, period, and run-level. control table Stores information that controls the processing of an application.
PAGE 296
Glossary delivery method In PeopleSoft Enterprise Learning Management, identifies the primary type of delivery method in which a particular learning activity is offered. Also provides default values for the learning activity, such as cost and language. This is primarily used to help learners search the catalog for the type of delivery from which they learn best. Because PeopleSoft Enterprise Learning Management is a blended learning system, it does not enforce the delivery method.
PAGE 297
Glossary elimination set In PeopleSoft Enterprise General Ledger, a related group of intercompany accounts that is processed during consolidations. entry event In PeopleSoft Enterprise General Ledger, Receivables, Payables, Purchasing, and Billing, a business process that generates multiple debits and credits resulting from single transactions to produce standard, supplemental accounting entries.
PAGE 298
Glossary fund In PeopleSoft Enterprise Promotions Management, a budget that can be used to fund promotional activity. There are four funding methods: top down, fixed accrual, rolling accrual, and zero-based accrual. gap In PeopleSoft Enterprise Campus Solutions, an artificial figure that sets aside an amount of unmet financial aid need that is not funded with Title IV funds.
PAGE 299
Glossary incentive rule In PeopleSoft Enterprise Sales Incentive Management, the commands that act on transactions and turn them into compensation. A rule is one part in the process of turning a transaction into compensation. incur In PeopleSoft Enterprise Promotions Management, to become liable for a promotional payment. In other words, you owe that amount to a customer for promotional activities.
PAGE 300
Glossary 276 keyword In PeopleSoft Enterprise Campus Solutions, a term that you link to particular elements within PeopleSoft Enterprise Student Financials, Financial Aid, and Contributor Relations. You can use keywords as search criteria that enable you to locate specific records in a search dialog box. KPI An abbreviation for key performance indicator. A high-level measurement of how well an organization is doing in achieving critical success factors.
PAGE 301
Glossary linked section In PeopleSoft Enterprise Incentive Management, a section that is defined in a plan template but appears in a plan. Changes to linked sections propagate to plans using that section. linked variable In PeopleSoft Enterprise Incentive Management, a variable that is defined and maintained in a plan template and that also appears in a plan. Changes to linked variables propagate to plans using that variable. LMS Abbreviation for learning management system.
PAGE 302
Glossary meta-SQL Meta-SQL constructs expand into platform-specific SQL substrings. They are used in functions that pass SQL strings, such as in SQL objects, the SQLExec function, and PeopleSoft Application Engine programs. metastring Metastrings are special expressions included in SQL string literals. The metastrings, prefixed with a percent (%) symbol, are included directly in the string literals. They expand at run time into an appropriate substring for the current database platform.
PAGE 303
Glossary PeopleCode PeopleCode is a proprietary language, executed by the PeopleSoft Enterprise component processor. PeopleCode generates results based on existing data or user actions. By using various tools provided with PeopleTools, external services are available to all PeopleSoft Enterprise applications wherever PeopleCode can be executed. PeopleCode event See event.
PAGE 304
Glossary Admissions for enrollment management. You can define a population level, link it to other levels, and set enrollment target numbers for it. See also division and cohort. 280 portal registry In PeopleSoft Enterprise applications, the portal registry is a tree-like structure in which content references are organized, classified, and registered.
PAGE 305
Glossary process request A single run request, such as a Structured Query Report (SQR), a COBOL or Application Engine program, or a Crystal report that you run through PeopleSoft Process Scheduler. process run control A PeopleTools variable used to retain PeopleSoft Process Scheduler values needed at runtime for all requests that reference a run control ID.
PAGE 306
Glossary publishing In PeopleSoft Enterprise Incentive Management, a stage in processing that makes incentive-related results available to participants. rating components In PeopleSoft Enterprise Campus Solutions, variables used with the Equation Editor to retrieve specified populations. record group A set of logically and functionally related control tables and views. Record groups help enable TableSet sharing, which eliminates redundant data entry.
PAGE 307
Glossary REN server Abbreviation for real-time event notification server in PeopleSoft MultiChannel Framework. requester In PeopleSoft Enterprise eSettlements, an individual who requests goods or services and whose ID appears on the various procurement pages that reference purchase orders. reservations In PeopleSoft Expenses, travel reservations that have been placed with the travel vendor.
PAGE 308
Glossary section In PeopleSoft Enterprise Incentive Management, a collection of incentive rules that operate on transactions of a specific type. Sections enable plans to be segmented to process logical events in different sections. security event In commitment control, security events trigger security authorization checking, such as budget entries, transfers, and adjustments; exception overrides and notifications; and inquiries.
PAGE 309
Glossary single signon With single signon, users can, after being authenticated by a PeopleSoft Enterprise application server, access a second PeopleSoft Enterprise application server without entering a user ID or password. source key process In PeopleSoft Enterprise Campus Solutions, a process that relates a particular transaction to the source of the charge or financial aid. On selected pages, you can drill down into particular charges.
PAGE 310
Glossary summary tree A tree used to roll up accounts for each type of report in summary ledgers. Summary trees enable you to define trees on trees. In a summary tree, the detail values are really nodes on a detail tree or another summary tree (known as the basis tree). A summary tree structure specifies the details on which the summary trees are to be built. syndicate To distribute a production version of the enterprise catalog to partners.
PAGE 311
Glossary travel group In PeopleSoft Expenses, the organization’s travel rules and polices that are associated with specific business units, departments, or employees. You must define at least one travel group when setting up the PeopleSoft Expenses travel feature. You must define and associate at least one travel group with a travel vendor. travel partner In PeopleSoft Expenses, the travel vendor with which the organization has a contractual relationship.
PAGE 312
Glossary See also inquiry access. 288 user interaction object In PeopleSoft Enterprise Sales Incentive Management, used to define the reporting components and reports that a participant can access in his or her context. All PeopleSoft Enterprise Sales Incentive Management user interface objects and reports are registered as user interaction objects. User interaction objects can be linked to a compensation structure node through a compensation relationship object (individually or as groups).
PAGE 313
Glossary yield by operation In PeopleSoft Enterprise Manufacturing, the ability to plan the loss of a manufactured item on an operation-by-operation basis. zero-rated VAT Abbreviation for zero-rated value-added tax. A VAT transaction with a VAT code that has a tax percent of zero. Used to track taxable VAT activity where no actual VAT amount is charged. Organizations that supply zero-rated goods and services can still recover the related input VAT. This is also referred to as exempt with recovery.
PAGE 315
Index A A dimension argument cannot be used here (error message) 115 ABS function 139 ACE, See PeopleSoft Analytic Calculation Engine ACOS function 139 addition (operator) 125 additional documentation xvi aggregate data pushing down 80 understanding the calculation of 78 understanding the persistence of 79 aggregate functions selecting for dimensions on data cubes 42 understanding 48 aggregate members creating 76 data types of 81 dimension order impact on 78 understanding 74 aggregate records mapping to cub
PAGE 316
Index part properties dialog box 231 security 230 understanding 229 using alongside PeopleSoft Application Designer 242 viewing tree properties with 72 Analytic model with name %1 not found (error message) 115 analytic models 15 See Also analytic model definitions converting BAM models to 253 editing data in Analytic Model Viewer 229 entering descriptions of 137 naming conventions for 28 understanding relationship to analytic types 201 validating 30 viewing properties with Analytic Model Viewer 230 viewing
PAGE 317
Index CHANGE 144 CHILDCOUNT 145 CHR 145 CONSOL 146 COS 146 CUBEID 147 CUMAVG 147 CUMSUM 148 DAVG 148 DAY 149 DCOUNT 150 DDB 150 DEC 151 DLOOKUP 152 DMAX 152 DMIN 153 DSUM 154 E 154 FIND 155 FIRST 155 FOR 156 FORCHILDREN 156 FORMEMBERS 157 FV 158 GROUPAVG 159 GROUPBY 160 GROUPMAX 160 GROUPMIN 161 GROUPSUM 162 GROW 163 IF 164 INC 164 INCDATE 165 INPUT 165 inserting into rules 128 INSUBTREE 166 INTERCEPT 167 IRR 167 ISINPUT 166 LEFT 168 LEN 169 LN 168 LOWER 169 MATCH 169 MAX 170 MBR2TEXT 171 MEDIAN 171 MEMBER
PAGE 318
Index creating for groups of members 133 creating for members 131 CASE function 144 causes 44 See Also Causes and Effects Tool displaying 45 viewing with Analytic Model Viewer 229 Causes and Effects tool 46 See Also causes; effects cells, viewing properties of 238 centered moving averages, creating 175 CHANGE function 144 CHILDCOUNT function 145 CHR function 145 circular formulas changing options for handling 136 convertible options 255 nonconvertible options 255 understanding 134 Circular reference (error
PAGE 319
Index example of working with dimensions 32 finding 30 formats and field definition attributes 38 formatting 41 mapping to fields 59 Member format 41 naming conventions for 28 Number format 41 populating with data 31 referring to in rules 129 referring to one slice of 130 relationship to cube collections 53 Text format 42 understanding references of 126 using in cube collections 31 viewing in part browser 25 viewing with Analytic Model Viewer 236 data types of aggregate members 81 database, installing duri
PAGE 320
Index displaying 45 understanding 44 Direct Effects command 25 #DIRECT predefined constant 124 division (operator) 125 DLOOKUP function 152 DMAX function 152 DMIN function 153 documentation printed xvi related xvi updates xvi drag and drop in analytic grids 9 of bars 22 of parts 25 DSUM function 154 Duplicate argument name %1 (error message) 115 Duplicate dimensions in member references (error message) 116 F #FALSE predefined constant 124 field definition attributes, relationship to data cube formats 38 f
PAGE 321
Index Invalid constant (error message) 116 Invalid dimension %1 (error message) 116 Invalid function %1 (error message) 116 Invalid member reference (error message) 117 Invalid member reference syntax. Valid syntax is [DIMENSION:Member].
PAGE 322
Index understanding 171 MEDIAN function 171 Member format 41 MEMBER function 172 members, See dimension members menu bar 21, 22 metadata changing using PeopleCode 9 viewing with Analytic Model Viewer 229 Metadata classes 9 MID function 173 MIN function 173 MOD function 174 Model Viewer, See Analytic Model Viewer MONTH function 174 Move, Down command 23 Move, Up command 23 multiplication (operator) 125 NUMMEMBERS function O operators comparison 122 execution order of 126 logical 123 mathematical 125 OPRID
PAGE 323
Index Paste Dimension command 22 Paste Function command 22 Paste Member Ref command 22 Paste User Function command 22 PCT function 180 PeopleBooks ordering xvi PeopleCode, typographical conventions xviii PeopleSoft Analytic Calculation Engine 3 See Also Analytic Calculation Engine classes; Analytic Calculation Engine Metadata classes; analytic calculation engines components of 7 development process using existing record structures 12 development process without existing record structures 11 implementing 3
PAGE 324
Index inserting for dimension members 129 inserting for dimensions 129 of blank members 127 of data cubes 126 of dimension members 126 related documentation xvi REPLACE function 186 reporting, using cube collections for 56 RETURN function 186 #REVERSE predefined constant 125 RIGHT function 186 right-click menus 23 ROOT, selecting 80 ROUND function 187 rule bar display behavior of 114 understanding 21 Rule Bar command 23 rules 112 See Also formulas; user functions converting code in 256 entering member refe
PAGE 325
Index U Unbalanced parentheses (error message) 117 Undefined data cube %1 (error message) 117 underscores in names 28 UPPER function 196 uppercase letters 28 use properties 218 user functions 112 See Also aggregate functions; filter user functions; formulas; rules converting 259 converting code in 256 defining 119 editing 119 entering notes for 30 finding 30 inserting into rules 128 naming conventions for 28 viewing in part browser 25 viewing properties with Analytic Model Viewer 241 user profiles, establi