CTE在SSAS导入模式正常，直连模式查询报错，是否不支持？

问题描述 #

我使用包含CTE的原生查询在SSAS中创建表，Import（导入）模式下运行完全正常。但切换到Direct Query（直连）模式时，模型可部署到SQL Server，不过在SSMS或DAX Studio中查询数据时出现如下错误：

Executing the query ... OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Incorrect syntax near the keyword 'with'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.. Run complete

我想确认SSAS直连模式是否支持CTE，相关CTE代码如下：

;with crmhfCTE as (
SELECT     
    lp.d_Book_Date as Transaction_Date
    ,hfa.OfferSelectedBase as Funding_Amount
    ,hfa.RealEstateActualPrice as Funded_Asset_Purchase_Amount
    ,N'شراء جاهز' as Product_type  
    ,hfa.PropertyType as Property_Type_Code
    ,(select lookupvalue  from CRM_Lookups  as lup where 1=1 and hfa.PropertyType = lup.lookupcode and  LookupType = 'vrp_propertytype' )  as Property_Type_Name
    ,hfa.SellerName as Seller_Data
    ,hfa.CustomerName as Customer_Name
    ,'750' as VAT_Amount
    ,hfa.ApplicationID as Transaction_Number
    ,hfa.FirstTimeHouseBuyer as First_Time_House_Buyer_Flag
    ,'-' as Annual_Statement_Of_Sold_Debts
    ,lp.v_type as Loan_Type
    ,lp.d_Extraction_Date as Extraction_Date
FROM HF_Application as hfa
RIGHT JOIN(
    SELECT 
        v_Application_Num
        ,d_Book_Date
        ,v_Type 
        ,d_Extraction_Date
    FROM Retail_Loan_Contract_Hist
        WHERE 1=1
        and v_Type IN ('RCMF')
) as lp on hfa.ApplicationID = lp.v_Application_Num

where 1=1 
and hfa.IsCurrent = 'y'

)

select 
    Transaction_Date as 'Transaction Date'
    ,Funding_Amount as 'Funding Amount'
    ,Funded_Asset_Purchase_Amount as 'Funded Asset Purchase Amount'
    ,Product_type  as 'Product Type'
    ,case Property_Type_Name
        when 'Ready Built Duplex' then N'دبلكس'
        when 'Ready Built Villa' then N'فيلا'
        when 'Ready Built Apartment' then N'شقة'
        when 'Ready Built Building' then N'مبنى'
        else Property_Type_Name  end as  'Property Type Name Arabic'
    ,Seller_Data as 'Seller Data'
    ,Customer_Name as 'Customer Name'
    ,VAT_Amount as 'VAT Amount'
    ,Transaction_Number as 'Transaction Number'
    ,First_Time_House_Buyer_Flag as 'First Time House Buyer Flag'
    ,Annual_Statement_Of_Sold_Debts as 'Annual Statement Of Sold Debts'
    ,Loan_Type as 'Loan Type'
    ,Extraction_Date as 'Extraction Date'
from crmhfCTE

解答 #

核心结论 #

SSAS直连模式支持CTE，报错是因为直连模式下SQL语句的拼接逻辑导致的语法问题，而非CTE本身不被支持。

问题原因 #

直连模式下，SSAS会将DAX查询转换为底层SQL查询，可能会在你的CTE语句前自动拼接额外的SQL片段。你的代码中;with前存在多余空格，且如果SSAS拼接的前序语句未正确终止，就会触发SQL语法规则冲突——SQL要求CTE的WITH关键字前必须用分号终止前序语句，格式不严谨时就会报错。

修复方案 #

1. 规范CTE开头格式：将分号紧贴WITH关键字，去掉多余空格，确保格式为;WITH而非带空格的 ;with
2. 添加冗余前置分号：在整个查询最开头额外添加一个分号（SQL允许无前置语句时的冗余分号），确保无论SSAS拼接什么内容，都能正确终止前序语句
3. 改写为子查询（可选）：将CTE逻辑改写为嵌套子查询，功能一致但可规避CTE的语法拼接问题

修复后的示例代码：

;WITH crmhfCTE AS (
SELECT     
    lp.d_Book_Date AS Transaction_Date
    ,hfa.OfferSelectedBase AS Funding_Amount
    ,hfa.RealEstateActualPrice AS Funded_Asset_Purchase_Amount
    ,N'شراء جاهز' AS Product_type  
    ,hfa.PropertyType AS Property_Type_Code
    ,(SELECT lookupvalue FROM CRM_Lookups AS lup WHERE hfa.PropertyType = lup.lookupcode AND LookupType = 'vrp_propertytype') AS Property_Type_Name
    ,hfa.SellerName AS Seller_Data
    ,hfa.CustomerName AS Customer_Name
    ,'750' AS VAT_Amount
    ,hfa.ApplicationID AS Transaction_Number
    ,hfa.FirstTimeHouseBuyer AS First_Time_House_Buyer_Flag
    ,'-' AS Annual_Statement_Of_Sold_Debts
    ,lp.v_type AS Loan_Type
    ,lp.d_Extraction_Date AS Extraction_Date
FROM HF_Application AS hfa
RIGHT JOIN(
    SELECT 
        v_Application_Num
        ,d_Book_Date
        ,v_Type 
        ,d_Extraction_Date
    FROM Retail_Loan_Contract_Hist
    WHERE v_Type IN ('RCMF')
) AS lp ON hfa.ApplicationID = lp.v_Application_Num
WHERE hfa.IsCurrent = 'y'
)
SELECT 
    Transaction_Date AS 'Transaction Date'
    ,Funding_Amount AS 'Funding Amount'
    ,Funded_Asset_Purchase_Amount AS 'Funded Asset Purchase Amount'
    ,Product_type AS 'Product Type'
    ,CASE Property_Type_Name
        WHEN 'Ready Built Duplex' THEN N'دبلكس'
        WHEN 'Ready Built Villa' THEN N'فيلا'
        WHEN 'Ready Built Apartment' THEN N'شقة'
        WHEN 'Ready Built Building' THEN N'مبنى'
        ELSE Property_Type_Name END AS 'Property Type Name Arabic'
    ,Seller_Data AS 'Seller Data'
    ,Customer_Name AS 'Customer Name'
    ,VAT_Amount AS 'VAT Amount'
    ,Transaction_Number AS 'Transaction Number'
    ,First_Time_House_Buyer_Flag AS 'First Time House Buyer Flag'
    ,Annual_Statement_Of_Sold_Debts AS 'Annual Statement Of Sold Debts'
    ,Loan_Type AS 'Loan Type'
    ,Extraction_Date AS 'Extraction Date'
FROM crmhfCTE

补充说明 #

直连模式下SSAS会动态生成SQL语句，对自定义原生查询的语法严谨性要求比导入模式更高：导入模式是直接执行你的查询并导入数据，而直连模式是将你的查询作为子查询或拼接到底层SQL中，因此格式细节会直接影响执行结果。

内容的提问来源于stack exchange，提问作者Vickar