Equivalent of DMX and MDX in Oracle

Question

All,

I'm starting to learn OLAP & Data Mining using SQLServer 2008/2012 and I was wondering out of curiosity what the equivalent of DMX and MDX would be in the Oracle/DB2/Sybase world.

Thanks,

ms

Answer 1

Hi,

I am not an Oracle expert, but based on my research, it appears that Oracle cubes are accessible via ansii-standard SQL. Please see below.

http://www.orafaq.com/wiki/Oracle_OLAP

Oracle Database OLAP Option's cubes are full-blown multidimensional structures and support calculations that are difficult, complex, or even impossible to define using standard SQL. Other, standalone multidimensional databases on the market require specialized multidimensional query languages to access their data and leverage their calculation engines - most are proprietary, though in recent years a Microsoft language called MDX (MultiDimensional eXpressions)has become popular tho each vendor has ended up coding to different versions of MDX as there is no public published 'standard' for MDX. The multidimensional OLAP cubes in Oracle Database however are accessible via simple ansii-standard SQL. This is a significant development.

Announced at Oracle Open World 2009, Oracle Database OLAP cubes are now also accessible via MDX. MDX is a language popular with standalone multidimensional databases, and for which a number of BI tools and applications exist. In particular Microsoft is investing heavily in 'cube enabling' Microsoft Office (especially Excel) with MDX. Thus, Excel becomes a client to the cubes. With the MDX Provider (available from Oracle Partner and MDX specialists, Simba Technologies), these MDX tools can now leverage the power and superior architecture of Oracle Database OLAP as an alternative to the architecture of separate specialist cubes required by other vendors. The 'version' of MDX that is used by this solution is equivalent to that used by Microsoft Excel to communicate with Microsoft Analysis Services (Microsoft's OLAP database which is sold with SQL Server).

---

Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Answer 2

Hi ms,

Multidimensional Expressions (MDX) is the query language that you use to work with and retrieve multidimensional data in Microsoft Analysis Services. MDX is based on the XML for Analysis (XMLA) specification, with specific extensions for SQL Server Analysis Services. MDX utilizes expressions composed of identifiers, values, statements, functions, and operators that Analysis Services can evaluate to retrieve an object (for example a set or a member), or a scalar value (for example, a string or a number). For more details about it, please see: http://msdn.microsoft.com/en-us/library/bb500184(v=sql.100).aspx

Data Mining Extensions (DMX) is a language that you can use to create and work with data mining models in Microsoft SQL Server Analysis Services. You can use DMX to create the structure of new data mining models, to train these models, and to browse, manage, and predict against them. DMX is composed of data definition language (DDL) statements, data manipulation language (DML) statements, and functions and operators. For more detaisl about it, please see: http://msdn.microsoft.com/en-us/library/ms132058.aspx

Thanks,
Eileen