Oracle Database has an OLAP Option that provides an embedded, full-featured online analytical processing server.
The OLAP Option is used in the following roles:
A summary management solution to SQL-based business intelligence tools and applications.
A calculation engine that provides SQL-based business intelligence tools with rich analytic content.
A full-featured multidimensional server, servicing dimensionally oriented business intelligence tools and applications.
Oracle Business Intelligence supports Oracle OLAP as a data source. When you import metadata from an Oracle OLAP source, the Oracle OLAP objects appear in the Physical layer of the Administration Tool. This section provides information about the Oracle OLAP objects in the Physical layer.
This section contains the following topics:
Learn how to use the Oracle BI Administration Tool to import metadata from Oracle OLAP.
When using the Oracle BI Administration Tool:
For Oracle OLAP cubes with multi-language metadata, only the default language is imported.
Only dimensions that contain at least one hierarchy are imported.
Multiple hierarchies in a single query are not supported. If a query includes columns from multiple hierarchies in a given dimension, the Oracle BI Server returns an error.
The default aggregation rule in the Business Model and Mapping layer for Oracle OLAP measures is External Aggregation. The External Aggregation rule means that the Oracle BI Server is not aware of the underlying aggregation rule for the specific measure and will not compute it internally. Instead, the Oracle BI Server always sends the query to the underlying multidimensional data source for aggregation.
In some cases, you may want to set the aggregation rule for a measure to something other than External Aggregation. For example, you may have federated multiple data sources, or you may want to perform higher-level aggregation along dimension attributes that are not represented by a level in Oracle OLAP. In both of these cases, you can change the default aggregation rule to match the rule in the underlying data source or sources. The aggregation is performed in the Oracle OLAP data source where possible.
See System Requirements and Certification for the latest information about the versions of Oracle OLAP supported by Oracle Business Intelligence.
You can view Oracle OLAP Analytic Workspace (AW) objects in the Physical layer of the Oracle BI Administration Tool.
These objects correspond to the analytic workspace object in the Oracle OLAP metadata, and are similar to physical catalog and physical schema objects. Analytic workspaces are containers for storing related cubes. You create dimensions, cubes, and other dimensional objects within the context of an analytic workspace.
Oracle OLAP Analytic Workspace objects have properties for Name, Description, and Dynamic Name. You can use the Dynamic Name tab to provide a variable that specifies the name of the Analytic Workspace object. Note that the Dynamic Name tab is not active unless at least one session variable is defined. See Using a Variable to Specify the Name of a Catalog or Schema for more information.
Oracle OLAP 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.
There are three types of Oracle OLAP dimensions:
Level-based dimensions. Members of level-based dimensions naturally group into levels based on their type, such as "month" and "year." Most dimensions are level-based.
Value-based dimensions. These dimensions have parent-child relationships among their members, but the members are all the same type (like 'Employee' or 'Account'), so these relationships do not form meaningful levels.
List or flat dimensions. These dimensions have no levels or hierarchies.
Note:
Oracle Business Intelligence does not support dimensions that have no hierarchies (flat dimensions). Importing flat dimensions from an Oracle OLAP data source will result in an error. If you have flat dimensions, replace them with single-level hierarchies in the data source before importing them into Oracle Business Intelligence.
On the General tab of the Oracle OLAP Dimension dialog, you can view and edit the name and description of the dimension, along with the following dimension properties:
Time. Indicates that this dimension is a time dimension.
Ragged. Indicates that this dimension contains a hierarchy that has at least one member with a different base, creating a "ragged" base level for the hierarchy.
Skipped levels. Indicates that this dimension contains a hierarchy that has 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).
External Name. The physical name that is used when referencing the dimension in physical SQL queries. This value must reflect the external name defined in the data source.
Cache properties. Select Cacheable to include this dimension in the Oracle BI Server query cache. To specify that cache entries do not expire, select Cache never expires. Alternatively, you can select Cache persistence time and enter a value to specify how long entries should persist in the query cache. Note that if a query references multiple physical objects with different persistence times, the cache entry for the query exists for the shortest persistence time set for any of the tables referenced in the query. This makes sure that no subsequent query gets a cache hit from an expired cache entry.
The Columns and Hierarchies tabs of the Oracle OLAP Dimension dialog list the dimension members and hierarchies that belong to the dimension. In the Columns tab, you can add or remove columns, and edit particular columns. In the Hierarchies tab, you can add, remove, or edit hierarchies. You can also use the type (key) button to select the default hierarchy for the dimension.
Dimensions can contain one or more hierarchies. Most hierarchies are level-based and 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 and skip-level hierarchies are also supported.
Dimensions can also contain value-based hierarchies, which are parent-child hierarchies that do not support levels. For example, an employee dimension might have a parent-child relationship 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.
For value-based hierarchies, the Nullable option is selected by default for the root member physical cube column. This option must be selected for the root member for value-based hierarchies to work correctly.
Multiple hierarchies for a dimension typically share the base-level dimension members and 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.
In the Oracle OLAP Hierarchy dialog, you can view and edit the name, external name, and description of the hierarchy. For level-based hierarchies, you can add, remove, edit, or reorder levels. For value-based hierarchies, you can add, remove, or edit columns. To specify a key column, double-click a column name.
In the Oracle OLAP Level dialog, you can view and edit the name, external name, and description of the level. You can also add, remove, or edit columns for that level. To designate a column as a level key, double-click a column name.
Oracle OLAP 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 the business measures and 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.
Oracle OLAP cubes have properties similar to other cubes. You can view, edit the name and description of the cube, and update the following cube properties:
External Name. The physical name that is used when referencing the cube in physical SQL queries. This value must reflect the external name defined in the data source.
Density and Materialization. For Oracle OLAP cubes that are sparse and fully materialized, specify values for these properties to optimize queries. If you set the Density option to Sparse and the Materialization option to Fully Materialized, the Oracle BI Server generates a loop clause to skip empty cells. If you leave the Density option blank, the Oracle BI Server assumes the data is sparse.
If you set these values, make sure that you set them to reflect the actual properties of the data source. Do not specify that the data is sparse and fully materialized unless this is true for the data source.
You do not need to set these values for Oracle OLAP 11g cubes. For these objects, optimization happens automatically.
Cache properties. Select Cacheable to include the cube in the Oracle BI Server query cache.
To specify that cache entries do not expire, select Cache never expires or select Cache persistence time and enter a value to specify how long entries should persist in the query cache.
If a query references multiple physical objects with different persistence times, the cache entry for the query exists for the shortest persistence time set for any of the tables referenced in the query. This makes sure that no subsequent query gets a cache hit from an expired cache entry.
The Columns tab on the Oracle OLAP Cube dialog lists the columns that belong to the cube. You can add or remove columns, and edit particular columns.
You can define measures, calculated measures, attributes, or level keys as Oracle OLAP columns. The Oracle OLAP columns have the same properties as other physical columns. See Creating and Editing a Column in a Physical Table for more information about physical column properties like Type, Length, and Nullable.