Honeywell ACS ACT -- Master Thesis DIM_PROJECT Example data model information workbook
Table Name DIMDATE
Table Type Dimension
View Name Date
Display Name Date
Description Date dimension brings time context. Generated before data are transferred, until the end of 2017.
Used in schemas All
Target Source
Column Name Description Datatype Size Precision Key? FKTo NULL? Default
Value
Example Values SCD
Type
Source
System
Source
Schema
Source Table Source Field
Name
Source Datatype ETL Rules Comments
sk Surrogate primary key smallint PK N 1, 2,3... Sequence Standard surrogate key
day Date of the day (without time
information)
Date NK N 31-03-2011 1 Generated Pregenerated Sigle row for every day of a year.
Must cover both existing historical
dates and enough future dates. The
day is without time information.
day_of_week day in a week in numerical
reresentation, from Monday (1) to
Sunday (7)
int N 1,2,3, 4, 5,6,7 0 Derived this day
d ay_of_week_name Name of a day of week, 'Monday'
to 'Sunday'
varchar 10 N Monday, Tuesday, 0 Derived this day_of_week nvarchar(50)
week week number in year int N 1,2,...,52 0 Derived this day nvarchar(50)
month month number in year int N 1,2,...,12 0 Derived this day
monthname month name varchar 10 N January,
December
0 Derived this month
quarter quarter number in year int N 1,2,3, 4 0 Derived this day
quarter_name quarter name varchar 2 N Q1,Q2,Q3, Q4 0 Derived this quarter
year year int N 2008, 2009, ... 0 Derived this day
26/5/2014
Data Warehouse Lifecycle Toolkit, 2nd Edition
Copyright 2008, Kimball Group Page 1 of 4
Honeywell ACS ACT -- Master Thesis
Table Name DIM_PROJECT
Table Type Dimension
View Name Project
Display Name Project
Description Identify project (and its hierarchy) where the defect belongs to.
Used in Schemas All
DIM_PROJECT Example data model information workbook
Target Source
Column Name Description Datatype Size Precision Key? FK To NULL? Default Value Example Values SCD
Type
Source
System
Source
Schema
Source Table Source Field
Name
Source
Datatype
ETL Rules Comments
sk Surrogate primary key smallint PK N Generated 1,2, 3... Sequence Standard surrogate key
project_name The project where the defect
belongs to.
varchar 255 NK N Not specified TotalConnect,
Vista Turbo, ACS
JIRA Extensions,
1 JIRA6-DEV dbo project pname nvarchar(255) If NULL or Empty => error
log record, use default
value
LOB Line of Business, where the
project belongs to.
varchar Not specified AP Lighting, CCS
NA, Combustion
NA, Homes NA, ...
1 STUDIO-
DEV
dbo
SBU Strategic Business Unit, where
the LOB or Physical site belongs
to.
varchar 10 Not specified ECC, HBS, HLS,
HPS, HSG, HSM,
SC
1 STUDIO-
DEV
dbo
physical_site Physical site, where the project
belongs to.
varchar Not specified A sample
description of
project.
1 STUDIO-
DEV
dbo
project_description The project description varchar MAX Not specified A project instance
description text.
1 JIRA6-DEV dbo project DESCRIPTION ntext Convert to nvarchar(4000)
and trim if needed; if
NULL or EMPTY => use
default value
N PI n umber The NPI (New Product
Introduction) project the issue
relates to.
varchar Not specified 12345 1 JIRA6-DEV dbo customfieldval
ue,
customfieldopt
ion
customvalue/
stringvalue
nvarchar(255) if NULL or EMPTY => use
default value
Custom field in JIRA, recover by
custom field sql predefined template.
26/5/2014
Data Warehouse Lifecycle Toolkit, 2nd Edition
Copyright 2008, Kimball Group Page 2 of 4
Honeywell ACS ACT -- Master Thesis
Table Name DIM_CLASIFICATION
Table Type Dimension
View Name Classification
Display Name Classification
Description Various clasification attributes of the defect.
Used in Schemas All
DECLASSIFICATION Example data model information workbook
Target Source
Column Name Description Datatype Size Precision Key? FK To NULL? Default
Value
Example Values SCD
Type
Source
System
Source
Schema
Source Table Source Field
Name
Source Datatype ETL Rules Comments
sk Surrogate primary key int PK N 1, 2, 3... Sequence Standard surrogate key
severity The relative impact ot this detect
on the use ot the software to the
end-user, an organization, third
parties. It is related to the extent
of functionality loss and existence
of a suitable workaround.
varchar 255 NK N Not specif Critical, Major,
Minor, Marginal
1 JIRA6-DEV dbo customfieldvalue,
customfieldoption
customvalue/
stringvalue
nvarchar{255) If NULL or EMPTY => use
default value
Custom field in JIRA, recover by
custom field sql predefined template.
injection_discipline The discipline as per ASDP in
which the defect has been initially
injected.
varchar 255 NK N Not specif Legacy,
Requirements,
Architecture,
Design,
Implementation,
test, Deployment,
Other
1 JIRA6-DEV dbo customfieldvalue,
customfieldoption
customvalue/
stringvalue
nvarchar{255) If NULL or EMPTY => use
default value
Custom field in JIRA, recover by
custom field sql predefined template.
resolution The resolution that has been
identified or implemented to fix
the defect.
varchar 60 NK N Not yet ret Cannot
Reproduce,
Duplicate, No
Issue, Obsolete,
Won't Fix, Fixed
1 JIRA6-DEV dbo resolution pname nvarchar{60) If NULL or EMPTY => use
default value
Resolution exists only after the defect
(issue, generally) has been resolved.
detectiondiscipline The discipline and activity as per
ASDP (ACS Software
Development Process) in which
the defect has been found. See
Table 10 for possible values.
varchar 255 NK N Not specif Requirements,
Architecture,
Design,
Implementation,
test, Deployment,
Other
1 JIRA6-DEV dbo customfieldvalue,
customfieldoption
customvalue/
stringvalue
nvarchar{255) If NULL or EMPTY => use
default value
Custom field in JIRA, recover by
custom field sql predefined template.
detectionactivity The discipline and activity as per
ASDP (ACS Software
Development Process) in which
the defect has been found.
varchar 255 NK Not specif Concept
Evaluation,
Customer
Feedback, Review,
Other,...
1 JIRA6-DEV dbo customfieldvalue,
customfieldoption
customvalue/
stringvalue
nvarchar{255) If NULL or EMPTY => use
default value
Custom field in JIRA, recover by
custom field sql predefined template.
status Signifies at which state of the
Defect Lifecycle the defect is.
varchar 60 NK N/A Open, Under
Investigation,
Investigated, More
Information
Needed, ...
1 JIRA6-DEV dbo issuestatus pname nvarchar(60) If NULL or EMPTY => log
error message, use
default value
26/5/2014
Data Warehouse Litecycle Toolkit, 2nd Edition
Copyright 2008, Kimball Group Page 3 of 4
Honeywell ACS ACT -- Master Thesis FAC_DEFECT Example data model intormation workbook
Table Name FAC_DEFECT
Table Type Fact
View Name Defect
Display Name Defect
Description Defect measures
Used in Schemas All
Target Source
Column Name Description Datatype Size Precision Key? FK To NULL? Default Value Example
Values
Additivity Source
System
Source
Schema
Source Table Source Field
Name
Source Datatype ETL Rules Comments
sk Surrogate primary key smallint PK N Generated 1, 2, 3... Sequence Standard surrogate key
project_key Project dimension reference smallint FK DIM_PROJECT N JIRA6-DEV dbo jiraissue PROJECT Foreign key to project dimension
date_created_key Created day dimension
reference
smallint FK DIMDATE N JIRA6-DEV dbo jiraissue CREATED datetime Foreign key to date dimension
date_updated_key Updated day dimension
reference
smallint FK DIMDATE N JIRA6-DEV dbo jiraissue UPDATED datetime Foreign key to date dimension
classificationkey classification dimension
reference
int FK DECLASSIFICATION N JIRA6-DEV dbo jiraissue Foreign key to classification
dimension
date_created full date of creation, with time
information
datetime N 14/3/2014
17:31:25 AM
JIRA6-DEV dbo jiraissue CREATED datetime Full datetime, With time information
date_updated full date of update, with time
information
datetime N 14/3/2014
17:31:25 AM
JIRA6-DEV dbo jiraissue UPDATED datetime Full datetime, With time information
quantity Defect quantity. int N 1 1 Y constant 1 implicitly, constantly In the most detailed grain is 1, is used
in aggregates.
issuekey Identification of the issue in
JIRA
varchar 60 NK N N/A ABC-12345 N JIRA6-DEV dbo jiraissue, projei issuenum, pkey
26/5/2014
Data Warehouse Lifecycle Toolkit, 2nd Edition
Copyright 2008, Kimball Group Page 4 of 4