BI views

Introduction

The BI views have been divided into five categories: Facts, dimensions, filters, Row level security and Helpers. Some views for fact, dimensions and filters will contain references to Site, Department or Skill category for Row level security purposes

Facts

Used to provide data for the decisionmaking process or kpi’s. Prefix: applv_bi_fact.

applv_bi_factBITarget

Purpose

Get target percentages set for BI

Source

BITarget

Filter

BiTRecStatus >= 0

Fields

Field

Example

Remark

PPC target

0.800000

Percentage Preventive corrective.

PMC target

0.900000

Preventive Maintenance compliance.

Emergency job target

0.100000

Rush jobs.

Status

Available since 2020.R2

Remarks

  • Percentage shown as decimal 0.8 -> 80%

applv_bi_factBudget

Purpose

Approved monthly budgets including approved mutations and the amount it’s being used

Source

Budgetperiod

Filter

BdgpContext = 4 AND BdgpRecStatus = 8 AND BdgpStartDate >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

Year

2018

Period

2018-m04

Date

1-4-2018

Total budget

750.000.000

Budget

550.000.000

Cost

875.000

Difference

749.125.000

Budget left%

0.9988333333333333

Percentage used

0.12

Mutation

200.000.000

BdgpCompId

000001

BdgpCcrId

01

applfn_GetBIIdWithCompany used

Status

Approved

applv_bi_helperUsedStatusDescriptions used

Status

Available since 2020.R2

Remarks

  • Percentage shown as decimal 0.8 -> 80%

  • Amounts are shown in company currency

applv_bi_factCompany

Purpose

Companies and their currency

Source

Company

Filter

None

Fields

Field

Example

Remark

CompId

000001

Currency

EUR

Status

Available since 2020.R2

applv_bi_factCost

Purpose

Incurred costs, categorised by period, object type, category, costs centre, cost type and department

Source

applvw_CcrPrint (Cost)

Filter

CstRecStatus = 2 AND CstBookDate >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

Costs

835.00000

CstContext

8

Cost category

Miscellaneous items

applv_bi_helperUsedContextDescriptions used

Book date

2023-09-26

CstEqmId

FL019

applfn_GetBIIdWithCompany used

CstPrfId

00013

applfn_GetBIIdWithCompany used

CstSitId

05

applfn_GetBIIdWithCompany used,

for Row level security

CstCcrId

06

applfn_GetBIIdWithCompany used

CstCsttId

FL08

CstDepId

06

applfn_GetBIIdWithCompany used,

for Row level security

EqmtId

FL003

From Equipment or Processfunction

CstCompId

000001

CstSkcId

015

applfn_GetBIIdWithCompany used,

for Row level security

Status

Available since 2020.R2

Remarks

  • Amounts are shown in company currency

applv_bi_factJob

Purpose

Reported jobs with dimensions and are they fininished in time

Source

Job

Filter

JobRecStatus >= 0 AND ‘Date reported’ >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

JobId

1001598

JobDepId

07

applfn_GetBIIdWithCompany used,

for Row level security

Date reported

2023-03-02 00:00:00.0000000 +00:00

When JobPmId is not null then COALESCE(job.JobOriginalSchStartDate, job.JobRecCreateDate)

else COALESCE(job.JobReportDate, job.JobRecCreateDate)

applfn_ConvertToBITimeZone used

Scheduled start date

2023-03-02 07:00:00.0000000 +00:00

applfn_ConvertToBITimeZone used

Target date

2023-03-02 16:00:00.0000000 +00:00

applfn_ConvertToBITimeZone used

Date finished

NULL

applfn_ConvertToBITimeZone used

JobCompId

000001

JobWotId

005

JobPriId

M

JobFalId

NULL

JobSitId

06

applfn_GetBIIdWithCompany used,

for Row level security

JobPrfId

000001

applfn_GetBIIdWithCompany used

JobEqmId

39704

applfn_GetBIIdWithCompany used

JobEqmtId

007

JobSkcId

015

applfn_GetBIIdWithCompany used,

for Row level security

JobCcrId

07

applfn_GetBIIdWithCompany used

JobPrsId

064

JobContext

