Search Results bic_measures_all_pk

Overview

BIC_MEASURES_ALL is a table owned by the BIC schema, the repository behind Oracle's Customer Intelligence module. Customer Intelligence (BIC) is documented as obsolete in the ETRM 12.1.1 / 12.2.2 reference set, meaning the product is no longer actively delivered or supported, yet the underlying schema objects frequently remain in databases where BIC was once licensed or where upgraded instances have retained legacy history. The table stores the seeded master list of measures — the quantitative definitions such as revenue, order count, or margin — that drive analytical scoring, bucket assignment, and customer segmentation within the BIC engine.

Under the heuristic Data Vault classification supplied in the metadata, BIC_MEASURES_ALL is satellite-leaning. It behaves as a descriptive attribute store hanging off a business key rather than as a pure hub or a relationship link. Modeling teams reconstructing a Data Vault layer from the EBS estate should treat MEASURE_CODE plus ORG_ID as the natural business key and MEASURE_ID as the technical surrogate, wrapping the descriptive columns in a satellite structure with effectivity dating from the audit columns.

Key Information Stored

The documented physical schema carries 15 columns. The most significant are:

• MEASURE_ID — the surrogate primary key, enforced by the BIC_MEASURES_ALL_PK constraint. It is the column other fact-like tables use to reference a measure.
• MEASURE_CODE — the seeded, human-readable business identifier for the measure. It is the join column to the attribute table and forms part of the second unique index.
• NAME and DESCRIPTION — the display label and longer explanation presented to users in the Customer Intelligence workbench.
• ORG_ID — the operating unit discriminator, allowing the same measure code to be defined once per organization. Together with MEASURE_CODE and the edition column it forms the BIC_MEASURES_U2 unique index.
• ENABLED_FLAG — controls whether a measure is active for scoring and display; disabled measures remain in the table for historical reference.
• WEIGHT — the numeric contribution this measure makes when the BIC scoring engine aggregates inputs into a composite score.
• TIME_UNIT_CODE — the period basis, such as day, month, or quarter, over which the measure is evaluated.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns; LAST_UPDATE_DATE also supports incremental extraction in interfaces.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, applying Oracle Applications row-level security partitioning.
• ZD_EDITION_NAME — the editioning column present in 12.2 online patching, which is why both unique indexes (BIC_MEASURES_U1 on MEASURE_ID plus ZD_EDITION_NAME, and BIC_MEASURES_U2 on MEASURE_CODE, ORG_ID plus ZD_EDITION_NAME) include it. On 12.1.1 this column does not exist.

Common Use Cases and Queries

The primary analytical use is listing and auditing the active measure catalog for a given operating unit. A representative query is:

• SELECT m.measure_id, m.measure_code, m.name, m.weight, m.time_unit_code FROM bic.bic_measures_all m WHERE m.enabled_flag = 'Y' AND m.org_id = :org_id ORDER BY m.measure_code;
• Joining to the attribute side to enumerate how each measure is configured: SELECT m.measure_code, a.attribute_code FROM bic.bic_measures_all m, bic.bic_measure_attribs a WHERE m.measure_code = a.measure_code.
• Locating measures that participate in bucketization: SELECT DISTINCT m.measure_code FROM bic.bic_measures_all m, bic.bic_measure_buckets b WHERE m.measure_id = b.measure_id.
• Incremental extraction for a warehouse load: SELECT * FROM bic.bic_measures_all WHERE last_update_date >= :high_water_mark.

Because the product is obsolete, the most frequent real-world queries are migration assessments that determine whether historical BIC configurations must be preserved before the schema is dropped.

Related Objects

• BIC_MEASURE_ATTRIBS — the principal dependent table. The metadata shows repeated foreign key relationships from BIC_MEASURES_ALL.MEASURE_CODE to BIC_MEASURE_ATTRIBS, indicating multiple attribute definitions attach to each measure code. Join on MEASURE_CODE.
• BIC_MEASURE_BUCKETS — references this table through MEASURE_ID, defining the score bands into which measure values are grouped. Join on MEASURE_ID.
• FND_SECURITY_GROUPS — the referenced security master, joined on SECURITY_GROUP_ID to enforce row-level access.
• BIC_MEASURES_ALL_PK, BIC_MEASURES_U1, BIC_MEASURES_U2 — the constraints and unique indexes that enforce the surrogate key and the two business-key candidates.