Handling MDX OPENQUERY, The identifier that starts with is too long. Maximum length is 128

Question

I have a problem using openquery. i want to select the column with it's name but the name is too long because there are much levels

SELECT 
     A."[Dimension].[NAMEOFLEVEL1].[NAMEOFLEVEL2].[NAMEOFLEVEL3].[NAMEOFLEVEL4].[NAMEOFLEVEL5].[NAMEOFLEVEL6]" AS Acc2 
FROM OPENQUERY(CUBEX,
        'SELECT 
            ({
                [Dimension].[NAMEOFLEVEL1].[NAMEOFLEVEL2].[NAMEOFLEVEL3].[NAMEOFLEVEL4].[NAMEOFLEVEL5].[NAMEOFLEVEL6]
            }) DIMENSION PROPERTIES MEMBER_KEY 
            ON COLUMNS, 
            NON EMPTY (
                [TIME].[MONTH]
            ) DIMENSION PROPERTIES MEMBER_KEY 
            ON ROWS
        FROM [MyTable] 
    ') A

the error is as follow :

The identifier that starts with '[Dimension].[NAMEOFLEVEL1].[NAMEOFLEVEL2].[NAMEOFLEVEL3].[NAMEOFLEVEL4].[NAMEOFLEVEL5].[NAMEOFLEVEL6]' is too long. Maximum length is 128.

there are limits for `OPENQUERY`. There is also a limit on the total string length allowed for it's second argument that I have encountered. — whytheq, Feb 11 '15 at 09:24

score 0 · Answer 1 · answered Feb 11 '15 at 09:58

Well a possible workaround is to switch from using the User Hierarchy to the equivalent Attribute Hierarchy which will have a much smaller identifier.

--USER HIERARCHY EXAMPLE
SELECT 
     "[Date].[Date - Calendar Month].[Calendar Month].[MEMBER_CAPTION]"
FROM OPENQUERY(linkedName,
'
SELECT 
  {[Measures].[xxx]} ON 0
 ,NON EMPTY 
    [Date].[Date - Calendar Month].[Calendar Month].MEMBERS ON 1
FROM [OurCube];')


--ATTRIBUTE HIERARCHY EXAMPLE - NOT MUCH SMALLER IN OUR CUBE
SELECT 
     "[Date].[Calendar Month].[Calendar Month].[MEMBER_CAPTION]"
FROM OPENQUERY(linkedName,
'
SELECT 
  {[Measures].[xxx]} ON 0
 ,NON EMPTY 
    [Calendar Month].[Calendar Month].MEMBERS ON 1
FROM [OurCube];')

Maybe yours shortens to this:

SELECT 
     A."[Dimension].[NAMEOFLEVEL6].[NAMEOFLEVEL6]" AS Acc2 
FROM OPENQUERY(CUBEX,
        'SELECT 
            ({
                [Dimension].[NAMEOFLEVEL6].[NAMEOFLEVEL6]
            }) DIMENSION PROPERTIES MEMBER_KEY 
            ON COLUMNS, 
            NON EMPTY (
                [TIME].[MONTH]
            ) DIMENSION PROPERTIES MEMBER_KEY 
            ON ROWS
        FROM [MyTable] 
    ') A

it's a parent/child type, with levels from 1 to 5. I don't know how to do in that case. — Data Engineer, Feb 11 '15 at 10:02
what i want to do is to get the values of all the levels and apply a COALESCE to get the NOT NULL value between the. — Data Engineer, Feb 11 '15 at 10:27
there's another problem, the names of the levels of an X member are the same [Dimension].[NAMEOFLEVEL].[NAMEOFLEVEL].[NAMEOFLEVEL].[NAMEOFLEVEL].[NAMEOFLEVEL====X Member]. I don't know if how to do something like [Dimension].level(1).[Xmember] and so on — Data Engineer, Feb 11 '15 at 10:30
I have no experience with parent-child dimensions so am unable to help. We had a very good contributor @FrankPI who may be able to help. Although is your last comment a new separate question ? — whytheq, Feb 12 '15 at 10:59

score 0 · Accepted Answer · edited May 23 '17 at 10:25

It is not a limit for OPENQUERY. Its the limit for SQL column name - actually 128 characters.

Still, your situation is easier - there is no need to list all hierarchies to include their names in output

SELECT 
 A."[Dimension].[NAMEOFLEVEL1].[MEMBER_CAPTION]" as Level1,
 A."[Dimension].[NAMEOFLEVEL2].[MEMBER_CAPTION]" as Level2
 ...
 FROM OPENQUERY(CUBEX,
    'SELECT 
        ({
            [Dimension].[NAMEOFLEVEL6].members
        }) DIMENSION PROPERTIES MEMBER_KEY 
        ON COLUMNS, 
        NON EMPTY (
            [TIME].[MONTH]
        ) DIMENSION PROPERTIES MEMBER_KEY 
        ON ROWS
    FROM [MyTable] 
') A

If you encounter really long names in MDX (assuming the fact Dimension full name + Member_CAPTION is used as column name), then wrap your level in a set with shorter name.

SELECT 
 A."[Measures].[allLevels].[MEMBER_CAPTION]" AS Acc2 
 FROM OPENQUERY(CUBEX,
    'with member allLevels as
      (
          [Dimension].[NAMEOFLEVEL1].[NAMEOFLEVEL2].[NAMEOFLEVEL3].[NAMEOFLEVEL4].[NAMEOFLEVEL5].[NAMEOFLEVEL6].members
      )

      SELECT 
        ({
           [Measures].[allLevels]
        }) DIMENSION PROPERTIES MEMBER_KEY 
        ON COLUMNS, 
        NON EMPTY (
            [TIME].[MONTH]
        ) DIMENSION PROPERTIES MEMBER_KEY 
        ON ROWS
    FROM [MyTable] 
') A

Handling MDX OPENQUERY, The identifier that starts with is too long. Maximum length is 128

2 Answers2