Unexpected output for Tabular results and Member Properties

From: summer_porch (summer_porch_at_hotmail.com)
Date: 04/06/04

  • Next message: Seva Kashin: "Problem with discover members (MDSCHEMA_MEMBERS)" 
    Date: Tue, 6 Apr 2004 13:07:12 -0400

    I've found what appears to be a bug for the XML/A 1.1 SDK when dealing with
    Member Properties (e.g. using the DIMENSION PROPERTIES MDX statements) when
    inserted into the query for Tabular Results. I get the same results with the
    ADOMD.NET Beta (which came as no surprise as it uses the XML/A 1.1 SDK)

    The following top 5 query on Foodmart 2000 Sales asks for top 5 customers
    plus the dimension properties Yearly Income and Member Card for each
    Customer (Name).

    Query #1

    SELECT
      NON EMPTY DISTINCT( { [Measures].[Unit Sales] } ) ON COLUMNS, NON EMPTY
    DISTINCT( TOPCOUNT( DISTINCT( { [Customers].[Name].MEMBERS } ), 5, (
    [Measures].CURRENTMEMBER ) ) ) DIMENSION PROPERTIES
    [Customers].[Name].[Yearly Income], [Customers].[Name].[Member Card] ON ROWS
    FROM [Sales]

    This works fine when running producing a multi-dimensional result set. The
    two properties are added as custom properties to the axis info for the
    associated Customer dimension Members.

    What I was expecting to get for a table result for Query #1 was:

    Column 1 - [Customers].[Country].[MEMBER_CAPTION]
    Column 2 - [Customers].[State Province].[MEMBER_CAPTION]
    Column 3 - [Customers].[City].[MEMBER_CAPTION]
    Column 4 - [Customers].[Name].[MEMBER_CAPTION]
    Column 5 - [Customers].[Yearly Income]
    Column 6 - [Customers].[Member Card]
    Column 7 - [Measures].[Unit Sales]

    However, when the asking for tabular results, only the dimension properties
    are returned (snippet from the XML/A results).

      <xsd:element
    name="_x005B_Customers_x005D_._x005B_Yearly_x0020_Income_x005D_"
    sql:field="[Customers].[Yearly Income]" />
      <xsd:element
    name="_x005B_Customers_x005D_._x005B_Member_x0020_Card_x005D_"
    sql:field="[Customers].[Member Card]" />
      <xsd:element name="_x005B_Measures_x005D_._x005B_Unit_x0020_Sales_x005D_"
    sql:field="[Measures].[Unit Sales]" minOccurs="0" />

    In more human readable form:

    Column 1 - [Customers].[Yearly Income]
    Column 2 - [Customers].[Member Card]
    Column 3 - [Measures].[Unit Sales]

    In contrast, if I remove the request for Dimension Properties, query below:

    Query #2

    SELECT
      NON EMPTY DISTINCT( { [Measures].[Unit Sales] } ) ON COLUMNS, NON EMPTY
    DISTINCT( TOPCOUNT( DISTINCT( { [Customers].[Name].MEMBERS } ), 5, (
    [Measures].CURRENTMEMBER ) ) ) ON ROWS
    FROM [Sales]

    I get the following:

      <xsd:element
    name="_x005B_Customers_x005D_._x005B_Country_x005D_._x005B_MEMBER_CAPTION_x0
    05D_" type="xsd:string" sql:field="[Customers].[Country].[MEMBER_CAPTION]"
    minOccurs="0" />
      <xsd:element
    name="_x005B_Customers_x005D_._x005B_State_x0020_Province_x005D_._x005B_MEMB
    ER_CAPTION_x005D_" type="xsd:string" sql:field="[Customers].[State
    Province].[MEMBER_CAPTION]" minOccurs="0" />
      <xsd:element
    name="_x005B_Customers_x005D_._x005B_City_x005D_._x005B_MEMBER_CAPTION_x005D
    _" type="xsd:string" sql:field="[Customers].[City].[MEMBER_CAPTION]"
    minOccurs="0" />
      <xsd:element
    name="_x005B_Customers_x005D_._x005B_Name_x005D_._x005B_MEMBER_CAPTION_x005D
    _" type="xsd:string" sql:field="[Customers].[Name].[MEMBER_CAPTION]"
    minOccurs="0" />
      <xsd:element name="_x005B_Measures_x005D_._x005B_Unit_x0020_Sales_x005D_"
    sql:field="[Measures].[Unit Sales]" minOccurs="0" />

    Column 1 - [Customers].[Country].[MEMBER_CAPTION]
    Column 2 - [Customers].[State Province].[MEMBER_CAPTION]
    Column 3 - [Customers].[City].[MEMBER_CAPTION]
    Column 4 - [Customers].[Name].[MEMBER_CAPTION]
    Column 5 - [Measures].[Unit Sales]

    Which is what I expected.

    The XML/A results can be confirmed by use of the XML/A Sample application
    (the VB version) and viewing the results in either grid or XML form.

  • Next message: Seva Kashin: "Problem with discover members (MDSCHEMA_MEMBERS)"

    Relevant Pages

    • Re: Unexpected output for Tabular results and Member Properties
      ... > Member Properties (e.g. using the DIMENSION PROPERTIES MDX statements) ... > inserted into the query for Tabular Results. ... > are returned (snippet from the XML/A results). ...
      (microsoft.public.data.xmlanalysis)
    • Another member properties question.
      ... Wellington] DIMENSION PROPERTIES Customers.Name.Gender} on rows, ... from Sales ... (fails with message "token is not valid...^Properties^...) ...
      (microsoft.public.sqlserver.olap)