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 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: Text; userId, password, sep: Optional 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;Database="&Database
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

List errors that can result, and what they mean. Most errors encountered using MdxQuery are returned from the OLAP service provider, and are often quite cryptic and hard to figure out. This section provides a place to sort this out using the collective wisdom of our users.

Note: If you encounter an error not listed here, or whose resolution was difficult, and you have now figured out what it means and how it was resolved, please contribute your wisdom here.

Errors returned by the OLE DB Provider

These messages are prefixed by: In MdxQuery: Errors in the OLE DB provider.

"The Extended Properties property is set to a value that is not supported."

Possible Cause: One of the fields named in the connection string is not recognized by your OLAP service provider. For example, in the connection string "Data Source=local;Provider=MSOLAP;My Database=X", the MSOLAP server did not recognize the property "My Database". Depending on the OLAP server you are using, perhaps you need "Database=" or "Default Database=".

"Errors in the OLE DB provider. The data source or location is not valid. Provide a valid server to which to connect."

Possible Cause:" The "Data Source=..." specified on your connection string is invalid or missing, or there is no OLAP server running at the indicated location.

Resolution: Specify a valid Data Source on the connection string.

Resolution: Ensure that your OLAP server (e.g., the Analysis Services service) is running.

"Either the user, <<user name>>, does not have access to the <<database>> database, or the database does not exist.

Database security and access configuration is quite involved, and you may need to consult a book on the topic. However, note this potential easy resolution to use Windows authentication:

Possible resolution: Try including "Integrated Security=SSPI" in the connection string.

Errors returned from the ODBC Driver Manager

These errors are prefixed by: [Microsoft][ODBC Driver Manager]

"Provider cannot be found. It may not be properly installed."

Possible Cause: You have a typo in the Provider property in your connection string.

Possible Cause: You don't have an Analysis Services client, or the OLAP client specified by the Provider property in your connection string, installed on your computer.

"Data source name not found and no default driver specified"

Possible Cause: You haven't to specified the Provider property in your connection string.

Errors from the OLAP Server while processing

"Multi-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

The OLE DB status values are not available from within Analytica. This message most likely indicates a basic problem with the connection string, probably an unrecognized value in one of the fields.

Possible Cause: The indicated value for the Integrated Security property is not recognized by the OLAP provider.

MDX errors