Understanding Data Storage - OLAP

Oracle OLAP multidimensional data is stored in analytic workspaces, which are, in turn, stored in relational tables. An analytic workspace can contain a variety of objects, such as dimensions, variables, and OLAP DML programs. These objects typically support a particular application or set of data. Whenever an analytic workspace is created, modified, or accessed, the information is stored in a table in the relational database.

Analytic Workspace Tables

Analytic workspaces are stored in tables in the Oracle Database. The names of these tables always begin with AW$. For example, if the GLOBAL_AW user creates two analytic workspaces, one named GLOBAL and the other named GLOBAL_PROGRAMS, then these tables will be created in the GLOBAL_AW schema:

AW$GLOBAL AW$GLOBAL_PROGRAMS

Tables are created by default with eight partitions. You can manage these partitions the same as you would for any other table in your database.

The tables store all of the object definitions and data. Each object in an analytic workspace is stored in one or more page spaces, and each page space is stored in a separate row of the table. A page space is grouping of related data pages; a page is a unit for swapping data in and out of memory.

For example, a dimension is stored in three page spaces and thus has three rows (one each for dimension members, a hash index, and a logical-to-physical map). A variable is stored in one row; a partitioned variable has a row for each partition. Table describes the columns of a table that stores an analytic workspace.

Column Descriptions for Analytic Workspace Tables

Column Descriptions for Analytic Workspace Tables

Table shows a few sample rows of an analytic workspace table, which are the results of the following query.

SELECT * FROM aw$global WHERE OBJNAME = 'TIME' OR OBJNAME = 'UNITS_VARIABLE' ORDER BY GEN#, PS#;

Sample Rows From AW$GLOBAL

Sample Rows From AW$GLOBAL

System Tables

The SYS user owns several tables associated with analytic workspaces:

AW$EXPRESS AW$AWCREATE AW$AWMD AW$ PS$
  • AW$EXPRESS stores the EXPRESS analytic workspace. This workspace contains objects and programs that support the OLAP DML. The EXPRESS workspace is used any time that a session is open.
  • AW$AWCREATE stores the AWCREATE analytic workspace, which contains programs for creating and managing standard form analytic workspaces.
  • AW$AWMD stores the AWMD analytic workspace, which contains programs for creating standard form catalogs.
  • AW$ maintains a record of all analytic workspaces in the database, recording its name, owner, and other information.
  • PS$ maintains a history of all page spaces. A page 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 PS$ enables the Oracle OLAP to discard pages that are no longer in use, and to maintain a consistent view of the data for all users, even when the workspace is being modified during their sessions. When changes to a workspace are saved, unused pages are purged and the corresponding rows are deleted from PS$.

The CWM1 and CWM2 read APIs are tables owned by the OLAPSYS user. Public synonyms provide user access to these tables.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics