Analysis Services OLAP Cubes

 A measure is a transactional value or measurement that a user may want to aggregate. The source of measures are usually columns in one or more source tables. Measures are grouped into measure groups.

 A dimension is a group of attributes that represent an area of interest related to the measures in the cube and which are used to analyze the measures in the cube. For example, a customer dimension might include the attributes:

These would enable measures in the cube to be analyzed by Customer Name, Customer Gender, and Customer City. The source of attributes are usually columns in one or more source tables. The attributes within each dimension can be organized into hierarchies to provide paths for analysis.

A cube is then augmented with calculations, key performance indicators (generally known as KPIs), actions, partitions, perspectives, and translations.

The information required to build and support an Analysis Services cube and its surrounding structure is reasonably complex and diverse. In attempting to automate the building of Analysis Services cubes QAD Data Warehouse Designer has simplified and restricted many of the functions available to the cube designer. QAD Data Warehouse Designer includes most of the commonly used capabilities and the components that logically fit into the methodology incorporated within QAD Data Warehouse Designer.

QAD Data Warehouse Designer broadly provides functionality to manage all of the above, except for perspectives and translations. These can be created outside of QAD Data Warehouse Designer, scripted in xmla and executed from within QAD Data Warehouse Designer. Features of cubes that are not supported in QAD Data Warehouse Designer can be added to the cube via the Microsoft tools. These altered cubes can still be processed through the QAD Data Warehouse Designer scheduler, and the cube should be documented within QAD Data Warehouse Designer to explain the post creation phases required.

As a general rule, once a cube or a component of a cube is created on the Analysis Services server it cannot be altered through QAD Data Warehouse Designer. The OLAP object can be dropped and recreated easily using Data Warehouse Designer. New OLAP objects defined in Data Warehouse Designer (e.g. additional calculations or measures) can be added by recreating the cube.

QAD Data Warehouse Designer supports cubes in Microsoft Sql Server Analysis Services versions 2005 and 2008.

OLAP Cubes can be created from fact, fact view or aggregate objects. A single cube can contain dates from multiple source star schemas, each defined with a measure group. An OLAP Cube consists of many parts namely, measure groups, measures, calculations, actions, dimensions, dimension hierarchies, dimension attributes and dimension attribute relationships. It is strongly recommended that drag and drop is used to create an OLAP Cube in order that all the components are set up correctly. OLAP Cubes can utilize a hierarchical structure in the dimensions to facilitate user queries. Therefore each dimension present in an OLAP Cube should have either a hierarchy of levels or attributes and relationships. The hierarchies are defined against the underlying dimensional attributes which can be inherited from the source dimension metadata. Individual attributes can be added to the dimension after the OLAP Cube or OLAP Dimension metadata has been created.