Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management

Data Mapping Guide

Release 2.5.2.0.1

E49014-07

June 2016

Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management Data Mapping Guide

E49014-07

Copyright © 2016 Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This guide provides the data mapping information from the Oracle Utilities Work and Asset Management source system to the Oracle Utilities Extractors and Schema target product.

Audience

The guide is intended for all implementers of Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management.

Related Documents

For more information, refer to the following documents:

•

Oracle Utilities Analytics Release Notes

•

Oracle Utilities Analytics Getting Started Guide

•

Oracle Utilities Analytics Quick Install Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Analytics Developer’s Guide

See Also :

•

Oracle Utilities Work and Asset Management Documentation Library

Notational Conventions

The following notational conventions are used in this document:

Notation

Indicates

boldface

Graphical user interface elements associated with an action, terms defined in text, or terms defines in the glossary

italic

Book titles, emphasis, or placeholder variables for which you supply particular values

monospace

Commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter

Overview

This guide provides the data mapping information from the Oracle Utilities Work and Asset Management source system to the Oracle Utilities Extractors and Schema target product. The guide describes the data mapping between the source system and the target, and the rules of data transformation for Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management.

Terminologies

The following terminologies are used for the data maps contained in this document:

<Presentation Table Name>

The Presentation Table Name lists the default name of the object in OBIEE when no customer modifications have been made to the name of the table. This is the default label seen in answers.

Properties

The Properties table lists properties of the table independent of each field. The following properties are listed in the table.

Property

Value

Load Table Name

Name of the data warehouse table that the extract file will be loaded into

Table Type

Fact or Dimension

Source System Driver Table

Name of the table in source database from which data is extracted

Source System Extract Program

Name of the program that creates the extract file

SCD Type

Type 1 - Existing records are updated directly

Type 2 - Existing records are marked inactive and new records are inserted from the staging file

Fact Type

Whether this is a snapshot or transactional fact table

Stage Table Name

Name of the table in the BI target database that can be used to query the data records from the staging file

Stage File Name

Operating system file name that will contain the data records to be loaded into the table. The filename will end in '.DAT'.

Control Table Name

Name of the table in the Oracle database that can be used to query the record from the control file

Control File Name

Name of the operating system file that is used as the control file in the extraction. The filename will end in '.CTL'.

Control file stores the record count and batch control information. It is used in load validation.

Update Procedure Name

The name of an Oracle procedure that will be used run prior to loading records from the staging data file. Used by Type 2 dimensions to update the Effective End Date value for records that exist in the staging file.

OWB Map Name

Name of the mapping that loads records from the staging file into the database table

OWB Work Flow Name

Name of the process flow that will process the next available staging file and load the records in there into the database table

OWB Work Flow Package Name

Name of the process flow package that contains the process flow

Extract Procedure

Name of the extract program that creates the extract files

Modify View Name

Name of the view used to find records that were added or updated in the database

Delete View Name

Name of the view used to find records that were deleted from the database

Fields

The Fields table lists the individual properties of each field in the Presentation Table or the Database Table. The following fields are listed in the tables:

Property

Value

Extract Field

Name of the field in the staging file that stores this data

Length

Length of the extract field in the staging file

Source

Field from source application or stage table or calculation is used to populate the extract field. If blank, then there is no default population of the field in the WAM extracts.

If the field is from the source system driver table, then only the field name is mentioned. If the field is from the edge application, then it is prefixed by the edge application table name.

Column

Name of the column in the database table. If blank, then the field is not present in the database table, but is only available from OBIEE.

OBIEE Field

Name of the field in the OBIEE Presentation folder. If blank, then the field is not available by default in OBIEE.

Load

How the data is populated. If the Column field is entered, then this is how the data is loaded in OWB. If the column field is empty, then this contains the calculation in OBIEE that is used by the column.

Data Maps for Oracle Utilities Work and Asset Management

This section contains data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management data:

•

Dimension Extract Programs

•

Fact Extract Programs

•

Dimension Table Schema

•

Fact Table Schema

Dimension Extract Programs

Asset Dimension

Properties

Property

Value

Load Table Name

CD_ASSET

Table Type

Dimension

Source System Driver Table

SA_ASSET

Source System Extract Program

EXTDASSE

Stage Table Name

STG_ASSET_EXT

Stage File Name

D_ASSET_EXT

Control Table Name

STG_ASSET_CTL_EXT

Control File name

D_ASSET_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_ASSET

OWB Work Flow Name

SPLWF_D_ASSET

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDASSE

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

ASSET_ID

20

PLANT+ASSET_ID+ASSET_RECORD_TYPE

ASSET_CD

20

PLANT+ASSET_ID+ASSET_RECORD_TYPE

ASSET_DESCR

60

ASSET_DESC

ASSET_INFO

254

ASSET_DESC

UDF1_CD

30

ASSET_CLASS

UDF1_DESCR

60

SA_ASSET_CLASS.ASSET_CLASS_DESCRIPTION

UDF2_CD

30

CRITICALITY

UDF2_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 40)

UDF3_CD

30

BUILDING

UDF3_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 25)

UDF4_CD

30

LOCATION

UDF4_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 231)

UDF5_CD

30

PROCESS_NO

UDF5_DESCR

60

ASSET_DESC

UDF6_CD

30

ASSET_RECORD

UDF6_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 239)

UDF7_CD

30

PLANT

UDF7_DESCR

60

SA_PLANT.PLANT_DESC

UDF8_CD

30

SA_PLANT.ORGANIZATION

UDF8_DESCR

60

SA_ORGANIZATION.ORGANIZATION_DESC

UDF9_CD

30

SA_PLANT.COMPANY

UDF9_DESCR

60

SA_COMPANY.COMPANY_DESC

UDF10_CD

30

UDF10_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

The base product uses the following fields to populate UDFs on the dimension:

•

ASSET_CLASS

•

CRITICALITY

•

BUILDING

•

LOCATION

•

PROCESS_NO

•

ASSET_RECORD_TYPE

Crew Dimension

Properties

Property

Value

Load Table Name

CD_CREW

Table Type

Dimension

Source System Driver Table

SA_CREW

Source System Extract Program

EXTDASSE

Stage Table Name

STG_CREW_EXT

Stage File Name

D_CREW_EXT

Control Table Name

STG_CREW_CTL_EXT

Control File name

D_CREW_EXT

Update Procedure Name

SPL_CREW_UPD_PRC

OWB Map Name

SPLMAP_D_CREW

OWB Work Flow Name

SPLWF_D_CREW

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDASSE

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

CREW_CD

30

PLANT+CREW

CREW_DESCR

60

CREW_DESC

SRC_CREW_ID

30

CREW_ID

CREW_TYPE_CD

30

CREW_TYPE_CD

CREW_TYPE_DESCR

100

CREW_TYPE_DESCR

ORG_UNIT1_CD

4

ORG_UNIT1_CD

ORG_UNIT1_DESCR

100

ORG_UNIT1_DESCR

ORG_UNIT2_CD

4

ORG_UNIT2_CD

ORG_UNIT2_DESCR

100

ORG_UNIT2_DESCR

ORG_UNIT3_CD

4

ORG_UNIT3_CD

ORG_UNIT3_DESCR

100

ORG_UNIT3_DESCR

UDF1_CD

30

PLANT+SUPERVISOR_TITLE

UDF1_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 310)

UDF2_CD

30

PLANT

UDF2_DESCR

60

SA_PLAN.PLANT_DESC

UDF3_CD

30

UDF3_CD

UDF3_DESCR

60

UDF3_DESCR

UDF4_CD

30

UDF4_CD

UDF4_DESCR

60

UDF4_DESCR

UDF5_CD

30

UDF5_CD

UDF5_DESCR

60

UDF5_DESCR

UDF6_CD

30

UDF6_CD

UDF6_DESCR

60

UDF6_DESCR

UDF7_CD

30

UDF7_CD

UDF7_DESCR

60

UDF7_DESCR

UDF8_CD

30

UDF8_CD

UDF8_DESCR

60

UDF8_DESCR

UDF9_CD

30

UDF9_CD

UDF9_DESCR

60

UDF9_DESCR

UDF10_CD

30

UDF10_CD

UDF10_DESCR

60

UDF10_DESCR

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

The base product uses the following field to populate UDFs on the dimension:

•

SUPERVISOR_TITLE

MWM Extract Program: mwm_schema_bi_ext

mcrew_vExtract Procedure: EXTMCREW

Modify View: EXTMCREW_MODIFY_V

Delete View: EXTMCREW_DELETE_V

Failure Dimension

Properties

Property

Value

Load Table Name

CD_FAILURE

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY.CODE_DESC (Code Table 10)

Source System Extract Program

EXTDFAIL

Stage Table Name

STG_FAILURE_EXT

Stage File Name

D_FAILURE_EXT

Control Table Name

STG_FAILURE_CTL_EXT

Control File name

D_FAILURE_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_FAILURE

OWB Work Flow Name

SPLWF_D_FAILURE

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDFAIL

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

FAILURE_CD

16

CODE

FAILURE_DESCR

60

CODE_DESC

FAILURE_ID

16

PLANT+CODE

UDF1_CD

30

PLANT

UDF1_DESCR

60

SA_PLANT.PLANT_DESC

UDF2_CD

30

UDF2_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default = ‘3’

EAM Extract Batch Control: EXTDFAIL

Fiscal Period Dimension

Properties

Property

Value

Load Table Name

CD_FISCAL_CAL

Table Type

Dimension

Source System Driver Table

SA_ACCOUNTING_PERIOD_DATES

Source System Extract Program

EXTDFPER

Stage Table Name

STG_FISCAL_EXT

Stage File Name

D_FISCAL_EXT

Control Table Name

STG_FISCAL_CTL_EXT

Control File name

D_FISCAL_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_FISCAL_CAL

OWB Work Flow Name

SPLWF_D_FISCAL_CAL

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDFPER

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

CALENDAR_CD

16

SA_PLANT.FISCAL_CALENDAR_CODE

CALENDR_DESCR

60

‘Fiscal Calendar’

FISCAL_YEAR

4

PERIOD_YEAR

PERIOD_NBR

3

PERIOD_MONTH

PERIOD_DESCR

60

PERIOD_MONTH + ‘-‘ + FISCAL_YEAR

PERIOD_START_DT

8

START_DATE

PERIOD_END_DT

8

END_DATE

UDF1_CD

30

UDF1_DESCR

60

UDF2_CD

30

UDF2_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

Operational Account Dimension

Properties

Property

Value

Load Table Name

CD_OP_ACCT

Table Type

Dimension

Source System Driver Table

SA_ACCOUNT_DATA

Source System Extract Program

EXTDOPAC

Stage Table Name

STG_OP_ACCT_EXT

Stage File Name

D_OP_ACCT_EXT

Control Table Name

STG_OP_ACCT_CTL_EXT

Control File name

D_OP_ACCT_EXT

Update Procedure Name

SPL_OP_ACCT_UPD_PRC

OWB Map Name

SPLMAP_D_OP_ACCT

OWB Work Flow Name

SPLWF_D_OP_ACCT

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDOPAC

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

OP_ACCT_ID

78

PLANT+ACCOUNT_NO

OP_ACCT_CD

78

PLANT+ACCOUNT_NO

OP_ACCT_DESCR

60

ACCOUNT_DESC

UDF1_CD

30

AREA

UDF1_DESCR

60

SA_AREA.AREA_DESC

UDF2_CD

30

PLANT

UDF2_DESCR

60

SA_PLANT.PLANT_DESC

UDF3_CD

30

SA_PLANT.ORGANIZATION

UDF3_DESCR

60

SA_ORGANIZATION.ORGANIZATION_DESC

UDF4_CD

30

SA_PLANT.COMPANY

UDF4_DESCR

60

SA_COMPANY.COMPANY_DESC

UDF5_CD

30

DEPARTMENT$1

UDF5_DESCR

60

SA_DEPARTMENT.DEPARTMENT_DESC

UDF6_CD

30

DEPARTMENT$2 - Parent Department of Department$1

UDF6_DESCR

60

SA_DEPARTMENT.DEPARTMENT_DESC (for DEPARTMENT$2)

UDF7_CD

30

DEPARTMENT$3 - Parent Department of Department$2

UDF7_DESCR

60

SA_DEPARTMENT.DEPARTMENT_DESC (for DEPARTMENT$3)

UDF8_CD

30

UDF8_DESCR

60

UDF9_CD

30

UDF9_DESCR

60

UDF10_CD

30

UDF10_DESCR

60

UDF11_CD

30

UDF11_DESCR

60

UDF12_CD

30

UDF12_DESCR

60

UDF13_CD

30

UDF13_DESCR

60

UDF14_CD

30

UDF14_DESCR

60

UDF15_CD

30

UDF15_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDOPAC

The base product uses the following fields to populate the dimension's UDF's:

•

AREA

•

PLANT

•

ORGANIZATION

•

COMPANY

•

DEPARTMENT$1 … DEPARTMENT$N.

The Operational Account extract supports extraction of multiple levels of department in the departmental hierarchies. Here, DEPARTMENT$1 is the highest level of Department in the hierarchy.

Operational Accounting Transaction Type Dimension

Properties

Property

Value

Load Table Name

CD_OP_ACTG_TY

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY

Source System Extract Program

EXTDOATT

Stage Table Name

STG_OP_ACTG_TY_EXT

Stage File Name

D_OP_ACTG_TY_EXT

Control Table Name

STG_OP_ACTG_TY_CTL_EXT

Control File name

D_OP_ACTG_TY_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_OP_ACTG_TY

OWB Work Flow Name

SPLWF_D_OP_ACTG_TY

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDOATT

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

OP_ACTG_TY_CD

16

CODE

OP_ACTG_TY_DESCR

60

CODE_DESC

UDF1_CD

30

UDF1_DESCR

60

UDF2_CD

30

UDF2_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDOATT

Operational Expense Dimension

Properties

Property

Value

Load Table Name

CD_OP_EXP

Table Type

Dimension

Source System Driver Table

SA_RULE_KEY ( where Rule ID = ‘EXPENSE CODES’ )

Source System Extract Program

EXTDOPEX

Stage Table Name

STG_OP_EXP_EXT

Stage File Name

D_OP_EXP_EXT

Control Table Name

STG_OP_EXP_CTL_EXT

Control File name

D_OP_EXP_EXT

Update Procedure Name

SPL_OP_EXP_UPD_PRC

OWB Map Name

SPLMAP_D_OP_EXP

OWB Work Flow Name

SPLWF_D_OP_EXP

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDOPEX

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

OP_EXP_ID

34

PLANT+KEY_NAME

OP_EXP_CD

30

PLANT+KEY_NAME

OP_EXP_DESCR

60

KEY_DESC

UDF1_CD

30

PLANT

UDF1_DESCR

60

SA_PLANT.PLANT_DESC

UDF2_CD

30

KEY_VALUE

UDF2_DESCR

60

INITCAP(KEY_VALUE)

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDOPEX

The following fields are used to populate the UDFs on the dimension:

•

KEY_VALUE

•

PLANT

Operational Unit of Measure Dimension

Properties

Property

Value

Load Table Name

CD_OP_UOM

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY (Code Table 23)

Source System Extract Program

EXTDOUOM

Stage Table Name

STG_OP_UOM_EXT

Stage File Name

D_OP_UOM_EXT

Control Table Name

STG_OP_UOM_CTL_EXT

Control File name

D_OP_UOM_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_OP_UOM

OWB Work Flow Name

SPLWF_D_OP_UOM

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDOUOM

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

UOM_CD

30

CODE

UOM_DESCR

60

CODE_DESC

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDOUOM

Planner Dimension

Properties

Property

Value

Load Table Name

CD_PLANNER

Table Type

Dimension

Source System Driver Table

SA_RULE_KEY where RULE_ID = ‘PLANNER’

Source System Extract Program

EXTDWRKP

Stage Table Name

STG_PLANNER_EXT

Stage File Name

D_PLANNER_EXT

Control Table Name

STG_PLANNER_CTL_EXT

Control File name

D_PLANNER_EXT

Update Procedure Name

SPL_PLANNER_UPD_PRC

OWB Map Name

SPLMAP_D_PLANNER

OWB Work Flow Name

SPLWF_D_PLANNER

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDWRKP

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

PLANNER_CD

16

KEY_NAME

PLANNER_DESCR

60

KEY_VALUE

PLANNER_ID

16

PLANT+KEY_NAME

UDF1_CD

30

PLANT

UDF1_DESCR

60

SA_PLANT.PLANT_DESC

UDF2_CD

30

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

UDF2_DESCR

60

EAM Extract Batch Control: EXTDWRKP

Repair Dimension

Properties

Property

Value

Load Table Name

CD_REPAIR

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY (Code Table 9)

Source System Extract Program

EXTDREPR

Stage Table Name

STG_REPAIR_EXT

Stage File Name

D_REPAIR_EXT

Control Table Name

STG_REPAIR_CTL_EXT

Control File name

D_REPAIR_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_REPAIR

OWB Work Flow Name

SPLWF_D_REPAIR

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDREPR

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

REPAIR_CD

16

CODE

REPAIR_DESCR

60

CODE_DESC

REPAIR_ID

16

PLANT+CODE

UDF1_CD

30

PLANT

UDF1_DESCR

60

SA_PLANT.PLANT_DESC

UDF2_CD

30

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

UDF2_DESCR

60

EAM Extract Batch Control: EXTDREPR

The following fields are used to populate the UDFs on the dimension:

•

KEY_NAME

Root Cause Dimension

