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

Field	Example
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

Field	Example
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

Field	Example
TableName	BudgetPeriod
Status	1
Description	Created

Status

Available since 24.03.14