Data Warehouse Views

Views 1 – Action Columns

S. No	Columns in Views	Description
1	[DimActionId]	Primary Key
2	[ID]	It represents client Id. It can be seen on ID column on Action Register page on CMO Web application.
3	[ImmediateOrgUnit]	Org Unit assigned to Action.
4	[ActionText]	There is an Action Text box for a user to enter text to describe the Action for which it is being created.
5	[Entity]	It describes the branch of an Org Unit (Entity) or Regional Entity or Location or Facility.
6	[ActionPriority]	It describes the priority of Action to be addressed or completed. E.g. High, Medium, Low etc.
7	[Status]	Status of an Action. Ex. Pending, Completed, Overdue etc.
8	[ActionCategory]	This describes the type of Action.
9	[RecordedDate]	Action creation date.
10	[DueDate]	Due date to complete an Action.
11	[CompletedDate]	Action completed date.
12	[Responsible]	Name of responsible person for which an Action is assigned.
13	[Obligation]	This describes an obligation that client must be compliant with.
14	[EntityType]	It describes the type of an Entity. E.g. Facility type or Regional Entity Type.
15	[Question]	Question text of the question for which Action was a created.
16	[Question_Html]	The Question_Html displays question text with all html tags.
17	[Section]	During form configuration, the user can create questions. Multiple questions can be placed under one section. In a form there can be multiple sections.
18	[EventID]	The Client ID for an Event. It can be seen on ID column on Event List page on CMO Web application
19	[EventCatrgories]	It describes the categories in the Event type or Audit type.
20	[EventTypes]	It describes the Audit type or Incident type.
21	[Risk]	It describes the level of threat to a company or an action. E.g. Moderate, High
22	[Level0]	Org Unit. E.g. Org Unit 1
23	[Level1]	Sub Org Unit. E.g. Org Unit 1.1
24	[Level2]	Sub Sub Org Unit. E.g. Org Unit 1.1.1
25	[Level3]	Sub Sub Sub Org Unit. E.g. Org Unit 1.1.1.1
26	[Level4]	Sub Sub Sub Sub Org Unit. E.g. Org Unit 1.1.1.1.1
27	[Level5]	Sub Sub Sub Sub Sub Org Unit. E.g. Org Unit 1.1.1.1.1.1

Tables/Views Used in DW for Action Views and Table Columns with its Corresponding Columns in Action Views

S. No	Tables/Views Used in Actions	Columns in Tables	Columns in Views
1	[dbo].[DimAction]	DimActionId (PK, int, not null)	[DimActionId]
2	[dbo].[DimAction]	[ActionNumber] (nvarchar(255), not null)	[ID]
3	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar (255), not null)	[ImmediateOrgUnit]
4	[dbo].[DimAction]	[ActionText] (nvarchar(max), not null)	[ActionText]
5	[dbo].[DimEntity]	[EntityName] (nvarchar(255), not null)	[Entity]
6	[dbo].[DimActionPriority]	[ActionPriorityName] (nvarchar(max), not null)	[ActionPriority]
7	[dbo].[DimActionStatusInfo]	[StatusName] (nvarchar(255), null)	[Status]
8	[dbo].[DimActionCategory]	[ActionCategoryName] (nvarchar(255), not null)	[ActionCategory]
9	[dbo].[DimAction]	[CreatedDateTime] (datetime, not null)	[RecordedDate]
10	[dbo].[DimAction]	[DueDateTime] (datetime, not null)	[DueDate]
11	[dbo].[DimAction]	[CompletedDateTime] (datetime, null)	[CompletedDate]
12	[dbo].[DimUser]	[FirstName] (nvarchar(255), null) [LastName] (nvarchar(255), null)	[Responsible]
13	[dbo].[DimObligation]	[ObligationDescription] (nvarchar(max), not null)	[Obligation]
14	[dbo].[DimEntityType]	[EntityTypeName] (nvarchar(255), not null)	[EntityType]
15	[dbo].[DimEventTemplateSegment]	[EventTemplateSegmentLabel] (nvarchar(max), null)	[Question]
16	[dbo].[DimEventTemplateSegment]	[EventTemplateSegmentLabelHtml] (nvarchar(max), null)	[Question_Html]
17	[dbo].[DimEventTemplateSection]	[EventTemplateSectionName] (nvarchar(500), null)	[Section]
18	[dbo].[DimEvent]	EventNumber (nvarchar(max), null)	[EventID]
19	[dbo].[DimEventTemplateCategory]	[EventTemplateCategoryName] (nvarchar(max), not null)	[EventCatrgories]
20	[dbo].[DimEventTemplateClass]	[EventTemplateClassName] (nvarchar(max), not null)	[EventTypes]
21	[dbo].[DimAction]	ActionRiskName (nvarchar (max), null)	[Risk]
22	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[Level0]
23	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[Level1]
24	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[Level2]
25	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[Level3]
26	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[Level4]
27	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[Level5]

