| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
Active Catalog
A set of relational views that expose the standard form metadata stored in analytic workspaces, where it can be accessed by SQL. Applications that use OracleBI Beans query the Active Catalog.
See also database standard form.
additive
Describes a fact (or measure) that can be summarized through addition. An additive fact is the most common type of fact. Examples include sales, cost, and profit.
Contrast with nonadditive, semi-additive.
aggregation
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. Aggregation is synonymous with summarization, and aggregate data is synonymous with summary data.
analytic workspace
A dimensional schema stored in a LOB table in the relational database. An analytic workspace can contain a variety of objects. Some of these objects may be integrally connected to other objects, while others are totally independent. Some objects store data that is useful to applications, and other objects may only exist for the purposes of the DBA or developer. There are several basic types of objects which play a variety of roles in the dimensional model. In these respects, an analytic workspace is very similar to a relational schema.
The OLAP DML is the basic, low-level language for working in an analytic workspace. Tools are available in PL/SQL and Java that provide an interface to the OLAP DML for users already familiar with those languages.
See also OLAP DML.
ancestor
A value at any level higher than a given value in a hierarchy. For example, in a Time dimension, the value 2002 might be the ancestor of the values Q1-02 and Jan-02. 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.
attribute
A descriptive characteristic of either a single dimension member or a group of dimension members. When applied to a single member, attributes 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 applied to a group, attributes represent logical groupings that enable users to select data based on like characteristics. For example, in a database representing footwear, you can use a shoe color attribute to select all boots, sneakers, and slippers that share the same color.
base level data
Data at the lowest level, often acquired from another source, such as a transactional database.
Contrast with aggregation.
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 variable with the dimensions MONTH and DISTRICT, then each combination of a month and a district identifies a separate cell of that variable.
child
A value at the level under a given value in a hierarchy. For example, in a Time dimension, the value Jan-02 might be the child of the value Q1-2002. A value can be a child for more than one parent if the child value belongs to multiple hierarchies.
Contrast with parent. See also descendant, hierarchy, level.
composite
An analytic workspace object that lists dimension-value combinations (also called a tuple) for which there is data. When a data value is added to a variable dimensioned by a composite, that action triggers the creation of a composite tuple. A composite is an index into one or more sparse data variables, and is used to store sparse data in a compact form.
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.
custom measure
A measure that is calculated at run-time and presented as one or more additional columns of data added to a result set. The result set includes a value for each dimension member currently in status. For example, a custom measure might calculate the difference in costs from the prior period by using the OLAP DML LAGDIF function on the COSTS measure. Another custom measure might calculate profits by subtracting the COSTS measure from the SALES measure.
See also dimension member, OLAP DML, measure, status.
custom member
A member of a dimension created at run-time and defined as the parent of one or more existing dimension members. The values of a measure for a custom member are calculated using the aggregation rules for that dimension.
See also aggregation, dimension member, parent.
data warehouse
A relational database that is 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.
database standard form
An analytic workspace that has been constructed with a specific set of objects, such as hierarchy dimensions, level dimensions, parent relations, and level relations. Each object must be defined with a set of properties that identify its role and its relationships with other objects in the analytic workspace. The standard form is required for an analytic workspace to be accessible to OLAP tools, however, it is not a prerequisite for multidimensional analysis.
derived measure
A measure that is calculated from one or more stored measures. The calculated data may be stored in the analytic workspace, or it may be calculated entirely in response to a query.
See also custom measure.
DBA
Database administrator. The person responsible for creating, installing, configuring and maintaining Oracle Databases.
definition
The description of an analytic workspace object. An object definition includes characteristics such as the object's name, type (for example, dimension or variable), data type, dimensions, long description, permission specifications, and properties.
See also dictionary, object, property.
derived fact (or measure)
A fact (or measure) that is generated from existing data using a mathematical operation or a data transformation. Examples include averages, totals, percentages, and differences.
descendant
A dimension member at any level below a particular member in a hierarchy. The level immediately below is the child.
Contrast with ancestor. See also aggregation, child, hierarchy, level.
dictionary
The collection of object definitions in an analytic workspace. The dictionary is also called the workspace dictionary.
See also definition, object.
dimension
A structure that categorizes data. Among the most common dimensions for sales-oriented data are time, geography, and product. Most dimensions have hierarchies.
In an analytic workspace, a dimension is a container for a list of values. A dimension acts as an index for identifying the values of a variable. For example, if sales data has a separate sales figure for each month, then the data has a month dimension; that is, the data is organized by month.
In SQL, a dimension is a type of object that defines hierarchical (parent/child) relationships between pairs of column sets.
See also hierarchy.
dimension member
One element in the list that makes up a dimension. Also called a dimension value. A computer company might have dimension members in the product dimension called LAPPC and DESKPC. Members in the geography dimension might include Boston and Paris. Members in the time dimension might include NOV02, DEC02, JAN03, FEB03, MAR03, and so forth.
dimension table
A relational table that stores all or part of the values for a logical dimension in a star or snowflake schema. Dimension tables describe the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. They are sometimes called lookup or reference tables.
dimension view
A relational view of data in an analytic workspace that contains the same types of data as a dimension table in a star schema, that is, columns for dimension members and attributes. A dimension view typically lists all dimension members in the key column, regardless of their level in the dimension hierarchy.
See also dimension table, star schema.
drill
To navigate from one item to a set of related items. Drilling typically involves navigating up and down through the levels in a hierarchy. When selecting data, you can expand or collapse a hierarchy by drilling down or up in it, respectively.
drill down
To expand the view to include child values that are associated with parent values in the hierarchy.
drill up
To collapse the list of descendant values that are associated with a parent value in the hierarchy.
edge
A set of one or more dimensions that are displayed together in a cube or document. Although there is no limit to the number of edges in a cube, data is often organized for display purposes along three edges, which are referred to as the row edge, the column edge, and the page edge.
In a cross-tab report, dimension members on the row edge appear in the first column and identify the rows, dimension members on the column edge appear in the first row and identify the columns, and dimension members on the page edge label the individual pages of the report.
See also cube.
EIF file
A specially formatted file for transferring data between analytic workspaces. Using the OLAP DML, you can create an EIF file using the EXPORT command and read an EIF file using the IMPORT command.
embedded total
A predefined level of aggregation built into a dimension for which a hierarchy exists. For example, in a time dimension, each quarter represents the total for the months in the quarter. Data for embedded totals is calculated in the analytic workspace by the aggregation system.
See also aggregation, dimension, hierarchy.
fact table
A table in a star schema that contains facts. 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 level facts or facts that have been aggregated. 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.
family relation
An analytic workspace relation object that identifies the complete parentage of each dimension member. A family relation has at least two dimensions: the data dimension and a level dimension. The contents of the relation identify, for each dimension member, the ancestor at each level of the hierarchy.
formula
A type of workspace object that represents a stored calculation, expression, or procedure that produces a value. A formula provides a way to define and save complex or frequently used relationships within the data without storing the result set. Each time you query a formula, the OLAP engine performs the calculation or procedure that is required to produce the value.
hierarchy
A logical structure that uses ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation; for example, in a time dimension, a hierarchy might be used to aggregate data from the month level to the quarter level to the year level. A hierarchy can be used to define a navigational drill path, regardless of whether the levels in the hierarchy represent aggregated totals.
In PL/SQL, hierarchies can be defined as part of a dimension object.
level
A position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels.
level relation
An analytic workspace relation object that identifies the level of each dimension member.
materialized view
A precomputed relational table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.
measure
Data that can be examined and analyzed, such as sales or cost data. You can select and display the data in a measure. Measures can be stored as variables or relations, or measures can be calculated by means of formulas. The terms measure and fact are synonymous; measure is more commonly used in a dimensional environment and fact is more commonly used in a relational environment.
There are both base measures and custom measures. Base measures, such as Volume Sales and Dollar Sales, are stored. Custom measures, such as Volume Share Year Ago, are calculated from base measures.
measure view
A relational view of data in analytic workspace that contains the same types of data as a fact table in a star schema. However, in addition to the base-level facts, a measure view also contains derived data, such as aggregates and inter-row calculations.
See also fact table, star schema.
metadata
Data that describes data and other structures, such as objects, business rules, and processes.
See also OLAP Catalog.
model
A type of analytic workspace object that contains a set of interrelated equations, which are used to calculate data and assign it to a variable or dimension value. Models are used frequently when working with financial data.
See also dimension member, object, variable.
NA value
A special data value that indicates that data is "not available" (NA). It is the value of any cell to which a specific data value has not been assigned or for which data cannot be calculated.
nonadditive
Describes a fact (or measure) that cannot be summarized through addition, such as average. Contrast with additive, semi-additive.
normalize
In a relational database, the process of removing redundancy in data by separating the data into multiple tables. Contrast with denormalized.
object
In an analytic workspace, a distinct item in the workspace dictionary. Analytic workspaces consist of one or more objects, such as variables, formulas, dimensions, relations, and programs, which are used to organize, store, and retrieve data. Each object is created with a particular object type and stores a particular type of information. Objects that are the same type (for example, three variables) can have different roles within the analytic workspace.
See also role.
object type
In Oracle object technology, a form of user-defined data type that is an abstraction of a real-world entity. An object type is a schema object with the following components:
A name, which identifies the object type uniquely within a schema
Attributes, which model the structure and state of the real-world entity
Methods, which implement the behavior of the real-world entity, in either PL/SQL or Java
OLAP Catalog
A metadata package consisting of a set of read and write APIs that describe data in dimensional terms, such as cubes, measures, dimensions, and attributes.
See also metadata.
on the fly
Calculated at run-time in response to a specific query. In an analytic workspace, custom measures and custom members are typically calculated on the fly. Aggregate data can be precalculated, calculated on the fly, or a combination of the two methods.
Contrast with precalculate.
online analytical processing (OLAP)
Functionality 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
online transaction processing (OLTP)
Systems 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.
parent
A dimension member at the level 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.
parent-child relation
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).
parent relation
An analytic workspace relation object that defines a dimension's hierarchies by storing the parent of each dimension member.
precalculate
Calculated and stored as a data maintenance procedure. In an analytic workspace, aggregate data can be precalculated, calculated on the fly, or a combination of the two methods.
Contrast with on the fly.
program
A type of database object that contains a series of OLAP DML commands. A program executes a set of related commands. Programs can be nested, with one calling another. A program can return a value; in this case, it is called a user-defined function.
See also object.
property
A characteristic of an object or component. Properties provide identifiers and descriptions, define object features (such as the number of decimal places or the color), or define object behaviors (such as whether an object is enabled). Properties are used extensively in standard form analytic workspaces.
See also object.
qualified data reference
A qualifier that limits one or more dimensions to a single value for the duration of an OLAP DML command. A QDR is useful when you want to temporarily reference a value without affecting the current status. In the following example of an OLAP DML command, the QDR limits the MONTH dimension to JUN02.
SHOW sales(month 'JUN02')
See also dimension, dimension member, status.
relation
A type of workspace object that is similar to a variable, except that it restricts its data values to the members of a particular dimension (such as PRODUCT) instead of to a particular data type (such as NUMBER). A relation establishes a correspondence between the values of a given dimension and the values of that dimension or other dimensions in the database.
For example, you might have a relation between cities and sales regions, such that each city belongs to a particular region. In a relation between cities and sales regions, the relation is dimensioned by CITY. Each cell holds the corresponding value of the REGION dimension.
See also cell, dimension, dimension member, variable.
role
The function of a workspace object within its broader categorization of object type. For example, a variable that stores numeric business measures has a role of measure. A variable that stores descriptive product names has a role of attribute. Both are variables, but they contain different types of information and play different roles in the dimensional model.
See also object.
rollup form
A table that displays the full ancestry of each dimension member within a row. The table provides a column for each level of the hierarchy.
For example, a row for base-level dimension member Florence has FLORENCE in the City column, ITALY in the Country column, and EUROPE in the Region column. A row for Italy has null in the City column, ITALY in the Country column, and EUROPE in the Region column.
Contrast with embedded total. See also ancestor, dimension member, hierarchy.
schema
A collection of related database objects. Relational schemas are grouped by database user ID and include tables, views, and other objects. Multidimensional schemas are called analytic workspaces and include dimensions, relations, variables, and other objects.
See also analytic workspace, snowflake schema, star schema.
semi-additive
Describes a fact (or measure) that can be summarized through addition along some, but not all, dimensions. Examples include head count and on-hand stock.
Contrast with additive, nonadditive.
snowflake schema
A type of star schema in which the dimension tables are partly or fully normalized.
See also normalize, schema, star schema.
solved data
A result set in which all derived data has been calculated. Data fetched from an analytic workspace 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 analytic workspace is the same whether the data was precalculated or calculated on the fly.
See also on the fly, precalculate.
source
A database, application, file, or other storage facility from which the data in a data warehouse is derived.
sparsity
A concept that refers to multidimensional data in which a relatively high percentage of the combinations of dimension values do not contain actual data. Such "empty," or NA, values can take up storage space in an analytic workspace. To handle sparse data efficiently, you can create a composite.
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 variable 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 contain NA values.
Random sparsity occurs when NA values are scattered throughout the variable, usually because some combinations of dimension values never have any data. For example, a district might only sell certain products and never have data for other products. Other districts might sell some of those products and other ones, too.
star query
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.
star schema
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 schema, snowflake schema
status
The list of currently accessible values for a given dimension. If the status of a given dimension is limited to a subset of its stored values, then all expressions that are based on that dimension will be limited to the corresponding subset of data. 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 dimension, dimension member.
valueset
A type of workspace object. A valueset contains a list of dimension members for a particular dimension. After defining a valueset, you use the LIMIT command to assign members from the dimension to the valueset. The values in a valueset can be saved across Oracle OLAP sessions.
When you begin a new Oracle OLAP session or start up a workspace, each dimension has all values in status. You can then limit a dimension to the values stored in the valueset for that dimension.
See also dimension.
variable
A type of workspace object that stores data. The data type of a variable indicates the kind of data that it contains, such as numeric or text data.
If a variable has dimensions, then those dimensions organize its data, and there is one cell for each combination of dimension members. A dimensioned variable is an array whose cells are individual data values. If a variable has no dimensions, then it is a single-cell variable, which contains one data value.
See also cell, dimension, dimension member, object.
