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