Note: For Org Unit Levels from 0 – 5, the table columns depend upon [DimOrgUnitHierarchy]

Views 2 – Event Columns

S. No	Columns in Views	Description
1	[DimEventId]	Primary key.
2	[ID]	It represents client Id. It can be seen on ID column on Event Register page on CMO Web application.
3	[OrgUnitName]	It represents the name of a company or a business unit.
4	[EntityName]	It represents the name of a branch of an Org Unit or Regional Entity or Location or Facility.
5	[EventTitle]	Title of an Event.
6	[Form]	Name of the Form/Template which was used to create an Event.
7	[Reporter]	An Event performer, Auditor or Responsible.
8	[DueDate]	Due date to complete an Event/Audit.
9	[EventDate]	It describes Audit date or Inspection date.
10	[EventStartDate]	It describes when an Audit date has begun or an Event has begun.
11	[ActualStartDate]	It describes date when the Event or Audit has begun.
12	[CompletedDate]	Event or Audit completion date.
13	[Score]	Score of an Event. Percentage of an Event completion (based on answered responses and configuration of Sections/Questions/responses of the Event).
14	[Status]	Status of an Event. E.g. Pending, Completed, Overdue etc.
15	[SubStatus]	Sub Status of an Event.
16	[Risk]	Shows name of the Risk assigned to an Event and score of the Risk. E.g. Moderate, High.
17	[EventCategories]	It describes the categories in the Event type or Audit type.
18	[EventTypes]	It describes the Audit type or Incident type.
19	[Severity]	Is shows Severity of event.
20	[Findings]	It shows number of Closed vs Recorded Findings against of an Event.
21	[Actions]	It shows number of Overdue vs Recorded Actions against of an Event.
22	[Classification]	The type of a user, performer, employee, contractor.
23	[PersonsInvolved]	Shows full name of Users (Parties involved for Event).
24	[EventWorkflowSteps]	If workflow is completed then shows “completed” otherwise shows Full Name of user(s) who is (are) responsible for current workflow of step(s).

Tables/Views Used in DW for Event View and Table Columns with its Corresponding Columns in Event View

