SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieving the ALL member only


Retrieving the ALL member only

Author
Message
aarded
aarded
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 314
Hi,

I have a problem with retrieving the ALL member.

With MDX executed from SSMS, it returns the 'All' value into a column, but executed from SQL with linked server, it doesn't return a column with 'All'.

MDX:
SELECT NON EMPTY( { [Customer].[Customer ID].[ALL] }) ON ROWS,
{[Measures].[Fact Count] } ON COLUMNS
FROM [SalesCube]
--> Result OK, 2 columns.

SQL:
SELECT * FROM OPENQUERY(OLAP_DWH,
'SELECT NON EMPTY( { [Customer].[Customer ID].[ALL] }) ON ROWS,
{[Measures].[Fact Count] } ON COLUMNS
FROM [SalesCube]')
--> Result only returns 1 column. The column with the 'All' isn't displayed.
Linked server: @server = N'OLAP_DWH', @srvproduct=N'OLAP', @provider=N'MSOLAP'

The real problem is: I want to store the results into a table, and the same procedure sometimes is used for the 'All' customers, sometimes for only 1 customer.

Does someone know a solution to always have the Customer column into the resultset when executed with linked server?

Thanks.



davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1633 Visits: 1008
Gah! I hate how this forum errors out if you take too long writing a reply. Lost all of it Sad

Anyway, here's a quick re-write.

Firstly, AXIS 0 is the columns, AXIS 1 is the rows, so I would generally write it in that order, but that's not important if you're naming them. Just a style issue so I won't argue about that.

Back to your issue.

The [All] member is not a real member. i.e. you haven't loaded a client into your cube called 'all', it's a system generated aggregate created when the cube is processed. Referring to it works in a direct MDX query, but doesn't make sense when returned to SQL.

Try this query:

SELECT 
*
FROM OPENQUERY(OLAP_DWH,
'SELECT NON EMPTY( { [Customer].[Customer ID].Allmembers }) ON ROWS,
{[Measures].[Fact Count] } ON COLUMNS
FROM [SalesCube]')



You will see your 'All' member is returned as the first row, and it's customer ID is null. To select for it you could write something like this:

SELECT 
'All' as Customer_ID
,"[Measures].[Fact Count]"
FROM OPENQUERY(OLAP_DWH,
'SELECT NON EMPTY( { [Customer].[Customer ID].Allmembers }) ON ROWS,
{[Measures].[Fact Count] } ON COLUMNS
FROM [SalesCube]')
where "[Customer].[Customer ID].[Customer ID].[MEMBER_CAPTION]" is NULL



Note that the 'All' pseudo-member is part of the [Customer].[Customer ID].Allmembers , but it is excluded in [Customer].[Customer ID].[Customer ID].Allmembers

So another way to write it would be to do the aggregate in SQL

SELECT 
'All' as Customer_ID
,SUM (Cast("[Measures].[Fact Count]" as float))
FROM OPENQUERY(OLAP_DWH,
'SELECT NON EMPTY( { [Customer].[Customer ID].[Customer ID].Allmembers }) ON ROWS,
{[Measures].[Fact Count] } ON COLUMNS
FROM [SalesCube]')



Note that all data from MDX is returned to SQL as nvarchar, and you can't sum nvarchar. Floats are generally a crappy datatype, decimal/numeric is far better but you can't cast nvarchars directly to decimals, so float will have to do.

If you are doing this query as part of a report (e.g. for SSRS) then it might make sense to return all customers to the report, excluding the 'All' aggregate (i.e. query the deeper [Customer ID].[Customer ID] level), and then doing the grouping in the Report, enabling drill down to customers if required.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93679 Visits: 38955
Here's a tip, if you have typed a long post copy it before you post so you can paste it in a new post if your post time out. Been there and had it happen to me.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1633 Visits: 1008
Thanks Lynn, good plan.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search