DW - Multidimensional Cube

Cubes are known as multidimensional (composition of fact tables and dimensions) data processing units to conceptually straightforward operations to retrieve decision support information from the data warehouse or data marts. The edge of the any cube contains dimension members and the body of the cube contains data values.

They can have 2-dimensional, 3-dimensional or more up to 128 dimensions as per the business requirement which are mainly designed and used for analysis purpose.

In the cube, each dimension represents some attribute in the database and the cells in the data cube represent the measure of interest.

Simply, a cube provides an easy-to-use mechanism for querying data with quick and uniform response times. End users use client applications to connect to an Analysis server and query the cubes on the server. In most client applications, end users issue a query on a cube by manipulating the user interface controls, which determine the contents of the query.

As for an example, you may have the following dimensions in a warehouse for keeping date, customer, store, product and sales data.

CustomerID

StoreId

ProductId

Date 

Sales

If you want to draw the dimension layout then it will support 4D "cube" layout which would allow queries to be easily performed for customers that may have different purchasing patterns throughout the year (and even at different times of the month). The above example has four dimensions (date, customer, store, and product) and one fact (sales) which could follow the star schema model look like as:

 

Conceiving data as a cube with hierarchical dimensions leads to conceptually straightforward operations to facilitate analysis. Aligning the data content with a familiar visualization enhances analyst learning and productivity. The user-initiated process of navigating by calling for page displays interactively, through the specification of slices via rotations and drill down/up is sometimes called "slice and dice". Common operations include slice and dice, drill down, roll up, and pivot.

If you try to view the data in the cube model then it would be display like as shown below:

A dimension (table) is a look-up table for properties of objects that rarely change. Product, customer and store may change some of their properties (attributes), but they rarely do. Dimension levels are a powerful data modeling tool because they allow end users to ask questions at a high level and then expand a dimension hierarchy to reveal more detail.

Fact table captures interactions between these objects. At the intersection of dimensions date, store, product and customer lies a measure SalesAmount. In addition, measures are the central values of a cube that are analyzed. That is, measures are the numeric data of primary interest to end users browsing a cube. 

A measure group has one or more partitions to manage the physical data. A measure group can have a default aggregation design. The default aggregation design can be used by all partitions in the measure group; also, each partition can have its own aggregation design.

Note how easy is to aggregate (sum) the sales amount by date, by year, by product, by brand, by city, by country, by age group, by whatever which was the idea in the first place.

Subordinate to the cubes In the object hierarchy, cubes are immediately subordinate to the database. A database just behaves like a container for all related cubes and the objects they share. Before creating a cube, we must create or have a database. In the object hierarchy, the following objects are immediately subordinate to the cube: Data sources The most important point is that a cube can have a single data source. It can be selected from the data sources in the database or created during cube creation. A cube’s dimensions must have the same data source as the cube, but its partitions can have different data sources. Measures True fact about the measures that a cube's measures are not shared with other cubes. They are created when the cube is created. A cube can contain up to 128 dimensions, each with millions of members, and up to 1,024 measures. Dimensions A cube's dimensions are either shared with other cubes in the database or private to the cube. Shared dimensions or public dimension can be created before or during cube creation. Private dimensions are created when the cube is created. Although the term cube suggests three dimensions but a cube can have up to 128 dimensions. Dimension members should be textual and are used as criteria for queries and as row and column headers in query results. Partitions A single partition is automatically created for a cube when the cube is created. If you have installed Analysis Services for SQL Server 2000 Enterprise Edition, after creating a cube, you can create additional partitions in the cube. Cube roles Every cube must have at least one cube role in order to provide access to end users. Cube roles are derived from database roles, which can be created before or after cube creation. They are created after cube creation. Commands Commands are optional. Commands are created after cube creation.

Please visit to know more on -

1. Collaboration of OLTP and OLAP systems. 
2. Major differences between OLTP and OLAP. 
3. Data Warehouse
4. Data Warehouse - Multidimensional Cube
5. Data Warehouse - Multidimensional Cube Types
6. Data Warehouse - Architecture and Multidimensional Model
7. Data Warehouse - Dimension tables.
8. Data Warehouse - Fact tables.
9. Data Warehouse - Conceptual Modeling.
10. Data Warehouse - Star schema.
11. Data Warehouse - Snowflake schema.
12. Data Warehouse - Fact constellations. 
13. Data Warehouse - OLAP Servers 

References:

https://technet.microsoft.com/en-us/library/aa216365(v=sql.80).aspx