Three months ago started the Oracle program: Are You The Smartest.
For me it is an opportunity to test my current knowledge level and to extend my knowledge.
After every session I follow, I will write a brief summary as part of the preparation for the test.
I will continue with the summary of the following session.

ARCHITECTS TRAINING – BUSINESS INTELLIGENCE – Oracle Business Intelligence Applications – Customizations

This 1 hour during session was divided into the following parts:

• Introduction to customization
• Types of customizations
• Typical cases of customization

Introduction to Customization

• By customization we mean changing the data warehouse structure, ETL logic and OBIEE repository and front-end to cover the customer specific needs that aren’t covered by the out-of-the-box solution
  • Each business has it’s own reporting requirements and they might be very special
  • Business environment changes and new requirements come up constantly
• The out-of-the-box BI Apps offers a good framework to build custom reporting
• Best practice customization is additive so we don’t lose the out-of-the-box objects when we customize BI Apps

Reasons for Customization

Transactional Applications Modifications 

The source system might have been customized for customer’s needs
• User Interface
• Data model extensions
• New entities (business components)
• Different relationships between components
• Modified visibility rules
• Different authorization/authentication
• These changes have to be addressed in BI Apps, too, with customization

BI Applications Extensions

When it comes to reporting, these are some of the typical requirements
• Company-specific dashboards, analysis, navigation
• New KPI’s or refining out-of-the-box ones
• Custom hierarchies
• Differences in change data capture logic
• New facts and/or dimensions for unsupported functionality
• Additional source systems or data warehouses which have to be integrated

Typical Effort & Customization Balance

80% of time is spent on dashboards en reports. 20% on more complex things.

OBIEE (Oracle Business Intelligence Enterprise Edition) Dashboard Customization 

Easiest and most frequently done customization, for example

• Changes of format, structure, navigation etc. in the existing reports, say
  • Data format such as date or currency format
  • Data presentation in pivot table, graph etc.
  • Grand totals, ordering, new filters, selection steps, dashboard prompts
  • Custom navigation from one report to another
• Adding new out-of-the-box or custom attributes or measures to the reports
• Building new dashboards and analyses using out-of-the-box or custom objects

OBIEE Metadata Customization

• Changes in OBIEE repository, the following are some typical changes
• Changing the structure of subject areas:
  • Drag and dropping more presentation columns
  • Deleting unnecessary columns from the presentation layer
  • Reordering and renaming objects
  • Changing the security (visibility) of subject areas
  • Creating new subject areas with different contents to different user groups
  • With OBIEE 11g combining several subject areas in the same report is so easy that they should be kept small
  • Implement repeating changes ones in the repository and not over and over again in every report.
• Creating new fact measures
  • OBIEE fact columns can be :
    • Base measures, which come from the data warehouse and do not involve any calculation in OBIEE
    • Derived measures, which are derived in OBIEE repository from base measures
  • New derived measures can easily be calculated from the base measures
    • Level-based measures to create totals on different levels of dimensions are another extremely powerful option in OBIEE
    • Case-statement and filters can often help you create custom measures based on the out-of-the-box measures
• Sometimes new attributes can also be built in OBIEE based on the existing attributes
• Changing logical dimensions
  • Adding new levels to the logical dimensions
  • Changing preferred drill-down path
  • Changing drill-down display columns
  • Creating new logical dimensions
• Adding new logical and physical data sources
  • Mapping new custom columns or custom tables
  • New aggregate tables
  • Aliasing out-of-the-box tables, for example to create new custom date dimensions
  • Adding additional data sources, for example relational databases or Essbase cubes

 ETL Customization

ETL customization involves changes in the data warehouse structure and Informatica mappings

• The main effort is done in the database and Informatica, but the changes have to be mapped also in DAC and OBIEE repository

Customization Scenarios

• Type 1 – New columns from source systems added to the data warehouse
• Type 2 – New fact or dimension tables added to the data warehouse
  • We have to build the corresponding SDE and SIL mappings
• Type 3 – New data from additional data sources extracted using the universal adapters

Type I Customization

Typical Steps to Extend Mappings

• Copy the appropriate workflows, sessions and mappings to a custom Informatica folder
  • Only modify objects in the custom folders
  • Update DAC task properties to point to the custom folders
• Add the new column(s) to staging and target tables
  • Create the column(s) in the database
  • Then import them to Informatica, DAC and OBIEE repository
  • Add new query indexes for the target table columns in DAC, if needed