Properties

Property

Value

Load Table Name

CD_ROOT_CAUSE

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY (Code Table 52)

Source System Extract Program

EXTDROOT

Stage Table Name

STG_ROOT_CAUSE_EXT

Stage File Name

D_ROOT_CAUSE_EXT

Control Table Name

STG_ROOT_CAUSE_CTL_EXT

Control File name

D_ROOT_CAUSE_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_ROOT_CAUSE

OWB Work Flow Name

SPLWF_D_ROOT_CAUSE

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDROOT

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

ROOT_CAUSE_CD

16

CODE

ROOT_CAUSE_DESCR

60

CODE_DESC

ROOT_CAUSE_ID

16

PLANT+CODE

UDF1_CD

30

PLANT

UDF1_DESCR

60

SA_PLANT.PLANT_DESC

UDF2_CD

30

UDF2_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDROOT

Stock Item Type Dimension

Properties

Property

Value

Load Table Name

CD_CTOCK_ITMTY

Table Type

Dimension

Source System Driver Table

SA_CATALOG

Source System Extract Program

EXTDSITE

Stage Table Name

STG_STOCK_ITMTY_EXT

Stage File Name

D_STOCK_ITMTY_EXT

Control Table Name

STG_STOCK_ITMTY_CTL_EXT

Control File name

D_STOCK_ITMTY_EXT

Update Procedure Name

SPL_STOCK_ITMTY_UPD_PRC

OWB Map Name

SPLMAP_D_STOCK_ITMTY

OWB Work Flow Name

SPLWF_D_STOCK_ITMTY

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDSITE

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

STOCK_ITMTY_ID

18

PLANT+STOCK_CODE

STOCK_ITMTY_CD

18

PLANT+STOCK_CODE

STOCK_ITMTY_DESCR

60

STOCK_DESC

STOCK_ITMTY_INFO

254

STOCK_DESC

UDF1_CD

30

STOCK_TYPE

UDF1_DESCR

60

CODE_DESC

UDF2_CD

30

STOCK_CLASS

UDF2_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 212)

UDF3_CD

30

COMMODITY_CATEGORY

UDF3_DESCR

60

SA_AUTHORITY.CODE_DESC (Code Table 212)

UDF4_CD

30

COMMODITY_NAME

UDF4_DESCR

60

SA_AUTHORITY.CODE_DESC (associated with Linked Code Table)

UDF5_CD

30

COMMODITY_TYPE

UDF5_DESCR

60

SA_AUTHORITY.CODE_DESC (associated with Linked Code Table)

UDF6_CD

30

PLANT

UDF6_DESCR

60

SA_PLANT.PLANT_DESC

UDF7_CD

30

SA_PLANT.ORGANIZATION

UDF7_DESCR

60

SA_ORGANIZATION.ORGANIZATION_DESC

UDF8_CD

30

SA_PLANT.COMPANY

UDF8_DESCR

60

SA_COMPANY.COMPANY_DESC

UDF9_CD

30

UDF9_DESCR

60

UDF10_CD

30

UDF10_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDSITE

The base product uses the following fields to populate the UDFs on the dimension:

•

STOCK_TYPE

•

STOCK_CLASS

•

COMMODITY_CATEGORY

•

COMMODITY_NAME

•

COMMODITY_TYPE

•

PLANT

•

ORGANIZATION

•

COMPANY

Storeroom Dimension

Properties

Property

Value

Load Table Name

CD_STRM

Table Type

Dimension

Source System Driver Table

SA_STOREROOM_SETUP

Source System Extract Program

EXTDSTRM

Stage Table Name

STG_STRM_EXT

Stage File Name

D_STRM_EXT

Control Table Name

STG_STRM_CTL_EXT

Control File name

D_STRM_EXT

Update Procedure Name

SPL_STRM_UPD_PRC

OWB Map Name

SPLMAP_D_STRM

OWB Work Flow Name

SPLWF_D_STRM

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDSTRM

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

STRM_ID

16

PLANT+STOREROOM

STRM_CD

16

PLANT+STOREROOM

STRM_DESCR

60

STOREROOM_DESC

UDF1_CD

30

STOREROOM_TYPE

UDF1_DESCR

60

SA_AUTHORITY.STOREROOM_TYPE_DESC (Code Table 4)

UDF2_CD

30

PLANT

UDF2_DESCR

60

SA_PLANT.PLANT_DESC

UDF3_CD

30

SA_PLANT.ORGANIZATION

UDF3_DESCR

60

SA_ORGANIZATION.ORGANIZATION_DESC

UDF4_CD

30

SA_PLANT.COMPANY

UDF4_DESCR

60

SA_COMPANY.COMPANY_DESC

UDF5_CD

30

UDF5_DESCR

60

UDF6_CD

30

UDF6_DESCR

60

UDF7_CD

30

UDF7_DESCR

60

UDF8_CD

30

UDF8_DESCR

60

UDF9_CD

30

UDF9_DESCR

60

UDF10_CD

30

UDF10_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDSTRM

The following fields are used to populate the UDFs on the dimension:

•

STOREROOM TYPE

•

PLANT

•

ORGANIZATION

•

COMPANY

Storeroom Transaction Type Dimension

Properties

Property

Value

Load Table Name

CD_STRM_TR_TYE

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY (Code Table 145)

Source System Extract Program

EXTDSTTT

Stage Table Name

STG_STRM_TR_TY_EXT

Stage File Name

D_STRM_TR_TY_EXT

Control Table Name

STG_STRM_TR_TY_CTL_EXT

Control File name

D_STRM_TR_TY_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_STRM_TR_TY

OWB Work Flow Name

SPLWF_D_STRM_TR_TY

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDSTTT

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

STRM_TR_TY_CD

16

CODE

STRM_TR_TY_DESCR

60

CODE_DESC

UDF1_CD

30

UDF1_DESCR

60

UDF2_CD

30

UDF2_DESCR

60

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDSTTT

The base product uses the following parameters to populate the UDFs on the dimension:

•

KEY_VALUE

Work Order Type Dimension

Properties

Property

Value

Load Table Name

CD_WRKORD_TY

Table Type

Dimension

Source System Driver Table

SA_AUTHORITY (Code Table 71)

Source System Extract Program

extdwoty

Stage Table Name

STG_WRKORD_TY_EXT

Stage File Name

D_WRKORD_TY_EXT

Control Table Name

STG_WRKORD_TY_CTL_EXT

Control File name

D_WRKORD_TY_EXT

Update Procedure Name

OWB Map Name

SPLMAP_D_WRKORD_TY

OWB Work Flow Name

SPLWF_D_WRKORD_TY

OWB Work Flow Package Name

DIM

Driving Data View

SV_BI_EXTRACT_EXTDWOTY

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

WRKORD_TY_CD

4

CODE

WRKORD_TY_DESCR

60

CODE_DESC

UDF1_CD

30

UDF1_DESCR

60

UDF2_CD

30

DATA_SOURCE_IND

6

UDF2_DESCR

60

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTDWOTY

Fact Extract Programs

Operational Accounting Transaction Fact (WAM)

Properties

Property

Value

Load Table Name

CF_OP_ACTG

Table Type

Fact

Source System Driver Table

SA_ACCOUNT_LOG

Source System Extract Program

EXTFOPAT

Stage Table Name

STG_OP_ACTG_EXT

Stage File Name

F_OP_ACTG_EXT

Control Table Name

STG_OP_ACTG_CTL_EXT

Control File name

F_OP_ACTG_EXT

Update Procedure Name

OWB Map Name

SPLMAP_F_OP_ACTG

OWB Work Flow Name

SPLWF_F_OP_ACTG

OWB Work Flow Package Name

FACT

Driving Data View

SV_BI_EXTRACT_EXTFOPAT

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

OP_ACTG_ID

16

TRANSACTION_ID

CURRENCY_CD

3

SA_PLANT.LOCAL_CURRENCY_CODE

OP_ACCT_ID

78

PLANT+ACCOUNT_NO

OP_EXP_ID

16

PLANT+EXPENSE_CODE

OP_ACTG_TY_CD

16

TRANSACTION_TYPE

FISCAL_CAL_CD

16

SA_PLANT.FISCAL_CALENDAR_CODE

FISCAL_PER_CD

2

PERIOD_MONTH

FISCAL_YEAR

4

PERIOD_YEAR

TRANS_DT

8

TRANSACTION_DATE

ASSET_ID

20

PLANT+ASSET_ID+ASSET_RECORD_TYPE

TRANS_AMT

16

TRANSACTION_AMOUNT

TRANS_HRS

11

TRANSACTION_HOURS

UDD1_CD

16

UDD2_CD

16

UDM1

19

UDM2

19

UDM3

19

UDM4

19

UDM5

19

UDDGEN1

8

UDDGEN2

8

UDDGEN3

8

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTFOPAT

Storeroom Inventory Snapshot Fact (WAM)

Properties

Property

Value

Load Table Name

CF_STRM_INV

Table Type

Fact

Source System Driver Table

SA_STOREROOM

Source System Extract Program

EXTFSTOR

Stage Table Name

STG_STRM_INV_EXT

Stage File Name

F_STRM_INV_EXT

Control Table Name

STG_STRM_INV_CTL_EXT

Control File name

F_STRM_INV_EXT

Update Procedure Name

OWB Map Name

SPLMAP_F_STRM_INV

OWB Work Flow Name

SPLWF_F_STRM_INV

OWB Work Flow Package Name

FACT

Driving Data View

SV_BI_EXTRACT_EXTFSTOR_INC or SV_BI_EXTRACT_EXTFSTOR_INI

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

STOCK_ITEMTY_ID

18

PLANT+STOCK_CODE

STRM_ID

16

PLANT+STOREROOM

CURRENCY_CD

3

SA_PLANT.LOCAL_CURRENCY_CODE

OP_UOM_CD

16

SA_CATALOG.UNIT_OF_ISSUE

TOTAL_VALUE

16

If First Snapshot, then TOTAL_VALUE

Else TOTAL_VALUE+NET_ADJUSTMENT_AMOUNT

QTY_ON_HAND

16

If First Snapshot, then INVENTORY_QUANTITY

Else INVENTORY_QUANTITY +TRANSACTION_QUANTITY

QTY_ON_DEMAND

16

If First Snapshot, then ON_DEMAND_QUANTITY

Else 0

QTY_OVER_MAX

16

If First Snapshot, then QUANTITY_OVER_MAX

Else 0

VALUE_OVER_MAX

16

If First Snapshot, then VALUE_OVER_MAX

Else 0

SNAP_TYPE_CD

1

SA_RULE_KEY.KEY_VALUE3 where rule_id = ‘OUBI SETUP’ and key_name = ‘OUBI INV SNAPSHOT’

SNAPSHOT_DATE

8

SYSDATE

OP_ACCT_ID

78

ASSET_ACCOUNT_NO

UDM1

19

UDM2

19

UDM3

19

UDM4

19

UDM5

19

UDD1_CD

16

UDD2_CD

16

UDDGEN1

8

UDDGEN2

8

UDDGEN3

8

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTFSTOR

In addition to the standard parameters, the following parameters must also be supplied to the extract process:

•

SNAPSHOT-TYPE. If taking a weekly snapshot, enter "W". If taking a monthly snapshot, enter "M".

•

NBR-HIST. This is the number of historical periods to be snapshot (for example: you can use this parameter to snapshot inventory data for each of the last X weeks or months). With the exception of the initial load, this should be set to 0.

If the SNAPSHOT-TYPE is weekly (W), 7 days is subtracted from the business date for each historical snapshot date. If the SNAPSHOT-TYPE is monthly (M), a historic snapshot is extracted on the last day of each month, starting from the month prior to the business date.

Storeroom Transaction Fact (WAM)

Properties

Property

Value

Load Table Name

CF_STRM_TR

Table Type

Fact

Source System Driver Table

SA_STOREROOM_LOG

Source System Extract Program

EXTFSTTR

Stage Table Name

STG_STRM_TR_EXT

Stage File Name

F_STRM_TR_EXT

Control Table Name

STG_STRM_TR_CTL_EXT

Control File name

F_STRM_TR_EXT

Update Procedure Name

OWB Map Name

SPLMAP_F_STRM_TR

OWB Work Flow Name

SPLWF_F_STRM_TR

OWB Work Flow Package Name

FACT

Driving Data View

SV_BI_EXTRACT_EXTFSTTR

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

STRM_TR_ID

16

TRANSACTION_ID

CURRENCY_CD

3

SA_PLANT.LOCAL_CURRENCY_CODE

STRM_TR_TY_CD

16

TRANSACTION_TYPE

STRM_ID

16

PLANT+STOREROOM

STOCK_ITMTY_ID

18

PLANT+STOCKCODE

OP_UOM_CD

16

UOM

TRANS_DT

8

TRANSACTION_DATE

TRANS_AMT

16

TRANSACTION_AMOUNT

TRANS_QTY

16

TRANSACTION_QUANTITY

STOCK_OUT_IND

1

STOCK_OUT_IND

UDM1

19

UDM2

19

UDM3

19

UDM4

19

UDM5

19

UDD1_CD

16

UDD2_CD

16

UDDGEN1

8

UDDGEN2

8

UDDGEN3

8

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTFSTTR

Work Order Task Fact (WAM)

Properties

Property

Value

Load Table Name

CF_WRKORD_TK

Table Type

Fact

Source System Driver Table

SV_WORK_ORDER_TASK_AMT

Source System Extract Program

EXTFWRKT

Stage Table Name

STG_WRKORD_TK_EXT

Stage File Name

F_WRKORD_TK_EXT

Control Table Name

STG_WRKORD_TK_CTL_EXT

Control File name

F_WRKORD_TK_EXT

Update Procedure Name

OWB Map Name

SPLMAP_F_WRKORD_TK

OWB Work Flow Name

SPLWF_F_WRKORD_TK

OWB Work Flow Package Name

FACT

Driving Data View

SV_BI_EXTRACT_EXTFWRKT

Fields (listed in the order they will appear in the flat file)

Extract Field

Length

Source

UPDATE_DTTM

20

SYSDATE

CHANGE_TYPE_CD

1

Values: I - Insert/Update, D - Deleted

WRKORD_TK_ID

16

(SA_WORK_ORDER_TASK or SAa_WORK_HISTORY_TASK).PLANT+WORK_ORDER_NO+ WORK_ORDER_TASK_NO

CURRENCY_CD

3

SA_PLAN.LOCAL_CURRENCY_CODE

ASSET_ID

20

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). PLANT+ASSET_ID+ASSET_RECORD_TYPE

PLANNER_ID

16

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). PLANT+PLANNER

CREW_ID

16

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). PLANT+CREW

WRKORD_TY_CD

4

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK).WORK_TYPE

FINISH_DATE

8

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). ACTUAL_FINISH_DATE

CLOSE_DATE

8

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). CLOSED_DATE

EST_TOT_AMT

16

ESTIMATEDTOTAMT

ACT_TOT_AMT

16

ACTUALTOTAMT

EST_LBR_AMT

16

ESTIMATEDLBRAMT

ACT_LBR_AMT

16

ACTUALLBRAMT

EST_LBR_HOURS

16

ESTIMATEDLBRHRS

ACT_LBR_HOURS

16

ACTUALLBRHRS

EST_MAT_AMT

16

ESTIMATEDMATAMT

ACT_MAT_AMT

16

ACTUALMATAMT

EST_SERVCON_AMT

16

ESTIMATEDCONSERVAMT

ACT_SERVCON_AMT

16

ACTUALCONSERVAMT

EST_OTHER_AMT

16

ESTIMATEDOTHERAMT

ACT_OTHER_AMT

16

ACTUALOTHERAMT

ACT_ADJUST_AMT

16

ACTUALADJUSTMENTAMT

LATE_SW

1

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). if ACTUAL_FINISH_DATE is after TASK_REQUIRED_DATE, then set to "Y", otherwise set to "N"

UDM1

19

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). ACTUAL_FINISH_DATE - TASK_REQUIRED_DATE

UDM2

19

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). CLOSED_DATE - ACTUAL_FINISH_DATE

UDM3

19

1 if SA_ASSET_DOWNTIME.DOWNTIME_SCHEDULED_IND = ‘Y’, else 0

UDM4

19

UDM5

19

FAILURE_ID

16

SA_WORK_ORDER_TASK_FAILURE.PLANT + SA_WORK_ORDER_TASK_FAILURE.FAILURE_CODE

REPAIR_ID

16

SA_WORK_ORDER_TASK_FAILURE.PLANT + SA_WORK_ORDER_TASK_FAILURE.REPAIRE_CODE

ROOT_CAUSE_ID

16

SA_WORK_ORDER_TASK_FAILURE.PLANT + SA_WORK_ORDER_TASK_FAILURE.ROOT_CAUSE

UDD1_CD

16

UDD2_CD

16

UDDGEN1

8

OP_ACCT_ID

78

(SA_WORK_ORDER_TASK or SA_WORK_HISTORY_TASK). PLANT+ACCOUNT_NO

UDDGEN2

8

UDDGEN3

8

DATA_SOURCE_IND

6

Value of Value3 Common Extract Parameter in the call to SDBP_BI_EXTRACT_HANDLER. Default value is ‘3’

EAM Extract Batch Control: EXTFWRKT

This process extracts FINISHED and CLOSE work order task records.

The base product uses the following parameters to populate the UDMs on the fact:

•

DAYS-LATE. If finished date is later than required date, Days Late = finished date - required date. Else, 0.

•

