What are SQL Cubes?

An OLAP (Online analytical processing) cube is a data structure that allows fast analysis of data. It can also be defined as the capability of manipulating and analyzing data from multiple perspectives. The arrangement of data into cubes overcomes some limitations of relational databases.

It can be thought of as extensions to the two-dimensional array of a spreadsheet.

For example: an insurance company might wish to analyze some financial data by product type, by time-period, by city, by type of revenue and cost, and by comparing actual data with a budget. These additional methods of analyzing the data are known as dimensions.

The OLAP cube consists of numeric facts called measures which are categorized by dimensions. The cube metadata (structure) may be created from tables in a relational database. Measures are derived from the records in the fact table and dimensions are derived from the dimension tables.



Fact table:

A fact table captures the data that measures the organization's business operations. A fact table might contain business sales events such as cash register transactions or the contributions and expenditures of a nonprofit organization. Fact tables usually contain large numbers of rows, sometimes in the hundreds of millions of records when they contain one or more years of history for a large organization.

A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized to provide information about the history of the operation of the organization. Each fact table also includes a multipart index that contains as foreign keys the primary keys of related dimension tables. Fact tables should not contain descriptive information or any data other than the numerical measurement fields and the index fields that relate the facts to corresponding entries in the dimension tables.

Dimension Table

It provides the context /descriptive information for fact table measurements.

Structure of Dimension - Surrogate key one or more other fields that compose the natural key (nk) and set of Attributes. Size of Dimension Table is smaller than Fact Table. Surrogate Key is used to prevent the primary key (pk) violation (store historical data). Values of fields are in numeric and text representation.