Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieving the ALL member only Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 4:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:58 AM
Points: 37, Visits: 290
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.



Post #1443720
Posted Sunday, April 21, 2013 7:10 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:57 PM
Points: 443, Visits: 822
Gah! I hate how this forum errors out if you take too long writing a reply. Lost all of it

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.

Post #1444836
Posted Sunday, April 21, 2013 7:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 22,475, Visits: 30,155
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.



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)
Post #1444837
Posted Monday, April 22, 2013 5:32 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:57 PM
Points: 443, Visits: 822
Thanks Lynn, good plan.
Post #1445231
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse