Forms (QTemplates, EventMasterTemplate, EventTemplate)

Users can create their own Forms with CMO. These Forms allow users to enter records against a predefined template with specific sections, questions, and workflows.  The table below is a quick references across the UI, transaction database, and the data warehouse.

 

Below is an example of a Form created within CMO named “DW_Form”.  Please note that in this example it is the 4th revision of the form. 

 

select demt.OriginalEventMasterTemplateId
,demt.EventMasterTemplateName
,det.EventTemplateName
,det.DimEventTemplateId
,det.EventTemplateRevisionNumber
from DimEventMasterTemplate demt
inner join CMO_Sandbox_DW.dbo.DimEventTemplate det
on det.DimEventMasterTemplateId = demt.DimEventMasterTemplateId
where demt.OriginalEventMasterTemplateId = '6187EFEA-F654-4313-B6E9-51FFC6D3C689'

Note: In the DW, DimEventMasterTemplate contains the record for each unique Form while DimEventTemplate contains a record for each Form version, specified by the EventTemplateRevisionNumber.

 

Forms Section (QSections, EventTemplateSections)

Within the CMO transactional database, Sections are referred to as QSection. CMO Forms can be separated into sections, for example an injury Form could have a section on how the incident happened and another section on how the incident was handled.  Forms can be revised to add, modify, and/or delete whole sections, but when a Section is created it is given a unique GUID - the GUID stays the same if a section is modified or edited (not deleted).Different version of each QSection will be releated to each other by this GUID but related to the Form version by its primary key (bigint). 

The image below gives a simple example of a Section structure:

 

Building on the previous query, we join the table DimEventTemplateSection in the DW using DimEventTemplateID from DimEventTemplate and DimEventTemplateId from DimEventTemplateSection. The query is below, and the results follow:

select demt.OriginalEventMasterTemplateId
,demt.EventMasterTemplateName
,det.EventTemplateName
,det_sec.EventTemplateSectionName
,det.DimEventTemplateId
,det.OriginalEventTemplateId
,det.EventTemplateRevisionNumber
,det_sec.DimEventTemplateSectionId
,det_sec.DimParentEventTemplateSectionId

from CMO_Sandbox_DW.dbo.DimEventMasterTemplate demt
inner join CMO_Sandbox_DW.dbo.DimEventTemplate det
on det.DimEventMasterTemplateId = demt.DimEventMasterTemplateId
inner join DimEventTemplateSection det_sec
on det_sec.DimEventTemplateID = det.DimEventTemplateID
where demt.OriginalEventMasterTemplateId = '6187EFEA-F654-4313-B6E9-51FFC6D3C689'
order by det.OriginalEventTemplateId
 

The results returned each section of each version of the Form. We have ordered the results by OriginalEventTemplateId. For sorting based on Section, please refer to the table FactEventTemplateSectionSegmentField in the DW.

 

Form Questions (Question, EventTemplateSegment)

Questions are stored within each Section of a Form. Questions will can contain multiple and different response types. However, the 4th and current revision of our example Form has three Sections, with one Question in each section as illustrated below.

Important Note: Questions are referred to as Segments in the DW.

Within the CMO transaction database Questions are stored in the Question table. Questions are given a unique GUID when created, and different versions of each Question will be related to each other by this GUID but related to the Section version by its primary key (bigint). Adding to the previous query we join DimEventTemplateSegment using DimEventTemplateSectionId from DimEventTemplateSegment and DimEventTemplateSectionId from DimEventTemplateSection.

Please note that a left join is being used to return all Sections, regardless if they have a Segment associated to it.  The results are illustrated below.

select demt.OriginalEventMasterTemplateId
,demt.EventMasterTemplateName
,det.EventTemplateName
,det_sec.EventTemplateSectionName
,det_seg.EventTemplateSegmentLabel
,det.DimEventTemplateId
,det.OriginalEventTemplateId
,det.EventTemplateRevisionNumber
,det_sec.DimEventTemplateSectionId
,det_sec.DimParentEventTemplateSectionId

