SQL Syntax Error in Power Bi while fetching Data From Diff Tables to Table - Visual

[hello_]

Hello Authors Please land me some help, 
I am getting Data from mysql server (Version 8.0) by MariaDB connector (in Direct Query) the schema is Star there and has 3 Tables and 2 connection between tables.
Connection - 

FactTable - table2 (Many to many)

FactTable - table3 (Many to One) 

when I try to put or see data in Table Visual I face this error 

when I put - 

Field1 from Table3

Field2 from FactTable

 What Should I do please Guied 

Thank You in Advance

[Anonymous]

Hi @hello_ ,

Based on the information you have provided, given that you are using a star schema and are having problems adding fields from different tables to the Table Vision object, here are some initial steps you can take to resolve the issue:

1. If any custom SQL queries have been written to extract data, make sure there are no syntax errors. Check that reserved keywords, quotes match, and join syntax is used correctly.
2. Keep in mind that Direct Query has some limitations. For example, many-to-many relationships may require the creation of unique keys, which can be a complex operation in Direct Query mode. Check whether the complexity of the queries generated by Direct Query may result in SQL syntax errors.
3. Ensure that the MySQL Connector/NET packages are correctly installed on the computer running the local Data Gateway, as this is required for MariaDB Connector to work properly.

In order to provide a more specific solution, if possible, please provide details of any custom SQL queries used in Power BI as well as screenshots of the relational views. With more details, we will be able to better guide you through this issue.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hello_]

the whole case is
- main database is made on MySQL Server(Version 5.0) and I only have readonly User 
- I made Backup file(.FRM, .MYD) and load data to my Root mysql (server virsion - 5.0)to see if after making changes and converting the schema to star every thing is going fine or not. 
- Had Same error to changed root mysql Server to 8.0 still had the error 

Is it because the Originaly Database is made on Old Server Version - 

How can I overcome this error happening because of version differance 

please guide me if there is any way  

Thank You

 

[123abc]

It seems like there might be an issue with the SQL query generated by Power BI while trying to fetch data from your MySQL server. The error message suggests a syntax error in the SQL query.

Here are a few steps you can take to troubleshoot and resolve the issue:

1. Check the Query in Power BI:

   • Open Power BI Desktop.
   • Go to the "Home" tab.
   • Click on "Transform Data" to open Power Query Editor.
   • Review the queries and check if there are any errors or warnings.

2. Check SQL Server for Syntax Errors:

   • If you have access to the MySQL server, you can try running the problematic SQL query directly in a MySQL client to see if it returns any syntax errors.

3. Review Relationships in Power BI:

   • Make sure that the relationships between your tables are correctly defined in Power BI.
   • Check if there are any circular references in your relationships, as this could cause issues.

4. Check MariaDB Connector Compatibility:

   • Ensure that the MariaDB connector you are using in Power BI is compatible with your MySQL server version (8.0).
   • Check for any updates or patches for the MariaDB connector that may address compatibility issues.

5. Check Data Types:

   • Ensure that the data types of the fields used in your visual are compatible and consistent across the tables involved in the relationships.

6. Review Many-to-Many Relationship:

   • Many-to-many relationships in Power BI can be complex. Ensure that your many-to-many relationship between FactTable and table2 is correctly defined.

7. Check for Known Issues:

   • Check Power BI forums or documentation for any known issues related to MariaDB connectors or the specific version you are using.

8. Enable Query Diagnostics:

   • In Power BI Desktop, go to "File" > "Options and settings" > "Options."
   • Under the "Query Diagnostics" tab, enable "Tracing" and "Verbose" options.
   • Refresh the data and check the diagnostic information for more details on the error.

By following these steps, you should be able to identify and resolve the SQL syntax error in Power BI. If the issue persists, providing more details about the specific error message or the generated SQL query could help in providing more targeted assistance.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[hello_]

Still Having problems 

Thank You for your time @123abc