Data Dictionary

Modified on Fri, 06 May 2022 at 02:10 PM

Revisions

Date
Description
02/24/2022
  • All references to dim_customitem updated to dim_customitemanswers
01/11/2022
  • Added auditquestiongroup, auditquestiongroupid to fact_auditquestion
09/13/2021
01/03/2021
  • Score replaces PointValue column in fact_auditanswer. PointValue is now deprecated and will be removed.
  • AuditorVerifyDate is now deprecated and will be removed.
05/14/2021
  • Added dim_user.withoutemail
  • Added fact_auditanswer.passfailansweroption

dim_address

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1address_idintegerNo
PK
2address1stringYes255
3address2stringYes255
4citystringYes255
5statestringYes255
6postalcodestringYes30
7country_idintegerYes

8province_idintegerYes

9tenant_idintegerYes
FK tenant
10sourcerecord_idintegerNo

dim_artifact

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1artifact_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3storageproviderintegerNo

4storagecontainerstringNo50
5storagekeystringNo355
6filenamestringYes355
7filesizebigintNo

8fileformat_idintegerNo

9is_transparentimagebooleanYes

10sourcerecord_idintegerYes

11auditanswerartifact_idintegerYes

12auditanswer_idintegerYes
FK fact_auditanswer
13mitigationartifact_idintegerYes

14mitigation_idintegerYes
FK fact_mitigationactivity
15documentartifact_idintegerYes

16document_idintegerYes

FK dim_document

17descriptionstringYes255
18urlstringYes255
19auditquestionartifact_idintegerYes

20auditquestion_idintegerYes
FK fact_auditquestion
21capaartifact_idintegerYes

22capaevent_idintegerYes
FK fact_capaevent

dim_auditcategory

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1auditcategory_idintegerNo
PK
2namestringNo50
3is_enabledbooleanYes

4tenant_idintegerYes
FK tenant
5is_lpabooleanYes

6is_readonlybooleanYes

7baseauditsubtype_idintegerYes

8is_suppliertemplatebooleanYes

9sourcerecord_idintegerYes

dim_auditquestiontag

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1auditquestiontag_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3auditquestion_idintegerNo
FK fact_auditquestion
4tag_idintegerNo
FK dim_tag
5sourcerecord_idintegerYes

dim_calendarevent

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1calendarevent_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3calendar_idintegerNo

4eventtypeintegerNo

0 = All,

1 = TimeOff,

2 = EmployeeTimeOff,

3 = Audit,

4 = OrgLayer

5eventdetailsintegerNo

6titlestringYes255
7descriptionstringYes255
8alldaybooleanNo

9starttimestampYes
UTC
10duetimestampYes
UTC
11is_deletedbooleanNo

dim_capaeventapprover

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capaeventapprover_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3capaevent_idintegerNo
FK fact_capaevent
4user_idintegerNo
FK dim_user
5approvalstatusintegerNo

6commentstringYes1000
7is_deletedbooleanNo

8deletedbyintegerYes
FK dim_user
9datedeletedtimestampYes
UTC

dim_capaeventassociation

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capaeventassociation_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3capaevent_idintegerNo
FK fact_capaevent
4capaeventassociationtypeintegerNo

0 = None,

1 = Audit,

2 = Location,

3 = Customer

5is_requiredbooleanNo

6namestring255

7auditevent_idintegerYes
FK fact_auditevent
8companysite_idintegerYes
FK dim_company
9location_idintegerYes
FK dim_location

dim_capaeventassociationsetup

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capaeventassociationsetup_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3documentrev_idintegerYes
FK dim_documentrev
4capaeventassociationtypeintegerNo

0 = None,

1 = Audit,

2 = Location,

3 = Customer

5is_requiredbooleanNo

6namestringYes255
7capaevent_idintegerYes
FK fact_capaevent
8is_enabledbooleanNo

dim_capaeventlineitem

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capaeventlineitem_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3capatask_idintegerNo
FK dim_capatask
4sequencenumintegerNo

5capaeventidintegerNo
FK fact_capaevent
6mitigationactivity_idintegerNo
FK fact_mitigationactivity
7is_lockedbooleanNo

dim_capaeventowner

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capaeventowner_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3capavent_idintegerNo
FK fact_capaevent
4user_idintegerNo
FK dim_user
5reviewstatusintegerNo