DAYS-TO-CLOSE. If there is a closed and finished date, then Days to Close = closed date - finished date. Else, 0.

•

SCHEDULED-DOWNTIME-IND. 1 if the down time was scheduled.

Dimension Table Schema

Asset Dimension <CD_ASSET>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

ASSET_KEY

SPL_ASSET_SEQ.NEXTVAL

ASSET_CD

Asset

Stage: ASSET_CD

ASSET_DESCR

Asset Description

Stage: ASSET_DESCR

ASSET_INFO

Asset Information

Stage: ASSET_INFO

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_ASSET_ID

Source Asset ID

Stage: ASSET_ID

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF10_CD

User Defined Field Code

Stage: UDF10_CD

UDF10_DESCR

User Defined Field Description

Stage: UDF10_DESCR

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Asset Class

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

Criticality

Stage: UDF2_DESCR

UDF3_CD

User Defined Field Code

Stage: UDF3_CD

UDF3_DESCR

Building

Stage: UDF3_DESCR

UDF4_CD

User Defined Field Code

Stage: UDF4_CD

UDF4_DESCR

Location

Stage: UDF4_DESCR

UDF5_CD

User Defined Field Code

Stage: UDF5_CD

UDF5_DESCR

Process

Stage: UDF5_DESCR

UDF6_CD

User Defined Field Code

Stage: UDF6_CD

UDF6_DESCR

Asset Record Type

Stage: UDF6_DESCR

UDF7_CD

User Defined Field Code

Stage: UDF7_CD

UDF7_DESCR

Facility

Stage: UDF7_DESCR

UDF8_CD

User Defined Field Code

Stage: UDF8_CD

UDF8_DESCR

Organization

Stage: UDF8_DESCR

UDF9_CD

User Defined Field Code

Stage: UDF9_CD

UDF9_DESCR

Company

Stage: UDF9_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Crew Dimension <CD_CREW>

Properties

Property

Value

Table Type

Dimension

SCD Type

2

Fields

Column

OBIEE Field

Load

CREW_KEY

Crew Key

Stage: CREW_CD

CREW_CD

Crew Code

Stage: CREW_CD

CREW_DESCR

Crew Description

Stage: CREW_DESCR

CREW_TYPE_CD

Crew Type Code

Stage: CREW_TYPE_CD

CREW_TYPE_DESCR

Crew Type Description

Stage: CREW_TYPE_DESCR

ORG_UNIT1_CD

Organization Unit 1 Code

Stage: ORG_UNIT1_CD

ORG_UNIT1_DESCR

Organization Unit 1 Description

Stage: ORG_UNIT1_DESCR

ORG_UNIT2_CD

Organization Unit 2 Code

Stage: ORG_UNIT2_CD

ORG_UNIT2_DESCR

Organization Unit 2 Description

Stage: ORG_UNIT2_DESCR

ORG_UNIT3_CD

Organization Unit 3 Code

Stage: ORG_UNIT3_CD

ORG_UNIT3_DESCR

Organization Unit 3 Description

Stage: ORG_UNIT3_DESCR

SRC_CREW_ID

Crew Id

Stage: CREW_ID

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

User Defined Field Description

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field Description

Stage: UDF2_DESCR

UDF3_CD

User Defined Field Code

Stage: UDF3_CD

UDF3_DESCR

User Defined Field Description

Stage: UDF3_DESCR

UDF4_CD

User Defined Field Code

Stage: UDF4_CD

UDF4_DESCR

User Defined Field Description

Stage: UDF4_DESCR

UDF5_CD

User Defined Field Code

Stage: UDF5_CD

UDF5_DESCR

User Defined Field Description

Stage: UDF5_DESCR

UDF6_CD

User Defined Field Code

Stage: UDF6_CD

UDF6_DESCR

User Defined Field Description

Stage: UDF6_DESCR

UDF7_CD

User Defined Field Code

Stage: UDF7_CD

UDF7_DESCR

User Defined Field Description

Stage: UDF7_DESCR

UDF8_CD

User Defined Field Code

Stage: UDF8_CD

UDF8_DESCR

User Defined Field Description

Stage: UDF8_DESCR

UDF9_CD

User Defined Field Code

Stage: UDF9_CD

UDF9_DESCR

User Defined Field Description

Stage: UDF9_DESCR

UDF10_CD

User Defined Field Code

Stage: UDF10_CD

UDF10_DESCR

User Defined Field Description

Stage: UDF10_DESCR

DATA_LOAD_DTTM

Data Load Date/Time

System Variable: CURRENT_DATE

DATA_SOURCE_IND

Data Source Indicator

Stage: DATA_SOURCE_IND

EFF_END_DTTM

Effective End Date/Time

System Variable: HIGH_DATE

EFF_START_DTTM

Effective Start Date/Time

UPDATE_DTTM

JOB_NBR

Job Number

Job Control: JOB_NBR

Date Dimension <CD_DATE>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Comment

This dimension is unusual in that it does not have an extract program. Rather, a utility generates the rows in this dimension. This utility is supplied in the form of a database stored procedure called SPL_LOADDATE. This stored procedure is delivered with the Oracle Warehouse Builder package.

Note that the same procedure is also included in the initial data warehouse setup workflow package called INIT_PKG for the execution.

This stored procedure has start date and end date as input parameters. These dates should be defined in the following format:

- start date: to_date('20000101','YYYYMMDD')

- end date: to_date('20090331','YYYYMMDD')"

Fields

Column

OBIEE Field

Load

DATE_KEY

SPL_DATE_SEQ.NEXTVAL

CAL_DT

Calendar Date

Date between the Start and End Date provided to the SPL_LOADDATE procedure

DAY_NBR_IN_MONTH

Day Number in Month (1-31)

TO_NUMBER( TO_CHAR( CAL_DT, 'DD' ))

DAY_NBR_IN_WEEK

Day Number in Week (1-7)

TO_NUMBER(TO_CHAR( CAL_DT, 'D' ))

DAY_NBR_IN_YEAR

Day number in Year (1-366)

TO_NUMBER(TO_CHAR( CAL_DT, 'DDD' ))

WORK_DAY_IND

Work Day Indicator

0

ABS_MONTH_NBR

Absolute Month Number

Incremental number of the month, starting at 0

CAL_MONTH_NBR

Calendar Month Number (1-12)

TO_NUMBER(TO_CHAR( CAL_DT, 'MM' ))

MONTH_END_DT

Month End Date

LAST_DAY( CAL_DT )

ABS_QTR_NBR

Absolute Quarter Number

Incremental number of the quarter, starting at 0

CAL_QTR_NBR

Calendar Quarter Number (1-4)

TO_NUMBER(TO_CHAR( CAL_DT, 'Q' ))

QTR_END_DT

Quarter End Date

ADD_MONTHS(TRUNC(CAL_DT, 'Q'), 3) - 1

ABS_WEEK_NBR

Absolute Week Number

Incremental number of the week, starting at 0

CAL_WEEK_NBR

Calendar Week Number (1-53)

TO_NUMBER( TO_CHAR( CAL_DT, 'WW' ) )

WEEK_END_DT

Week End Date

CAL_DT + (7 - DAY_NBR_IN_WEEK);

CAL_YEAR

Calendar Year

TO_NUMBER( TO_CHAR( CAL_DT, 'YYYY' ) )

YEAR_END_DT

Year End Date

ADD_MONTHS(TRUNC(CAL_DT,'YYYY'), 12) - 1;

UDF1_CD

User Defined Field Code

DAY_NBR_IN_WEEK

UDF1_DESCR

Day of Week

TO_CHAR( CAL_DT, ‘Day’ )

UDF2_CD

User Defined Field Code

CAL_QTR_NBR

UDF2_DESCR

Calendar Quarter

‘Quarter ‘ || trim( to_char( CAL_QTR_NBR ))

UDF3_CD

User Defined Field Code

If Month in ‘December, January, February’, then Winter,

If Month in ‘March, April, May’, then Spring,

If Month in ‘June, July, August’, then Summer,

If Month in ‘September, October, November’, then Fall

UDF3_DESCR

Season

If Month in ‘December, January, February’, then Winter,

If Month in ‘March, April, May’, then Spring,

If Month in ‘June, July, August’, then Summer,

If Month in ‘September, October, November’, then Fall

UDF4_CD

User Defined Field Code

WORK_DAY_IND

UDF4_DESCR

Workday

‘Yes’, ‘No’

UDF5_CD

User Defined Field Code

CAL_MONTH_NBR

UDF5_DESCR

Calendar Month

TO_CHAR( CAL_DT, ‘Month’ )

UDF6_CD

User Defined Field Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field Code

UDF10_DESCR

User Defined Field 10 Description

Month

trim(trailing ' ' from UDF5_DESCR || ' ' || cast(CAL_YEAR as CHARACTER ( 30 )) )

Quarter

trim(trailing ' ' from UDF2_DESCR || ' ' || cast(CAL_YEAR as CHARACTER ( 30 )) )

