We have data on a MS SQL Server that we analyze in KNIME using the SQL Server Connector node (Figure 1.). We would like to get output data from KNIME workflows pushed to Power BI. However, as there is no connector for Power BI in KNIME, a workaround we thought of is to push the data onto Azure SQL DB and connect Power BI to that (Figure 2.).
Figure 1. Data pull from MS SQL Server in KNIME for analysis.
My question is: what is the best way to push data into Azure SQL Database from a local KNIME workflow? Or, if anyone has successfully pushed data into Power BI from KNIME, we’d be interested in that too.
you can use the Microsoft SQL Server Connector node also to log into an Azure SQL Database which is basically a standard SQL Server in the cloud. Once connected you can use the standard DB nodes such as the DB Writer, DB Insert or DB Update node to push data into the database.
To obtain the JDBC connection information log into the Azure portal and select the database you want to connect to. Once the database is selected click on the Connection strings option in the left menu bar. Then select the JDBC tab which will look like the following:
You can then either use the dedicated Microsoft SQL Server Connector node and enter the given information into the Connection Setting and JDBC Parameter tab
In both cases make sure that you are using the official Microsoft SQL Server driver that you need to install separately in KNIME due to license restrictions. For further details see the Third-party Database Driver Plug-in section of the database documentation.
We are also looking into the development of dedicated nodes for Power BI which will work similar to the Tableau integration. Would this kind of functionality e.g. pushing data directly to Power BI server be enough to solve your problem? Do you have other use cases e.g. loading data from Power BI into KNIME?
I missed your earlier point about using the DB Update node to push data into the database. That makes sense, thank you.
Regarding Power BI, having a dedicated connector similar to Tableau would be fantastic. That would essentially eliminate the need for a “data warehouse” as we would just connect to source systems (e.g. SQL Server), blend and analyze in KNIME, and immediately push to Power BI service to automatically update reports and dashboards.
I will think of other use cases for possible Power BI node functionality and update the post. I’m happy to collaborate or help in any way to make this functionality a reality. Thank you.
Hi Haystack,
no problem. You can also work directly with the Azure Blob Store using the Azure Blob Store Connector together with the file handling nodes or the Azure Blob Store File Picker. For more details see this example workflow and blog post.
Thanks for offering your help. I will write you a personal message to stay in contact regarding the Power BI integration.
Bye
Tobias
