Search Results edw_calsum4_bsc

Overview

EDW_CALSUM4_BSC is a staging and aggregation table owned by the BIS schema within Oracle E-Business Suite, catalogued under the BIS — Applications BIS product group. The name follows the convention used by Oracle's Enterprise Data Warehouse (EDW) extraction layer for Balanced Scorecard (BSC) content, and the CALSUM prefix indicates that the table materialises calendar-based summary measures rather than transactional detail. In the documented ETRM 12.1.1 physical schema the object carries ten columns, and the metadata explicitly records that it is "Not implemented in this database," meaning the definition ships as a reference or template object within the ETRM documentation set rather than as an active, populated table in the source environment described.

Because the table is a denormalised summary structure rather than a normalised master or transaction entity, it does not participate in foreign-key constraints as a parent. The only documented relationship is an inbound reference from EDW_CALSUM4_BSC.FACT_ID to ASO_ER_DATA_BIN_FACT, which ties each summary row to a fact definition in the Essbase/ASO analytic layer. The heuristic Data Vault classification derived from the mined key structure is standalone; this should be read as a modeling suggestion only. A standalone designation indicates that the object functions as an autonomous reference or derived structure not directly joined through enforced constraints to other hubs or links in the vault, though the FACT_ID dependency still anchors it logically to the analytic fact registry.

Key Information Stored

The ten documented columns fall into three functional groups: fact linkage, measure segregation, hierarchy/calendar assignment, and audit columns.

• FACT / FACT_ID — the fact name and its numeric identifier. FACT_ID is the column through which EDW_CALSUM4_BSC joins to ASO_ER_DATA_BIN_FACT, and it is the primary candidate for the business key linking a summary row to its underlying fact definition.
• MEASURE / MEASURE_ID — the measure name and its surrogate identifier, describing which metric (for example revenue, margin, or headcount) the summary row aggregates.
• TIMEHIER / HIERID — the time hierarchy name and hierarchy identifier, identifying the calendar structure under which the summary is computed.
• CALSUMMARY / CALSUM_ID — the calendar summary descriptor and its identifier, the business-key candidate that names the specific summarisation level (such as month, quarter, or fiscal period).
• LAST_UPDATE_DATE / CREATION_DATE — standard Oracle EBS audit columns recording when the row was last modified and when it was first created.

No surrogate primary key column such as a sequence-generated ID is documented independently of CALSUM_ID, and no unique index is confirmed in the supplied metadata. Practically, the composite of FACT_ID, MEASURE_ID, HIERID, and CALSUM_ID is the most defensible business-key candidate for de-duplication when the table is populated.

Common Use Cases and Queries

This table is used primarily to serve pre-computed calendar summaries to Balanced Scorecard dashboards and EDW extraction jobs, avoiding expensive on-the-fly aggregation of the underlying fact data. A representative query retrieves all summary definitions for a given fact and measure:

• SELECT FACT, FACT_ID, MEASURE, MEASURE_ID, TIMEHIER, HIERID, CALSUMMARY, CALSUM_ID FROM EDW_CALSUM4_BSC WHERE FACT_ID = :p_fact_id AND MEASURE_ID = :p_measure_id;
• Joining to the fact registry to obtain fact metadata: SELECT c.FACT, c.CALSUMMARY, f.FACT_NAME FROM EDW_CALSUM4_BSC c, ASO_ER_DATA_BIN_FACT f WHERE c.FACT_ID = f.FACT_ID;
• Incremental extraction for downstream warehouses using the audit columns: SELECT * FROM EDW_CALSUM4_BSC WHERE LAST_UPDATE_DATE >= :p_since;

Typical consumers are BSC scorecard loaders, EDW staging routines, and reconciliation reports that compare summary totals against their source facts.

Related Objects

The most significant related object is the parent fact definition table referenced by the documented foreign key:

• ASO_ER_DATA_BIN_FACT — joined on EDW_CALSUM4_BSC.FACT_ID = ASO_ER_DATA_BIN_FACT.FACT_ID; supplies the fact registry entry for each summary row.
• Other EDW summary staging tables in the BIS schema following the EDW_CALSUM naming convention, which share the FACT_ID, MEASURE_ID, and CALSUM_ID design pattern.
• BSC scorecard and Essbase/ASO analytic load programs that read from this staging table to populate scorecard cubes.
• EDW extraction and interface concurrent programs that stage data into the BIS schema from source EBS modules.

Because the object is documented as not implemented in the reference database and carries a standalone Data Vault classification, integrators should verify its physical presence and population status in each target environment before relying on it as an active data source.