with ibm corp....using etls with pentaho spoon .... . 15 using etls with ibm t ivoli dir ectory...

IBM TRIRIGA Application PlatformVersion 3 Release 5

Application Building for theIBM TRIRIGA Application Platform:Performance Framework

IBM

NoteBefore using this information and the product it supports, read the information in “Notices” on page 73.

This edition applies to version 3, release 5, medication 0 of IBM TRIRIGA Application Platform and to allsubsequent releases and modifications until otherwise indicated in new editions.

© Copyright IBM Corporation 2011, 2015.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.

Contents

Chapter 1. Performance framework . .. 1

Chapter 2. Data structures . . . . . .. 3Architecture overview . . . . . . . . . .. 3Fact tables . . . . . . . . . . . . . .. 4

Example fact table and associated dimensions .. 6Metrics structure . . . . . . . . . . . .. 8ETL integration . . . . . . . . . . . .. 10

ETL integration architecture . . . . . . .. 10ETL integration process . . . . . . . .. 11Prerequisite setup for ETL integration . . .. 14Defining and maintaining ETL transforms . .. 15

Using ETLs with Pentaho Spoon . . . .. 15Using ETLs with IBM Tivoli DirectoryIntegrator Configuration Editor . . . . .. 31

Running ETL transforms . . . . . . . .. 53ETL job items, job groups, and job schedulers 53Creating or modifying ETL job items . . .. 54Adding or modifying job groups . . . .. 56Creating or modifying job schedulers . . .. 56

Customizing transform objects . . . . . .. 58Defining transform business objects, forms,and workflows . . . . . . . . . .. 58Saving transform XML into the ContentManager . . . . . . . . . . . .. 58Configuring workflow run time . . . .. 58Running an ETL custom workflow taskspecification . . . . . . . . . . .. 59

Chapter 3. Metrics . . . . . . . . .. 61Metrics reports . . . . . . . . . . . .. 61Key metrics . . . . . . . . . . . . .. 62Form metrics . . . . . . . . . . . . .. 62

Data filtering . . . . . . . . . . . .. 62Sub reports . . . . . . . . . . . .. 63

Chapter 4. Hierarchy flattener . . . .. 65Flat hierarchies . . . . . . . . . . . .. 65

Examples of flat hierarchies . . . . . . .. 66Hierarchy structure manager . . . . . . .. 67

Accessing hierarchy structures . . . . . .. 67Creating a data hierarchy . . . . . . . .. 67Creating a form hierarchy . . . . . . .. 68

Chapter 5. Fact tables . . . . . . .. 69List of fact tables and metrics supported. . . .. 69Facts that require special staging tables and ETLs . 69Dependent ETLs. . . . . . . . . . . .. 70

Notices . . . . . . . . . . . . .. 73Trademarks . . . . . . . . . . . . .. 75Terms and conditions for product documentation.. 75IBM Online Privacy Statement . . . . . . .. 76

© Copyright IBM Corp. 2011, 2015 iii

iv © Copyright IBM Corp. 2011, 2015

Chapter 1. Performance framework

IBM® TRIRIGA® Workplace Performance Management and IBM TRIRIGA RealEstate Environmental Sustainability provide viable solutions to help corporationsstrategically plan, manage, evaluate, and improve processes that are related tofacilities and real estate.

IBM TRIRIGA performance framework is managed within TRIRIGA WorkplacePerformance Management and TRIRIGA Real Estate Environmental Sustainability,which include the following components:v Data transform and fact table load servicesv A metric builder that uses the Data Modelerv A metric query enginev Enhanced Report Manager for building metric reportsv Advanced portal features to render metric scorecardsv A series of prebuilt metrics, reports, and alerts that significantly improve the

productivity of the many roles that are supported within TRIRIGA

© Copyright IBM Corp. 2011, 2015 1

2 © Copyright IBM Corp. 2011, 2015

Chapter 2. Data structures

TRIRIGA uses an extract, transform, and load (ETL) development environment asthe mechanism for moving the data from business object tables to fact tables. Inorder to present the metrics, reports, scorecards, and other performance measures,the data must be in a form of fact tables and flat hierarchy tables that the reportingtools can process.

Architecture overviewThe source data for TRIRIGA Workplace Performance Management comes from theTRIRIGA application database, financial summary data that is imported from anexternal financial system, and building meter data that is imported from externalbuilding management systems.

Using ETL technology, the source data is loaded into fact tables. The fact tables anddimension tables are in the same database repository as the TRIRIGA applications.The fact tables store the numerical data, referred to as facts, that is used tocalculate the TRIRIGA Workplace Performance Management metric values. Eachrow in a fact table references one or more related business objects, classifications,or lists that group and filter the facts. These rows are called dimensions.

The metric query engine runs queries on the fact and dimension tables. Metricqueries quickly recalculate metric values as the user moves up and down ahierarchical dimension.

The following diagram shows the distinct layers that make up this architecture andthe flow of data between these layers:


WPM reports

Fact tables Dimension tables

WPM Performance Manager

Metric query engine

ETL scripts

WPMroles-based

portals

TRIRIGA IWMSFinancial

summary dataStaging tables

ETL scripts

Building meterdata

Fact tablesFact tables store the data that is used to calculate the metrics in metric reports. Facttables are populated only through ETL transforms. To identify a business object asa fact table, from the Data Modeler set the Externally Managed flag in the facttable business object definition.

Each fact table is implemented in the IBM TRIRIGA Application Platform as aspecial business object that has some or all of the following elements:


Table 1. Fact tables

Fact table element Description

Hierarchical dimensions Each hierarchical dimension is a locator fieldto a business object that belongs to ahierarchical module (for example, aBuilding, Service Cost Code, or City). Foreach hierarchical dimension, a correspondinghierarchy structure supports metric reports.A hierarchical dimension can reference anyor all business objects within a module. Beas specific as possible. Targeting a specificbusiness object improves the granularity ofyour reporting.Each hierarchical dimension must have acorresponding hierarchy structure defined.Hierarchy structures are used for drill pathsin metric reports.

Non-hierarchical dimensions Each non-hierarchical dimension is either alist field or a locator field to a businessobject that belongs to a non-hierarchicalmodule (for example, a Task or Person).

Numeric fact fields Numeric fact fields are standard numericfields, including or excluding Unit ofMeasure (UOM) properties. Numeric factfields can be characterized as one of thefollowing types:

v Additive – Can be summed across alldimensions.

v Semi-additive – Can be summed onlyacross some dimensions. For example, thetotal number of people for a buildingcaptured monthly cannot be summedquarterly, since doing so would not yielda total for the quarter, whereas it can besummed by geography. Therefore, this factis non-additive over time.

v Non-additive – Cannot be summed acrossany dimension. For example, a ratio is anon-additive fact, since you cannot sum aratio. Also, fields that contain values fromdifferent grains are non-additive.

UOM fields Unit of measure (UOM) fields (except forArea fields) are captured in their local,entered, UOM.

Area fields Area fields are captured in both Imperial(for example, square feet) and metric (forexample, square meters) values.

Currency fields Currency fields are captured by using thebase currency. No currency conversionoccurs.

Chapter 2. Data structures 5

Table 1. Fact tables (continued)

Fact table element Description

Current time period The time period dimension is a specialdimension that is used to identify thedate/time period for which a single factrecord is applicable. This is most likely thetime period when the data was captured.For cases when the time period dimension isnot used as a drill path or filter, thetriCapturePeriodTX field must be populatedto indicate the dimension that is used toindicate the capture period. If this fieldexists, the corresponding business object forthat dimension should contain a field that isnamed triCurrentBL, which is used to flagthose dimension records that reflect thecurrent period. These records are then usedto filter the result set for the metric report.

Fiscal period The fiscal period classification is used by theETL process to define the capture period forfact records. This is the primary time perioddimension in metric reports.Because it is possible to have different facttables that contain data that is based ondifferent capture frequencies with a singlerecord for each level within the hierarchy,each level can be flagged as the currentperiod. For example, if ayear/quarter/month hierarchy is created inthe fiscal period classification, it is possibleto identify the current year, current quarter,and current month. A special ETL job typeprovides workflow to keep this datasynchronized.As a general rule, all data in a single facttable should be captured at the same timeperiod grain/level (year, quarter, month). Ifthe time period grain/level is changed afterdata has been captured for a particular facttable, all data in that fact table must eitherbe revised to the correct grain/level ortruncated/removed.

Fact table business object To identify a business object as one that willhave fact tables supporting it, select theExternally Managed radio button in thebusiness object properties when creating thebusiness object.

Tip: Do not delete or change any of the fact business objects, fact tables, or ETLscripts that are delivered with the standard TRIRIGA software. Instead, to changean existing one, copy it, rename the copy, and tailor the copy to your needs.

Example fact table and associated dimensionsThe fact and dimension tables are built by using the star schema method of datawarehouse design. They are stored in the same database repository as theTRIRIGA applications.


The following diagram shows an example of one of the preconfigured fact tables inTRIRIGA Workplace Performance Management:

Space business object

Fact Table Fields

Space capacity

Total headcount

Remaining capacity

Space area

Allocated area

Space ID

Building ID

Location ID

Geography ID

Space class ID

Building class ID

Building tenure ID

Dimension Tables

Building business object

Location hierarchy

Geography hierarchy

Space class hierarchy

Building class hierarchy

Building tenure hierarchy

The diagram shows the space fact with five facts, including space capacity, totalheadcount, remaining capacity, space area, and allocated area. The space fact alsoreferences seven dimensions, including space, building, location, geography, spaceclass, building class, and building tenure. The dimensions in the fact table link thefacts to the corresponding dimension tables. Some dimensions are hierarchical,such as location and geography and others are not, such as space and building.

Flat hierarchy tables are used to identify the children of a selected business object.Flat hierarchy tables enable the metric query engine to browse hierarchicalmodules, business objects, and classifications.

The following table shows an example of a flat hierarchy that is based ongeography:

Table 2. Geography Flat Hierarchy Example

SPEC_IDLevelNumber

Level 1SPEC_ID

Level 2SPEC_ID

Level 3SPEC_ID

Level 4SPEC_ID

World 1 World N/A N/A N/A

NorthAmerica

2 World NorthAmerica

N/A N/A

EMEA 2 World EMEA N/A N/A

APAC 2 World APAC N/A N/A


Table 2. Geography Flat Hierarchy Example (continued)

SPEC_IDLevelNumber

Level 1SPEC_ID

Level 2SPEC_ID

Level 3SPEC_ID

Level 4SPEC_ID

United States 3 World NorthAmerica

United States N/A

Canada 3 World NorthAmerica

Canada N/A

Nevada 4 World NorthAmerica

United States Nevada

Texas 4 World NorthAmerica

United States Texas

Using the example, if you wanted to identify all of the geographies that arechildren of North America, you first look up the North America SPEC_ID in thefirst column of the geography flat example table. Then, you might use the levelnumber for North America, which is 2, to determine the filter column. By using theSPEC_ID and level number, you can identify all the geographies that are children,grandchildren, or any level below North America.

Metrics structureThe TRIRIGA Workplace Performance Management functionality focuses oncapturing metric facts and enabling metric reporting.

Most TRIRIGA metrics are multidimensional, where the same metric provides ahigh-level summary view (for example, the Total Operating Cost/Area for theentire organization and portfolio) and, by drill down through various dimensionsor filters, a role-specific view (for example, Total Operating Cost/Area for NorthAmerican Operations for the facilities that are managed by North Americanoperations).

Metrics measure process performance that is capable of identifying actionableresults. Typically, the measures are ratios, percentages, or scores. Metrics havetargets, thresholds, action conditions, accountability, and action task functionality.

TRIRIGA Workplace Performance Management includes the following types ofmetrics, which are the Scorecard categories in the Key Metrics portal section:

Customer MetricsMeasure customer satisfaction

Financial MetricsMeasure financial performance

Portfolio MetricsMeasure operational utilization and asset life cycle health

Process MetricsMeasure process efficiency and effectiveness

Reporting and Analysis MetricsAnalyze a specific performance metric

Additionally, TRIRIGA Real Estate Environmental Sustainability includes thefollowing types of metrics:


EnvironmentalMeasure performance of environmental initiatives

Building metersMeasure characteristics of a building as reported by meters and sensors

The following high-level process diagram depicts how metrics are defined,captured, and presented to the user:

Metric processing engine(analytic/reporting tool)

Externaldata TRIRIGA

Fact Dimension

TRIRIGA application portal

Staging

The TRIRIGA database is the primary source of data for gathering operational datato be loaded into fact tables. Optionally, you can extract data from other sources tobe loaded into fact tables.

Each fact table contains the lowest level of aggregated data (such as Building level)for each metric category. For efficiency reasons, a fact table is a de-normalized(flattened) table containing data elements from multiple TRIRIGA tables.

The dimension table contains dimensions for each metric. Dimensions are stored ina separate table for efficiency. The fact table contains a key (Spec ID) for eachdimension. The dimension table can be either a flat hierarchy table or an TRIRIGAbusiness object table.

The Metric Processing Engine (Analytic/Reporting Tool) generates metrics usingdata stored in fact tables along with metric setup data and dimension data.

Metric data, along with notifications, actions, and alerts, are presented to users in arole-based portal in various forms (including reports, queries, and graphs) asdefined in the metric setup table. A user can drill down into a specific object ordrill path to further analyze the metric data presented to them.

Metric reporting is dependent on metric fact tables. These fact tables areimplemented using the Data Modeler but are identified with a unique object typethat signifies that it is a metric object. Metric objects are populated using an ETLdevelopment environment, which is different from all other object types that areupdated through the metadata layer. The scheduling of the ETL process iscontrolled from within the TRIRIGA system using the Job Scheduler.


ETL integrationTRIRIGA uses either the Tivoli® Directory Integrator ETL developmentenvironment Configuration Editor or the Pentaho ETL development environmentSpoon to generate transform XML files. These transforms, when run through theAPI, move data from source to destination tables.

ETL integration architectureTRIRIGA uses two ETL environments to create the ETL scripts that populate thefact tables. The two ETL development environments are the Tivoli DirectoryIntegrator Configuration Editor and the Pentaho Data Integration tool Spoon. TheETL development environments enable the creation of SQL queries that read datafrom the TRIRIGA business object tables and map and transform the results to thefact table fact and dimension columns.

The following diagram shows the flow of data between the source data, ETLdevelopment environment, and the TRIRIGA Workplace Performance Managementdata model layers:

Facilities

TRIRIGA IWMS

Real estate

Operations

Projects

Financialsummary data

Dimension tables

Fact tables

Configuration Editoror Spoon

Source Data ETL DevelopmentEnvironment

WPM Data Model

Building meterdata

Staging tables

ETL job items are the business objects that reference the ETL scripts that are usedto populate the fact tables.

TRIRIGA Workplace Performance Management uses standard TRIRIGAApplication Platform tools.

The following diagram shows the application platform tools:


- Manager builder- Portal section manager- Portal manager

- Report manager

- Data modeler

- ETL development environment- ETL job item- ETL job scheduler

- TRIRIGA offline

ETL scripts

WPMroles-based

portals

Financialsummary data

TRIRIGAIWMS

Stagingtables

ETL scripts

Buildingmeter data

DimensiontablesFact tables

Metric query engine

WPM reports

WPM Performance Manager

ETL integration processTo move data from the source to destination tables, you run the ETL transformfiles that you developed in either Tivoli Directory Integrator Configuration Editoror Pentaho Spoon through the API.

There must be a transform for each fact table. Fact tables are populated onlythrough ETL transforms and not through the TRIRIGA application.

The TRIRIGA Application Platform includes a workflow that runs on a schedule toload the fact tables. The workflow calls a platform Custom workflow task, whichretrieves the latest transform XML from the Content Manager and uses either theTivoli Directory Integrator or the Kettle API to run the transform.


DatabaseFile system

XML file

ETL development environmentgenerates transform XML

Save XMLOpen XML

TRIRIGA Application PlatformBO/form defined to manage

the transforms

UploadDownload

Contentmanager

TRIRIGA Application Platformworkflow calls custom task on aschedule to run the transforms

Contentmanager

There are three main processes:v Setup, which involves creating the Transform business object/form/navigation

item and the workflow itself.v Creating/Maintaining the Transform XML using an ETL development

environment.v Runtime, which is a scheduled workflow that executes a Custom workflow task

to periodically run through the transforms to update the fact tables.

