“Internal error: An unexpected exception occurred.” when trying to filter simple dimension that has ragged parent-child hierarchy in multidimensional model.

Michał Pawlikowski
This issue is not resolved yet. I am describing the problem here to get help from the community. There is also a question asked on Stack Overflow and bug reported to the Microsoft.

Platform: SQL Server Analysis Services 2016 RTM and later (Multidimensional)

 

In one of our projects, we encountered an interesting problem related to parameter filtering on one of our reports (SSRS). However, we would like to make sure that no mistake has been made before it hits as a bug for Microsoft.

 

In the database we have the following dimension of companies:

The parent-child hierarchy does not loop but it’s ragged. The graph looks as follows (treant-js drawing):

 

The Analysis Services project contains only one dimension – Company, created using the wizard. All settings are set with default values. “CompanySK” is the KeyColumn and “CompanyName” is the NameColumn. “CompanyParentSK” was renamed to “Company Hierarchy” and Usage was set to “Parent”, which indicates this attribute holds parent id. Nothing unusual:

 

To make it possible to query this data as Reporting Services is doing, the cube was created with just one simple measure: count of Companies. Bear in mind, that this measure is irrelevant to us, we will not query it.

 

The project is always loaded and processed without problems.

The hierarchy works and is represented correctly in the tree viewer (SSMS). Because “MembersWithData” is set to “NonLeafDataVisible” (by default), you can see repeated company names of every parent in his child list.

 

Now we need to query the Dimension using MDX. This query simulates Reporting Services Parameters Query so it always get MEMBER_CAPTION, UNIQUENAME and LEVEL.ORDINAL. 

In our report Companies were listed only by selecting Countries first, so the country parameter is always filtering companies and it’s doing it by using STRTOSET([members selected in the previous parameter]) function in WHERE clause. 

 

When you try to list all Companies where “Country Name” is “Germany”:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
WITH
MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
[Company].[Company Name].ALLMEMBERS ON ROWS
FROM [HierarchyErrorCube]
WHERE STRTOSET( "{[Company].[Country Name].&[Germany]}" );

it returns an error:

 

Executing the query …

Internal error: An unexpected exception occurred.

Run complete

 

BUT if you will ask for Australia:

Transact-SQL
1
2
(...)
WHERE STRTOSET( "{[Company].[Country Name].&[Australia]}" );

or United Kingdom

Transact-SQL
1
2
(...)
WHERE STRTOSET( "{[Company].[Country Name].&[United Kingdom]}" )

it just works:

 

 

The problem becomes more strange when you will try to make any combinations of available countries. For example “United Kingdom” and “Australia” filtered together – are making the query always failing:

Transact-SQL
1
2
(...)
WHERE STRTOSET( "{[Company].[Country Name].&[Australia],[Company].[Country Name].&[United Kingdom]}" );

 

And now the interesting facts:

  • If you will remove/comment [Measures].[ParameterCaption] from the selection – query starts to work,
  • if you will disable “Company Hierarchy” by changing it back to regular attribute – query starts to work,
  • removing the filter (WHERE) makes the query working,
  • so as you can see – even if “Company Hierarchy” is not a part of any query listed above, it affects somehow the engine and fails the query when it comes to retrieving CURRENTMEMBER.MEMBER_CAPTION on “Company Name” attribute when another attribute – “Country Name” – is filtered. And not for all filtered values, but we couldn’t find any pattern here (for example if country level affects somehow).
  • Using SQL Server Profiler (with enabled all events in capture scope) and sniffing one of the failing queries, it is clear, that the error is appearing during last phase of getting members – exactly for CURRENTMEMBER.MEMBER_CAPTION.

 

Unfortunately, we’re running out of ideas what can be wrong with this case. Is it there any setting in Visual Studio project that can help or maybe it’s just a bug in the engine? Any help would be appreciated. 

 

