Implementing ILM for Data Warehouse Database

Information Lifecycle Management (ILM) is the practice of applying policies for the effective management of information throughout its useful life. Implementing ILM become more beneficial in data warehouse environments, where we are required to keep historical data. Data Warehouse databases can grow exponentially and It makes complete sense to move data to different storage tier though its life cycle to save cost and availability.

Benefits of implementing ILM 

  1. Works at the database level with no application changes
  2. Reduces disk space requirements for all types of data
  3. Allows you to automate the movement of data to lower-cost storage based on activity patterns
  4. Lowers query times by keeping only active data on high performance storage
  5. Increases availability of critical information on the most reliable storage

How Implement ILM using Oracle database features  

  1. Use Oracle ADO to create policies for data compression and data movement between different compression level and different tiers of storage.
  2. Use Oracle 12c feature  Heat Map to track data usage
  3. Use Table Partitioning to move historical data to low performance storage tier
  4. Use Advance / HCC compression to compress historical data to same or low cost storage tier

Note : Oracle 12c introduce 2 new feature call Automatic Data Optimization (ADO)  and Heat Map, Both features are part of Oracle Advance Compression Option.

OBIEE Performance Tuning With Exadata

Database Level (Exadata)  

  1. Use Partition-Wise Join or Star transformation
  2. Use Partitioning
  3. Collect stats Regularly
  4. Make sure to use Smart Scan (Exadata )
  5. Avoid using view if there are concurrent users
  6. Set Parallel MAX and MIN parameter properly
  7. Set Optimizer Mode properly
  8. Use Histograms Carefully
  9. Use summary tables when needed
  10. Enable result cache
  11. Use Bitmap Indexes
  12. Cache tables ( Exadata )
  13. Don’t restart Database to often
  14. Enable Parallelism according to your needs
  15. Enable compression (Exadata )

 

 

OBIEE Level:  

  1. Remove Snowflakes
  2. Avoid Circular join by using Aliases in Physical Layer
  3. Don’t add unnecessary column to data model
  4. Implement Cache Management
  5. Turn off logging
  6. Add connection pools
  7. Set query limits
  8. Push heavy load to database layer
  9. Limit number of initialization blocks
  10. Use Database hints