The following diagram summarizes these processes for Pentaho Spoon ETLtransforms:


Actor 2Application designer

In TRIRIGA Application Platform- Create fact BO- Determine mapping between factand meta data

Create

In Spoon- Create transform XML- Save to file in the file system

In TRIRIGA Application Platform- Upload XML file with transform form

Actor 3Professional services

In TRIRIGA Application Platform- Download XML file with transformform

Edit

In Spoon- Edit transform XML- Save to file in the file system


Maintaining Transforms - For Each Fact


In TRIRIGA Application Platform- Create transform BO/form- Create workflow to call custom task

Setup - Done Once

System

In TRIRIGA Application Platform- Workflow will trigger custom task for each transform- Gather variables- Download XML to temporary file- Set JNDI to application server- Call Kettle API to run the transform

Runtime - On a Schedule

The following diagram summarizes these processes for Tivoli Directory IntegratorConfiguration Editor ETL transforms:



In TRIRIGA Application Platform- Create fact BO- Determine mappings between factand meta data

Create

In Configuration Editor- Create transform XML- Save to file in the file system


Actor 3Professional services

In TRIRIGA Application Platform- Download XML file with transformform

Edit

In Configuration Editor- Edit transform XML- Save to file in the file system


Maintaining Transforms - For Each Fact


In TRIRIGA Application Platform- Create transform BO/form- Create workflow to call custom task- Define JDBC connection settings

Setup - Done Once

System

In TRIRIGA Application Platform- Workflow will trigger custom task for each transform- Gather variables- Get the JDBC connection settings- Send XML to TDI server to run transform

Runtime - On a Schedule

Data is pulled by ETL scripts from business objects, including the FinancialSummary business object, into which financial summary records are imported fromspreadsheets or by customer-designed interfaces with a financial system.

Prerequisite setup for ETL integrationWithin TRIRIGA Application Platform, a business object and a form manage thetransforms. The source tables and destination fact table must be defined and themappings understood to create the transformation.

There is a record for each fact table loaded through a transform. A binary field onthe Transform business object pulls the transform XML file into the ContentManager. The form provides a way to upload/download the XML file so the


transform XML can be easily maintained. TRIRIGA comes preconfigured with anETL Job Item as the implementation of this business object or form.

Within the TRIRIGA Application Platform, a workflow runs on a schedule andcalls a Custom workflow task for each fact table that needs to be loaded orupdated. The Job Scheduler provides a mechanism that automatically calls thecustom workflow task for ETL Job Items.

TRIRIGA ships all business objects, forms, and workflows required to support theas-delivered TRIRIGA Workplace Performance Management and TRIRIGA RealEstate Environmental Sustainability products.

Defining and maintaining ETL transformsUse an ETL development environment to create a transformation to move data.During the transform, you can do calculations and use variables from TRIRIGAApplication Platform and from the system.

Using ETLs with Pentaho SpoonYou can use Pentaho Spoon as an ETL development environment.

Overview of using Pentaho Spoon:

You must first create the source and destination tables and establish thecorresponding mappings. Next, you must identify variables that need to be passedinto the transform and add these variables to the transform business object orform. Then, you can use Pentaho Spoon and the following steps to define andmaintain transforms.

Tip: It might not be necessary to perform all of the following steps. The steps thatare required depend on whether you are defining or maintaining a transform.v Run the spoon.bat or kettle.exe file by opening Spoon. Select No Repository as

you do not need to use one.v Either open an existing XML file, that was downloaded to the file system by

using the Transform Form, or use File > New > Transformation to create a newtransform.

v Define the JNDI settings for the local database. Use TRIRIGA as the connectionname. Set the database connection in the tool by using View > DatabaseConnections > New. When the transform is run by the workflow, the connectionis overwritten with the application server’s connection information.

v Use the Design menu to lay out the transform as follows:– Extract rows from the tables by using Design > Input > Table Input.– Make sure that all row fields have values when used in a calculation with

Design > Transform > Value Mapper.– Use Design > Transform > Calculator for calculations.– Provide the sequencing for the destination rows with Design > Lookup >

Call DB Procedure by using the NEXTVAL stored database procedure.– Use Scripting > Modified JavaScript Value and other steps to transform data

as necessary.– Identify the table to which the rows are output with Design > Output >

Table Output

– Map the fields by Generated Mappings against Target Step.v Link steps by using View > Hops and lay out the transform, step-by-step.


v Test thoroughly by using execute, and other available utilities. Testing makessure that the process is accurate and that the expected rows are returned andtransformed appropriately.

v Save the transform by using File > Save. Do not save to the repository. Instead,set the file type to XML and save with the .ktr file extension. If you do not setthe file type, the default is Kettle transform, which saves an XML file with the.ktr file extension.

Installing Pentaho Spoon:

You can install Pentaho Spoon as an ETL development environment. Use version3.1, which is the version with which TRIRIGA integrates.

Procedure

1. Locate Pentaho Spoon version 3.1 at http://sourceforge.net/projects/pentaho/files/Data%20Integration/3.1.0-stable/pdi-open-3.1.0-826.zip.

2. Extract the files from the .zip file and keep the directory structures intact.3. Review the most recent version of Pentaho Spoon and accompanying detailed

documentation at http://kettle.pentaho.org/.

Setting up a local JNDI:

You must define the local JNDI settings for your database by updating theproperties file.

Procedure

1. From the pdi-open-3.1.0-826/simple-jndi directory, edit the jdbc.propertiesfile and add the following properties:v LocalJNDI/type=javax.sql.DataSource

v LocalJNDI/driver=oracle.jdbc.driver.OracleDriver

v LocalJNDI/url=jdbc:oracle:thin:@localhost:1521:orcl

v LocalJNDI/user=tridata2

v LocalJNDI/password=tridata2

2. Update the information as appropriate, including the driver if you are usingDB2 or SQL Server.

3. Save and close the file.

Creating transforms and database connections:

You can create transforms and database connections for use between PentahoSpoon and TRIRIGA

Procedure

1. Run the spoon.bat file in the pdi-open-3.1.0-826 directory by opening theSpoon tool. Choose to run without a repository.

2. To create a new transform, right-click Transformations and select New.3. In View mode, create your database connection. Right-click database

connections within Transformations and select New.4. The Custom workflow task replaces the TRIRIGA connection with the

application server JNDI settings. Configure the database connection as follows:v Connection Name:TRIRIGA

v Connection Type:Oracle


http://sourceforge.net/projects/pentaho/files/Data%20Integration/3.1.0-stable/pdi-open-3.1.0-826.zip

http://sourceforge.net/projects/pentaho/files/Data%20Integration/3.1.0-stable/pdi-open-3.1.0-826.zip

http://kettle.pentaho.org/

v Access:JNDI

v Settings:JNDI Name:LocalJNDI

5. Select Test to make sure that the connection is set up correctly.6. Save the database connection details.7. Be sure to save the transform as an XML file not in the repository. The

extension for the Kettle transformation is .ktr. The default for Kettletransformation saves the file as .ktr.

Running a transform from Pentaho Spoon:

You can run a transform that is either completed or is in the process of beingcompleted.

Procedure

1. Save the transform and select Run.2. Set variables, if necessary.3. Select Preview to display the changes to the input stream as each step is run.

Selecting Spoon steps:

You can use the Design Mode to select the various Spoon step types and add themto a transform.

Procedure

1. To add a step to a transform, select Step type and drag the step in the leftnavigation onto your palette.

2. To link two steps, select View in the left navigation and double-click Hops.3. Put in the From and To steps and select OK.4. Alternatively, you might select Ctrl+click on two steps, right-click one of the

steps, and select New hop.5. To add a note to the transform, right-click the palette and select New Note.

Spoon example transform:

You can download a copy of any of the existing .ktr scripts that are contained inan existing ETL job item to follow along in the step descriptions. The followingshows an example of a Spoon transform.

Most of the as-delivered ETLs have the same flow as the example but the specificsare different, for example, the database tables from which data is extracted andhow the data is transformed.

The example transform includes the following items:v Pulls input rows and fields from T_TRIORGANIZATIONALLOCATION org,

and T_TRISPACE space where org.TRILOCATIONLOOKUPTXOBJID =space.SPEC_ID.

v Uses IBS_SPEC.UPDATED_DATE to limit the rows that are selected, by usingthe date range that is passed in from the transform business object.

v Use Value Mapper to make sure that there is a value in all rows forspace.TRIHEADCOUNTNU, space.TRIHEADCOUNTOTHERNU, andorg.TRIALLOCPERCENTNU, if not set it to 0.


v Uses the Calculator to set TRIFACTTOTALWORKERSASS to(space.TRIHEADCOUNTNU + space.TRIHEADCOUNTOTHERNU) *org.TRIALLOCPERCENTNU.

v Gets TRICREATEDBYTX and TRIRUNDA, passed in from the Transform BOthrough Get Variables step.

v Uses Add Constant to set the sequence name and increment so that it isavailable in the input stream for sequencing step.

v Uses the DB Procedure NEXTVAL to set the SPEC_ID, set this step to use fivethreads for enhanced performance.

v Uses a JavaScript scripting step to determine whether the project was on time ornot, and to calculate the duration of the project. Set this step to use three threadsfor better performance.

v Maps the fields to T_TRISPACEALLOCFACTOID.

Key things to consider as you build a transform include the following items:v Test as you add each step to make sure that your transform is doing what you

want.v Transforms need to be developed in a defensive manner. For example, if you are

making calculations that are based on specific fields, all rows must have a valuein these fields, no empties. If not, the transform crashes. Use Value Mapper tomake sure that all fields used in a calculation have a value.

v Dates are difficult to handle as the databases TRIRIGA supports keep DATE andTIME in the date field. Date solutions show how to handle date ranges in SQL.

v Make sure to use JNDI settings and that your transform database isindependent, especially if your solution needs to run multiple databaseplatforms (DB2, Oracle, and Microsoft SQL Server).

v Any attributes on the Transform business object are sent to the Transform as avariable. There are a couple exceptions. Attributes of type Time or SystemVariable are ignored. You can use the variables in your SQL or pull them intothe input stream by using Get Variables with the following syntax:${VariableName}, where VariableName is the attribute name.

v Make sure to completely test and set up the transform before you use variablesin the Table Input. It is challenging to test JavaScript, Table Input Preview, andTable Mapping. You can set variables in the transform with Edit > SetEnvironment Variables or in the Execute page Variable section. By usingvariables more of the test functions within Spoon are made available.

v Test your connection before you use JNDI, before you run a search, or beforeyou run a Spoon transform. The JNDI connection must be tested to avoid Spoonhaving any potential performance issues.

v Consider adding an index. It can be key to performance as the ETLs pull datafrom the T tables in a manner that is different from the regular application.

The preceding items detail the transform as you configure the Spoon steps used.The items concentrate on the main steps that are used by the transforms that aredelivered with TRIRIGA. Spoon provides other step types that you can use tomanipulate your data; use the steps as necessary, depending on your transformneeds.

Configuring Spoon input steps:

You can use input steps to bring data into the transform.


About this task

Table input is the source of most of your data. By using the specified databaseconnection, you can set up SQL to extract data from tables.

Procedure

1. Double-click a table input step to open up the information for the step.2. Set the connection to TRIRIGA or the source database.3. Enter your SQL into the SQL table.4. Select OK to save the table input.5. Select Preview to preview the data that the table input includes. If you are

using variables in SQL, the variables must be set for the Preview to function.You must either hardcode the variable values while testing or select Edit > SetEnvironment Variables to set the variable values. The variables in SQL are$(triActiveStartDA_MinDATE} and ${triActiveEndDA_MaxDATE}.

Results

The SQL provided extracts input rows from T_TRIORGANIZATIONALLOCATIONorganization and T_TRISPACE space, where org.TRILOCATIONLOOKUPTXOBJID= space.SPEC_ID. It uses dates from the transform business object to limit the datathat is included.

Configuring Spoon transform steps:

You can use transform steps to change input data or add information to the inputstream.

About this task

In the Spoon example transform, the Calculator, Add Constants, and ValueMapper steps are used. You can add a sequence through Spoon but it is notdatabase independent and does not work on SQL Server. Instead, you can use theprovided DB Procedure.

Procedure

1. Use the Value Mapper step to ensure that fields have values or to set fieldsdifferent values. You can set values to a target field based on the values of asource field. If the target field is not specified, the source field is set instead ofthe target field. You must ensure that all the fields in a calculation have avalue. If a null value is encountered during a calculation the transform fails.

2. Double-clicking the Value Mapper opens the dialog to input the necessaryinformation. In the Spoon example transform, it is used to set a field to 0 if itdoes not have a value.

3. Use the Add Constants step to add constants to the input stream and to set thevalues that are needed for the NEXTVAL DB Procedure.

4. You must have this step in all transforms that use the NEXTVAL DB Procedure.Set SEQ_NAME to SEQ_FACTSOID and INCR to 1.

5. Use the Calculator step to take fields and run a limited set of calculations. Itprovides a set of functions that are used on the field values. The Calculatorstep performs better than using JavaScript scripting steps.

6. The built-in calculations are limited. Select the Calculation column to show thelist of available functions.


Configuring Spoon lookup steps:

You can use lookup steps to extract extra data from the database into the datastream.

About this task

The Call DB procedure allows the transform to call a database procedure.Information flows through the procedure and back to the transform. You can createsequences for the fact table entries.

Procedure

1. Set up your DB procedure call to use NEXTVAL, to send in SEQ_NAME andINCR and to output by using CURR_VALUE.

2. Determine how many instances of this lookup step to run. When you aretesting the transform, running this step with five instances greatly helps withperformance. For example, for 30,000 records the performance time reducesfrom 90 seconds down to 30 seconds.

3. Change the number of threads that run a step by right-clicking the step andselecting Change number of copies to start.

4. Tune the number of threads that are running the DB Procedure step.

Configuring Spoon job steps:

Even though you are not creating jobs that you need to get Kettle variables andfields into the input stream, you need to ensure that you can set an output field toa variable.

About this task

In the Spoon example transform, the triCreatedByTX and triRunDA variables arebrought into the input stream. You also get variables to pull in the ONTIME andDURATION variables so that you can set them during the JavaScript scriptingsteps.

Procedure

It is important in case there is a failure during a transform to time stamp when thetransform is run. The example does it by using the triRunDA variable and thisprovides an avenue for rollback, even though the process does not have explicitsteps for it. When you are setting fields to values in the transform, they must bethe same type otherwise the transform fails.

Configuring Spoon scripting steps:

You can use scripting steps to implement JavaScript features.

About this task

You can use it for specific data manipulations on the input stream that cannot bedone with the Calculator. You can calculate the duration or set values into thestream, which is based on other values with an if/then/else clause. You can setvalues into the transform stream that are constants or are from a variable.


Procedure

1. Use JavaScript scripting if you need logic to set the values.2. In the Spoon example transform, duration is calculated by subtracting two

dates from each other. The duration then determines whether a plan was ontime.

3. With the JavaScript scripting features, if you want information out of the TableInput rows, you must iterate to find the field you want. You cannot access thefield directly, unless you alias the field in the Table Input step.

Example

The JavaScript scripting example details how to obtain and set the variables.var actualEnd;var actualStart;var plannedEnd;var plannedStart;var duration;var valueDuration;var valueOnTime;

// loop through the input stream row and get the fields// we want to play withfor (var i=0;i<row.size();i++) {

var value=row.getValue(i);

// get the value of the field as a numberif (value.getName().equals("TRIACTUALENDDA")) {actualEnd = value.getNumber();

}if (value.getName().equals("TRIACTUALSTARTDA")) {actualStart = value.getNumber();

}if (value.getName().equals("TRIPLANNEDENDDA")) {plannedEnd = value.getNumber();

}if (value.getName().equals("TRIPLANNEDSTARTDA")) {plannedStart = value.getNumber();

}

// these are the ’variables’ in the stream that we want// to update with the duration and ontime setting// so we want the actual Value class not the value// of the variableif (value.getName().equals("DURATION")) {valueDuration = value;

}if (value.getName().equals("ONTIME")) {valueOnTime = value;

}}

// calculate the duration in daysduration = Math.round((actualEnd - actualStart) / (60*60*24*1000));// calculate the duration in hours// duration = (actualEnd - actualStart) / (60*60*1000);

// set the duration into the ’variable’ in the rowvalueDuration.setValue(duration);

// determine ontime and set the value into the// ’variable’ in the row streamif ((actualEnd == null) || (plannedEnd == null))