Of course, we’ve tried a lot of changes and checks. Let me list them here to eliminate unnecessary assumptions:

  • We have checked probably(?) every possible setting on “Company Hierarchy” and if it affects this issue (like encoding, ordering, data visible, naming template etc.) Only changing this hierarchy to regular attribute (“Usage” : “Regular” from “Parent”) is making the issue resolved and unfortunately, we would like to have this hierarchy for future analysis.
  • We have changed manually the parent-child chain to flat one – only one parent and all are children, and simple nested one – 2 is a child of 1, 3 is a child of 2 and so on and so forth. This makes the issue go away, queries are working.
  • This error does not generate any important information in the log. It is a general unknown error known also as “Type: 3, Category: 256, Event ID: 0xC1000016”. It triggers memory dumps creation.
  • We are aware of several bugs that were introduced and fixed by Microsoft in this area (parent-child), none of them apply to our case (for example we are querying using admin account, we do not use no translations etc.)
  • In fact we decided to test this case across many platforms and bugfixes, all of them failed on these SSAS versions: 2016 RTM, 2016 SP2, 2016 SP2 CU7, 2017 RTM, 2017 CU15
  • Our case was more complex, but we were able to simplify it and introduce it to you by using just a small dimension with the same hierarchy that was failing. 
  • It’s totally reproducible across different platforms (as mentioned above), different versions of Data Tools (15.9.0, 16.0.6) and Visual Studio (2017 & 2019)
  • If you would like to reproduce this in your environment, you can download the [DATABASE backup here] (or use table creation code below) and [SSAS PROJECT here]
  • Dimension Table
    Transact-SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    USE [your_db]
    CREATE TABLE [dbo].[Company](
    [CompanySK] [int] NOT NULL,
    [CompanyParentSK] [int] NULL,
    [CompanyName] [nvarchar](13) NOT NULL,
    [CountryName] [nvarchar](24) NOT NULL,
    CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
    (
    [CompanySK] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (1, 5, N'Company A', N'Australia')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (2, 1, N'Company B', N'Malaysia')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (3, 5, N'Company C', N'Brasil')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (4, 5, N'Company D', N'Switzerland')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (5, 7, N'Company E', N'Germany')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (6, 5, N'Company F', N'Denmark')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (7, NULL, N'Company G', N'Germany')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (8, 10, N'Company H', N'United Kingdom')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (9, 8, N'Company I', N'United Kingdom')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (10, 6, N'Company J', N'Denmark')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (11, 10, N'Company K', N'United Kingdom')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (12, 1, N'Company L', N'United States of America')
    GO
    INSERT [dbo].[Company] ([CompanySK], [CompanyParentSK], [CompanyName], [CountryName]) VALUES (13, 5, N'Company M', N'Spain')
    GO
    ALTER TABLE [dbo].[Company]  WITH CHECK ADD  CONSTRAINT [FK_Company_Company] FOREIGN KEY([CompanyParentSK])
    REFERENCES [dbo].[Company] ([CompanySK])
    GO
    ALTER TABLE [dbo].[Company] CHECK CONSTRAINT [FK_Company_Company]
    GO
     
  • And more MDX examples:
    MDX exmaples of failing and working queries
    Transact-SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]; --works
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Germany]}" ); --error
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Denmark]}" ); --error
    --WHERE [Company].[Country Name].&[Denmark]; --error
     
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Australia]}" ); --works
     
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[United Kingdom]}" ); --works
     
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Brasil]}" ); --works
     
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Australia],[Company].[Country Name].&[United Kingdom]}" ); --error
     
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Australia],[Company].[Country Name].&[Brasil]}" ); --error
     
     
    WITH
    MEMBER [Measures].[ParameterCaption] AS [Company].[Company Name].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Company].[Company Name].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Company].[Company Name].CURRENTMEMBER.LEVEL.ORDINAL
    SELECT {
    [Measures].[ParameterCaption],
    [Measures].[ParameterValue],
    [Measures].[ParameterLevel]
    } ON COLUMNS ,
    [Company].[Company Name].ALLMEMBERS ON ROWS
    FROM [HierarchyErrorCube]
    WHERE STRTOSET( "{[Company].[Country Name].&[Denmark],[Company].[Country Name].&[Australia],[Company].[Country Name].&[Germany]}" ); --error
     
    --WHERE STRTOSET( "{[Company].[Country Name].&[Germany]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Germany],[Company].[Country Name].&[Brasil]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Germany],[Company].[Country Name].&[Brasil],[Company].[Country Name].&[Switzerland]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Switzerland]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Switzerland],[Company].[Country Name].&[Denmark]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Switzerland],[Company].[Country Name].&[Denmark]}" ) -- error
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark],[Company].[Country Name].&[Germany]}" ) -- error
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark],[Company].[Country Name].&[Germany]}" ) -- error
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark],[Company].[Country Name].&[Denmark]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Australia]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Malaysia]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark],[Company].[Country Name].&[Australia],[Company].[Country Name].&[Germany]}" )
    --WHERE STRTOSET( "{[Company].[Country Name].&[Denmark],[Company].[Country Name].&[Germany],[Company].[Country Name].&[Australia]}" )
     

Leave a Reply