Skip to main content
Mitratech Success Center

Data Warehouse Views

Data Warehouse Views

Data Warehouse (DW) Description

It is a Management Data Warehouse which is a form of a database which contain data collected from a sever. All the data is maintained in the form of tables which contain schema. This data is used for generating custom reports for an Organization for in depth data analysis.

CMO Data Warehouse is hosted on SQL server.

Views in Data Warehouse

Views in the Data Warehouse represent the data which a user can see in the CMO web application. Currently, there are four Views in the CMO Data Warehouse which are as follows:

  1. Action
  2. Event
  3. Finding
  4. Obligation

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]

 

 

  • Was this article helpful?