valueOnTime.setValue("");


else if (actualEnd > plannedEnd)valueOnTime.setValue("no");

elsevalueOnTime.setValue("yes");

Select Test Script to make sure that the JavaScript compiles. The Test Script andPreview steps in Table Input cannot handle variables unless they are set. You canset variables in the transform by using Edit > Set Environment Variables. Thismakes more of the test function within Pentaho Spoon.

For example, you can use Edit > Set Environment Variables and settriActiveStartDA_MinDATE to to_date(‘20061201’, ‘YYYYmmdd’).

If you are using column aliases when you are defining your query, you must usethe same alias when you are looking up the column with getName.

The following example, in the table input step, shows the select option:SELECT mainProject.triProjectCalcEndDA ActualEndDate,

mainProject.triProjectActualStartDA ActualStartDate

If you are looking up the value for ActualEndDate, use the alias and not the columnname from the database, as illustrated:if (value.getName().equals("ActualEndDate")) {

actualEnd = value.getNumber();}

Configuring Spoon output steps:

You can use output steps to write data back to the database.

About this task

Table output and table output mapping stores information to a database. Thisinformation is then used in the fact tables. When you have all the information tosave a transform, you can add output steps to the end of your transform andconnect them to the last step.

Procedure

1. Double-click and add the connection information and the fact table you want touse as the output table.

2. When it is set up and the steps are connected, right-click the table output step.3. Select Generate mapping against this target step.4. Map the source fields to the target fields in the target database and select OK.

Source fields include the additional fields added to the input stream. Ensure toset the table mapping before you use variables in the table input step.

5. To complete your transform, drag the mapping step in between the last twosteps.

6. If necessary, you can modify and add more fields to the mapping step.

Testing the transform:

You can test the transform after you add each Spoon step, or at the end when allSpoon steps are complete. Testing after each step makes debugging easier. Beforeyou can test the transform, you must first save the transform.


About this task

The variables section details the variables that are used in the transform. Whenyou test the transform by using Spoon, you can set values to these variables. Whenthe transform is run from within TRIRIGA, the variables form part of thetransform business object. Set and save the values that are used into the smartobject before the custom workflow task is called.

Procedure

1. Set the triRunDA variable to the date and time of the workflow run. It does notneed to be an attribute on the transform business object. It is the Numberrepresentation of the run date and time. triRunDA does not have six formats ofthe date since it is generated dynamically by the custom workflow task.triRunDA is needed for setting the create date of the fact row.

2. triCreatedByTX is an attribute on the transform business object.3. triActiveStartDA_MinDATE and triActiveEndDA_MaxDATE are the wrapped

representations of triActiveStartDA and triActiveEndDA. During Spoon testing,if you are testing on Oracle or DB2, you must wrap them with to_date (‘thedate you want’, ‘the format’).

4. Click Launch to run the transform. If a step has an error, the step appears inred and the error is saved to the log file. You can access the log file through thelog page.

Date solution:

Several date variables require calculation and comparison when used by PentahoSpoon. Date solution provides these calculations and comparisons.

There are three instances when date solution is required:1. Compare two dates. This comparison is used to determine whether a project is

on time.2. Calculate duration between two dates in days. In some cases, this calculation is

used to calculate the duration in hours.3. Compare a date, such as modified date or processed date, to a range of dates,

such as first day of month and last day of month.

The first and second instances are solved by using JavaScript scripting steps.

The third instance is solved by using a date range in the table input.

There are two types of dates. Dates that are stored as a Date in the database anddates that are stored as a Number in the database.

Tip: All TRIRIGA objects store Date and Date and Time fields as numbers in thedatabase. Select a field as a number to interact with business object tables. Select afield as a date to interact with system platform table fields defined as date.

Select field as date:

You can interact with system platform table fields defined as date by selecting afield as a date.


The following example code uses IBS_SPEC.UPDATED_DATE as the date field todetermine whether a row is needed. triActiveStartDA and triActiveEndDA are thedate range. These dates come from the triActiveStartDA and triActiveEndDA fieldson the transform business object.

The IBS_SPEC table is not a TRIRIGA object. It is a system platform table that isused to track objects in TRIRIGA. It includes a field that changes every time anobject in TRIRIGA is updated. The field is the UPDATED_DATE field and in thedatabase it is a date field, not a number field.

In the following example code, ${triActiveStartDA_MinDATE} and${triActiveEndDA_MaxDATE} are used. These wrapped dates fields fetch all recordsfrom 12:00 am on the start date to 11:59 pm on the end date.SELECT org.SPEC_ID ORG_SPEC_ID, org.TRIORGANIZATIONLOOKUOBJID,space.CLASSIFIEDBYSPACESYSKEY, org.TRIALLOCPERCENTNU, org.TRIALLOCAREANU,space.TRIHEADCOUNTNU, space.TRIHEADCOUNTOTHERNU, spec.UPDATED_DATEFROM T_TRIORGANIZATIONALLOCATION org, T_TRISPACE space, IBS_SPEC specWHERE org.TRILOCATIONLOOKUPTXOBJID = space.SPEC_IDand space.SPEC_ID = spec.SPEC_IDand spec.UPDATED_DATE >= ${triActiveStartDA_MinDATE}and spec.UPDATED_DATE <= ${triActiveEndDA_MaxDATE} order by UPDATED_DATE

In Oracle or DB2, ${triActiveStartDA_MinDATE} displays like to_date (‘2007070100:00:00’, ‘YYYYmmdd hh24:mi:ss’) and ${triActiveEndDA_MaxDATE} displayslike to_date (‘20070731 23:59:59’, ‘YYYYmmdd hh24:mi:ss’).

In SQL Server, these dates look slightly different because of database specifics, butare set up to capture all the rows between the two dates.

Select field as number:

You can interact with business object tables by selecting a field as a number.

Instead of using IBS_SPEC.UPDATED_DATE as the date determination field for theTRIRIGA date, this method compares the determination field directly totriActiveStartDA and triActiveEndDA, since they are all numbers in the database.

In the following example code, triCaptureDA is a field on T_TRISPACE.SELECT org.SPEC_ID ORG_SPEC_ID, org.TRIORGANIZATIONLOOKUOBJID,space.CLASSIFIEDBYSPACESYSKEY, org.TRIALLOCPERCENTNU, org.TRIALLOCAREANU,space.TRIHEADCOUNTNU, space.TRIHEADCOUNTOTHERNU, space.TRICAPTUREDAFROM T_TRIORGANIZATIONALLOCATION org, T_TRISPACE space, IBS_SPEC specWHERE org.TRILOCATIONLOOKUPTXOBJID = space.SPEC_IDand space.TRICAPTUREDA >= ${triActiveStartDA_Min}and space.TRICAPTUREDA <= ${triActiveEndDA_Max} order by space.TRICAPTUREDA

Similar to the date fields, use the Min and Max variables to make sure that thestart is 00:00:00 and the end is 23:59:59. For example, use these variables to makeyour search pick up a record on December 31st at 13:54 in the afternoon.

Date variables:

For each Date or Date and Time attribute on the Fact Transform business object,the system creates six Kettle variables.

The following table summarizes these Kettle variables:


Table 3. Kettle variables

Kettle Variable Description

${triActiveStartDA} No suffix = is the value in milliseconds sinceJanuary 1, 2014, with no changes to the time.This variable is for fields that arerepresented as a number.

${triActiveStartDA_Min} Min = is the value in milliseconds sinceJanuary 1, 2014, with the time value set to00:00:00 for the specified date. This variableis for fields that are represented as anumber.

${triActiveStartDA_Max} Max = is the value in milliseconds sinceJanuary 1, 2014, with the time value set to23:59:59 for the specified date. This variableis for fields that are represented as anumber.

${triActiveStartDA_DATE} DATE = is the wrapped value in dateformat, with no changes to the time. Thisvariable is for fields that are represented asdate in the database.For Oracle or DB2 it is wrapped anddisplays like: to_date (‘2007061522:45:10’,’YYYYmmdd h24:mi:ss’)For SQL Server it displays like: ‘2007061522:45:10’

${triActiveStartDA_MinDATE} MinDATE = is the wrapped value in dateformat, with the time value set to 00:00:00.This variable is for fields that arerepresented as date in the database.

${triActiveStartDA_MaxDATE} MaxDATE = is the wrapped value in dateformat, with the time value set to 23:59:59.This variable is for fields that arerepresented as date in the database.

When you specify the ${triActiveStartDA_Min} and ${triActiveStartDA_Max}variables to see a time period between two dates, you need to capture all the rowswithin the time period. You need to start at midnight and stop at 1 second beforemidnight. If you use only the date value, you might not get all the rows that youwant, depending on the time on the variable. You must specify the minutes andseconds because both TRIRIGA databases store dates in a date time or numberfield.

The ${triActiveStartDA_MinDATE} and ${triActiveStartDA_MaxDATE} variables helpwith date comparisons.

For example, for triActiveStartDA whose value is 20070615 22:45:10,triActiveStartDA_MinDATE =(Oracle) to_date('20070615 00:00:00','YYYYmmdd h24:mi:ss')(SQL Server) '20070615 00:00:00'triActiveStartDA_MaxDATE =(Oracle) to_date('20070615 23:59:59','YYYYmmdd h24:mi:ss')(SQL Server) '20070615 23:59:59'


Moving ETL Scripts into TRIRIGA from Kettle:

Once the transform is completed and tested, it must be uploaded to the TRIRIGAETL job item.

Remember: Save the transform with the file type as XML and extension .ktr.

The following graphic describes the flow between the ETL environment andTRIRIGA.

DatabaseFile system

XML file


Save XMLOpen XML


the transforms

UploadDownload

Contentmanager


Contentmanager

Variables passed to Kettle:

All variables that are passed to Kettle are of type String. Number variables areconverted by the custom workflow task to type String. The TRIRIGA field typesthat are supported in Kettle are Text, Boolean, Date, Date and Time, Locators, andNumbers.

Table 4. The following are example fields on the ETL Job Item:

Field name Field label Field type

triActiveEndDA Active End Date Date

triActiveStartDA Active Start Date Date

triBONamesTX BO Names Text

triControlNumberCN Control Number Control Number

triCreatedByTX Created By Text

triLocator triLocator Text

triModuleNamesTX Module Names Text

triNameTX Name Text

triTransformBI Transform File Binary


Table 5. The following are the variables passed to Kettle:

Variable that is passed to Kettle Description

triNameTX (Text)

triActiveStartDA (Number) date in milliseconds since January1, 2014

triActiveStartDA_DATE (Date) wrapped if Oracle or DB2, time iswhatever it was on the attribute

triActiveStartDA_MinDATE (Date) wrapped if Oracle or DB2, time is00:00:00

triActiveStartDA_MaxDATE (Date) wrapped if Oracle or DB2, time is23:59:59

triActiveStartDA_Min (Number) date in milliseconds since January1, 2014, time is 00:00:00

triActiveStartDA_Max (Number) date in milliseconds since January1, 2014, time is 23:59:59

triActiveEndDA (Number) date in milliseconds since January1, 2014

triActiveEndDA_DATE (Date) wrapped if Oracle or DB2, time iswhatever it was on the attribute

triActiveEndDA_MinDATE (Date) wrapped if Oracle or DB2, time is00:00:00

triActiveEndDA_MaxDATE (Date) wrapped if Oracle or DB2, time is23:59:59

triActiveEndDA_Min (Number) date in milliseconds since January1, 2014, time is 00:00:00

triActiveEndDA_Max (Number) date in milliseconds since January1, 2014, time is 23:59:59


triCreatedByTX (Text)

triRunDATE (Number) Run Date set by Customworkflow task

triLocator (Text – Locator) is a locator field thatcontains a reference to another businessobject. This variable contains the text valueof that record’s field

triLocator_IBS_SPEC (Text - Locator) contains the spec_id of therecord in the triLocator field. You can usethis spec_id to find information related thatrecord through other database tables

triControlNumberCN and triTransformBI are not passed to Kettle.

Important: Things to remember about variables:v There are six variables for each Date, and Date and Time, type field. TRIRIGA

wraps the value and hands it to Kettle in six different formats.v Variables in Kettle are all strings. If you need a variable to be a number in the

script, you need to use a conversion. You can set a number field like, forexample, TRICREATEDDA, with a variable like, for example, triRunDATE.


Kettle does some implicit conversions, but if you want to do any calculationswith a variable, you must first convert the variable to a number.

v For dates, you must use the correct representation. For example, you cannotinclude spec.UPDATED_DATE >= ${triCreatedDA} in your selection.spec.UPDATED_DATE is a date while triCreatedDA is a number. The results areinaccurate or the SQL fails.

v The attribute types supported to pass to Kettle are limited to Text, Boolean, Date,Date and Time, and Numbers. All other TRIRIGA data types are skipped (exceptLocators).

v For Locator fields, two variables are created, one for the text of the Locator andthe other for the SPEC_ID of the linked record. You can use the SPEC_ID to findinformation that is related to that record through other database tables.

Debugging ETL scripts in the application:

To debug ETL scripts in the application, you must first set up logging and thentrigger the RunETL Custom workflow task to view the log information.

Setting up logging:

TRIRIGA provides debugging capabilities when ETL scripts run in the TRIRIGAapplication.

Procedure

1. In the Administrator Console, select the Platform Logging managed object.Then select the option to turn on ETL logging.

2. Select Category ETL > Transforms > Run Transform to turn on debug loggingin the TRIRIGA platform code that processes ETL job items. Log messages areprinted to server.log.

3. Select Category ETL > Transforms > Kettle to turn on debug logging in theKettle transforms. Log messages are printed to the server.log.

4. Apply the changes. Now when an ETL Script runs, ETL related informationwill be put into the server log.

Important: Because of the large volume of information you may encounter in alog, set Pentaho Spoon logging to debug for only one execution of the ETL jobitem.

Debugging using ETL jobs:

Once you have set up logging, you will need a way to trigger the RunETL Customworkflow task to see any information in the logs.

About this task

If you are using the ETL Job Item, then you can simply click the Run Processaction on that form.

Procedure

Do not forget to fill the field values in the form that the ETL Script would expect.Only use the Run Process action for debugging purposes. For production, use theJob Scheduler instead. Note that Run Process will update tables in the database, sodo not use this action in a production environment.


Example

The following shows a sample log output:2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triCalendarPeriodTX_SPEC_ID} = 3103902

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triCalendarPeriodTX} = \Classifications\Calendar Period\2010\Q4 - 2010\October - 2010

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) *** objectfield found = BoFieldImpl[name=triEndDA,id=1044,Section=BoSectionImpl[name=General,id=BoSectionId[categoryId=1,subCategoryId=1],Business Object=BoImpl[name=triETLJobItem,id=10011948,module=ModuleImpl[name=triJobItem,id=22322]]]]

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triEndDA_MinDATE} = to_date(’20101031 00:00:00’,’YYYYmmdd hh24:mi:ss’)

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triEndDA_MaxDATE} = to_date(’20101031 23:59:59’,’YYYYmmdd hh24:mi:ss’)

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triEndDA_DATE} = to_date(’20101031 00:00:00’,’YYYYmmdd h24:mi:ss’)

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triEndDA} = 1288508400000

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triEndDA_Min} = 1288508400000

2011-01-21 14:01:27,125 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) Kettlevariable set - ${triEndDA_Max} = 1288594799000

2011-01-21 14:02:10,595 INFO [SpaceFact](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) SpaceFact - Process Remove Nulls (LEGALINTEREST_SPEC_ID)’.0ended successfully, processed 3282 lines. ( 76 lines/s)

2011-01-21 14:02:10,595 INFO [SpaceFact](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) SpaceFact - Process Remove Nulls ( REALPROPERTYUSE_SPEC_ID)’.0 ended successfully, processed 3282 lines. ( 76 lines/s)

2011-01-21 14:02:10,595 INFO [SpaceFact](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) SpaceFact - Process Remove Nulls ( REALPROPERTYTYPE_SPEC_ID)’.0 ended successfully, processed 3282 lines. ( 76 lines/s)

2011-01-21 14:02:10,595 INFO [SpaceFact](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) SpaceFact - Process Filter rows’.0 ended successfully,processed 3307 lines. ( 76 lines/s)

2011-01-21 14:02:10,595 INFO [SpaceFact](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) SpaceFact - Process Dummy (do nothing)’.0 ended successfully,processed 25 lines. ( 0 lines/s)


2011-01-21 14:02:10,595 INFO [SpaceFact](WFA:11325389 - 3070255 triProcessManual:38447392 IE=38447392) SpaceFact - Process Query for Space’.0 ended successfully,processed 0 lines. ( 0 lines/s)

Performance tuning tips:

Use the following tips to improve performance of ETLs with Spoon.

Summary

1. When you are finished getting your ETL to do what you want it to do, take abaseline performance measurement.

2. Using Spoon, run the ETL against a database where you have thousands ofrows added to your fact table.

3. Make sure that you are using a JNDI connection and running Spoon on thenetwork where the database lives so that you do not have network latency. Donot run it through a VPN.

4. Get a completed list of your run. For example, from a run of thetriSpacePeopleFact ETL.

Analysis

1. JavaScript and DB Procedure (Get Next Spec ID) steps have multiple copies.Right-click on the step and changing the number of copies to start.v For the triSpacePeopleFact ETL in the preceding example run, changing the

JavaScript and DB Procedure (Get Next Spec ID) steps to three copies ofeach:

v Unaltered: 12.9, 12.7, 12.5, 12.6v Three copies of each: 11.4, 11.6, 12.3, 12.2

2. Change the default row set size from 1000 to 10000. New transformations havethis set automatically. Right-click the ETL and open the properties of thetransform.

3. Analyze the run. Is there a bottleneck? Is there a step that is slower than theothers? Possibly other steps can have multiple copies for better throughput.

4. Is the Data Input step a bottleneck? Will an index to the database help? If so,add an index and rerun. Is the performance better? Maybe use a Filter stepinstead of using the database to filter down the result set.

5. Analysis is an iterative process. Always have multiple copies of the JavaScriptand DB Procedure (Get Next Spec ID) steps.

6. An ETL run with 300-800 rows per second is performing well and definitely inthe acceptable performance range.

For the triSpacePeopleFact ETL, after initial development substantial improvementswere achieved by just doing Steps 1 and 2.

Whereas, for the triSpaceFact ETL, substantial improvements were achieved bydoing Steps 1, 2, and 4.

The following shows the triSpacePeopleFact ETL run with Steps 1 and 2:Query for Space People: Time = 11.6 sec; Speed (r/s) = 743.6

The following shows the triSpaceFact ETL run with Steps 1 and 2:Query for Space: Time = 313.9 sec; Speed (r/s) = 24.0


Notice that it is clear that the Query for Space step, which is the Data Input step, isa bottleneck at 24 rows per second.

Notice that the Query for Space People is not a bottleneck like the Query for Spacestep. The triSpaceFact ETL runs well without any modifications besides Steps 1and 2, getting over 700 rows per second.

For Step 4 on the triSpaceFact ETL, look at the SQL for the Query for Space task.Notice in the SQL that there are SUMs. SUMs are expensive, especially since thereare two of them and none of the fields are indexed.

Add an index toT_TRIORGANIZATIONALLOCATION.TRILOCATIONLOOKUPTXOBJID. It isonly necessary to add an index to TRILOCATIONLOOKUPTXOBJID, even thoughthe TRISTATUSCL is in the SELECT SUM WHERE. TRISTATUSCL is a 1000character field and made the index slow and not even viable on SQL Server.CREATE INDEX IDX01_TRIORGALLOC ON T_TRIORGANIZATIONALLOCATION(TRILOCATIONLOOKUPTXOBJID) NOPARALLEL;

Rerun the ETL.

The following shows the triSpaceFact ETL run with Steps 1, 2, and 4.Query for Space: Time = 3.2 sec; Speed (r/s) = 2378.3

Notice that the change in the Data Input step rows per second (2378.3) and howlong the ETL took to run (3.2 seconds for 7544 rows).

Important: Things to keep in mind while you are developing your ETLs:v Avoid complex SQL and aggregate functions like COUNT, MIN, MAX, and

SUM. If you need to use these functions, see whether an index helps out theData Input step. Do not create an index on a field that is large varchar; SQLServer can handle only indexes < 900 bytes.

v Avoid OR and NOT and using views (M_TableName in TRIRIGA databases) ifpossible.

v Use the Calculator step instead of JavaScript if that is possible. The JavaScriptstep can be expensive.

v Have only one JavaScript scripting step.

Using ETLs with IBM Tivoli Directory Integrator ConfigurationEditorTivoli Directory Integrator Configuration Editor is the ETL developmentenvironment that is included in Tivoli Directory Integrator. Configuration Editorlets you create, maintain, test, and debug ETL transforms, which Tivoli DirectoryIntegrator calls configuration files; it builds on the Eclipse platform to provide adevelopment environment that is both comprehensive and extensible.

Before you begin

System developers who are responsible for defining or maintaining transformsusing Configuration Editor must have access to and experience working withTRIRIGA databases.

Before you can define and maintain ETL transforms using Tivoli DirectoryIntegrator Configuration Editor, you must complete the following tasks:v Create the source and destination tables


v Establish the corresponding mappingsv Identify the variables that need to be passed into the transformv Add the variables to the transform business object or form

Installing Tivoli Directory Integrator Configuration Editor:

Although the Tivoli Directory Integrator runtime server is automatically installedwith TRIRIGA, to create or modify Tivoli Directory Integrator AssemblyLines, youmust install Configuration Editor on the workstation that you will use for ETLdevelopment.

Procedure

1. Download and install the appropriate Tivoli Directory Integrator install packagefrom Passport Advantage at http://www.ibm.com/software/howtobuy/passportadvantage/pao_customers.htm.

Option Description

TDI711_TAP340_Install_Wind.zip IBM Tivoli Directory Integrator V7.1.1Installer for IBM TRIRIGA ApplicationPlatform V3.4.0 on Windows Multilingual

TDI711_TAP340_Install_Linux.tar IBM Tivoli Directory Integrator V7.1.1Installer for IBM TRIRIGA ApplicationPlatform V3.4.0 on Linux Multilingual

TDI711_TAP340_Install_SOLIntl.tar IBM Tivoli Directory Integrator V7.1.1Installer for IBM TRIRIGA ApplicationPlatform V3.4.0 on Solaris Intel Multilingual

TDI711_TAP340_Install_SOLSprc.tar IBM Tivoli Directory Integrator V7.1.1Installer for IBM TRIRIGA ApplicationPlatform V3.4.0 on Solaris Sparc Multilingual

TDI711_TAP340_Install_AIX.tar IBM Tivoli Directory Integrator V7.1.1Installer for IBM TRIRIGA ApplicationPlatform V3.4.0 on AIX Multilingual

2. Update to IBM Tivoli Directory Integrator V7.1.1 Fixpack 3, which is availableat http://www.ibm.com/support/docview.wss?uid=swg27010509.

3. Apply 7.1.1-TIV-TDI-LA0014, which is available by contacting IBM support.4. From the TRIRIGA install directory, copy the JDBC driver for the database type

your AssemblyLines will connect to into the Tivoli Directory Integratordirectory TDI Install Directory/jars.v For SQL Server, copy jtds-1.2.8.jarv For Oracle, copy ojdbc6.jarv For DB2, copy db2jcc4.jar

Changing the ports that are used by Tivoli Directory Integrator:

You specify the ports that are used by Tivoli Directory Integrator during theTRIRIGA installation. In rare cases, you may need to change these port settings.

About this task

To change the port on which TRIRIGA sends ETL transforms to Tivoli DirectoryIntegrator to execute, change TDI_HTTP_SERVER_PORT in TRIRIGAWEB.properties.v


http://www.ibm.com/software/howtobuy/passportadvantage/pao_customers.htm

http://www.ibm.com/software/howtobuy/passportadvantage/pao_customers.htm

http://www.ibm.com/support/docview.wss?uid=swg27010509

To change the port that is used by the Tivoli Directory Integrator Agent to managethe Tivoli Directory Integrator server, complete the following tasks:v Change TDI_SERVER_PORT in TRIRIGAWEB.propertiesv Change api.remote.naming.port in TRIRIGA_Install_Directory/TDI_IE/

TDISolDir/solution.properties

Getting started with Tivoli Directory Integrator Configuration Editor:

To get ready to define and maintain ETLs with Configuration Editor, you must firstlearn basic tasks, such as opening Configuration Editor, understanding the views,and creating a project, AssemblyLine, hook, script, or connector, and importing aconfiguration file.

Configuration Editor is started by using the ibmditk wrapper script. This script isin the Tivoli Directory Integrator installation directory. Choose a workspace folderto store your projects and files.

The workspace window of Configuration Editor displays the following views:v The navigator (upper left) contains all of the projects and source files for server

configurations and Tivoli Directory Integrator solutions. The navigator can alsocontain other files and projects, such as text files. Configuration Editor treatsTivoli Directory Integrator projects specifically, so other files and projects remainunaffected by the Configuration Editor.

v The servers view (lower left) shows the status for each of the servers that aredefined in the TDI Servers project. You can define an unlimited number ofservers. The server view provides a number of functions to operate on serversand their configurations. The Refresh button refreshes status for all servers inthe view.

v The editor area (upper right) is where you open a document, such as anAssemblyLine configuration, to edit. This area is split vertically with an area thatcontains various views to provide other relevant information. Among the mostimportant are the Problems view that shows potential problems with a TivoliDirectory Integrator component, the Error Log that shows errors that occurwhile you are developing solutions, and the Console view that shows theconsole log for running Tivoli Directory Integrator servers, for example, thosethat are started by Configuration Editor.

Common activities include the following basic tasks:v To create a project, right-click File > New > Project.v To create an AssemblyLine, select a project from the navigator and right-click

File > New > AssemblyLine. An AssemblyLine is a set of components that arestrung together to move and transform data. An AssemblyLine describes theroute along which the data will pass. The data that is handled through thatjourney is represented as an Entry object. The AssemblyLine works with a singleentry at a time on each cycle of the AssemblyLine. It is the unit of work in TivoliDirectory Integrator and typically represents a flow of information from one ormore data sources to one or more targets. You must enter the name that yougive the new AssemblyLine when you create the ETL job item that runs thisAssemblyLine from TRIRIGA.

v To add an AssemblyLine hook, in the editor area, click Options > AssemblyLineHooks. Enable the checkbox next to one of the hooks and click Close. After thehook has been added, you can select the hook and add JavaScript code.

v To add a script, in the editor area, select either the Feed or Data Flow folder.Right-click and select Add Component. Select the scripts filter, then select a


component and click Finish. Select the script that was added to yourAssemblyLine and add a user-defined block of JavaScript code.

v To add a connector, in the editor area, select either the Feed or Data Flow folder.Right-click and select Add Component. Select the connectors filter, then select acomponent and click Finish. Select the connector that was added to yourAssemblyLine and specify the required configuration data.

v To import a configuration file, click File > Import, then select IBM TivoliDirectory Integrator and Configuration. Click Next, specify a configuration file,then click Finish. When prompted for the project name, enter a name and clickFinish.

For more information on using Tivoli Directory Integrator, see the IBM TivoliDirectory Integrator Version 7.1.1 information center at http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/topic/com.ibm.IBMDI.doc_7.1.1/welcome.htm.

Tivoli Directory Integrator Agent:

The Tivoli Directory Integrator Agent provides the interface to start and stop theTivoli Directory Integrator runtime server from within TRIRIGA.

The Tivoli Directory Integrator server must be running for Tivoli DirectoryIntegrator type ETL job items to run successfully. ETL job Items fail if the TivoliDirectory Integrator server is not running and an error is printed to the TRIRIGAserver log.

To start or stop the Tivoli Directory Integrator server, go to the Agent Managerpanel in the Administrator Console and start or stop the Tivoli Directory IntegratorAgent. Once started, this agent runs on a schedule and monitors the TivoliDirectory Integrator server. If it finds the Tivoli Directory Integrator server notrunning, it attempts to restart it.

For more information on the Agent Manager panel, see the IBM TRIRIGAApplication Platform 3 Administrator Console User Guide.

Tivoli Directory Integrator Agent Configuration:

You can configure properties to check that the Tivoli Directory Integrator server isrunning.

The TDI_AGENT_SLEEPTIME property for the application server that runs the TivoliDirectory Integrator Agent controls how often the agent checks if the TivoliDirectory Integrator server is running and tries to restart it.

The TDI_SERVER_TIMEOUT property for the application server that runs the TivoliDirectory Integrator Agent controls how long to wait for a Tivoli DirectoryIntegrator server start or stop command to complete before raising a failure.

These properties are defined in the TRIRIGAWEB.properties file.

Tivoli Directory Integrator Agent Logging:

Issues with the Tivoli Directory Integrator Agent are logged in the server.log. Toobtain additional information turn on debug logging in the Tivoli DirectoryIntegrator Agent by changing platform logging settings in the Admin Console onthe server where the agent is running.


http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/topic/com.ibm.IBMDI.doc_7.1.1/welcome.htm



Additional logging configuration can be done by changing the log4j settings on theserver where the agent is running.

For example, you can monitor problems with the Tivoli Directory Integrator serverby following standard log4j conventions. If the Tivoli Directory Integrator Agent isunable to start the Tivoli Directory Integrator server, it writes an error to thefollowing log4j category:"com.tririga.platform.tdi.agent.TDIAgent.TDISTARTFAILED". By default, errorswritten to this category go to the TRIRIGA server log. You can configure differentbehavior, such as sending a mail notification, by configuring that category to writeto a different log4j appender.

Running transforms from Tivoli Directory Integrator Configuration Editor:

To run a transform that is either in process of development or completed, save thetransform and then click Run from the toolbar.

About this task

A new window or tab is opened in the editor area with the same name as theAssemblyLine. This window will show the results from the execution of theAssemblyLine. You can also use the Debugger button to see the changes to theinput stream as each step is performed.

Tivoli Directory Integrator Configuration Editor example transform:

The following information provides an example of some of the steps involved in aTivoli Directory Integrator transform.

You can download a copy of the ‘Load Meter Item Staging Table’ .xmlconfiguration file that is contained in an existing ETL job item to follow along inthe step descriptions. Many of the as-delivered ETLs follow the same flow exceptthe specifics are different; for example, the database tables from which data ispulled and how the data is transformed.

This example transform does the following:v Specifies the logging parameters that are used by this AssemblyLine, including

Logger Type, File Path, Append, Date Pattern, Layout Pattern, Log Level, andLog Enabled.

v Establishes a database connection by using the AssemblyLine connectionparameters as configured for DB-TRIRIGA.

v Retrieves the Energy Log Lag Time parameter from the Application Settingstable

v Uses a JDBC connector to retrieve records from the Asset Hourly Fact tablev Uses an Input Attribute Map to map the database columns to internal Tivoli

Directory Integrator work entry attributesv Uses a Tivoli Directory Integrator hooks and scripts to perform data transforms.v Uses a JDBC connector to insert or update records in the Environmental Meter

Item staging tablev Uses an Output Attribute Map to map internal work variables to the database

columns in the table that is configured in the JDBC Output Connectorv Uses On Success hook to complete the following tasks:

– Log processing statistics for the run


– Set errCount and errMsgv Uses Tivoli Directory Integrator On Failure hook to complete the following tasks:

– Log error messages– Set errCount and errMsg

The following sections provide more details about the key components of theTivoli Directory Integrator transform. The discussion concentrates on the maincomponents that are used by the TRIRIGA-delivered transform. Tivoli DirectoryIntegrator provides many other components that you can use to manipulate yourdata depending on your transform needs.

Consider the following tips as you build a transform:v Use the testing features that are available as you build your AssemblyLine to

make sure that your transform is doing what you want.v Transforms need to be developed in a defensive manner. For example, if you are

doing calculations that use specific fields, all rows must have a value in thesefields, no empties. If not, the transform crashes. Add verification logic to makesure all fields that are used in a calculation have a value.

v Check for null or undefined variables and return errors if the null or undefinedvariables are needed for your AssemblyLine to succeed. For example, if yourAssemblyLine depends on the DB-OTHER variables (for example jdbcOthUrl,jdbcOthDriver) in order to make a connection to an external database, it mustcheck that those variables are defined and handle the error case appropriately.

v Dates are difficult because all of the databases that TRIRIGA supports keepDATE and TIME in the date field.

v Make sure to use the AssemblyLine Connections and make your transformdatabase independent, especially if your solution needs to run multiple databaseplatforms (DB2, Oracle, and Microsoft SQL Server).

v Test your AssemblyLine Connections before running the Tivoli DirectoryIntegrator transform. Otherwise, your transform might end with a databaseconnection error.

Configuring loggers:

Configure the AssemblyLine loggers and specify the logging parameters that willbe used by this assembly, such as Logger Type, File Path, Append, Date Pattern,Layout Pattern, Log Level, and Log Enabled.

About this task

Note: Adhere to the following log settings conventions so that TRIRIGA willhandle your ETL logging correctly when the transform is run from TRIRIGA:v When you set the file path, specify the relative path ../../log/TDI/log name

where log name is the name of the AssemblyLine. For example, File Path =../log/TDI/triLoadMeterData.log. This ensures the triLoadMeterData.log can beviewed from the TRIRIGA Admin console with the rest of the TRIRIGA logs.

v Set the Logger Type to DailyRollingFileAppender to be consistent with otherTRIRIGA logs.

These log settings apply to logs that are created when the transform is run fromConfiguration Editor. Some of the values will be overridden when the transform isinvoked from a TRIRIGA ETL Job Item. Log level, Date Pattern and Layout Patternwill be overridden with values specified in the TRIRIGA log4j .xml file in order to


make Tivoli Directory Integrator logs consistent with TRIRIGA logs andconfigurable in the same way as TRIRIGA logs.

Procedure

In the editor area, click Options > Log Settings to configure the AssemblyLineloggers and specify the logging parameters that will be used by this assembly.

Example

In the example, we have configured a DailyRollingFileAppender log with the filepath set to ../../log/TDI/triLoadMeterData.log. The AssemblyLine hooks canwrite data to this log by invoking a command similar to the following:var methodName = "triLoadMeterData - LookupMeterItemDTO ";task.logmsg("DEBUG", methodName + "Entry");

Initializing AssemblyLines:

During AssemblyLine initialization, the script engine is started and theAssemblyLine’s Prolog Hooks are invoked.

Procedure

1. In editor area, create an AssemblyLine Hook by clicking Options > AssemblyLine Hooks and enable the Prolog – Before Init checkbox.

2. Add the following JavaScript code to the script to establish a databaseconnection and retrieve the Application Setting parameters:var methodName = "triLoadMeterData - Prolog - Before Init - ";

// By default, this AL is setup to run from TRIRIGA.To run this AL from TDI CE,// the runStandAlone flag should be set to 1 and the database connection// parameters should be specified below.var runStandAlone = 1;

// Retrieve database connection parameters passed into this AL from TRIRIGAif (runStandAlone == 0){task.logmsg("DEBUG", methodName + "Set TRIRIGA db connection parameters");var op = task.getOpEntry();var jdbcTriURL = op.getString("jdbcTriURL");var jdbcTriDriver = op.getString("jdbcTriDriver");var jdbcTriUser = op.getString("jdbcTriUser");var jdbcTriPassword = op.getString("jdbcTriPassword");

}else// Modify these database connection parameters if running directly from TDI{task.logmsg("DEBUG", methodName + "StandAlone: Set default TRIRIGA db

connection parameters");var jdbcTriURL = "jdbc:oracle:thin:@1.1.1.1:1521:test";var jdbcTriDriver = "oracle.jdbc.driver.OracleDriver";var jdbcTriUser = "userid";var jdbcTriPassword = "password";

}

try{

triConn.initialize(new Packages.com.ibm.di.server.ConnectorMode("Iterator"));}


catch (err){

task.logmsg("ERROR", methodName + "TRIRIGA Connection Failed");task.logmsg("DEBUG", methodName + "Exception:" + err);dbConnectionFailed = true;errCount=1;system.abortAssemblyLine("TRIRIGA DB Connection Failed");return;

}triConn.setCommitMode("After every database operation (Including Select)");var conn2 = triConn.getConnection();conn2.setAutoCommit(true);task.logmsg("DEBUG", methodName + "TRIRIGA connection success");

...

// Get application settingstask.logmsg("DEBUG", methodName + "Get triALEnergyLogLagTimeNU fromApplication Settings");

var selectStmt1 = conn1.createStatement();var query = "select TRIALENERGYLOGLAGTIMEN from T_TRIAPPLICATIONSETTINGS";task.logmsg("DEBUG", methodName + "query:" + query);var rs1 = selectStmt1.executeQuery(query);var result = rs1.next();while (result){

try{

energyLogLagTime = rs1.getString("TRIALENERGYLOGLAGTIMEN");if (energyLogLagTime == null) energyLogLagTime=5

}catch (err){task.logmsg("INFO", methodName + "Setting Default Values for

Application Settings");energyLogLagTime=5

}

task.logmsg("INFO", methodName + "energyLogLagTime:" + energyLogLagTime);result = rs1.next();

}rs1.close();selectStmt1.close();

3. Save the AssemblyLine hook.

Example

In our example, the following tasks are completed in the Prolog – Before Init hook:v Retrieve Tivoli Directory Integrator transformation variables passed into the

AssemblyLine. This only occurs when the AssemblyLine is run from an ETL jobitem. When the AssemblyLine is invoked from the Configuration Editor, theJDBC connection parameters need to be configured in the Prolog.

v Establish database connections.v Retrieve AssemblyLine specific settings from the Application Settings record.

Data retrieval:

Data enters the AssemblyLine from connected systems using Connectors and somesort of input mode.


In our example, we use a JDBC connector and an attribute map to feed data to ourAssemblyLine.

Connector

Table input is the source of most of your data. This is where you set up aconnector to pull data from tables by using the database connection parameterspassed to the AssemblyLine during initialization.

In our example, the connector named JDBCConnectorToHourlyFact retrieves inputrows from the Hourly Fact table. This component is added to the feed section ofthe AssemblyLine.

The Connection tab allows you to configure JDBC connection parameters. In theexample, we use Advanced (Javascript) to retrieve the connection parameterspassed to the AssemblyLine during initialization. In addition, the following SQLquery is specified under the Advanced section in the SQL Select field to narrowthe scope of the data retrieved from the database table.select * from T_TRIASSETENERGYUSEHFACT where TRIMAINMETERBL in (’TRUE’) andTRI-MAINMETERPROCESSEDN = 0 order by TRIWRITETIMETX.

Tip: To enable logging of SQL statements for a connector, enable the Detailed Logcheckbox on the connectors Connection tab.

Attribute map

Use a Connector Attribute Map to ensure certain fields have a value or to set afield to a different value. In the Attribute Map, you can set values to a target fieldbased on the values of a source field. If the target field is not specified, the fieldcan be set to a default value instead of the target field.

Click the JDBCConnectorToHourlyFact connector, then click the Input Map tab todisplay the Attribute Map. In the example, we use the map to set default valuesfor some of the fields.

Once the first Connector has done its work, the bucket of information (the "workentry", called, appropriately, "work") is passed along the AssemblyLine to the nextComponent.

Scripting:

Scripts can be added to implement a user-defined block of JavaScript code.

In the example, scripting is used to add custom processing to the AssemblyLine.The following sections describe some of the different ways that scripting was used.

Validation

In our example, we perform field validation on our input data as shown in thescript below:var methodName = "triLoadMeterData - JDBCConnectorToHourlyFact -GetNext Successful ";

task.logmsg("DEBUG", methodName + "Entry");

rowsProcessed = parseInt(rowsProcessed) + 1;


task.logmsg("DEBUG", methodName + "Update count:" + count);selectStmt1.close();system.exitBranch();

}

Data filtering

In our example, there is a script that determines if a record should be processed.The script below includes a conditional statement to compare two date fields. If thecondition is true, the system.exitBrankch() method is called which results in thecurrent record being skipped.// Should we process this record?if (capturedDT < earliestDateUTC){

task.logmsg("DEBUG", methodName + "Skip!");rowsSkipped = rowsSkipped + 1;

// Set triMainMeterProcessedNU=2 flag in T_TRIASSETENERYUSEHFACT table// indicating that we will not process this recordvar selectStmt1 = conn1.createStatement();var query = "update " + tableName + " set TRIMAINMETERPROCESSEDN =

2 where SPEC_ID = \’" + SPEC_ID + "\’";task.logmsg("DEBUG", methodName + "query:" + query);var count = selectStmt1.executeUpdate(query);task.logmsg("DEBUG", methodName + "Update count:" + count);selectStmt1.close();system.exitBranch();

}

Call DB procedure

Stored procedures can be called from JavaScript. Information can flow out throughthe procedure and information can flow back to the transform.

Our example does not implement a stored procedure call, but an example has beenprovided below. Here is an example of how to create sequences for the fact tableentries. It calls a DB Procedure in either DB2, SQL Server or Oracle called NEXTVAL.The following is an example of the NEXTVAL stored procedure:// Stored procedure calltask.logmsg("DEBUG", methodName + "Call Stored Procedure")var command = "{call NEXTVAL(?,?,?)}";try{

cstmt = conn2.prepareCall(command);cstmt.setString(1, "SEQ_FACTSOID");cstmt.setInt(2, 1);cstmt.registerOutParameter(3, java.sql.Types.INTEGER);cstmt.execute();result = cstmt.getInt(3);task.logmsg("DEBUG", methodName + "Result:" + result);work.setAttribute("SPECID", result)cstmt.close();

}catch (e){

task.logmsg("DEBUG", "Stored Procedure call failed with exception:" + e);}


Joining data from different data sources by using a lookup connector:

A lookup can be performed that enables you to join data from different datasources. This action can be implemented using a custom script or using a lookupconnector.

About this task

In our example, we use a script to perform a database lookup and pull additionaldata into the data stream.

Procedure

1. In the editor area, click Data Flow to create a script.2. Click Add Component, select the Empty Script component, and click Finish.3. Add the following JavaScript code to the script to perform a lookup on a

record in the Meter Item DTO table.var methodName = "triLoadMeterData - LookupMeterItemDTO ";

task.logmsg("DEBUG", methodName + "Entry");

// Lookup Meter Item DTO recordvar recordFound = false;var selectStmt1 = conn1.createStatement();var rs1 = selectStmt1.executeQuery("select DC_SEQUENCE_ID,TRICOSTPERUNITNU, TRIQUANTITYNU from S_TRIENVMETERITEMDTO1where TRIMETERIDTX = \’" + work.TRIMETERIDTX.getValue() + "\’and TRITODATEDA = " + TRITODATEDA );var result = rs1.next();while (result){...}rs1.close();selectStmt1.close();

Output of entries to a data source:

During output, transformed data is passed along the AssemblyLine to anotherJDBC connector in some output mode, which outputs the data to the connectedsystem. Since the connected system is record-oriented, the various attributes inwork are mapped to columns in the record using an Output Attribute Map.

Connector

Table output is the target of most of your data. This is where you set up a JDBCconnector to insert or update data into tables using the database connectionparameters passed to the Assembly Line during initialization.

In our example, the connector named JDBCConnectoToMeterItemDTO is used tostore information in the TRIRIGA Environmental Meter Log staging table. Thiscomponent is added to the end of your AssemblyLine once you have all of theinformation generated to save.

The Link Criteria tab on the connector is used to specify the following criteriaused to generate a SQL statement to update the output table. In our example, thecriteria include the following entries:v TRIMETERIDTX equals $TRIMETERIDTX


v TRITODATEDA equals $TRITODATEDA

Tip: The $ is used to indicate that the variable name that follows should bereplaced with the internal work entry value.

Attribute map

The connector has an Output Attribute Map that is used to specify the mappingbetween internal work variables and the column names in the output table.

Select the ‘JDBCConnectorToMeterItemDTO’ component, then click the OutputMap tab. The Output Map page opens. Use this page to map the source fields tothe target fields in the target database. Notice that the source fields include theadditional fields added to the input stream.

Propagating status to ETL job items:

All scripts must contain both an On Success and On Failure hook that populatesthe errCount and errMsg work attributes to report status back to TRIRIGA whenthe transform is run from an ETL job item.

About this task

The errCount work attribute is the number of errors encountered when theAssemblyLine file ran. The errMsg work attribute is the error message that iswritten to the TRIRIGA log.

Procedure

1. To add AssemblyLine hooks, in the editor area, click Options > AssemblyLineHooks.

2. Select the checkboxes next to the On Success and On Failure hooks and clickClose.

3. Modify the On Success hook to set the errCount and errMsg work attributes byusing the following code.// Set errCount and errMessage for ALif (task.getResult() == null) {

var result = system.newEntry();result.errCount = errCount;result.errMsg=’Assembly line completed successfully.’;task.setWork(result);

}else{

work.errCount = errCount;work.errMsg=’Assembly line completed successfully.’;

}

4. Modify the On Failure hook to set the errCount and errMsg work attributes byusing the following code.// Set errCount and errMessage for triDispatcher ALif (task.getResult() == null) {

var result = system.newEntry();result.errCount = 1;result.errMsg=’Assembly line failed.’;task.setWork(result);

}else


{work.errCount = 1;work.errMsg=’Assembly line failed.’;

}

Transform testing in Tivoli Directory Integrator:

Testing after each step makes debugging easier.

You need to save before running a transform; otherwise Tivoli Directory Integratorwill invoke the AssemblyLine without the changes you have make since the lastsave.

As you develop the AssemblyLine you can test it by either running to completionor by stepping through the components one by one. There are two buttons to runthe AssemblyLine. The Run in Console action starts the AssemblyLine and showsthe output in a console view. The Debugger action runs the AssemblyLine with thedebugger.

The process of starting an AssemblyLine goes through the following steps:1. If the AssemblyLine contains errors, such as missing output maps, you will be

prompted to confirm running the AssemblyLine with the followingmessage:This AssemblyLine has one or more errors in it. Proceed withrun?

2. The next check is whether the Tivoli Directory Integrator server is available. Ifthe server is unreachable you will see this message:Connection to serverDefault.tdiserver cannot be obtained.

3. Finally, Configuration Editor transfers the runtime configuration to the serverand waits for the AssemblyLine to be started. In this step you will see aprogress bar in the upper right part of the window. The toolbar button to stopthe AssemblyLine is also grayed out as it hasn't started yet. Once theAssemblyLine is running, the progress bar will be spinning and you shouldstart seeing messages in the log window. You can now stop the AssemblyLineby clicking the Stop action in the toolbar.

Moving ETL scripts into TRIRIGA from Tivoli Directory Integrator:

Once the transform is completed and tested, it must be uploaded to the TRIRIGAETL job item.

The following graphic describes the flow between the ETL environment andTRIRIGA.


DatabaseFile system

XML file


Save XMLOpen XML


the transforms

UploadDownload

Contentmanager


Contentmanager

Setting up the TRIRIGA database AssemblyLine connections:

Before you upload the runtime configuration file to a TRIRIGA ETL job itemrecord, you must configure the TRIRIGA database AssemblyLine connections withthe parameters to be passed to the Tivoli Directory Integrator assembly duringinitialization.

Procedure

1. Click Tools > System Setup > General > Application Settings.2. On the Environmental Settings tab, in the AssemblyLine Settings section,

configure the database AssemblyLine connections.a. Required: Configure DB-TRIRIGA as the connection to your TRIRIGA

database. If this connection is not configured, Tivoli Directory IntegratorETL job items will not run.

b. Optional: Configure DB-OTHER if your Tivoli Directory IntegratorAssemblyLines must connect to an external database. Because DB-OTHER isoptional, TRIRIGA does not verify that it is set. Therefore the AssemblyLinemust check that this connection is set before using it.

3. Click Test DB Connection to verify that the connection data is correct.

Retrieving the AssemblyLine parameters:

After the TRIRIGA database AssemblyLine connections have been configured,modify the AssemblyLine to retrieve the parameters.

Procedure

1. Open the Prolog script that initializes the AssemblyLine.2. Add the following statements to retrieve the TRIRIGA-DB connection

parameters.


var op = task.getOpEntry();var jdbcTriURL = op.getString("jdbcTriURL");var jdbcTriDriver = op.getString("jdbcTriDriver");var jdbcTriUser = op.getString("jdbcTriUser");var jdbcTriPassword = op.getString("jdbcTriPassword");

3. Add the following statements to retrieve the OTHER-DB connectionparameters.var op = task.getOpEntry();var jdbcOthURL = op.getString("jdbcOthURL");var jdbcOthDriver = op.getString("jdbcOthDriver");var jdbcOthUser = op.getString("jdbcOthUser");var jdbcOthPassword = op.getString("jdbcOthPassword");

4. Save the changes.

Moving ETL scripts into TRIRIGA:

After you have developed an ETL script in Tivoli Directory IntegratorConfiguration Editor, you must move it into TRIRIGA.

About this task

Tivoli Directory Integrator Configuration Editor automatically stores the runtimeconfiguration file in the following location: Workspace/ProjectName/Runtime-ProjectName/ProjectName.xml.

Procedure

1. Create an ETL job item record with the Job Item Type field set to TivoliDirectory Integrator Transformation.

2. Set the Assembly Line Name field to the name of the AssemblyLine that youwant to start after loading the configuration file.

3. Set the Transform File field by uploading the runtime config file that youcreated in Configuration Editor.

4. Optional: If your ETL transform requires any files as input, associate those filesto the ETL job item as resource files. For example, if your ETL transform mustprocess data from a spreadsheet file, you must associate that file as a resourcefile.a. In the Resource File section, click Add.b. Set the Resource Name to a name that is used to identify the resource file.

This name will be part of the temporary resource file name that TRIRIGAsends to the ETL transform. This is useful if you associate more than oneResource File to an ETL job item because it enables the ETL transform toidentify the multiple files that are sent to it by name.

c. To set the Resource File field, click the Upload Resource File icon next tothe field, specify the file location, and click OK to upload this file to theResource File field.

Variables passed to Tivoli Directory Integrator:

TRIRIGA passes several input variables to Tivoli Directory Integrator ETLs.

To use any of these variables in an AssemblyLine, access them via the TivoliDirectory Integrator getOpEntry() interface. For example, var op =task.getOpEntry(); var jdbcTriURL = op.getString("jdbcTriURL");


TRIRIGA extracts field values from the ETL job item record and passes them asinput parameters. The field types that are supported are Text, Boolean, Date, Dateand Time, Locators, and Numbers. Any ETL job item field of this type is passed asvariables.

If an ETL job item has the following fields, the variables in the 2nd table will bepassed to Tivoli Directory Integrator:

Table 6. ETL job item fields


triActiveEndDA Active End Date Date

triActiveStartDA Active Start Date Date

triControlNumberCN Control Number Control Number

triCreatedByTX Created By Text

triLocator triLocator Text

triNameTX Name Text

triTransformBI Transform File Binary

The variables that are passed to Tivoli Directory Integrator are as follows:

Table 7. Variables passed to Tivoli Directory Integrator

Variable passed to Tivoli DirectoryIntegrator Description

triNameTX (Text)

triActiveStartDA (Number) date in milliseconds since January1, 1970

triActiveStartDA_DATE (Date) wrapped if Oracle or DB2, time iswhatever it was on the attribute

triActiveStartDA_MinDATE (Date) wrapped if Oracle or DB2, time is00:00:00

triActiveStartDA_MaxDATE (Date) wrapped if Oracle or DB2, time is23:59:59

triActiveStartDA_Min (Number) date in milliseconds since January1, 1970, time is 00:00:00

triActiveStartDA_Max (Number) date in milliseconds since January1, 1970, time is 23:59:59


triActiveEndDA_DATE (Date) wrapped if Oracle or DB2, time iswhatever it was on the attribute

triActiveEndDA_MinDATE (Date) wrapped if Oracle or DB2, time is00:00:00

triActiveEndDA_MaxDATE (Date) wrapped if Oracle or DB2, time is23:59:59

triActiveEndDA_Min (Number) date in milliseconds since January1, 1970, time is 00:00:00

triActiveEndDA_Max (Number) date in milliseconds since January1, 1970, time is 23:59:59

triCreatedByTX (Text)


Table 7. Variables passed to Tivoli Directory Integrator (continued)


triRunDATE (Number) Run Date set by Customworkflow task

triLocator (Text – Locator) is a locator field thatcontains a reference to another businessobject. This variable contains the text valueof that record’s field

triLocator_IBS_SPEC (Text - Locator) contains the spec_id of therecord in the triLocator field. You can usethis spec_id to find information related thatrecord through other database tables

triAssemblyLineNameTX The name of the main AssemblyLine in theETL transform that you want to run

TRIRIGA also passes Resource File variables. A Resource File is a file that an ETLtransform requires as input. For example, if an ETL transform must process datafrom a Comma Separated Value (.csv) file, it needs to know where and how toreference that file when it runs.

If an ETL job item has two associated Resource File records, each includes thefollowing fields:

Table 8. Fields associated with Resource File records


triResourceNameTX Name that is used to identifythe resource file

Text

triResourceFileBI Contents of the resource file Binary

The variables that are passed to Tivoli Directory Integrator for these Resource Filerecords are as follows:

Table 9. Variables passed to Tivoli Directory Integrator


RESOURCE_1 Fully qualified file name of the resource file.File name contains value fromtriResourceNameTX field to help ETLtransform identify it

RESOURCE_2 Fully qualified file name of the otherresource file. File name contains value fromtriResourceNameTX field to help ETLtransform identify it

Table 10. TRIRIGA also passes JDBC input variables that are defined in the ApplicationSettings.


jdbcTriURL TRIRIGA database driver URL

jdbcTriDriver TRIRIGA database driver name


Table 10. TRIRIGA also passes JDBC input variables that are defined in the ApplicationSettings. (continued)


jdbcTriUser TRIRIGA database user name

jdbcTriPassword TRIRIGA database password

jdbcOthURL Other database driver URL

jdbcOthDriver Other database driver name

jdbcOthUser Other database user name

jdbcOthPassword Other data

Debugging ETL scripts in the application:

To debug ETL scripts in the application, you must first set up logging and thentrigger the RunETL Custom workflow task to view the log information.

Setting up logging:

TRIRIGA provides debugging capabilities when ETL scripts run in the TRIRIGAapplication.

Procedure

1. In the Administrator Console, select the Platform Logging managed object.Then select the option to turn on ETL logging.

2. Select Category ETL > Transforms > Run Transform to turn on debug loggingin the TRIRIGA platform code that processes ETL job items. Log messages areprinted to server.log.

3. Select Category ETL > Transforms > Tivoli Directory Integrator to turn ondebug logging in the Tivoli Directory Integrator AssemblyLines. Log messagesare printed to the AssemblyLine log. Each AssemblyLine has its own log file.

4. Apply the changes. Now when an ETL Script runs, ETL related informationwill be put into the server log or AssemblyLine log.

Important: Because of the large volume of information you may encounter in alog, set Tivoli Directory Integrator logging to debug for only one execution ofthe ETL job item.

Debugging using ETL jobs:

Once you have set up logging, you will need to trigger the RunETL Customworkflow task to see any information in the logs.

Procedure

If you are using the ETL Job Item, then you can simply click Run Process on thatform. Do not forget to fill the field values in the form that the ETL Script wouldexpect.

Note: Only use the Run Process action for debugging purposes. For production,use the Job Scheduler instead. Note that Run Process will update tables in thedatabase, so do not use this action in a production environment.


Example

