FYI: Maximizing Your Oracle Support and Oracle Documentation for OBIEE 11g

When most OBIEE Architects and Developers encounter a problem or road block, their first solution (assuming they don't know how to resolve the issue) is to use google to search for error codes, potential solutions, or at least other developers to commiserate with!

An exceptional resource for OBIEE 10g/11g issues that I often see underused or completely ignored is Oracle's Support web site - http://support.oracle.com  . Yes, we all use Oracle Support to create trouble tickets but the Support site offers much more than just the ability to raise defects.

Here are key documents that all Oracle Business Intelligence Architects should follow if they want to stay current with the latest patches,  news, quarterly updates, and official Oracle announcements (like Oracle officially dropping Premier support of OBIEE 10g 10.1.3.4!)

• Information Center: Business Analytics Index (EPM/BI) [ID 1378677.2]
• Why: This is the central 'home page' for all of Oracle's Analytics products. You'll have access to the Quarterly BI News Letter, Oracle OBIEE Community, and more.

( Make sure you are book marking all of these using the star icon !)

• Oracle Business Intelligence Enterprise Edition (OBIEE) Product Information Center (PIC) [ID 1267009.1]
• Why: This is the starting point for all official Oracle Business Intelligence guides including : Troubleshooting, patching, and white papers
• Information Center: Oracle Business Intelligence Enterprise Edition (OBIEE) Release 10g and Later [ID 1349983.2]
• Why:  This  document aggregates posts from Oracle's OBIEE community, new features and processes,  and highlights new articles
• The Official Oracle Business Intelligence Enterprise Edition Community - https://communities.oracle.com/portal/server.pt/community/obiee/475
• Why: This is similar to Oracle's freebie forum http://forums.oracle.com with the exception that there are dedicated Oracle OBIEE Architects who reply to your posts - definitely worth the bookmark!

I also recommend subscribing to Oracle's Hot Topics email for OBIEE as it's another way for you to stay current with OBIEE news if you don't have the time to review the above web sites. You can subscribe as follows:

Navigate to My Oracle Support -> Settings -> Hot Topics E-mail

Then specify the products of your interest. I recommend: Business Intelligence Interactive Dashboard,  Business Intelligence Server Administrator, Oracle Business Intelligence Applications Foundation, Oracle Business Intelligence Server Enterprise Edition, Oracle Business Intelligence Suite Enterprise Edition

Of course, the Official Oracle Documentation is another must have:

• Oracle Fusion Middleware 11g (11.1.1.6) Documentation Library
• Business Intelligence Documentation for Oracle Fusion Middleware 11g (11.1.1.6)

FYI: Kimball Fact Table Primer (Star Schemas)

One fact table does not fit all, depending on your requirements you may have a need to identify all invoices transactions for a given point in time.  Maybe the end user wants to identify sales revenue for a regular, predictable date interval (such as quarter).  Also consider the order fulfillment pipeline which creates a scenario where a supply chain specialist must identify the span of time it takes a product to be processed through the pipeline.

Should 1 denormalized fact table design be used for the above scenarios? Of course not . Take, for example, Oracle Business Intelligence Applications HR Analytics 7.9.6.3 . Oracle designers created work force fact tables of various designs to capture the business process being measured: Transactional fact tables for employee salary, periodic fact tables for headcount, and accumulating fact tables for the HR recruitment process.

Below is a primer of Kimball fact tables and the characteristics of each one. Before selecting the fact table type to use for your design, always make sure you:

1) Identify the business process you are trying to measure
2) Determine the grain of the fact table
3) Identify the attributes of the fact table (dimensions)
4) Identify the measures to be captured in the fact table.

Also highly recommended: Read Kimball's Data Warehouse Tool kit. This is a must read for any OBIEE Architect. The table below is referenced from the above book.