S. No	Tables/Views Used in Actions	Columns in Tables	Columns in Views
1	[dbo].[DimEvent]	[DimEventId] (PK, int, not null)	[DimEventId]
2	[dbo].[DimEvent]	[EventNumber] (nvarchar(max), null)	[ID]
3	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[OrgUnitName]
4	[dbo].[DimEntity]	[EntityName] (nvarchar(255), not null)	[EntityName]
5	[dbo].[DimEvent]	[EventName] (nvarchar(max), null)	[EventTitle]
6	[dbo].[DimEventTemplate]	[EventTemplateName] (nvarchar(255), not null)	[Form]
7	[dbo].[DimUser]	[FirstName] (nvarchar(255), null) [LastName] (nvarchar(255), null)	[Reporter]
8	[dbo].[DimEvent]	[EventDueDateTime] (datetime, null)	[DueDate]
9	[dbo].[DimEvent]	[EventDateTime] (datetime, null)	[EventDate]
10	[dbo].[DimEvent]	[EventStartDateTime] (datetime, null)	[EventStartDate]
11	[dbo].[DimEvent]	[EventCommencedDateTime] (datetime, null)	[ActualStartDate]
12	[dbo].[DimEvent]	[EventCompletedDateTime] (datetime, null)	[CompletedDate]
13	[dbo].[DimEvent]	[EventScore] (float, null)	[Score]
14	[dbo].[DimEventStatus]	[EventStatusName] (nvarchar(255), not null)	[Status]
15	[dbo].[DimEventSubStatus]	[EventSubStatusName] (nvarchar(255), not null)	[SubStatus]
16	[dbo].[DimEvent]	[EventRiskName] (nvarchar(max), null)	[Risk]
17	[dbo].[DimEventTemplateCategory]	[EventTemplateCategoryName] (nvarchar(max), not null)	[EventCategories]
18	[dbo].[DimEventTemplateClass]	[EventTemplateClassName] (nvarchar(max), not null)	[EventTypes]
19	[dbo].[DimSeverity]	[SeverityName] (nvarchar(4000), not null)	[Severity]
20	[dbo].[DimFinding]		[Findings]
21	[dbo].[DimAction]		[Actions]
22	[dbo].[DimHoursType]	HoursTypeName (nvarchar(300), not null)	[Classification]
23	[dbo].[DimUser]	[FirstName] (nvarchar(255), null) [LastName] (nvarchar(255), null)	[PersonsInvolved]
24	[dbo].[DimEvent] [dbo].[DimEventWorkflow]	[EventCompletedDateTime] (datetime, null) [EventWorkFlowStartedDateTime] (datetime, null) [EventWorkFlowTerminatedDateTime] (datetime, null)	[EventWorkflowSteps]

Note: For Findings and Actions, it calculates counts related to the event based on each column.

Views 3 – Finding Columns

S. No	Columns in Views	Description
1	[DimFindingId]	Primary key.
2	[ID]	It represents client Id. It can be seen on ID column on Findings Register page on CMO Web application.
3	[Description]	Finding text.
4	[InheritedRisk]	It describes about the current risk or base risk
5	[ResidualRisk]	It describes about the migrated risk or remaining risk
6	[TaregtRisk]	It describes about the desired risk or tolerable risk
7	[Recorded]	It is combination of user name who created finding and finding date.
8	[OrgUnitName]	It represents the name of a company or a business unit.
9	[EntityName]	It represents the name of a branch of an Org Unit or Regional Entity or Location or Facility.
10	[FindingCategoryName]	It represents name of a finding category.
11	[FindingParentCategoryName]	It represents name of a finding parent category.
12	[Process]	Process name.
13	[Question]	It is a name of a question in perform event for which finding was created.
14	[Status]	Status of a Finding. E.g. open, accepted, closed etc.
15	[EventCategories]	It describes the categories in the Event type or Audit type.
16	[EventTypes]	It describes the Audit type or Incident type.
17	[Response]	For all provided responses within the question for which finding was created. Note: If user provides value for response it will be shown in this column. When response is empty, it doesn’t show any value.
18	[Actions]	It shows number of actions with specific status recorded against finding.

Tables/Views Used in DW for Finding View and Table Columns with its Corresponding Columns in Finding View

