MD (multidimensional) structures involve 1, 2, 3, 4, or more dimensions, whereas relational structures involve 2 dimensional tables and columns. In MD structures, a particular subject (called a cube) is looked at from different perspectives (called dimensions). Each dimension may have a hierarchy of aggregates. Each step in the hierarchy is called a level. The upper levels aggregate lower level data, while lower levels segregate upper level data. A chunk of data within a level is a called a member. EG: In the 2000 Wally Sales cube, what were the sales for Time (1999, May, 15) at Store (IL, Chicago, 60647, Will E. Coyote Goods) for Product (Acme, rockets, A1 Turbo)?
Data is placed into an MD structure by an ETL (Extract, Transform, Load) Process.
Here are the common MD structures:
- A Data Warehouse or Mart is simply a relational database where data is structured for MD analysis. The difference between a data warehouse and a data mart is that the former are larger and more centralized, whereas the latter are smaller and more localized.
- A Cube takes the data from a data warehouse or data mart and pre-aggregates (processes) the data for all the dimensions so that MD analysis can occur quickly.
Once you have a MD structure it needs to under go MD Analysis. OLAP (On-Line Analytical Processing) is the presentation of data in MD structures, whereas OLTP (On-Line Transactional Processing) is the use and presentation of data in most relational structures. Data mining is sort of an extension of OLAP that predicts and trends data, whereas plain old OLAP looks at data as is.
MDX (MD Expressions) are used to query a MD structure, just as SQL is used to query a relational structure.
ADO MD (ActiveX Data Objects MD) is used to programmatically access a MD structure and the query results, just as ADO is used to programmatically access a relational structure and the query results.
A typical query to a MD structure yields a cell set with 2-4 axes, whereas a query to a relational structure yields a result set with 2 axes, i.e. a 2D table. An axis may consist of multiple dimensions, each of which may be drilled down to specified various levels. A position may be specified along each axis by specifying the particular dimensions and members along an axis for a cell. A cell is the intersection of the axes as denoted by its position. Each cell can yield particular values called measures. In addition to the primary data of each measure, a cell has other data including a formatted version of the data, and the ordinal value of the cell.
- XMLA (XML for Analysis) is like OLEDB but improved to allow open access to multidimensional databases from any platform.
- mdXML is an access language just like MDX but mdXML takes advantage of XML and is program language independent.