1

Description

Maintenance photocopier

JobExecutionDays

73

Final finish date

2023-05-13 00:00:00.0000000 +00:00

applfn_ConvertToBITimeZone used

Finished on time

0

Only when ‘Final finish date’ has value

Overdue

0

Only when ‘Final finish date’ has value

Days overdue

NULL

Only when ‘Final finish date’ has value

Exceedance

NULL

Only when ‘Final finish date’ has value

Preventive

1

based on WotCategory = 'P’

Emergency job

0

based on PriEmergency

Non Emergency job

1

based on PriEmergency

PM model

00407

JobReportDate

2023-02-28 11:03:00.0000000 +00:00

applfn_ConvertToBITimeZone used

Status

Available since 2020.R2

applv_bi_factJobReportedAndFinished

Purpose

Compare date reported and finished for jobs

Source

Job

Filter

JobRecStatus >= 0 AND ‘Date reported / finished’ >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

JobId

1000012

JobCompId

000001

JobSitId

01

applfn_GetBIIdWithCompany used,

for Row level security

JobDepId

07

applfn_GetBIIdWithCompany used,

for Row level security

JobSkcId

015

applfn_GetBIIdWithCompany used,

for Row level security

Date

2022-12-06 10:18:00.0000000 +00:00

applfn_ConvertToBITimeZone used

JobAction

Reported

Reported or Finished

Status

Available since 2020.R2

applv_bi_factJobWeekLab

Purpose

Compare preventive and corrective hours spend on jobs

Source

Job

Filter

JobRecStatus >= 0 AND COALESCE(JobReportDate, JobRecCreateDate) >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

JobwlJobId

1000047

JobwlCompId

000001

JobwlDepId

07

applfn_GetBIIdWithCompany used,

for Row level security

JobwlSkcId

015

applfn_GetBIIdWithCompany used,

for Row level security

Total hours corrective

0.00

WotCategory = 'C’

Total hours preventive

2.75

WotCategory = 'P’

Total hours maintenance

2.75

WotCategory in (‘C’, 'P’)

Total hours non maintenance

0.00

WotCategory not in (‘C’, 'P’)

Total hours unknown

0.00

WotCategory is null

Total hours

2.75

Status

Available since 2020.R2

Remarks

  • Grouped by jobid, wotcategory, compid, depid, skcid

applv_bi_factObjectDowntime

Purpose

Mean Time to Repair (MTTR), Mean Time Between Failures (MTBF) and Mean Time To Failure (MTTF)

Source

ObjectDowntime

Filter

JobRecStatus >= 0 AND ObjDwtContext = 2 AND ObjDwtEndDate IS NOT NULL AND ObjDwtStartDate >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

Downtime

3.000000

Duration in minutes

180

Production loss

119.999999

MTBF

168

MTTF

165

ObjDwtCompId

000001

ObjDwtAssetFkId

N.EXP.01P

Equipment -> applfn_GetBIIdWithCompany + 'E'

Processfunction -> applfn_GetBIIdWithCompany + 'P'

ObjDwtDepId

05

applfn_GetBIIdWithCompany used,

for Row level security

ObjDwtCcrId

05

applfn_GetBIIdWithCompany used

ObjDwtSitId

02

applfn_GetBIIdWithCompany used,

for Row level security

Start date

2022-05-01 09:00:00.0000000 +00:00

applfn_ConvertToBITimeZone used

JobFalId

US005

JobEqmtId

0020

Repair costs

135.00000

JobAssetFkId

N.EXP.01P

Equipment -> applfn_GetBIIdWithCompany + 'E'

Processfunction -> applfn_GetBIIdWithCompany + 'P'

Total costs

254.999999

Status

Available since 21.01.03

Remarks

  • Amounts are shown in company currency

applv_bi_factPmWorkOrder

Purpose

Determine the end date for periodical maintenance

Source

PmWorkOrder

Filter

PmwRecStatus >= 0

Fields

Field

Example

Remark

PmwId

00542

Execution days

37

PmwRecordStatus

2

PmwDepId

29

applfn_GetBIIdWithCompany used,

for Row level security

PmwSitId

24

applfn_GetBIIdWithCompany used,

for Row level security

PmwSkcId

015

applfn_GetBIIdWithCompany used,

for Row level security

PmwCompId

000001

PmwContext

1024

Context

64

For Pmwcontext 1024 (PeriodicActivityChild) the context is based on backoffice for corresponding Maintenanceclassification.

  • When bofJobContext = 1 -> 1 --TS

  • When bofJobContext = 512 -> 2 --Instrument

  • When bofJobContext = 2048 -> 16 --Building

  • When bofJobContext = 16384 -> 64 --Fleet

  • When bofJobContext = 32768 -> 32768 --Infra

  • When bofJobContext = 64 -> 131072 -- ITKnownError -> IT

  • When bofJobContext = 128 -> 131072 --ITIncident -> IT

  • When bofJobContext = 256 -> 131072 --ITProblem -> IT

  • Else PmwContext

Status

Available since RR (2021.05.11)

applv_bi_factShiftLogLine

Purpose

'Autonomous maintenance performance', the processing performance of maintenance tasks

Source

ShiftLogLine

Filter

ShllContext = 2 AND ShllShlId IS NOT NULL AND ShllRecCreateDate >= UmmgStartDateFinanceYear – 5

Fields

Field

Example

Remark

ShllOriginShllId

0000000000044

applfn_GetBIIdWithCompany used

ShllShlbId

01

applfn_GetBIIdWithCompany used

ShllCompId

000001

ShllSrId

NULL

ShllPrfId

N.PRO.02.008

applfn_GetBIIdWithCompany used

ShllEqmId

39861

applfn_GetBIIdWithCompany used

Date

2022-12-27 10:22:55.0000000 +00:00

pplfn_ConvertToBITimeZone used

Finished on time

1

Finished too late

0

Skipped

0

Applicable for performance dashboard

1

ShllRecStatus = 128 OR ShllRecStatus = 256

Status

Available since RR (2022.04.29)

Dimensions

Used to describe the objects in facts. Prefix: applv_bi_dim.

Remarks

  • To be able to only show dimension relevant for a specific company extra item(s) will be added to the dimensions with a company.

    • A ‘blank’ (null) dimension item will be added per company.

      • Example: ‘BIRWC-Blank-000001’ (Id) for ‘blank’ and company “000001

applv_bi_dimAsset

Purpose

Assets for applv_bi_factPmWorkOrder

Source

Equipment / processfunction

Fields

Field

Example

Remark

AssetId

00003E

Equipment -> applfn_GetBIIdWithCompany + 'E'

Processfunction -> applfn_GetBIIdWithCompany+ 'P'

Asset

Crate washer machine (00003)

Aplfn_GetBIDescriptionWithId used

AssetRecStatus

2

AssetCompId

000001

AssetSitId

02

applfn_GetBIIdWithCompany used,

for Row level security

AssetDepId

05

applfn_GetBIIdWithCompany used,

for Row level security

FilterAssetId

00003E

applfn_GetBIFilterId used

Status

Available since 21.01.03

applv_bi_dimCostCenter

Source

Costcenter

Fields

Field

Example

Remark

CcrId

001

applfn_GetBIIdWithCompany used

Cost centre

Office (001)

applfn_GetBIDescriptionWithId used

CcrRecStatus

2

CcrCompId

000001

FilterCcrId

001

applfn_GetBIFilterId used

Status

Available since 2020 R2

applv_bi_dimCostType

Source

CostType

Fields

Field

Example

Remark

CsttId

001

Cost type

Office (001)

applfn_GetBIDescriptionWithId used

CsttRecStatus

0

FilterCsttId

001

applfn_GetBIFilterId used used

Status

Available since 2020 R2

applv_bi_dimDepartment

Source

Department

Fields

Field

Example

Remark

DepId

01

applfn_GetBIIdWithCompany used

Department

Administration (01)

applfn_GetBIDescriptionWithId used

DepRecStatus

0

DepCompId

000001

FilterDepId

001

applfn_GetBIFilterId used

IsEmpty

0

Status

Available since 2020 R1

applv_bi_dimEqmType

Source

EqmType

Fields

Field

Example

Remark

EqmtId

001

Equipment type

Accessories (000001)

applfn_GetBIDescriptionWithId used

EqmtRecStatus

0

FilterEqmtId

001

applfn_GetBIFilterId used

Status

Available since 2020 R2

applv_bi_dimFailType

Source

FailType

Fields

Field

Example

Remark

FalId

016

Failure cause

Faulty maintenance (016)

applfn_GetBIDescriptionWithId is used

FalRecStatus

0

FilterFalId

016

applfn_GetBIFilterId used

Status

Available since 2020 R1

applv_bi_dimOriginalShiftLogLine

Source

ShiftLogLine

Fields

Field

Example

Remark

ShllId

0000000000050

applfn_GetBIIdWithCompany used

ShllCompId

000001

Maintenance task

Lubricate conveyor belt of Packing line

ShllRecStatus

128

FilterShllId

0000000000050

applfn_GetBIFilterId used

Status

Available since 21.01.03

applv_bi_dimPriority

Source

Priority

Fields

Field

Example

Remark

PriId

01

Priority

High (01)

applfn_GetBIDescriptionWithId used

PriRecStatus

0

Priority emergency

1

Status

Available since 2020 R1

applv_bi_dimProgressStatus

Source

ProgressStatus

Fields

Field

Example

Remark

PrsId

026

Progress status

Registered (026)

applfn_GetBIDescriptionWithId used

PrsRecStatus

0

Status

Available since 2020 R1

applv_bi_dimShiftLogbook

Source

ShiftLogbook

Fields

Field

Example

Remark

ShlbId

02

applfn_GetBIIdWithCompany used

Logbook

Logbook - Purifying installation

ShlbRecStatus

0

ShlbDepId

000001

applfn_GetBIIdWithCompany used,

for Row level security

ShlbSitId

07

applfn_GetBIIdWithCompany used,

for Row level security

ShlbCompId

000001

FilterShlbId

02

applfn_GetBIFilterId used

IsEmpty

0

Status

Available since 21.01.03

applv_bi_dimSite

Source

Site

Fields

Field

Example

Remark

SitId

01

applfn_GetBIIdWithCompany

Site

Office (01)

applfn_GetBIDescriptionWithId

Country

Netherlands

SitRecstatus

0

SitCompId

000001

FilterSitId

01

applfn_GetBIFilterId used

Status

Available since 2020.R1

applv_bi_dimSkillCat

Source

SkillCat

Fields

Field

Example

Remark

SkcId

011

applfn_GetBIIdWithCompany used

Team

Other (011) *

applfn_GetBIDescriptionWithId used

SkcRecstatus

-2147483648

SkcCompId

000001

Status

Available since 2020.R1

applv_bi_dimSkipReason

Source

Site

Fields

Field

Example

Remark

SrId

US01

Reason to skip

Insufficient time (US01)

applfn_GetBIDescriptionWithId

FilterSrId

US01

applfn_GetBIFilterId

SrRecStatus

0

Status

Available since RR (2022.04.29)

applv_bi_dimWorkOrderType

Source

Site

Fields

Field

Example

Remark

WotId

003

Job type

Inspection/Calibration (003)

applfn_GetBIDescriptionWithId

WotCategory

P

Category (job type)

Preventive

WHEN WotCategory = 'P' THEN 'Preventive'

WHEN WotCategory = 'C' THEN 'Corrective'

WHEN WotCategory NOT IN('P', 'C') THEN 'Other'

ELSE 'Unknown'

WotRecstatus

0

FilterWotId

003

applfn_GetBIFilterId used

Status

Available since 2020.R1

Filters

Filter facts / dimension. Prefix: applv_bi_filter.

Remarks

  • To provide extra filtering possibilities extra/replacement items will be added to the filters.

    • For filters with a company a ‘blank’ (null) filter item will be added per company.

      • Example: ‘BIRWC-Blank-000001’ (Id) for ‘blank’ and company “000001

    • When trashed items exist all trashed items will be replaced with one trashed filter item (per company) and the description will be ‘* (Trashed)’

      • Example: ‘ ‘BIRWC-Trashed-000001’ (Id) for ‘trashed’ and company “000001

      • Example: ‘ ‘BIR-Trashed’ (Id) for ‘trashed’ without company

  • Most filters are a representation of their dimension, look at the dimension for extra information (source, used functions, remarks, etc.)

applv_bi_filterAsset

Purpose

Assets for applv_bi_factPmWorkOrder

Source

applv_bi_dimAsset

Fields

Field

Example

FilterAssetId

BIRWC-Trashed-000001E

Asset

* (Trashed)

IsTrashed

1

AssetCompId

000001

Remarks

Could contain two trashed items per company. One for equipment and one for processfunction

Status

Available since 21.01.03

applv_bi_filterCostCenter

Source

applv_bi_dimCostCenter

Fields

Field

Example

FilterCcrId

06

Cost centre

Dispatch department (06)

IsTrashed

0

CcrCompId

000001

Status

Available since 2020R2

applv_bi_filterCostType

Source

applv_bi_dimCostType

Fields

Field

Example

FilterCsttId

010

Cost type

Hardware costs (010)

IsTrashed

0

Status

Available since 2020R2

applv_bi_filterDepartment

Source

applv_bi_dimDepartment

Fields

Field

Example

FilterDepId

05

Department

Dispatch & Logistics (05)

IsTrashed

0

DepCompId

000001

IsEmpty

0

Status

Available since 2020R2

applv_bi_filterEqmType

Source

applv_bi_dimEqmType

Fields

Field

Example

FilterEqmtId

0009

Equipment type

Hydraulic Generators (0009)

IsTrashed

0

Status

Available since 2020R2

applv_bi_filterEquipment

Source

Equipment (joined with equipment via EqmTopOfEqmId)

Fields

Field

Example

Remark

EqmId

00001

applfn_GetBIIdWithCompany used

Equipment

Forklift 1 type 35 (00001)

applfn_GetBIDescriptionWithId used

EqmRecStatus

2

WHEN EqmRecStatus < 0 THEN EqmRecStatus

ELSE topeqm.EqmRecStatus (via EqmTopOfEqmId)

EqmTopOfEqmId

00001

if equals EqmId then it's a top eqm

EqmCompId

000001

FilterEqmId

00001

applfn_GetBIFilterId used

IsEmpty

0

EqmContext

1

Remarks

Best practice is to use applv_bi_filterTopOfEquipment

Status

Available since 2020R1

applv_bi_filterEquipmentContext

Source

applv_bi_helperUsedContextDescriptions

Fields

Field

Example

EqmContext

1

Equipment context

Equipment (1)

Status

Available since RR (2021.12.16)

applv_bi_filterFailType

Source

applv_bi_dimFailType

Fields

Field

Example

FilterFalId

016

Failure cause

Faulty maintenance (016)

IsTrashed

0

Status

Available since 2020R2

applv_bi_filterProcessFunction

Source

ProcessFunction (joined with equipment via PrfTopOfPrfId)

Fields

Field

Example

Remark

PrfId

0.24

applfn_GetBIIdWithCompanyused

Process function

Buildings and Sites (0.24)

applfn_GetBIDescriptionWithIdused

PrfRecStatus

1

WHEN PrfRecStatus < 0 THEN PrfRecStatus

ELSE topPrf.PrfRecStatus (via PrfTopOfPrfId)

PrfTopOfPrfId

002

if equals PrfId then it's a top Prf

PrfCompId

000001

FilterPrfId

002

applfn_GetBIFilterId used used

IsEmpty

0

Remarks

Best practice is to use applv_bi_filterTopOfProcessFunction

Status

Available since 2020R1

applv_bi_filterShiftLogbook

applv_bi_dimShiftLogBook

Fields

Field

Example

FilterShlbId

01

Logbook

Cake line 2

IsTrashed

0

ShlbCompId

000001

Status

Available since 21.01.03

applv_bi_filterShiftLogLine

applv_bi_dimShiftLogLine

Fields

Field

Example

FilterShllId

0000000000038

Maintenance task

Measure noise level near crate washing line

IsTrashed

0

ShllCompId

000001

Status

Available since 21.01.03

applv_bi_filterSite

Source

applv_bi_dimSite

Fields

Field

Example

FilterSitId

01

Site

Office (01)

IsTrashed

0

SitCompId

000001

Status

Available since 2020R2

applv_bi_filterSkipReason

Source

applv_bi_dimSkipReason

Fields

Field

Example

FilterSrId

US01

Reason to skip

Insufficient time (US01)

IsTrashed

0

Status

Available since 21.01.03

applv_bi_filterTopOfEquipment

Source

applv_bi_dimEquipment

Filter

EqmRecStatus >= 0 AND eqmid = EqmTopOfEqmId

Fields

Field

Example

FilterEqmId

00001

Equipment

Forklift 1 type 35 (00001)

IsTrashed

0

EqmCompId

000001

IsEmpty

0

Status

Available since 2020R1

applv_bi_filterTopOfProcessFunction

Source

applv_bi_dimProcessFunction

Filter

PrfRecStatus >= 0 AND Prfid = PrfTopOfPrfId

Fields

Field

Example

FilterPrfId

00001

ProcessFunction

Reception workplace (00001)

IsTrashed

0

PrfCompId

000001

IsEmpty

0

Status

Available since 2020R1

applv_bi_filterWorkOrderType

Source

applv_bi_dimWorkOrderType

Fields

Field

Example

FilterWotId

001

Job type

Administration (001)

IsTrashed

0

Status

Available since 2020R2

Row level security

Used to limit access to data like our record authorization would. Prefix: applv_bi_RLS. Contains all data (dimensions) per user for which he is authorized via record authorization.

Remarks

  • When no record for a user, no record authorization applies, and all data is visible to the user.

  • Data will always show all records that can be seen by the user even when only a subset is used when logging in.

  • Configurational changes to record authorization via workflows will not be shown as they are not in the standard database

  • Only most used dimensions have a view at this moment

applv_bi_RLSDepartment

Source

Standard record authorization settings for user or group (SuserRecordAuthSetting, SuserRecordAuthorization, SgroupRecordAuthSetting and SgroupRecordAuthorization)

Filter

DepRecStatus >= 0

Fields

Field

Example

User

Manager

SiteId

01

Status

Available since 2020R1

applv_bi_RLSSite

Source

Standard record authorization settings for user or group (SuserRecordAuthSetting, SuserRecordAuthorization, SgroupRecordAuthSetting and SgroupRecordAuthorization)

Filter

SitRecStatus >= 0

Fields

Field

Example

User

JohnW

DepId

02

Status

Available since 2020R1

applv_bi_RLSSkillCat

Source

Standard record authorization settings for user or group (SuserRecordAuthSetting, SuserRecordAuthorization, SgroupRecordAuthSetting and SgroupRecordAuthorization)

Filter

SkcRecStatus >= 0

Fields

FieldExample

User

Purchase

SkcId

001

Status

Available since 2020R1

Helpers

Used to help show data in the bi views that cannot (completely) be found in the database

applv_bi_helperUsedContextDescriptions

Purpose

Show English context descriptions for contexts that are used in views for filtering or displaying categories

Source

Standard Context descriptions (as copy from datafile) and custom context descriptions from SRECORDCONTEXTCOUNTRY for tables Equipment and Cost (EN)

Filter

SRECXREC IN ('COST', 'EQUIPMENT' AND SrecxRecStatus >= 0 AND (SRECCCCOU IS NULL OR SRECCCCOU = 'EN')

Fields

FieldExample

TableName

Equipment

Context

512

Description

Instrument

Status

Available since 24.02.21

applv_bi_helperUsedStatusDescriptions

Purpose

Show English status descriptions for contexts that are used in views for displaying categories

Source

Standard status descriptions (as copy from datafile) and custom status descriptions from SRECORDSTATUSCOUNTRY for table BUDGETPERIOD (EN)

Filter

SRESTRECORD = 'BUDGETPERIOD' AND (SRECSCCOU IS NULL OR SRECSCCOU = 'EN')

Fields

FieldExample

TableName

BudgetPeriod

Status

1

Description

Created

Status

Available since 24.03.14

PreviousBI functionsNextTerms & Conditions

Last updated