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 2: / Line 2: @@
 [[Category:Doc Status C]] <!-- For Lumina use, do not change -->
-[[What's new in Analytica 4.0?]] >
+''(Requires Analytica Enterprise)''
-= MdxQuery(connectionString, mdx) =
+== 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.)
-'''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]]().
-'''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.
+«mdx» is text containing the query in the MDX language.
-To learn about the MDQ 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]
+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]
-(Requires Analytica Enterprise)
+[[Syntax]]:
+:[[MdxQuery]](connectionString, mdx: Text; userId, password, sep: Optional Text)
-;[[Syntax]]: MdxQuery(connectionString, mdx: Text; userId, password, sep: Optional Text)
-= Example =
+== Example ==
 This query retrieves a 3-D result, since the MDX query specifies 3 axes.
- Constant Database := "Adventure Works DW";
+:<code>Constant Database := "Adventure Works DW";</code>
- Constant MdxDB_connection:= "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI;Database="&Database
+:<code>Constant MdxDB_connection := "Data Source = local; Provider = MSOLAP; Integrated Security = SSPI; Database = "&Database</code>
- Variable MdxResult :=
+:<code>Variable MdxResult :=</code>
-    MdxQuery(connectionStr: MdxDB_connection,
+::<code>MdxQuery(connectionStr: MdxDB_connection,</code>
-      mdx: "SELECT ([Customer].[English Occupation].[English Occupation],
+:::<code>mdx: "SELECT ([Customer].[English Occupation].[English Occupation],</code>
-       {[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),
+::::<code>{[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),</code>
-       ([Due Date].[Calendar Year].[Calendar Year],
+::::<code>([Due Date].[Calendar Year].[Calendar Year],</code>
-       [Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),
+::::<code>[Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),</code>
-       [Customer].[Marital Status] on axis(2)
+::::<code>[Customer].[Marital Status] on axis(2)</code>
-       FROM [Adventure Works DW]" )
+::::<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 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.
+== Dimensions and Indexes ==
+'''MdxQuery()''' creates a local index for each dimension. The local indexes are named ''.Axis1, .Axis2, .Axis3'', etc.
-= Writeback =
+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>.
-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.
+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.
-= UserID and Password =
+== 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 =
+== 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.
-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.
-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 ===
-== Errors returned by the OLE DB Provider ==
+These messages are prefixed by:  <code>''In MdxQuery: Errors in the OLE DB provider.''</code>
-These messages are prefixed by:  ''In MdxQuery: Errors in the OLE DB provider.''
+;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>.
-* "''The Extended Properties property is set to a value that is not supported''."
+;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.
-:'''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=".
+;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 in the OLE DB provider.  The data source or location is not valid.  Provide a valid server to which to connect."
+=== Errors returned from the ODBC Driver Manager ===
+These errors are prefixed by: <code>''[Microsoft][ODBC Driver Manager]''</code>
-:'''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.
+;Error message: <code>''Provider cannot be found.  It may not be properly installed.''</code>
-:'''Resolution:''' Specify a valid Data Source on the connection string.
+;Possible Cause: You have a typo in the <code>Provider</code> property in your connection string.
-:'''Resolution:''' Ensure that your OLAP server (e.g., the ''Analysis Services'' service) is running.
+;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.
-* "''Either the user, <<user name>>, does not have access to the <<database>> database, or the database does not exist.
+;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.
-: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:
+=== Errors from the OLAP Server while processing ===
-:'''Possible resolution''': Try including "Integrated Security=SSPI" in the connection string.
+;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.
-== Errors returned from the ODBC Driver Manager ==
+=== MDX errors ===
-These errors are prefixed by: ''[Microsoft][ODBC Driver Manager]''
+New in Analytica 4.0.
-* "''Provider cannot be found.  It may not be properly installed.''"
+==See Also==
+* [[DbQuery]]
-:'''Possible Cause:''' You have a typo in the Provider property in your connection string.
+* [[MdTable]]
-:'''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.
+* [[Database functions]]
+* [[Database access]]
-* "''Data source name not found and no default driver specified''"
+* [[OLE linking]]
-:'''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 ==
+<footer>Database functions / Accessing a multidimensional or OLAP database with MDX / COM Automation Objects</footer>