Difference between revisions of "MdxQuery"
(Rewrite)
Line 3: Line 3:
 
[[What's new in Analytica 4.0?]] >
 
[[What's new in Analytica 4.0?]] >
  
−
Queries an OLAP server database using an MDX query.
+
=== MdxQuery(connectionString, mdx) ===
  
−
(Requires Analytica Enterprise)
+
'''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.)
  
−
== Declaration ==
+
'''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.
  
−
MdxQuery( connectionString, mdx : atomic textual ; userId, password, sep : optional atomic textual )
+
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]
  
−
== Description ==
+
(Requires Analytica Enterprise)
  
−
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.
+
;[[Syntax]]: MdxQuery(connectionString, mdx: Atomic Text; userId, password, sep: Optional Atomic Text)
  
−
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 ====
  
−
== Example Usage ==
+
This query retrieves a 3-D result, since the MDX query specifies 3 axes.
  
−
  Constant database := "Adventure Works DW";
+
  Constant Database := "Adventure Works DW";
−
  Constant connStr := "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI"
+
  Constant MdxDB_connection:= "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI"
−
  Variable mdx :=
+
  Variable MdxResult :=
−
     MdxQuery( connectionStr : connStr, mdx :
+
     MdxQuery(connectionStr: MdxDB_connection,  
−
    "SELECT ([Customer].[English Occupation].[English Occupation],
+
      mdx: "SELECT ([Customer].[English Occupation].[English Occupation],
−
    {[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),
+
      {[Measures].[Sales Amount],[Measures].[Freight]}) on axis(0),
−
    ([Due Date].[Calendar Year].[Calendar Year],
+
      ([Due Date].[Calendar Year].[Calendar Year],
−
    [Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),
+
      [Due Date].[Calendar Quarter].[Calendar Quarter]) on axis(1),
−
    [Customer].[Marital Status] on axis(2)
+
      [Customer].[Marital Status] on axis(2)
−
    FROM [Adventure Works DW]" )
+
      FROM [Adventure Works DW]" )
  
−
This query retrieves a 3-D result, since the MDX query specifies 3 axes.
+
==== Dimensions and Indexes ====
 +
 
 +
'''MdxQuery()''' creates a local index for each dimension. The local indexes are named .Axis1, .Axis2, .Axis3, etc.  
  
−
== Connection String ==
+
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.
  
−
== Dimension Handling ==  
+
==== Writeback ====
  
−
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.
+
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.
  
−
== Writeback ==
+
==== UserID and Password ====
  
−
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.
+
These are optional parameters, sometimes needed for access to the OLAP database.
  
−
== MdxQuery Errors ==
+
==== MdxQuery Errors ====
  
 
(TBD: List errors that can result, and what they mean)
 
(TBD: List errors that can result, and what they mean)

Revision as of 18:57, 8 March 2007


What's new in Analytica 4.0? >

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)

Comments


You are not allowed to post comments.