Difference between revisions of "MdxQuery"
m (OLAP and Database Functions moved to Ana:Fn:MdxQuery: Moved to Ana:Fn namespace) |
|||
| Line 3: | Line 3: | ||
Queries an OLAP server database using an MDX query. | Queries an OLAP server database using an MDX query. | ||
| − | + | == Declaration == | |
| − | MdxQuery( connectionString, mdx : atomic textual ; userId, password, sep : optional atomic textual ) | + | MdxQuery( connectionString, mdx : atomic textual ; userId, password, sep : optional atomic textual ) |
| + | |||
| + | == Description == | ||
| + | |||
| + | 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. | ||
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. | 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 Usage == | |
Constant database := "Adventure Works DW"; | Constant database := "Adventure Works DW"; | ||
| Line 23: | Line 27: | ||
This query retrieves a 3-D result, since the MDX query specifies 3 axes. | This query retrieves a 3-D result, since the MDX query specifies 3 axes. | ||
| + | |||
| + | == Connection String == | ||
| + | |||
| + | == Dimension Handling == | ||
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. | 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. | ||
| + | |||
| + | == Writeback == | ||
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. | 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. | ||
| − | + | == MdxQuery Errors == | |
| − | + | ||
| − | + | (TBD: List errors that can result, and what they mean) | |
Revision as of 16:46, 24 January 2007
MdxQuery
Queries an OLAP server database using an MDX query.
Declaration
MdxQuery( connectionString, mdx : atomic textual ; userId, password, sep : optional atomic textual )
Description
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.
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 Usage
Constant database := "Adventure Works DW";
Constant connStr := "Data Source=local;Provider=MSOLAP;Integrated Security=SSPI"
Variable mdx :=
MdxQuery( connectionStr : connStr, 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]" )
This query retrieves a 3-D result, since the MDX query specifies 3 axes.
Connection String
Dimension Handling
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.
Writeback
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.
MdxQuery Errors
(TBD: List errors that can result, and what they mean)
Enable comment auto-refresher