cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shancius
Frequent Visitor

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
shancius
Frequent Visitor

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
Community Champion
Community Champion

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
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ 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


Sahir_Maharaj
Community Champion
Community Champion


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
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ 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


Sahir_Maharaj
Community Champion
Community Champion

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
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ 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


jaweher899
Impactful Individual
Impactful Individual

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Kudoed Authors