Adding Columns to Existing Tables

•  Customize the out-of-the-box SDE and SIL mappings
  •  Modify the extract query to include the new column(s)
  •  If new tables are needed import them and be careful that no rows are lost or unwanted rows returned because of new joins
  •  Take the new column(s) through the two mappings following the path of the placeholder column X_CUSTOM
  •  After changes validate the corresponding Informatica sessions
• Modify DAC execution plan
  • Generate parameters and set values for the custom DAC folders
  • Re-build the execution plan
• Map columns in the logical and presentation layer in OBIEE

Safe Path 

• Most mappings have a single placeholder column, named X_CUSTOM, that marks a safe path through the mapping.
• All extension logic should follow the same route through the mapping as X_CUSTOM. You can add additional transformations to the mapping, but they should follow the same route through the mapping as X_CUSTOM. The graphic shows the pre-configured logic in grey. You should not modify anything contained within these objects. You should add customizations to the existing mapping, which allows them to run parallel to the existing logic.

Points to Remember

• Encapsulated objects must never be customized unless directed by Oracle
  • An exception are the extract mapplets in SDE mappings which can be modified
• Exposed objects can be extended but must never be otherwise modified
• Minimize the number of changes to exposed objects by using custom objects
  • Custom objects are never changed during an upgrade

Type 2 Customization

Adding Additional Tables

• Sometimes we have to create new dimension and fact tables
• Custom SDE and SIL mappings have to be created to populate each custom table

Table Definitions

• Create new table(s) in database
  • Remember to include the required system columns
  • Then import them to Informatica, DAC and OBIEE repository
  • Add ETL indexes and new query indexes in DAC
  • Define table type and foreign key columns in DAC – important for dependency detection

More on Indices

• Staging tables typically do not require indices
• Create indices on all columns that the ETL uses for dimensions and facts
• Define indices to improve query performance
• If you create indices in the database be sure to register them in DAC

Custom Mappings

• Create new mappings in Informatica to contain your ETL logic
  • Always create custom objects in your custom folders
  • There should be only one target table per mapping
  • Thanks to the change capture and update logic we can normally use the same mapping for a full load and incremental load of a table
  • However, if necessary, you can create a different mapping for full and incremental ETL
• Reuse existing source and target definitions, transformations, mapplets and mappings to save development time and to follow the best practices
  • Take advantage of the out-of-the-box encapsulated logic such as lookups and mapplets to retrieve ETL_PROC_WID, convert
    currencies etc.
  • Adapt the out-of-the-box logic to your custom mappings, for example:
    • Treatment of surrogate keys and system columns
    • Change capture and update strategy logic
    • Creation of unspecified records, slowly changing dimension, aggregates etc.

Custom Workflows

• Create a session for each mapping
  • Set the appropriate source and target connection values in Workflow Manager
• Create custom workflows
  • Each workflow should load only one table
  • The workflow name should match a session name that is used inside the workflow
• You have to create a workflow and session for both full load and incremental load
  • Usually the sessions and workflows can simply be duplicated with two different names

DAC Development 

• Create new tasks in DAC
  • In the properties of a DAC task you can define the command for incremental and full load – these are the names of workflows in Informatica
  • Other important settings are for example execution type, folder name, source and target and task phase
  • Synchronize the tasks to add the source and target tables
    • Set the properties for the tables, for example the target table truncate options 
• Create new subject area in DAC
  • If your customization is an enhancement of an existing star then assemble the corresponding DAC subject area again
  • If you’ve built a new star then create a custom subject area in DAC
• Create new execution plan
  • Reuse existing execution plan or create a new one
    • Add the new subject areas, if any
  • Generate and set the parameters
  • Build

More on Truncating Target Tables

• Truncating should be done through the DAC
• A single DAC task has one placeholder for a full load, and one for an incremental load

OBIEE Development

• New data warehouse tables need to be mapped in OBIEE
  • Tables imported, aliased and joined in the physical layer
  • New logical dimensions, dimension tables and facts defined in the logical layer
  • New subject areas created in the presentation layer

Naming Conventions

• Separate your custom development as well as possible from out-of-the-box with naming convention in OBAW, Informatica, DAC and OBIEE
  • For example prefixes Custom_ and C_ can be used
  • In OBIEE dummy columns can be used to separate the out-of-the-box and custom columns