2
Understanding Express Databases

Chapter summary

When you create a query with Express Spreadsheet Add-In, you fetch data from an Express database. This chapter briefly describes the multidimensional nature of an Express database and defines terms that are used in your Express Spreadsheet Add-In documentation.

List of topics

This chapter includes the following topics:

• Understanding Multidimensionality

• Understanding Dimensions, Hierarchies, and Levels

• Understanding Measures

Understanding Multidimensionality

Relational versus multidimensional data model

Decision makers typically view and analyze data by time period, region, product, customer type, or other similar dimensions. To match the way you view data, the Express data model goes beyond a two-dimensional relational or spreadsheet model and uses a multidimensional array structure.

Illustration: Express multidimensional array

The following illustration contrasts a two-dimensional spreadsheet with an Express multidimensional array.

Text description of the illustration datacube.gif

Understanding Dimensions, Hierarchies, and Levels

Definitions: Dimensions and dimension values

Dimensions are the foundation of an Express database. A dimension is a list of values that provides an index to your data.

The values or entities that make up a dimension are know as dimension values. Within a dimension, dimension values are usually organized into different levels, with each level representing the aggregated value of the data from any lower levels.

Each database has its own unique set of dimensions and dimension values, provided by the DBA, to meet organizational needs.

Examples: Dimensions and dimension values

For purposes of illustration, the following table lists some sample dimensions and shows sample levels and values.

Dimension	Hierarchical Levels	Dimension Values
Time	Year    Quarter         Month	1996    Q1         Jan
Geography	Country    Region          City	United States    East         New York
Product	Product    Brand         Category	Deodorant    Brand X         Roll-On
Organization	Division    Company	Consumer Products    Best Company
Financial	Account    Line Item	Expenses    Administrative

Aggregate values

Some dimensions have values at varying levels of aggregation. For example, a Geography dimension might have cities, countries, and continents. In a measure that has Geography as one of its dimensions, each country value holds the total of all the values of the cities in that country. Each continent holds a total for all the countries in the continent. The dimension might also have a Total value that holds the total for all the continents.

Structure of hierarchies

The hierarchies within a dimension establish family relationships. For example, the Geography dimension might be constructed such that the United States, East, and New York make up a family in which there are parent, child, and descendant relationships.

A hierarchy provides structure for dimensions that have values at several different levels. The DBA specifies the hierarchies while configuring the database for use. The DBA specifies which dimension values aggregate to which other dimension values (for example, which cities are in Canada). The DBA also provides names for each level of aggregation, such as City, Country, Continent, and Total Geography.

Illustration: Structure of a hierarchy

You can view the structure of a hierarchy in several places when you use the Selector -- a set of tools for selecting dimension values. For example, the following illustration shows how the different levels of a dimension hierarchy are indented when they appear in the Selector's List tool.

Text description of the illustration cjhiera.gif

Understanding Measures

Definition: Measure

A measure is a database object that is used to store data values. A measure can have up to 32 dimensions, which serve as indexes to its values.

Relationship between measures and dimensions

A dimension is defined only once in the database. However, any number of measures can use the same dimensions. Measures dimensioned by the same dimension are said to share that dimension. For example, you can have both expense data and income data dimensioned by month, by category, and by division.

Measures are stored as formulas, relations, and variables

Measures are stored in an Express database as formulas, relations, and variables.

• A formula is an expression that you name and define. The expression itself (whether it is a constant value, a function, or an arithmetic or Boolean calculation) is included in the definition.

• A relation describes a correspondence between the values of two or more dimensions. For example, you might have a relation between cities and sales regions, such that each city belongs to a particular region. A relation can have more than one dimension, but the values of the relation must be values from the related dimension.

• A variable is an array whose cells hold individual data values.