April 18, 2013 at 4:13 am
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.
April 21, 2013 at 7:10 pm
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.
April 21, 2013 at 7:56 pm
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.
April 22, 2013 at 5:32 pm
Thanks Lynn, good plan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply