Premise Installation and Contract

The Premise, Installation and Contract Analytic Model contains document header and item data enhanced with calculated key figures based on Count Distinct.

The following lineage diagram shows the entities described here:

The following utilities categories are included and are sourced for this view:

  • Device Rate Data

  • Contract

  • Premise

  • Data Extractor for Installation History

The following table lists all the Count Distinct calculations in the model:

Calculated Column

Transformation Rule

Number of Contract

Count Distinct(Contract Dim)

Number of Installation

Count Distinct(Installation Dim)

Number of Person

Count Distinct(Person Dim)

Number of Premise

Count Distinct(Premise Dim)

Number of Purchase

Count Distinct(Purchase Dim)

The following table lists all the Date dimensions in the model converted from String format:

Calculated Column

Formula

Validity End Date of Installation

TO_DATE(UtilsInstHistValidityEndDate,'YYYYMMDD')

Validity Start Date of Installation

TO_DATE(UtilsInstHistValidityStartDate,'YYYYMMDD')

Record Creation Date

TO_DATE(UtilsContractCreationDate, 'YYYYMMDD')

Move In

CASE WHEN UtilitiesMoveInDate='00000000'

THEN '19000101'

ELSE TO_DATE(UtilitiesMoveInDate,'YYYYMMDD')

END

Move Out

CASE WHEN UtilitiesMoveOutDate='00000000'

THEN '19000101'

ELSE TO_DATE(UtilitiesMoveInDate,'YYYYMMDD')

END

Validity Start Date Device Rate Data

TO_DATE(UtilsDvceRateDataValdtyStrtDte,'YYYYMMDD')

Validity End Date Device Rate Data

TO_DATE(UtilsDvceRateDataValdtyEndDte,'YYYYMMDD')

Installation date

CASE WHEN UtilsDeviceInstallationDate='00000000'

THEN NULL

ELSE TO_DATE(UtilsDeviceInstallationDate,'YYYYMMDD')

END

Device removal date

CASE WHEN UtilsDeviceRemovalDate='00000000'

THEN NULL

ELSE TO_DATE(UtilsDeviceRemovalDate,'YYYYMMDD')

END

Date of last lead sealing

CASE WHEN UtilsDeviceLastLeadSealingDate='00000000'

THEN NULL

ELSE TO_DATE(UtilsDeviceLastLeadSealingDate,'YYYYMMDD')

END

Last changed on

CASE WHEN LastChangeDate='00000000'

THEN NULL

ELSE TO_DATE(LastChangeDate,'YYYYMMDD')

END

Acquisition date

CASE WHEN AcquisitionDate='00000000'

THEN NULL

ELSE TO_DATE(AcquisitionDate,'YYYYMMDD')

END

Start-up date of the technical object

CASE WHEN OperationStartDate='00000000'

THEN NULL

ELSE TO_DATE(OperationStartDate,'YYYYMMDD')

END

Valid-from date

TO_DATE(ValidityStartDate,'YYYYMMDD')

Valid to date

TO_DATE(ValidityEndDate,'YYYYMMDD')

Date of last goods movement

CASE WHEN MatlSrlNmbrLastGdsMvtDte='00000000'

THEN NULL

ELSE TO_DATE(MatlSrlNmbrLastGdsMvtDte,'YYYYMMDD')

END

End-of-use date of the technical object

CASE WHEN MatlSrlNmbrLastGdsMvtDte='00000000'

THEN NULL

ELSE TO_DATE(MatlSrlNmbrLastGdsMvtDte,'YYYYMMDD')

END