The following shows a sample server log output:2014-03-27 13:18:10,427 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: getETLVarsFromFields...2014-03-27 13:18:10,431 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: getETLVarsFromFields2014-03-27 13:18:10,431 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: getETLVarsFromResourceFiles2014-03-27 13:18:10,432 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: getETLVarsFromResourceFiles2014-03-27 13:18:10,432 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Processing Job Item with Type = Tivoli Directory Integrator Transformation2014-03-27 13:18:10,432 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: transformRecordTDI2014-03-27 13:18:10,474 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.TDIRequest](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: init2014-03-27 13:18:10,474 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.TDIRequest](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: init2014-03-27 13:18:10,483 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189) ***ETL Variable = triIdTX : triLoadMeterData2014-03-27 13:18:10,483 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189) ***ETL Variable = triAssemblyLineNameTX : triLoadMeterData...2014-03-27 13:18:10,483 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)*** ETL Variable = triNameTX :Load Meter Item Staging Table2014-03-27 13:18:10,488 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.DataSourceConnectionInfoImpl] (WFA:221931 - 15290804triProcessManual:305676189 IE=305676189)Entry: init2014-03-27 13:18:10,495 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.DataSourceConnectionInfoImpl](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: init2014-03-27 13:18:10,495 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.DataSourceConnectionInfoImpl](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: init2014-03-27 13:18:10,496 DEBUG [com.tririga.platform.workflow.runtime.


taskhandler.ETL.DataSourceConnectionInfoImpl](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: init2014-03-27 13:18:10,497 INFO [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Setting TDI log level to Debug.2014-03-27 13:18:10,503 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.LogSettingsServiceImpl](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: getLogSettings2014-03-27 13:18:10,503 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.LogSettingsServiceImpl](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Found DailyRollingFileAppender.2014-03-27 13:18:10,503 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.LogSettingsServiceImpl](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: getLogSettings2014-03-27 13:18:10,503 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.TDIRequest](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Entry: send2014-03-27 13:18:14,396 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.TDIRequest](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: send2014-03-27 13:18:14,396 INFO [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)RunETL request returned fromTDI server version: 7.1.1.3 - 2013-12-06 running on host: i3650x3cr22014-03-27 13:18:14,396 INFO [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)RunETL: Prepare=2014/03/2713:18:10.475 Start=2014/03/27 13:18:10.503 Stop=2014/03/27 13:18:14.3962014-03-27 13:18:14,396 INFO [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)RunETL: Processing endedafter 3 seconds.2014-03-27 13:18:14,396 DEBUG [com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL](WFA:221931 - 15290804 triProcessManual:305676189 IE=305676189)Exit: transformRecordTDI

The following shows a sample AssemblyLine log:2014-03-27 13:18:11,062 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]CTGDIS967I AssemblyLine started by triLoadMeterData_1395951491025.2014-03-27 13:18:11,063 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]CTGDIS255I AssemblyLine AssemblyLines/triLoadMeterData is started.2014-03-27 13:18:11,073 DEBUG [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]CTGDIS089I Current statistics: Interval=0, Maximum Errors=0, Maximum Read=02014-03-27 13:18:11,073 DEBUG [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]CTGDIS069I Loading Connectors....2014-03-27 13:18:14,384 DEBUG [org.apache.log4j.DailyRollingFileAppender.


a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success Entry2014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success Processing Success2014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success -----------------------------------------------------------------------------

2014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success Processing Summary2014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success rowsProcessed = 3602014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success stagingTableWriteRowSuccess = 3602014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success stagingTableWriteRowFail = 02014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success rowsSkipped = 02014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success rowsNotValid = 02014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success dcJobsToReadyState = 02014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success createdDT = 13959514910882014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success finishedDT = 13959514943842014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success seconds = 32014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success secondsPerRecord = 0.012014-03-27 13:18:14,384 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success -----------------------------------------------------------------------------2014-03-27 13:18:14,386 DEBUG [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]triLoadMeterData - On Success Exit2014-03-27 13:18:14,386 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]CTGDIS116I Scripting hook of type onsuccess finished.2014-03-27 13:18:14,386 INFO [org.apache.log4j.DailyRollingFileAppender.a9e2d096-4cdc-4f87-a57b-5e31323093d9]CTGDIS080I Terminated successfully (0 errors).

Performance tuning tips:

Use the following information to improve performance.

When you are done getting your ETL to do what you want it to do, take a baselineperformance measurement.1. Using the Tivoli Directory Integrator Configuration Editor, run the ETL against

a database where you will have thousands of rows added to your fact table.


2. Make sure you are using a database connection and running the TivoliDirectory Integrator transformation on the network where the database lives soyou do not have network latency. Do not run it through a VPN.

3. Review the information logged for your run. For example, from a run of thetriLoadMeterData ETL, review the triLoadMeterData.log file.

Analysis of the run:1. Analyze the run. Is there a step that is slower than the others?2. Is the data input step slow? Should an index be added to the database? If so,

add an index and rerun. Is the performance better? Maybe a filter step shouldbe used instead of using the database to filter down the result set.

Tips for developing ETLs:v Avoid complex SQL and aggregate functions like COUNT, MIN, MAX, and

SUM. If you need to use these, see if an index will help out the Data Input step.Do not create an index on a field that is large varchar; SQL Server can onlyhandle indexes < 900 bytes.

v Avoid OR and NOT and using views (M_TableName in IBM TRIRIGAdatabases) if possible.

Running ETL transformsUse the TRIRIGA Job Scheduler to run the ETL job items and job groups that areused to move data into the TRIRIGA fact tables or flattened hierarchy tables.

ETL job items, job groups, and job schedulersETL job items define an ETL transformation script or rebuild hierarchy process thatis used to capture information from the TRIRIGA database, transform it, and loadit into fact tables. ETL job groups are simply collections of ETL job items. Jobschedulers define when ETL job items and job groups are to run. A job schedulemust be activated to run the jobs that are associated with it.

The TRIRIGA Workplace Performance Management analytic/reporting toolcalculates metrics that are used to generate reports and graphs by using queriesagainst fact tables that are grouped by the data in flat hierarchy tables. ETL jobitems are used by the Job Scheduler to trigger the workflows that extract data fromsource tables, such as TRIRIGA business object tables, and loading it into facttables. ETL job items also can be used to update flat hierarchies.

There are three types of ETL job items in TRIRIGA Workplace PerformanceManagement :

Kettle TransformationExtracts data from TRIRIGA business object tables and loads the data intoTRIRIGA Workplace Performance Management fact tables.

Tivoli Directory Integrator TransformationExtracts data from TRIRIGA business object tables or tables from externalsources and loads data into TRIRIGA Workplace Performance Managementfact tables.

Rebuild HierarchyExtracts data from TRIRIGA business object tables and loads the data intoTRIRIGA Workplace Performance Management flattened-hierarchy tables.


Creating or modifying ETL job itemsETL job items define the scripts that capture information from the TRIRIGAdatabase, transform it, and load it into fact tables or flattened hierarchy tables.

Procedure1. Click Tools > System Setup > Job Scheduling > ETL Job Item.2. Select an existing job item or click Add.3. In the General section, enter an ID for the ETL job item. Include the fact table

name in the ID field. The status is supplied by the TRIRIGA system when theETL job item is created.

4. Enter a name and description for the ETL job item. In the Details section, theJob Item Class field is set to ETL by the TRIRIGA system.

5. Select the Job Item Type.6. If the job item type is Rebuild Hierarchy, complete the following steps.

a. Enter the hierarchy module name. If specified, it must be a module with ahierarchy defined in TRIRIGA, such as Location. When this ETL job itemruns, all flat hierarchies for this module are rebuilt.

b. Enter the hierarchy name. When this ETL job item runs, the flat hierarchyfor this business object is rebuilt. If specified, the hierarchy name takesprecedence over the hierarchy module name.

c. The TRIRIGA system ignores information that is entered in the calendarperiod, fiscal period, and other dates in the Details section.

d. To rebuild all flat hierarchies of a specific module, specify the hierarchymodule name and leave hierarchy name blank.

e. To rebuild a single flat hierarchy, specify both the hierarchy module nameand hierarchy name.

f. If both the hierarchy module name and hierarchy name are blank, or eithercontains All, all flat hierarchies are rebuilt.

7. If the job item type is Kettle Transformation or TDI Transformation,complete the following steps.a. Specify the transform file by browsing for and selecting the file. The

TRIRIGA system expects the Kettle transform file to be in .ktl or .xmlformat and the Tivoli Directory Integrator transform file to be in .xmlformat.

b. Optional: After you upload the transform file, it can be viewed by clickingView Content.

8. When job item type is TDI Transformation, enter an assembly line name.9. When job item type is Kettle Transformation,

a. Enter the module names. If more than one name is specified, they must bedelimited with a comma.v Each module name is converted into a variable for the transform file in

the format ${Module.<moduleName>.ViewName} where <moduleName> isthe module name.

v Each variable’s value that is passed into the ETL is the name of theView for that module. This variable’s value can be used if the ETL mustknow the name of a specific Module's view.

b. Enter the business object names. If you specify more than one businessobject name, the names must be delimited with a comma.


v Each business object name is converted into a variable for the transformfile in the format ${BO.<boName>.TableName} where <boName> is thebusiness object name.

v A business object is not guaranteed to be unique across the databaseunless the module name is included. If you use a business object that isnot uniquely named, include the module name in the comma-separatedlist. Use the following syntax: <moduleName>::<boName>, where<moduleName> is the module name and <boName> is the business objectname.

v A variable is provided to the transform file as${BO.<moduleName>::<boName>.TableName}. Each variable’s value is thename of the table for that business object. This variable’s value can beused if the ETL must know the name of a specific Business Object'stable.

10. If the job item type is Kettle Transformation or TDI Transformation,complete the following steps.a. Typically, ETL job items are run under the control of one or more job

schedules.b. To unit test the ETL job item, set the date parameters in the Details section.c. The following date parameters depend on the ETL, some ETLs use the

information, some ETLs do not. The date parameters are overwritten whenan ETL job item is run by the Job Scheduler.v Select the Calendar Period to pass a variable that contains the calendar

period of the job.v Select the Fiscal Period to pass a variable that contains the fiscal period

of the job. The fiscal period is used by the Capture Period field in theTRIRIGA Workplace Performance Management fact tables.

v Select the Date to pass a variable that contains the date record of thejob. The date record is used to stamp the Date Dimension field in theTRIRIGA Workplace Performance Management fact tables.

v Select the Date to pass a variable that contains the date of the job. Entera date or click the Calendar icon and select a date.

v Select the Start Date to specify the date of the first data capture and topass a variable that contains the start date of the job. Enter a date orclick the Calendar icon and select a date.

v Select the End Date to specify the date of the last data capture and topass a variable that contains the end date of the job. Enter a date or clickthe Calendar icon and select a date.

11. The Metrics section summarizes logging data for this ETL job item. TheAverage Duration is calculated based on the Total Duration and the # ofRuns (Total Duration / # of Runs).

12. The Logs section shows the time and status from each time the ETL job itemis run. This data is summarized in the Metrics section.

13. Click Create Draft.14. Click Activate.

Results

The ETL job item record is created and ready to be included in a job group, a jobschedule, or both.


What to do next

For unit testing, click Run Process to trigger the Kettle or Tivoli DirectoryIntegrator transform or the Rebuild Hierarchy process that is specified in the ETLjob item.

Adding or modifying job groupsTo simplify job scheduling, use job groups to make collections of ETL job items tobe run on the same schedule.

Procedure1. Click Tools > System Setup > Job Scheduling > Job Group.2. Select an existing job group or click Add.3. In the Job Group form, enter a name and description for the job group.4. Add or remove ETL job items from the job group in the Job Items section by

using the Find or Remove actions.5. Optional: Adjust the sequence of the job items.6. Click Create.

Results

The job group record is available to include in a job schedule.

Creating or modifying job schedulersUse job schedulers to schedule when TRIRIGA runs ETL job items and job groups.You can schedule jobs to run on an hourly, daily, weekly, or monthly basis.

Before you begin

Although TRIRIGA includes predefined job schedulers, no jobs are scheduled untilyou revise the predefined job schedulers or create a new job scheduler with a startdate and end date and click Activate.

Procedure1. Click Tools > System Setup > Job Scheduling > Job Scheduler.2. Select an existing job scheduler or click Add.3. Enter a name and description for the job scheduler.4. In the Schedule section, select the Schedule Type frequency from the list

presented.v If you select the Daily schedule type, the Hourly and Every fields are

displayed. Click the Hourly checkbox to schedule jobs to run hourly, thenclick the Every field to specify the number of hours between each scheduledjob.

v If you select the Advanced schedule type, the Recurrence Pattern fielddisplays in the Schedule section. Click Recurrence Pattern to open the JobEvent form, which provides flexible options for scheduling jobs.

5. Optional: In the Schedule section, select the Run Historic Captures? check boxto indicate that historic data is included in the metrics that are calculated fromthe results of the activities in this job schedule. When this job schedule isactivated, this option instructs the job scheduler to generate and run jobs wherethe scheduled date is earlier than today. The parameters, such as start date, enddate, and fiscal period, for the time frame or period are passed into each jobitem to simulate a historic capture. However, since the process is being run


now, success is entirely dependent on how the ETL scripts use theseparameters. Running historic captures is an advanced option that requires acomplete understanding of how each script works.

6. Select the starting date for the first capture, the ending date for the last capture,and the capture lag from the end date of each capture, which is the amount oftime that the system waits to start the workflows that process the job. Eachdata capture period is calculated by using the start date and the schedule type.The system triggers a workflow to run the ETL job items immediately after thecapture lag from the end of each capture period. For example, if the start dateis 01/01/2014, the schedule type is monthly, and the capture lag is one day, thefollowing events are scheduled:

Table 11. Job runs scheduled from the beginning of the month

Job runs - Just aftermidnight on Captured data start date Captured data end date

02/01/2014 01/01/2014 01/31/2014

03/01/2014 02/01/2014 02/28/2014

04/01/2014 03/01/2014 03/31/2014

The End Date determines the last event to capture. Using the precedingexample, if 03/15/2014 were the End Date, the last event would be scheduledas follows:

Table 12. Job runs scheduled from the middle of the month

Job runs - Just aftermidnight on Captured data start date Captured data end date

03/16/2014 03/01/2014 03/15/2014

v When the Reset Capture Period? check box is selected, it forces the systemto ensure that the capture period is kept current every time a job runs.

v For example, if an activated job scheduler is configured with schedule typeof monthly, the system wakes up every month and runs the job items in therecord. During the wake-up, if the Job Scheduler’s reset capture period isselected, the system ensures that the capture period is set correctly based onthe wake-up date.

v When you specify job items, job groups, or both and activated this jobschedule record, the list of scheduled events for this Job Schedule shows inthe Scheduled Jobs section.

7. In the Job Items section, use the Find or Remove actions to select the ETL jobitems and job groups to include in the schedule and click OK. The job itemsrun in the order that is specified in the Sequence column.v When this job schedule is activated, the Metrics section summarizes logging

data for this job schedule.v The Average Duration is the calculated based on the Total Duration and the

Number of Runs (Total Duration / Number of Runs).v When this job schedule is activated, the Logs section shows the time and

status from each time this job schedule is run.8. Optional: Adjust the sequence of the job items.9. Click Create Draft and then click Activate.


Results

The TRIRIGA system creates a set of scheduled jobs that are based on the scheduletype and the start and end dates. These can be seen in the scheduled jobs sectionof the job schedule.

Customizing transform objectsTRIRIGA provides ETL job items and transform objects. Rather than defining anew transform object, you can customize an existing ETL job item transform object.If you use an existing transform object, then you must define or maintain thetransform. However, you do not need to define or maintain the business objects,forms, or workflow tasks, as they are already defined.

Defining transform business objects, forms, and workflowsYou can use the existing TRIRIGA ETL job item as an example, when you define anew transform object. Defining a new own transform object also requires that youdefine and maintain the business objects, forms, and workflows.

Before you begin

Create the source and destination tables (business objects) and establish thecorresponding mappings.

Procedure1. Create the transform business object.

a. Identify variables to be passed into the transform and add them to thetransform business object. For example, time period.

b. Ensure that there is a binary field for the transform XML.2. Create the transform form and provide a navigation item, or other method, to

show the transform form.3. Create the workflow that calls the ETL transform custom workflow task.

a. Set up the workflow to run on a schedule.b. Iterate through all the transform business object records that must be run,

calling the custom workflow task for each one.

Saving transform XML into the Content ManagerAfter you define the transform XML, you can save it in the file system and uploadit into the TRIRIGA Content Manager. The transform XML can then be tested byusing an ETL development environment.

Procedure1. Open the navigation item for your transform business object. Edit an existing

transform business object or add a new one.2. Use the binary field to upload the transform XML into the TRIRIGA Content

Manager.3. Update other fields, if necessary.4. Save the transform record.

Configuring workflow run timeAfter you upload the transform XML into the TRIRIGA Content Manager, you canand set up the workflow to run on a schedule. The workflow iterates through allthe transform business object records and calls the custom workflow task for eachrecord.


Procedure1. The workflow gets records for the transform business object.2. The workflow determines the records to be sent to the custom workflow task.3. The workflow iterates through calling custom workflow task for each record.

The class name must becom.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL.

4. The custom workflow task:a. Loads the transform XML from the Content Manager into a temporary file.b. Gathers all the fields on the business object and creates a variable to hand

to the ETL tool. Special handling is needed for date/date and time formats.c. Creates the ETL environment.d. Sets the TRIRIGA connection to the local application server JNDI.e. Runs the transform by using the ETL API.f. Returns false if an error occurs during processing, otherwise return true to

the workflow.

Running an ETL custom workflow task specificationThe workflow iterates through all the transform business object records and callsthe custom workflow task for each record. The custom workflow task includes adefined specification.

When the custom workflow task is called for each transform business object, thefields on it are processed as follows:1. The triTransformBI field is required and holds the reference to the transform

XML file that you want to run.2. The triBONamesTX field, if present, is parsed as a comma-separated list of

business object names. The custom workflow task creates variables of the form${BO.<boName>.TableName}. For example, if the field contains triBuilding, thereis a ${BO.triBuilding.TableName} variable available in the ETL script. Thisvariable contains the actual database table name that stores triBuilding records.Since business object names might not be unique, you have the option ofspecifying the module by using the form <moduleName>::<boName>, whichresults in a corresponding ${BO.<moduleName>::<boName>.TableName} variable.For example, Location::triBuilding is available as the variable${BO.Location::triBuilding.TableName} in the ETL script.

3. The triModuleNamesTX field, if present, is parsed as a comma-separated list ofmodule names. The custom workflow task creates variables of the form${Module.<moduleName>.ViewName}.


Chapter 3. Metrics

A metric is an operational objective that you want to measure. All TRIRIGAmetrics use the same metric technology and data but different metrics are intendedfor different purposes.

Metrics can be divided into the following two purposes:

Performance metricsMetrics that measure process performance to identify actions that can betaken for improvement. Typically, the measures are ratios, percentages, orscores. Performance metrics have targets, thresholds, action conditions,accountability, and action task functions.

Analysis and reporting metricsMetrics that are information for general reporting or further analysis of arelated performance metric. This information is useful for dimensionalanalysis and navigation, so it uses the metric capabilities of theperformance management application. Analysis and reporting metrics donot have targets, thresholds, action conditions, accountability, and actiontasks. The key metrics values for the Results, Target, and Status fields areblank for this metric type.

For more information, see the IBM TRIRIGA 10 Workplace Performance ManagementUser Guide.

Metrics reportsMetric reports use the dimensions and fact fields that are defined in the fact tablesto represent a specific metric calculation. Metric reports show aggregated valuesfor a single metric.

Collections of metric reports for each user’s role appear in the portal Home pageand in the Performance Manager.

Do not edit or modify the as-delivered metric reports as you customize TRIRIGAsoftware to your company’s environment. Instead, create a derivative metric reportby copying an existing metric report, renaming it, and editing it, or by creating anentirely new metric report. View the metric reports at My Reports > SystemReports with the Module filter set to triMetricFact, the Display Type filter set toMetric, and the Name filter set to Metric. To view the metric tabular reports thatare related to metric graphic reports, set the Name filter to Related Reports.

Each metric report consists of the following elements:

Drill pathsA special reporting feature that takes advantage of hierarchical dimensions,providing report users with the ability to move up and down a hierarchy.

Filters Provide report users with the ability to change or filter the data that ispresented in a report.

Metric calculationThe metric, which is the main focus of the report.


Related reportsDisplay more, possibly non-metric, data for a metric report.

For more information, see the IBM TRIRIGA Application Platform 3 Reporting UserGuide.

Key metricsThe Scorecard portal includes Key Metrics sections that collect multiple metricsinto a single view.

Do not edit or modify the as-delivered key metrics as you customize TRIRIGA foryour organization. Rather, create derivative key metrics by copying an as-deliveredkey metric, renaming it, and editing it. You can also create an entirely new keymetrics portal section.

The process of using the Scorecard Builder, Portal Builder, and Navigation Builderto set up a portal is documented in the IBM TRIRIGA Application Platform 3 UserExperience User Guide.

Tips:

v In the Queries tab of the Scorecard form, include all queries that are to appearin this Key Metrics portal section in the Selected Queries section.

v When you put the Scorecard in a navigation item, specify the Default Report.v The response time that users experience while they moving around in their

Home portal and their Performance Manager can vary. The response time isdirectly related to the number of metrics that are included in the Scorecard andthe amount of data behind each metric. The more precise the filters, the moreperformance improves.

Form metricsAn alternative method to displaying metrics is by using a form view.

Displaying metrics in a form is accomplished by defining a query section in aform, where the query referenced is a metric query. The data that the metric queryselects can be filtered to display based on the parent record that the form isdisplayed in. Related switching of reports allows the exchanging of queries that arebased on user actions.

At design time, define a metric query with a $$RECORDID$$ and$$PARENT::[Section]:: [Field]$$ filter. At runtime, the parent record the form isdisplayed in implicitly filters the data that the Metric Query Engine selects todisplay.

You can define other metric queries as related reports. At runtime, when the metricquery is displayed within a form-related report, switching control enables the userto exchange the displayed query.

Data filteringBefore metrics are displayed in a form, the data is filtered.

At runtime, before the query is run,


v The $$RECORDID$$ filter is replaced by the record ID of the parent record that theform is being displayed within.

v A $$PARENT::[Section]::[Field]$$ filter is resolved to the defined parent recordfield’s value.

v If a metric query is displayed outside of a parent record, any parent-sensitivefilters, such as $$RECORDID$$ and $$PARENT::[Section]::[Field]$$ are ignored.

Define $$RECORDID$$ and $$PARENT::[Section]::[Field]$$ filters against fieldsthat are defined as a drill path in the fact table. The metric that is filtered for aprogram or a classification, and that uses these filters, acts similar to selecting fromthe drill path list. It also filters for the specified record and includes all children ofthat record.

When a $$PARENT::[Section]::[Field]$$ filter value is specified on a hierarchicalfilter, a null parent field is the equivalent of choosing the root node of the drillpath. It also includes all of the records that match the root value. Anon-hierarchical filter behaves much like a business object query filter and filtersfor records with null value when the parent field value is null.

$$RECORDID$$ and $$PARENT::[Section]::[Field]$$ filters behave as if they areruntime filters except that their value is passed from the parent record.

If a metric query has $$RUNTIME$$ and $$PARENT::[Section]::[Field]$$ filters thatare defined against the same field, when the query is displayed inside a form, the$$RUNTIME$$ filter control is not used. Instead, the value fromthe$$PARENT::[Section]::[Field]$$ is used as the filter value.

Sub reportsForm metrics can include both tabular and graphical sub reports.

A tabular sub report defines a Performance Manager’s related report. Anon-tabular sub report represents a metric graph that can be switched within theform.

Both tabular and graphical reports can be added to the Sub Reports section of ametric query. At runtime, a Performance Manager displays only tabular reports asRelated Reports, and a Metric in a form query section has only graphical reports asoptions in the Sub Report section swap controls.

The type of the sub report (for example, metric, query, or report) displays in theSub Reports section of the Report Builder. For metric queries, the Tabular Outputflag is available.

A metric query can display other metric queries as sub reports.

Chapter 3. Metrics 63

Chapter 4. Hierarchy flattener

The definition of a hierarchy in the TRIRIGA Application Platform providesflexibility for implementations with multiple permutations of data hierarchies, forexample, organizations. However, reporting tools prefer a more structured datasummary to simplify reporting and maximize performance.

The purpose of the hierarchy flattener tool is for an administrator to define a set ofnamed structures that are used by the TRIRIGA Metric Reporting engine to quicklyprocess the hierarchical data within the system. The resulting structure is referredto as a flat hierarchy and these structures are used in a metric reports.

Administrators use the job scheduler, with the help from ETL job items, to run thehierarchy flattener process. System developers trigger the process by using acustom workflow task. In this case, the class name that is used to run the hierarchyflattener process iscom.tririga.platform.workflow.runtime.taskhandler.flathierarchy.RebuildFlatHierarchies

Triggering the process by using a custom workflow task is discussed in ApplicationBuilding for the IBM TRIRIGA Application Platform 3.

Important: Whenever a hierarchy in TRIRIGA is changed, the hierarchy tree ispromptly updated. For example, suppose that you add a new triBuilding, then theLocation hierarchy is updated. However, the corresponding flat hierarchy fortriBuilding is not updated until you rebuild it with a Rebuild Hierarchy Job Item.Therefore, it is important to schedule rebuild hierarchy job items at the same timeas when you plan to capture TRIRIGA Workplace Performance Management datathrough fact tables. Rebuild hierarchy job items ensure that you keep informationcurrent.

Flat hierarchiesHierarchy structure definitions depend on what the flattening is based on. The flathierarchy can be based on the standard parent-child relationships for a specifiedmodule’s hierarchy. The flat hierarchy can also be based on specific levels in themodule’s hierarchy, and their respective business objects.

Each hierarchy structure definition contains a single header record that identifieshierarchy name, module, and hierarchy type. The hierarchy name describes thehierarchy and the module is the module that the hierarchy represents. Thehierarchy type is used by the flattening process to understand how to flatten thedata. There are two hierarchy types, Data and Form.

A data hierarchy is used to flatten the path of data based on the standardparent-child relationships for the specified module’s hierarchy. This hierarchy typehas no named levels because TRIRIGA Application Platform applications allowdifferent types of data to be represented at the same physical level in a module’shierarchy. For example, a location hierarchy might have data for both property,building and floor, and for building and floor. Thus the first level in the hierarchywould contain a mixture of properties and buildings, and the second level wouldcontain a mix of buildings and floors.


A form hierarchy is used to flatten the path of data based on the parent-childrelationships for the specified module’s hierarchy and the business objects thatrepresent levels. Only one business object can represent each level.

Each form hierarchy must specify explicit levels that contain the level number, thebusiness object that the level represents, and the type. The type is used by theflattening process to understand how to find the data for the level. The type hasthree options: Find, Ignore, and Recurse.v When the type value is Find, the system searches through the sublevels of the

instance data for a particular thread until a record is found for the specifiedform. If no records are found, the remaining levels in the hierarchy definition areignored and no more flat data is created for that thread. If a record is found, thesystem creates a flat data record for that node and proceeds to the next level inthe definition. This mode provides the capability to collapse a tree to better alignyour business data.

v When the type value is Ignore, the system searches for the specified form, onelevel below the last parent. If a record is not found, the system creates a gap forthis level and proceeds with the next level in the definition. If a record is found,the system creates a flat data record for that node and proceeds to the next levelin the definition. This mode provides the capability to expand a tree to betteralign your business data. To facilitate the reporting process, the gaps must begiven a name or label. Use the Gap Label value in the Hierarchy StructureManager for this purpose.

v When the type value is Recurse, the system searches through the sublevels ofthe instance data for a particular thread until a record is found for the specifiedform. If no records are found, the remaining levels in the hierarchy definition areignored and no more flat data is created for that thread. For each record found,the system creates a flat data record for that node before it proceeds to the nextlevel in the definition.

Examples of flat hierarchiesYou can reference flat hierarchy examples to better understand the structure of aflat hierarchy definition.

Sample flat hierarchy header records

The following table shows examples of flat hierarchies that are based on moduleswithin hierarchies:

Table 13. Sample header records

Hierarchy name Module Hierarchy type

Space Hierarchy Location GUI

Land Hierarchy Location GUI

City Hierarchy Geography GUI

Full Location Hierarchy Location Data

Full Organization Hierarchy Organization Data

Internal OrganizationHierarchy

Organization GUI

External OrganizationHierarchy

Organization GUI


Sample flat hierarchy level records

The following table shows examples of flat hierarchies that are based on levelswithin hierarchies:

Table 14. Sample flat hierarchy level records

Hierarchy name Level number Form Find module

Space Hierarchy 1 Property Ignore

Space Hierarchy 2 Building Find

Space Hierarchy 3 Floor Find

Space Hierarchy 4 Space Recurse


1 Company Find


2 Division Ignore


3 Department Recurse

Hierarchy structure managerYou can define hierarchies and level information by using the Hierarchy StructureManager. The Hierarchy Structure Manager provides a single interface for creating,updating, and deleting flat hierarchies.

Accessing hierarchy structuresTo add, modify, or delete hierarchies, access the hierarchy structures functionality.

Procedure1. Click Tools > Builder Tools > Data Modeler.2. Click Utilities.3. Click Hierarchy Structures.

Creating a data hierarchyA data hierarchy is used to flatten the path of data based on the standardparent-child relationships for the specified module’s hierarchy. When you create adata hierarchy, named levels are not required as different types of data can berepresented at the same physical level in a module’s hierarchy.

Procedure1. Click Create Hierarchy.2. In the Name field, enter a name to describe what the hierarchy represents.3. From the Module list, select the relevant module for the data hierarchy.4. From the Hierarchy Type list, select Data.5. Click Create.6. Click Save, then click Close.

Chapter 4. Hierarchy flattener 67

Creating a form hierarchyA form hierarchy is used to flatten the path of data based on the parent-childrelationships for the specified module’s hierarchy and the business objects thatrepresent levels. When you create a form hierarchy, only one business object canrepresent each level.

Procedure1. Click Create Hierarchy.2. In the Name field, enter a name to describe what the hierarchy represents.3. From the Module list, select the relevant module for the form hierarchy.4. From the Hierarchy Type list, select Form.5. Click Create. The Levels section displays. Enter information for the level 1

form.6. From the Business Object list, select the relevant business object.7. From the Form list, select the relevant form.8. From the Type list, select Find. The Gap Label is the label that is specified

when Ignore is selected from the Type list and a record is not found.9. Click Save.

10. Continue entering and saving information until all levels are defined.11. Click Save, then click Close.


Chapter 5. Fact tables

Fact tables consist of the measurements, metrics, or facts of a business process. Facttables store the data that is used to calculate the metrics in metric reports.

Fact table information is based on the as-delivered TRIRIGA WorkplacePerformance Management and TRIRIGA Real Estate Environmental Sustainabilityproducts. The implementation at your company might be different.

Each fact table has an ETL to load data and another to clear data. The names of theETLs that clear data end with – Clear, for example Building Cost Fact – Clear. Toview the ETLs, click Tools > System Setup > ETL Job Items.

List of fact tables and metrics supportedYou can reference the list of fact tables and metrics that are supported in theTRIRIGA implementation at your company.

Accessing fact tables

Click Tools > Builder Tools > Data Modeler.

Locate triMetricFact and select it to reveal the list of fact table business objects.

Accessing metrics

Click Tools > Builder Tools > Report Manager > System Reports.

Filter by Business Object and Module to sort the metric information and obtainthe relevant list of reports.

Tip: #FM# means that a metric is also a form metric in the system.

Facts that require special staging tables and ETLsFor most fact tables, the process to load the stored data to calculate metrics issimple. However, some fact tables require special staging tables, and ETLs to assistwith the loading process.

The following table shows the facts that require special staging tables and ETLs:

Table 15. Facts that require special staging tables and ETLs

Fact table name Fact table business object

Financial Summary triFinancialSummary

Standard Hours triStandardHours

Standard Hours Details triStandardHoursDetails

Asset Analytic Hourly Fact triAssetAnalyticHFact

Asset Energy Use Daily Fact triAssetEnergyUseDFact

Asset Energy Use Hourly Fact triAssetEnergyUseHFact

Asset Energy Use Monthly Fact triAssetEnergyUseMFact


Dependent ETLsSome ETLs depend on other ETLs to assist with the loading process.

The following table shows the ETLs that depend on other ETLs:

Table 16. Dependent ETLs


Building Cost Fact Load ETL This ETL depends on the availability of datain the Financial Summary Table. TheFinancial Summary Table can be loadedeither by backend integration with yourfinancial system or by using the OfflineFinancial Summary Excel process. ToFacilitate the Offline Financial SummaryExcel process, there is a special ETL to pushthe data from the Excel/Offline process tothe Financial Summary Table. In theas-delivered TRIRIGA WorkplacePerformance Management, the special ETLsare named Load Financial Summary FromOffline Staging and Clear FinancialSummary From Offline Staging. If you areimporting financial summary data with theOffline Financial Summary Excel process,you must first run the Load FinancialSummary From Offline Staging ETL. Youmust then run the Building Cost Fact LoadETL.

Building Fact Load ETL This ETL depends on the availability of datain the Financial Summary Table. TheFinancial Summary Table can be loadedeither by backend integration with yourfinancial system or by using the OfflineFinancial Summary Excel process. ToFacilitate the Offline Financial SummaryExcel process, there is a special ETL to pushthe data from the Excel/Offline process tothe Financial Summary Table. In theas-delivered TRIRIGA WorkplacePerformance Management, the special ETLsare named Load Financial Summary FromOffline Staging and Clear FinancialSummary From Offline Staging. If you areimporting financial summary data with theOffline Financial Summary Excel process,you must first run the Load FinancialSummary From Offline Staging ETL. Youmust then run the Building Fact Load ETL.

Resource Fact Load ETL Dependent on Standard Hours Load ETL.

Standard Hours Load ETL Dependent on Standard Hours Details LoadETL.


Table 16. Dependent ETLs (continued)


Asset Daily Fact ETL

Asset Hourly Fact ETL

Asset Monthly Fact ETL

These ETLs depend on the availability ofdata in a staging table. The staging tabletypes must be generic. The staging tablemust include specific fields. The stagingtable can be loaded by back-end integrationwith the building management system. Thestaging table can also be loaded by using anETL to bring the data in from an externaldatabase.See the Integrated Service ManagementLibrary for more details including, samplestaging tables and sample ETLs.

Chapter 5. Fact tables 71

Notices

This information was developed for products and services offered in the US. Thismaterial might be available from IBM in other languages. However, you may berequired to own a copy of the product or product version in that language in orderto access it.

IBM may not offer the products, services, or features discussed in this document inother countries. Consult your local IBM representative for information on theproducts and services currently available in your area. Any reference to an IBMproduct, program, or service is not intended to state or imply that only that IBMproduct, program, or service may be used. Any functionally equivalent product,program, or service that does not infringe any IBM intellectual property right maybe used instead. However, it is the user's responsibility to evaluate and verify theoperation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matterdescribed in this document. The furnishing of this document does not grant youany license to these patents. You can send license inquiries, in writing, to:

IBM Director of LicensingIBM CorporationNorth Castle Drive, MD-NC119Armonk, NY 10504-1785US

For license inquiries regarding double-byte character set (DBCS) information,contact the IBM Intellectual Property Department in your country or sendinquiries, in writing, to:

Intellectual Property LicensingLegal and Intellectual Property LawIBM Japan Ltd.19-21, Nihonbashi-Hakozakicho, Chuo-kuTokyo 103-8510, Japan

INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THISPUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHEREXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIEDWARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESSFOR A PARTICULAR PURPOSE. Some jurisdictions do not allow disclaimer ofexpress or implied warranties in certain transactions, therefore, this statement maynot apply to you.

This information could include technical inaccuracies or typographical errors.Changes are periodically made to the information herein; these changes will beincorporated in new editions of the publication. IBM may make improvementsand/or changes in the product(s) and/or the program(s) described in thispublication at any time without notice.

Any references in this information to non-IBM websites are provided forconvenience only and do not in any manner serve as an endorsement of those


websites. The materials at those websites are not part of the materials for this IBMproduct and use of those websites is at your own risk.

IBM may use or distribute any of the information you provide in any way itbelieves appropriate without incurring any obligation to you.

Licensees of this program who wish to have information about it for the purposeof enabling: (i) the exchange of information between independently createdprograms and other programs (including this one) and (ii) the mutual use of theinformation which has been exchanged, should contact:

IBM Director of LicensingIBM CorporationNorth Castle Drive, MD-NC119Armonk, NY 10504-1785US

Such information may be available, subject to appropriate terms and conditions,including in some cases, payment of a fee.

The licensed program described in this document and all licensed materialavailable for it are provided by IBM under terms of the IBM Customer Agreement,IBM International Program License Agreement or any equivalent agreementbetween us.

The performance data and client examples cited are presented for illustrativepurposes only. Actual performance results may vary depending on specificconfigurations and operating conditions.

Information concerning non-IBM products was obtained from the suppliers ofthose products, their published announcements or other publicly available sources.IBM has not tested those products and cannot confirm the accuracy ofperformance, compatibility or any other claims related to non-IBM products.Questions on the capabilities of non-IBM products should be addressed to thesuppliers of those products.

Statements regarding IBM's future direction or intent are subject to change orwithdrawal without notice, and represent goals and objectives only.

This information contains examples of data and reports used in daily businessoperations. To illustrate them as completely as possible, the examples include thenames of individuals, companies, brands, and products. All of these names arefictitious and any similarity to actual people or business enterprises is entirelycoincidental.

COPYRIGHT LICENSE:

This information contains sample application programs in source language, whichillustrate programming techniques on various operating platforms. You may copy,modify, and distribute these sample programs in any form without payment toIBM, for the purposes of developing, using, marketing or distributing applicationprograms conforming to the application programming interface for the operatingplatform for which the sample programs are written. These examples have notbeen thoroughly tested under all conditions. IBM, therefore, cannot guarantee orimply reliability, serviceability, or function of these programs. The sample


programs are provided "AS IS", without warranty of any kind. IBM shall not beliable for any damages arising out of your use of the sample programs.

Each copy or any portion of these sample programs or any derivative work must include a copyright notice as follows:© (your company name) (year).Portions of this code are derived from IBM Corp. Sample Programs.© Copyright IBM Corp. _enter the year or years_.

TrademarksIBM, the IBM logo, and ibm.com are trademarks or registered trademarks ofInternational Business Machines Corp., registered in many jurisdictions worldwide.Other product and service names might be trademarks of IBM or other companies.A current list of IBM trademarks is available on the web at "Copyright andtrademark information" at www.ibm.com/legal/copytrade.shtml.

Java™ and all Java-based trademarks and logos are trademarks or registeredtrademarks of Oracle and/or its affiliates.

Linux is a trademark of Linus Torvalds in the United States, other countries, orboth.

Microsoft, Windows, Windows NT, and the Windows logo are trademarks ofMicrosoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and othercountries.

Other product and service names might be trademarks of IBM or other companies.

Terms and conditions for product documentationPermissions for the use of these publications are granted subject to the followingterms and conditions.

Applicability

These terms and conditions are in addition to any terms of use for the IBMwebsite.

Personal use

You may reproduce these publications for your personal, noncommercial useprovided that all proprietary notices are preserved. You may not distribute, displayor make derivative work of these publications, or any portion thereof, without theexpress consent of IBM.

Commercial use

You may reproduce, distribute and display these publications solely within yourenterprise provided that all proprietary notices are preserved. You may not makederivative works of these publications, or reproduce, distribute or display thesepublications or any portion thereof outside your enterprise, without the expressconsent of IBM.

Notices 75

http://www.ibm.com/legal/us/en/copytrade.shtml

Rights

Except as expressly granted in this permission, no other permissions, licenses orrights are granted, either express or implied, to the publications or anyinformation, data, software or other intellectual property contained therein.

IBM reserves the right to withdraw the permissions granted herein whenever, in itsdiscretion, the use of the publications is detrimental to its interest or, asdetermined by IBM, the above instructions are not being properly followed.

You may not download, export or re-export this information except in fullcompliance with all applicable laws and regulations, including all United Statesexport laws and regulations.

IBM MAKES NO GUARANTEE ABOUT THE CONTENT OF THESEPUBLICATIONS. THE PUBLICATIONS ARE PROVIDED "AS-IS" AND WITHOUTWARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDINGBUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY,NON-INFRINGEMENT, AND FITNESS FOR A PARTICULAR PURPOSE.

IBM Online Privacy StatementIBM Software products, including software as a service solutions, (“SoftwareOfferings”) may use cookies or other technologies to collect product usageinformation, to help improve the end user experience, to tailor interactions withthe end user, or for other purposes. In many cases no personally identifiableinformation is collected by the Software Offerings. Some of our Software Offeringscan help enable you to collect personally identifiable information. If this SoftwareOffering uses cookies to collect personally identifiable information, specificinformation about this offering’s use of cookies is set forth below.

This Software Offering does not use cookies or other technologies to collectpersonally identifiable information.

If the configurations deployed for this Software Offering provide you as customerthe ability to collect personally identifiable information from end users via cookiesand other technologies, you should seek your own legal advice about any lawsapplicable to such data collection, including any requirements for notice andconsent.

For more information about the use of various technologies, including cookies, forthese purposes, see IBM’s Privacy Policy at http://www.ibm.com/privacy andIBM's Online Privacy Statement at http://www.ibm.com/privacy/details in thesection entitled “Cookies, Web Beacons and Other Technologies,” and the "IBMSoftware Products and Software-as-a-Service Privacy Statement" athttp://www.ibm.com/software/info/product-privacy/.


http://www.ibm.com/privacy

http://www.ibm.com/privacy/details

http://www.ibm.com/software/info/product-privacy/

Notices 77

IBM®

Printed in USA

with ibm corp....using etls with pentaho spoon .... . 15 using etls with ibm t ivoli dir ectory...

Documents