In this article we will be developing a MDX Query Builder.This is similar to the MDX sample application which comes along the analysis services SQL 2002.ADOMD is used to get the meta data of their cubes.
Multidimensional Expressions (MDX) is the language that Microsoft included with OLAP Services to allow access to OLAP data from client applications. MDX is used to describe multidimensional queries.
The general syntax for an MDX statement is(it can get more complex when used to define calculated members,filters and order):
SELECT <member selection > on axis1, < member selection > on axis 2, ..
FROM <cube name > where <Slicer >
For example:
select { [Measures].[Profit]} on columns,[Product].[All Products].[Drink].[Alcoholic Beverages].children on rows from sales where ( [Store].[All Stores].[USA])
ADOMD
Cube schemas are queried through the Catalog branch and cube data are queried through the Cellset branch.
In this article the cubes metadata is queried using the catalog.The MDX query generated is tested using the Cellset.
A cube has dimensions,dimensions have levels,levels have members and members may have child members.
Measure is a special type of dimension.
The tree is created on demand,because it will take more time to retreive the complete metadata of the cube.
In most of the functions we will return an arraylist of strings which is in the following format UniqueName~Name~HasChildren
This arraylist is used to populate the tree.