MDX VS MDX (MSOLAP) OPenrowset

Question

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)

WITH MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUE_NAME
SELECT { [Measures].[UniqueName]} ON COLUMNS,
{ [Employee].[Employees].Members} ON ROWS
FROM [Adventure Works]

**Results**
    UniqueName
All Employees   [Employee].[Employees].[All Employees]
Ken J. Sánchez  [Employee].[Employees].&[112]
Brian S. Welcker    [Employee].[Employees].&[277]
Amy E. Alberts  [Employee].[Employees].&[290]
Jae B. Pak  [Employee].[Employees].&[291]
Rachel B. Valdez    [Employee].[Employees].&[295]
Ranjit R. Varkey Chudukatil [Employee].[Employees].&[292]
Stephen Y. Jiang    [Employee].[Employees].&[272]

SQL With Openrowset against the same cube.

Select 
   *
From OpenRowset('MSOLAP','Datasource=Local;Initial Catalog=Adventure Works DW 2008R2'
, 'WITH MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUE_NAME
SELECT { [Measures].[UniqueName]} ON COLUMNS,
{ [Employee].[Employees].Members} ON ROWS
FROM [Adventure Works]')

Results

[Employee].[Employees].[Employee Level 02].[MEMBER_CAPTION] [Employee].[Employees].[Employee Level 03].[MEMBER_CAPTION] [Employee].[Employees].[Employee Level 04].[MEMBER_CAPTION] [Employee].[Employees].[Employee Level 05].[MEMBER_CAPTION] [Employee].[Employees].[Employee Level 06].[MEMBER_CAPTION] [Measures].[UniqueName]
NULL    NULL   NULL  NULL NULL    [Employee].[Employees].[All Employees]
Ken J. Sánchez  NULL NULL    NULL   NULL  [Employee].[Employees].&[112]
Ken J. Sánchez  Brian S. Welcker NULL    NULL   NULL  [Employee].[Employees].&[277]
Ken J. Sánchez  Brian S. Welcker Amy E. Alberts  NULL NULL    [Employee].[Employees].&[290]
Ken J. Sánchez  Brian S. Welcker Amy E. Alberts  Jae B. Pak   NULL  [Employee].[Employees].&[291]
Ken J. Sánchez  Brian S. Welcker Amy E. Alberts  Rachel B. Valdez NULL    [Employee].[Employees].&[295]
Ken J. Sánchez  Brian S. Welcker Amy E. Alberts  Ranjit R. Varkey Chudukatil  NULL [Employee].[Employees].&[292]
Ken J. Sánchez  Brian S. Welcker Stephen Y. Jiang    NULL   NULL  [Employee].[Employees].&[272]

Results:

Answer 1

Hi,

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 COALESCE function like in this example:

Select 
   COALESCE(
        "[Employee].[Employees].[Employee Level 06].[MEMBER_CAPTION]",
        "[Employee].[Employees].[Employee Level 05].[MEMBER_CAPTION]",
        "[Employee].[Employees].[Employee Level 04].[MEMBER_CAPTION]",
        "[Employee].[Employees].[Employee Level 03].[MEMBER_CAPTION]",
        "[Employee].[Employees].[Employee Level 02].[MEMBER_CAPTION]",
        'All Employees') AS EmployeeName,
        "[Measures].[UniqueName]" AS MemberUniqueName
From OpenRowset('MSOLAP','Datasource=Local;Initial Catalog=Adventure Works DW 2008R2',
'WITH
    MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUE_NAME
SELECT
    { [Measures].[UniqueName]} ON COLUMNS,
    { [Employee].[Employees].Members} ON ROWS
FROM [Adventure Works]')

It will give you results like below:

EmployeeName                        MemberUniqueName
----------------------------------- ---------------------------------------
All Employees                       [Employee].[Employees].[All Employees]
Ken J. Sánchez                      [Employee].[Employees].&[112]
Brian S. Welcker                    [Employee].[Employees].&[277]
Amy E. Alberts                      [Employee].[Employees].&[290]
Jae B. Pak                          [Employee].[Employees].&[291]
Rachel B. Valdez                    [Employee].[Employees].&[295]
Ranjit R. Varkey Chudukatil         [Employee].[Employees].&[292]
....
....
....
Ovidiu V. Cracium                   [Employee].[Employees].&[267]
Janice M. Galvin                    [Employee].[Employees].&[269]
Thierry B. D'Hers                   [Employee].[Employees].&[6]
Rob Walters                         [Employee].[Employees].&[4]
Rob Walters                         [Employee].[Employees].&[5]
Sharon B. Salavaria                 [Employee].[Employees].&[274]

MDX VS MDX (MSOLAP) OPenrowset

Follow this question

Related Questions