SAP B1 Report to PowerBI

[shancius]

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,

[shancius]

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]

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" 🙂

[Sahir_Maharaj]

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. 

[Sahir_Maharaj]

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.

[jaweher899]

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.