0 = Open,

1 = InReview,

2 = Approved,

3 = Rejected

6is_deletedintegerNo

7deletedbyintegerYes
FK dim_user
8datedeletedtimestampYes
UTC

dim_capasetting

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capasetting_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3documentrev_idintegerNo
FK dim_documentrev
4is_duedaterequiredbooleanNo

5is_severityrequiredbooleanNo

6is_escapepointrequiredbooleanNo

7is_escapevaluerequiredbooleanNo

8is_siterequiredbooleanNo

9enableseveritybooleanNo

10enableescapepointbooleanNo

11enableescapevaluebooleanNo

12severitycustomgroup_idintegerYes
FK dim_customgroup
13escapepointcustomgroup_idintegerYes
FK dim_customgroup
14escapevaluecustomgroup_idintegerYes
FK dim_customgroup

dim_capatask

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capatask_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3titlestringYes500
4descriptionstringYes500
5duedatetimestampYes
UTC
6is_attachementrequiredbooleanNo

7is_approvalrequiredbooleanNo

8enablenotifynexttaskownerbooleanNo

9enablenotifycapaownerbooleanNo

10mitigationactivity_idintegerYes
FK fact_mitigationactivity
11is_deletedbooleanNo

12deletedbyintegerYes
FK dim_user
13datedeletedtimestampYes
UTC
14is_validationbooleanNo

15is_duetodaynotifiedbooleanNo

16daysdueafterreopenedintegerYes

dim_company

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1company_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3namestringNo255
4is_deletedbooleanNo

5mappedbbyintegerYes
FK dim_user
6mappedontimestampYes
UTC
7sourcerecord_idintegerYes

dim_companysite

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1companysite_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3siteinfo_idintegerNo
FK dim_siteinfo
4company_idintegerNo
FK dim_company
5namestringNo255
6is_supplierbooleanNo

7is_customerbooleanNo

8is_deletedbooleanNo

9mappedbyintegerYes
FK dim_user
10mappedontimestampYes
UTC
11sourcerecord_isintegerYes

dim_contact

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1contact_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3site_idintegerNo
FK dim_siteinfo
4namestringYes255
5jobtitlestringYes255
6workphonestringYes50
7cellphonestringYes50
8detailsstringYes255
9address_idintegerYes
FK dim_address
10company_idintegerYes
FK dim_company
11companysite_idintegerYes
FK dim_companysite
12is_primarycontactatsitebooleanNo

13is_primarycontactatcompanybooleanNo

14is_deletedbooleanNo

dim_customcategory

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customcategory_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3namestringYes255
4customgroup_idintegerNo
FK dim_customgroup
5is_deletedbooleanNo

6deletedby_idintegerYes
FK dim_user
7deletedatetimestampYes
UTC
8sourcerecord_idintegerYes

dim_customcategorylineitem

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customcategorylinteitem_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3customcategory_idintegerNo
FK dim_customcategory
4customitemanswers_idintegerNo
FK dim_customitemanswers

dim_customfield

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customfield_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3fieldtype_idintegerNo
enum
4namestringYes500
5entity_idintegerYes
???
6entitytype_idintegerYes

0 = Undefined,

1 = Audit,

2 = AuditQuestion,

3 = AuditAnswer,

4 = Job,

5 = Customer,

6 = User,

7 = MitigationActivity,

8 = Shift,

9 = ShiftManager,

10 = Site,

11 = Location,

12 = OrgLayer,

13 = Department,

14 = Scope,

15 = UserRole,

16 = Rank,

17 = IssueClassification,

18 = CustomCategory,

19 = CustomItem,

20 = DocumentRev,

21 = CapaEvent,

22 = CapaEventOwner,

23 = CapaEventApprover,

24 = CapaArtifact,

25 = CustomFieldValue,

26 = CapaTask,

27 = Document,

28 = MitigationArtifact,

29 = CapaEventAssociation


entitysubtypeintegerYes
???
8entityfilterstringYes255
9restrictentitystringYes255
10restrictrangestringYes255
11restricttogroupintegerYes
???
12restricttocategoryintegerYes
???
13cardinalitystringYes1
14is_DeletedbooleanYes

15deletedby_idintegerYes
FK dim_user
16deletedatetimestampYes
UTC
17nullablebooleanYes

18is_requiredbooleanYes

19sourcerecord_idintegerYes

dim_customfieldvalue

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customfieldvalue_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3parententitytype_idintegerYes
???
4parententity_idintegerYes
???
5entitytype_idintegerYes

0 = Undefined,

1 = Audit,

2 = AuditQuestion,

3 = AuditAnswer,

4 = Job,

5 = Customer,

6 = User,

7 = MitigationActivity,

8 = Shift,

9 = ShiftManager,

10 = Site,

11 = Location,

12 = OrgLayer,

13 = Department,

14 = Scope,

15 = UserRole,

16 = Rank,

17 = IssueClassification,

18 = CustomCategory,

19 = CustomItem,

20 = DocumentRev,

21 = CapaEvent,

22 = CapaEventOwner,

23 = CapaEventApprover,

24 = CapaArtifact,

25 = CustomFieldValue,

26 = CapaTask,

27 = Document,

28 = MitigationArtifact,

29 = CapaEventAssociation

6entity_idintegerYes
???
7customfieldgroup_idintegerYes
FK dim_customgroup
8groupkeyvalueintegerYes

9customfield_idintegerYes
FK dim_customfield
10fieldvaluestrstringYes200
11fieldvaluenumdoubleYes

12fieldvaluedatetimestampYes
UTC
13fieldvaluecustomitem_idintegerYes
FK dim_customitemanswers
14fieldvaluecustomcategory_idintegerYes
FK dim_customcategory
15is_deletedbooleanYes

16sourcerecord_idintegerYes

dim_customgroup

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customgroup_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3namestringNo255
4is_deletedbooleanYes

5deleteby_idintegerYes
FK dim_user
6deletedatetimestampYes
UTC
7is_systemgroupbooleanYes

8systemgrouptypeintegerYes
internal
9sourcerecord_idintegerNo

dim_customitemanswers

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customitemanswers_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3namestringYes500
4is_deletedbooleanNo

5deletedbyintegerYes
FK dim_user
6deletedatetimestampYes
UTC
7sourcerecord_idintegerYes

dim_customitemtag

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1customitemtag_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3customitemanswers_idintegerNo
FK dim_customitemanswers
4tag_idintegerNo
FK dim_tag
5sourcerecord_idintegerNo

dim_department

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1department_idintegerNo
PK
2siteinfo_idintegerNo
FK dim_siteinfo
3namestringYes50
4descriptionstringYes255
5manager_idintegerYes
FK dim_user
6is_deletedbooleanNo

7tenant_idintegerNo
FK tenant
8sourcerecord_idintegerYes

dim_document

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1document_idintegerNo
PK
2namestringYes150
3is_publishedbooleanYes

4tenant_idintegerNo
FK tenant
5scope_idintegerYes

6is_deletedbooleanNo

7locationtierlevelsstringYes255
8documentrev_idintegerYes
FK dim_documentrev
9altmanager_idintegerYes
FK dim_user
10sourcerecord_idintegerYes

dim_documentrev

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1documentrev_idintegerNo
PK
2document_idintegerNo

3part_idintegerYes
internal
4department_idintegerYes
FK dim_department
5createdby_idintegerYes
FK dim_user
6createdontimestampYes
UTC
7effectivefromtimestampYes
UTC
8effectivetotimestampYes
UTC
9inuseby_idintegerYes
internal
10checkedoutby_idintegerYes
internal
11checkedoutontimestampYes
UTC
12originalfilepathstringYes255
13originalfilenamestringYes255
14originalfileformat_idintegerYes
internal
15siteinfo_idintegerNo
FK dim_siteinfo
16requestingpartystringYes255
17manager_idintegerYes
FK dim_user
18documenttype_idintegerYes
Enum: Audit, Corrective Action
19documentsubtypeintegerYes
FK dim_auditcategory
20revintegerNo

21revbyintegerNo
FK dim_user
22revontimestampNo
UTC
23releasestatus_idintegerNo
enum
24changerequest_idintegerYes
internal
25workingdocdata_idintegerYes
internal
26releasedocdata_idintegerYes
internal
27is_deletedintegerYes

28orglayer_idintegerYes
FK dim_orglayer
29tenant_idintegerNo
FK tenant
30isworkingdocbooleanYes

31sourcerecord_idintegerYes