from CMO_Sandbox_DW.dbo.DimEventMasterTemplate demt
inner join CMO_Sandbox_DW.dbo.DimEventTemplate det
on det.DimEventMasterTemplateId = demt.DimEventMasterTemplateId
inner join DimEventTemplateSection det_sec
on det_sec.DimEventTemplateID = det.DimEventTemplateID
left join DimEventTemplateSegment det_seg
on det_seg.DimEventTemplateSectionId = det_sec.DimEventTemplateSectionId
where demt.OriginalEventMasterTemplateId = '6187EFEA-F654-4313-B6E9-51FFC6D3C689'
order by det.OriginalEventTemplateId 

Note: the “NULL” values, which signify that there were no Segments within the Section. Please also note the last three records correspond with the latest Form version which has three Sections, with a Segment in each.

 

Response (Answer / EventTemplateField)

Answers are ultimately the fields that capture user inputs and information. Within the CMO DW, Answers are referred to as EventTemplateFields. CMO provides many different response types options and examples of the most commonly used types are provided within this document.  EventTemplateFields are given a unique GUID when created, and different versions of each EventTemplateField will be related to each other by this GUID but related to the Segment version by its primary key (bigint).  

Adding to the previous query, we left join DimEventTemplateField using DimEventTemplateSegmentId from DimEventTemplateField and DimEventTemplateSegment.  We again use a left join to include Segments that do not have any EventTemplateFields associated to them.

Please make note of the column named EventTemplateFieldMasterId, which is the GUID for a Field. This GUID will be very important in the following section when we query responses for an Event.

select demt.OriginalEventMasterTemplateId
,demt.EventMasterTemplateName
,det.EventTemplateName
,det_sec.EventTemplateSectionName
,det_seg.EventTemplateSegmentLabel
,det_field.EventTemplateFieldLabel
,det.DimEventTemplateId
,det.OriginalEventTemplateId
,det.EventTemplateRevisionNumber
,det_sec.DimEventTemplateSectionId
,det_sec.DimParentEventTemplateSectionId
,det_seg.DimEventTemplateSegmentId
,det_field.EventTemplateFieldMasterId
from CMO_Sandbox_DW.dbo.DimEventMasterTemplate demt
inner join CMO_Sandbox_DW.dbo.DimEventTemplate det
on det.DimEventMasterTemplateId = demt.DimEventMasterTemplateId
inner join DimEventTemplateSection det_sec
on det_sec.DimEventTemplateID = det.DimEventTemplateID
left join DimEventTemplateSegment det_seg
on det_seg.DimEventTemplateSectionId = det_sec.DimEventTemplateSectionId
left join DimEventTemplateField det_field
on det_field.DimEventTemplateSegmentId = det_seg.DimEventTemplateSegmentId
where demt.OriginalEventMasterTemplateId = '6187EFEA-F654-4313-B6E9-51FFC6D3C689'
order by det.OriginalEventTemplateId

 

 

CMO Events

Events in CMO are any records that have been created against a Form version. Over time, there may be many records created across multiple Form versions.  As explained previously, regardless of the number of revisions that have been created against a Form, every Section, Question, and Answer or Section, Segment, Field as referred to in the DW, has a unique GUID assigned to it.  As a Form evolves with the addition or modification of its components, Events created against a Form version can have different Field values.  Please note that a Field should never change its response type, but its possible values can change. For example, a “Drop Down” will always be a “Drop Down”, but the list of possible values can change over time. Within our example, we have created a response (answer) type for each of the following: 

• Actual Severity 
• Address 
• Button 
• Checkbox 
• Complete Form/Workflow 
• Date
• Drop Down
• Entity
• Heat map
• Hours Worked
• Items / Sub Items
• Number
• Party Involved
• Potential Severity 
• Radio Button
• Single Line Answer
• Status Answer
• Text Multiple Line

In our example, we have created an Event using the latest version our DW_Form and filled out all the Fields. The DW_Form_Event PDF (link provided) with this document contains a PDF report of the Event we are querying, will all its responses. In this section we will create a query to pull the answers from the DW as they are shown in the PDF.  

Note: the following t-SQL is being created to help map relationships between tables and fields in the DW and that t-SQL can be written differently to optimize performance.

The table DimEvent is searched for all Events that have a DimEventTemplateId that belongs to our “DW_Form”.  This top-level information for an Event can be used to join additional information.

 

