Build Your Data Warehouse Using Oracle
July 20, 2016
Build Your Data Warehouse Using Oracle
Score 9 out of 10
Vetted Review
Verified User
Overall Satisfaction with Oracle Data Warehouse
My organization designs, builds and maintains a data warehouse for various clients both from public and private sectors. Based on the individual client needs the data warehouse is being used both at the department level and as well as at the entire organization level. The data warehouse is being used to answer and provide insights to the organizations' various activities. Using the data warehouse the organizations are able to derive advanced analytic analysis and in turn increase fraud detection, efficiency and much more.
- Able to handle very large data sizes efficiently from a performance, high availability and manageability perspective. This is accomplished through the Oracle Partitioning functionality. Partitioning allows large segments (tables, IOT index-organized tables, indexes) to be broken into smaller segments at the physical layer but treated as a whole at the logical layer.
- Provides support for dual-format architecture through Oracle In-Memory functionality. Without any change to application code one can obtain in-memory performance. This functionality enables us to have the tables represented in both the row format and the column format using in-memory format. This is a huge boost for BI/analytic queries since the Oracle optimizer is able to intelligently choose the appropriate format.
- Provision to materialize a subset of table data or table joins. This is through materialized views and the optimizer will rewrite the query against the base tables to make use of this materialized view. This provides a huge performance boost and is critical in VLDBs as in a data warehouse. The query rewrite is fully transparent to users.
- Provides multiple compression capabilities. This is very useful not only for deducing the storage foot print but as well as increase performance at different layers of the infrastructure including query performance. The compression functionality can be applied against both structured and unstructured data.
- With the advent of Engineered Systems (Exadata, Database Machine, SuperCluster) there are specific features and functionalities that can further boost the Oracle data warehouse. These are related to consolidation, Smart Scan, Storage Indexes, EHCC (Exadata hybrid columnar compression) and much more.
- RAC - Real Application Clusters (with 2 or more nodes) provides functionality for high availability, performance and scaling as the work load increases. The parallelism is provided both within a node and as well as across nodes. If for any reason a node goes down the data warehouse is still available through other nodes and the running queries are transparently failed over to the surviving nodes.
- For the query rewrite related to the materialized views the optimizer at times goes against the base tables. There is room for improvement for the optimizer to make more intelligent choices. There does exist functionality to identify the reason why the optimizer failed to do the rewrite of the original query. This can be further expanded.
- On the storage indexes currently there is limitation of only 8 columns. In addition Oracle decides which of the columns are chosen as part of the storage index. It would be nice to see if both of these are addressed in future versions/releases.
- Some of the features are 'Options' which would increase the overall licensing cost and is an important factor for certain class of users/clients. It would be nice to see if at least some of the options are standard functionality.
- Overall the business objective of all of our clients have been met positively with Oracle Data Warehouse. All of the required analysis the users were able to successfully carry out using the warehouse data.
- Using a 3-tier architecture with the Oracle Data Warehouse at the back end the mid-tier has been integrated well. This is big plus in providing the necessary tools for end users of the data warehouse to carry out their analysis.
- All of the various BI products (OBIEE, Cognos, etc.) are able to use and exploit the various analytic built-in functionalities of the Oracle Data Warehouse.