I would like to share some of the Oracle Business Intelligence Application (OBIA) customization tips and best practices guidelines from my knowledge base. This document is based on my experiences of BI Apps implementation over the period of last 10/11 years since Siebel days and extract from Oracle’s standard documentation around BI Apps.
OBIA Architecture
OBIA Architecture has following components i.e. ETL Architecture, Data Model, Metadata (Repository), Dashboard/Webcat along with security.
OBIA Pre-Built Contents
Oracle Business Intelligence Applications 7.9.6.3 are pre-built solutions from source to dashboard. The pre-built solution contents are:
Pre-built Analytics Data Warehouse Model
The Business Analytics Data Warehouse is the persistent layer where information is stored for retrieval when required. The data warehouse resides in a relational database platform like Oracle, IBM DB2 LUW, Teradata or Microsoft SQL Server. It consists of transaction grain tables used to capture facts from the key business processes across the organization. The model consists of current snapshots, periodic snapshots and aggregate tables to support complex analysis requirements along with the hundreds of dimension tables conformed across the fact tables which contribute to enrich the analyses.
Pre-built Data Integration Components (ETL/E-LT)
Data Integration components ( data extraction routines) are used to integrate data from various operational systems such as E-Business Suite, PeopleSoft Enterprise, Oracle's Siebel CRM Applications, Oracle JD Edwards Applications, and third party legacy systems, and load the information in the Business Analytics Data Warehouse.
The data integration components are built using either the Informatica Power Center data integration platform (ETL) or Oracle Data Integrator (E-LT). It uses a layered approach based on data warehouse best practices, which is open and flexible which provides ease of configuration and customization to enable changing the out of the box transformation rules, adding additional attributes, facts and dimensions.
Pre-built Metadata (BI Repository)
The metadata layer includes the definition of role based views, dimensions, metrics, hierarchies, calculations, aggregation rules, canonical dime dimensions, time series metrics, roles and security. The metadata layer is built on the Oracle Business Intelligence Enterprise Edition platform using the Admin Tool, a core component of the BI server, which is designed to meet the requirements of the Common Information Model to deliver consistent and integrated information to business users, by capturing the data from multiple source systems. Oracle Business Intelligence Applications provides ready to use pre-built metadata covering dimensions, metrics and hierarchies. In addition, the pre-built metadata includes the capability to leverage the security profiles inherited from operational systems such as Oracle EBS, PeopleSoft and JD Edwards.
Pre-built OBI-EE dashboard and report content (Catalog)
The OBIEE Presentation Catalog contains pre-built metrics, analytic decision workflows, reports, dashboards and alerts used to deliver information, stored in the Business Analytics Data Warehouse, to information consumers. These services are built on the Oracle Business Intelligence Enterprise Edition delivery platform, which is designed to meet the requirements of an enterprise business intelligence solution. Pre-built reports and dashboards are built on industry best practices, and provide rich content covering functional areas including sales, service, marketing, finance, supply chain, order management, procurement and human resources.
Customization Best Practice – Keep it to Minimum
In line with other Oracle applications products in general, customization should be kept to minimum and wherever possible out-of-the-box (OOTB) functionality should be employed. This should be done in such manner that future upgrades to the product will not require significant amount of rework, i.e. where there is a good match between the requirements and the out-of-the-box functionality. However, in reality it is often unavoidable.
Customization Reality – It is Unavoidable
It is often seen that certain key business requirements are not covered by the pre-built contents (Out of Box contents), For example the OOTB Data Warehouse may not include certain attributes customer would like to report on from the source system, or more likely, require an external secondary data store (file, RDBMS, XML, and OLAP) to include additional reporting matrices.
For a rapid development we must re-use as much of the OOTB data model as possible to avoid “re-inventing the wheel” by developing data objects that have already been populated by BI-Apps. There are number of situations where there is a need to modify the pre-built contents.
In these cases it is important to follow certain guidelines and techniques when developing non-standard BI elements. This blog is an attempt to detail guidelines on how to develop custom objects to integrate into BI Apps. The idea is to follow the same design techniques and guidelines that BI Apps uses to build OOTB contents for customization. This approach would be helpful to maintain the oracle’s high standard development practices and keep “look and feel” like standard BI-Apps content.
Types of Customization and Impacts
There are 6 types of customizations that will be implemented. These customizations can be as simple as exposing already existing attributes in the DW to the Presentation Layer of OBI-EE. They can also be as complex as creating new dimension, facts and external data sources. The new report creation is not in scope of the this discussion.
The following are the main extensions covered in this blog.
TYPE A- Simple Extensions and Adding Simple/Complex Logical Definition
The Data Warehouse has a vast amount of attributes within its data model. However, not all this data is exposed to the repository and dashboards. One of the simplest customizations is to expose the attribute top the Presentation Layer of OBI-EE.
Creating new logical definitions (simple-moderate-complex) based on physical/logical columns already exposed should also be included into the Type A category. Time Series Logical columns are an example of this type of customization.
Impact:
· Changes only in the OBI-EE repository in the Physical, Logical and Presentation Layer are needed.
TYPE B- Attribute Extensions
Adding attributes to existing dimensions and measures to existing facts. The complexity of this customization depends on the SQL required to populate the attribute. If the attribute is part of an existing table in an existing query then the inclusion is very simple.
Impact:
- Additional column(s) to the physical staging and target tables.
- Changing existing SDE source mapping to add the additional column(s) to the SQL query and the target table.
- Changing existing SIL target mapping to add the additional column(s) to the SQL extraction query to the target table.
- Changes required to the OBI-EE repository to add the column to the Physical and modeled through to the Business and Presentation Layers.
TYPE C- Additional Fact, Parent Child Hierarchies Table, Bridge Table
Adding new Facts, Parent Child Hierarchies (value hierarchies) or Bridge tables (a technique for modeling Many-to-Many relationships) will require new SDE mappings for extraction to the DW staging area and new SIL loading routines from the staging area to the DW
Impact:
- Additional physical tables created in the staging and DW areas.
- Add new SDE source mapping to populate the target staging table
- Add new SIL target mapping to populate the DW target table
- Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
- Optionally applying security filtering.
- Adding new SDE and SIL to the DAC dependency
TYPE D- Localization of Oracle Business Intelligence (Repository Metadata, Oracle BI Data or Report and Dashboard)
Localization of Oracle Business Intelligence requires three configurations i.e. localization of Oracle BI Repository Metadata, localization of Oracle BI Data and localization of Reports and Dashboards. These configurations require configuration file changes, creating metadata objects to support it which includes initialization block, externalize metadata objects etc , DW tables and routine to populate the language dependent data.
Impact:
- Additional translation tables/ Modification to existing columns in staging and DW areas.
- Add/Modify SDE source mapping to populate the target staging table.
- Add/Modify SIL target mapping to populate the DW target table.
- Session Variables and Configuration Settings and creation of supporting metadata objects
- Import the data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
- Adding new SDE and SIL to the DAC dependency if required
TYPE E- Fact Granularity Change
Add new Dimensions to an existing Fact requires changes to existing SDE and SIL mappings for the Fact table. In addition, SDE and SIL mappings are required to implement new dimensions.
Impact:
- Additional physical dimension table created in the staging & DW areas.
- Additional key fields to staging and DW fact tables.
- Add new SDE source mapping to populate the new target staging table.
- Add new SIL target mapping to populate the DW target table.
- Changing existing SDE source mapping to add the additional keys staging fact table.
- Changing existing SIL target mapping to add the additional key column(s) to the DW target fact table. Also, include the key lookup to the new dimension.
- Create SDE & SIL workflows.
- Create DAC tasks with required dependencies.
- Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
- Changes required to the OBI-EE repository to model new Physical model and modeled through to the Business and Presentation Layers.
- Optionally applying security filtering.
TYPE F- New Star Schema
New facts and some new dimensions integrated with existing dimensions.
Impact:
- Additional physical dimension and fact table(s) created in the staging & DW areas.
- For each new physical dimension table and fact table a new SDE source mapping to populate the new target staging tables.
- For each new physical dimension table and fact table a new SIL target mapping to populate the DW target table.
- Create SDE & SIL workflows
- Create DAC tasks with required dependencies.
- Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
- Optionally applying security filtering.
TYPE G- Additional Source Systems or Using Universal Adaptors
Adding a second home-grown or non-supported data source. OOTB BI-Apps has one supported data source. However, it is possible that you want to integrate other data sources into the Business Analytic Warehouse (BAW). Additional sources can include simple spreadsheets to interlacing data into existing data models.
Interlacing is a method of loading data into the existing data model without matching & merging into the supported data source. For example, there might be Purchase Orders from a different ERP system that you want to provide integrated reporting with eBS. In this case you will need to integrate dimensional and fact data.
Another example is to use the universal adaptors to push the data into the BI Apps. The universal adaptors implementation lists following impacts.
Impact:
- Additional columns created for additional attributes in dimension and fact table(s) created in the staging & DW areas.
- Modify the universal adaptor SDE mapping & SIL mappings to include additional attributes if any
- Create SDE workflow
- Create DAC tasks with required dependencies.
- Import the new columns data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
In the next blog of this series I would like to cover the naming conventions and development guidelines that should be followed when developing new attributes, tables, dimensions and facts in the Business Analytics Warehouse (BAW) and new/changed repository objects in the OBIEE repository.
I would like to cover the naming conventions and development guidelines that should be followed when developing new attributes, tables, dimensions and facts in the Business Analytics Warehouse (BAW) and new/changed repository objects in the OBIEE repository.
In this blog my focus would be more on database objects modification standard practices and guidelines such as tables, columns and indexes.
Why Best Practices? – The standard and guidelines for database schema, tables, columns and indexes is for better manageability and simplify the future upgrades. In long term it would help to reduce the application day to day cost and successive upgrades.
Database Schema and Objects
The Business Analytics Warehouse (BAW) is the pre-built Data Warehouse as part of BI-Apps. The database schema will be decided by the installation team. A default is “BAW”. It contains both the staging tables and DW target tables. All customized tables, columns, indexes and other database objects MUST be added to BAW schema. (Best Practice DB 1)
Why to add in BAW Schema? – For better manageability and avoid any cross schema joins at OBIEE or Informatica Level as I understand cross schema joins within repository and Informatica would more complicated than keeping it simple. The mantra is keep it simple.
The database objects changes which is been done within the database schema and objects should use best practices for creating and managing versions and comments option of underline databases. The database objects customization should use versions and comments features of database for better traceability. (Best Practice DB 2)
Why Versions and Comments? Putting comments on tables and columns based on phase of projects, user created and date would be useful and provide enough traceability of customization done.
In addition make sure that what so ever customization done to database objects should be imported into the DAC container. The database objects customization should be imported into the DAC container. (Best Practice DB 3)
Why Import? DAC should be your central repository which would be used to migrate the database objects customization from development environment to test and then production. We should avoid using word/text file as a storage option for database objects.
Tables
Naming of Tables
The naming conventions used for tables in the Oracle BI Data Warehouse are as follows. The naming conventions for tables defined in BI Data Warehouse should be followed if custom tables are created in the warehouse. (Best Practice DB 4)
Why Standard Naming Conentions? It would help to identify meaning of each of the table and its significance and usability in overall solution.
The names of the Oracle Data Warehouse tables consist of three parts:
Prefix
Name
Suffix
The three components are capitalized and separated by an underscore (_), e.g. W_GL_OTHER_F. Each component has a meaning, as described in the table below:
Component
|
Value
|
Meaning
|
Prefix
|
W_
|
Out-of-the-box warehouse table
|
Prefix
|
WC_
|
Custom warehouse table
|
Suffix
|
_F
|
Fact table (base)
|
Suffix
|
_FS
|
Staging Fact table (base)
|
Suffix
|
_A
|
Aggregate fact table (base)
|
Suffix
|
_D
|
Dimension table (base)
|
Suffix
|
_DS
|
Staging Dimension table (base)
|
Suffix
|
_DH
|
Dimension hierarchy table (base)
|
Suffix
|
_SCD
|
Slowly changing dimension table (base)
|
Suffix
|
_MD
|
Mini-dimension table
|
Suffix
|
_H
|
Helper table
|
Suffix
|
_HS
|
Helper Staging Table
|
Suffix
|
_TMP
|
Pre Staging Temporary table
|
Suffix
|
_G, _GS, _S
|
Internal Table
|
Prefix
|
_UD
|
Unbounded dimension
|
Prefix
|
_DHLS
|
Staging for Dimension Helper
|
Prefix
|
_DHL
|
Dimension Helper Table
|
Prefix
|
_M
|
Map Dimension
|
The types of tables used by the Oracle Business Analytics Warehouse are as follows. The developer should use following types of tables; I believe the list of the tables used has covered almost all types of tables possible for DW solution. Any new addition would be a treated as a new entrant in the family and should be welcomed. (Best Practice DB 5)
Table Type
|
Description
|
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 twojoining dimension tables
|
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 mappingsfor data transformation and controlling ETL runs
|
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(_UD)
|
Tables containing information that is not bounded in transactional database data but should be treated as bounded data in the Oracle Business Analytics Warehouse
|
Columns
Naming of Columns
This section describes the naming conventions used for columns in the Oracle BI Data Warehouse. The naming conventions for columns defined in BI Data Warehouse should be followed if custom columns are created in the warehouse. (Best Practice DB 6)
Why Naming Convention for Columns? It would help to identify meaning of each of the column and its significance and usability in overall table usage.
The columns of the Oracle Data Warehouse tables consist of three parts:
Prefix
Name
Suffix
Component
|
Value
|
Meaning
|
Prefix
|
X_
|
Custom warehouse column
|
Suffix
|
_AMT
|
This column contains a currency amount
|
Suffix
|
_CD
|
The column value is based on the contents of the List of Values (LOV)
|
Suffix
|
_DT
|
This column contains a date or date time value.
|
Suffix
|
_FLG
|
This column contains a Boolean value where Y indicates Yes or True; N indicates No or False
|
Suffix
|
_I
|
Language Independent Code
|
Suffix
|
_ID
|
Columns are used in staging tables, they corresponds to the source identifier
|
Suffix
|
_WID
|
Identifier generated by Oracle BI linking dimensionand fact tables, except for ROW_WID
|
Suffix
|
_NAME
|
Name corresponding to the code column (columns ending with _CODE)
|
Suffix
|
_DESC
|
Long Description corresponding to the code column (columns ending with _CODE)
|
Suffix
|
_NUM
|
This column contains a number or an identifying alphanumeric value
|
All new tables, whether Fact or Dimension MUST contain the following important fields :( Best Practice DB 7)
Why System Fields? It would provide metadata information about the data and DML operations. Metadata of data such as source identifier, source type, created by, creation date etc is useful in many ways such as setting up changed data capture, setting up DML operations such as insert, update and up-sert.
Column Name
|
Column Type
|
Required in Area
|
Description
|
INTEGRATION_ID
|
VARCHAR2(80 CHAR)
|
Staging / DW
|
Stores the primary key or unique identifier of a record in the source table.
|
DATASOURCE_NUM
|
NUMBER(10)
|
Staging / DW
|
Stores the data source from which the data is extracted – this is setup in DAC. E-Business Suite = 4. New external sources should have their own unique number.
|
ETL_PROC_WID
|
NUMBER(10)
|
DW
|
Stores the ID of the ETL process information; details of ETL process are stored in the W_ETL_RUN in the warehouse
|
ROW_WID
|
NUMBER
|
DW
|
Is the sequence number generated during the ETL process; unique identifier (sequence number) for the tables.
|
CREATED_ON_DT
|
DATE
|
Staging / DW
|
Stores the creation date of a record in the primary source table
|
CHANGED_ON_DT
AUX1_CHANGED_ON_DT
AUX2_CHANGED_ON_DT
AUX3_CHANGED_ON_DT
AUX4_CHANGED_ON_DT
|
DATE
|
Staging / DW
|
Stores the last update date of a record in the primary source table
Additional column prefixed by AUX# can be added to store the last update date related to auxiliary source tables
|
CREATED_BY_ID
|
VARCHAR2(80)
|
Staging
|
Stores the user id who created a record in the primary source table
|
CREATED_BY_WID
|
NUMBER(10,0)
|
DW
|
Surrogate key of the user id who created a record in the primary source table
|
CHANGED_BY_ID
|
VARCHAR2(80)
|
Staging
|
Stores the user id who last updated a record in the primary source table
|
CHANGED_BY_WID
|
NUMBER(10,0)
|
DW
|
Surrogate key of the user id who last updated a record in the primary source table
|
TENANT_ID
|
VARCHAR2(80)
|
Staging / DW
|
This permits to distinguish distinct source instances having a common data source num identifier
Default value is ‘DEFAULT’
|
Miscellaneous Technical columns for new tables
All new tables, MAY contain the following important fields:( Best Practice DB 8)
Why Flg Columns ? It would provide metadata information about the data and DML operations. Metadata of data such as source identifier, source type, created by, creation date etc is useful in many ways such as setting up changed data capture, setting up DML operations such as insert, update and upsert.
Column Name
|
Column type
|
Required in Area
|
Description
|
CURRENT_FLG
|
CHAR(1)
|
DW
|
For Slowly changing dimension purpose
|
DELETE_FLG
|
CHAR(1)
|
Staging / DW
|
For Soft/Hard delete purposes
|
W_INSERT_DT
|
DATE
|
DW
|
optional insertion date for ETL audit purpose
|
W_UPDATE_DT
|
DATE
|
DW
|
optional update date for ETL audit purpose
|
SRC_EFF_FROM_DATE
|
DATE
|
Staging / DW
|
For Type 2,3 or 6 slowly changing dimension purpose
|
SRC_EFF_TO_DATE
|
DATE
|
Staging / DW
|
For Type 2,3 or 6 slowly changing dimension purpose
|
EFFECTIVE_FROM_DATE
|
DATE
|
Staging / DW
|
For Type 2,3 or 6 slowly changing dimension purpose
|
EFFECTIVE_TO_DATE
|
DATE
|
Staging / DW
|
For Type 2,3 or 6 slowly changing dimension purpose
|
Miscellaneous Technical columns for new tables
The currency codes for related system columns are as follows:( Best Practice DB 9)
Why Currency Codes? To support reporting in multiple currencies is one of the prime features of BI Apps implementations.
System Column
|
Description
|
DOC_CURR_CODE
|
Code for the currency in which the document was created in the source system.
|
LOC_CURR_CODE
|
Usually the reporting currency code for the financial company in which the document was created.
|
GRP_CURR_CODE
|
The primary group reporting currency code for the group of companies or organizations in which the document was created.
|
LOC_EXCHANGE_RATE
|
Currency conversion rate from the document currency code to the
local currency code.
|
GLOBAL1_EXCHANGE_RATE
|
Currency conversion rate from the document currency code to the primary group currency code.
|
GLOBAL2_EXCHANGE_RATE
|
Currency conversion rate from the document currency code to the GLOBAL1 currency code.
|
GLOBAL3_EXCHANGE_RATE
|
Currency conversion rate from document currency code to the GLOBAL2 currency code.
|
Indexes
Naming of Indexes
The naming conventions used for Indexes in the Oracle BI Data Warehouse are as follows. The naming conventions for indexes defined in BI Data Warehouse should be followed if custom indexes are created in the warehouse. (Best Practice DB 10)
Why Naming Conventions for Indexes ? It would help to identify custom indexes and its significance and usability in overall solution.
The index name of the Oracle Data Warehouse tables consists of two parts
Prefix
Name
Suffix
The three components are capitalized and separated by an underscore (_), e.g. W_GL_BALANCE_F_U1, each component has a meaning as described in the table below.
Component
|
Value
|
Description
|
Prefix
|
W_
|
Standard Data Warehouse Table
|
Prefix
|
WC_
|
Custom Table
|
Name
|
XXX
|
Table Name (e.g. W_GL_BALANCE_F)
|
Suffix
|
_U1
|
Unique Index ( e.g. composite index on INTEGRATION_ID and DATASOURCE_NUM_ID
|
Suffix
|
_F1
|
Non Unique Dimensional Reference Key Index ( e.g. bitmap index such as BALANCE_DT_WID )
|
Suffix
|
_M1
|
Non Unique Index ( e.g. bitmap or normal index or composite normal index )
|
Best Practices for Indexes
The best practices for the indexes are as below.
· The custom indexes in Source System ( e.g. Oracle EBS) for incremental Loads performance can be categorized into following groups and action need to taken based on facts
o Tables that do not have indexes on LAST_UPDATE_DATE in the latest EBS releases, but there are no performance implications reported with indexes on LAST_UPDATE_DATE column.
o Tables that have indexes on LAST_UPDATE_DATE columns, introduced in Oracle EBS Release 12.
o Tables that cannot have indexes on LAST_UPDATE_DATE because of serious performance degradations in the source EBS environments.
The indexes on source system should have prefix OBIEE_ and DO NOT follow to the standard source system index naming conventions (e.g. OBIEE_OE_ORDER_HEADERS_ALL)(Best Practice DB 11)
Why? This is to make sure that these custom indexes are managed separately from the rest of the standard indexes.
· Staging tables typically do not require indices, not a strict rule
Don’t hesitate to include indexes on staging table if necessary(Best Practice DB12).
Why? Let common sense prevails, I hate strict rules; if you prove the index would help you in data load performance don’t worry go ahead and just do it.
· Create indices on all columns that the ETL uses for dimensions and facts. For example:
o ROW_WIDs of dimensions and facts
o INTEGRATION_ID
o DATASOURCE_NUM_ID
o Flags
o Dimensional Reference Keys of fact tables
Create indices on all system columns such as ROW_WID, INTEGRATION_ID etc used within ETL processes.This is absolutely MUST for ETL performance. (Best Practice DB13).
Why? ETL Performance, without these indices I can tell you that your ETL won’t run efficiently.
· Create Bitmap Indexes for Dimensional reference keys and flags, treat this a MUST requirement. (Best Practice DB14).
Why? With Bitmap Indexes for Dimensional reference keys and flags allows us to use STAR TRANSFORMATION.I believe it is absolutely essential to achieve best query performances.
Make sure that the database parameter for STAR TRANSFORMATION & Repository Database Parameters setting has been in place.
· Carefully consider on which columns to put filter conditions (Best Practice DB15).
Why? Setting up index on column which we used to put condition in report would improve performance for obvious reason.
· Define indices to improve query performance(Best Practice DB16)
o Inspect standard repository objects for guidance
Why? The index creation processes should be driven by business requirements rather than physical structures and conventional wisdom. I believe the reporting system is a mainly a business support system and should be driven by business requirements.
· Register new indices in the DAC, treat this as a MUST requirement ( Best Practice DB17)
Options within DAC are
o Local or Partitioned Index
o Query Index or ETL Index
o The DAC server drops and re-creates indices during a full load and /or incremental load
Why? As I said earlier DAC should be your central repository for all database objects which includes indexes. This is helpful during migration of code from development instance to test and finally to production. DAC would be useful to maintain drop and re-recreate indices during full load and/or incremental load.
· Review/Revisit Index Strategy on regular basis ( Best Practice DB18)
Why? Review and Revisit the Indices should be done on regular basis, particularly with every new release which might end up with different usage pattern and query performances.
· The best practices for handling BI Applications indexes in Exadata Warehouse (Best Practice DB19):
Why? Exadata, Exalytics are specially designed engineering system and requires different approach. Based on my experience in this area the success mantra is don’t rush to any conclusion, make sure that proper comprehensive benchmarking is done before dropping or disabling any indices.
o Turn on Index usage monitoring to identify any unused indexes and drop / disable them in your environment.
o Consider pinning the critical target tables in smart flash cache
o Consider building custom aggregates to pre-aggregate more data and simplify queries performance.
o Drop selected query indexes and disable them in DAC to use Exadata Storage Indexes / Full Table Scans only after running comprehensive benchmarks and ensuring no impact on any other queries performance.
I hope you find the second part of this series informative and useful. My plan is to write next couple of blogs on guideline on Informatica, DAC and Repository customisation.
This post is written in a very good way and entails a lot of useful information for me. I am glad to find a good way of writing the post. If you want more detail then i have a website deltechsolutionsinc.net you also try that. May be it help you.
ReplyDeleteI really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle BI Applications for eBusiness OBIA
ReplyDelete, kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Oracle BI Applications for eBusiness OBIA. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us.
Nitesh Kumar
MaxMunus
E-mail: nitesh@maxmunus.com
Skype id: nitesh_maxmunus
Ph:(+91) 8553912023
http://www.maxmunus.com/