SQL Server CTE is working in SSAS Import mode but is not working in SSAS Direct Query mode

igetnqfo  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(107)

I'm using a native query that contains a CTE to create a table in SSAS. When I use the CTE for a SSAS model in IMPORT mode, it works perfectly well. But when I use the same CTE in SSAS model in DIRECT QUERY mode, I am able to deploy the model on SQL Server but then when I try to query data from SSMS or DAX Studio I get an error as below
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

Can someone help me understand whether CTE is not supported in SSAS DIRECT QUERY mode? I couldn't find any relevant info online.

  1. ;with crmhfCTE as (
  2. SELECT
  3. lp.d_Book_Date as Transaction_Date
  4. ,hfa.OfferSelectedBase as Funding_Amount
  5. ,hfa.RealEstateActualPrice as Funded_Asset_Purchase_Amount
  6. ,N'شراء جاهز' as Product_type
  7. ,hfa.PropertyType as Property_Type_Code
  8. ,(select lookupvalue from CRM_Lookups as lup where 1=1 and hfa.PropertyType = lup.lookupcode and LookupType = 'vrp_propertytype' ) as Property_Type_Name
  9. ,hfa.SellerName as Seller_Data
  10. ,hfa.CustomerName as Customer_Name
  11. ,'750' as VAT_Amount
  12. ,hfa.ApplicationID as Transaction_Number
  13. ,hfa.FirstTimeHouseBuyer as First_Time_House_Buyer_Flag
  14. ,'-' as Annual_Statement_Of_Sold_Debts
  15. ,lp.v_type as Loan_Type
  16. ,lp.d_Extraction_Date as Extraction_Date
  17. FROM HF_Application as hfa
  18. RIGHT JOIN(
  19. SELECT
  20. v_Application_Num
  21. ,d_Book_Date
  22. ,v_Type
  23. ,d_Extraction_Date
  24. FROM Retail_Loan_Contract_Hist
  25. WHERE 1=1
  26. and v_Type IN ('RCMF')
  27. ) as lp on hfa.ApplicationID = lp.v_Application_Num
  28. where 1=1
  29. and hfa.IsCurrent = 'y'
  30. )
  31. select
  32. Transaction_Date as 'Transaction Date'
  33. ,Funding_Amount as 'Funding Amount'
  34. ,Funded_Asset_Purchase_Amount as 'Funded Asset Purchase Amount'
  35. ,Product_type as 'Product Type'
  36. ,case Property_Type_Name
  37. when 'Ready Built Duplex' then N'دبلكس'
  38. when 'Ready Built Villa' then N'فيلا'
  39. when 'Ready Built Apartment' then N'شقة'
  40. when 'Ready Built Building' then N'مبنى'
  41. else Property_Type_Name end as 'Property Type Name Arabic'
  42. ,Seller_Data as 'Seller Data'
  43. ,Customer_Name as 'Customer Name'
  44. ,VAT_Amount as 'VAT Amount'
  45. ,Transaction_Number as 'Transaction Number'
  46. ,First_Time_House_Buyer_Flag as 'First Time House Buyer Flag'
  47. ,Annual_Statement_Of_Sold_Debts as 'Annual Statement Of Sold Debts'
  48. ,Loan_Type as 'Loan Type'
  49. ,Extraction_Date as 'Extraction Date'
  50. from crmhfCTE
sdnqo3pr

sdnqo3pr1#

As suggested by mxix in the question comments , creating the view containing the CTE code and then accessing this view in SSAS in Direct Query mode worked for me.

相关问题