Event Standard Information

An Event’s standard information can be linked from this main table.  For example, an Event’s Organizational Unit, Entity, Company, and Status can be queried by joining DimOrgUnit, DimEntity, DimBranch, and DimEventStatus, respectively.  Below we have added to the previous query by adding several tables to gather an Event’s Branch, Entity, Auditor’s name, Org. Unit, and Status.

 

Event Section, Section, and Fields

This section illustrated how to join DimEventSection, DimEventSegment, and DimEventField tables to the previous query to pull all the responses available for an Event.  An Event’s Field (DimEventField) record value is a child of a Segment (DimEventSegment) which is a child of a Section (DimEventSection).  This follows the previous logic outlined for Forms/Templates in Section 1 of this document.  Please note the following table which links the Event information for Section, Segment, and Field with the Form information for Section, Segment, and Field, which allows us to pull descriptive information for each. 

Event Data	Form Data
DimEventSection	DimEventTemplateSection
DimEventSegment	DimEventTemplateSegment
DimEventField	DimEventTemplateField

 

Please review the following query:

 

We have joined DimEventSection to DimEvent by DimEventId, DimEventSegment to DimEventSection by DimEventSectionId, DimEventField to DimEventSegment by DimEventSegmentId.  We have also added a nested inner join for Section, Segment, and Field to pull information for each from the Template tables.  The query results are illustrated below.

 

The results of the query give all the Field values for an event, with the corresponding Section and Segment data.  The EventFieldValue in DimEventField contains the answer value for each response.  Depending on the the type of response, the EventFieldValue will be a different format. The database column type is nvarchar(max) and captures any possible CMO value type.  Using the DimEventTemplateFieldTypeId queries can be created using SQL functions (IF/OR, Case) to parse the data into a reportable format.

The Event Fields to Columns section of this document will demonstrate more common queries used to pull and pivot data for Events into a reportable query result.

Event Segment Actions and Findings

CMO Compliance allows creating Actions and Findings in a Segment, as well as creating them independent of Events. For this example, we will focus on Actions and Findings that are part of an Event.  

The table below illustrates the table names for records for Actions and Findings, and their descriptive information. 

Actions	Findings
DimAction	DimFinding
DimActionCategory	DimFindingCategory
DimActionComment	DimFindingComment
DimActionCustomField	DimFindingCustomField
DimActionPriority	DimFindingStatus
DimActionStatus

 

To collect Action and Finding information for an Event, we have added to the previous query, joining DimAction and DimFinding by DimEventSegmentId. Please review the query and illustrated results below.

select de.DimEventId
,de.EventNumber
,de.EventName
,dorg.OrgUnitName
,den.EntityName
,dcu.FirstName + ' ' + dcu.LastName Auditor
,ds.EventStatusName
,db.BranchName
,sec.EventTemplateSectionName
,seg.EventTemplateSegmentLabel
,detf.EventTemplateFieldLabel
,detf.DimEventTemplateFieldTypeId
,def.DimEventFieldId
,def.EventFieldValue
,detf.EventTemplateFieldMasterId
,df.DimFindingId
,df.FindingText
,da.DimActionId
,da.ActionText

from DimEvent de
left join DimBranch db
on db.DimBranchId = de.DimBranchId
left join DimEntity den 
on den.DimEntityId = de.DimEntityId
left join DimOrgUnit dorg 
on dorg.DimOrgUnitTypeId = de.DimOrgUnitId
left join DimCompanyUser dcu
on dcu.DimCompanyUserId = de.DimEventCreatedByUserId
left join DimEventStatus ds
on ds.DimEventStatusId = de.DimEventStatusId

left join DimEventSection sec
    inner join DimEventTemplateSection _sec
    on _sec.DimEventTemplateSectionId = sec.DimEventTemplateSectionId
on sec.DimEventId = de.DimEventId

left join DimEventSegment seg
    inner join DimEventTemplateSegment _seg
    on _seg.DimEventTemplateSegmentId = seg.DimEventTemplateSegmentId
on seg.DimEventSectionId = sec.DimEventSectionId

left join DimEventField def
    inner join DimEventTemplateField detf
    on detf.DimEventTemplateFieldId = def.DimEventTemplateFieldId
