MDX to SQL and check Syntax error

Sudipto Duyari 1 Reputation point
2021-04-09T13:01:41.2+00:00

SELECT
{}
ON 0,
NON EMPTY (
[Entity].[EntityPRID].&[118445008]*
[Entity-OrgType].[EntityOrgType].[EntityOrgType].MEMBERS
)
ON 1
FROM [Model]

Can anyone please help with the above query, I want to know if there is any syntax error, and what will be SQL query for the above?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,310 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,821 Reputation points
    2021-04-12T06:16:59.637+00:00

    Hi,

    There is no syntax error in the query. But you are not selecting any results from this query because of the the Non Empty function.

    I guess you are trying to find :

    SELECT
    {}
    ON 0,
     (
    [Entity].[EntityPRID].&[118445008]*
    [Entity-OrgType].[EntityOrgType].[EntityOrgType].MEMBERS
    )
    ON 1
    FROM [Model]
    

    and SQL query cannot run in multidimensional cube. It is not very meaningful to find the "same" sql query.

    But similar SQL against the original data source would be something like :

    SELECT '118445008' as EntityPRID, EntityOrgType from Entity-OrgType
    

    Regards,
    Lukas

    0 comments No comments

  2. Lukas Yu -MSFT 5,821 Reputation points
    2021-04-19T02:25:50.573+00:00

    Hi,
    We did not hear from you back, was you problem solved?

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.