dim_documentteammember

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1documentteammember_idintegerNo
PK
2user_idintegerNo
FK dim_user
3tenant_idintegerNo
FK tenant
4canapprovebooleanNo

5canmodifybooleanNo

6is_deletedbooleanNo

7deletedbyintegerYes
FK dim_user
8datedeletedtimestampYes
UTC
9documentrev_idintegerYes
FK dim_documentrev

dim_documentteammemberapproval

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1documentteammemberapproval_idintegerNo
PK
2documentteammember_idintegerNo
FK dim_documentteammember
3appovaldatetimetampYes
UTC
4documentrev_idintegerNo
FK dim_documentrev
5tenant_idintegerNo
FK tenant
6statusintegerNo
enum
7commentstringYes255
8lastupdatetimestampNO
UTC

dim_location

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1location_idintegerNo
PK
2tenant_idintegerYes
FK tenant
3siteinfo_idintegerYes
FK dim_siteinfo
4locationtype_idintegerNo

0 = None,

1 = Organization,

2 = Division,

3 = Site,

4 = Workgroup,

5 = Workcenter,

6 = Workstation

5namestringYes50
6descriptionstringYes500
7address_idintegerYes
FK dim_address
8is_deletedbooleanYes

9sequenceintegerYes
Deprecated
10deletedby_idintegerYes
FK dim_user
11deletedatetimestamp

UTC
12organizationintegerYes

13divisiontier5integerYes
Self reference to location_id
14divisiontier4integerYes
Self reference to location_id
15divisiontier3integerYes
Self reference to location_id
16divisiontier2integerYes
Self reference to location_id
17divisionintegerYes
Self reference to location_id
18site_idintegerYes
Self reference to location_id
19area_idintegerYes
Self reference to location_id
20workcenter_idintegerYes
Self reference to location_id
21station_idintegerYes
Self reference to location_id

dim_orglayer

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1orglayer_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3numberintegerNo

4is_deletedibooleanYes

dim_orglayersitedetail

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1orglayersitedetailintegerNo
PK
2tenant_idintegerNo
FK tenant
3siteinfo_idintegerNo
FK dim_siteinfo
4orglayer_idintegerYes
FK dim_orglayer
5namestringYes50
6descriptionstringYes255

dim_randomquestionconfig

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1randomquestionconfig_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3numquestionsintegerYes

dim_randomquestiontag

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1randomequestiontag_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3randomquestionconfig_idintegerNo
FK dim_randomquestionconfig
4tag_idintegerNo
FK dim_tag

dim_rank

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1rank_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3namestringYes100
4is_notificationenabledbooleanYes

5resolutionperiodintegerYes

6is_additionalapprovalenabledbooleanYes

7is_defaultbooleanYes

8is_deletedbooleanYes

9descriptionstringYes255
10siteinfo_idintegerYes
FK dim_siteinfo
11sourcerecord_idintegerNo

dim_role

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1role_idintegerNo
PK
2site_idintegerNo
FK dim_siteinfo
3namestringNo30
4descriptionstringYes255
5roletype_idintegerYes
internal
6tenant_idintegerNo
FK tenant

dim_shift

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1shift_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3siteinfo_idintegerNo
FK dim_siteinfo
4sequenceintegerNo

5namestringNo255
6descriptionstringNo255
7sourcerecord_idintegerNo

dim_schedulemaintenance

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1schedulemaintenance_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3siteinfo_idintegerNo
FK dim_siteinfo
4auditevent_idintegerNo
FK fact_auditevent
5auditor_idintegerNo
FK dim_user
6auditor_commentvarcharYes

7approver_idintegerNo
FK dim_user
8approver_commentvarcharYes

9statusintegerNo
0 = Open, 1 = Approved, 2 = Reassigned, 3 = Rejected
10create_datetimestampNo

11reject_datetimestampYes

12last_updatetimestampYes

13request_typeintNo
0 = Missed Audit Delete Request, 1 = Upcoming Audit Delete Request

dim_siteinfo

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1siteinfo_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3codestringYes6
4namestringNo6
5descriptionstringYes255
6is_deletedbooleanNo

7useshiftsbooleanNo

8timezonestringYes255
9createddatetimestampYes
UTC
10sourcerecord_idintegerYes

dim_tag

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1tag_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3labelstringYes255
4is_systemtagbooleanYes

5auditsubtype_idintegerYes
FK dim_auditcategory
6is_deletedbooleanYes

7deletedby_idintegerYes
FK dim_user
8deletedatetimetampYes
UTC
9sourcerecord_idintegerYes

dim_user

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1user_idintegerNo
PK
2siteinfo_idintegerNo
FK dim_siteinfo
3tenant_idintegerNo
FK tenant
4department_idintegerNo
FK dim_department
5scope_idintegerYes

6orglayer_idintegerYes
FK dim_orglayer
7firstnamestringNo100
8lastnamestringNo100
9is_validatedbooleanYes

10is_lockedbooleanYes

11is_deletedbooleanNo

12emailstringYes255
13shift_idintegerYes

14is_usernotifiedbooleanYes

15createdatetimestampYes
UTC
16languagestringYes10
17adminscope_idintegerYes

18deletedby_idintegerYes
Self reference to user_id
19datedeletedtimestampYes
UTC
20is_securitylockedbooleanYes

21failedloginattemptsintegerYes

22sourcerecord_idintegerYes

23withoutemailbooleanYes

dim_userrole

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1userrole_idintegerNo
PK
2user_idintegerNo
FK dim_user
3role_idintegerNo
FK dim_role
4namestringNo30
5tenant_idintegerYes

6sourcerecord_idintegerYes

fact_auditanswer

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1auditanswer_idintegerNo
PK
2auditevent_idintegerNo
FK fact_auditevent
3auditquestion_idintegerNo
FK fact_auditquestion
4auditquestioncategory_idintegerNo

5tenant_idintegerNo
FK tenant
6commentstringYes1000
7auditorverifydatetimestampYes
DEPRECATED
8altmanager_idintegerYes
FK dim_user
9pointvalueintegerYes
DEPRECATED
10hasnonconformancebooleanYes

11issueclassification_idintegerYes

12is_nabooleanYes

13is_passedbooleanYes

14is_failedbooleanYes

15customcategory_idintegerYes
FK dim_customcategory
16customitem_idintegerYes
FK dim_customitemanswers
17scoreintegerYes
Replaced point value, actual score no need for -1
18passfailansweroptionintegerYes

Will replace is_passed, is_failed and is_na.

0 = undefined, 1 = passed, 2 = failed, NA = 3, unanswered = 4

fact_auditevent

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1auditevent_idintegerNo
PK
2tenant_idintegerNo
FK tenant
3descriptionstringYes150
4daterquiredtimestampNo
UTC
5daterequiredlocaltimestampYes
Local site time
6siteinfo_idintegerYes
FK dim_siteinfo
7location_idintegerYes
FK dim_location
8documentrev_idintegerYes
FK dim_documentrev
9document_idintegerYes
FK dim_document
10shiftmanager_idintegerYes
FK dim_user
11statusintegerYes

0 = Not Started,

1 = Complete,

2 = Partially Complete,

3 = Missed,

4 = Completed Late,

5 = Past Due

12hasnonconformancebooleanNo

13is_passedbooleanYes

14calendarevent_idintegerYes
FK dim_calendarevent
15datestartedtimestampYes
UTC
16dateupdatedtimestampYes
UTC
17datecompletedtimestampYes
UTC
18department_idintegerYes
FK dim_department
19auditoruser_idintegerYes
FK dim_user
20percentcompleteintegerYes

21scoreintegerYes

22auditcategoryintegerYes
FK dim_auditcategory
23is_deletedbooleanYes

24is_adhocbooleanYes

25deletedbyuser_idintegerYes
FK dim_user
26deletedatetimestampYes
UTC
27job_idintegerYes
Deprecated
28orglayer_idintegerYes
FK dim_orglayer
29shift_idintegerYes

30assignedbyuser_idintegerYes
FK dim_user
31dateenteredtimestampYes
UTC
32enteredbyuser_idintegerYes
FK dim_user
33companysite_idintegerYes
FK dim_companysite
34graceperioddaysintegerNo

35pastduedaysintegerNo

36is_autogeneratedbooleanYes

37startwindowintegerYes

38sourcerecord_idintegerYes

39timezone_idstringYes255
40auditcompliance_idintegerYes

41auditcompliancestartdatetimestampYes
UTC
42auditcomplianceenddatetimestampYes
UTC
43compliancestartintegerYes

fact_auditquestion

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1auditquestion_idintegerNo
PK
2descriptionstringYes250
3questiontextstringYes1500
4auditcategoryintegerNo
FK dim_auditcategory
5location_idintegerYes
FK dim_location
6pointvalueintegerNo

7weightintegerNo

8is_criticalbooleanYes

9tenant_idintegerYes
FK dim_tenant
10is_genericbooleanYes

11allowauditortoassignaltmanagerbooleanYes

12questiontypeintegerNo

0 = None,

1 = Pass/Fail,

2 = Point Value,

3 = Multiple Choice,

4 = Multi Select,

5 = Text,

6 = Numeric

13passdescriptionstringYes1000
14faildescriptionstringYes1000
15original_idintegerYes
Self reference to auditquestion_id
16customfield_idintegerYes
FK dim_customfield
17photoinputresponseoptionintegerYes

0 = Off,

1 = Optional,

2 = RequiredOnFail,

3 = Required,

99 = Modified

18minimumpassingscoreintegerYes

19mitigationresponseoptionintegerYes

0 = Off,

1 = Optional,

2 = Required,

99 = Modified

20is_deletedbooleanYes

21revintegerYes

22revbyintegerYes
FK dim_user
23is_latestrevboolYes

24auditquestionset_idintegerYes

25revondatetimeYes
UTC
26passingmindoubleYes

27passingmaxdoubleYes

28comparisonintegerYes

29reactionplanstringYes2000
30scope_idintegerYes

31is_passfailtypebooleanYes

32sourcerecord_idintegerYes

33auditquestiongroupidintegerYes

34aduitquestiongroupstringYes2000

fact_capaevent

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1capaevent_idintegerNo
PK
2tenant_idintegerNo
FK dim_tenant
3namestringYes300
4duedatetimestampYes
Utc
5pastduedaysintegerNo

6graceperioddaysintegerNo

7capastatusintegerNo

8dateopenedtimestampYes
UTC
9severitycustomitem_idintegerYes
FK dim_customitemanswers
10escapepointcustomitem_idintegerYes
FK dim_customitemanswers
11esacpevaluecustomitem_idintegerYes
FK dim_customitemanswers
12siteinfo_idintegerNo
FK dim_siteinfo
13createdbyuser_idintegerNo
FK dim_user
14summarystringYes3000
15documentrev_idintegerYes
FK dim_documentrev
16document_idintegerYes
FK dim_document
17capasetting_idintegerNo
FK dim_capasetting
18is_deletedbooleanNo

19deletedbyintegerYes
FK dim_user
20datedeletedtimestamp

UTC
21dateclosedtimestamp

UTC
22validationstatusintegerNo
enum
23is_duetodaynotifiedbooleanNo

24commentsstringYes350

fact_mitigationactivity

#
Column Name
Data Type
Allow Nulls
Max Length
Description
1mitigationactivity_idintegerNo
PK
2tenant_idintegerNo
FK dim_tenant
3siteinfo_idintegerNo
FK dim_siteinfo
4sequenceintegerNo

5responsiblepartyuser_idintegerYes
FK dim_user
6statusintegerNo

0 = Open-On Time,

1 = Open-Past Due,

2 = Pending-On Time,

3 = Pending-Past Due,

4 = Closed-On Time,

5 = Closed-Past Due

7commentstringYes1500
8auditevent_idintegerYes
FK fact_auditevent
9dateopeneddatetimeYes
UTC
10datecloseddatetimeYes
UTC
11auditanswer_idintegerYes
FK fact_auditanswer
12is_pastduebooleanNo

13pastduedaysintegerYes

14rank_idintegerYes
FK dim_rank
15countermeasurecustomitem_idintegerYes
FK dim_customitemanswers
16approvingmanager_idintegerYes

17customfieldvalue_idintegerYes
FK dim_customfieldvalue
18namestringYes300
19location_idintegerYes
FK dim_location
20creator_idintegerYes
FK dim_user
21descriptionstringYes500
22issueclassification_idintegerYes

23mitigationtype_idintegerYes

24approvingpartycommentstringYes1000
25is_deletedbooleanNo

26deletedby_idintegerYes
FK dim_user
27deletedatetimestampYes

UTC

28is_partofcapabooleanYes

29sourcerecord_idintegerNo

30customcategory_idintegerYes
FK dim_customcategory
31customitem_idintegerYes
FK dim_customitemanswers





Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article