Retrieving the ALL member only

  • 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.

  • 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.

  • 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.

  • 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