OLAP cube
|
|
In database theory, an OLAP cube is an abstract representation of a projection of an RDBMS relation. Given a relation of order N, consider a (hopefully PK-preserving) projection that subtends X, Y, and Z as the key and W as the residual attribute. Characterizing this as a function,
- W : (X,Y,Z) → W,
the attributes X, Y, and Z correspond to the axes of the cube, while the W value into which each ( X, Y, Z ) triple maps corresponds to the data element that populates each cell of the cube.
Insofar as two-dimensional output devices cannot readily characterize four dimensions, it is more practical to project "slices" of the data cube (we say project in the classic vector analytic sense of dimensional reduction, not in the SQL sense, although the two are clearly conceptually homologous), perhaps
- W : (X,Y) → W
which, if not PK-preserving, may have some semantic significance nevertheless, perhaps a slice of the triadic functional representation for a given Z value of interest.
The motivation behind OLAP displays harks back to the cross-tabbed report paradigm of 1980s DBMS. One may wish for a spreadsheet-style display, where—to appropriate the Microsoft Excel paradigm—values of X populate row $1; values of Y populate column $A; and values of W : ( X, Y ) -> W populate the individual cells "southeast of" $B2, so to speak, $B2 itself included. While one can certainly use the DML of traditional SQL to display ( X, Y, W ) triples, this output format is not nearly as convenient as the cross-tabbed alternative: certainly, the former requires one to hunt linearly for a given ( X, Y ) pair in order to determine the corresponding W value, while the latter enables one to arguably more conveniently scan for the intersection of the proper X column with the proper Y row.
While the MDX (Multidimensional Expressions) language has been developed as a facile means for expressing OLAP problems, it is possible to translate the lion's share of these into traditional SQL, albeit frequently requiring the synthesis of clumsy expressions that rely heavily upon JOIN and UNION formulations liberally besprinkled with NULL values. Vendors have been slow—even altogether reluctant—to integrate MDX facilities with SQL at the lowest possible programmatic layer, e.g., the procedural API: Microsoft, for example, offers MDX only in the context of the stand-alone Data Analysis Services (DAS) tool that constructs an ornate GUI form governing a rectangular grid that it ultimately populates with the requested data tuples. There is no facility for incorporating MDX syntax into, say, DECLARE CURSOR or CREATE VIEW and, attendantly, no means for executing MDX queries and feeding the returned data into interactive data visualization (IDV) tools (such as Corda PopChart) that, after all, should not be stymied by any rectangular representation of data (surely—hierarchical column groupings and other unique MDX artifacts aside—a set of rigidly structured, domain-semantics-obedient tuples returned by an MDX query is no different in principle than a set of tuples returned by an SQL query)—but, in fact, are.de:Cube (OLAP)
fr:Hypercube OLAP
ru:OLAP куб