Cubes are key elements in online analytic processing (OLAP), a technology that provides fast access to data in a data warehouse. Cubes provide a mechanism for querying data with quick and uniform response time regardless of the amount of data in the cube or the complexity of the query.
Cubes are subsets of data from the data warehouse, organized and summarized into multidimensional structures. Summaries of data, precalculated according to selected business factors, provide the mechanism for rapid and uniform response times to complex queries.
Cube definition is the first of three steps in cube creation. The other steps are to specify the summarization strategy by designing aggregations (precalculated data elements), and to load the cube by processing it.
To define a cube, select a fact table and identify measures (numerical columns of interest to cube users) within the fact table. Then select dimensions, each composed of one or more columns from another table. Dimensions provide the categorical descriptions by which the measures are separated for analysis by cube users. For example, a cube for sales analysis might include the measures Item_Sale_Price and Item_Cost from the table Sales_Facts, and the dimensions Store_Location, Product_Line, and Fiscal_Year. This cube enables users to separate Item_Sale_Price and Item_Cost into various categories by Store_Location, Product_Line, and Fiscal_Year.
Each cube dimension can contain a hierarchy of levels to specify the categorical breakdown available to users. For example, a Store_Location dimension might include the level hierarchy: Continent, Country, Region, State_Province, City, Store_Number. Each level in a dimension is of finer granularity than its parent. For example, continents contain countries, and states or provinces contain cities. Similarly, the hierarchy of a time dimension might include levels for year, quarter, month, and day.
Dimension levels are a powerful data modeling tool because they allow users to ask questions at a high level and then expand a dimension hierarchy to reveal more detail. For example, an analyst might start by asking to see Item_Cost values of products for the past three fiscal years. The analyst may notice that 1998 Item_Cost values are high in comparison to other years. Expanding the Fiscal_Year dimension to the month level, the analyst sees that Item_Cost values were especially high in the months January and August. The analyst might then explore levels of the Store_Location dimension to see if a particular region significantly contributed to the high Item_Cost values, or expand into the Product_Line dimension to see if Item_Cost values were high for a particular product group or product. This type of exploration is known as drill-down and is common in OLAP applications.
Although the term cube suggests three dimensions, a cube can have up to 64 dimensions, including the Measures dimension.
For more information about dimensions, see Dimensions.
The Cube wizard provides an easy way to design cubes. The Cube editor offers additional flexibility for creating and modifying cube structures.