MdxQuery

What's new in Analytica 4.0? >

Queries an OLAP server database using an MDX query.

(Requires Analytica Enterprise)

Declaration

MdxQuery( connectionString, mdx : atomic textual ; userId, password, sep : optional atomic textual )

Description

While DbQuery can query relational database servers, you can't use it to query SQL Server's Analysis Services to access data stored in an OLAP hypercube. For this, MdxQuery is required.

This function queries an OLAP server such as Microsoft Analysis Services (or theoretically others such as Hyperion, etc), using an arbitrary MDX query, and returns a multi-D array.

Example Usage

Constant database := "Adventure Works DW";
Constant connStr := "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI"
Variable mdx :=
   MdxQuery( connectionStr : connStr, mdx :
   "SELECT ([Customer].[English Occupation].[English Occupation],
   {[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),
   ([Due Date].[Calendar Year].[Calendar Year],
   [Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),
   [Customer].[Marital Status] on axis(2)
   FROM [Adventure Works DW]" )

This query retrieves a 3-D result, since the MDX query specifies 3 axes.

Dimension Handling

The MdxFunction creates local indexes for each dimension, and uses the member captions as labels for the elements in each axis. The local indexes are named .Axis1, .Axis2, .Axis3. With a simple single-dimension MDX axis, the index simply contains the member captions as elements. However, some cube axes, such as Axis(0) and Axis(1) in the example MDX query are hierarchical. For these, MdxQuery concatenates the member captions, so you might get an element such as: "2003,1" for Axis(1) -- i.e., [Year] is 2003 and [Quarter] is 1, so the Analytica element is "2003,1". The sep parameter controls the separator (comma by default), so using sep:"|" you would get elements "2003|1" instead, for example.

Writeback

MdxQuery can be used to read or write data to the hypercube, depending on the Mdx command used. The method in which writes are handled is a function of the OLAP server. Microsoft Analysis Services uses a write-back table, which changes the apparent cube contents if the cube is requeried, but does not change the back-end relational data tables. Some write-backs are temporary (live only as long as the session), while other change the cube permanently.

MdxQuery Errors

(TBD: List errors that can result, and what they mean)