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 with D

Data source

Dimension

The term dimension is commonly used in two ways:

• A general term for any characteristic that is used to specify the members of a data set. The three most common dimensions in sales-oriented data warehouses are time, geography, and product. Most dimensions have hierarchies.
• An object defined in a database to enable queries to navigate dimensions. In Oracle Database 10g, a dimension is a database object that defines hierarchical (parent/child) relationships between pairs of column sets. In Oracle Express, a dimension is a database object that consists of a list of values.

A dimension is a structure that categorizes data in order to enable users to answer business questions. Commonly used dimensions are customers, products, and time. For example, each sales channel of a clothing retailer might gather and store data regarding sales and reclamations of their Cloth assortment. The retail chain management can build a data warehouse to analyze the sales of its products across all stores over time and help answer questions such as:

• What is the effect of promoting one product on the sale of a related product that is not promoted?
• What are the sales of a product before and after a promotion?
• How does a promotion affect the various distribution channels?

The data in the retailer's data warehouse system has two important components: dimensions and facts. The dimensions are products, customers, promotions, channels, and time. One approach for identifying your dimensions is to review your reference tables, such as a product table that contains everything about a product, or a promotion table containing all information about promotions. The facts are sales (units sold) and profits. A data warehouse contains facts about the sales of each product at on a daily basis.

Within the OLAP Option the fact information is stored in what is called a Cube and the dimensional information is stored in a Dimension. In our example, each sales transaction record is uniquely defined as for each customer, for each product, for each sales channel, for each promotion, and for each day (time).

Typically members within a dimension are organized according to hierarchies and each dimension can have as many hierarchies as required to fully describe all the possible relationships. Each hierarchy represents a natural 1:n relationship between members or groups of members (the levels of a hierarchy). When traversing a hierarchy, going up a level in the hierarchy is called rolling up the data and going down a level in the hierarchy is called drilling down the data. In the retailer example:

• Within the time dimension, months roll up to quarters, quarters roll up to years, and years roll up to all years.
• Within the product dimension, products roll up to subcategories, subcategories roll up to categories, and categories roll up to all products.
• Within the customer dimension, customers roll up to city. Then cities roll up to state. Then states roll up to country. Then countries roll up to subregion. Finally, subregions roll up to region, as shown below

Data analysis typically starts at higher levels in the dimensional hierarchy and gradually drills down if the situation warrants such analysis.

A single dimension can contain multiple hierarchies. For example, suppose our retailer wants to track the sales of certain items over time. Most analysis over time typically involves looking at data rolled up over calendar time periods as well as over fiscal time periods. Therefore, a typical time dimension will contain at least two time hierarchies:

• Calendar
• Fiscal

Dimension Table

Dimension tables describe the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables. Typically, these types of tables are divided into three basic categories, that are related to the mapping options within Analytic Workspace schema:

Star Schema Dimension Table
A star schemaprovides one table or view with columns containing member id's representing all levels of a hierarchy for each dimension. Each row in the table specifies a branch in the hierarchy. Additional columns identify additional attributes for each level, such as long and short descriptions. In the case of a time dimension, additional attributes will be required to provide information on end date and time span for each level.




Snowflake Schema Dimension Table A snowflake schema provides separate tables or views for each levels of a hierarchy. Each row in the table specifies a level in the hierarchy with an additional column to link to each parent across the various hierarchies. The same basic requirements apply as for star schemas in terms of uniqueness.


Other more complex schemas can be created. The basic snowflake schema can be taken a stage further by moving the various attributes, such as descriptions etc, to separate tables. This follows a more 3NF approach to data storage and although it looks more complicated it can easily be managed within AWM's mapping editor.



Also see the entries for Snowflake, Star, Ragged Hierarchy, Skip Level Hierarchy

Dimension Value

One element in the list that makes up a dimension. For example, a computer company might have dimension values in the product dimension called LAPPC and DESKPC. Values in the geography dimension might include Boston and Paris. Values in the time dimension might include MAY96 and JAN97.

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.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. The image below shows drilling within Discoverer Report via the dimension on the row edge.