Difference between revisions of "MdxQuery"

Latest revision as of 00:10, 8 August 2017

(Requires Analytica Enterprise)

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 MDX language, you can consult Microsoft overview of MDX or Intro to MDX in Database Journal

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.

Error message: 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.
Resolution: Depending on the OLAP server you are using, perhaps you need Database = or Default Database = .

Error message: 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.

Error message: 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]

Error message: 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.

Error message: 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

Error message: 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

New in Analytica 4.0.

@@ Line 1: / Line 1: @@
-= MdxQuery =
+[[Category:Database Functions]]
+[[Category:Doc Status C]] <!-- For Lumina use, do not change -->
+''(Requires Analytica Enterprise)''
-Queries an OLAP server database using an MDX query.
+== 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.)
-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:
+«connectionStr» is the standard text used to identify and connect with the database, similar to that used in other database functions, such as [[DbQuery]]().
-MdxQuery( connectionString, mdx : atomic textual ; userId, password, sep : optional atomic textual )
+«mdx» is text containing the query in the MDX language.
-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.
+To learn about the MDX language, you can consult [http://msdn2.microsoft.com/en-us/library/ms145506.aspx Microsoft overview of MDX] or [http://www.databasejournal.com/features/mssql/article.php/10894_1495511_3 Intro to MDX in Database Journal]
-An example usage would be:
+[[Syntax]]:
+:[[MdxQuery]](connectionString, mdx: Text; userId, password, sep: Optional Text)
- Constant database := "Adventure Works DW";
+== Example ==
- Constant connStr := "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI"
+This query retrieves a 3-D result, since the MDX query specifies 3 axes.
- Variable mdx :=
-    MdxQuery( connectionStr : connStr, mdx :
+:<code>Constant Database := "Adventure Works DW";</code>
-    "SELECT ([Customer].[English Occupation].[English Occupation],
+:<code>Constant MdxDB_connection := "Data Source = local; Provider = MSOLAP; Integrated Security = SSPI; Database = "&Database</code>
-    {[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),
+:<code>Variable MdxResult :=</code>
-    ([Due Date].[Calendar Year].[Calendar Year],
+::<code>MdxQuery(connectionStr: MdxDB_connection,</code>
-    [Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),
+:::<code>mdx: "SELECT ([Customer].[English Occupation].[English Occupation],</code>
-    [Customer].[Marital Status] on axis(2)
+::::<code>{[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),</code>
-    FROM [Adventure Works DW]" )
+::::<code>([Due Date].[Calendar Year].[Calendar Year],</code>
+::::<code>[Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),</code>
+::::<code>[Customer].[Marital Status] on axis(2)</code>
+::::<code>FROM [Adventure Works DW]")</code>
+== 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 <code>Axis(0)</code> and <code>Axis(1)</code> in the example above, are hierarchical. <code>Due Date, Axis(1)</code>, includes Calendar year and quarter. For these, [[MdxQuery]] concatenates the member captions. For example, an element of <code>Due Date</code>, on <code>Axis(1)</code>, would be <code>"2003, 1"</code> -- i.e., <code>[Year]</code> is <code>2003</code> and <code>[Quarter]</code> is <code>1</code>.
+The optional parameter «sep» controls the separator between the hierarchy levels. It uses comma by default.  If MdxQuery used parameter, <code>sep:" Q"</code>, you would get elements <code>"2003 Q1"</code> 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.
-This query retrieves a 3-D result, since the MDX query specifies 3 axes.
+== 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:  <code>''In MdxQuery: Errors in the OLE DB provider.''</code>
+;Error message:<code>''The Extended Properties property is set to a value that is not supported.''</code>
+;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 <code>"Data Source = local; Provider = MSOLAP; My Database = X"</code>, the MSOLAP server did not recognize the property <code>My Database</code>.
+;Resolution: Depending on the OLAP server you are using, perhaps you need <code>Database = </code> or <code>Default Database = </code>.
+;Error message:<code>Errors in the OLE DB provider.  The data source or location is not valid.  Provide a valid server to which to connect.''</code>
+;Possible Cause: The <code>Data Source=...</code> 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 <code>Analysis Services</code> service) is running.
+;Error message:<code>''Either the user, <<user name>>, does not have access to the <<database>> database, or the database does not exist.''</code>
+: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 <code>Integrated Security = SSPI</code> in the connection string.
+=== Errors returned from the ODBC Driver Manager ===
+These errors are prefixed by: <code>''[Microsoft][ODBC Driver Manager]''</code>
+;Error message: <code>''Provider cannot be found.  It may not be properly installed.''</code>
+;Possible Cause: You have a typo in the <code>Provider</code> 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.
+;Error message: <code>''Data source name not found and no default driver specified.''</code>
+;Possible Cause: You haven't to specified the <code>Provider</code> property in your connection string.
+=== Errors from the OLAP Server while processing ===
+;Error message: <code>''Multi-step OLE DB operation generated errors.  Check each OLE DB status value, if available.  No work was done.''</code>
+: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 <code>Integrated Security</code> property is not recognized by the OLAP provider.
+=== MDX errors ===
-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.
+New in Analytica 4.0.
-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.
+==See Also==
+* [[DbQuery]]
+* [[MdTable]]
+* [[Database functions]]
+* [[Database access]]
+* [[OLE linking]]
-Additional Detailed Information:
+<footer>Database functions / Accessing a multidimensional or OLAP database with MDX / COM Automation Objects</footer>
-* [[Connection Strings]]
-* [[MdxQuery Errors]] and what they mean