Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

Accelerating Data Warehouses | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades


Wipro Technologies
Tata Consultancy Services
Cognizant Technologies

Home » Articles » Accelerating Data Warehouses

Accelerating Data Warehouses

Article Posted On Date : Friday, May 22, 2009

Accelerating Data Warehouses

HTML clipboardAccelerating Data Warehouses

Oracle OLAP 11g brings high-performance data warehouse features to Oracle Database 11g.

An option of Oracle Database 11g Enterprise Edition, Oracle OLAP 11g is a full-featured online analytical processing engine. Because Oracle OLAP 11g is embedded in Oracle Database, it benefits from database scalability, security, and manageability features.

Key new features in Oracle OLAP 11g include database-managed relational views of a cube, a cube scan row source that is used by the SQL optimizer, and cube-organized materialized views. This article focuses on enhancements in Oracle OLAP 11g, how OLAP cubes fit into the Oracle data warehouse, and how they are used within SQL-based business intelligence applications to provide enriched content with high performance.


Users' queries are often unpredictable. On different days, the same users will perform trend analysis, drill down on specific product lines, and compare a week's sales against those of the same week last year. With standard relational systems, it is difficult to optimize data structures that provide consistently good query performance for such an unpredictable query pattern.

To address this need, DBAs and designers frequently create a system of summary tables or materialized views. OLAP cubes, which provide consistently fast query performance across an entire data model, often provide a better alternative to summary management. Sophisticated calculations can be easily embedded within the cube to enhance the analytic content of applications.

These calculations often rely on data from many rows and interrow calculations. For example, an OLAP cube might include a calculation that compares the current year's sales for each region and product line with those from the same period last year and two years ago. The cube structure is optimized to handle this kind of querying and analysis.

Why Oracle OLAP?

Oracle OLAP uses an analytic workspace in the database to perform OLAP analysis. Oracle OLAP stores data in the database as multidimensional cubes, which are designed for fast incremental update and query. Cubes are organized by dimensions, which act as keys to the fact data and define the basic structure of the cube. In many ways, a cube is similar to a star schema. The cube plays the role of the fact table, and an OLAP dimension plays the role of a dimension table. Dimensions can be simple lists of members, or they can be organized into levels and hierarchies. Hierarchical dimensions enable data to be aggregated from lower levels to higher levels of summarization. They support navigation such as drill-down and certain types of calculations such as Share to Parent, Share within Ancestor, and Rank within Parent. They also support many time-series calculations such as Year to Date. These types of calculations are easy to define within the analytic workspace manager (the administrative tool of Oracle OLAP 11g) and are efficiently computed within the cube at runtime.

Oracle OLAP 11g can significantly shorten query processing times for users of SQL-based business intelligence (BI) tools such as Oracle Business Intelligence Suite Enterprise Edition and other third-party tools. Oracle OLAP 11g also makes it easy to embed computations in an OLAP cube. Oracle OLAP 11g capabilities are all provided within Oracle Database, which allows for centralized control of data, business rules, and security.

Creating Cube-Organized Materialized Views for Summary Management

Cube-organized materialized views are a new capability in Oracle OLAP 11g that enables cubes to be used as a summary management solution, often replacing relational summary tables and table-based materialized views. In this scenario, an application queries the fact table by using aggregation functions (such as SUM) and a GROUP BY clause, and the query rewrite feature of the database automatically redirects the query to the cube-organized materialized view. The cube manages summary data transparently to the querying application, and users benefit from improved query performance.

To create a cube-organized materialized view, DBAs build a cube from the fact and dimension tables that applications currently query. They then enable the cube to allow materialized view refresh and query rewrite. The materialized view can be created via an API call or, more commonly, via certain options in the analytic workspace manager, as shown in Figure 1. The cube is self-indexing and manages the entire aggregate space.


figure 1
Figure 1: Analytic workspace manager

Build the GLOBAL sample cube. To create and use the GLOBAL sample OLAP cube and walk through the Oracle OLAP 11g capabilities this article describes, install and set up the products, schema, and workspace as follows:

1. Install the Oracle Database 11g database server and client. Note that the server and client are separate downloads, both available on Oracle Technology Network at otn.oracle.com. From this page, select Downloads -> Database, and then click the See All link next to the name of your operating system in the Oracle Database 11g download section. When installing the database server, you can use default options. When installing the client, make sure you select the Administrator option to install the extended set of tools, which includes the analytic workspace manager.

