OBAW Table Format: Prefix + ‘_’ + Table_Name + ‘_’ + Suffix
Prefix Values Prefix Descriptions
W_ Warehouse Table
WS Staging Tables for Usage Accelerator
Suffix Values Suffix Descriptions
_A Aggregate
_D Dimension
_DS Staging for dimension
_DH Dimension Hierarchy
_DHL Dimension Helper Table
_DHLS Staging for Dimension Helper
_F Fact
_FS Staging for Fact
_G, _GS, _S Internal Table
_H Helper table
_M Map dimension
_MD Mini dimension
_UD Unbounded dimension
_TMP Pre staging temporary table
Descriptions:
Aggregate tables (_A) Contain summed (aggregated) data.
Dimension tables (_D) Star analysis dimensions.
Staging tables for Dimension (_DS) Tables used to hold dimension information that have not been through
the final ETL transformations.
Staging tables for Usage Accelerator (WS_) Tables containing the necessary columns for the ETL transformations.
Dimension Hierarchy tables (_DH) Tables that store the dimension’s hierarchical structure.
Dimension Helper tables (_DHL) Tables that store M:M relationships between two joining dimensiontables.
Staging tables for Dimension Helper (_DHLS) Staging tables for storing M:M relationships between two joining
dimension tables.
Fact tables (_F) Contain the metrics being analyzed by dimensions.
Fact Staging tables (_FS) Staging tables used to hold the metrics being analyzed by dimensions
that have not been through the final ETL transformations.
Internal tables (_G, _GS, _S) Internal tables are used primarily by ETL mappings for datatransformation
and controlling ETL runs. Not even administered through DAC.
Helper tables (_H) Helper tables are inserted between the fact and dimension tables to support
a many-to-many (M:M) relationship between fact and dimension records.
Map dimension tables (_M) Tables that store cross-referencing mappings between the external data ID
and the internal ID.
Mini dimension tables (_MD) Include combinations of the most queried attributes of their parent dimensions.
The database joins these small tables to the fact tables.
Primary extract tables (_PE) Tables that are used to support the soft delete feature. The table includes all the primary key columns (integration ID column) from the source system. When a delete event happens, the full extract from the source compares the data previously extracted in the primary extract table to determine if a physical deletion was done in the transactional database. The soft delete feature is disabled by default. Therefore, the primary extract tables are not populated until you enable the soft delete feature.
Unbounded dimension tables Tables containing information that is not bounded in transactional
database data but should be treated as bounded data in the OBAW.
- A fact table in the Oracle Business Analytics Warehouse has a surrogate key only if the records in the fact table need to be updated or if the fact table has an aggregate table on top of it.
- Importance of ROW_WID column
- Dimension Class Tables are physical tables that store multiple logical entities that have similar attributes.
-
- Unique numeric key generated as part of dimension load process.
- Used to join dimension tables to any fact/other tables.
- ROW_WID value of zero is always reserved for ‘Unspecified’.
Classic example is W_XACT_TYPE_D which is a single physical table representing a dimension in OBAW that houses all logical entities like sales order types, sales invoice types, purchase order types etc.
- Mini Dimension Tables is a smaller subset of dimension tables based on frequency of usage to speed up the query performance. Mini Dimensions can be views/materialized views on top of large dimensions or Dimension Class tables depending on refresh schedules of the parent dimensions. Example: W_RESPONSE_D dimension has W_RESPONSE_MD, W_AGREE_MD, W_ASSET_MD, W_OPTY_MD etc.
- Types of hierarchical tables in OBAW are structured hierarchy and parent-child hierarchy tables. While the former has defined number of levels, the later does not.
| Name | Purpose | Location |
| W_PARAM_G | Stores parameters and defaults that are used in ETL processes. | Data warehouse |
| W_DUAL_G | Used to generate records for the Day dimension. | Data warehouse |
| W_ETL_RUN_S | Stores a record for every ETL run with a status of running when the ETL is running and Completed when the ETL finishes successfully. | Data warehouse |
| W_COSTLST_G | Stores cost lists. | Data warehouse |
| W_EXCH_RATE_G | Stores exchange rates. | Data warehouse |
| W_LOV_G | Stores lists of values in a way that translations can be done effectively using SQL. | Data warehouse |
| W_LST_OF_VAL_G | Stores the list of values. A temporary table that is built during every ETL run and used to populate W_LOV_D. | Data warehouse |
| W_LOV_EXCPT_G | Stores the list of values for the LOV types in whichthe ETL process finds exceptions. | Data warehouse |
| W_UOM_CONVERSION_G | Stores a list of From and To UOM codes and their conversion rates. | Data warehouse |
| S_ETL_COSTLST | Mirror table of W_COST_LST_G. Used by real-time Analytics processes. | Transactional database |
| S_ETL_EXCH_RATE | Mirror table of W_COST_LST_G. Used by real-timeAnalytics processes. | Transactional database |
| S_ETL_CTRYREGN | Mirror table of W_EXCH_RATE_G. Used byreal-time Analytics processes. | Transactional database |
| S_ETL_CURR_RUN | Stores the last ETL run date for which forecasts wereextracted and the current ETL run date when forecasts are being extracted. | Transactional database |
| S_ETL_RUN | Mirror table of W_ETL_RUN_S. | Transactional database |
| S_ETL_TIME_DAY | A seeded table. The date from this table is extractedand loaded into W_TIME_DAY_D. Also used by real-time Analytics processes. | Transactional database |
| S_ETL_DAY | Mirror table of W_DAY_D. Used by real-time Analytics processes. | Transactional database |
| S_ETL_ERRHLP | A seeded table. Stores the error messages displayed in exception reports. | Transactional database |
| S_ETL_ERRLOG | Stores exceptions. | Transactional database |
| S_ETL_INDUS_VER | A seeded table. Stores the vertical for every SIC industry code. This table is used to load W_INDUSTRY_D and also used in real-time Analytics processes. | Transactional database |
| S_ETL_LOV | Mirror table of W_LOV_D. Used by real-time Analytics processes. | Transactional database |
| S_ETL_PARAM | Mirror table of W_PARAM_G. Used to extract and convert exchange rates to the data warehouse’s currency. | Transactional database |
| S_ETL_PRD_ATTR | Stores all possible attributes for a given product. | Transactional database |
| S_ETL_PRD_REL | Stores class inheritance of a product. | Transactional database |
Column Format Descriptions (Prefix+ ‘_’ + Column_Name + ‘_’ + Suffix)
| Prefix/Suffix | Description | In Table Types |
| W_ | Domain value columns | _D |
| _CD | Code Field | _D, _DS, _FS, _G, _GS |
| _DT | Date Field | _D, _DS, _FS, _G, _DHL, _DHLS |
| _I | Language Independent Code. | _D, _MD |
| _ID | _ID columns are used in _FS tables. They correspondto the _WID columns of the corresponding _F table. | _FS |
| _FLG | Indicator or Flag. | _D, _DHL, _DS, _FS, _F, _G,_DHLS |
| _WID | Identifier generated by Oracle BI linking dimension and fact tables, except for ROW_WID. | _F, _A, _DHL |
| _NAME | Name corresponding to the code column (columns ending with _CODE) | _D, _F, _A |
| _DESC | Long Description corresponding to the code column (columns ending with _CODE) | _D, _F, _A |
- System Fields/Columns are system generated and should never be modified by user. Ex: ROW_WID, CREATED_ON_DT, CHANGED_ON_DT, DELETE_FLG etc.
- Users of OBAW will be assigned to one of the 10 built-in security groups at the session level.
- OBAW supports three currency types while analyzing data.
- Domain values for all calendar tables have columns that indicate the relationship between a record and the current system date (period). These values are ‘Previous’, ‘Current’, Next’, ‘?’
-
- Document Currency –> Currency in which transaction has occurred
- Local Currency –> Accounting Currency of the legal company where the transaction occurred.
- Global Currency –> Predefined currencies by which analysis can be reported.
