multidimensional expression (BI) (MDX)
Multidimensional Expression (MDX) is a query programming language for multidimensional structures as described by the OLAP cube. The MDX query language was essentially developed by Mosha Pasumansky and further developed by Microsoft.
Multidimensional Expression (MDX) as a DB language for multidimensional databasesis visually based on the well-known database programming languageSQL, but has its own syntax, which SQL programmers (precisely because of its superficially similar syntax) initially need to get used to. The differences stem, among other things, from the fact that SQL only ever queries data records, i.e. in principle one-dimensional lists with several values per line (with a fixed structure).
In MDX, on the other hand, multi-dimensional structures, so-called OLAP cubes, are queried, analogous to the principle of a pivot table. The latter are then often also the representation type of MDX queries used, e.g. in MS Excel or Open Office Calc. Therefore, MDX usually uses only the two axes of a pivot table plus possible filters, although up to 128 axes are possible.
The notion of a set in MDX is different from SQL. MDX distinguishes between tuples and sets. The former is a construct of elements of different attributes, the latter consists of several elements of one attribute or several tuples structured in the same way. For example, a set can contain all months of a year, a tuple contains e.g. the current month and a product line. The current month alone can be represented as a tuple, as a set as well as an element (as a so-called member). The term attribute is used here partly synonymous to the usual use of the term dimension.
The main advantage of using Multidimensional Expression is the extended selectability over multidimensional structures and the relative calculations (percent, previous periods, year-to-date/month-to-date, ). Disadvantages are the less intuitive syntax, the rather confusing similarity to SQL, the application possibilities limited to OLAP structures and the lack of support for the Data Definition Language( DDL).
MDX is therefore by far not as widespread as SQL. Nevertheless, there are some well-known vendors that use MDX. The best known tools are Microsoft SQL Server Analysis Services and Oracle Hyperion Essbase.