x

MDX VS MDX (MSOLAP) OPenrowset

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:
more ▼

asked Jan 30 '12 at 01:03 AM in Default

dfswart gravatar image

dfswart
26 2 2 2

Oh....@dfswart Please trim your results. I hope there is no real data you have posted. If there is some, then replace it with some test data OR by replacing the real data with XYZ OR ABC etc.
Jan 30 '12 at 01:28 AM Usman Butt
I guess a better way could have been by adding an image.
Jan 30 '12 at 01:35 AM Usman Butt

The results from the OPENROWSET is as follow: [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]

From the Management studio Query:

Employee Name 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]

The data is from Adventure Works.

Jan 30 '12 at 01:37 AM dfswart
I will replace the results in your question with this one. I hope there is no problem with that.
Jan 30 '12 at 01:40 AM Usman Butt
Moreover, can you please pinpoint the major difference?
Jan 30 '12 at 01:41 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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]
more ▼

answered Jan 30 '12 at 08:30 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x341
x62
x18
x9

asked: Jan 30 '12 at 01:03 AM

Seen: 1735 times

Last Updated: Jan 31 '12 at 10:15 AM