Friday, January 18, 2013

Table Organization in OBAW (Oracle Business Analytics Warehouse)


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_GStores parameters and defaults that are used in ETL processes. Data warehouse
W_DUAL_GUsed to generate records for the Day dimension.Data warehouse
W_ETL_RUN_SStores 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_GStores cost lists.Data warehouse
W_EXCH_RATE_GStores exchange rates.Data warehouse
W_LOV_GStores lists of values in a way that translations can be done effectively using SQL.Data warehouse
W_LST_OF_VAL_GStores 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_GStores the list of values for the LOV types in whichthe ETL process finds exceptions.Data warehouse
W_UOM_CONVERSION_GStores a list of From and To UOM codes and their conversion rates.Data warehouse
S_ETL_COSTLSTMirror table of W_COST_LST_G. Used by real-time Analytics processes.Transactional database
S_ETL_EXCH_RATEMirror table of W_COST_LST_G. Used by real-timeAnalytics processes.Transactional database
S_ETL_CTRYREGNMirror table of W_EXCH_RATE_G. Used byreal-time Analytics processes.Transactional database
S_ETL_CURR_RUNStores the last ETL run date for which forecasts wereextracted and the current ETL run date when forecasts are being extracted.Transactional database
S_ETL_RUNMirror table of W_ETL_RUN_S.Transactional database
S_ETL_TIME_DAYA 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_DAYMirror table of W_DAY_D. Used by real-time Analytics processes.Transactional database
S_ETL_ERRHLPA seeded table. Stores the error messages displayed in exception reports.Transactional database
S_ETL_ERRLOGStores exceptions.Transactional database
S_ETL_INDUS_VERA 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_LOVMirror table of W_LOV_D. Used by real-time Analytics processes.Transactional database
S_ETL_PARAMMirror table of W_PARAM_G. Used to extract and convert exchange rates to the data warehouse’s currency.Transactional database
S_ETL_PRD_ATTRStores all possible attributes for a given product.Transactional database
S_ETL_PRD_RELStores class inheritance of a product.Transactional database

Column Format Descriptions (Prefix+ ‘_’ + Column_Name + ‘_’ + Suffix) 
Prefix/SuffixDescriptionIn Table Types
W_Domain value columns_D
_CDCode Field_D, _DS, _FS, _G, _GS
_DTDate Field_D, _DS, _FS, _G, _DHL, _DHLS
_ILanguage Independent Code._D, _MD
_ID_ID columns are used in _FS tables. They correspondto the _WID columns of the corresponding _F table._FS
_FLGIndicator or Flag._D, _DHL, _DS, _FS, _F, _G,_DHLS
_WIDIdentifier generated by Oracle BI linking dimension and fact tables, except for ROW_WID._F, _A, _DHL
_NAMEName corresponding to the code column (columns ending with _CODE)_D, _F, _A
_DESCLong 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.