S. No	Tables/Views Used in Actions	Columns in Tables	Columns in Views
1	[dbo].[DimFinding]	DimFindingId (PK, int, not null)	[DimFindingId]
2	[dbo].[DimFinding]	FindingNumber (nvarchar(255), null)	[ID]
3	[dbo].[DimFinding]	FindingText (nvarchar(max), not null)	[Description]
4	[dbo].[DimFinding]	FindingRiskName (nvarchar(max), null)	[InheritedRisk]
5	[dbo].[DimFinding]	[ResidualRiskName] (nvarchar(max), null) [ResidualRiskScore] (nvarchar(max), null)	[ResidualRisk]
6	[dbo].[DimFinding]	[TargetRiskName] (nvarchar(max), null) [TargetRiskScore] (nvarchar(max), null)	[TaregtRisk]
7	[dbo].[DimUser] [dbo].[DimFinding]	[FirstName] [LastName] [FindingDateTime] (datetime, not null)	[Recorded]
8	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[OrgUnitName]
9	[dbo].[DimEntity]	[EntityName] (nvarchar(255), not null)	[EntityName]
10	[dbo].[DimFindingCategory]	[FindingCategoryName] (nvarchar(500), not null)	[FindingCategoryName]
11	[dbo].[DimFindingCategory] [dbo].[BridgeFindingFindingCategory]	[FindingCategoryName] (nvarchar(500), not null) [DimParentFindingCategoryId] (FK, int, not null) [DimFindingCategoryId] (PK, FK, int, not null)	[FindingParentCategoryName]
12	[dbo].[Dim Process]	[ProcessTitle] (nvarchar(max), not null)	[Process]
13	[dbo].[DimEventTemplateSegment]	EventTemplateSegmentLabel (nvarchar(max), null) EventTemplateSegmentLabelHtml (nvarchar(max), null)	[Question]
14	[dbo].[DimFindingStatus]	[FindingStatusName] (nvarchar(max), not null)	[Status]
15	[dbo].[DimEventTemplateCategory]	[EventTemplateCategoryName] (nvarchar(max), not null)	[EventCategories]
16	[dbo].[DimEventTemplateClass]	[EventTemplateClassName] (nvarchar(max), not null)	[EventTypes]
17	[dbo].[DimEventTemplateField]	[DimEventTemplateFieldTypeId] (FK, int, not null)	[Response]
18	[dbo].[DimActionStatusInfo]	[StatusName]	[Actions]

Note: [dbo].[DimActionStatusInfo] is a view.

Views 4 – Obligation Columns

S. No	Columns in Views	Description
1	[DimObligationId]	Primary key.
2	[ObligationNumber]	It represents client ID.
3	[ObligationStatusName]	It represents name of an obligation status.
4	[ResponsibleUserName]	It represents name of a responsible user assigned for an obligation.
5	[ObligationRiskName]	It represents name of an Obligation risk
6	[LicenseAuthorityName]	It represents name of a license authority.
7	[ObligationTypeName]	It represents name of an obligation type.
8	[ObligationDescription]	It describes the type of an obligation.
9	[LastAssessed]	Date time of last modification of applicability status for an obligation.
10	[SourceName]	It represents the name of an obligation source.
11	[OrgUnitName]	It represents the name of org nits.
12	[EntityName]	It represents the name of entities assigned to an obligation.
13	[Topic]	It represents name of a topic assigned to an obligation.

Tables/Views Used in DW for Obligation Views and Table Columns with its Corresponding Columns in Obligation Views

S. No	Tables/Views Used in Actions	Columns in Tables	Columns in Views
1	[dbo].[DimObligation]	[DimObligationId] (PK, int, not null)	[DimObligationId]
2	[dbo].[DimObligation]	[ObligationNumber] (int, not null)	[ObligationNumber]
3	[dbo].[DimObligationStatus]	[ObligationStatusName] (nvarchar(255), not null)	[ObligationStatusName]
4	[dbo].[DimUser]	[FirstName] (nvarchar(255), null) [LastName] (nvarchar(255), null)	[ResponsibleUserName]
5	[dbo].[DimObligation]	[ObligationRiskName] (nvarchar(255), null)	[ObligationRiskName]
6	[dbo].[DimLicenseAuthority]	[LicenseAuthorityName] (nvarchar(4000), not null)	[LicenseAuthorityName]
7	[dbo].[DimObligationType]	[ObligationTypeName] (nvarchar(255), not null)	[ObligationTypeName]
8	[dbo].[DimObligation]	[ObligationDescription] (nvarchar(max), not null)	[ObligationDescription]
9	[dbo].[DimInPlaceObligation]	[LastAssessedDateTime] (datetime2(7), null)	[LastAssessed]
10	[dbo].[DimLicense] [dbo].[DimObligationName]	[LicenseName] (nvarchar(255), null) [ObligationNameTitle] (nvarchar(255), not null)	[SourceName]
11	[dbo].[DimOrgUnit]	[OrgUnitName] (nvarchar(255), not null)	[OrgUnitName]
12	[dbo].[DimEntity]	[EntityName] (nvarchar(255), not null)	[EntityName]
13	[dbo].[DimObligationTopic]	[ObligationTopicName] (nvarchar(max), not null)	[Topic]