Business Analytics Cubes

Cubes are the main objects in online analytic processing (OLAP), a technology that provides fast access to data in a data warehouse. A cube is a set of data that is usually constructed from a subset of a data warehouse and is organized and summarized into a multidimensional structure defined by a set of dimensions and measures.

You use a client application (such as Microsoft Excel) to connect to an Analysis server and query the cubes on the server. An Analysis server can support many different cubes, such as a cube for sales, a cube for inventory, a cube for customers, and so on.

Every cube has a schema, which is the set of joined tables in the data warehouse from which the cube draws its source data. The central table in the schema is the fact table, the source of the cube's measures. The other tables are dimension tables, the sources of the cube's dimensions.

A cube is defined by the measures and dimensions that it contains. For example, a cube for sales analysis includes the measures Item Sale Price and Item Cost and the dimensions Store Location, Product Line, and Fiscal Year. This cube enables you 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 you. For example, the Store Location dimension includes 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 the Fiscal Year dimension includes the levels Year, Quarter, Month, and Day.

Dimension levels are a powerful data modeling tool because they allow you to ask questions at a high level and then expand a dimension hierarchy to reveal more detail. For example, you start by asking to see Item Cost values of products for the past three fiscal years. You may notice that 1998 Item Cost values are higher than those in other years. Expanding the Fiscal Year dimension to the Month level, you see that Item Cost values were especially high in the months January and August. You can drill down on the Store Location dimension to see if a particular region contributed significantly to the high Item Cost values, or you can expand into the Product Line dimension to see if Item Cost values were high for a particular product group or product.

Related Topics

The Business Analytics Configurator

Defining Cubes in a Business Analytics Database

Setting Up Business Analytics Databases