question

dfswart avatar image
dfswart asked

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:
sql-servermdxolapopenrowset
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
I guess a better way could have been by adding an image.
0 Likes 0 ·
**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.
0 Likes 0 ·
I will replace the results in your question with this one. I hope there is no problem with that.
0 Likes 0 ·
Moreover, can you please pinpoint the major difference?
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.