Search Results bic_dimv_geography




Overview
Explore in AI Chat

Overview

BIC_DIMV_GEOGRAPHY is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BIC – Customer Intelligence product family. BIC (Customer Intelligence) is an obsolete module in both EBS 12.1.1 and 12.2.2, so this object survives primarily for backward compatibility with historical reports, OBIEE/BI Publisher data models, and data-warehouse extraction routines that were originally built against the Customer Intelligence star schema. The object is catalogued as a VIEW with VALID status and is documented in the ETRM repository under the obsolete BIC product node.

The view presents a conformed geography (location) dimension. It exposes country, state, and city attributes at a single grain, allowing transactional facts such as customers, orders, and sales to be sliced and filtered geographically. As a dimension view it is typically joined on a surrogate key (GEOGRAPHY_ID) to fact views or tables within the BIC analytics layer.

Underlying Base Objects

The documented view text defines BIC_DIMV_GEOGRAPHY as the UNION ALL of two source sets:

  • BIM_GEOGRAPHY — the primary BIC geography table. Distinct combinations of COUNTRY, STATE, and CITY are selected, each assigned a literal GEOGRAPHY_ID of 1.
  • FND_LOOKUPS — the standard EBS flex/validation lookup table. A single "unknown" or "not applicable" row is manufactured from lookup type BIC_VALUE_TYPE with lookup code -999, assigning GEOGRAPHY_ID -999 and using the lookup MEANING to populate all three name columns.

The statement is declared WITH READ ONLY, confirming no DML is permitted. The header for the view references no base objects when the metadata was extracted, but the documented SQL clearly identifies BIM_GEOGRAPHY and FND_LOOKUPS as the operative sources.

Key Columns

  • GEOGRAPHY_ID — surrogate key for the dimension member; 1 for real geography rows and -999 for the seeded unknown member used in outer joins.
  • COUNTRY / COUNTRY_NAME — the country code and its presentation label (aliased identically in the query).
  • STATE / STATE_NAME — state or province code and label.
  • CITY / CITY_NAME — city code and label.

Because GEOGRAPHY_ID is a constant (1) for all genuine members, the true uniqueness of a row is the COUNTRY / STATE / CITY combination rather than the ID column. Join logic must account for this; the -999 member is intended to satisfy referential integrity when a fact has no matching geography.

Common Use Cases and Queries

Typical uses include geography drill-down in BIC dashboards, country/state roll-ups for customer segmentation, and supply of the "unknown" member to outer-joined fact queries. A representative query listing all members is:

SELECT geography_id, country_name, state_name, city_name
FROM   apps.bic_dimv_geography
ORDER  BY country_name, state_name, city_name;

Reporting against a fact joined to the dimension could filter using the deterministic key:

SELECT g.country_name, g.state_name, f.sales_amount
FROM   apps.bic_dimv_geography g, apps.bic_fact_sales f
WHERE  g.geography_id = f.geography_id
AND    g.geography_id <> -999;

Given the obsolete status of BIC, implementations on 12.1.1 and 12.2.2 should treat this view as a legacy compatibility artifact and validate it before reuse in new reporting solutions.