Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SAP B1 Report to PowerBI

Hey Experts,

Currently we have been trying to set-up a PowerBI Dashboard with Live SQL query to SAP. However, the information we want to use in the dashboard is from an overview in SAP. We believe that this SAP overview is build with multiple SAP queries.

My question is as following, how can we know which queries are used in SAP B1 to create the overview OR how can we connect PowerBI directly to this overview.

Thank you kindly in advance,

5 REPLIES 5
Anonymous
Not applicable

Thank you everybody for the help,

We fixed the issues ourselves in the following way:

We searched the SAP B1 Report in the SQL Database in the query table "OUQR", in the OUQR you can find all the queries that are being used in SAP B1. We then found the query that was done to create the report in B1. This query had it's own query string ofcourse, we took this query string, copied it inside the powerBI SQL connect and then it worked.

Sahir_Maharaj
Super User
Super User

Alternatively, you can use the PowerBI DirectQuery or PowerBI Live Connection to connect to the SQL database directly, but this requires a PowerBI Premium or PowerBI Embedded license.

 

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User


Unfortunately, you cannot connect PowerBI directly to the SAP B1 overview. The only way to access the data in the overview is to extract the data and bring it into PowerBI. You can extract the data into a SQL database, a CSV file, or an Excel spreadsheet and use it as a data source for PowerBI. 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

To know which queries are used in SAP B1 to create the overview, you can look for the view definition in the SQL database. You can access the SQL database using a SQL client tool such as SQL Server Management Studio. Connect to the SQL database, locate the relevant view and examine the script of the view.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
jaweher899
Super User
Super User

To find the SAP queries that were used to build an overview in SAP B1, you can follow these steps:

  1. Log in to SAP B1 as a user with administrative privileges.
  2. Go to the "SAP Business One" menu and select "Tools."
  3. Click on "SQL Query" to open the SQL Query tool.
  4. In the SQL Query tool, select the "Queries" tab.
  5. Search for the relevant queries by name or by the name of the overview they were used in.

To connect PowerBI directly to the overview in SAP B1, you may have to extract the data from SAP B1 into a separate data source, such as a database or a data file, and then connect PowerBI to that data source.

One approach is to use an SAP B1 add-on, such as the "SAP Business One DI API" add-on, to extract the data from SAP B1 into a database or data file. You can then connect PowerBI to the database or data file to access the data for your dashboard.

Note that SAP B1 may have limitations on the data you can extract and the methods you can use to extract it, so it's important to check the SAP B1 documentation or contact SAP support for more information.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.