MdxQuery

MdxQuery(connectionString, mdx)

MdxQuery lets you read or write multidimensional data on an OLAP server database, returning or sending a multidimensional Analytica array. It uses the standard query language, MDX. MDX is analogous to SQL, but where SQL accesses any standard relational database, MDX accesses multidimensional "hypercube" databases. MdxQuery() works with Microsoft SQL Server Analysis Services. (It will probably also work with other OLAP servers, such as Hyperion, but has not yet been tested.)

connectionStr is the standard text used to identify and connect with the database, similar to that used in other database functions, such as DBQuery(). mdx is text containing the query in the MDX language.

To learn about the MDQ language, you can consult Microsoft overview of MDX or Intro to MDX in Database Journal

(Requires Analytica Enterprise)

Syntax: MdxQuery(connectionString, mdx: Atomic Text; userId, password, sep: Optional Atomic Text)

Example

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

Constant Database := "Adventure Works DW";
Constant MdxDB_connection:= "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI"
Variable MdxResult :=
   MdxQuery(connectionStr: MdxDB_connection, 
     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]" )

Dimensions and Indexes

MdxQuery() creates a local index for each dimension. The local indexes are named .Axis1, .Axis2, .Axis3, etc.

With a simple MDX axis, the index contains the member captions as its elements. Some cube axes, such as Axis(0) and Axis(1) in the example above, are hierarchical. Due Date, Axis(1),includes Calendar year and quarter. For these, MdxQuery concatenates the member captions. For example, an element of Due Date, on Axis(1), would be "2003,1" -- i.e., [Year] is 2003 and [Quarter] is 1. The optional parameter sep controls the separator between the hierarchy levels. It uses comma by default. If MdxQuery used parameter, sep:" Q", you would get elements "2003 Q1" instead.

Writeback

You can use MdxQuery to write data to the hypercube, as well as read data. depending on the MDX command. 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 it does not change the back-end relational data tables. Some write-backs are temporary -- i.e., live only as long as the session -- while others change the cube permanently.

UserID and Password

These are optional parameters, sometimes needed for access to the OLAP database.

MdxQuery Errors

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