CHARACTERISTIC	TRANSACTION GRAIN	PERIODIC SNAPSHOT GRAIN	ACCUMULATING SNAPSHOT GRAIN
Time period represented	Point in time	Regular, predictable intervals	Indeterminate time span, typical short-lived
Grain	One row per transaction event	One row per period	One row per life
Fact table loads	Insert	Insert	Insert and update
Fact row updates	Not revisited	Not revisited	Revisited whenever activity
Date dimension	Transaction date	End-of-period date	Multiple dates for standard milestones
Facts	Transaction activity	Performance for predefined time interval	Performance over finite lifetime

These three fact table variations are not totally dissimilar because they share conformed dimensions, which are the keys to building separate fact tables that can be used together with common, consistent filters and labels. While the dimensions are shared, the administration and rhythm of the three fact tables are quite different.

FYI: OOTB HR Analytics Workforce Fact table Informatica Workflow (Peoplesoft HCM 9.1)

w_wrkfc_evt_month_f - just mentioning it to OBIEE practitioners send shivers down their back.

Why? This behemoth of a fact table is the fact table of OBIA HR Analytics. It is used across all OBIA HR subject areas for cross functional analysis. It has 12 logical aliases in the BMM and it exists in all 3 of Kimball's fact table types: Transactional, period, and accumulating.

There is no other OBIA fact table that spans as many subject areas, reports, and KPIs as w_wrkfc_evt_month_f.

Which brings me to the following point.  

What if the business requirement results in modifications to this fact table?

Proceed with caution. Can the business process you're trying to measure be created using another fact table? Would it be less work to create a new star schema than to modify the workforce event tables?


Oracle provides little documentation on this fact table in terms of impacted Informatica work flows and mappings.  Any modifications to the w_wrkfc_evt_month_f fact table or its cousins ( e.g. _agg), will require your ETL developer to use the existing mappings.

Here is how  w_wrkfc_evt_month_f is populated for OOTB OBIA HR 7.9.6.3 with PSFT HCM 9.1 as the source:

Step 1: Temp table

Informatica Folder: (SDE_PSFT_91_Adaptor)

W_PSFT_WEVT_JOB_PS

1. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full
2. SDE_PSFT_PersistedStage_WorkforceEvent_Job
3. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full (Mapping)
4. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Mntn (Mapping)

Step 2: Staging table

W_WRKFC_EVT_FS

1. SDE_PSFT_WorkforceEventFact_Job
2. SDE_PSFT_WorkforceEventFact_Job_Full
3. SDE_PSFT_WorkforceEventFact_Job (Mapping)

Step 3: Fact table (does not exist in Metadata layer, only used for ETL PLP)

Informatica folder: (SILOS)

 

W_WRKFT_EVT_F

1. SIL_WorkforceEventFact
2. SIL_WorkforceEventFact (Mapping)

Step 4:  Fact table PLP 2

W_WRKFC_EVT_MERGE_F

1. PLP_WorkforceEventFact_Merge
2. PLP_WorkforceEventFact_Merge_Full
3. PLP_WorkforceEventFact_Merge (Mapping)
4. PLP_WorkforceEventFact_Merge_Mntn (Mapping)

 

Step 5:  Metadata/RPD fact table
 

W_WRKFC_EVT_MONTH_F

1. PLP_WorkforceEventFact_Month_PositionHierarchy_Update_TD
2. PLP_WorkforceEventFact_Month_PositionHierarchy_Update
3. PLP_WorkforceEventFact_Month_Full
4. PLP_WorkforceEventFact_Month
5. PLP_WorkforceEventFact_Month_PositionHierarchy_Update_TD (Mapping)
6. PLP_WorkforceEventFact_Month_PositionHierarchy_Update (Mapping)
7. PLP_WorkforceEventFact_Month_Mntn (Mapping)
8. PLP_WorkforceEventFact_Month (Mapping)

For those of you following along, that's right, the data has the possibility of flowing through 21 different mappings! You must consider this before you attempt to modify this fact table. 

Unless absolutely critical, I recommend creating a new star schema.