Calendar Day (Only Date)

cast(CAL_DT as DATE )

Failure Dimension <CD_FAILURE>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

FAILURE_KEY

SPL_FAILURE_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_FAILURE_ID

Source Failure ID

Stage: FAILURE_ID

FAILURE_CD

Stage: FAILURE_CD

FAILURE_DESCR

Failure

Stage: FAILURE_DESCR

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Facility

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Fiscal Period Dimension <CD_FISCAL_CAL>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

FISCAL_CAL_KEY

SPL_FISCAL_SEQ.NEXTVAL

FISCAL_CAL_CD

Stage: CALENDAR_CD

FISCAL_CAL_DESCR

Fiscal Calendar

Stage: CALENDR_DESCR

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

PERIOD_DESCR

Fiscal Period

Stage: PERIOD_DESCR

PERIOD_END_DT

Fiscal Period End Date

Stage: PERIOD_END_DT

PERIOD_NBR

Fiscal Period Code

Stage: PERIOD_NBR

PERIOD_START_DT

Fiscal Period Start Date

Stage: PERIOD_START_DT

UPDATE_DTTM

Stage: UPDATE_DTTM

FISCAL_YEAR

Fiscal Year

Stage: FISCAL_YEAR

ABS_PERIOD_NBR

0

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Fiscal Quarter Number

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

Fiscal Quarter

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Operational Account Dimension <CD_OP_ACCT>

Properties

Property

Value

Table Type

Dimension

SCD Type

2

Fields

Column

OBIEE Field

Load

OP_ACCT_KEY

SPL_OP_ACCT_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_OP_ACCT_ID

Source Operational Account ID

Stage: OP_ACCT_ID

OP_ACCT_CD

Stage: OP_ACCT_CD

OP_ACCT_DESCR

Account Description

Stage: OP_ACCT_DESCR

UDF10_CD

User Defined Field Code

Stage: UDF10_CD

UDF10_DESCR

User Defined Field 10 Description

Stage: UDF10_DESCR

UDF11_CD

User Defined Field Code

Stage: UDF11_CD

UDF11_DESCR

User Defined Field 11 Description

Stage: UDF11_DESCR

UDF12_CD

User Defined Field Code

Stage: UDF12_CD

UDF12_DESCR

User Defined Field 12 Description

Stage: UDF12_DESCR

UDF13_CD

User Defined Field Code

Stage: UDF13_CD

UDF13_DESCR

User Defined Field 13 Description

Stage: UDF13_DESCR

UDF14_CD

User Defined Field Code

Stage: UDF14_CD

UDF14_DESCR

User Defined Field 14 Description

Stage: UDF14_DESCR

UDF15_CD

User Defined Field Code

Stage: UDF15_CD

UDF15_DESCR

User Defined Field 15 Description

Stage: UDF15_DESCR

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Area

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

Facility

Stage: UDF2_DESCR

UDF3_CD

User Defined Field Code

Stage: UDF3_CD

UDF3_DESCR

Organization

Stage: UDF3_DESCR

UDF4_CD

User Defined Field Code

Stage: UDF4_CD

UDF4_DESCR

Company

Stage: UDF4_DESCR

UDF5_CD

User Defined Field Code

Stage: UDF5_CD

UDF5_DESCR

Department

Stage: UDF5_DESCR

UDF6_CD

User Defined Field Code

Stage: UDF6_CD

UDF6_DESCR

Level-2 Department

Stage: UDF6_DESCR

UDF7_CD

User Defined Field Code

Stage: UDF7_CD

UDF7_DESCR

Level-3 Department

Stage: UDF7_DESCR

UDF8_CD

User Defined Field Code

Stage: UDF8_CD

UDF8_DESCR

User Defined Field 8 Description

Stage: UDF8_DESCR

UDF9_CD

User Defined Field Code

Stage: UDF9_CD

UDF9_DESCR

User Defined Field 9 Description

Stage: UDF9_DESCR

EFF_END_DTTM

31-DEC-2999

EFF_START_DTTM

Stage: UPDATE_DTTM

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Operational Accounting Transaction Type Dimension <CD_OP_ACTG_TY>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

OP_ACTG_TY_KEY

SPL_OP_ACTG_TY_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

OP_ACTG_TY_CD

Stage: OP_ACTG_TY_CD

OP_ACTG_TY_DESCR

Transaction Type

Stage: OP_ACTG_TY_DESCR

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

User Defined Field 1 Description

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Operational Expense Dimension <CD_OP_EXP>

Properties

Property

Value

Table Type

Dimension

SCD Type

2

Fields

Column

OBIEE Field

Load

OP_EXP_KEY

SPL_OP_EXP_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_OP_EXP_ID

Source Expense ID

Stage: OP_EXP_ID

OP_EXP_CD

Stage: OP_EXP_CD

OP_EXP_DESCR

Expense Code

Stage: OP_EXP_DESCR

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Expense Category

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

Facility

Stage: UDF2_DESCR

EFF_END_DTTM

31-DEC-2999

EFF_START_DTTM

Stage: UPDATE_DTTM

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Operational Unit of Measure Dimension <CD_OP_UOM>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

OP_UOM_KEY

SPL_OP_UOM_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

OP_UOM_CD

Stage: UOM_CD

OP_UOM_DESCR

Unit of Measure

Stage: UOM_DESCR

UPDATE_DTTM

Stage: UPDATE_DTTM

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Planner Dimension <CD_PLANNER>

Properties

Property

Value

Table Type

Dimension

SCD Type

2

Fields

Column

OBIEE Field

Load

PLANNER_KEY

SPL_PLANNER_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_PLANNER_ID

Source Planner ID

Stage: PLANNER_ID

PLANNER_CD

Stage: PLANNER_CD

PLANNER_DESCR

Planner

Stage: PLANNER_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Facility

Stage: UDF1_DESCR

EFF_END_DTTM

31-DEC-2999

EFF_START_DTTM

Stage: UPDATE_DTTM

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Repair Dimension <CD_REPAIR>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

REPAIR_KEY

SPL_REPAIR_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_REPAIR_ID

Source Repair ID

Stage: REPAIR_ID

REPAIR_CD

Stage: REPAIR_CD

REPAIR_DESCR

Repair Description

Stage: REPAIR_DESCR

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Facility

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Root Cause Dimension <CD_ROOT_CAUSE>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

ROOT_CAUSE_KEY

SPL_ROOT_CAUSE_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_ROOT_CAUSE_ID

Source Root Cause ID

Stage: ROOT_CAUSE_ID

ROOT_CAUSE_CD

Stage: ROOT_CAUSE_CD

ROOT_CAUSE_DESCR

Root Cause

Stage: ROOT_CAUSE_DESCR

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Facility

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Snapshot Type Dimension <CD_SNAP_TYPE>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Comment

This dimension is unusual in that it does NOT have an extract program. Rather, a utility generates the rows in this dimension. This utility is supplied in the form of a database stored procedure called SPL_LOADSNAPTYPE. This stored procedure is delivered with the Oracle Warehouse Builder package. Note, the same procedure is also included in the initial data warehouse setup workflow package called INIT_PKG for the execution.

This stored procedure stores the following values:

Y: yearly

Q: quarterly

M: monthly

W: weekly

D: daily

H: hourly

A: adhoc

Fields

Column

OBIEE Field

Load

SNAP_TYPE_CD

Snapshot Type

Y, Q, M, W, D, H, A

SNAP_TYPE_DESCR

Snapshot Type Description

Yearly

Quarterly

Monthly

Weekly

Daily

Hourly

Adhoc

Stock Item Type Dimension <CD_STOCK_ITMTY>

Properties

Property

Value

Table Type

Dimension

SCD Type

2

Fields

Column

OBIEE Field

Load

STOCK_ITMTY_KEY

SPL_STOCK_ITMTY_SEQ.NEXTVAL

SRC_STOCK_ITMTY_ID

Stage: STOCK_ITMTY_ID

STOCK_ITMTY_INFO

Stage: STOCK_ITMTY_INFO

STOCK_ITMTY_CD

Stage: STOCK_ITMTY_CD

STOCK_ITMTY_DESCR

Stock Item Type

Stage: STOCK_ITMTY_DESCR

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

UDF10_CD

User Defined Field Code

Stage: UDF10_CD

UDF10_DESCR

User Defined Field 10 Description

Stage: UDF10_DESCR

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Stock Type

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

Stock Class

Stage: UDF2_DESCR

UDF3_CD

User Defined Field Code

Stage: UDF3_CD

UDF3_DESCR

Commodity Category

Stage: UDF3_DESCR

UDF4_CD

User Defined Field Code

Stage: UDF4_CD

UDF4_DESCR

Commodity Name

Stage: UDF4_DESCR

UDF5_CD

User Defined Field Code

Stage: UDF5_CD

UDF5_DESCR

Commodity Type

