text:
�AEoa�, mimetypeapplication/epub+zipPK �AE iTunesMetadata.plist<��
A cube always returns summary data to a query as needed. While the cube may store data at the day level, for example, it will return a result at the quarter or year level without requiring a calculation in the query. This chapter explains how to optimize the unique aggregation subsystem of Oracle OLAP to provide the best performance for both data maintenance and querying.
This chapter contains the following topics:
Aggregation is the process of consolidating multiple values into a single value. For example, data can be collected on a daily basis and aggregated into a value for the week, the weekly data can be aggregated into a value for the month, and so on. Aggregation allows patterns in the data to emerge, and these patterns are the basis for analysis and decision making. When you define a data model with hierarchical dimensions, you are providing the framework in which aggregate data can be calculated.
Aggregation is frequently called summarization, and aggregate data is called summary data. While the most frequently used aggregation operator is Sum, there are many other operators, such as Average, First, Last, Minimum, and Maximum. Oracle OLAP also supports weighted and hierarchical methods. Following are some simple diagrams showing how the basic types of operators work. For descriptions of all the operators, refer to "Aggregation Operators" .
Figure 9-1 shows a simple hierarchy with four children and one parent value. Three of the children have values, while the fourth is empty. This empty cell has a null or
NA value. The Sum operator calculates a value of (2 + 4 + 6)=12 for the parent value.
The Average operator calculates the average of all real data, producing an aggregate value of ((2 + 4 + 6)/3)=4, as shown in Figure 9-2.
The hierarchical operators include null values in the count of cells. In Figure 9-3, the Hierarchical Average operator produces an aggregate value of ((2 + 4 + 6 +NA)/4)=3.
The weighted operators use the values in another measure to generate weighted values before performing the aggregation. Figure 9-4 shows how the simple sum of 12 in Figure 9-1 changes to 20 by using weights ((3*2) + (2*4) + (NA*6) +(4*NA)).
Analytic workspaces provide an extensive list of aggregation methods, including weighted, hierarchical, and weighted hierarchical methods.
The following are descriptions of the basic aggregation operators:
Average: Adds non-null data values, then divides the sum by the number of data values that were added together.
First Non-NA Data Value: Returns the first real data value.
Last Non-NA Data Value: Returns the last real data value.
Maximum: Returns the largest data value among the children of each parent.
Minimum: Returns the smallest non-null data value among the children of each parent.
Nonadditive: Does not aggregate the data.
Sum: Adds data values.
These operators require a measure providing the weight or scale values in the same cube. In a weight measure, an NA (null) is calculated like a
1. In a scale measure, an NA is calculated like a
The weighted operators use outer joins, as described in "When Does Aggregation Order Matter?".
These are the scaled and weighted aggregation operators:
Scaled Sum: Adds the value of a weight object to each data value, then adds the data values.
Weighted Average: Multiplies each data value by a weight factor, adds the data values, and then divides that result by the sum of the weight factors.
Weighted First: Multiplies the first non-null data value by its corresponding weight value.
Weighted Last: Multiplies the last non-null data value by its corresponding weight value.
Weighted Sum: Multiplies each data value by a weight factor, then adds the data values.
The following are descriptions of the hierarchical operators. They include all cells identified by the hierarchy in the calculations, whether or not the cells contain data.
Hierarchical Average and the Hierarchical Weighted operators use outer joins.
Hierarchical Average: Adds data values, then divides the sum by the number of the children in the dimension hierarchy. Unlike Average, which counts only non-null children, hierarchical average counts all of the children of a parent, regardless of whether each child does or does not have a value.
Hierarchical First Member: Returns the first data value in the hierarchy, even when that value is null.
Hierarchical Last Member: Returns the last data value in the hierarchy, even when that value is null.
Hierarchical Weighted Average: Multiplies non-null child data values by their corresponding weight values, then divides the result by the sum of the weight values. Unlike Weighted Average, Hierarchical Weighted Average includes weight values in the denominator sum even when the corresponding child values are null.
Hierarchical Weighted First: Multiplies the first data value in the hierarchy by its corresponding weight value, even when that value is null.
Hierarchical Weighted Last: Multiplies the last data value in the hierarchy by its corresponding weight value, even when that value is null.
The OLAP engine aggregates a cube across one dimension at a time. When the aggregation operators are the same for all dimensions, the order in which they are aggregated may or may not make a difference in the calculated aggregate values, depending on the operator.
You should specify the order of aggregation when a cube uses more than one aggregation method. The only exceptions are that you can combine Sum and Weighted Sum, or Average and Weighted Average, when the weight measure is only aggregated over the same dimension. For example, a weight measure used to calculate weighted averages across Customer is itself only aggregated across Customer.
The weight operators are uncompressible for the specified dimension and all preceding dimensions. For a compressed cube, you should list the weighted operators as early as possible to minimize the number of outer joins. For example, suppose that a cube uses Weighted Sum across Customer, and Sum across all other dimensions. Performance will be best if Customer is aggregated first.
The following information provides guidelines for when you need to specify the order of the dimensions as part of defining the aggregation rules for a cube.
When these operators are used for all dimension of a cube, the order does not affect the results:
Hierarchical First Member
Hierarchical Last Member
Hierarchical Average
Even when these operators are used for all dimensions of a cube, the order can affect the results:
First Non-NA Data Value
Last Non-NA Data Value
Weighted First
Weighted Last
Hierarchical Weighted First
Hierarchical Weighted Last
Scaled Sum
Even though you can use the Sum and Maximum operators alone without ordering the dimensions, you cannot use them together without specifying the order. The following figures show how they calculate different results depending on the order of aggregation. Figure 9-5 shows a cube with two dimensions. Sum is calculated first across one dimension of the cube, then Maximum is calculated down the other dimension.
Figure 9-6 shows the same cube, except Maximum is calculated first down one dimension of the cube, then Sum is calculated across the other dimension. The maximum value of the sums in Figure 9-5 is 15, while the sum of the maximum values in Figure 9-6 is 19.
This example describes changes to the default aggregation of the Units cube in the
GLOBAL analytic workspace. These changes will take effect in the next data refresh.
Analytic Workspace Manager initially sets all dimensions to use the Sum operator and aggregates all levels of all dimensions. To change these default settings, use the Rules subtab of the Aggregation tab.
Figure 9-7 shows the operators for the Units Cube. Time is now set to Last Non-NA Data Value, and it will be aggregated after the other dimensions. For operators like First and Last, the order in which the dimensions are aggregated can change the results.
Another change is that only the Shipments hierarchy of the Customer dimension will be aggregated during data maintenance. Because the Segment hierarchy is seldom queried, the Global DBA chose not to calculate these aggregate values in order to save maintenance time and storage space. However, response time will be slower for queries that request Segment aggregations.
Analytic Workspace Manager initially chooses cost-based aggregation with 20% precomputed values for the bottom partitions and 0% for the top partition. An unpartitioned cube is also set to 20%. This setting means that 20% of the aggregate values will be calculated and stored during data maintenance, and 80% will be calculated in response to a query. These settings optimize data maintenance.
Increasing the materialization of the bottom partitions improves querying of both the bottom and the top partitions. Increasing the materialization of the top partition improves querying of the most aggregate data and any other hierarchies of the partitioned dimension.
Figure 9-8 shows the settings for the Units Cube. In this case, the Global DBA chose to keep the top partition at 0%, and to increase the bottom partitions from 20 to 50%. This change will increase maintenance costs in time and storage space, but will improve runtime performance of all partitions.
This preface identifies the major enhancements to the OLAP option of Oracle Database.
The OLAP Option to Oracle Database 11g continues the development trends of Oracle9i and Oracle Database 10g, especially in deepening integration with the database and enhancing SQL access to cubes, security, and metadata. The power of OLAP is easily accessible to SQL applications. Oracle Database 11g also introduces the cube as a summary management solution for relational OLAP (ROLAP) implementations.
OLAP Metadata Integration
All metadata for cubes and dimensions is stored in the Oracle database and revealed in the data dictionary views, so that you can query the entire business model in SQL. Use of the data dictionary to store the metadata officially codifies the dimensional model in the database, provides significant improvements for metadata queries, and supports other new features such as SQL object security for cubes and dimensions.
Automatic Maintenance of Cube and Dimension Views
Oracle Database 11g automatically creates and maintains relational views for every cube, dimension, and hierarchy in the database. If you modify a dimensional object, such as adding a calculated measure to a cube, the view is immediately re-created to reflect the change. Oracle Database defines these views using the new
CUBE_TABLE function, which enables the SQL Optimizer enhancements.
Cube Scripts
A cube script is an ordered list of commands that prepare a cube for querying, such as Clear Data, Load Data, Aggregate, Execute PL/SQL, and Execute OLAP DML. For many applications, cube scripts will eliminate the need to use procedural programs for processing cubes.
Cost-Based Aggregation
Fast updates and uniform querying performance are two hallmarks of the OLAP option. Cost-based aggregation enhances performance in both areas by executing a fine-grained pre-aggregation strategy and storing sparse data sets very efficiently.
Calculation Expression Syntax
OLAP calculation expressions extend the syntax of the SQL analytic functions. This syntax is already familiar to SQL developers and DBAs, so that it is easier for them to adopt than proprietary OLAP languages and APIs.
This syntax is used to define calculations that are embedded in the cube, such as dynamically calculated facts or measures.
Cube Materialized Views
Cube materialized views are cubes that have been enhanced to use the automatic refresh and query rewrite features of Oracle Database.
Cube materialized views bring the fast update and fast query capabilities of the OLAP option to applications that query detail relational tables. Summary data is generated and stored in a cube, and query rewrite automatically redirects queries to the cube materialized views. Applications experience excellent query performance.
Object and Data Security
Oracle Database 11g introduces both object security and data security to OLAP cubes and dimensions. Both types of security are granted to database users and roles.
Object security controls access to analytic workspaces, cubes, and dimensions using standard SQL
GRANT and
REVOKE syntax.
Data security controls access to the data in a cube or a dimension. You can grant
UPDATE, and
DELETE privileges to dimension members (keys) either globally or in the context of a particular cube to control access to the data in a cube.
See Also:Chapter 8, "Security"
Describes a measure or fact that can be summarized through addition, such as a
SUM function. An additive measure is the most common type. Examples include sales, cost, and profit.
Contrast with nonadditive.
The process of consolidating data values into a single value. For example, sales data could be collected on a daily basis and then be aggregated to the week level, the week data could be aggregated to the month level, and so on. The data can then be referred to as aggregate data.
The term aggregation is often used interchangeably with summarization, and aggregate data is used interchangeably with summary data. However, there are a wide range of aggregation methods available in addition to
The process of distributing aggregate data down a hierarchy to the detail level, sometimes using an existing set of data as the basis for the allocation. Allocation is often used in forecasting and budgeting systems. An example of a financial allocation is the automated distribution of a bonus pool, based on the current salaries and performance ratings of the employees.
A container for storing related dimensional objects, such as dimensions and cubes. An analytic workspace is stored in a relational table.
See also cube, cube dimension.
A dimension member at a higher level of aggregation than a particular member. For example, in a Time dimension, the year 2007 is the ancestor of the day 06-July-07. The member immediately above is the parent. In a dimension hierarchy, the data value of the ancestor is the aggregated value of the data values of its descendants.
Contrast with descendant. See also hierarchy, level, parent.
A database object related to an OLAP cube dimension. An attribute stores descriptive characteristics for all dimension members, or members of a particular hierarchy, or only members at a particular level of a hierarchy.
When the values of an attribute are unique, they provide supplementary information that can be used for display (such as a descriptive name) or in analysis (such as the number of days in a time period). When the values of an attribute apply to a group of dimension members, they enable users to select data based on like characteristics. For example, in a database representing footwear, you might use a color attribute to select all boots, sneakers, and slippers of the same color.
See also cube dimension.
See detail data.
See measure.
A stored expression that executes in response to a query. For example, a calculated measure might generate the difference in costs from the prior period by using the
LAG_VARIANCE function on the
COSTS measure. Another calculated measure might calculate profits by subtracting the
COSTS measure from the
SALES measure. The expression resolves only the values requested by the query.
See also expression, measure.
A single data value of an expression. In a dimensioned expression, a cell is identified by one value from each of the dimensions of the expression. For example, if you have a measure with the dimensions
MONTH and
CUSTOMER, then each combination of a month and a customer identifies a separate cell of that measure.
See also cube dimension.
A dimension member that is part of a more aggregate member in a hierarchy. For example, in a Time dimension, the month Jan-06 might be the child of the quarter Q1-2006. A dimension member can be the child of a different parent in each hierarchy.
Contrast with parent. See also descendant, hierarchy.
A compact format for storing sparse multidimensional data. Oracle OLAP provides two types of composites: a compressed composite for extremely sparse data, and a regular composite for moderately sparse data.
A cube with very sparse data that is stored in a compressed composite.
See also composite.
See compressed cube.
consistent solve specification
See solve specification.
An organization of measures with identical dimensions and other shared characteristics. The edges of the cube contain the dimension members, and the body of the cube contains the data values. For example, sales data can be organized into a cube whose edges contain values from the Time, Product, and Customer dimensions and whose body contains Volume Sales and Dollar Sales data.
A cube dimension is a dimensional object that stores a list of values. It is an index for identifying the values of a measure. For example, if Sales data has a separate sales figure for each month, then the data has a Time dimension that contains month values, which organize the data by month.
In the context of multidimensional analysis, a cube dimension is called a dimension.
See also dimension.
A cube that has been enhanced with materialized view capabilities. A cube materialized view can be incrementally refreshed through the Oracle Database materialized view subsystem, and it can serve as a target for transparent rewrite of queries against the source tables.
Also called a cube-organized materialized view or a cube-based materialized view.
A sequence of steps that prepare the data for querying, such as loading and aggregating new data.
A relational view of the data stored in a cube, which can be queried by SQL. It contains columns for the dimensions, measures, and calculated measures of the cube.
See calculated measure.
A dimension member whose data is calculated from the values of other members of the same dimension using the rules defined in a model.
See model.
A group of users and database roles that is defined just for use in managing OLAP security policies.
A relational table, view, synonym, or other database object that provides detail data for cubes and cube dimensions.
A database designed for query and analysis rather than transaction processing. A data warehouse usually contains historical data that is derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources.
Permit redundancy in a table. Contrast with normalize.
See calculated measure.
A dimension member at a lower level of aggregation than a particular member. For example, in a Time dimension, the day 06-July-07 is the descendant of year 2007. The member immediately below is the child. In a dimension hierarchy, the data values of the descendants roll up into the data values of the ancestors.
Contrast with ancestor. See also aggregation, child, hierarchy, level.
Data at the lowest level, which is acquired from another source.
Contrast with aggregation.
A structure that categorizes data. Among the most common dimensions for sales-oriented data are Time, Geography, and Product. Most dimensions have hierarchies and levels.
In a cube, a dimension is a list of values at all levels of aggregation.
In a relational table, a dimension is a type of object that defines hierarchical (parent/child) relationships between pairs of column sets.
See also cube dimension, hierarchy.
See dimension member.
One element in the list that composes a cube dimension. For example, a Time dimension might have dimension members for days, months, quarters, and years.
A relational table that stores all or part of the values for a dimension in a star or snowflake schema. Dimension tables typically contain columns for the dimension keys, levels, and attributes.
See dimension member.
A relational view of a cube dimension that provides information about all members of all hierarchies. It includes columns for the dimension keys, level, and attributes.
See also cube dimension, hierarchy view.
To navigate from one item to a set of related items. Drilling typically involves navigating up and down through the levels in a hierarchy.
Drilling down expands the view to include child values that are associated with parent values in the hierarchy.
Drilling up collapses the list of descendant values that are associated with a parent value in the hierarchy.
A specially formatted file for transferring data between analytic workspaces, or for storing versions of an analytic workspace (all of it or selected objects) outside the database.
A list of dimension members at all levels of a hierarchy, such that the aggregate members (totals and subtotals) are interspersed with the detail members. For example, a Time dimension might contain dimension members for days, months, quarters, and years.
A combination of one or more values (typically provided by a measure or a calculated measure), operators, and functions that evaluates to a value. An expression generally assumes the data type of its components.
The following are examples of expressions, where
SALES is a measure:
See measure.
A table in a star schema that contains factual data. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.
A fact table might contain either detail facts or aggregated facts. Fact tables that contain aggregated facts are typically called summary tables or materialized views. A fact table usually contains facts with the same level of aggregation.
See also materialized view.
A way to organize data at different levels of aggregation. Hierarchies are used to define data aggregation; for example, in a Time dimension, a hierarchy might be used to aggregate data from days to months to quarters to years. Hierarchies are also used to define a navigational drill path.
In a relational table, hierarchies can be defined as part of a dimension object.
See also level-based hierarchy, ragged hierarchy, skip-level hierarchy, value-based hierarchy.
A relational view of a cube dimension that provides information about the members that belong to a particular hierarchy. It includes columns for the dimension keys, parents, levels of the hierarchy, and attributes.
See also cube dimension, dimension view.
A column or set of columns included in the definition of certain types of integrity constraints. Keys describe the relationships between the different tables and columns of a relational database.
See also dimension member.
See detail data.
A named position in a hierarchy. For example, a Time dimension might have a hierarchy that represents data at the month, quarter, and year levels. The levels might be named Month, Quarter, and Year. The names provide an easy way to reference a group of dimension members at the same distance from the base.
A hierarchy composed of levels. For example, Time is always level based with levels such as Month, Quarter, and Year. Most hierarchies are level based.
See also value-based hierarchy.
The definition of the relationship and data flow between source and target objects. For example, the metadata for a cube includes the mappings between each measure and the columns of a fact table or view.
A database object that provides access to aggregate data and can be recognized by the automatic refresh and the query rewrite subsystems.
See also cube materialized view.
Data that represents a business measure, such as sales or cost data. You can select, display, and analyze the data in a measure. The terms measure and fact are synonymous; measure is more commonly used in a multidimensional environment and fact is more commonly used in a relational environment.
Measures are dimensional objects that store data, such as Volume Sales and Dollar Sales. Measures belong to a cube.
See also calculated measure, fact, cube.
A database object that organizes and label groups of measures. Users may have access to several schemas with measures named Sales or Costs, and measure folders provide a way to differentiate among them.
A set of inter-related equations specified using the members of a particular dimension. Line item dimensions often use models to calculate the values of dimension members.
See also custom member. Contrast with calculated measure.
A special data value that indicates that data is "not available" (
NA) or null. It is the value of any cell to which a specific data value has not been assigned or for which data cannot be calculated.
Describes a measure or fact that cannot be summarized through addition, such as Unit Price. Maximum is an example of a nonadditive aggregation method.
Contrast with additive.
In a relational database, the process of removing redundancy in data by separating the data into multiple tables. Contrast with denormalized.
Online Analytical Processing. OLAP functionality is characterized by dynamic, dimensional analysis of historical data, which supports activities such as the following:
Calculating across dimensions and through hierarchies
Analyzing trends
Drilling up and down through hierarchies
Rotating to change the dimensional orientation
Contrast with OLTP.
Online Transaction Processing. OLTP systems are optimized for fast and reliable transaction handling. Compared to data analysis systems, most OLTP interactions involve a relatively small number of rows, but a larger group of tables.
Contrast with OLAP.
Calculated at run-time in response to a specific query. In a cube, calculated measures and custom members are typically calculated on the fly. Aggregate data can be precomputed, calculated on the fly, or a combination of the two methods.
Contrast with precompute.
See solve specification.
A dimension member immediately above a particular member in a hierarchy. In a dimension hierarchy, the data value of the parent is the aggregated total of the data values of its children.
A one-to-many relationship between one parent and one or more children in a hierarchical dimension. For example, New York (at the state level) might be the parent of Albany, Buffalo, Poughkeepsie, and Rochester (at the city level).
See precompute.
Calculate and store as a data maintenance procedure. In a cube, aggregate data can be precomputed, calculated on the fly, or a combination of the two methods.
Contrast with on the fly.
A hierarchy that contains at least one member with a different base level, creating a "ragged" base level for the hierarchy. Organization dimensions are frequently ragged.
See data security role.
A hierarchy that contains at least one member whose parents are more than one level above it, creating a hole in the hierarchy. For example, in a Geography dimension with levels for City, State, and Country, Washington D.C. is a city that does not have a State value; its parent is United States at the Country level.
A type of star schema in which the dimension tables are partly or fully normalized.
See also normalize, star schema.
A result set in which all derived data has been calculated. Data fetched from an cube is always fully solved, because all of the data in the result set is calculated before it is returned to the SQL-based application. The result set from the cube is the same whether the data was precomputed or calculated on the fly.
See also on the fly, precompute.
See data source.
A concept that refers to multidimensional data in which a relatively high percentage of the combinations of dimension values do not contain actual data.
There are two types of sparsity:
Controlled sparsity occurs when a range of values of one or more dimensions has no data; for example, a new measure dimensioned by Month for which you do not have data for past months. The cells exist because you have past months in the Month dimension, but the cells are empty.
Random sparsity occurs when nulls are scattered throughout a measure, usually because some combinations of dimension members never have any data. For example, a district might only sell certain products and never have sales data for the other products.
Some dimensions may be sparse while others are dense. For example, every time period may have at least one data value across the other dimensions, making Time a dense dimension. However, some products may not be sold in some cities, and may not be available anywhere for some time periods; both Product and Geography may be sparse dimensions.
See also composite.
A join between a fact table and a number of dimension tables. Each dimension table is joined to the fact table using a primary key to foreign key join, but the dimension tables are not joined to each other.
A relational schema whose design represents a dimensional data model. The star schema consists of one or more fact tables and one or more dimension tables that are related through foreign keys.
See also snowflake schema.
The list of currently accessible values for a given dimension. The status of a dimension persists within a particular session, and does not change until it is changed deliberately. When an analytic workspace is first attached to a session, all members are in status.
See also cube dimension, dimension member.
See aggregation.
A hierarchy defined only by the parent-child relationships among dimension members. The dimension members at a particular distance from the base level do not form a meaningful group for analysis, so the levels are not named. For example, an employee dimension might have a parent-child relation that identifies each employee's supervisor. However, levels that group together first-, second-, and third-level supervisors and so forth may not be meaningful for analysis.
See also hierarchy, level-based hierarchy.
This chapter introduces the powerful analytic resources available in the Oracle Database with the OLAP option. It consists of the following topics:
Developing Reports and Dashboards Using SQL Tools and Application Builders
Oracle Database offers the industry's first and only embedded OLAP server. Oracle OLAP provides native multidimensional storage and speed-of-thought response times when analyzing data across multiple dimensions. The database provides rich support for analytics such as time series calculations, forecasting, advanced aggregation with additive and nonadditive operators, and allocation operators. These capabilities make the Oracle database a complete analytical platform, capable of supporting the entire spectrum of business intelligence and advanced analytical applications.
By integrating multidimensional objects and analytics into the database, Oracle provides the best of both worlds: the power of multidimensional analysis along with the reliability, availability, security, and scalability of the Oracle database.
Oracle OLAP is fully integrated into Oracle Database. At a technical level, this means:
The OLAP engine runs within the kernel of Oracle Database.
Dimensional objects are stored in Oracle Database in their native multidimensional format.
Cubes and other dimensional objects are first class data objects represented in the Oracle data dictionary.
Data security is administered in the standard way, by granting and revoking privileges to Oracle Database users and roles.
Applications can query dimensional objects using SQL.
The benefits to your organization are significant. Oracle OLAP offers the power of simplicity: One database, standard administration and security, standard interfaces and development tools.
Oracle OLAP makes it easy to enrich your database and your applications with interesting analytic content. Native SQL access to Oracle multidimensional objects and calculations greatly eases the task of developing dashboards, reports, business intelligence (BI) and analytical applications of any type compared to systems that offer proprietary interfaces. Moreover, SQL access means that the power of Oracle OLAP analytics can be used by any database application, not just by the traditional limited collection of OLAP applications.
Because Oracle OLAP is completely embedded in the Oracle database, there is no administration learning curve as is typically associated with stand-alone OLAP servers. You can leverage your existing DBA staff, rather than invest in specialized administration skills.
One major administrative advantage of Oracle's embedded OLAP technology is automated cube maintenance. With stand-alone OLAP servers, the burden of refreshing the cube is left entirely to the administrator. This can be a complex and potentially error-prone job. The administrator must create procedures to extract the changed data from the relational source, move the data from the source system to the system running the stand-alone OLAP server, load and rebuild the cube. The DBA must take responsibility for the security of the deltas (changed values) during this process as well.
With Oracle OLAP, in contrast, cube refresh is handled entirely by the Oracle database. The database tracks the staleness of the dimensional objects, automatically keeps track of the deltas in the source tables, and automatically applies only the changed values during the refresh process. The DBA simply schedules the refresh at appropriate intervals, and Oracle Database takes care of everything else.
With Oracle OLAP, standard Oracle Database security features are used to secure your multidimensional data.
In contrast, with a stand-alone OLAP server, administrators must manage security twice: once on the relational source system and again on the OLAP server system. Additionally, they must manage the security of data in transit from the relational system to the stand-alone OLAP system.
Business intelligence and analytical applications are dominated by actions such as drilling up and down hierarchies and comparing aggregate values such as period-over-period, share of parent, projections onto future time periods, and a myriad of similar calculations. Often these actions are essentially random across the entire space of potential hierarchical aggregations. Because Oracle OLAP precomputes or efficiently computes on the fly all aggregates in the defined multidimensional space, it delivers unmatched performance for typical business intelligence applications.
Oracle OLAP queries take advantage of Oracle shared cursors, dramatically reducing memory requirements and increasing performance.
When Oracle Database is installed with Real Application Clusters (RAC), OLAP applications receive the same benefits in performance, scalability, fail over, and load balancing as any other application.
All these features add up to reduced costs. Administrative costs are reduced because existing personnel skills can be leveraged. Moreover, the Oracle database can manage the refresh of dimensional objects, a complex task left to administrators in other systems. Standard security reduces administration costs as well. Application development costs are reduced because the availability of a large pool of application developers who are SQL knowledgeable, and a large collection of SQL-based development tools means applications can be developed and deployed more quickly. Any SQL-based development tool can take advantage of Oracle OLAP. Hardware costs are reduced by Oracle OLAP's efficient management of aggregations, use of shared cursors, and Oracle RAC, which enables highly scalable systems to be built from low-cost commodity components.
Analysts can choose any SQL query and analysis tool for selecting, viewing, and analyzing the data. You can use your favorite tool or application, or use one of the tools supplied with Oracle Database.
Figure 1-1 displays a portion of a dashboard created in Oracle Application Express, which is distributed with Oracle Database. Application Express generates HTML reports that display the results of SQL queries. It only understands SQL; it has no special knowledge of dimensional objects.
This dashboard demonstrates information-rich calculations such as ratio, share, prior period, and cumulative total. Separate tabs on the dashboard present Profitability Analysis, Sales Analysis, and Product Analysis. Each tab presents the data in dials, bar charts, horizontal bar charts, pie charts, and cross-tabular reports. A drop-down list in the upper left corner provides a choice of Customers.
The dial displays the quarterly profit margin. To the right is a bar chart that compares current profits with year-ago profits.
The pie chart in Figure 1-2 displays the percent share that each product family contributed to the total profits in the last quarter.
The horizontal bar chart in Figure 1-3 displays ranked results for locations with the largest gains in profitability from a year ago. Decision makers can see at a glance how each location improved by the last quarter.
Figure 1-4 compares current profits with year-to-date, year-to-date year ago, the change between year-to-date and year-to-date year ago, and percent change between year-to-date and year-to-date year-ago profits. The cross-tabular report features interactive drilling, so that decision makers can easily see the detailed data that contributed to a parent value of interest.
Dimensional objects are an integral part of OLAP. Because OLAP is on-line, it must provide answers quickly; analysts pose iterative queries during interactive sessions, not in batch jobs that run overnight. And because OLAP is also analytic, the queries are complex. The dimensional objects and the OLAP engine are designed to solve complex queries in real time.
The dimensional objects include cubes, measures, dimensions, attributes, levels, and hierarchies. The simplicity of the model is inherent because it defines objects that represent real-world business entities. Analysts know which business measures they are interested in examining, which dimensions and attributes make the data meaningful, and how the dimensions of their business are organized into levels and hierarchies.
Figure 1-5 shows the general relationships among dimensional objects.
The dimensional data model is highly structured. Structure implies rules that govern the relationships among the data and control how the data can be queried. Cubes are the physical implementation of the dimensional model, and thus are highly optimized for dimensional queries. The OLAP engine leverages this innate dimensionality in performing highly efficient cross-cube joins for inter-row calculations, outer joins for time series analysis, and indexing. Dimensions are pre-joined to the measures. The technology that underlies cubes is based on an indexed multidimensional array model, which provides direct cell access.
The OLAP engine manipulates dimensional objects in the same way that the SQL engine manipulates relational objects. However, because the OLAP engine is optimized to calculate analytic functions, and dimensional objects are optimized for analysis, analytic and row functions can be calculated much faster in OLAP than in SQL.
The dimensional model enables Oracle OLAP to support high-end business intelligence tools and applications such as OracleBI Discoverer Plus OLAP, OracleBI Spreadsheet Add-In, OracleBI Suite Enterprise Edition, BusinessObjects Enterprise, and Cognos ReportNet.
Cubes provide a means of organizing measures that have the same shape, that is, they have the exact same dimensions. Measures in the same cube can easily be analyzed and displayed together.
A cube usually corresponds to a single fact table or view.
Measures populate the cells of a cube with the facts collected about business operations. Measures are organized by dimensions, which typically include a Time dimension.
An analytic database contains snapshots of historical data, derived from data in a transactional database, legacy system, syndicated sources, or other data sources. Three years of historical data is generally considered to be appropriate for analytic applications.
Measures are static and consistent while analysts are using them to inform their decisions. They are updated in a batch window at regular intervals: weekly, daily, or periodically throughout the day. Some administrators refresh their data by adding periods to the time dimension of a measure, and may also roll off an equal number of the oldest time periods. Each update provides a fixed historical record of a particular business activity for that interval. Other administrators do a full rebuild of their data rather than performing incremental updates.
A critical decision in defining a measure is the lowest level of detail. Users may never view this detail data, but it determines the types of analysis that can be performed. For example, market analysts (unlike order entry personnel) do not need to know that Beth Miller in Ann Arbor, Michigan, placed an order for a size 10 blue polka-dot dress on July 6, 2006, at 2:34 p.m. But they might want to find out which color of dress was most popular in the summer of 2006 in the Midwestern United States.
The base level determines whether analysts can get an answer to this question. For this particular question, Time could be rolled up into months, Customer could be rolled up into regions, and Product could be rolled up into items (such as dresses) with an attribute of color. However, this level of aggregate data could not answer the question: At what time of day are women most likely to place an order? An important decision is the extent to which the data has been aggregated before being loaded into a data warehouse.
Dimensions contain a set of unique values that identify and categorize data. They form the edges of a cube, and thus of the measures within the cube. Because measures are typically multidimensional, a single value in a measure must be qualified by a member of each dimension to be meaningful. For example, the Sales measure has four dimensions: Time, Customer, Product, and Channel. A particular Sales value (43,613.50) only has meaning when it is qualified by a specific time period (Feb-06), a customer (Warren Systems), a product (Portable PCs), and a channel (Catalog).
Base-level dimension values correspond to the unique keys of a fact table.
A hierarchy is a way to organize data at different levels of aggregation. In viewing data, analysts use dimension hierarchies to recognize trends at one level, drill down to lower levels to identify reasons for these trends, and roll up to higher levels to see what affect these trends have on a larger sector of the business.
Each level represents a position in the hierarchy. Each level above the base (or most detailed) level contains aggregate values for the levels below it. The members at different levels have a one-to-many parent-child relation. For example,
Q1-05 and
Q2-05 are the children of
2005, thus
2005 is the parent of
Q1-05 and
Suppose a data warehouse contains snapshots of data taken three times a day, that is, every 8 hours. Analysts might normally prefer to view the data that has been aggregated into days, weeks, quarters, or years. Thus, the Time dimension needs a hierarchy with at least five levels.
Similarly, a sales manager with a particular target for the upcoming year might want to allocate that target amount among the sales representatives in his territory; the allocation requires a dimension hierarchy in which individual sales representatives are the child values of a particular territory.
Hierarchies and levels have a many-to-many relationship. A hierarchy typically contains several levels, and a single level can be included in more than one hierarchy.
Each level typically corresponds to a column in a dimension table or view. The base level is the primary key.
Although hierarchies are typically composed of named levels, they do not have to be. The parent-child relations among dimension members may not define meaningful levels. For example, in an employee dimension, each manager has one or more reports, which forms a parent-child relation. Creating levels based on these relations (such as individual contributors, first-level managers, second-level managers, and so forth) may not be meaningful for analysis. Likewise, the line item dimension of financial data does not have levels. This type of hierarchy is called a value-based hierarchy.
An attribute provides additional information about the data. Some attributes are used for display. For example, you might have a product dimension that uses Stock Keeping Units (SKUs) for dimension members. The SKUs are an excellent way of uniquely identifying thousands of products, but are meaningless to most people if they are used to label the data in a report or a graph. You would define attributes for the descriptive labels.
You might also have attributes like colors, flavors, or sizes. This type of attribute can be used for data selection and answering questions such as: Which colors were the most popular in women's dresses in the summer of 2005? How does this compare with the previous summer?
Time attributes can provide information about the Time dimension that may be useful in some types of analysis, such as identifying the last day or the number of days in each time period.
Each attribute typically corresponds to a column in dimension table or view.
User's Guide
11g Release 1 (11.1)
June 2008
Oracle OLAP User's Guide, 11g Release 1 (11.1)
Copyright © 2003, 2008, Oracle. All rights reserved.
The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.
Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.
This chapter explains how to design a data model and create dimensions and cubes using Analytic Workspace Manager. It contains the following topics:
Creating a Dimensional Data Store Using Analytic Workspace Manager
Chapter 1 introduced the dimensional objects: Cubes, measures, dimensions, levels, hierarchies, and attributes. In this chapter, you will learn how to define them in Oracle Database, but first you need to decide upon the dimensional model you want to create. What are your measures? What are your dimensions? How can you distinguish between a dimension and an attribute in your data? You can design a dimensional model using pencil and paper, a database design software package, or any other method that suits you.
If your source data is already in a star or snowflake schema, then you already have the elements of a dimensional model:
Fact tables correspond to cubes.
Data columns in the fact tables correspond to measures.
Foreign key constraints in the fact tables identify the dimension tables.
Dimension tables identify the dimensions.
Primary keys in the dimension tables identify the base-level dimension members.
Parent columns in the dimension tables identify the higher level dimension members.
Columns in the dimension tables containing descriptions and characteristics of the dimension members identify the attributes.
You can also get insights into the dimensional model by looking at the reports currently being generated from the source data. The reports will identify the levels of aggregation that interest the report consumers, as well as the attributes used to qualify the data.
While investigating your source data, you may decide to create relational views that more closely match the dimensional model that you plan to create.
See Also:
"Overview of the Dimensional Data Model" for an introduction to dimensional objects
Appendix A, "Designing a Dimensional Model" for a case study of developing a dimensional model for the Global analytic workspace
Analytic Workspace Manager is the primary tool for creating, developing, and managing dimensional objects in Oracle Database. Your goal in using Analytic Workspace Manager is to create a dimensional data store that supports business analysis. This data store can stand alone or store summary data as part of a relational data warehouse.
Populating dimensional objects involves a physical transformation of the data. The first step in that transformation is defining the cubes, measures, dimensions, levels, hierarchies, and attributes. Afterward, you can map these dimensional objects to their relational data sources. The data loading process transforms the data from a relational format into a dimensional format.
Using Analytic Workspace Manager, you can:
Develop a dimensional model of your data.
Instantiate that model as dimensional objects.
Load data from relational tables into those objects.
Define information-rich calculations.
Create materialized views that can be used by the database refresh system.
Automatically generate relational views of the dimensional objects.
You can load data from these sources in the database:
You must have
SELECT privileges on the relational data sources so you can load the data into the dimensions and cubes. This chapter assumes that you have a star, snowflake, or other relational schema that supports dimensional objects.
Figure 3-1 shows the main window of Analytic Workspace Manager. It contains menus, a toolbar, a navigation tree, and property sheets. When you select an object in the navigation tree, the property sheet to the right provides detailed information about that object. When you right-click an object, you get a choice of menu items with appropriate actions for that object.
Analytic Workspace Manager has a full online Help system, which includes context-sensitive Help.
An analytic workspace is a container for storing related cubes. You create dimensions, cubes, and other dimensional objects within the context of an analytic workspace.
To create an analytic workspace:
Open Analytic Workspace Manager and connect to your database instance as the user defined for this purpose.
Create a new analytic workspace in the database:
In the navigation tree, expand the folders until you see the schema where you want to create the analytic workspace.
Right-click Analytic Workspaces, then choose Create Analytic Workspace.
Complete the Create Analytic Workspace dialog box, then choose Create.
The new analytic workspace appears in the Analytic Workspaces folder for the schema.
Define the dimensions for the data.
Define the cubes for the data.
See "Creating Cubes".
Load data into the cubes and dimensions.
When you have finished, you will have an analytic workspace populated with the detail data fetched from relational tables or views. You may also have summarized data and calculated measures.
In addition to the basic steps, you can add functionality to the cubes in these ways:
Develop custom cube scripts to customize the builds.
Generate materialized views that support automatic refresh and query rewrite.
Support multiple languages by adding translations of metadata and attribute values.
Define measure folders to simplify access for end users.
Analytic Workspace Manager saves changes automatically that you make to the analytic workspace. You do not explicitly save your changes.
Saves occur when you take an action such as these:
Click OK or the equivalent button in a dialog box.
For example, when you click Create in the Create Dimension dialog box, the new dimension is committed to the database.
Click Apply in a property sheet.
For example, when you change the labels on the General property page for an object, the change takes effect when you click Apply.
Dimensions are lists of unique values that identify and categorize data. They form the edges of a cube, and thus of the measures within the cube. In a report, the dimension values (or their descriptive attributes) provide labels for the rows and columns.
You can define dimensions that have any of these common forms:
Level-based dimensions that use parent-child relationships to group members into levels. Most dimensions are level-based.
Value-based dimensions that have parent-child relationships among their members, but these relationships do not form meaningful levels.
List or flat dimensions that have no levels or hierarchies.
Dimension Members Must Be Unique
Every dimension member must be a unique value. Depending on your data, you can create a dimension that uses either natural keys or surrogate keys from the relational sources for its members. If you have any doubt that the values are unique across all levels, then keep the default choice of surrogate keys.
Source keys are read from the relational sources without modification. To use the same exact keys as the source data, the values must be unique across levels. Because each level may be mapped to a different relational column, this uniqueness may not be enforced in the source data. For example, a dimension table might have a Day column with values of
1 to
366 and a Week column with values of
1 to
52. Unless you take steps to assure uniqueness, the values from the Week column will overwrite the first 52 Day values.
Surrogate keys ensure uniqueness by adding a level prefix to the members while loading them into the analytic workspace. For the previous example, surrogate keys create two dimension members named
DAY_1 and
WEEK_1, instead of a single member named
1. A dimension that has surrogate keys must be defined with at least one level-based hierarchy.
Analytic Workspace Manager creates surrogate keys unless you specify otherwise.
Time Dimensions Have Special Requirements
You can define dimensions as either User or Time dimensions. Business analysis is performed on historical data, so fully defined time periods are vital. A time dimension table must have columns for period end dates and time span. These required attributes support comparisons with earlier or later time periods. If this information is not available, then you can define Time as a User dimension, but it will not support time-based analysis.
You must define a Time dimension with at least one level to support time-based analysis, such as a custom measure that calculates the difference from the prior period.
Expand the folder for the analytic workspace.
Right-click Dimensions, then choose Create Dimension.
The Create Dimension dialog box is displayed.
Complete the General tab.
If the keys in the source table are unique across levels, you can change the default setting on the Implementation Details tab.
Click Create.
The new dimension appears as a subfolder under Dimensions.
Figure 3-2 shows the creation of the Product dimension.
For business analysis, data is typically summarized by level. For example, your database may contain daily snapshots of a transactional database. Days are the base level. You might summarize this data at the weekly, quarterly, and yearly levels.
Levels have parent-child or one-to-many relationships, which form a level-based hierarchy. For example, each week summarizes seven days, each quarter summarizes 13 weeks, and each year summarizes four quarters. This hierarchical structure enables analysts to detect trends at the higher levels, then drill down to the lower levels to identify factors that contributed to a trend.
For each level that you define, you must identify a data source for dimension members at that level. Members at all levels are stored in the same dimension. In the previous example, the Time dimension contains members for weeks, quarters, and years.
To create a level:
Expand the folder for the dimension.
Right-click Levels, then choose Create Level.
The Create Level dialog box is displayed.
Complete the General tab of the Create Level dialog box.
Click Create.
The new level appears as an item in the Levels folder.
Tip:Alternatively, you can create levels in the Create Dimension dialog box Levels tab.
Figure 3-3 shows the creation of the Class level for the Product dimension.
Dimensions can have one or more hierarchies. They can be level-based or value-based.
Level-Based Hierarchies
Most hierarchies are level-based. Analytic Workspace Manager supports these common types of level-based hierarchies:
Normal hierarchies consist of one or more levels of aggregation. Members roll up into the next higher level in a many-to-one relationship, and these members roll up into the next higher level, and so forth to the top level.
Ragged hierarchies contain at least one member with a different base, creating a "ragged" base level for the hierarchy. Ragged hierarchies are not supported for cube materialized views.
Skip-level hierarchies contain at least one member whose parents are more than one level above it, creating a hole in the hierarchy. An example of a skip-level hierarchy is City-State-Country, where at least one city has a country as its parent (for example, Washington D.C. in the United States).
In relational source tables, a skip-level hierarchy may contain nulls in the level columns. Skip-level hierarchies are not supported for cube materialized views.
Multiple hierarchies for a dimension typically share the base-level dimension members and then branch into separate hierarchies. They can share the top level if they use all the same base members and use the same aggregation operators. Otherwise, they need different top levels to store different aggregate values. For example, a Customer dimension may have multiple hierarchies that include all base-level customers and are summed to a shared top level. However, a Time dimension with calendar and fiscal hierarchies must aggregate to separate Calendar Year (January to December) and Fiscal Year (July to June) levels, because they use different selections of base-level members.
Value-Based Hierarchies
You may also have dimensions with parent-child relations that do not support levels. For example, an employee dimension might have a parent-child relation that identifies each employee's supervisor. However, levels that group together first-, second-, and third-level supervisors and so forth may not be meaningful for analysis. Similarly, you might have a line-item dimension with members that cannot be grouped into meaningful levels. In this situation, you can create a value-based hierarchy defined by the parent-child relations, which does not have named levels. You can create value-based hierarchies only for dimensions that use the source keys, because surrogate keys are formed with the names of the levels.
Expand the folder for the dimension.
Right-click Hierarchies, then choose Create Hierarchy.
The Create Hierarchy dialog box is displayed.
Complete the General tab of the Create Hierarchy dialog box.
Click Help for information about these choices.
Click Create.
The new hierarchy appears as an item in the Hierarchies folder.
Figure 3-4 shows the creation of the Primary hierarchy for the Product dimension.
Attributes provide information about the individual members of a dimension. They are used for labeling crosstabular and graphical data displays, selecting data, organizing dimension members, and so forth.
Analytic Workspace Manager creates some attributes automatically when creating a dimension. These attributes have a unique type, such as "Long Description."
All dimensions are created with long and short description attributes. If your source tables include long and short descriptions, then you can map the attributes to the appropriate columns. However, if your source tables include only one set of descriptions, then you can map the long description attributes. If you map the short description attributes to the same column, the data will be loaded twice.
Time dimensions are created with time-span and end-date attributes. This information must be provided for all Time dimension members.
You can create additional "User" attributes that provide supplementary information about the dimension members, such as the addresses and telephone numbers of customers, or the color and sizes of products.
Expand the folder for the dimension.
Right-click Attributes, then choose Create Attribute.
The Create Attribute dialog box is displayed.
Complete the General tab of the Create Attribute dialog box.
Some attributes apply to all dimension members, and others apply to only one level. Your selection in the Apply Attributes To box controls the mapping of the attribute to one column or to more than one.
Click Help for information about these choices.
To change the data type from the default choice of
VARCHAR2, complete the Implementation Details tab.
Click Create.
The new attribute appears as an item in the Attributes folder.
Figure 3-5 shows the creation of the Marketing Manager attribute for the Product dimension. Notice that this attribute applies only to the Item level.
Materialized views require that each dimension of the cube have unique key attributes. These attributes store the original key values of the source dimensions, which may have been changed when creating the embedded total dimensions of the cubes.
Analytic Workspace Manager automatically creates unique key attributes for the dimensions of a cube materialized view. You do not create them manually.
Mapping identifies the relational data source for each dimensional object. After mapping a dimension to a column of a relational table or view, you can load the data. You can create, map, and load each dimension individually, or perform each step for all dimensions before proceeding to the next step.
The mapping window has a tabular view and a graphical view. You can switch between the two views, using the icons at the top of the canvas.
Tabular view: Drag-and-drop the names of individual columns from the schema navigation tree to the rows for the dimensional objects.
Graphical view: Drag-and-drop icons, which represent tables and views, from the schema navigation tree onto the mapping canvas. Then draw lines from the columns to the dimensional objects.
You can use expressions when mapping dimensions in the tabular view. This capability enables you to create the top level of a dimension without having a source column in the dimension table.
Click Help on the Mapping window for more information.
To map a dimension:
In the navigation tree, expand the dimension folder and click Mappings.
The Mapping window contains a schema navigation tree on the left and a mapping table for the dimension with rows for the levels and their attributes. This is the tabular view.
To enlarge the Mapping Window, drag the divider to the left.
In the schema tree, expand the tables, views, or synonyms that contain the dimension members and attributes.
Drag-and-drop the source columns onto the appropriate cells in the mapping table for the dimension.
After you have mapped all levels and attributes, click Apply.
Drag the divider back to the right to reveal the navigation tree.
Figure 3-6 shows the Product dimension mapped in the tabular view. The arrow highlights how the
PRODUCT_DIM.ITEM_BUYER column maps to the
PRODUCT.ITEM.BUYER attribute.
You can view the contents of a particular source column without leaving the mapping window. The information is readily available, eliminating the guesswork when the names are not adequately descriptive.
To see the values in a particular source table or view:
Right-click the source object in either the schema tree or the graphical view of the mapping canvas.
Choose View Data from the pop-up menu.
Figure 3-7 shows the data stored in the
PRODUCT_DIM table.
Analytic Workspace Manager provides several ways to load data into dimensional objects. The quickest way when developing a data model is using the default choices of the Maintenance Wizard. Other methods may be more appropriate in a production environment than the one shown here. They are discussed in "Choosing a Data Maintenance Method".
To load data into the dimensions:
In the navigation tree, right-click the Dimensions folder or the folder for a particular dimension.
Choose Maintain Dimension.
The Maintenance Wizard opens on the Select Objects page.
Select one or more dimensions from Available Target Objects and use the shuttle buttons to move them to Selected Target Objects.
Click Finish to load the dimension values immediately.
The additional pages of the wizard enable you to create a SQL script or submit the load to the Oracle job queue. To use these options, click Next instead.
Review the build log, which appears when the build is complete. If the log shows that errors occurred, then fix them and run the Maintenance Wizard again.
Errors are typically caused by problems in the mapping. Check for incomplete mappings or changes to the source objects.
Figure 3-8 shows the first page of the Maintenance Wizard. Only the Product dimension has been selected for maintenance. All the Product dimension members and attributes will be fetched from the mapped relational sources.
The Maintenance Wizard automatically generates relational views of dimensions and hierarchies. Chapter 4 describes these views and explains how to query them.
Figure 3-9 shows the description of the relational view of the Product Primary hierarchy. You can view the data on the Data tab.
After loading a dimension, you can display the default hierarchy.
To display the �� default hierarchy:
In the navigation tree, right-click the name of a dimension.
Choose View Data.
Figure 3-10 shows the Primary hierarchy of the Product dimension.
Cubes are informational objects that identify measures with the exact same dimensions and thus are candidates for being processed together at all stages: data loading, aggregation, storage, and querying.
Cubes define the shape of your business measures. They are defined by a set of ordered dimensions. The dimensions form the edges of a cube, and the measures are the cells in the body of the cube.
To create a cube:
Expand the folder for the analytic workspace.
Right-click Cubes, then choose Create Cube.
The Create Cube dialog box is displayed.
On the General tab, enter a name for the cube and select its dimensions.
On the Aggregation tab, click the Rules subtab and select an aggregation method for each dimension. If the cube uses more than one method, then you may need to specify the order in which the dimensions are aggregated to get the desired results.
You can ignore the bottom of the tab, unless you want to exclude a hierarchy from the aggregation.
If you run the advisors after mapping the cube, Oracle OLAP can determine the best partitioning and storage options. However, if you want to define these options yourself, then complete the Partitioning and Storage tabs before creating the cube.
Click Create. The new cube appears as a subfolder under Cubes.
Figure 3-11 shows the Rules subtab for the Units cube with the list of operators displayed.
See Also:"Aggregation Operators" for descriptions of the aggregation operators
Measures store the facts collected about your business. Each measure belongs to a particular cube, and thus shares particular characteristics with other measures in the cube, such as the same dimensions. The default characteristics of a measure are inherited from the cube.
To create a measure:
Expand the folder for the cube that has the dimensions of the new measure.
Right-click Measures, then choose Create Measure.
The Create Measure dialog box is displayed.
On the General tab, enter a name for the measure.
Click Create.
The new measure appears in the navigation tree as an item in the Measures folder.
Figure 3-12 shows the General tab of the Create Measure dialog box.
You use the same interface to map cubes as you did to map dimensions, as described in "Mapping Dimensions".
You can use expressions when mapping cubes in the tabular view. This capability enables you to perform tasks like these as part of data maintenance, without any intermediate staging of the data:
Perform calculations on the relational data using any combination of functions and operators available in the OLAP expression syntax.
Create measures that are more aggregate than their relational sources. For example, suppose the Time dimension has columns for Day, Month, Quarter, and Year, and the fact table for Sales is related to Time by the Day foreign key column. In a basic mapping, you would store data in the cube at the Day level. However, you could aggregate it to the Month level during the data refresh. Using a technique called one-up mapping, you would map the cube to the Month column for Time, and specify a join between the dimension table and the fact table on the Day columns.
Click Help on the Mapping window for more information about these techniques.
In the navigation tree, expand the cube folder and click Mappings.
The Mapping window contains a schema navigation tree on the left and a mapping table for the cube and its dimensions. This is the tabular view.
To enlarge the Mapping window, drag the divider to the left.
In the schema tree, expand the tables, views, or synonyms that contain the data for the measures.
Drag-and-drop the source columns onto the appropriate cells in the mapping table for the cube.
After you have mapped all dimensions and measures, click Apply.
Drag the divider back to the right to reduce the size of the Mapping window.
Figure 3-13 shows the mapping canvas with the Units cube mapped to columns in the
UNITS_FACT table. After you save the mappings, Analytic Workspace Manager provides the join conditions for base-level mappings such as the ones shown here.
Partitioning is a method of physically storing the measures in a cube. It improves the performance of large measures in the following ways:
Improves scalability by keeping data structures small. Each partition functions like a smaller measure.
Keeps the working set of data smaller both for queries and maintenance, since the relevant data is stored together.
Enables parallel aggregation during data maintenance. Each partition can be aggregated by a separate process.
Simplifies removal of old data from storage. Old partitions can be dropped, and new partitions can be added.
The number of partitions affects the database resources that can be allocated to loading and aggregating the data in a cube. Partitions can be aggregated simultaneously when sufficient resources have been allocated.
The Cube Partitioning Advisor analyzes the source tables and develops a partitioning strategy. You can accept the recommendations of the Cube Partitioning Advisor, or you can make your own decisions about partitioning.
Note:Run the Cube Partitioning Advisor after mapping the cube to a data source and before loading the data. You can change the partitioning strategy at any time using the Cube Partitioning Advisor, but you will need to reload the data afterward.
You can specify your own partitioning strategy only when creating the cube.
If your partitioning strategy is driven primarily by life-cycle management considerations, then you should partition the cube on the Time dimension. Old time periods can then be dropped as a unit, and new time periods added as a new partition. In Figure 3-15, for instance, the Quarter level of the Time dimension is used as the partitioning key. The Cube Partitioning Advisor has a Time option, which will recommend a hierarchy and a level in the Time dimension for partitioning.
If life-cycle management is not a primary consideration, then run the Cube Partitioning Advisor and choose the Statistics option. The Cube Partitioning Advisor will develop a strategy designed to achieve optimal build and query performance.
To run the Cube Partitioning Advisor:
Map the cube to its data source, if you have not done so already.
On the navigation tree, select the cube to display its property pages.
On the Partitioning tab, click Cube Partitioning Advisor.
Choose Partition Using a Time Dimension or Partition Using Statistics.
Wait while the Cube Partitioning Advisor analyzes the cube. When it is done, the Cube Partitioning Advisor displays its recommendations.
Evaluate the recommendations of the Cube Partitioning Advisor.
Select Accept Partition Advice to accept the recommendations. The cube will be re-created with the new partitions.
Clear the Accept Partition Advice box to reject the recommendations.
Click OK.
Figure 3-14 shows the Cube Partitioning Advisor dialog box.
The Cube Partitioning Advisor might recommend partitioning at the Quarter level of the Calendar hierarchy of the Time dimension. Each Quarter and its descendants are stored in a separate partition. If there are three years of data in the analytic workspace, then partitioning on Quarter produces 12 bottom partitions, in addition to the default top partition. The top partition contains all remaining levels, that is, those above Quarter (such as Year) and those in other hierarchies (such as Fiscal Year or Year-to-Date).
Figure 3-15 illustrates a Time dimension partitioned by Quarter.
You load data into cubes using the same methods as dimensions. However, loading and aggregating the data for your business measures typically takes more time to complete. Unless you are developing a dimensional model using a small sample of data, you may prefer to run the build in one or more background processes.
In the navigation tree, right-click the Cubes folder or the name of a particular cube.
Choose Maintain Cube.
The Maintenance Wizard opens on the Select Objects page.
Select one or more cubes from Available Target Objects and use the shuttle buttons to move them to Selected Target Objects. If the dimensions are already loaded, you can omit them from Selected Target Objects.
On the Dimension Data Processing Options page, you can keep the default values.
On the Task Processing Options page, you can submit the build to the Oracle job queue or create a SQL script that you can run outside of Analytic Workspace Manager.
You can also select the number of processes to dedicate to this build. The number of parallel processes is limited by the smallest of these numbers: The number of partitions in the cube, the number of processes dedicated to the build, and the setting of the
JOB_QUEUE_PROCESSES initialization parameter.
Click Help for information about these choices.
Click Finish.
Figure 3-16 shows the build submitted immediately to the Oracle job queue.
After loading a cube, you can display the data for your business measures in Analytic Workspace Manager.
To display the data in a cube:
In the navigation tree, right-click the cube.
Choose View Data from the pop-up menu.
The Measure Data Viewer displays the selected measure in a crosstab at the top of the page and a graph at the bottom of the page. On the crosstab, you can expand and collapse the dimension hierarchies that label the rows and columns. You can also change the location of a dimension by pivoting or swapping it. If you wish, you can use more than one dimension to label the columns and rows, by nesting one dimension under another.
To pivot, drag a dimension from one location and drop it at another location, usually above or below another dimension.
To swap dimensions, drag and drop one dimension directly over another dimension, so they exchange locations.
To make extensive changes to the selection of data, choose Query Builder from the File menu.
Figure 3-17 shows the Units cube in the Measure Viewer.
The Maintenance Wizard automatically generates relational views of a cube. Chapter 4 describes these views and explains how to query them.
Figure 3-18 shows the description of the relational view of the Units cube.
While developing a dimensional model of your data, mapping and loading each object immediately after you create it is a good idea. That way, you can detect and correct any errors that you made to the object definition or the mapping.
However, in a production environment, you want to perform routine maintenance as quickly and easily as possible. For this stage, you can choose among data maintenance methods.
You can refresh all cubes using the Maintenance Wizard. This wizard enables you to refresh a cube immediately, or submit the refresh as a job to the Oracle job queue, or generate a PL/SQL script. You can run the script manually or using a scheduling utility, such as Oracle Enterprise Manager Scheduler or the
DBMS_SCHEDULER PL/SQL package.
The generated script calls the
BUILD procedure of the
DBMS_CUBE PL/SQL package. You can modify this script or develop one from scratch using this package.
The data for a partitioned cube will be loaded and aggregated in parallel when multiple processes have been allocated to the build. You will be able to see this in the build log.
In addition, each cube can support these data maintenance methods:
Custom cube scripts
Cube materialized views
If you are defining cubes to replace existing materialized views, then you will use the materialized views as an integral part of data maintenance. Materialized view capabilities restrict the types of analytics that can be performed by a custom cube script.
See Also:
A cube script is an ordered list of steps that prepare a cube for querying. Each step represents a particular data transformation. By specifying the order in which these steps are performed, you can allow for interdependencies.
Each cube automatically has a default cube script named Load_and_Aggregate that loads the data and aggregates it using the rules defined on the cube. You can define any number of additional scripts and designate one of them as the default cube script. All methods of refreshing a cube execute the default cube script. You can execute other cube scripts manually using the Maintenance Wizard.
You can choose from these step types:
Clear Data: Clears the data from the entire cube, from selected measures, or from selected portions of the cube. You can clear just the detail data (called leaves) for a fast refresh, just the aggregate data, or both for a complete refresh. Clearing old data values is typically done before loading new values.
Load: Loads the data from the source tables into the cube. You can load all measures in the cube or just selected measures.
Aggregation: Generates aggregate values using the rules defined for the cube. You can aggregate the entire cube, selected measures, or selected portions of the cube.
Analyze: Generates optimizer statistics, which can improve the performance of some types of queries. For more information, see "Analyzing Cubes and Dimensions". Generating statistics is typically done at the end of data maintenance.
PL/SQL: Executes a PL/SQL command or script. You can run a PL/SQL script, for example, at the beginning of data maintenance to initiate a refresh of the relational source tables.
If a cube is used to support advanced analytics in a cube script, then it cannot be enhanced as a cube materialized view, as described in "Adding Materialized View Capability to a Cube". In this case, you are responsible for detecting when the data in the cube is stale and needs to be refreshed.
To create a cube script:
Expand the folder for a cube.
Right-click Cube Scripts, then choose Create Cube Script.
The Create Cube Script dialog box is displayed.
On the General tab, enter a name for the cube script.
To create a new step, click New Step.
Choose the type of step.
The New Step dialog box is displayed for that type of step.
Complete the tabs, then click OK.
The new step is listed on the Cube Script General tab.
Click Create.
The new cube script appears as an item in the Cube Script folder.
To run the cube script:
Right-click the cube script on the navigation tree, and choose Run Cube Script.
The Maintenance Wizard opens.
Follow the steps of the wizard.
To view the results, right-click the cube and choose View Data.
Figure 3-19 shows the Create Cube Script dialog box, in which several steps have already been defined.
Oracle OLAP cubes can be enhanced with materialized view capabilities. They can be incrementally refreshed through the Oracle Database materialized view subsystem, and they can serve as targets for transparent rewrite of queries against the source tables. A cube that has been enhanced in this way is called a cube materialized view.
The OLAP dimensions associated with a cube materialized view are also defined with materialized view capabilities.
A cube must conform to these requirements, before it can be designated as a cube materialized view:
All dimensions of the cube have at least one level and one hierarchy. Ragged and skip-level hierarchies are not supported.
All dimensions of the cube use the same aggregation operator, which is either
MIN, or
The cube is fully defined and mapped. For example, if the cube has five measures, then all five are mapped to the source tables.
The detail tables support dimension and rely constraints. If they have not been defined, then use the Relational Schema Advisor to generate a script that defines them on the detail tables.
The cube is compressed.
The cube can be enriched with calculated measures, but it cannot support more advanced analytics in a cube script.
See Also:"Cube Materialized Views"
To add materialized view capabilities:
In the navigation tree, select a cube.
The property sheets for the cube are displayed.
Choose the Materialized Views tab.
Review the check list and, if some tests failed, fix the cause of the problem.
You cannot define a cube materialized view until the cube is valid.
For automatic refresh, complete just the top half of the page. For query rewrite, complete the entire page.
Click Help for information about the choices on this page.
Click Apply.
The cube materialized views appear in the same schema as the analytic workspace. A materialized view is created for the cube and each of its dimensions. Cube materialized views do not store data; the data is stored in the cube. A
CB$ prefix identifies the tables as cube materialized views.
The initial state of a new materialized view is stale, so it will not support query rewrite until after it is refreshed. You can specify the first refresh time on the Materialized View tab of the cube, or you can run the Maintenance Wizard.
Figure 3-20 shows the Materialized View tab of the Units Cube. It specifies an automatic refresh of the data every Thursday at 10:00 P.M.
A single analytic workspace can support multiple languages. This support enables users of OLAP applications and tools to view the metadata in their native languages. For example, you can provide translations for the display names of measures, cubes, and dimensions. You can also map attributes to multiple columns, one for each language.
The number and choice of languages is restricted only by the database character set and your ability to provide translated text. Languages can be added or removed at any time.
To add support for multiple languages:
In the navigation tree, expand the folder for the analytic workspace.
Select Languages to display its property page.
On the General tab, click Modify Languages.
On the Modify Languages dialog box, select the languages that you want the analytic workspace to support. Use the shuttle keys to move them to the Selected Languages box.
Click OK to return to the Languages property page.
Enter the translations of the various labels and descriptions. Each language has a column where you can enter this information.
For each dimension, open the Mappings window. Map the attributes to the source columns for each language.
Measure folders organize and label groups of measures. Users may have access to several analytic workspaces or relational schemas with measures named Sales or Costs, and measure folders provide a way for applications to differentiate among them.
Expand the folder for the analytic workspace.
Right-click Measure Folders, then choose Create Measure Folder from the pop-up menu.
Complete the General tab of the Create Measure Folder dialog box.
Click Help for specific information about these choices.
The new measure folder appears in the navigation tree under Measure Folders. You can also create subfolders.
Figure 3-21 shows creation of a measure folder.
Analytic Workspace Manager enables you to save all or part of the data model as a text file. This text file contains the XML definitions of the dimensional objects, such as dimensions, levels, hierarchies, attributes, and measures. Only the metadata is saved, not the data. Templates are small files, so you can easily distribute them by email or on a Web site, just as the templates for Global and Sales History are distributed on the Oracle Web site. To re-create the dimensional objects, you simply identify the templates in Analytic Workspace Manager.
You can save the following types of objects as XML templates:
Analytic workspace: Saves all dimensional objects. You can save measure folders only by saving the complete analytic workspace.
Dimension: Saves the dimension and its levels, hierarchies, attributes, and mappings.
Cube: Saves the cube and its measures, calculated measures, cube scripts, and mappings.
You can save the template in anywhere on your local system.
To create a template:
In the navigation tree, right-click an analytic workspace, a dimension, or a cube, and choose Save object to Template.
To re-create an analytic workspace from a template:
In the navigation tree, right-click Analytic Workspaces and choose Create Analytic Workspace Fr��om Template.
To re-create a dimension or a cube from a template:
Create or open an analytic workspace.
In the navigation tree, right-click Dimensions or Cubes and choose Create object From Template.
Oracle OLAP adds power to your SQL applications by providing extensive analytic content and fast query response times. A SQL query interface enables any application to query cubes and dimensions without any knowledge of OLAP.
The OLAP option automatically generates a set of relational views on cubes, dimensions, and hierarchies. SQL applications query these views to display the information-rich contents of these objects to analysts and decision makers. You can also create custom views that comply with the structure expected by your applications, using the system-generated views like base tables.
In this chapter, you will learn the basic methods for querying dimensional objects in SQL. It contains the following topics:
See Also:
The system-generated views are created in the same schema as the analytic workspace. Oracle OLAP provides three types of views:
Cube views
Dimension views
Hierarchy views
These views are related in the same way as fact and dimension tables in a star schema. Cube views serve the same function as fact tables, and hierarchy views and dimension views serve the same function as dimension tables. Typical queries will join a cube view with either a hierarchy view or a dimension view.
Each cube has a cube view that presents the data for all the measures and calculated measures in the cube. You can use a cube view like a fact table in a star or snowflake schema. However, the cube view contains all the summary data in addition to the detail level data.
The default name for a cube view is
_VIEW. To find the view for
UNITS_CUBE in your schema, you might issue a query like this one:
SELECT view_name FROM user_views WHERE view_name LIKE 'UNITS_CUBE%'; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW
The next query returns the names of all the cube views in your schema from
SELECT view_name FROM user_cube_views; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW PRICE_CUBE_VIEW
Like a fact table, a cube view contains a column for each measure, calculated measure, and dimension in the cube. In the following example,
UNITS_CUBE_VIEW has columns for the
UNITS, and
COST measures, for several calculated measures on
SALES, and for the
PRODUCT, and
CHANNEL dimensions.
DESCRIBE units_cube_view Name Null? Type ----------------------------------------- -------- ---------------------------- SALES NUMBER UNITS NUMBER COST NUMBER SALES_PP NUMBER SALES_CHG_PP NUMBER SALES_PCTCHG_PP NUMBER SALES_PROD_SHARE_PARENT NUMBER SALES_PROD_SHARE_TOTAL NUMBER SALES_PROD_RANK_PARENT_PP NUMBER TIME VARCHAR2(100) CUSTOMER VARCHAR2(100) PRODUCT VARCHAR2(100) CHANNEL VARCHAR2(100)
USER_CUBE_VIEW_COLUMNS data dictionary view describes the columns of a cube view, as shown by the following query.
SELECT column_name, column_type FROM user_cube_view_columns WHERE view_name = 'UNITS_CUBE_VIEW'; COLUMN_NAME COLUMN_TYPE ------------------------------ -------------- SALES MEASURE UNITS MEASURE COST MEASURE SALES_PP MEASURE SALES_CHG_PP MEASURE SALES_PCTCHG_PP MEASURE SALES_PROD_SHARE_PARENT MEASURE SALES_PROD_SHARE_TOTAL MEASURE SALES_PROD_RANK_PARENT_PP MEASURE TIME KEY CUSTOMER KEY PRODUCT KEY CHANNEL KEY 13 rows selected.
You can display the contents of a cube view quickly with a query like this one. All levels of the data are contained in the cube, from the detail level to the top.
SELECT sales, units, time, customer, product, channel FROM units_cube_view WHERE ROWNUM < 15; SALES UNITS TIME CUSTOMER PRODUCT CHANNEL ---------- ---------- ---------- ---------- ---------- -------- 1120292752 4000968 TOTAL TOTAL TOTAL TOTAL 134109248 330425 CY1999 TOTAL TOTAL TOTAL 130276514 534069 CY2003 TOTAL TOTAL TOTAL 100870877 253816 CY1998 TOTAL TOTAL TOTAL 136986572 565718 CY2005 TOTAL TOTAL TOTAL 140138317 584929 CY2006 TOTAL TOTAL TOTAL 144290686 587419 CY2004 TOTAL TOTAL TOTAL 124173522 364233 CY2000 TOTAL TOTAL TOTAL 92515295 364965 CY2002 TOTAL TOTAL TOTAL 116931722 415394 CY2001 TOTAL TOTAL TOTAL 31522409.5 88484 CY2000.Q1 TOTAL TOTAL TOTAL 27798426.6 97346 CY2001.Q2 TOTAL TOTAL TOTAL 29691668.2 105704 CY2001.Q3 TOTAL TOTAL TOTAL 32617248.6 138953 CY2005.Q3 TOTAL TOTAL TOTAL 14 rows selected.
Each dimension has one dimension view plus a hierarchy view for each hierarchy associated with the dimension. For example, a Time dimension might have these three views:
Time dimension view
Calendar hierarchy view
Fiscal hierarchy view
You can use dimension views and hierarchy views like dimension tables in a star schema.
USER_CUBE_DIM_VIEWS identifies the dimension views for all dimensions. The default name for a dimension view is
SELECT * FROM user_cube_dim_views; DIMENSION_NAME VIEW_NAME ------------------------------ ------------------------------ PRODUCT PRODUCT_VIEW CUSTOMER CUSTOMER_VIEW CHANNEL CHANNEL_VIEW TIME TIME_VIEW
USER_CUBE_HIER_VIEWS identifies the hierarchy views for all the dimensions. For a hierarchy view, the default name is
_VIEW. The following query returns the dimension, hierarchy, and view names.
SELECT * FROM user_cube_hier_views ORDER BY dimension_name; DIMENSION_NAME HIERARCHY_NAME VIEW_NAME --------------- --------------- ------------------------------ CHANNEL PRIMARY CHANNEL_PRIMARY_VIEW CUSTOMER SEGMENT CUSTOMER_SEGMENT_VIEW CUSTOMER SHIPMENTS CUSTOMER_SHIPMENTS_VIEW PRODUCT PRIMARY PRODUCT_PRIMARY_VIEW TIME FISCAL TIME_FISCAL_VIEW TIME CALENDAR TIME_CALENDAR_VIEW
Like a dimension table, a dimension view contains a key column, level name, level keys for every level of every hierarchy associated with the dimension, and attribute columns. In the following example,
TIME_VIEW has a column for the dimension keys, the level name, and the dimension attributes.
DESCRIBE time_view Name Null? Type ----------------------------------------- -------- ---------------------------- DIM_KEY VARCHAR2(100) LEVEL_NAME VARCHAR2(30) DIM_ORDER NUMBER END_DATE DATE LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) TIME_SPAN NUMBER
USER_CUBE_DIM_VIEW_COLUMNS describes the information in each column, as shown in this query.
SELECT column_name, column_type FROM user_cube_dim_view_columns WHERE view_name ='TIME_VIEW'; COLUMN_NAME COLUMN_TYPE ------------------------------ -------------------- DIM_KEY KEY LEVEL_NAME LEVEL_NAME DIM_ORDER DIM_ORDER END_DATE ATTRIBUTE TIME_SPAN ATTRIBUTE LONG_DESCRIPTION ATTRIBUTE SHORT_DESCRIPTION ATTRIBUTE 7 rows selected.
The following query displays the level and attributes of each dimension key.
SELECT dim_key, level_name, long_description description, time_span, end_date FROM time_view WHERE dim_key LIKE '%2005%'; DIM_KEY LEVEL_NAME DESCRIPTION TIME_SPAN END_DATE ------------ -------------------- ------------ ---------- --------- CY2005 CALENDAR_YEAR 2005 365 31-DEC-05 CY2005.Q2 CALENDAR_QUARTER Q2.05 91 30-JUN-05 CY2005.Q4 CALENDAR_QUARTER Q4.05 92 31-DEC-05 CY2005.Q3 CALENDAR_QUARTER Q3.05 92 30-SEP-05 CY2005.Q1 CALENDAR_QUARTER Q1.05 90 31-MAR-05 2005.01 MONTH JAN-05 31 31-JAN-05 2005.05 MONTH MAY-05 31 31-MAY-05 2005.07 MONTH JUL-05 31 31-JUL-05 2005.03 MONTH MAR-05 31 31-MAR-05 2005.04 MONTH APR-05 30 30-APR-05 2005.08 MONTH AUG-05 31 31-AUG-05 2005.09 MONTH SEP-05 30 30-SEP-05 2005.02 MONTH FEB-05 28 28-FEB-05 2005.11 MONTH NOV-05 30 30-NOV-05 2005.06 MONTH JUN-05 30 30-JUN-05 2005.10 MONTH OCT-05 31 31-OCT-05 2005.12 MONTH DEC-05 31 31-DEC-05 FY2005 FISCAL_YEAR FY2005 365 30-JUN-05 FY2005.Q4 FISCAL_QUARTER Q4 FY-05 91 30-JUN-05 FY2005.Q1 FISCAL_QUARTER Q1 FY-05 92 30-SEP-04 FY2005.Q2 FISCAL_QUARTER Q2 FY-05 92 31-DEC-04 FY2005.Q3 FISCAL_QUARTER Q3 FY-05 90 31-MAR-05 22 rows selected.
Like the dimension views, the hierarchy views also contain columns for the dimension key, level name, and level keys. However, all of the rows and columns are associated with the dimension keys that belong to the hierarchy.
DESCRIBE time_calendar_view Name Null? Type ----------------------------------------- -------- ---------------------------- DIM_KEY VARCHAR2(100) LEVEL_NAME VARCHAR2(30) DIM_ORDER NUMBER HIER_ORDER NUMBER LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) END_DATE DATE TIME_SPAN NUMBER PARENT VARCHAR2(100) TOTAL VARCHAR2(100) CALENDAR_YEAR VARCHAR2(100) CALENDAR_QUARTER VARCHAR2(100) MONTH VARCHAR2(100)
The following query displays the dimension keys, parent key, and the full ancestry for calendar year 2005.
SELECT dim_key, long_description description, parent, calendar_year year, calendar_quarter quarter, month FROM time_calendar_view WHERE calendar_year='CY2005' ORDER BY level_name, end_date; DIM_KEY DESCRIPTION PARENT YEAR QUARTER MONTH ------------ ------------ ------------ ------------ ------------ ------------ CY2005.Q1 Q1.05 CY2005 CY2005 CY2005.Q1 CY2005.Q2 Q2.05 CY2005 CY2005 CY2005.Q2 CY2005.Q3 Q3.05 CY2005 CY2005 CY2005.Q3 CY2005.Q4 Q4.05 CY2005 CY2005 CY2005.Q4 CY2005 2005 TOTAL CY2005 2005.01 JAN-05 CY2005.Q1 CY2005 CY2005.Q1 2005.01 2005.02 FEB-05 CY2005.Q1 CY2005 CY2005.Q1 2005.02 2005.03 MAR-05 CY2005.Q1 CY2005 CY2005.Q1 2005.03 2005.04 APR-05 CY2005.Q2 CY2005 CY2005.Q2 2005.04 2005.05 MAY-05 CY2005.Q2 CY2005 CY2005.Q2 2005.05 2005.06 JUN-05 CY2005.Q2 CY2005 CY2005.Q2 2005.06 2005.07 JUL-05 CY2005.Q3 CY2005 CY2005.Q3 2005.07 2005.08 AUG-05 CY2005.Q3 CY2005 CY2005.Q3 2005.08 2005.09 SEP-05 CY2005.Q3 CY2005 CY2005.Q3 2005.09 2005.10 OCT-05 CY2005.Q4 CY2005 CY2005.Q4 2005.10 2005.11 NOV-05 CY2005.Q4 CY2005 CY2005.Q4 2005.11 2005.12 DEC-05 CY2005.Q4 CY2005 CY2005.Q4 2005.12 17 rows selected.
Querying a cube is similar to querying a star schema. In a star schema, you join a fact table to a dimension table. The fact table provides the numerical business measures, and the dimension table provides descriptive attributes that give meaning to the data. Similarly, you join a cube view with either a dimension view or a hierarchy view to provide fully identified and meaningful data to your users.
For dimensions with no hierarchies, use the dimension views in your queries. For dimensions with hierarchies, use the hierarchy views, because they contain more information than the dimension views.
When querying a cube, remember these guidelines:
Apply a filter to every dimension.
The cube contains both detail level and aggregated data. A query with an unfiltered dimension typically returns more data than users need, which negatively impacts performance.
Let the cube aggregate the data.
Because the aggregations are already calculated in the cube, a typical query does not need a
GROUP BY clause. Simply select the aggregations you want by using the appropriate filters on the dimension keys or attributes.
To create a level filter, you must know the names of the dimension levels. You can easily acquire them by querying the dimension or hierarchy views:
SELECT DISTINCT level_name FROM time_calendar_view; LEVEL_NAME ------------------------------ CALENDAR_YEAR CALENDAR_QUARTER MONTH TOTAL
Several data dictionary views list the names of the levels. The following example queries
SELECT level_name FROM user_cube_hier_levels WHERE dimension_name = 'TIME' AND hierarchy_name ='CALENDAR'; LEVEL_NAME -------------------- TOTAL CALENDAR_YEAR CALENDAR_QUARTER MONTH
To see the importance of applying a filter to every dimension, consider the query in Example 4-1, which has no filter on the time dimension.
Example 4-1 Displaying Aggregates at All Levels of Time
/* Select key descriptions and facts */ SELECT t.long_description time, ROUND(f.sales) sales /* From dimension views and cube view */ FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* No filter on Time */ WHERE p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY t.end_date;
Without a filter on the Time dimension, the query returns values for every level of time. This is more data than users typically want to see, and the volume of data returned can negatively impact performance.
Now consider the results when a filter restricts Time to yearly data.
Example 4-2 shows a basic query. It selects the Sales measure from
UNITS_CUBE_VIEW, and joins the keys from the cube view to the hierarchy views to get descriptions of the keys.
Example 4-2 Basic Cube View Query
/* Select key descriptions and facts */ SELECT t.long_description time, ROUND(f.sales) sales /* From dimension views and cube view */ FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Create level filters */ WHERE t.level_name = 'CALENDAR_YEAR' AND p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY t.end_date;
Example 4-2 selects the following rows. For
PRODUCT, and
CHANNEL, only one value is at the top level.
TIME has a value for each calendar year.
TIME SALES -------- ---------- 1998 100870877 1999 134109248 2000 124173522 2001 116931722 2002 92515295 2003 130276514 2004 144290686 2005 136986572 2006 140138317 9 rows selected.
Dimension attributes also provide a useful way to select the data for a query. The
WHERE clause in Example 4-3 uses attributes values to filter all of the dimensions.
Example 4-3 Selecting Data With Attribute Filters
/* Select key descriptions and facts */ SELECT t.long_description time, p.long_description product, cu.long_description customer, ch.long_description channel, ROUND(f.sales) sales /* From dimension views and cube view */ FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Create attribute filters */ WHERE t.long_description in ('2005', '2006') AND p.package = 'Laptop Value Pack' AND cu.long_description LIKE '%Boston%' AND ch.long_description = 'Internet' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY time, customer;
The query select two calendar years, the products in the Laptop Value Pack, the customers in Boston, and the Internet channel.
TIME PRODUCT CUSTOMER CHANNEL SALES ------ ------------------------------ --------------------- -------- ---------- 2005 Laptop carrying case KOSH Entrpr Boston Internet 5936 2005 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston Internet 45285 2005 Internal 48X CD-ROM KOSH Entrpr Boston Internet 2828 2005 Standard Mouse KOSH Entrpr Boston Internet 638 2005 Envoy Standard Warren Systems Boston Internet 19359 2005 Laptop carrying case Warren Systems Boston Internet 13434 2005 Standard Mouse Warren Systems Boston Internet 130 2006 Standard Mouse KOSH Entrpr Boston Internet 555 2006 Laptop carrying case KOSH Entrpr Boston Internet 6357 2006 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston Internet 38042 2006 Internal 48X CD-ROM KOSH Entrpr Boston Internet 3343 2006 Envoy Standard Warren Systems Boston Internet 24198 2006 Laptop carrying case Warren Systems Boston Internet 13153 2006 Standard Mouse Warren Systems Boston Internet 83 14 rows selected.
A cube contains all of the aggregate data. As shown in this chapter, a query against a cube just needs to select the aggregate data, not calculate the values.
The following is a basic query against a fact table:
/* Querying a fact table */ SELECT t.calendar_year_dsc time, SUM(f.sales) sales FROM time_dim t, units_fact f WHERE t.calendar_year_dsc IN ('2005', '2006') AND t.month_id = f.month_id GROUP BY t.calendar_year_dsc;
The next query fetches the exact same results from a cube using filters:
/* Querying a cube */ SELECT t.long_description time, f.sales sales FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Apply filters to every dimension */ WHERE t.long_description IN ('2005', '2006') AND p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY time;
Both queries return these results:
The query against the cube does not compute the aggregate values with a
SUM operator and
GROUP BY clause. Because the aggregates exist already in the cube, this would re-aggregate previously aggregated data. Instead, the query selects the aggregates directly from the cube and specifies the desired aggregates by applying the appropriate filter to each dimension.
The most efficient queries allow the OLAP engine to filter the data, so that the minimum number of rows required by the query are returned to SQL.
The following are among the
WHERE clause operations that are pushed into the OLAP engine for processing:
The OLAP engine also processes nested character functions, including
RPAD, and
SQL processes other operations and functions in the
WHERE clause, and all operations in other parts of the
SELECT syntax.
Drilling is an important capability in business analysis. In a dashboard or an application, users click on a dimension key to change the selection of data. Decision makers frequently want to drill down to see the contributors to a data value, or drill up to see how a particular data value contributes to the whole. For example, the Boston regional sales manager might start at total Boston sales, drill down to see the contributions of each sales representative, then drill up to see how the Boston region contributes to the New England sales total.
The hierarchy views include a
PARENT column that identifies the parent of every dimension key. This column encapsulates all of the hierarchical information of the dimension: If you know the parent of every key, then you can derive the ancestors, the children, and the descendants.
For level-based hierarchies, the
LEVEL_NAME column supplements this information by providing a convenient way to identify all the keys at the same depth in the hierarchy, from the top to the base. For value-based hierarchies, the
PARENT column provides all the information about the hierarchy.
See Also:Chapter 6, "Developing Reports and Dashboards" about using bind variables to support drilling
You can use the
PARENT column of a hierarchy view to select only the children of a particular value. The following
WHERE clause selects the children of calendar year
/* Select children of calendar year 2005 */ WHERE t.parent = 'CY2005' AND p.dim_key = 'TOTAL' AND cu.dim_key = 'TOTAL' AND ch.dim_key = 'TOTAL'
The query drills down from Year to Quarter. The four quarters
Q1-05 to
Q4-05 are thJ{��e children of year
CY2005 in the Calendar hierarchy.
PARENT column of a hierarchy view identifies the parent of each dimension key. Columns of level keys identify the full heritage. The following
WHERE clause selects the parent of a Time key based on its
LONG_DESCRIPTION attribute.
/* Select the parent of a Time key*/ WHERE t.dim_key = (SELECT DISTINCT parent FROM time_calendar_view WHERE long_description='JAN-05') AND p.dim_key= 'TOTAL' AND cu.dim_key = 'TOTAL' AND ch.dim_key = 'TOTAL'
The query drills up from Month to Quarter. The parent of month
JAN-05 is the quarter
Q1-05 in the Calendar hierarchy.
The following
WHERE clause selects the descendants of calendar year
2005 by selecting the rows with a
LEVEL_NAME of
MONTH and a
CALENDAR_YEAR of
/* Select Time level and ancestor */ WHERE t.level_name = 'MONTH' AND t.calendar_year = 'CY2005' AND p.dim_key = 'TOTAL' AND cu.dim_key = 'TOTAL' AND ch.dim_key = 'TOTAL'
The query drills down two levels, from year to quarter to month. The 12 months
Jan-05 to
Dec-05 are the descendants of year
2005 in the Calendar hierarchy.
The hierarchy views provide the full ancestry of each dimension key, as shown in "Displaying the Contents of a Hierarchy View". The following
WHERE clause uses the
CALENDAR_YEAR level key column to identify the ancestor of a
MONTH dimension key.
/* Select the ancestor of a Time key based on its Long Description attribute */ WHERE t.dim_key = (SELECT calendar_year FROM time_calendar_view WHERE long_description = 'JAN-05') AND p.dim_key = 'TOTAL' AND cu.dim_key = 'TOTAL' AND ch.dim_key = 'TOTAL'
The query drills up two levels from month to quarter to year. The ancestor of month
Jan-05 is the year
2005 in the Calendar hierarchy.
A DBA can create calculated measures in Analytic Workspace Manager, so they are available to all applications. This not only simplifies application development, but ensures that all applications use the same name for the same calculation.
Nonetheless, you may want to develop queries that include your own calculations. In this case, you can use an inner query to select aggregate data from the cube, then perform calculations in an outer query. You can select data from cubes that use any type of aggregation operators, and you can use any functions or operators in the query. You only need to make sure that you select the data from the cube at the appropriate levels for the calculation, and that the combination of operators in the cube and in the query create the calculation you want.
Example 4-4 shows a query that answers the question, What was the average sales of Sentinel Standard computers to Government customers for the third quarter of fiscal year 2005.
UNITS_CUBE is summed over all dimensions, so that
FY2005.Q3 is a total for July, August, and September. The inner query extracts the data for these months, and the outer query uses the
MAX, and
AVG operator s and a
GROUP BY clause to calculate the averages.
Example 4-4 Calculating Average Sales Across Customers
SELECT customer, ROUND(MIN(sales)) minimum, ROUND(MAX(sales)) maximum, ROUND(AVG(sales)) average FROM (SELECT cu.long_description customer, f.sales sales FROM time_fiscal_view t, product_primary_view p, customer_segment_view cu, channel_primary_view ch, units_cube_view f WHERE t.parent = 'FY2005.Q3' AND p.dim_key = 'SENT STD' AND cu.parent = 'GOV' AND ch.level_name = 'TOTAL' AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ) GROUP BY customer ORDER BY customer;
This is the data extracted from the cube by the inner query:
CUSTOMER TIME SALES ---------------------------------------- -------- ---------- Dept. of Labor JAN-05 1553.26 Dept. of Labor MAR-05 1555.6 Ministry of Intl Trade JAN-05 1553.26 Ministry of Intl Trade FEB-05 1554.56 Ministry of Intl Trade MAR-05 1555.6 Royal Air Force JAN-05 1553.26 Royal Air Force FEB-05 6218.23 UK Environmental Department JAN-05 4659.78 UK Environmental Department FEB-05 3109.12
The outer query calculates the minimum, maximum, and average sales for each customer:
CUSTOMER MINIMUM MAXIMUM AVERAGE ------------------------------ ---------- ---------- ---------- Dept. of Labor 1553 1556 1554 Ministry of Intl Trade 1553 1556 1554 Royal Air Force 1553 6218 3886 UK Environmental Department 3109 4660 3884
An OLAP cube aggregates the data within its hierarchies, using the parent-child relationships revealed in the hierarchy views. The OLAP engine does not calculate aggregates over dimension attribute values.
Nonetheless, you may want to aggregate products over color or size, or customers by age, zip code, or population density. This is the situation when you can use a
GROUP BY clause when querying a cube. Your query can extract data from the cube, then use SQL to aggregate by attribute value.
The cube must use the same aggregation operator for all dimensions, and the aggregation operator in the
SELECT list of the query must match the aggregation operator of the cube. You can use a
GROUP BY clause to query cubes that use these operators:
First Non-NA Value
Last Non-NA Value
Example 4-5 shows a query that aggregates over an attribute named Package. It returns these results:
TIME PACKAGE SALES ------ ------------------ ---------- 2005 All 1809157.64 2005 Multimedia 18083256.3 2005 Executive 19836977 2005 Laptop Value Pack 9547494.81
Units Cube uses the
SUM operator for all dimensions, and the query uses the
SUM operator to aggregate over Sales. The Package attribute applies only to the Item level of the Product dimension, so the query selects the Item level of Product. It also eliminates nulls for Package, so that only products that belong to a package are included in the calculation. The
GROUP BY clause breaks out Total Sales by Time and Package
Example 4-5 Aggregating Over an Attribute
SELECT t.long_description time, p.package package, SUM(f.sales) sales FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Select Product by level and attribute */ WHERE p.level_name = 'ITEM' AND p.package IS NOT NULL AND t.long_description = '2005' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimensions and cube */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel GROUP BY t.long_description, p.package;
Before using the technique described in "Aggregating Measures Over Attributes", be sure that the calculation is meaningful. For example, the common calculation Percent Change might be defined as a calculated measure in a cube. Summing over Percent Change would produce unexpected results, because the calculation for Percent Change (
(a-b)/b,) is not additive.
Consider the following rows of data. The correct Total Percent Change is
.33, whereas the sum of the percent change for the first two rows is
|Sales Prior Period
|Percent Change
Example 4-6 shows a query that aggregates over the Package attribute and calculates Percent Change From Prior Period. The inner query aggregates Sales and Sales Prior Period over the attributes, and the outer query uses the results to compute the percent change. These are the results of the query, which show the expected results for
TIME PACKAGE SALES PRIOR_PERIOD PCT_CHG_PP ------ ------------------ ---------- ------------ ---------- 2005 All 1809157.64 1853928.06 -.02414895 2006 All 1720399.03 1809157.64 -.04906074 2005 Executive 19836977 20603879.8 -.03722128 2006 Executive 19580638.4 19836977 -.01292226 2005 Laptop Value Pack 9547494.81 10047298.6 -.04974509 2006 Laptop Value Pack 9091450.58 9547494.81 -.04776585 2005 Multimedia 18083256.3 19607675.5 -.07774604 2006 Multimedia 18328678.7 18083256.3 .013571806 8 rows selected.
Example 4-6 Querying Over Attributes Using Calculated Measures
/* Calculate Percent Change */ SELECT TIME, package, sales, prior_period, ((sales - prior_period) / prior_period) pct_chg_pp FROM /* Fetch data from the cube and aggregate over Package */ (SELECT t.long_description time, p.package package, SUM(f.sales) sales, SUM(f.sales_pp) prior_period FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f /* Create filters */ WHERE p.level_name = 'ITEM' AND p.package IS NOT NULL AND t.long_description IN ('2005', '2006') AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' /* Join dimension views to cube view */ AND t.dim_key = f.time AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel GROUP BY t.long_description, p.package ORDER BY p.package);
You can generate and view execution plans for queries against cubes and dimensions the same as for those against relational tables.
The SQL
EXPLAIN PLAN command creates a table with the content of the explain plan. The default table name is
The following command creates an execution plan for a basic query on a cube:
EXPLAIN PLAN FOR SELECT t.long_description time, p.long_description product, cu.long_description customer, ch.long_description channel, f.sales sales FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f WHERE t.level_name = 'CALENDAR_YEAR' AND p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY t.end_date;
Example 4-7 shows selected columns of the execution plan. A
CUBE SCAN operation is performed. The plan option is
PARTIAL OUTER, which is described in "Types of Execution Plans".
Example 4-7 Selected Columns From PLAN_TABLE
SQL> SELECT operation, options, object_name FROM plan_table; OPERATION OPTIONS OBJECT_NAME -------------------- -------------------- --------------- SELECT STATEMENT SORT ORDER BY JOINED CUBE SCAN PARTIAL OUTER CUBE ACCESS UNITS_CUBE CUBE ACCESS CHANNEL CUBE ACCESS CUSTOMER CUBE ACCESS PRODUCT CUBE ACCESS TIME 8 rows selected.
DISPLAY table function of the
DBMS_XPLAN PL/SQL package formats and displays information from an execution plan, as shown in Example 4-8.
Example 4-8 Formatted Execution Plan From DBMS_XPLAN
SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Plan hash value: 1667678335 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 100 | 104 (3)| 00:00:02 | | 1 | SORT ORDER BY | | 1 | 100 | 104 (3)| 00:00:02 | | 2 | JOINED CUBE SCAN PARTIAL OUTER| | | | | | | 3 | CUBE ACCESS | UNITS_CUBE | | | | | | 4 | CUBE ACCESS | CHANNEL | | | | | | 5 | CUBE ACCESS | CUSTOMER | | | | | | 6 | CUBE ACCESS | PRODUCT | | | | | |* 7 | CUBE ACCESS | TIME | 1 | 100 | 103 (2)| 00:00:02 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter(SYS_OP_ATG(VALUE(KOKBF$),12,13,2)='CALENDAR_YEAR' AND SYS_OP_ATG(VALUE(KOKBF$),43,44,2)='TOTAL' AND SYS_OP_ATG(VALUE(KOKBF$),33,34,2)='TOTAL' AND SYS_OP_ATG(VALUE(KOKBF$),23,24,2)='TOTAL') 22 rows selected.
Table 4-1 describes the types of execution plans for cubes.
Table 4-1 Descriptions of Execution Plans for Cubes and Dimensions
Uses an outer join for least one dimension, and inner joins for the other dimensions.
Uses outer joins for all cube access.
If you are developing a generic application -- that is, one where the names of the dimensional objects are not known -- then your application can retrieve this information from the data dictionary.
Among the static views of the database data dictionary are those that provide information about dimensional objects. All OLAP metadata is stored in the data dictionary. A few of the data dictionary views were introduced previously in this chapter.
Table 4-2 provides brief descriptions of the
ALL views. There are corresponding
DBA and
USER views.
Table 4-2 Static Data Dictionary Views for OLAP
Describes the visibility of the attributes for cube dimensions.
Describes the attributes for cube dimensions.
Describes the cube build processes and maintenance scripts.
Describes the calculated members (keys) for cube dimensions.
Describes the cube dimension levels.
Describes the models for cube dimensions.
Describes the columns of the system-generated relational views of cube dimensions.
Describes the system-generated relational views of OLAP dimensions.
Describes the dimension order of the OLAP cubes.
Describes the cube dimensions.
Describes the hierarchy levels for cube dimensions.
Describes the columns of relational hierarchy views of cube dimensions.
Describes the hierarchies for cube dimensions.
Describes the OLAP dimension hierarchies.
Describes the measures in the OLAP cubes.
Describes the columns of the relational views of OLAP cubes.
Describes the system-generated relational views of OLAP cubes.
Describes the OLAP cubes.
Describes the contents of OLAP measure folders.
Describes the OLAP measure folders.
See Also:Oracle Database Reference for full descriptions of data dictionary views.
This chapter describes the preliminary steps you need to take to use Oracle OLAP. It assumes that you have already installed Oracle Database 11g Enterprise Edition. The OLAP option is installed automatically as part of a Basic installation of Oracle Database.
Note:To start querying dimensional objects immediately, install the Global analytic workspace, as described in "Installing the Sample Schema". Then follow the instructions in Chapter 4.
This chapter includes the following topics:
You can download and install the sample Global schema from the Oracle Web site and use it to try the examples shown throughout this guide:
Instructions for installing the schema are provided in the
README file.
You should create undo, permanent, and temporary tablespaces that are appropriate for use by dimensional objects. Follow the recommendations in "Storage Management".
Anyone who needs to create or manage dimensional objects in Oracle Database must have the necessary privileges. These privileges are different from those needed just to query the data stored in dimensional objects. The security system is discussed in Chapter 8.
DBAs and application developers need the following roles and privileges.
To create dimensional objects in the user's own schema:
To create dimensional objects in different schemas:
To administer data security:
To create cube materialized views in the user's own schema:
To create cube materialized views in different schemas:
Users also need an unlimited quota on the tablespace in which the dimensional objects will be stored. The tablespaces should be defined specifically for OLAP use, as described in Chapter 7.
If the source tables are in a different schema, then the owner of the dimensional objects needs
SELECT object privileges on those tables.
Example 2-1 shows the SQL statements for creating the
GLOBAL user.
In this section, you will learn how to install Analytic Workspace Manager software and make a connection to Oracle Database.
Analytic Workspace Manager is distributed on the Oracle Database Client installation disk.
If you are installing on the same system as the database, then choose a Custom installation and install into the same Oracle home directory as the database. Select OLAP Analytic Workspace Manager and Worksheet from the list of components.
If you are installing on a remote system, then choose either an Administrator or a Custom installation. The Administrator choice automatically installs Analytic Workspace Manager on the client.
See Also:An installation guide for your client platform, such as the Oracle Database Client Quick Installation Guide for Microsoft Windows (32-Bit).
On Windows, to open Analytic Workspace Manager:
From the Start menu, choose Oracle - Oracle_home, then Integrated Management Tools, and then OLAP Analytic Workspace Manager and Worksheet.
On Linux, to open Analytic Workspace Manager:
From the shell command line, enter this command:
Figure 2-1 shows the initial display.
You can define a connection to each database that you use for OLAP. After you define a connection, the database instance is listed in the navigation tree for you to access at any time.
To define a database connection:
Right-click the top Databases folder in the navigation tree, then choose Add Database to Tree from the pop-up menu.
Complete the Add Database to Tree dialog box.
Figure 2-2 shows the connection information on the General tab of the Add Database to Tree dialog box.
Click the plus icon (+) next to a database in the navigation tree.
Supply your database user name and password in the Connect to Database dialog box.
Plugins extend the functionality of Analytic Workspace Manager. Any Java developer can create a plugin. Plugins are distributed as JAR files. The developer should provide information about what the plugin does and how to use it.
If you have one or more plugins, then you only need to identify their location to Analytic Workspace Manager.
To use plugins:
Create a local directory for storing plugins for Analytic Workspace Manager.
Copy the JAR files to that directory.
Open Analytic Workspace Manager.
Choose Configuration from the Tools menu.
The Configuration dialog box opens.
Select Enable Plugins and identify the plugin directory. Click OK.
Close and reopen Analytic Workspace Manager.
The new functionality provided by the plugins is available in the navigator.
See Also:Developing Analytic Workspace Manager Plug-ins, which you can download from the Oracle Technology Network at
Because Oracle OLAP is contained in the database and its resources are managed using the same tools, the management tasks of Oracle OLAP and the database converge. Nonetheless, you should address tasks such as database tuning in the specific context of data warehousing.
This chapter contains the following topics:
Table 7-1 identifies the parameters that affect the performance of Oracle OLAP. Alter your server parameter file or
init.ora file to these values, then restart your database instance. You can monitor the effectiveness of these settings and adjust them as necessary.
See Also:
Table 7-1 Initial Settings for Database Parameters
|Default Value
|Recommended Setting
Number of CPUs, plus one additional process for every three CPUs; in a multi-core CPU, each core counts as a CPU
For example,
Controls the degree of parallelism in OLAP builds, as described in "Parallelism"
2.5 * maximum number of simultaneous OLAP users
Provides sufficient background processes for each user
Specifies use of an undo tablespace
Name of the undo tablespace, which must already be defined
Identifies the undo tablespace defined for OLAP use, as shown in "Creating an Undo Tablespace"
To set the system parameters:
Open the
init.ora initialization file in a text editor.
Add or change the settings in the file, as described in Table 7-1.
Stop and restart the database.
On Windows, use the Services utility to stop and restart
On Linux, use commands like the following. Be sure to identify the initialization file in the
STARTUP command.
SQLPLUS '/ AS SYSDBA' SHUTDOWN IMMEDIATE STARTUP pfile=$ORACLE_BASE/admin/orcl/pfile/init.ora.724200516420
Analytic workspaces are stored in the owner's default tablespace, unless the owner specifies otherwise. All tablespaces for OLAP use should specify
EXTENT MANAGEMENT LOCAL. Tablespaces created using default parameters may use resources inefficiently. You should create undo, permanent, and temporary tablespaces that are appropriate for storing analytic workspaces.
Create an undo tablespace with the
EXTENT MANAGEMENT LOCAL clause, as shown in this example:
CREATE UNDO TABLESPACE olapundo DATAFILE '$ORACLE_BASE/oradata/undo.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
After creating the undo tablespace, change your system parameter file to include the following settings, then restart the database as described in "Setting Database Initialization Parameters".
Each dimensional object occupies at least one extent. A fixed extent size may waste most of the allocated space. For example, if an object is 64K and the extents are set to a uniform size of 1M (the default), then only a small portion of the extent is used.
Create permanent tablespaces with the
EXTENT MANAGEMENT LOCAL and
SEGMENT SPACE MANAGEMENT AUTO clauses, as shown in this example:
CREATE TABLESPACE glo DATAFILE '$ORACLE_BASE/oradata/glo.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Oracle OLAP uses the temporary tablespace to store all changes to the data in a cube, whether the changes are the result of a data load or data analysis. Saving the cube moves the changes into the permanent tablespace and clears the temporary tablespace.
This usage creates numerous extents within the tablespace. A temporary tablespace suitable for use by Oracle OLAP should specify the
EXTENT MANAGEMENT LOCAL clause and a
UNIFORM SIZE clause with a small size, as shown in this example:
CREATE TEMPORARY TABLESPACE glotmp TEMPFILE '$ORACLE_BASE/oradata/glotmp.tmp' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
Oracle Database provides excellent storage management tools to simplify routine tasks. Automatic Storage Management (ASM) provides a simple storage management interface that virtualizes database storage into disk groups. You can manage a small set of disk groups, and ASM automates the placement of the database files within those disk groups.
ASM spreads data evenly across all available storage resources to optimize performance and utilization. After you add or drop disks, ASM automatically rebalances files across the disk group.
Because OLAP is part of Oracle Database, you can use ASM to manage both relational and dimensional data.
ASM is highly recommended for analytic workspaces. A system managed with ASM is faster than a file system and easier to manage than raw devices. ASM optimizes the performance of analytic workspaces both on systems with RAC and those without RAC.
However, you do not need ASM to use Oracle OLAP. You can still spread your data across multiple disks, just by defining the tablespaces like in this example:
CREATE TABLESPACE glo DATAFILE 'disk1/oradata/glo1.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ALTER TABLESPACE glo ADD DATAFILE 'disk2/oradata/glo2.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1024M, 'disk3/oradata/glo3.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED;
Oracle Database data dictionary views and system tables contain extensive information about analytic workspaces.
Among the static views of the database data dictionary are several that provide information about analytic workspaces. Table 7-2 provides brief descriptions of them. All data dictionary views have corresponding
DBA and
USER views.
Table 7-2 Static Data Dictionary Views for OLAP
Describes all analytic workspaces accessible to the current user.
Describes the current objects in all analytic workspaces accessible to the current user.
Describes the properties defined in all analytic workspaces accessible to the current user.
Describes the page spaces currently in use by all analytic workspaces accessible to the current user.
See Also:
SYS user owns several tables associated with analytic workspaces. Table 7-3 provides brief descriptions.
Important:These tables are vital for the operation of Oracle OLAP. Do not delete them or attempt to modify them directly without being fully aware of the consequences.
Table 7-3 OLAP Tables Owned By SYS
Maintains a record of all analytic workspaces in the database, recording its name, owner, and other information.
Stores the
Stores the
Stores the
Stores the
Stores the
Stores the
Describes the objects stored in analytic workspaces.
Stores program data. Not currently used.
Stores analytic workspace object properties.
Stores tracking data about access to aggregate cells. Not currently used.
Maintains a history of all page spaces. A page space is an ordered series of bytes equivalent to a file. Oracle OLAP manages a cache of workspace pages. Pages are read from storage in a table and written into the cache in response to a query. The same page can be accessed by several sessions.
The information stored in
Analytic workspaces are stored in tables in the Oracle database. The names of these tables always begin with
For example, if the
GLOBAL user creates two analytic workspaces, one named
FINANCIALS and the other named
MARKETING, then these tables will be created in the
GLOBAL schema:
The tables store all of the object definitions and data.
The first time you load data into a cube or dimension using Analytic Workspace Manager, a table named
CUBE_BUILD_LOG is created in your schema. This table stores information about the build. Information about each subsequent build is added to the table and is identified by its own build identifier.
CUBE_BUILD_LOG is populated whenever a cube is refreshed, whether by Analytic Workspace Manager, the database materialized view refresh subsystem, or a PL/SQL procedure. It is updated in real time during the build, so that you can check its status at any time by querying the table in SQL.
The Maintenance Wizard in Analytic Workspace Manager displays the relevant rows from this table at the end of every build on the Log page.
Cubes are often partitioned to improve build and maintenance times. For information about creating a partitioned cube, refer to "Choosing a Partitioning Strategy".
To discover the current partitioning, query the
ALL_CUBES data dictionary view. The
PARTITION_HIERARCHY_NAME, and
PARTITION_LEVEL_NAME columns display partitioning information. For example, the following query shows that the Units Cube is partitioned on the Time dimension, the Calendar hierarchy, and the Calendar Year level.
SELECT partition_dimension_name, partition_hierarchy_name, partition_level_name FROM all_cubes WHERE owner='GLOBAL' AND cube_name='UNITS_CUBE'; PARTITION_DIMENSION_NAME PARTITION_HIERARCHY_NAME PARTITION_LEVEL_NAME ------------------------- ------------------------- -------------------- TIME CALENDAR CALENDAR_YEAR
The OLAP engine automatically creates and drops partitions as part of data maintenance, as members are added and deleted from the partitioning dimension.
For example, assume that in the sample Global analytic workspace, the Units cube is partitioned on the Time dimension, using the Calendar hierarchy, and at the Calendar Quarter level. The OLAP engine creates a partition for each Calendar Quarter and its children. The default top partition contains Calendar Years and all members of the Fiscal hierarchy. If Global has three years of data, then the Units cube has 13 partitions: Four bottom partitions for each Calendar Year, plus the top partition.
A data refresh typically creates new time periods and deletes old ones. Whenever a Calendar Quarter value is loaded into the Time dimension, a corresponding new partition is added to the cube. Whenever a Calendar Quarter value is deleted from the Time dimension, the corresponding empty partition is deleted from the cube.
You can improve the performance of data maintenance by enabling parallel processing. There are two levels of parallelism:
Parallel job execution: Loading and aggregating the data using multiple processes.
Parallel update: Moving the data from temporary to permanent tablespaces using multiple processes.
This number of parallel processes is controlled by these factors:
The number of objects that can be aggregated in parallel. Each cube and each partition (including the top partition) can use a separate process.
You can control the number of partitions in a cube on the Partitioning tab of the cube property sheet in Analytic Workspace Manager.
The number of simultaneous database processes the user is authorized to run.
This number is controlled by the
JOB_QUEUE_PROCESSES parameter. The setting for this parameter is based on the number of processors, as described in "Setting Database Initialization Parameters". If you have
SYS privileges, you can obtain the current parameter setting with the following SQL command:
For parallel update, the number of processes you allocate to the job. You can specify the number of processes in the Maintenance Wizard of Analytic Workspace Manager when specifying the task processing options, or on the Materialized View tab of the cube.
Suppose that a cube is partitioned on the Quarter level of Time, and the cube contains three years of data. The cube has 3*4=12 bottom partitions,
JOB_QUEUE_PROCESSES is set to 8, and you set the parallelism option to 4 for the build. Oracle Database will process the cube in this way:
Load and build the dimensions of the cube serially using a single process.
Load and build the 12 bottom partitions in parallel using 4 processes. As soon as one process finishes, another begins until all 12 are complete.
This cube could use the 8 processes allowed by
JOB_QUEUE_PROCESSES, but it is limited to 4 by the build setting.
Load and build the top partition.
Example 7-1 shows excerpts from
CUBE_BUILD_LOG for a build of the Units cube and its dimensions. Partitioning on the Calendar Year level of the Time dimension created 10 bottom partitions for 1998 to 2007.
JOB_QUEUE_PROCESSES is set to 2 and the parallelism option is set to 2 for the build also. The log shows that Oracle Database processed the Global in this way:
Processed the four dimensions serially.
Processed each partition of the Units cube
Example 7-1 Build Log for the Global Analytic Workspace
SELECT slave_number, status, command, build_object, partition FROM cube_build_log WHERE build_id='4'; SLAVE_NUMBER STATUS COMMAND BUILD_OBJECT PARTITION ------------ ---------- ------------------------- ------------ ---------- STARTED BUILD STARTED FREEZE COMPLETED FREEZE STARTED LOAD NO SYNCH TIME COMPLETED LOAD NO SYNCH TIME STARTED COMPILE TIME COMPLETED COMPILE TIME STARTED UPDATE TIME COMPLETED UPDATE TIME STARTED LOAD NO SYNCH CHANNEL COMPLETED LOAD NO SYNCH CHANNEL STARTED COMPILE CHANNEL COMPLETED COMPILE CHANNEL STARTED UPDATE CHANNEL COMPLETED UPDATE CHANNEL STARTED LOAD NO SYNCH CUSTOMER COMPLETED LOAD NO SYNCH CUSTOMER STARTED COMPILE CUSTOMER COMPLETED COMPILE CUSTOMER STARTED UPDATE CUSTOMER COMPLETED UPDATE CUSTOMER STARTED LOAD NO SYNCH PRODUCT COMPLETED LOAD NO SYNCH PRODUCT STARTED COMPILE PRODUCT COMPLETED COMPILE PRODUCT STARTED UPDATE PRODUCT COMPLETED UPDATE PRODUCT STARTED COMPILE AGGMAP UNITS_CUBE COMPLETED COMPILE AGGMAP UNITS_CUBE STARTED UPDATE UNITS_CUBE COMPLETED UPDATE UNITS_CUBE STARTED DBMS_SCHEDULER.CREATE_JOB UNITS_CUBE P10 COMPLETED DBMS_SCHEDULER.CREATE_JOB UNITS_CUBE P10 STARTED DBMS_SCHEDULER.CREATE_JOB UNITS_CUBE P9 COMPLETED DBMS_SCHEDULER.CREATE_JOB UNITS_CUBE P9 1 STARTED BUILD P10 1 STARTED LOAD UNITS_CUBE P10 1 COMPLETED LOAD UNITS_CUBE P10 1 STARTED UPDATE UNITS_CUBE P10 1 COMPLETED UPDATE UNITS_CUBE P10 1 COMPLETED BUILD P10 2 STARTED BUILD P9 . . . STARTED DBMS_SCHEDULER.CREATE_JOB UNITS_CUBE P0 COMPLETED DBMS_SCHEDULER.CREATE_JOB UNITS_CUBE P0 11 STARTED BUILD �� P0 11 STARTED LOAD UNITS_CUBE P0 11 COMPLETED LOAD UNITS_CUBE P0 11 STARTED SOLVE UNITS_CUBE P0 11 COMPLETED SOLVE UNITS_CUBE P0 11 STARTED UPDATE UNITS_CUBE P0 11 COMPLETED UPDATE UNITS_CUBE P0 11 COMPLETED BUILD P0 STARTED ANALYZE UNITS_CUBE COMPLETED ANALYZE UNITS_CUBE STARTED THAW COMPLETED THAW COMPLETED BUILD 144 rows selected.
Oracle Database allocates the specified number of processes regardless of whether all of them can be used simultaneously at any point in the job. For example, if your job can use up to three processes, but you specify five, then two of the processes allocated to your job cannot be used by it or by any other job.
If Oracle Database is installed with Real Application Clusters (RAC), then a script submitted to the job queue will be distributed across all nodes in the cluster. The performance gains can be significant. For example, a job running on four nodes in a cluster may run up to four times faster than the same job running on a single computer.
If your application executes queries directly against a single cube, you do not need to generate optimizer statistics for the cube. These queries are automatically optimized within the analytic workspace.
Optimizer statistics are used to create execution plans for queries that join two cube views or join a cube view to a table or a view of a table. They are also used for cost-based rewrite to cube materialized views. You need to generate the statistics only for these types of queries.
To generate optimizer statistics, use the
DBMS_AW_STATS PL/SQL package. You can run this package in Analytic Workspace Manager as part of a cube script, in SQL*Plus, or in any other SQL interface. Generating the statistics does not have a significant performance cost.
DBMS_AW_STATS has the following syntax:
DBMS_AW_STATS.ANALYZE (object IN VARCHAR2);
The argument can be either a cube or a dimension. Example 7-2 shows a sample script for generating statistics on the Units cube and its dimensions.
Example 7-2 Generating Statistics for the Units Cube
BEGIN DBMS_AW_STATS.ANALYZE('units_cube'); DBMS_AW_STATS.ANALYZE('time'); DBMS_AW_STATS.ANALYZE('customer'); DBMS_AW_STATS.ANALYZE('product'); DBMS_AW_STATS.ANALYZE('channel'); END; /
Although you cannot view the statistics directly, you can examine the execution plans, as described in "Viewing Execution Plans".
See Also:Oracle Database Performance Tuning Guide
Oracle Database provides various tools to help you diagnose performance problems. As an Oracle DBA, you will find these tools useful in tuning the database:
Oracle Enterprise Manager Database Control (Database Control) is a general database management and administration tool. In addition to facilitating basic tasks like adding users and modifying datafiles, Database Control presents a graphic overview of a database's current status. It also provides an interface to troubleshooting and performance tuning utilities.
Automatic Workload Repository collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and saves session information.
Automatic Database Diagnostic Monitor watches database performance statistics to identify bottlenecks, analyze SQL statements, and offer suggestions to improve performance.
Oracle Database also provides system views to help you diagnose performance problems. The following topics identify views that are either specific to OLAP or provide database information that is pertinent to OLAP.
Each Oracle Database instance maintains fixed tables that record current database activity. These tables collect data on internal disk structures and memory structures. Among them are tables that collect data on Oracle OLAP.
These tables are available to users through a set of dynamic performance views. By monitoring these views, you can detect usage trends and diagnose system bottlenecks. Table 7-4 provides a brief description of each view. Global dynamic performance views (
GV$) are also provided.
See Also:Oracle Database Reference for full descriptions of the OLAP dynamic performance views.
Table 7-4 OLAP Dynamic Performance Views
Lists the aggregation operators available in analytic workspaces.
Lists the allocation operators available in analytic workspaces.
Collects information about the use of cache space and the status of dynamic aggregation.
Collects status information about SQL fetches.
Collects information about each active session.
Collects information about the status of active analytic workspaces.
Table 7-5 describes some other dynamic performance views that are not specific to OLAP, but which you may want to use when tuning your database for OLAP.
Table 7-5 Selected Database Performance Views
Displays log file information from the control file.
Contains information about redo log files.
Provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when
Displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.
Lists system statistics.
The following queries extract OLAP information from the data dictionary. You must have
More complex queries are provided in a script that you can download from the Oracle OLAP web site on the Oracle Technology Network. For descriptions of these scripts and download instructions, refer to "OLAP DBA Scripts".
The OLAP option is provided with Oracle Database Enterprise Edition. To verify that the OLAP components have been installed, issue this SQL command:
SELECT comp_name, version, status FROM DBA_REGISTRY WHERE comp_name LIKE '%OLAP%'; COMP_NAME VERSION STATUS ------------------------ ------------------------------ ----------- OLAP Analytic Workspace 126.96.36.199.0 VALID Oracle OLAP API 18.104.22.168.0 VALID OLAP Catalog 22.214.171.124.0 VALID
DBA_AWS view provides information about all analytic workspaces. Use the following SQL command to get a list of names, their owners, and the version:
SELECT owner, aw_name, aw_version FROM DBA_AWS; OWNER AW_NAME AW_VERSION ---------- ------------------------------ ---------- SYS EXPRESS 11.1 GLOBAL GLOBAL 11.1 SYS AWCREATE 11.1 SH SH 11.1 SYS AWMD 11.1 SYS AWXML 11.1 SYS AWREPORT 11.1 SYS AWCREATE10G 11.1
See Also:"System Tables" for descriptions of the analytic workspaces owned by
To find out the size in bytes of the tablespace extents for a particular analytic workspace, use the following SQL statements, replacing
GLOBAL with the name of your analytic workspace.
SELECT extnum, SUM(dbms_lob.getlength(awlob)) bytes FROM global.aw$global GROUP BY extnum; EXTNUM BYTES ---------- ---------- 0 191776956
To see the size of the LOB table containing an analytic workspace, use a SQL command like the following, replacing
GLOBAL.AW$GLOBAL with the qualified name of your analytic workspace.
SELECT ROUND(SUM(dbms_lob.getlength(awlob))/1024,0) kb FROM global.aw$global; KB ---------- 187282
DBA_OBJECTS view provides the creation date of the objects in your database. The following SQL command generates an easily readable report for analytic workspaces.
SELECT owner, object_name, created, status FROM dba_objects WHERE object_name LIKE 'AW$%' AND object_name!='AW$' GROUP BY owner, object_name, created, status ORDER BY owner, object_name; OWNER OBJECT_NAME CREATED STATUS ---------- --------------- --------- ------- GLOBAL AW$GLOBAL 05-JUL-07 VALID SYS AW$AWCREATE 30-JUN-07 VALID SYS AW$AWCREATE10G 30-JUN-07 VALID SYS AW$AWMD 30-JUN-07 VALID SYS AW$AWREPORT 30-JUN-07 VALID SYS AW$AWXML 30-JUN-07 VALID SYS AW$EXPRESS 30-JUN-07 VALID 7 rows selected.
You can download a file that contains several SQL scripts from the Oracle OLAP web site on the Oracle Technology Network. These scripts typically extract information from two or more system views and generate a report that may be useful in monitoring and tuning a database. To download the file, use this URL:
Table 7-6 describes these scripts. For more information, refer to the
README file provided with the scripts.
Table 7-6 OLAP DBA Scripts
|SQL Script
Identifies the objects in the buffer cache that are related to analytic workspaces.
Tallies the reads from temporary and permanent tablespaces, the writes to cache, and the rows processed in analytic workspaces.
Displays the amount of disk space used by each analytic workspace.
Provides extensive information about the tablespaces used by analytic workspaces.
Identifies the users of analytic workspaces.
Describes the wait events experienced by users of analytic workspaces over the previous hour.
Calculates the buffer cache hit ratio.
Indicates whether the database parameters that limit the number of open cursors are set too low.
Determines how much PGA is in use, the size of the OLAP page pool, and the hit/miss ratio for OLAP pages for each user.
Determines how much PGA is consumed by the OLAP page pool to perform operations on analytic workspaces.
Identifies the use of cursors, PGA, and UGA for each open session.
Calculates the shared pool hit ratio.
Several of the scripts listed in "OLAP DBA Scripts" provide detailed information about the use of memory and other database resources by OLAP sessions. You can use these scripts as is, or you can use them as the starting point for developing your own scripts.
Example 7-3 shows the information returned by the session_resources script. It lists the use of resources such as cursors, PGA, and UGA.
Example 7-3 Querying Session Resources
@session_resources USERNAME NAME VALUE -------------------- ------------------------------ ---------- GLOBAL:86 opened cursors cumulative 621 opened cursors current 18 session cursor cache count 50 session cursor cache hits 432 session pga memory 5356368 session pga memory max 10468176 session stored procedure space 0 session uga memory 4230692 session uga memory max 7049780 9 rows selected.
Several of the scripts listed in "OLAP DBA Scripts" provide detailed information about the use of disk space by analytic workspaces. Example 7-4 shows the information returned by the
aw_size script. It lists all of the analytic workspaces in the database, the disk space they consume, and the tablespaces in which they are stored.
Example 7-4 Querying the Use of Disk Space By Analytic Workspaces
@aw_size Analytic Workspace On Disk MB Tablespace ---------------------------------------- --------------- -------------------- GLOBAL.GLOBAL 249.31 GLOBAL SYS.AWCREATE 7.81 SYSAUX SYS.AWCREATE10G 1.63 SYSAUX SYS.AWMD 8.00 SYSAUX SYS.AWREPORT 1.63 SYSAUX SYS.AWXML 18.00 SYSAUX SYS.EXPRESS 2.25 SYSAUX --------------- Total Disk: 288.63 7 rows selected.
You can backup and recover analytic workspaces using the same tools and procedures as the rest of your database.
Oracle Recovery Manager (RMAN) is a powerful tool that simplifies, automates, and improves the performance of backup and recovery operations. RMAN enables one time backup configuration, automatic management of backups, and archived logs based on a user-specified recovery window, restartable backups and restores, and test restore/recovery.
RMAN implements a recovery window to control when backups expire. This lets you establish a period of time during which it is possible to discover logical errors and fix the affected objects by doing a database or tablespace point-in-time recovery. RMAN also automatically expires backups that are no longer required to restore the database to a point-in-time within the recovery window. Control file auto backup also allows for restoring or recovering a database, even when an RMAN repository is not available.
You can copy analytic workspaces in several different ways, either to replicate them on another computer or to back them up.
Data Pump. Analytic workspaces are copied with the other objects in a schema or database export. Use the
impdp database utilities.
Transportable Tablespaces. Analytic workspaces are copied with the other objects to a transportable tablespace. However, you can only transport the tablespace to the same platform (for example, from Linux to Linux, Solaris to Solaris, or Windows to Windows) because the OLAP
DECIMAL data type is hardware dependent. Use the
impdp database utilities. Transportable tablespaces are much faster than dump files.
XML Templates. A template saves the XML definition of objects in an analytic workspace. You can save the entire analytic workspace, or individual cubes, dimensions, and calculated measures. Using a saved template, you can create a new analytic workspace exactly like an existing one. The template does not save any data, nor does it save any customizations to the analytic workspace. You can copy a template to a different platform.
The owner of an analytic workspace can create an XML template, or export the schema to a dump file. Only users with the
EXP_FULL_DATABASE privilege or a privileged user (such as
SYS or a user with the
DBA role) can export the full database or create a transportable tablespace.
See Also:
A cube materialized view is an Oracle OLAP cube that has been enhanced with the capabilities of a materialized view at build time.
The data dictionary contains numerous static views that provide information about materialized views. They list cube materialized views along with all other materialized views. The
ALL_MVIEW_DETAIL_SUBPARTITION view is the only exception, with no information about cube materialized views.
See Also:Oracle Database Reference for complete descriptions of the data dictionary views
USER_MVIEWS contains a row for each materialized view owned by the current user. The following query lists the materialized views owned by the
GLOBAL user. The
CB$ prefix identifies a cube materialized view.
SELECT mview_name, refresh_mode "MODE", refresh_method "METHOD", last_refresh_date "DATE", staleness FROM user_mviews; MVIEW_NAME MODE METHOD DATE STALENESS ------------------------------ ------ -------- --------- ------------------- CB$TIME_CALENDAR DEMAND FORCE 10-JUL-07 FRESH CB$TIME_FISCAL DEMAND FORCE 10-JUL-07 FRESH CB$PRODUCT_PRIMARY DEMAND FORCE 10-JUL-07 FRESH CB$CUSTOMER_SHIPMENTS DEMAND FORCE 10-JUL-07 FRESH CB$CUSTOMER_SEGMENT DEMAND FORCE 10-JUL-07 FRESH CB$CHANNEL_PRIMARY DEMAND FORCE 10-JUL-07 FRESH CB$UNITS_CUBE DEMAND FORCE 10-JUL-07 FRESH 7 rows selected.
The example shows the cube materialized views defined by Analytic Workspace Manager: One for each dimension hierarchy and one for each cube.
Oracle Database can maintain a set of logs on the master tables for the cube materialized views. These logs support incremental (fast) refresh of the cube. The script generated by the Relational Schema Advisor creates a log for each fact and dimension table to record any changes to the data. The following query lists the materialized view logs owned by the
GLOBAL user:
SELECT master, log_table FROM user_mview_logs; MASTER LOG_TABLE ------------------------------ ------------------------------ CHANNEL_DIM MLOG$_CHANNEL_DIM CUSTOMER_DIM MLOG$_CUSTOMER_DIM PRODUCT_DIM MLOG$�= �_PRODUCT_DIM TIME_DIM MLOG$_TIME_DIM UNITS_FACT MLOG$_UNITS_FACT
You can initiate a data refresh of a cube materialized view in several different ways using Analytic Workspace Manager or a PL/SQL package:
Automatic Refresh: On the Materialized View tab for a cube, you can create a regular schedule for the materialized view refresh subsystem, as described in "Adding Materialized View Capability to a Cube".
Maintenance Wizard: The Maintenance Wizard is available for refreshing all cubes and dimensions, including cube materialized views.
DBMS_CUBE: The
DBMS_CUBE PL/SQL package is available for refreshing all cubes and dimensions.
DBMS_MVIEW: The
DBMS_MVIEW PL/SQL package contains several procedures for use with cube materialized views.
DBMS_CUBE can be used to create and populate an analytic workspace. You can use it to maintain any cube, including cube materialized views.
The following command initiates a complete refresh of
UNITS_CUBE, which is enabled as a cube materialized view. It automatically refreshes any stale dimensions before refreshing the cube.
EXECUTE dbms_cube.build('GLOBAL.UNITS_CUBE'); PL/SQL procedure successfully completed.
You can determine the refresh method from
USER_MVIEWS, as shown in "Identifying Cube Materialized Views".
DBMS_MVIEW can be used to refresh all types of materialized views. These refresh procedures can be used with cube materialized views:
REFRESH refreshes a list of one or more materialized views.
REFRESH_ALL_MVIEWS refreshes all materialized views that meet certain criteria.
REFRESH_DEPENDENT refreshes all materialized views that depend on a particular master table and meet certain criteria.
Dimensions must be refreshed before the cube. An error is raised during refresh of a cube materialized view if any of its associated dimension materialized views are stale. The procedures in
DBMS_MVIEW can refresh multiple materialized views in one call, but they do not guarantee the appropriate refresh order. You must be sure to list all the dimension materialized views before the cube materialized views when using this package.
The following command initiates a refresh of the materialized view for the
CHANNEL_PRIMARY hierarchy. Only the Complete refresh type is valid for dimensions.
EXECUTE dbms_mview.refresh('CB$CHANNEL_PRIMARY', 'C'); PL/SQL procedure successfully completed.
In Analytic Workspace Manager, you can specify the
FAST, or
FORCE methods for refreshing a cube. Two additional methods,
FAST_PCT and
FAST_SOLVE, are invoked by the materialized view subsystem. They are not separate choices.
Table 7-7 describes the refresh methods that are supported on cube materialized views.
Table 7-7 Refresh Methods For Cube Materialized Views
|Refresh Method
Deletes and recreates the cube.
This option supports arbitrarily complex mappings from the source tables to the cube.
Loads and re-aggregates only changed values, based on the materialized view logs.
The source for the refresh is the incremental differences that have been captured in the materialized view logs, rather than the original mapped sources. These differences are used to incrementally rebuild the cube. Only cells that are affected by the changed values are re-aggregated.
This option supports only simple mappings for cube materialized views, that is, where no expressions (other than table.column), views, or aggregations occur in the query defining the mapping.
The materialized view subsystem determines whether to perform a
Loads and re-aggregates data only from changed partitions.
The Partition Change Tracking method is not available for cube materialized views enabled for query rewrite. This method works best when the source table and the cube are partitioned on the same dimension.
Loads and re-aggregates only changed values, based on the original mapped data source.
This option is supported for arbitrarily complex mappings from the source tables to the cube. To discover whether a
Loads and re-aggregates only changed values, using the best method possible.
The materialized view system first attempts a
The build log lists the
CLEAR LEAVES command when the
FAST SOLVE method was used. Example 7-5 shows the rows of
CUBE_BUILD_LOG concerned with building
See Also:"Build Logs"
Example 7-5 Identifying a FAST SOLVE Refresh
SELECT build_object, status, command FROM cube_build_log WHERE build_object='UNITS_CUBE' AND build_id=8; BUILD_OBJECT STATUS COMMAND ------------ ---------- ------------------------- UNITS_CUBE STARTED COMPILE AGGMAP UNITS_CUBE COMPLETED COMPILE AGGMAP UNITS_CUBE STARTED UPDATE UNITS_CUBE COMPLETED UPDATE UNITS_CUBE STARTED CLEAR LEAVES UNITS_CUBE COMPLETED CLEAR LEAVES UNITS_CUBE STARTED LOAD UNITS_CUBE COMPLETED LOAD UNITS_CUBE STARTED SOLVE UNITS_CUBE COMPLETED SOLVE UNITS_CUBE STARTED UPDATE UNITS_CUBE COMPLETED UPDATE UNITS_CUBE STARTED ANALYZE UNITS_CUBE COMPLETED ANALYZE 14 rows selected.
Query rewrite changes a query to select data from the materialized views instead of calculating the result set from the master tables. The transformation is fully transparent to the client, and requires no mention of the materialized views in the SQL statement. In the case of cube materialized views, the query is written against the tables or views of a star or snowflake schema, and it is transformed into a query against a cube materialized view. This transformation can result in significant improvements in runtime performance.
Query rewrite requires optimizer statistics on the cubes and dimensions. You can discover whether a query will be rewritten by generating and examining its execution plan.
Oracle Database uses two initialization parameters to control query rewrite:
QUERY_REWRITE_ENABLED: Enables or disables query rewrite globally for the database.
QUERY_REWRITE_INTEGRITY: Determines the degree to which query rewrite monitors the consistency of materialized views with the source data. The
trusted or
stale tolerated settings are recommended when using rewrite to cube materialized views.
Administration of cube materialized views is the same as any other materialized view except that the cube materialized views must be in the same schema as the analytic workspace. Users require the
GLOBAL QUERY REWRITE privilege to have rewrite to materialized views that are in schemas other than their own. However, the owner can access the materialized views from any schema without additional privileges.
See Also:
Oracle Database has numerous PL/SQL packages for managing materialized views. Cube materialized views are already optimized to provide the best performance, so you have no need to use most of these packages. Few design decisions remain for you to make. For this reason, the
TUNE_MVIEW procedure of
DBMS_ADVISOR is disabled for cube materialized views.
However, there are a few packages that you may find useful, as shown in Table 7-8.
Table 7-8 PL/SQL Packages for Cube Materialized Views
Executes data refreshes. See "Initiating a Data Refresh".
You can use the
Displays an execution plan. See "Viewing Execution Plans".
The Oracle OLAP User's Guide explains how SQL applications can extend their analytic processing capabilities and manage summary data by using the OLAP option of Oracle Database. It also provides information about managing resources for OLAP.
The preface contains these topics:
This manual is intended for DBAs who need to perform these tasks:
Develop and manage a data warehouse
Create and maintain dimensional data objects
Administer Oracle Database with the OLAP option
Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
TTY Access to Oracle Support Services
Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, 7 days a week. For TTY support, call 800.446.2398. Outside the United States, call +1.407.458.2479.
For more information about the OLAP option, see the following manuals in the Oracle Database 11g documentation set:
Oracle OLAP Java API Developer's Guide
Introduces the Oracle OLAP API, a Java application programming interface for Oracle OLAP, which is used for defining, building, and querying dimensional objects in the database.
Oracle OLAP Java API Reference
Describes the classes and methods in the Oracle OLAP Java API for defining, building, and querying dimensional objects in the database.
Contains a complete description of the OLAP Data Manipulation Language (OLAP DML) used to define and manipulate analytic workspace objects.
The following text conventions are used in this document:
|Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.
|Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.
|Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.