I have a MDX Query that when I run it in Management Studio or BIS (Reporting Services) I Get one set of results. If however I run it via OPENROWSET I get diffrent results. The major diffrence is that via the OPENROWSET I get the hierrachy and not the members as with the MDX query. The field That I query is a Hierarchy diemension. How can I get the same reults from OPENROWSET as from Management Studio.
MDX Query: MDX (Against Adventure Works)
SQL With Openrowset against the same cube.
this happens as you are getting members of a multi-level hierchy
In case of MDX query a tuples are retrieved and particular tuple uniquely identifies the node (there are retrieved also additional properties etc) so you see only the names listed, but by double click on the cell you can see other properties like hierarchy level etc.
In case of OPENROWSET, the hierarchy is flattened and each level of hierarchy is represented by separate column. In this way you see the whole path to the leaf node in the flattened element.
If you want to see only the names of leaf nodes, you can collapse the result into single column using SQL
It will give you results like below: