QPR Knowledge Base 2017.1

QPR Metrics Database Tables

QPR Metrics Database Tables

Previous topic Next topic No directory for this topic  

QPR Metrics Database Tables

Previous topic Next topic Topic directory requires JavaScript Mail us feedback on this topic!  

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