July 19, 2017 at 6:06 am
Hi colleagues!
We have been run into issue after migration from SQL 2008R2 to SQL 2016. Our reporting application (Tableau) generates MDX queries wich we can not affect on. But in some cases query that comes to server fails with error
"Query (23, 1) Execution of the managed stored procedure DATEADD failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.
Deeply nested recursive calculations cannot invoke a CLR Stored Procedure.." . If take this exact MDX and execute it on SQL 2008R2 - it works perfect.
Do anybody know what has been changed in SQL 2016 to get this difference in behaviour? Any checkbox I did not find?
Caught MDX query:
WITH
MEMBER [Measures].[LEVEL INSTANCE none:Filter Date:qk - lev00] ASMEMBER [Measures].[LEVEL INSTANCE none:Filter Date:qk - lev00] AS
'CASE WHEN IsEmpty([Timeline].[Filter Date].CurrentMember.MemberValue) OR [Timeline].[Filter Date].CurrentMember.MemberValue = null THEN null ELSE CDate([Timeline].[Filter Date].CurrentMember.MemberValue) END','CASE WHEN IsEmpty([Timeline].[Filter Date].CurrentMember.MemberValue) OR [Timeline].[Filter Date].CurrentMember.MemberValue = null THEN null ELSE CDate([Timeline].[Filter Date].CurrentMember.MemberValue) END',
SOLVE_ORDER = 127SOLVE_ORDER = 127
SELECTSELECT
{[Measures].[Headcount]} DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,{[Measures].[Headcount]} DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,
NON EMPTY CROSSJOIN(NON EMPTY CROSSJOIN(
Union(Union(
[Business Unit Active Structure].[Structure].[Level 02].AllMembers,[Business Unit Active Structure].[Structure].[Level 02].AllMembers,
Union(Union(
{[Business Unit Active Structure].[Structure].&[{6A63A0B7-196C-4CE2-A832-A65E00E872D2}]:[Business Unit Active Structure].[Structure].&[{C306AD38-A43D-4060-BD30-A5B601659E34}]},{[Business Unit Active Structure].[Structure].&[{6A63A0B7-196C-4CE2-A832-A65E00E872D2}]:[Business Unit Active Structure].[Structure].&[{C306AD38-A43D-4060-BD30-A5B601659E34}]},
{[Business Unit Active Structure].[Structure].&[{43B1622C-0768-4FE4-A977-C35E2D45FBB2}],{[Business Unit Active Structure].[Structure].&[{43B1622C-0768-4FE4-A977-C35E2D45FBB2}],
[Business Unit Active Structure].[Structure].&[{213AA134-4E0F-43AF-9BD8-6692DD11561C}],[Business Unit Active Structure].[Structure].&[{213AA134-4E0F-43AF-9BD8-6692DD11561C}],
[Business Unit Active Structure].[Structure].&[{6B7CBC6E-FB12-456A-8FB2-9D4A01086D2C}],[Business Unit Active Structure].[Structure].&[{6B7CBC6E-FB12-456A-8FB2-9D4A01086D2C}],
[Business Unit Active Structure].[Structure].&[{9B229016-036C-4983-BAF1-9D4A010C4C3B}]})),[Business Unit Active Structure].[Structure].&[{9B229016-036C-4983-BAF1-9D4A010C4C3B}]})),
[Timeline].[Financial Years].[Financial Month].AllMembers) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS[Timeline].[Financial Years].[Financial Month].AllMembers) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS
FROM [Personnel]FROM [Personnel]
WHEREWHERE
StripCalculatedMembers(CROSSJOIN(StripCalculatedMembers(CROSSJOIN(
{[Business Unit Active Structure].[LXTDGT].&[Yes]},{[Business Unit Active Structure].[LXTDGT].&[Yes]},
Filter(Filter(
[Timeline].[Filter Date].[Filter Date].AllMembers,[Timeline].[Filter Date].[Filter Date].AllMembers,
DATEADD("m",-4,DATEADD("m",DATEDIFF("m",CDate(36526),NOW()),CDate(36526))) <= [Measures].[LEVEL INSTANCE none:Filter Date:qk - lev00] AND [Measures].[LEVEL INSTANCE none:Filter Date:qk - lev00] < DATEADD("m",1,DATEADD("m",DATEDIFF("m",CDate(36526),NOW()),CDate(36526))))))DATEADD("m",-4,DATEADD("m",DATEDIFF("m",CDate(36526),NOW()),CDate(36526))) <= [Measures].[LEVEL INSTANCE none:Filter Date:qk - lev00] AND [Measures].[LEVEL INSTANCE none:Filter Date:qk - lev00] < DATEADD("m",1,DATEADD("m",DATEDIFF("m",CDate(36526),NOW()),CDate(36526))))))
As you can see DATEADD function is in where clause. By the way, if we delete {[Business Unit Active Structure].[LXTDGT].&[Yes]} it works
July 20, 2017 at 5:54 am
In general, dimensional data should not need date calculations, as the data feeding the cube should have that kind of thing either "pre-computed" or be an actual field. However, I'll guess that re-designing what feeds into your cube either isn't going to happen or would take a long time. So net result is that it may be that you are operating your database in 2016 compatibility level, when what you may need is to operate in 2008 compatibility level. Not sure how that kind of thing plays into SSAS, but it seems like a logical conclusion, given that regular databases have that to worry about.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 20, 2017 at 11:45 pm
Hi Steve!
Thanks for your answer. Unfortunately we can not affect MDX query cause it is generating by client app. Compatibility level of the database do not cross compatibility level of olap database, but it was a good idea. We were playing with compatibility level of OLAP with no success
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply