Reporting Database Structure

The Registers Gateway populates the reporting database once the consolidation process is completed. The reporting database is a standardized (not asset specific) database design, optimized to report on data quality as well as supporting generic information dashboards such as project execution or operational dashboards.

Reporting Database Tables Design

Most of the tables are prefixed either by EDW (Engineering Data Warehouse) or ISM (Information Standards Manager)

• All tables with the "EDW" prefix contain instance data that describes the loaded objects.

• All tables prefixed by "ISM" contain data relating to the information standard and application configuration as configuered in ISM. These tables enable opportunities to relate and compare actual data found in the "EDW" tables with the prescribed data requirements in ISM

The EDW tables primarily consist of four categories of data objects:

• Document: include drawings, diagrams, datasheets, etc.

• Functional: Functional Tags representing the functional aspects of objects such as motors, pumps, valves, etc.

• General: All types of objects not fitting into the other 3 categories. Typically, things such as Work Orders, Purchase Orders, Functional Locations, Reference Data like Sites, Systems, Process Units, etc.

• Physical: The Physical installed Equipment information

Query EDW Functional Tables

The following is an example of a query for retrieving functional tags and their attributes:

SELECT

F.TenantID

,F.ContextID

,F.ObjectID

,F.ObjectName

,F.ObjectFullName

,F.ObjectDescription

,F.NamingTemplateByNumber

,F.NamingTemplateByNumberAndClass

,F.NamingTemplateElementLookupIssues

,F.CreatedDate ObjectCreatedDate

,F.LastModified ObjectModifiedDate

,FA.AttributeID

,FA.AttributeValue

,FA.AttributeUOM

,FA.CreatedDate AttributeCreatedDate

,FA.LastModified AttributeModifiedDate

FROM [AIM].[EDW_Functional] F

JOIN [AIM].[EDW_Functional_Attributes] FA ON FA.TenantID = F.TenantID and FA.ContextID = F.ContextID and FA.ObjectID = F.ObjectID

Query EDW Documents Tables

The following is an example of a query for retrieving documents and their attributes:

SELECT

Doc.[TenantID]

,Doc.[ContextID]

,Doc.[ObjectID]

,Doc.[ObjectName]

,Doc.[ObjectFullName]

,Doc.[ObjectDescription]

,Doc.[ObjectClass]

,Doc.[NamingTemplateByNumber]

,Doc.[NamingTemplateByNumberAndClass]

,Doc.[NamingTemplateElementLookupIssues]

,Doc.[UpdatedBy]

,Doc.[CreatedDate]

,Doc.[LastModified]

,Att.[AttributeID]

,Att.[AttributeValue]

,Att.[AttributeUOM]

,Att.[AttributeValueConformanceIssues]

FROM [AIM].[EDW_Document] Doc

JOIN [AIM].[EDW_Document_Attributes] Att on Att.TenantID = Doc.TenantID and Att.ContextID = Doc.ContextID and Att.ObjectID = Doc.ObjectID