Stage: UDF5_DESCR

UDF6_CD

User Defined Field Code

Stage: UDF6_CD

UDF6_DESCR

Facility

Stage: UDF6_DESCR

UDF7_CD

User Defined Field Code

Stage: UDF7_CD

UDF7_DESCR

User Defined Field 7 Description

Stage: UDF7_DESCR

UDF8_CD

User Defined Field Code

Stage: UDF8_CD

UDF8_DESCR

User Defined Field 8 Description

Stage: UDF8_DESCR

UDF9_CD

User Defined Field Code

Stage: UDF9_CD

UDF9_DESCR

User Defined Field 9 Description

Stage: UDF9_DESCR

EFF_END_DTTM

31-DEC-2999

EFF_START_DTTM

Stage: UPDATE_DTTM

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Storeroom Dimension <CD_STRM>

Properties

Property

Value

Table Type

Dimension

SCD Type

2

Fields

Column

OBIEE Field

Load

STRM_KEY

SPL_STRM_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SRC_STRM_ID

Source Storeroom ID

Stage: STRM_ID

STRM_CD

Storeroom Code

Stage: STRM_CD

STRM_DESCR

Storeroom

Stage: STRM_DESCR

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Storeroom Type

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

Facility

Stage: UDF2_DESCR

UDF3_CD

User Defined Field Code

Stage: UDF3_CD

UDF3_DESCR

Organization

Stage: UDF3_DESCR

UDF4_CD

User Defined Field Code

Stage: UDF4_CD

UDF4_DESCR

Company

Stage: UDF4_DESCR

UDF5_CD

User Defined Field Code

Stage: UDF5_CD

UDF5_DESCR

User Defined Field 5 Description

Stage: UDF5_DESCR

UDF10_CD

User Defined Field Code

Stage: UDF10_CD

UDF10_DESCR

User Defined Field 10 Description

Stage: UDF10_DESCR

UDF6_CD

User Defined Field Code

Stage: UDF6_CD

UDF6_DESCR

User Defined Field 6 Description

Stage: UDF6_DESCR

UDF7_CD

User Defined Field Code

Stage: UDF7_CD

UDF7_DESCR

User Defined Field 7 Description

Stage: UDF7_DESCR

UDF8_CD

User Defined Field Code

Stage: UDF8_CD

UDF8_DESCR

User Defined Field 8 Description

Stage: UDF8_DESCR

UDF9_CD

User Defined Field Code

Stage: UDF9_CD

UDF9_DESCR

User Defined Field 9 Description

Stage: UDF9_DESCR

EFF_END_DTTM

31-DEC-2999

EFF_START_DTTM

Stage: UPDATE_DTTM

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Storeroom Transaction Type Dimension <CD_STRM_TR_TY>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

STRM_TR_TY_KEY

SPL_STRM_TR_TY_SEQ.NEXTVAL

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

STRM_TR_TY_CD

Stage: STRM_TR_TY_CD

STRM_TR_TY_DESCR

Storeroom Transaction Type

Stage: STRM_TR_TY_DESCR

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

Transaction Group

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Work Order Type Dimension <CD_WRKORD_TY>

Properties

Property

Value

Table Type

Dimension

SCD Type

1

Fields

Column

OBIEE Field

Load

WRKORD_TY_KEY

SPL_WRKORD_TY_SEQ.NEXTVAL

WRKORD_TY_CD

Stage: WRKORD_TY_CD

WRKORD_TY_DESCR

Work Order Type

Stage: WRKORD_TY_DESCR

DATA_LOAD_DTTM

SYSDATE

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

UPDATE_DTTM

Stage: UPDATE_DTTM

UDF1_CD

User Defined Field Code

Stage: UDF1_CD

UDF1_DESCR

User Defined Field 1 Description

Stage: UDF1_DESCR

UDF2_CD

User Defined Field Code

Stage: UDF2_CD

UDF2_DESCR

User Defined Field 2 Description

Stage: UDF2_DESCR

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

Fact Table Schema

Operational Accounting Transaction Fact <CF_OP_ACTG>

Entity Relationship Diagram

Properties

Property

Value

Table Type

Fact

Fact Type

Transaction

Default Analytic

Work and Asset Analytics

Fields

Column

OBIEE Field

Load

OP_ACTG_KEY

SPL_OP_ACTG_SEQ.NEXTVAL

ASSET_KEY

Join to CD_ASSET: Optional

CD_ASSET.SRC_ASSET_ID(+) = Stage.ASSET_ID

CURRENCY_CD

Currency Code

Stage: CURRENCY_CD

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

TRANS_DATE_KEY

Join to CD_DATE: Required

CD_DATE.CAL_DT = Stage.TRANS_DT

FACT_CNT

Financial Transaction Count

1

OP_ACCT_KEY

Join to CD_OP_ACCT: Required

CD_OP_ACCT.SRC_OP_ACCT_ID = Stage.OP_ACCT_ID

OP_EXP_KEY

Join to CD_OP_EXP: Required

CD_OP_EXP.SRC_OP_EXP_ID = Stage.OP_EXP_ID

OP_ACTG_TY_KEY

Join to CD_OP_ACTG_TY: Required

CD_OP_ACTG_TY.OP_ACTG_TY_CD = Stage.OP_ACTG_TY_CD

OP_ACTG_UDD1_KEY

Join to CD_OP_ACTG_UDD1: Optional

CD_OP_ACTG_UDD1.UDD1_CD(+) = Stage.UDD1_CD

OP_ACTG_UDD2_KEY

Join to CD_OP_ACTG_UDD2: Optional

CD_OP_ACTG_UDD2.UDD2_CD(+) = Stage.UDD2_CD

SRC_OP_ACTG_ID

Account Log ID

Stage: OP_ACTG_ID

TRANS_AMT

Transaction Amount

Stage: TRANS_AMT

TRANS_HOURS

Transaction Hours

Stage: TRANS_HRS

UDM1

User Defined Measure 1

Stage: UDM1

UDM2

User Defined Measure 2

Stage: UDM2

UDM3

User Defined Measure 3

Stage: UDM3

UDM4

User Defined Measure 4

Stage: UDM4

UDM5

User Defined Measure 5

Stage: UDM5

FISCAL_CAL_KEY

Join to CD_FISCAL_CAL: Required

CD_FISCAL_CAL.FISCAL_CAL_CD = Stage.FISCAL_CAL_CD AND

CD_FISCAL_CAL.FISCAL_YEAR = Stage.FISCAL_YEAR AND

CD_FISCAL_CAL.PERIOD_NBR = Stage.FISCAL_PER_CD

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

UDDGEN1

User Defined Degenerate Dimension 1

Stage: UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

Stage: UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

Stage: UDDGEN3

Expenditure of the Asset

sum(TRANS_AMT)

Storeroom Inventory Snapshot Fact <CF_STRM_INV>

Entity Relationship Diagram

Properties

Property

Value

Table Type

Fact

Fact Type

Snapshot

Default Analytic

Work and Asset Analytics

Fields

Column

OBIEE Field

Load

CURRENCY_CD

Currency Code

Stage: CURRENCY_CD

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

SNAPSHOT_DATE_KEY

Join to CD_DATE: Required

CD_DATE.CAL_DT = Stage.SNAPSHOT_DATE

FACT_CNT

Storeroom Snapshot Count = 1

1

OP_ACCT_KEY

Join to CD_OP_ACCT: Required

CD_OP_ACCT.SRC_OP_ACCT_ID = Stage.OP_ACCT_ID

OP_UOM_KEY

Join to CD_OP_UOM: Required

CD_OP_UOM.OP_UOM_CD = Stage.OP_UOM_CD

QTY_ON_DEMAND

Quantity On Demand

Stage: QTY_ON_DEMAND

QTY_ON_HAND

Quantity On Hand

Stage: QTY_ON_HAND

QTY_OVER_MAX

Quantity Over Max

Stage: QTY_OVER_MAX

STOCK_ITMTY_KEY

Join to CD_STOCK_ITMTY: Required

CD_STOCK_ITMTY.SRC_STOCK_ITMTY_ID = Stage.STOCK_ITEMTY_ID

STRM_INV_KEY

SPL_STRM_INV_SEQ.NEXTVAL

STRM_KEY

Join to CD_STRM: Required

CD_STRM.SRC_STRM_ID = Stage.STRM_ID

STRM_INV_UDD1_KEY

Join to CD_STRM_INV_UDD1: Optional

CD_STRM_INV_UDD1.UDD1_CD(+) = Stage.UDD1_CD

STRM_INV_UDD2_KEY

Join to CD_STRM_INV_UDD2: Optional

CD_STRM_INV_UDD2.UDD2_CD(+) = Stage.UDD2_CD

SNAP_TYPE_CD

Join to CD_SNAP_TYPE: Required

CD_SNAP_TYPE.SNAP_TYPE_CD = Stage.SNAP_TYPE_CD

VALUE_OVER_MAX

Value Over Max

Stage: VALUE_OVER_MAX

TOTAL_VALUE

Total Value

Stage: TOTAL_VALUE

UDM1

User Defined Measure 1

Stage: UDM1

UDM2

User Defined Measure 2

Stage: UDM2

UDM3

User Defined Measure 3

Stage: UDM3

UDM4

User Defined Measure 4

Stage: UDM4

UDM5

User Defined Measure 5

Stage: UDM5

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

UDDGEN1

User Defined Degenerate Dimension 1

Stage: UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

Stage: UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

Stage: UDDGEN3

Number of Items

SUM(FACT_CNT)

Storeroom Transaction Fact <CF_STRM_TR>

Entity Relationship Diagram

Properties

Property

Value

Table Type

Fact

Fact Type

Transaction

Default Analytic

Work and Asset Analytics

Fields

Column

OBIEE Field

Load

STRM_TR_KEY

SPL_STRM_TR_SEQ.NEXTVAL

CURRENCY_CD

Currency Code

Stage: CURRENCY_CD

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

TRANS_DT_KEY

Join to CD_DATE: Required

CD_DATE.CAL_DT = Stage.TRANS_DT

FACT_CNT

Number of Stock Outs

1

OP_UOM_KEY

Join to CD_OP_UOM: Required

CD_OP_UOM.OP_UOM_CD = Stage.OP_UOM_CD

STOCK_ITMTY_KEY

Join to CD_STOCK_ITMTY: Required

CD_STOCK_ITMTY.SRC_STOCK_ITMTY_ID = Stage.STOCK_ITMTY_ID

STOCK_OUT_IND

Stock Out Indicator ('N' = 0, "Y" = 1)

Stage: STOCK_OUT_IND

STRM_KEY

Join to CD_STRM: Required

CD_STRM.SRC_STRM_ID = Stage.STRM_ID

SRC_STRM_TR_ID

Storeroom Transaction ID

Stage: STRM_TR_ID

STRM_TR_TY_KEY

Join to CD_STRM_TR_TY: Required

CD_STRM_TR_TY.STRM_TR_TY_CD = Stage.STRM_TR_TY_CD

STRM_TR_UDD1_KEY

Join to CD_STRM_TR_UDD1: Optional

CD_STRM_TR_UDD1.UDD1_CD(+) = Stage.UDD1_CD

STRM_TR_UDD2_KEY

Join to CD_STRM_TR_UDD2: Optional

CD_STRM_TR_UDD2.UDD2_CD(+) = Stage.UDD2_CD

TRANS_AMT

Transaction Amount

Stage: TRANS_AMT

TRANS_QTY

Transaction Quantity

Stage: TRANS_QTY

UDM1

User Defined Measure 1

Stage: UDM1

UDM2

User Defined Measure 2

Stage: UDM2

UDM3

User Defined Measure 3

Stage: UDM3

UDM4

User Defined Measure 4

Stage: UDM4

UDM5

Stage: UDM5

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

UDDGEN1

User Defined Degenerate Dimension 1

Stage: UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

Stage: UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

Stage: UDDGEN3

Work Order Task Fact <CF_WRKORD_TK>

Entity Relationship Diagram

Properties

Property

Value

Table Type

Fact

Fact Type

Transaction

Default Analytic

Work and Asset Analytics

Fields

Column

OBIEE Field

Load

WRKORD_TK_KEY

SPL_WRKORD_TK_SEQ.NEXTVAL

OP_ACCT_KEY

Join to CD_OP_ACCT: Required

CD_OP_ACCT.SRC_OP_ACCT_ID = Stage.OP_ACCT_ID

ASSET_KEY

Join to CD_ASSET: Optional

CD_ASSET.SRC_ASSET_ID(+) = Stage.ASSET_ID

CREW_KEY

Join to CD_CREW: Optional

CD_CREW.SRC_CREW_ID(+) = Stage.CREW_ID

WRKORD_TY_KEY

Join to CD_WRKORD_TY: Required

CD_WRKORD_TY.WRKORD_TY_CD = Stage.WRKORD_TY_CD

FINISH_DT_KEY

Join to CD_DATE: Required

CD_DATE.CAL_DT = Stage.FINISH_DATE

FAILURE_KEY

Join to CD_FAILURE: Optional

CD_FAILURE.SRC_FAILURE_ID(+) = Stage.FAILURE_ID

CLOSE_DT_KEY

Join to CD_DATE: Optional

CD_DATE.CAL_DT(+) = Stage.CLOSE_DATE

PLANNER_KEY

Join to CD_PLANNER: Optional

CD_PLANNER.SRC_PLANNER_ID(+) = Stage.PLANNER_ID

REPAIR_KEY

Join to CD_REPAIR: Optional

CD_REPAIR.SRC_REPAIR_ID(+) = Stage.REPAIR_ID

ROOT_CAUSE_KEY

Join to CD_ROOT_CAUSE: Optional

CD_ROOT_CAUSE.SRC_ROOT_CAUSE_ID(+) = Stage.ROOT_CAUSE_ID

WRKORD_TK_UDD1_KEY

Join to CD_WRKORD_TK_UDD1: Optional

CD_WRKORD_TK_UDD1.UDD1_CD(+) = Stage.UDD1_CD

WRKORD_TK_UDD2_KEY

Join to CD_WRKORD_TK_UDD2: Optional

CD_WRKORD_TK_UDD2.UDD2_CD(+) = Stage.UDD2_CD

ACT_TOT_AMT

Actual Total Amount

Stage: ACT_TOT_AMT

EST_TOT_AMT

Estimated Total Amount

Stage: EST_TOT_AMT

ACT_ADJUST_AMT

Actual Adjustment Amount

Stage: ACT_ADJUST_AMT

ACT_LBR_AMT

Actual Labor Amount

Stage: ACT_LBR_AMT

ACT_LBR_HOURS

Actual Labor Hours

Stage: ACT_LBR_HOURS

ACT_MAT_AMT

Actual Materials Amount

Stage: ACT_MAT_AMT

ACT_OTHER_AMT

Actual Other Amount

Stage: ACT_OTHER_AMT

ACT_SERVCON_AMT

Actual Contract Services Amount

Stage: ACT_SERVCON_AMT

CURRENCY_CD

Currency Code

Stage: CURRENCY_CD

DATA_SOURCE_IND

Stage: DATA_SOURCE_IND

EST_LBR_AMT

Estimated Labor Amount

Stage: EST_LBR_AMT

EST_LBR_HOURS

Estimated Labor Hours

Stage: EST_LBR_HOURS

EST_MAT_AMT

Estimated Materials Amount

Stage: EST_MAT_AMT

EST_OTHER_AMT

Estimated Other Amount

Stage: EST_OTHER_AMT

EST_SERVCON_AMT

Estimated Con tract Services Amount

Stage: EST_SERVCON_AMT

FACT_CNT

Work Order Task Count = 1

1

LATE_SW

Late Code = ( 1 on late task else 0 )

Stage: LATE_SW

SRC_WRKORD_TK_ID

Source Work Order Task ID

Stage: WRKORD_TK_ID

UDM1

Days Late

Stage: UDM1

UDM2

Days to close

Stage: UDM2

UDM3

Scheduled Downtime Indicator

Stage: UDM3

UDM4

User Defined Measure 4

Stage: UDM4

UDM5

User Defined Measure 5

Stage: UDM5

JOB_NBR

Job Number

B1_ETL_JOB_CTRL.JOB_NBR

UDDGEN1

User Defined Degenerate Dimension 1

Stage: UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

Stage: UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

Stage: UDDGEN3

Average Time to Complete

sum(UDM2) / nullif( count(SRC_WRKORD_TK_ID) , 0)

Average Labor Hours

sum(ACT_LBR_HOURS) / nullif( count(SRC_WRKORD_TK_ID) , 0)

Cost

sum(ACT_TOT_AMT)

Number Of Assets

count(CD_ASSET.ASSET_CD)

Proactive Maintenance

sum(case when WRKORD_TY_DESCR = 'Preventive Maintenance' then 1 else 0 end )

Reactive Maintenance

sum(case when WRKORD_TY_CD <> 'P' then 1 else 0 end )

Proactive %

sum(case when CD_WRKORD_TY.WRKORD_TY_DESCR = 'Preventive Maintenance' then 1 else 0 end ) * 100.0 / nullif( sum(case when CD_WRKORD_TY.WRKORD_TY_DESCR = 'Preventive Maintenance' then 1 else 0 end ) + sum(case when CD_WRKORD_TY.WRKORD_TY_CD <> 'P' then 1 else 0 end ) , 0)

Mean Time To Repair (hours)

avg(ACT_LBR_HOURS)

Work Order Count

count(SRC_WRKORD_TK_ID)

Total Labor Hours

sum(ACT_LBR_HOURS)