SSAS DSV Incorrect syntax near ')' + CTE is not allowed in SSAS DSV and its Solution

CTE is not allowed in SSAS DSV . it will give the error
Incorrect syntax near ')'

To avoid this error Please use Named Query instead of CTE.

Example :



WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
        system_type_id
        FROM sys.columns)
        select [CTETest].*
        from
        (
        SELECT object_id, name, column_id, system_type_id
        FROM mycte as mycte_1
        ) AS [CTETest]

You can use

Select Object_id , column_id , system_type_id
FROM (SELECT TOP (200) object_id, name, column_id,
        system_type_id
        FROM sys.columns) myCTE