Results for “msc_bis_target_levels_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_BIS_TARGET_LEVELS is a table in the MSC schema (Advanced Supply Chain Planning) that defines the distinct combinations of dimension levels used to build performance indicators for reporting. Each row specifies a target level — a named configuration that pins a measure to specific organizational, time, and dimensional granularities so that a performance indicator can be computed for a given reporting need. The documented example is a target level used to break sales revenues down along selected dimensions.

Within the ETRM 12.2.2 physical schema, the object owns 31 documented columns and is classified heuristically as satellite-leaning in Data Vault terms. That classification is offered as a modeling suggestion only: the table carries a surrogate key (TARGET_LEVEL_ID, SR_INSTANCE_ID) and descriptive, versioned attributes (names, measure linkage, dimension levels, notification defaults), which is characteristic of a satellite rather than a hub or link. Note that it also sits on the referencing side of the MSC_BIS_TARGETS relationship, so it participates in the surrounding target-definition structure.

Key Information Stored

The primary key MSC_BIS_TARGET_LEVELS_PK is composite, comprising TARGET_LEVEL_ID and SR_INSTANCE_ID, where SR_INSTANCE_ID identifies the source instance. Two unique indexes act as business-key candidates: MSC_BIS_TARGET_LEVELS_U1 (SR_INSTANCE_ID, TARGET_LEVEL_ID) and MSC_BIS_TARGET_LEVELS_U2 (TARGET_LEVEL_SHORT_NAME, SR_INSTANCE_ID). The U2 index confirms that the short name must be unique per instance.

The most significant columns include:

• TARGET_LEVEL_ID — surrogate identifier for the level combination.
• SR_INSTANCE_ID — source instance discriminator, part of both the PK and U1/U2.
• TARGET_LEVEL_SHORT_NAME and TARGET_LEVEL_NAME — the internal and descriptive labels for the target level.
• DESCRIPTION — free-text explanation of the reporting intent.
• MEASURE_ID — foreign key to MSC_BIS_PERFORMANCE_MEASURES, tying the level to its performance measure.
• ORG_LEVEL_ID and TIME_LEVEL_ID — the organizational and time granularities applied.
• DIMENSION1_LEVEL_ID through DIMENSION5_LEVEL_ID — up to five additional dimension levels applied simultaneously.
• UNIT_OF_MEASURE — the unit associated with the indicator.
• COMPUTING_FUNCTION_ID and REPORT_FUNCTION_ID — function references used in computation and reporting.
• SYSTEM_FLAG and REFRESH_NUMBER — indicating seeded versus user-defined rows and refresh state.
• DEFAULT_NOTIFY_RESP_ID and DEFAULT_NOTIFY_RESP_SHORT_NAME — default notification responsibility.
• Standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and concurrent-program columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE).

Common Use Cases and Queries

Typical usage is to resolve which dimension levels underlie a performance measure, or to find all target levels that reference a measure. The following patterns are representative:

• List active target levels with their measure: SELECT t.TARGET_LEVEL_SHORT_NAME, t.TARGET_LEVEL_NAME, m.MEASURE_NAME FROM MSC.MSC_BIS_TARGET_LEVELS t JOIN MSC.MSC_BIS_PERFORMANCE_MEASURES m ON m.MEASURE_ID = t.MEASURE_ID AND m.SR_INSTANCE_ID = t.SR_INSTANCE_ID WHERE t.SYSTEM_FLAG = 'N';
• Find the target levels attached to a given target: SELECT t.TARGET_LEVEL_ID, t.TARGET_LEVEL_NAME FROM MSC.MSC_BIS_TARGET_LEVELS t JOIN MSC.MSC_BIS_TARGETS g ON g.TARGET_LEVEL_ID = t.TARGET_LEVEL_ID AND g.SR_INSTANCE_ID = t.SR_INSTANCE_ID;
• Inspect dimension granularity: select ORG_LEVEL_ID, TIME_LEVEL_ID, and DIMENSION1_LEVEL_ID through DIMENSION5_LEVEL_ID for a target level.
• Reporting-layer queries that drive the BIS performance-indicator dashboards consume these rows when rendering revenues or similar metrics broken down by the configured levels.

Related Objects

The table participates in two documented relationships:

• MSC_BIS_PERFORMANCE_MEASURES — referenced through MEASURE_ID (join on MEASURE_ID plus SR_INSTANCE_ID), supplying the measure the level is built upon.
• MSC_BIS_TARGETS — this table is referenced by MSC_BIS_TARGETS.TARGET_LEVEL_ID, so targets link back to their defining level.

Because the object is a BIS planning table, related structures also include the MSC_BIS dimension and level definitions referenced by ORG_LEVEL_ID, TIME_LEVEL_ID, and the DIMENSION*_LEVEL_ID columns, and the function references behind COMPUTING_FUNCTION_ID and REPORT_FUNCTION_ID.