QPR Knowledge Base 2019.1
Introduction
This chapter describes the Database specification used in QPR Metrics. Each of the tables, along with their field names, type & indication of the primary keys and any existing indexes, are listed in this chapter.
Relationship diagrams illustrating the tables QPR Metrics database are presented in the next chapter section.
If you are not familiar with the naming convention used in the QPR Metrics Database, please refer to chapter section called Naming Convention. Certain table names that are used do not correspond to the names used in the software due to terminology changes. These are discrepancies are also described in the Naming Convention section.
IMPORTANT NOTE Do not modify the database manually if you do not know what you are doing, as this may result in corrupting the whole model. Take a backup copy of your model every time you are going to modify the database manually.
Relationships
All the relationships that exist in the QPR Metrics database are shown below. To simplify the illustrations, references to object tables are marked with the letter 'o' in parenthesis in the relationship diagrams.
Tables
All the tables in the QPR Metrics database are listed below with a brief description.
There is a standard field name format, which is used in every table. These names are used repeatedly with the relevant preset according to that specific table. They carry a global meaning that is true for all the tables.
_ID: |
Carries a unique identifier for every single data entry. E.g. User ID, Measure ID. |
_NAME: |
A textual name is given to the group data of which the _ID is given. E.g. User Name, Measure Name. |
_DESCRIPTION: |
A detailed description of the record in a table. E.g. User description i.e. what type of user it is, Measure description. |
SC_OBJECT table has one row for each model element. It contains the following fields that can be used in monitoring usage:
_USR_ID: |
User who made the last change to the object. |
_CREA_DATE: |
The date when the current instance of record was created. |
_LAST_CHANGE: |
The last modified date for the current record instance. |
SC_2_ELEMENT
Secondary elements (visions, strategies and perspectives) of the SC model.
Field Name |
Field Type |
Description |
|---|---|---|
EL2_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
EL2_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
EL2_MEA_ID |
N NOT NULL |
Ref: SC_MEASURE (MEA_ID) |
EL2_MEA_MOD_ID |
N NOT NULL |
Ref: SC_MEASURE (MEA_MOD_ID) |
EL2_ETY_ID |
N NOT NULL |
Ref: SC_ELEMENT_TYPE (ETY_ID) |
EL2_ETY_MOD_ID |
N NOT NULL |
Ref: SC_ELEMENT_TYPE (ETY_MOD_ID) |
EL2_NAME |
C(100) |
|
EL2_DESCRIPTION |
Text |
|
EL2_SCC_ID |
N |
Ref: SC_SCORECARD (SCC_ID) |
EL2_SCC_MOD_ID |
N |
Ref: SC_SCORECARD (SCC_MOD_ID) |
EL2_STATUS |
N |
INDEX ELEM21 (EL2_MOD_ID);
INDEX ELEM22 (EL2_MEA_MOD_ID, EL2_MEA_ID);
INDEX ELEM23 (EL2_ETY_MOD_ID, EL2_ETY_ID);
INDEX ELEM24 (EL2_SCC_MOD_ID, EL2_SCC_ID);
SC_ALERT_USER
Alert users for Alerts.
Field Name |
Field Type |
Description |
|---|---|---|
AUS_ID |
N NOT NULL (*) |
|
AUS_MOD_ID |
N NOT NULL (*) |
|
AUS_USR_ID |
N NOT NULL (*) |
SC_CHART_TEMPLATE
History chart templates.
Field Name |
Field Type |
Description |
|---|---|---|
CT_ID |
N NOT NULL (*) |
|
CT_MOD_ID |
N NOT NULL (*) |
Reference to SC_MODEL |
CT_NAME |
C(100) |
|
CT_SETTINGS |
BLOB |
SC_ELEMENT_TYPE
Contains different element types available in models. (There is a separate set of element types for each model.)
Field Name |
Field Type |
Description |
|---|---|---|
ETY_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
ETY_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
ETY_ET2_PARENT_ID |
N |
Ref: SC_ELEMENT_TYPE, element type to which secondary element type can be connected. |
ETY_ET2_P_MOD_ID |
N |
Ref: SC_ELEMENT_TYPE |
ETY_IS_2ELEMENT |
N |
1=is secondary element type |
ETY_ACTPLANS |
N |
1= Allow action planning for elements of this type. |
ETY_COMMENTS |
N |
1= Allow commenting for elements of this type. |
ETY_NAME |
C(100) |
|
ETY_DESCRIPTION |
VC(2000) |
|
ETY_ICON |
BLOB |
Icon for element type. |
ETY_WARNING |
N |
1=Warning is on |
ETY_WARNING_ICON |
BLOB |
Warning icon |
ETY_DEFAULT_SYMBOL |
BLOB |
Symbol settings. |
ETY_SETTINGS |
BLOB |
Role settings. |
INDEX ELEMTYPE1 (ETY_MOD_ID);
INDEX ELEMTYPE2 (ETY_ET2_P_MOD_ID, ETY_ET2_PARENT_ID);
SC_INFO_ITEM
Contains information items.
Field Name |
Field Type |
Description |
|---|---|---|
INF_MOD_ID |
N NOT NULL (*) |
References SC_OBJECT table |
INF_ID |
N NOT NULL (*) |
References SC_OBJECT table |
INF_NAME |
C(100) |
|
INF_DESCRIPTION |
VC(1800) |
|
INF_LOCATION |
VC(2000) |
INDEX INFO_ITEM1 (INF_MOD_ID);
SC_INFO_OBJECT
Breaks many-to-many relation between information item and object.
Field Name |
Field Type |
Description |
|---|---|---|
INO_MOD_ID |
N NOT NULL (*) |
References SC_INFO_ITEM table |
INO_INF_ID |
N NOT NULL (*) |
References SC_INFO_ITEM table |
INO_OBJ_MOD_ID |
N NOT NULL (*) |
References SC_OBJECT table |
INO_OBJ_ID |
N NOT NULL (*) |
References SC_OBJECT table |
INDEX INFO_OBJECT1 (INO_MOD_ID);
SC_INTEGRATION
In the SC_INTEGRATION table, each line stores an integration (import, external analysis), which is defined in a model.
Field Name |
Field Type |
Description |
|---|---|---|
INT_MOD_ID |
N NOT NULL (*) |
References SC_OBJECT table |
INT_ID |
N NOT NULL (*) |
References SC_OBJECT table |
INT_TASK_MOD_ID |
N |
References SC_TASK table |
INT_TASK_ID |
N |
References SC_TASK table |
INT_SCHEDULE_MOD_ID |
N |
References SC_SCHEDULE table |
INT_SCHEDULE_ID |
N |
References SC_SCHEDULE table |
INT_SETTINGS |
BLOB |
Contains settings for the object |
INDEX INTEGR1 (INT_MOD_ID);
SC_MEASURE
A measure created in the model.
Field Name |
Field Type |
Description |
|---|---|---|
MEA_AR_FLAGS |
N |
|
MEA_AR_DRILLDOWN_DEPTH |
N |
|
MEA_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
MEA_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
MEA_SYMBOL |
C(30) |
|
MEA_NAME |
C(300) |
|
MEA_DESCRIPTION |
VC(2000) |
|
MEA_USR_CHARGE_ID |
N |
|
MEA_WARNING |
N |
|
MEA_WARNING_ICON |
BLOB |
|
MEA_ETY_ID |
N |
Ref: SC_ELEMENT_TYPE |
MEA_ETY_MOD_ID |
N |
Ref: SC_ELEMENT_TYPE |
MEA_MUN_ID |
N |
Ref: SC_MEASURE_UNIT (MUN_ID) |
MEA_MUN_MOD_ID |
N |
Ref: SC_MEASURE_UNIT (MUN_MOD_ID) |
MEA_MEA_PARENT_ID |
N |
Ref: SC_MEASURE (MEA_ID) |
MEA_MEA_P_MOD_ID |
N |
Ref: SC_MEASURE (MEA_MOD_ID) |
MEA_MEA_COPIED_ID |
N |
Ref: SC_MEASURE (MEA_ID) |
MEA_MEA_C_MOD_ID |
N |
Ref: SC_MEASURE (MEA_MOD_ID) |
MEA_SCC_ID |
N |
Ref: SC_SCORECARD (SCC_ID) |
MEA_SCC_MOD_ID |
N |
Ref: SC_SCORECARD (SCC_MOD_ID) |
MEA_PSE_ID |
N |
Ref: SC_PATH_SET (PSE_ID) |
MEA_PSE_MOD_ID |
N |
Ref: SC_PATH_SET (PSE_MOD_ID) |
MEA_PLE_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_ID) |
MEA_PLE_MOD_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_MOD_ID) |
MEA_ACCUM_RULE |
N |
|
MEA_CH_TEMPL_ID |
N |
|
MEA_CH_TEMPL_MOD_ID |
N |
|
MEA_SETTINGS |
BLOB |
Role settings. |
INDEX MEASURE1 (MEA_MOD_ID);
INDEX MEASURE2 (MEA_MUN_MOD_ID, MEA_MUN_ID);
INDEX MEASURE4 (MEA_MEA_P_MOD_ID, MEA_MEA_PARENT_ID);
INDEX MEASURE5 (MEA_MEA_C_MOD_ID, MEA_MEA_COPIED_ID);
INDEX MEASURE6 (MEA_SCC_MOD_ID, MEA_SCC_ID);
INDEX MEASURE7 (MEA_PSE_MOD_ID, MEA_PSE_ID);
INDEX MEASURE8 (MEA_PLE_MOD_ID, MEA_PLE_ID);
INDEX MEASURE9 (MEA_USR_CHARGE_ID);
SC_MEASURE_GRAPH
Graph information for the values of the measure.
Field Name |
Field Type |
Description |
|---|---|---|
MGR_MOD_ID |
N NOT NULL (*) |
Ref: SC_MEASURE (MEA_MOD_ID) |
MGR_MEA_ID |
N NOT NULL (*) |
Ref: SC_MEASURE (MEA_ID) |
MGR_GRAPH_SETTINGS |
BLOB |
INDEX MEASUREGRAPH1 (MGR_MOD_ID);
SC_MEASURE_H
Contains measure hierarchies (for reporting purposes) of models.
Field Name |
Field Type |
Description |
|---|---|---|
MEH_MOD_ID |
N NOT NULL (*) |
|
MEH_SCC_ID |
N NOT NULL (*) |
|
MEH_INDEX |
N NOT NULL (*) |
Unique index (within measure and sc, describing correct hierarchy order. |
MEH_MEA_ID |
N NOT NULL |
|
MEH_LEVEL |
N NOT NULL |
Level of indentation in hierarchy. |
INDEX SC_MEAHIER1 (MEH_MOD_ID, MEH_MEA_ID);
SC_MEASURE_PATH
Paths (and path-specific measure data) connected to a measure.
Field Name |
Field Type |
Description |
|---|---|---|
MPA_ACCUM_RULE |
N |
|
MPA_MOD_ID |
N NOT NULL (*) |
Ref: SC_MEASURE (MEA_MOD_ID), Ref: SC_PATH (PAT_MOD_ID) |
MPA_MEA_ID |
N NOT NULL (*) |
Ref: SC_MEASURE (MEA_ID) |
MPA_PAT_ID |
N NOT NULL (*) |
Ref: SC_PATH (PAT_ID) |
MPA_INPUT_TYPE |
N |
If 0 or null, then = manual, other numbers are different integration possibilities. |
MPA_INT_ID |
N |
References to SC_INTEGRATION table |
MPA_INT_MOD_ID |
N |
References to SC_INTEGRATION table |
MPA_LABEL |
C(100) |
|
MPA_FORMULA |
Text |
|
MPA_MUN_ID |
N |
Ref: SC_MEASURE_UNIT (MUN_ID) |
MPA_MUN_MOD_ID |
N |
Ref: SC_MEASURE_UNIT (MUN_MOD_ID) |
MPA_PLE_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_ID) Points to the period level of the element series. If null, the element series uses the period level of the element. |
MPA_PLE_MOD_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_MOD_ID) |
INDEX MEASUREPATH1 (MPA_MOD_ID);
INDEX MEASUREPATH2 (MPA_MOD_ID, MPA_MEA_ID);
INDEX MEASUREPATH3 (MPA_MOD_ID, MPA_PAT_ID);
INDEX MEASUREPATH4 (MPA_MUN_MOD_ID, MPA_MUN_ID);
INDEX MEASUREPATH5 (MPA_PLE_MOD_ID, MPA_PLE_ID);
SC_MEASURE_UNIT
Different units used in a measure e.g. meters, dollars, and euros.
Field Name |
Field Type |
Description |
|---|---|---|
MUN_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
MUN_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
MUN_NAME |
C(100) |
|
MUN_DESCRIPTION |
Text |
|
MUN_SYMBOL |
C(30) |
|
MUN_DECIMALS |
N |
|
MUN_SEPARATOR_DECI |
C(1) |
|
MUN_DIGITS |
N |
|
MUN_SEPARATOR_DIGI |
C(1) |
|
MUN_BEFORE |
N |
|
MUN_NEGINDEX |
N |
|
MUN_DATE_FORMAT |
Text |
INDEX MEASUREUNIT1 (MUN_MOD_ID);
SC_MEASURE_VALUE
A measure value.
Field Name |
Field Type |
Description |
|---|---|---|
MVA_MOD_ID |
N NOT NULL (*) |
Ref: SC_MEASURE_PATH (MPA_MOD_ID) Ref: SC_PERIOD (PER_MOD_ID) |
MVA_MEA_ID |
N NOT NULL (*) |
Ref: SC_MEASURE_PATH (MPA_MEA_ID) |
MVA_PAT_ID |
N NOT NULL (*) |
Ref: SC_MEASURE_PATH (MPA_PAT_ID) |
MVA_PER_ID |
N NOT NULL (*) |
Ref: SC_PERIOD |
MVA_VALUE |
Float NOT NULL |
|
MVA_DATE |
N |
|
MVA_SOP_ID |
N |
Ref: SC_STATUS_OPTION |
MVA_SOP_LAST_CHANGE |
DateTime |
|
MVA_TEXT |
Text |
INDEX MEASURE_VALUE1 (MVA_MOD_ID);
INDEX MEASURE_VALUE2 (MVA_MOD_ID, MVA_MEA_ID, MVA_PAT_ID);
INDEX MEASURE_VALUE3 (MVA_MOD_ID, MVA_PER_ID);
INDEX MEASURE_VALUE4 (MVA_MOD_ID, MVA_SOP_ID);
SC_MODEL
A QPR Metrics model.
Field Name |
Field Type |
Description |
|---|---|---|
MOD_ID |
N NOT NULL (*) |
|
MOD_NAME |
C(100) |
|
MOD_CREA_DATE |
DateTime |
|
MOD_USR_ID |
N |
|
MOD_DESCRIPTION |
VC(2000) |
|
MOD_REPLY_ADDRESS |
C(255) |
|
MOD_READ_ONLY |
N NOT NULL |
|
MOD_REF_SCHEDULE_ID |
N NOT NULL |
|
MOD_SETTINGS |
BLOB |
SC_OBJECT
Every model object has one row.
Field Name |
Field Type |
Description |
|---|---|---|
OBJ_MOD_ID |
N NOT NULL (*) |
Ref: SC_MODEL (MOD_ID) |
OBJ_ID |
N NOT NULL (*) |
|
OBJ_USR_ID |
N NOT NULL |
|
OBJ_CREA_DATE |
DateTime NOT NULL |
|
OBJ_LAST_CHANGE |
DateTime NOT NULL |
|
OBJ_TYPE |
N NOT NULL |
|
OBJ_SORT_ORDER |
N |
|
OBJ_SOURCE_ID |
N NOT NULL |
|
OBJ_SOURCE_MOD_ID |
N NOT NULL |
|
OBJ_ATTACHED_TO_SOURCE |
N NOT NULL |
INDEX OBJECT1 (OBJ_MOD_ID);
INDEX OBJECT2 (OBJ_USR_ID);
SC_PARAM_DATA
Parameter values.
Field Name |
Field Type |
Description |
|---|---|---|
PAR_ENTRY |
C(32) NOT NULL |
|
PAR_VALUE_INT |
N |
|
PAR_VALUE_DT |
DateTime |
|
PAR_VALUE |
BLOB |
Contains the version of database in "DB_VERSION" entry, default language in "PAR_DEF_LANG" and also holds information for locked measure values in entries "PAR_LOCKED" and "PAR_LOCKED_DAYS".
SC_PATH
Measure paths (path presents a set of measure values given for certain purpose).
Field Name |
Field Type |
Description |
|---|---|---|
PAT_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
PAT_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
PAT_SYMBOL |
C(30) |
|
PAT_NAME |
C(100) |
|
PAT_TYPE |
N |
If = 1,control path, otherwise value path. |
PAT_COLOR |
N |
|
PAT_PSE_ID |
N NOT NULL |
Ref: SC_PATH_SET (PSE_ID), default value path of the path set. |
PAT_PSE_MOD_ID |
N NOT NULL |
Ref: SC_PATH_SET (PSE_MOD_ID), default value path of the path set. |
PAT_FORMULA |
TEXT |
Default formula of the series |
PAT_SOP_ID |
N |
0 = Status control disabled 1 = Status control enabled |
PAT_REVERSED_TREND |
N |
0 = Reversed trend arrow not used 1 = Reversed trend arrow used |
PAT_TREND_TYPE |
N |
0 = Two period difference 1 = One period polarity |
PAT_DEF_GRAPH_VISIBILITY |
N |
|
PAT_MUN_ID |
N |
Ref: SC_MEASURE_UNIT (MUN_ID) |
PAT_MUN_MOD_ID |
N |
Ref: SC_MEASURE_UNIT (MUN_MOD_ID) |
PAT_ACCUM_RULE |
N |
INDEX PATH1 (PAT_MOD_ID);
INDEX PATH2 (PAT_PSE_MOD_ID, PAT_PSE_ID);
INDEX PATH3 (PAT_MUN_MOD_ID, PAT_MUN_ID);
SC_PATH_SET
Grouping element for measure paths (a measure uses one path set).
Field Name |
Field Type |
Description |
|---|---|---|
PSE_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
PSE_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
PSE_NAME |
C(100) |
|
PSE_DESCRIPTION |
VC(2000) |
|
PSE_FORCE_MIN |
N |
If = 1, then values under minimum path are not allowed. |
PSE_FORCE_MAX |
N |
If = 1, then values under minimum path are not allowed. |
PSE_PAT_DEFVAL_ID |
N |
Ref: SC_PATH (PAT_ID), default value path of the path set. |
PSE_MOD_DEFVAL_ID |
N |
Ref: SC_PATH (PAT_ID), default value path of the path set. |
INDEX PATHSET1 (PSE_MOD_ID);
INDEX PATHSET2 (PSE_MOD_DEFVAL_ID, PSE_PAT_DEFVAL_ID);
SC_PERIOD
Time periods used in measuring (separate for each model) are stored to this table.
Field Name |
Field Type |
Description |
|---|---|---|
PER_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
PER_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
PER_NAME |
C(100) |
|
PER_PLE_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_ID) |
PER_PLE_MOD_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_MOD_ID) |
PER_START_DATE |
DateTime |
Defined only for bottom-level periods |
PER_END_DATE |
DateTime |
Defined only for bottom-level periods |
PER_FLAGS |
N |
INDEX PERIOD1 (PER_MOD_ID);
INDEX PERIOD2 (PER_PLE_MOD_ID, PER_PLE_ID);
SC_PERIOD_LEVEL
Period hierarchy levels available (separate for each model).
Field Name |
Field Type |
Description |
|---|---|---|
PLE_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
PLE_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
PLE_NAME |
C(100) |
|
PLE_MOD_PARENT_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_MOD_ID) |
PLE_PARENT_ID |
N |
Ref: SC_PERIOD_LEVEL (PLE_ID) |
PLE_ACCUMULATION |
N |
|
PLE_PREFIX |
C(100) |
|
PLE_TYPE |
N |
|
PLE_RULES |
TEXT |
|
PLE_ID_STARTTIME |
N |
|
PLE_ID_ENDTIME |
N |
|
PLE_ID_DURATION |
N |
INDEX PERIODLEVEL1 (PLE_MOD_ID);
INDEX PERIODLEVEL2 (PLE_MOD_PARENT_ID, PLE_PARENT_ID);
SC_RANGE
Ranges available for classifying measure values (connect to measure through Path Sets).
Field Name |
Field Type |
Description |
|---|---|---|
RAN_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
RAN_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
RAN_NAME |
C(100) |
|
RAN_MIN_PATH_IN |
N |
1=Minimum path inside range |
RAN_MAX_PATH_IN |
N |
1=Maximum path inside range |
RAN_VALUE |
FLOAT |
|
RAN_PSE_ID |
N |
Ref: SC_PATH_SET (PSE_ID) |
RAN_PSE_MOD_ID |
N |
Ref: SC_PATH_SET (PSE_ID) |
RAN_PAT_MIN_ID |
N |
Minimum path, Ref: SC_PATH (PAT_ID) |
RAN_PAT_MOD_MIN_ID |
N |
Minimum path, Ref: SC_PATH (PAT_MOD_ID) |
RAN_PAT_MAX_ID |
N |
Maximum path, Ref: SC_PATH (PAT_ID) |
RAN_PAT_MOD_MAX_ID |
N |
Maximum path, Ref: SC_PATH (PAT_MOD_ID) |
RAN_COLOR |
N |
RGB color used in indicators etc. |
RAN_BITMAP |
BLOB |
Bitmap used in SC view. |
RAN_WARNING |
N |
1=Warning is on |
INDEX RANGE1 (RAN_MOD_ID);
INDEX RANGE2 (RAN_PSE_MOD_ID, RAN_PSE_ID);
INDEX RANGE3 (RAN_PAT_MOD_MAX_ID, RAN_PAT_MAX_ID);
INDEX RANGE4 (RAN_PAT_MOD_MIN_ID, RAN_PAT_MIN_ID);
SC_RDL
Remote model import settings:
Field Name |
Field Type |
Description |
|---|---|---|
RDL_ID |
N NOT NULL (*) |
|
RDL_SERVER_IP |
C(32) |
|
RDL_SERVER_PORT |
N |
|
RDL_REMOTE_MOD_ID |
N |
|
RDL_REMOTE_MOD_NAME |
C(100) |
|
RDL_LOCAL_MOD_ID |
N |
|
RDL_LOCAL_MOD_NAME |
C(100) |
|
RDL_USER_NAME |
C(32) |
|
RDL_USER_PASSWORD |
C(32) |
|
RDL_DOMAIN_NAME |
C(32) |
|
RDL_SCHEDULE_MOD_ID |
N |
SC_SCHEDULE |
RDL_SCHEDULE_ID |
N |
SC_SCHEDULE |
RDL_TASK_MOD_ID |
N |
SC_TASK |
RDL_TASK_ID |
N |
SC_TASK |
INDEX SC_RDL1 (RDL_REMOTE_MOD_ID);
SC_REPORT
Contains a definition for report.
Field Name |
Field Type |
Description |
|---|---|---|
REP_ID |
N NOT NULL (*) |
|
REP_IND |
N NOT NULL (*) |
Index, needed in handling large reports. |
REP_FOL_ID |
N NOT NULL |
Ref: SC_REPORT (REP_ID) |
REP_FOL_IND |
N NOT NULL |
Ref: SC_REPORT (REP_IND) |
REP_NAME |
C(255) |
|
REP_INT_NAME |
C(255) |
|
REP_VERSION |
N |
Internal version number for report. |
REP_IS_SUBREP |
N |
1= Is a subreport |
REP_IS_FOLDER |
N |
1= Is a report folder, does not contain a report. |
REP_PUBLISHED |
N |
1= Report is available for viewing. |
REP_MODIFIED |
DateTime |
REP_USR_ID |
REP_USR_ID |
N |
|
REP_DATA |
BLOB |
|
REP_FILE_NAME |
C(255) |
INDEX SC_REPORT1 (REP_USR_ID);
INDEX SC_REPORT2 (REP_FOL_ID, REP_FOL_IND);
SC_REPORT_RIGHT
Report rights are stored to this table.
Field Name |
Field Type |
Description |
|---|---|---|
RRI_REP_ID |
N NOT NULL (*) |
Refers to SC_REPORT table |
RRI_REP_IND |
N NOT NULL (*) |
|
RRI_USR_ID |
N NOT NULL (*) |
|
RRI_LEVEL |
N NOT NULL |
SC_SCHEDULE
Contains schedule objects.
Field Name |
Field Type |
Description |
|---|---|---|
SLE_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
SLE_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
SLE_START_DATE |
DateTime |
|
SLE_TIME |
Integer |
Time of day (0-24) when scheduled task is executed |
SLE_SCHED_TYPE |
N |
1=period-based, otherwise based on calendar time. |
SLE_INTERVAL |
N |
|
SLE_EXEC_TIME |
N |
|
SLE_EVERY_WEEKDAY |
N |
|
SLE_EXEC_DAYS |
C(30) |
|
SLE_EXEC_MONTHS |
C(30) |
|
SLE_ON_MONTHS_DAY |
N |
|
SLE_MONTHS_DAY |
N |
|
SLE_DAY_INDICATOR |
N |
|
SLE_PERIOD_OFFSET |
C(30) |
|
SLE_OFFSET_MAPPING |
N |
|
SLE_PERIOD_LEVEL_ID |
N |
|
SLE_LAST_EXECUTED |
DateTime |
INDEX SC_SCHEDULE1 (SLE_MOD_ID);
SC_SCORECARD
A scorecard of a model. (A model contains a scorecard hierarchy.) Most of the information of the actual model element is stored in SC_MEASURE (corresponding root measure of the scorecard measure hierarchy).
Field Name |
Field Type |
Description |
|---|---|---|
SCC_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
SCC_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
SCC_SCC_PARENT_ID |
N |
Ref: SC_SCORECARD (SCC_ID) |
SCC_SCC_P_MOD_ID |
N |
Ref: SC_SCORECARD (SCC_MOD_ID) |
SCC_SYMBOL |
C(30) |
|
SCC_DEF_SCVIEW_MOD_ID |
N |
Ref: SC_VIEW_SETTINGS(SET_MOD_ID) |
SCC_DEF_SCVIEW_SET_ID |
N |
Ref: SC_VIEW_SETTINGS(SET_ID) |
SCC_DEF_SMVIEW_MOD_ID |
N |
Ref: SC_VIEW_SETTINGS(SET_MOD_ID) |
SCC_DEF_SMVIEW_SET_ID |
N |
Ref: SC_VIEW_SETTINGS(SET_ID) |
SCC_USR_CHARGE_ID |
N |
|
SCC_DESCRIPTION |
VC(2000) |
|
SCC_BASE_SCC_ID |
N |
|
SCC_INHERITANCE |
C(255) |
|
SCC_LOCKING |
C(255) |
INDEX SCORECARD1 (SCC_MOD_ID);
INDEX SCORECARD2 (SCC_SCC_P_MOD_ID, SCC_SCC_PARENT_ID);
SC_SCORECARD_H
Contains scorecard hierarchies (for reporting purposes) for models.
Field Name |
Field Type |
Description |
|---|---|---|
SCH_MOD_ID |
N NOT NULL (*) |
|
SCH_INDEX |
N NOT NULL (*) |
Unique index describing correct hierarchy order. |
SCH_SCC_ID |
N NOT NULL |
|
SCH_LEVEL |
N NOT NULL |
Level of indentation in hierarchy. |
INDEX SCHIER1 (SCH_MOD_ID, SCH_SCC_ID);
SC_SHAPE_TYPE
Contains different shape types available in models (There is a separate set of shape types for each model).
Field Name |
Field Type |
Description |
|---|---|---|
STY_MOD_ID |
N NOT NULL (*) |
Model id |
STY_ID |
N NOT NULL (*) |
Shape type's id |
STY_NAME |
C(100) |
Shape type's name |
STY_DESCRIPTION |
VC(2000) |
Shape type's description |
STY_DEFAULT_SYMBOL |
BLOB |
Symbol settings |
INDEX SC_SHAPETYPE1 (STY_MOD_ID);
SC_STATUS_OPTION
A status option.
Field Name |
Field Type |
Description |
|---|---|---|
SOP_MOD_ID |
N NOT NULL (*) |
References SC_OBJECT table |
SOP_ID |
N NOT NULL (*) |
References SC_OBJECT table |
SOP_NAME |
C(100) |
|
SOP_LOCK_TYPE |
N NOT NULL |
0 = Not locked, 1 = Measure values locked in browser |
SOP_ICON |
BLOB |
SC_TASK
Task objects.
Field Name |
Field Type |
Description |
|---|---|---|
TAS_MOD_ID |
N NOT NULL (*) |
References SC_OBJECT table |
TAS_ID |
N NOT NULL (*) |
References SC_OBJECT table |
TAS_TYPE |
N |
Which subclass of the CTask class this record represents. |
TAS_SCHEDULE_MOD_ID |
N |
References SC_SCHEDULE table |
TAS_SCHEDULE_ID |
N |
References SC_SCHEDULE table |
TAS_TARGET_ID |
N |
d of the object affected by this task. |
TAS_LAST_EXEC |
DateTime |
|
TAS_STATUS |
N |
Status of last execute (null= not executed, 0=OK, 1=error) |
INDEX SC_TASK1 (TAS_MOD_ID);
SC_USER_CLS_RIGHTS
The rights of the user classes.
Field Name |
Field Type |
Description |
|---|---|---|
UCR_USR_ID |
N NOT NULL (*) |
|
UCR_ETY_ID |
N NOT NULL (*) |
Ref: SC_ELEMENT_TYPE (ETY_ID) |
UCR_ETY_MOD_ID |
N NOT NULL (*) |
Ref: SC_ELEMENT_TYPE (ETY_MOD_ID) |
UCR_RIGHTS |
N |
INDEX SC_USR_CLS_RIGHTS2 (UCR_ETY_MOD_ID, UCR_ETY_ID);
SC_USER_OBJ_RIGHTS
User rights for specific objects.
Field Name |
Field Type |
Description |
|---|---|---|
URI_USR_ID |
N NOT NULL (*) |
|
URI_OBJ_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
URI_OBJ_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_MOD_ID) |
URI_RIGHTS |
N |
SC_VALIDATION
Stores validation information (only one MOD_ID field here, because all foreign keys are of Not Null type i.e. problems with null values should not occur).
Field Name |
Field Type |
Description |
|---|---|---|
VAL_MOD_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
VAL_X_OBJ_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
VAL_Y_OBJ_ID |
N NOT NULL (*) |
Ref: SC_OBJECT (OBJ_ID) |
VAL_STRENGTH |
N NOT NULL |
Strength of dependency between objects (0=no, 1= weak, 2= moderate and 3= strong). |
INDEX SC_VALIDATION1 (VAL_MOD_ID);
SC_VALUE_ALERT
Value alerts.
Field Name |
Field Type |
Description |
|---|---|---|
ALE_ID |
N NOT NULL (*) |
|
ALE_MOD_ID |
N NOT NULL (*) |
Ref: SC_MODEL (MOD_ID) |
ALE_TYPE |
N |
|
ALE_MEA_ID |
N |
Ref: SC_MEASURE (MEA_ID) |
ALE_MEA_MOD_ID |
N |
Ref: SC_MEASURE (MEA_MOD_ID) |
ALE_PATH_ID |
N |
Ref: SC_PATH (PAT_ID) |
ALE_PATH_MOD_ID |
N |
Ref: SC_PATH (PAT_MOD_ID) |
ALE_RAN_ID |
N |
Ref: SC_RANGE (RAN_ID) |
ALE_RAN_MOD_ID |
N |
Ref: SC_RANGE (RAN_MOD_ID) |
ALE_DATE |
DateTime |
|
ALE_LAST_PER_ID |
N |
Ref: SC_PERIOD (PER_ID) |
ALE_LAST_P_MOD_ID |
N |
Ref: SC_PERIOD (PER_MOD_ID) |
ALE_URL |
C(255) |
|
ALE_VAL_DELAY |
N |
|
ALE_DELAYED_ALERT |
N |
|
ALE_CHECK_DATE |
Datetime |
Date when delayed value changed alerts were checked last time |
SC_VIEW_SETTINGS
Scorecard view and strategy map settings.
Field Name |
Field Type |
Description |
|---|---|---|
SET_MOD_ID |
N NOT NULL (*) |
References SC_OBJECT table |
SET_ID |
N NOT NULL (*) |
References SC_OBJECT table |
SET_SCC_MOD_ID |
N NOT NULL |
References SC_SCORECARD table |
SET_SCC_ID |
N NOT NULL |
References SC_SCORECARD table |
SET_TYPE |
N NOT NULL |
Type of view settings |
SET_NAME |
C(100) |
|
SET_SETTINGS |
BLOB |
INDEX VIEWSETTINGS1 (SET_MOD_ID);
INDEX VIEWSETTINGS2 (SET_SCC_MOD_ID, SET_SCC_ID);
INDEX VIEWSETTINGS3 (SET_TYPE);
Naming Convention
The following naming convention is used throughout the QPR Metrics database:
•Each table starts with 'SC'-prefix to avoid conflicts with reserved words in database servers.
•Each field has a unique name that starts with a different three-letter acronym in each table, e.g. 'MOD_NAME' for model name field.
•In foreign key references a three-letter acronym of table is used, e.g. 'MEA_MOD_ID' references 'MOD_ID' in MODEL table.
•'_' is used to separate words from each other, if model contains several of them, e.g. 'MOD_CREA_DATE'
In addition to that, table names are changed in the following way:
•Models -> MODEL i.e. capital letter, singular format.
•Measuregraphs -> MEASURE_GRAPH i.e. '_' is used to clarify names.
Database scripts must be modified, and corresponding constants must be changed in software.
Some naming discrepancies exist between the software features and database table names. To determine which QPR Metrics feature corresponds to a certain term used in a database table name, use the following table:
Database term |
Corresponding QPR Metrics feature |
|---|---|
path set |
value settings |
Path |
series |
Measure |
element |
simulation (sim) |
cause and effect |
secondary elements (2_element) |
linked elements |
measure graph |
history chart |
Object class (user class) |
(user rights to) model elements |
Notation and Database-Specific Types
The following notation is used in describing the database tables:
Field names have the following rules:
•Each field has unique name that starts with a different three-letter acronym in each table, e.g. 'MOD_NAME' for model name field.
•In foreign key references a three-letter acronym of table is used, e.g. 'MOD_FOL_ID' references 'FOL_ID' in folder table.
With Type column the following alternatives are used:
•N (Integer, Long Value)
•Float (floating-point value, Double).
•C (fixed size string less than 256 characters).
•VC (string with 256 or more characters).
•Date/Time (stores either date or time or both of them).
•VB (fixed binary data with size less than 256 bytes)
•BLOB (stores pictures or texts with unlimited size). Only limitations set by different supported databases limit the amount of data stored to these fields. If size has limitations, maximum is shown in parentheses.
•CLOB (Memo's)
If a null value is not allowed with a field, the term 'Not Null' is included in the Field Type column.
Fields marked with (*) are key fields.
The following table shows the database-specific types used with different supported databases:
Type |
MS SQL Server |
Oracle 11g |
|---|---|---|
N |
INT |
NUMBER |
FLOAT |
FLOAT |
NUMBER |
C(x) |
VARCHAR (x) |
VARCHAR2 (x) |
VC(x) |
NVARCHAR (x) |
VARCHAR2 (x) |
Text(x) |
NVARCHAR (x) |
CLOB |
VB(x) |
VARBINARY (x) |
RAW(x) |
BLOB |
VARBINARY (x) |
BLOB |
DateTime |
DATETIME |
DATE |
