Already a member?
Sign in
Welcome! Wikis are websites that everyone can build together. It's easy!
Oracle Olap Terminology-C
The aim of this page is to provide a glossary of common OLAP and data warehousing terms and expressions. This list is for all terms beginning withC
Note: Oracle OLAP has a library of several hundred multidimensional calculation functions that can be used in calculated measures. It is even possible for expert users of Oracle OLAP to define their own functions to perform virtually any calculation.
From a business user perspective both measures appear and are used in exactly the same way and have equally as fast query access. All measures are equivalent as far as the end-user interface is concerned. However, it can be useful to at least provide them with the access to the definition of a calculated measure via a tooltip for example, just to avoid confusion.
The creation of composites is largely transparent to users of AWM. However, the creation process is driven by the marking of dimensions as sparse within the cube wizard as show here:
AWM 10g Cube Wizard
A “global” composite is simply a single composite for all data within a cube. When you are using AWM to design your cube, then depending on the "Compression" and "Partitioning" choices you make, it is possible to create a Global Composite. When would you opt to create Global Composites? The answer is very rarely. It can be beneficial to select this option in the case of a non-compressed cube that is partitioned. Below is the part of the Cube Wizard dialog showing the option to create a Global Composite.
AWM 10g Cube Wizard
If the “Use Compression” option is selected, then additional efficiency can often (but not always) be achieved by marking all dimensions (including Time) as sparse, especially for sparse data where there is known seasonality in the data, and especially if your AW is also partitioned on Time. But see my previous notes regarding this subject.
AWM 10g Cube Wizard
When implemented they allow the broadest set query rewrite transformations to make use of the related cube MV. The mv logs are required to enable logged based fast refresh.
See also: Analytic Workspace Manager, Materialized Views
So Cubes represent factual data; they are sometimes called “facts.” and are organized by one or more dimensions. Many people visualize measures as being a simple cube type shape, in which the edges of the shape are the dimensions and the contents of the shape are the measure values. The image below shows a generic simple three-dimensional measure.
Of course measures are not restricted to just three-dimensional definitions. A cube can have as many or as few dimensions as required to accurately manage the data associated with the measure. In additional Oracle’s OLAP option allows you to design and manage multiple cubes each with different dimensionality. In addition Oracle OLAP supports a number of different data types for measure such as: numeric (Oracle SQL data type), decimal, integer, text, date and boolean.
Oracle OLAP allows business users to quickly and easily slice and dice their cubes to analyse data most relevent to them.
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. ACB$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. The image belowshows the Materialized View tab of the Units Cube. It specifies an automatic refresh of the data every Thursday at 10:00 P.M.
AWM 11g Cube Wizard
See also Determine Existence and ReWriteability ofCube Organized...
Cube View
In AWM 11g, a relational view is automatically generated over a cube. The view definition and contents can be seen directly within AWM 11g as seen here:
In AWM10g an additional add-in utility has to be used to create the relational view over the cube. The aim of the View Generator add-in sample is to provide an easy way to define relational views over dimensions and cubes in an analytic workspace. The sample delivers a PL/SQL interface to perfom these tasks - enabling the functions to be called from a wide variety of clients. The View Generator add-in can be downloaded from the OLAP Home page on OTN:
One installed and registered with AWM, both the Dimension and Cube right-click menus will gain an extra menu-option as shown below:
The "Create View" wizard allows you to select which objects (measures, dimensions, levels, attributes etc) to externalise via the view:
Calculated measure
Calculated measures are measures whose values are calculated dynamically at query time. Only the calculation rule(s) is stored in the database. Common calculations include measures such as ratios, differences, moving totals, and averages. Calculations do not require disk storage space, and they do not extend the processing time required for data maintenance.Note: Oracle OLAP has a library of several hundred multidimensional calculation functions that can be used in calculated measures. It is even possible for expert users of Oracle OLAP to define their own functions to perform virtually any calculation.
From a business user perspective both measures appear and are used in exactly the same way and have equally as fast query access. All measures are equivalent as far as the end-user interface is concerned. However, it can be useful to at least provide them with the access to the definition of a calculated measure via a tooltip for example, just to avoid confusion.
Cell
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 dimensionsMONTHandCUSTOMER, then each combination of a month and a customer identifies a separate cell of that measure.Child
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 withparent. See alsodescendant,hierarchy.Commit
The COMMIT command executes a SQLCOMMITstatement. When you want changes that you have made in a workspace to be committed when you execute the COMMIT command, then you must first update the workspace using anUPDATE statement. UPDATE moves changes from a temporary work area to the database table in which the workspace is stored. Changes that have not been moved to the table are not committed. When you donotuseUPDATEand COMMIT statements, changes made to an analytic workspace during your session are discarded when you end your Oracle session. When you execute a SQLCOMMITstatement in your database session, all changes made in your session (including all updated changes in workspaces that you have attached with read/write access) are committed. All committed changes are visible to other users who subsequently attach the workspace. However, another user'sUPDATEand COMMIT statements do not affect your view of an already attached workspace. Many users execute DML statements using SQL*Plus® or OLAP Worksheet. Both of these tools automatically execute a COMMIT statement when you end your sessionComposite
A composite is an analytic workspace object used to manage sparsity. It maintains a list of all the sparse dimension-value combinations for, which there is data. By ignoring the sparse “empty” combinations in the underlying physical storage, the composite reduces the disk space required for sparse data. When data is added to a measure dimensioned by a composite, the AW automatically maintains the composite with any new values.The creation of composites is largely transparent to users of AWM. However, the creation process is driven by the marking of dimensions as sparse within the cube wizard as show here:
AWM 10g Cube Wizard
A “global” composite is simply a single composite for all data within a cube. When you are using AWM to design your cube, then depending on the "Compression" and "Partitioning" choices you make, it is possible to create a Global Composite. When would you opt to create Global Composites? The answer is very rarely. It can be beneficial to select this option in the case of a non-compressed cube that is partitioned. Below is the part of the Cube Wizard dialog showing the option to create a Global Composite.
AWM 10g Cube Wizard
Compressed cube
A cube with very sparse data that is stored in a compressed composite. See also composite. Version 10g of Oracle OLAP provides a new, internationally patented technology for the AW, which is exposed via a simple check box in AWM. This is an extremely powerful data storage and aggregation algorithm optimized for sparse data. It is a new technology that is often dramatically faster than any previous OLAP server technology when aggregating sparse multidimensional data. The use of this feature can improve aggregation performance by a factor of 5 to 50. At the same time, query performance can improve, and disk storage is often also dramatically reduced. This feature is ideal for large volumes of sparse data but not suitable for all cubes (especially dense cubes).If the “Use Compression” option is selected, then additional efficiency can often (but not always) be achieved by marking all dimensions (including Time) as sparse, especially for sparse data where there is known seasonality in the data, and especially if your AW is also partitioned on Time. But see my previous notes regarding this subject.
AWM 10g Cube Wizard
Compression
See compressed cube.Constraint Advisor
This feature is part of MV_CUBE_ADVICE procedure, which is part of the DBMS_CUBE_ADVISE package. This table function evaluates the metadata for a specified cube materialized view. It generates recommendations and returns them as a SQL result set. These SQL statements can be used to create constraints, SQL dimension objects, and materialized view logs that allow the broadest range of query rewrite transformations and log-based fast refresh of the cube materialized view. Analytic Workspace Manager (AWM) makes use of the DBMS_CUBE_ADVISE package to deliver relational constraint ddl/dml based on metadata of a deployed rewrite cube MV. The constraint recommendations include:- primary key
- foreign key
- not null,
- relational dimension
- mv log statements.
When implemented they allow the broadest set query rewrite transformations to make use of the related cube MV. The mv logs are required to enable logged based fast refresh.
See also: Analytic Workspace Manager, Materialized Views
Cube
“A logical organization of measures with identical dimensions. The edges of the cube contain dimension members and the body of the cube contains 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. In a star schema, a cube is represented by a fact table.”So Cubes represent factual data; they are sometimes called “facts.” and are organized by one or more dimensions. Many people visualize measures as being a simple cube type shape, in which the edges of the shape are the dimensions and the contents of the shape are the measure values. The image below shows a generic simple three-dimensional measure.
Of course measures are not restricted to just three-dimensional definitions. A cube can have as many or as few dimensions as required to accurately manage the data associated with the measure. In additional Oracle’s OLAP option allows you to design and manage multiple cubes each with different dimensionality. In addition Oracle OLAP supports a number of different data types for measure such as: numeric (Oracle SQL data type), decimal, integer, text, date and boolean.
Oracle OLAP allows business users to quickly and easily slice and dice their cubes to analyse data most relevent to them.
So what is the difference between a cube and a measure?
If you have used Analytic Workspace Manager you will be familiar with the concept of a cube. This is a high level container, which is invisible to business users querying the OLAP data, for grouping together measures that share the same dimensionality. Cubes do make your life much easier in terms of being able to manage a whole group of measures collectively, store them all to an XML template, load data into a group of measures via single reference and so on.Cube Organized Materialized View (MView)
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. 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.
- All dimensions of the cube use the same aggregation operator, which is eitherSUM,MIN, orMAX.
- 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
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. ACB$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. The image belowshows the Materialized View tab of the Units Cube. It specifies an automatic refresh of the data every Thursday at 10:00 P.M.
AWM 11g Cube Wizard
See also Determine Existence and ReWriteability ofCube Organized...
Cube View
In AWM 11g, a relational view is automatically generated over a cube. The view definition and contents can be seen directly within AWM 11g as seen here:In AWM10g an additional add-in utility has to be used to create the relational view over the cube. The aim of the View Generator add-in sample is to provide an easy way to define relational views over dimensions and cubes in an analytic workspace. The sample delivers a PL/SQL interface to perfom these tasks - enabling the functions to be called from a wide variety of clients. The View Generator add-in can be downloaded from the OLAP Home page on OTN:
- OLAP View Generator for use with AWM 10gR2 (10.2.0.3)
- readme
One installed and registered with AWM, both the Dimension and Cube right-click menus will gain an extra menu-option as shown below:
The "Create View" wizard allows you to select which objects (measures, dimensions, levels, attributes etc) to externalise via the view:
Custom measure
See calculated measure.Custom member
A dimension member whose data is calculated from the values of other members of the same dimension using the rules defined in a model.Seemodel.
Latest page update: made by keith_laker
, Feb 7 2008, 8:58 AM EST
(about this update
About This Update
Add Constraint Advisor
- keith_laker
148 words added
2 words deleted
view changes
- complete history)
Add Constraint Advisor
- keith_laker
148 words added
2 words deleted
view changes
- complete history)
Keyword tags:
OLAP terminology
More Info: links to this page