2. Download the GLOBAL schema (global_11g_schema.zip) from otn.oracle.com/products/bi/olap. From this page, click the 11.1 link next to the Global Schema selection in the Downloads section of this page. Unzip the file contents to a directory.

3. Download otn.oracle.com/oramag/oracle/08-may/o38olap.zip, and unzip the contents, the global_oramag.xml file, to a directory.

4. Follow the directions specified in the global_11g_readme.html documentation file, which is provided in the GLOBAL schema zip file downloaded in step 2. These directions enable you to create the GLOBAL schema with the tables necessary to build an analytic workspace.

5. Launch the analytic workspace manager. From the Windows Start menu, you can find it at {Oracle11g Client Home} -> Integrated Management Tools -> Oracle OLAP Analytic Workspace Manager and Worksheet.

6. Create a connection to your instance, by right-clicking Databases and selecting Add Database to tree. . . . Enter a description for the connection and the appropriate connection string for your instance (such as localhost:1521:orcl11). Then click Create.

7. Expand the Database tree until your new connection appears, and then click + (plus sign) next to your connection to connect to the instance. Enter GLOBAL for the username, and enter the password you assigned in step 4.

8. Expand the Schemas tree until you see Analytic Workspaces. Right-click Analytic Workspaces, and select Create Analytic Workspace from Template. . . .

9. Navigate the file chooser to find the global_oramag.xml template file you downloaded in step 3. Choose this file, and click Create. Wait for a few minutes while it creates the analytic workspace.

10. Right-click the new GLOBAL analytic workspace, and select Maintain Analytic Workspace GLOBAL.

11. Click >> (Add All), and then click Finish. This step loads data into the cube and aggregates it according to the settings specified in the template file. It may take a few minutes to complete. Once you're done, click Close to close the build log.

Run the OLAP query. With the GLOBAL cube loaded and the data aggregated, now enter the following—

SELECT t.calendar_year_id time,            p.class_id product,            c.region_id region,            SUM(f.sales) sales  FROM time_dim t,          product_dim p,          customer_dim c,          units_fact f  WHERE t.month_id = f.month_id     AND p.item_id = f.item_id     AND c.ship_to_id = f.ship_to_id     AND c.region_id = 'AMER'  GROUP BY t.calendar_year_id,                p.class_id,                c.region_id  ORDER BY t.calendar_year_id,                p.class_id;  

—using Oracle SQL Developer (or another SQL tool of choice). This typical OLAP query summarizes the data in the UNITS_FACT fact table by year, class, and region for only those rows that have a REGION_ID value of AMER.

If there were no summary tables, this query would have to sift through the CUSTOMER_DIM (dimension) table to find all rows with REGION_ID='AMER' and then find rows in the fact table with those SHIP_TO_ID values. Finally, it would have to summarize the SALES column in the fact table, grouping the data by year, class, and region.

With Oracle OLAP 11g, using materialized views, these totals are immediately accessible. It simply fetches the rows of summarized data and returns them to the SQL processor. The result is much faster query response, with a simple summary management system that is easy to maintain.

If you run an explain plan against the query, you'll see that the Oracle optimizer uses the CUBE_SCAN operation instead of the much less efficient HASH JOIN operation, which is required when you don't have the materialized view. To see the difference, execute the commands alter materialized view cb$units_cube disable query rewrite and alter materialized view cb$units_cube enable query rewrite and view the resulting explain plans in each case. You will also see a huge difference in performance. In my tests on the small (300,000 lowest-level rows) GLOBAL fact table, I found that with the query rewrite feature enabled (using the cube), the query returned results two to five times as fast as without the cube. You will find larger differences in performance when using larger, more-realistically sized cubes.

The summary management system is also easy to maintain. It has one materialized view, rather than a separate materialized view for each combination of levels of each dimension. You can control the extent to which the data is precomputed, but the cube always presents itself as fully solved for every combination of the levels of the dimensions involved. A materialized view log keeps track of the rows that change in the base table, and it controls how much of the cube is incrementally updated and aggregated and when.

Sponsored Ads

Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions


Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions

Copyright © 2001-2022 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2022

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |

Copyright ©2001-2022 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.