on def.DimEventSegmentId = seg.DimEventSegmentId

left join DimFinding df
on df.DimEventSegmentId = seg.DimEventSegmentId

left join DimAction da
on da.DimEventSegmentId = seg.DimEventSegmentId

where de.DimEventTemplateId in 
    (select _det.DimEventTemplateId from DimEventTemplate _det                                 
    inner join DimEventMasterTemplate _demt
    on _demt.DimEventMasterTemplateId = _det.DimEventMasterTemplateId
    where _demt.OriginalEventMasterTemplateId = '6187EFEA-F654-4313-B6E9-51FFC6D3C689')
 

The image below illustrates how one Action and one Finding have been created in an Event’s Segment.

 

 

Event Fields to Columns

This section will demonstrate how to query field values for an Event into columns.  To accomplish this, EventTemplateFieldMasterId is key to creating SELECT statements.  This GUID was mentioned previously as the unique identifier for a specific response.  

Please note that the assumption is that Sections/Segments have not been cloned within the Event, a feature that is available in CMO, but rarely used. Please review the query below.

select de.DimEventId
,de.EventNumber
,de.EventName
,dorg.OrgUnitName
,den.EntityName
,dcu.FirstName + ' ' + dcu.LastName Auditor
,ds.EventStatusName
,db.BranchName
,(select _df.EventFieldValue from DimEventField _df 
inner join DimEventSegment _dseg 
on _dseg.DimEventSegmentId = _df.DimEventSegmentId 
inner join DimEventSection _dsec
on _dsec.DimEventSectionId = _dseg.DimEventSectionId
inner join DimEventTemplateField _detf
on _detf.DimEventTemplateFieldId = _df.DimEventTemplateFieldId
where _dsec.DimEventId = de.DimEventId
    and _detf.EventTemplateFieldMasterId = '8A55E057-4BE6-43F0-ABC8-1ADAA0C8F4B0') Actual_Severity_Answer

,(select _df.EventFieldValue from DimEventField _df 
inner join DimEventSegment _dseg 
on _dseg.DimEventSegmentId = _df.DimEventSegmentId 
inner join DimEventSection _dsec
on _dsec.DimEventSectionId = _dseg.DimEventSectionId
inner join DimEventTemplateField _detf
on _detf.DimEventTemplateFieldId = _df.DimEventTemplateFieldId
where _dsec.DimEventId = de.DimEventId
    and _detf.EventTemplateFieldMasterId = '03D90E36-0475-4445-B208-453FE163E726') Text_Multiple_Line_Response

,(select _detf.EventTemplateFieldLabel from DimEventField _df 
inner join DimEventSegment _dseg 
on _dseg.DimEventSegmentId = _df.DimEventSegmentId 
inner join DimEventSection _dsec
on _dsec.DimEventSectionId = _dseg.DimEventSectionId
inner join DimEventTemplateField _detf
on _detf.DimEventTemplateFieldId = _df.DimEventTemplateFieldId
where _dsec.DimEventId = de.DimEventId
    and _df.EventFieldValue = 'Checked'
    and _detf.EventTemplateFieldMasterId = 'BC20CB78-8FE6-434E-AC5A-A9BBA60B5022') CheckBox_Response

from DimEvent de
left join DimBranch db
on db.DimBranchId = de.DimBranchId
left join DimEntity den 
on den.DimEntityId = de.DimEntityId
left join DimOrgUnit dorg 
on dorg.DimOrgUnitTypeId = de.DimOrgUnitId
left join DimCompanyUser dcu
on dcu.DimCompanyUserId = de.DimEventCreatedByUserId
left join DimEventStatus ds
on ds.DimEventStatusId = de.DimEventStatusId
 

The query pulls the top-level record for an Event from DimEvent and joins tables that contain standard field information.  To pivot the actual answers to each response, a SELECT statement is added for each field, querying for the response value based on the EventTemplateFieldMasterId GUID and the DimEventId. Please note that depending on the response type, EventFieldValue or EventTemplateFieldLabel is selected.  This SQL query provides an example on how to query the data from CMO DW, using t-SQL much more complicated queries can be created using Cross Apply, Temp Tables, SQL Functions, etc. Experienced SQL developer knowledge is required.