Understanding the types of tables in Oracle Business Intelligence Data Warehouse

The Oracle Business Analytics Warehouse (OBAW) is based on the dimensional modeling technique where fact tables are joined to multiple related dimension tables to form a "star schema".   While at the most basic level the star schema can be represented by dimensions and facts, the process of building those tables in the Oracle Business Analytics Warehouse requires several more table types.   

The standard OBAW ETL (Extract, Transform, Load) process includes the following tables:

Dimension Staging  (_DS) contains data loaded from a source application database.

Dimension Target   (_D)  contains the transformed dimension data for the final star schema model.

Aggregate tables (_A) sontains pre-summarized data at a higher grain than the base fact table.  Aggregate tables are used to improve query performance on front end tools.

Fact Staging tables (_FS) contains staged metric data prior to ETL transformation.   Foreign key values for the dimensions have not yet been translated to WID (warehouse ID) key values.

Fact tables (_F) contains the metrics to be analyzed along with foreign key WID (warehouse id) values pointing to the associated dimensions.

Delete tables (_DEL)  contains the primary keys of rows that were physically deleted from the source application.  These rows are either flagged or deleted from the data warehouse during the ETL process.

Dimension Hierarchy tables (_DH) contains dimension hierarchical structure information.  These are flattened hierarchies with multiple columns representing each level of the hierarchy, typically a code and name pair at each level and allow for rolling up data at various summary group levels.

Staging tables for Dimension (_DS) contains dimension hierarchy information that has not been processed by final ETL transformations.

Internal tables (_G, _GS, _S) Internal tables are referenced by ETL mappings for data transformation purposes as wells as controlling ETL execution.

Mini dimension tables (_MD) Include combinations of the most queried attributes of
their parent dimensions. These smaller "combo" tables are then joined to the fact tables in order to improve query performance.

Persisted staging tables (_PS) contains a static copy of source table data with additional calculated columns in order to improve ETL processing.    These tables are useful when there isn't a clear incremental extract date available to handle changes that affect multiple records.  These tables are never truncated except during a full load.

Pre-staging temporary table (_TMP) Source-specific tables used as part of the ETL processes to make the source data match the standard staging table structure. These tables contain intermediate results that are created as part of the conforming process.

Five things you can do today to improve your OBIEE skills

As they say, "Your skills pay the bills".    If you are in a position where you are responsible for delivering OBIEE expertise, you will want to continually work on improving your skills in that area.

Here are five things you can do today to help you achieve that goal:

1.  Buy one or both of the following books and read them cover-to-cover.  You will be surprised at how much you DON'T know about OBIEE when you do:

Oracle Business Intelligence 11g Developers Guide [Paperback] Mark Rittman (Author)

Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial [Paperback] Christian Screen (Author), Haroun Khan (Author), Adrian Ward (Author) 

2. Install the OBIEE 11g Sample Application Virtual Machine

http://hcminsight.blogspot.com/2013/02/obiee-sampleapp-v207.html


3. Make sure you bookmark the following blogs and review them on a regular basis:
 http://www.rittmanmead.com/blog/
http://greatobi.wordpress.com/
http://www.perceptualedge.com/blog/
http://www.artofbi.com/

4. Review the release notes for OBIEE 11.1.1.6.6 and you may find some interesting features that don't appear in the regular documentation like this one:

http://docs.oracle.com/cd/E23943_01/relnotes.1111/e10132/biee.htm#CACCBIAF

5. Take one or more the free online OBIEE 1g quizzes available from HCMinsight.   These quizzes cover everything in the OBIEE platform from RPD to Answers to Security and more.   If you can score an 80% on these quizzes, you will be well prepared to pass Oracle's certification exam.

http://hcminsight.blogspot.com/2012/12/obiee-11g-online-quizzes-available.html

OBIEE SampleApp V207

OBIEE SampleApp V207 has been available on Oracle Technology Network for approximately six months.  It is the best way to get an OBIEE 11.1.1.6.2 BP1 environment up and running for test purposes or to examine various advanced reporting and data modeling techniques.

Download the sample application here:

Oracle Business Intelligence Enterprise Edition Plus Samples

In addition to some small datasets in the environment, there is a much larger database containing over 6 million rows of U.S. airline flight delay information.    This larger dataset is useful for demonstrating some the of latest features of OBIEE 11g including Exalytics support.   There are also examples of new visualizations in the sample dashboards.