![\"Power](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310-150x150.png\"){kind=logo}
In this article, you will learn how to push data into a Power BI<\/a> dataset from SQL Server. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Among its dataset types, it has a streaming dataset into which you can push data from a Power BI REST API<\/a>. Once pushed, data will be immediately refreshed in a visual, pinned to a\u00a0dashboard. Thus this approach of sending data to a streaming dataset is a perfect\u00a0way for displaying real-time data. Yet it is not possible to push data to a dataset straight from SQL Server out of the box. However, with ZappySys ODBC PowerPack<\/a> it is possible to achieve that quite easily. We will employ one of its apps – ZappySys Data Gateway<\/a> – to make HTTP requests and push data to a streaming dataset.<\/p>\nThe use cases when you may want to push data to a streaming dataset from a SQL Server are:<\/p>\n
- \n
- Displaying real-time data from many SQL Server instances at once (and you cannot use the DirectQuery<\/em> option in a report).<\/li>\n
- Displaying real-time data when many sources write to a dataset, and a SQL Server is one of them.<\/li>\n
- Any other case.<\/li>\n<\/ul>\n
Let’s begin and write some data to a Power BI streaming dataset!<\/p>\n
Prerequisites<\/h2>\n
- \n
- You have a Power BI account and you have basic Power BI skills.<\/li>\n
- SQL Server instance installed (can be a SQL Server Express LocalDB<\/a> instance).<\/li>\n
- SQL Server Management Studio<\/a> (SSMS) installed.<\/li>\n
- ZappySys ODBC PowerPack<\/a>\u00a0installed\u00a0(must be installed on a\u00a0Windows machine; can be a different machine than SQL Server is installed on).<\/li>\n<\/ol>\n
Step By Step\u00a0– Push data into a Power BI dataset from a SQL Server<\/h2>\n
Create a streaming dataset in Power BI<\/h3>\r\nThe first thing you need to do is to create a streaming dataset. We will create a simple dataset for demo purposes which will have two columns: Timestamp<\/strong> and Temperature<\/strong>.\r\n
- \r\n \t
- Go to Power BI portal, hit My Workspace<\/strong>, + Create<\/strong> and then click Streaming dataset<\/strong>:\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/powerbi-create-streaming-dataset-1.png\")
<\/li>\r\n \t- Then select API<\/strong> and click Next<\/strong>:\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/powerbi-create-streaming-dataset-2-select-api.png\")
<\/li>\r\n \t- Give dataset a name, add two columns and check Historic data analysis<\/strong> to enable data history and the ability to create report\u00a0easily (otherwise you will be able to create a tile in a dashboard only):\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/powerbi-create-streaming-dataset-3-give-a-name.png\")
<\/li>\r\n \t- Once a dataset is created, copy Push URL<\/strong> to a notepad so we can use it later. Also, note the structure of the JSON we will have to use to submit live data into the created dataset:
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/powerbi-create-streaming-dataset-4-created.png\")
<\/li>\r\n<\/ol><\/div>\nConfigure ZappySys Data Gateway<\/span><\/h3>\r\nNow let's look at steps to configure Data Gateway after installation.\r\n
- \r\n \t
- Assuming you have installed\u00a0ZappySys ODBC PowerPack<\/a>\u00a0using default options (Which also enables Data Gateway Service)<\/li>\r\n \t
- Search \"Gateway\" in your start menu and click ZappySys Data Gateway\r\n
\r\n![\"Open](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/start-menu-open-zappysys-data-gateway.png\")
<\/a>\r\nOpening ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t
- First, make sure Gateway Service is running (Verify Start icon is disabled)<\/li>\r\n \t
- Also, verify Port on General Tab\r\n
\r\n![\"Port](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ZappySys-data-gateway-port-5000.png\")
<\/a>\r\nChecking port number setting on ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t
- Now go to Users tab. Click Add<\/strong> icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.\r\n
\r\n![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/gateway-configure-add-user.png\")
<\/a>\r\nAdding the Gateway user<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n <\/div>\n
Add a data source in the Gateway<\/h3>\n
It’s time to add a data source, which we will use to POST data to Power BI REST API:<\/p>\n
- \n
- In the Gateway, click on Data sources<\/strong> tab, click Add<\/strong>, give data source a name and set Connector Type<\/em> to Native – ZappySys JSON Driver<\/strong>:
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/gateway-create-datasource-1-1.png\")
<\/li>\n- Then click Edit<\/strong> to modify the data source:
\n![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/gateway-create-datasource-2-2.png\")
<\/li>\n- Finally, paste into Url <\/em>textbox Push URL<\/strong>\u00a0of the dataset which we saved in a previous step. Also, set HTTP Request Method<\/em> to POST<\/strong>:\n
![\"Configure](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/gateway-create-datasource-3.png\")
Configure REST API Connection for Power BI dataset insert (Using ZappySys JSON \/ REST Driver)<\/p><\/div><\/li>\n<\/ol>\n
Add a Linked Server to the Gateway<\/h3>\n
Once you configured the data source in Gateway, we can now set up a Linked Server in a SQL Server.\r\n- \r\n \t
- Open SSMS and connect to a SQL Server.<\/li>\r\n \t
- Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server...\r\n<\/strong>\r\n\r\n\r\n
![\"Add](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/create-new-linked-server-ssms.png\")
\r\n<\/a>\r\nAdding Linked Server in SQL Server<\/p>\r\n\r\n<\/div><\/li>\r\n \t
- Now enter the linked server name, select Provider as SQL Native Client.<\/li>\r\n \t
- Enter data source as GatewayServerName,PORT_NUMBER<\/span><\/strong>\u00a0where server name is where ZappySys Gateway is running (can be the same as SQL Server machine or a remote machine). Default PORT_NUMBER is 5000 but confirm that on the Gateway > General tab in case it's different.<\/li>\r\n \t
- Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column\r\n
\r\n![\"Configure](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/ssms-sql-server-configure-linked-server-2.png\")
\r\n<\/a>\r\nConfigure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection<\/p>\r\n<\/div>\r\n
\r\nINFO:<\/strong>- \r\n
- \r\n For SQL Server 2012, 2014, 2016, 2017, and 2019<\/strong>, use the SQL Server Native Client 11.0<\/em> as the Provider.\r\n <\/li>\r\n
- \r\n For SQL Server 2022 or higher<\/strong>, use the Microsoft OLE DB Driver for SQL Server<\/em> as the Provider.\r\n <\/li>\r\n <\/ul>\r\n<\/div><\/li>\r\n \t
- Click on Security Tab and select the last option \"Be made using this security context<\/strong>\". Enter your gateway user account here.\r\n
\r\n![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/add-linked-server-sql-server-3-security-2.png\")
\r\nConfiguring Linked Server credentials<\/p>\r\n<\/li>\r\n
- \r\n
Optional: Under the Server Options Tab, Enable RPC<\/b> and RPC Out<\/b> and Disable Promotion of Distributed Transactions(MSDTC)<\/b>.<\/p>\r\n\t\t
\r\n\t\t\t![\"RPC](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/linked-server-options-rpc-msdtc.png\" "\\"RPC")
\r\n\t\t\tRPC and MSDTC Settings<\/p>\r\n\t\t<\/div>\r\n
\r\n\r\n You need to enable RPC Out if you plan to use EXEC(...) AT [MY_LINKED_SERVER_NAME]<\/i><\/b> rather than OPENQUERY.\r\n
\r\n If don't enabled it, you will encounter the 'Server \"MY_LINKED_SERVER_NAME\" is not configured for RPC'<\/i> error.\r\n <\/p>\r\n\r\n Query Example:\r\n
EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]<\/code>\r\n <\/p>\r\n
\r\n\r\n If you plan to use 'INSERT INTO...EXEC(....) AT [MY_LINKED_SERVER_NAME]'<\/i><\/b> in that case you need to Disable Promotion of Distributed Transactions(MSDTC).\r\n
\r\n If don't disabled it, you will encounter the 'The operation could not be performed because OLE DB provider \"SQLNCLI11\/MSOLEDBSQL\" for linked server \"MY_LINKED_SERVER_NAME\" was unable to begin a distributed transaction.'<\/i> error.\r\n <\/p>\r\n\r\n Query Example:\r\n
Insert Into dbo.Products \r\nEXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]<\/pre>\r\n <\/p>\r\n
\r\n<\/li>\r\n \t- Click OK to save the Linked Server.<\/li>\r\n<\/ol><\/div>\n
Writing data into Power BI dataset from SQL Server<\/h3>\n
Once you add a Linked Server you can execute a query to push data into a Power BI streaming dataset:<\/p>\n
- \n
- In the same SQL Server Management Studio create a new query and paste the code below. In Body<\/em> attribute, we pass the JSON in the same format which was shown when we created a Power BI dataset:\n
SELECT * FROM\r\nOPENQUERY([GATEWAY], 'SELECT * FROM $\r\nWITH (Body=''[ \r\n\t{ \"Timestamp\" :\"2018-11-15T02:57:04.547Z\", \"Temperature\" : 50.5 },\r\n\t{ \"Timestamp\" :\"2018-11-16T02:57:04.547Z\", \"Temperature\" : 55.5 },\r\n\t{ \"Timestamp\" :\"2018-11-17T02:57:04.547Z\", \"Temperature\" : 60.5 },\r\n\t{ \"Timestamp\" :\"2018-11-18T02:57:04.547Z\", \"Temperature\" : 57.5 },\r\n\t{ \"Timestamp\" :\"2018-11-19T02:57:04.547Z\", \"Temperature\" : 62.3 }\r\n]''\r\n,meta=''[ { \"Name\": \"Dummy\", \"Type\": \"String\" }]'' )')<\/pre>\nNote:<\/strong> Use the same Linked Server name you created in a previous step. In our case, it is “GATEWAY”.<\/div><\/div><\/li>\n- Execute the query.<\/li>\n<\/ol>\n
The results: creating a Power BI report<\/h2>\n
Now it’s time to check the results and see if data went through:<\/p>\n
- \n
- Go to Power BI portal.<\/li>\n
- Select Datasets\u00a0<\/strong>and click on the add new report icon:\n
![\"Create](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/powerbi-create-report-from-streaming-dataset-1.png\")
Create new Power BI Report for real time dataset<\/p><\/div><\/li>\n
- Add a Line Chart, a Table and link them to the dataset.<\/li>\n
- You should see the data we pushed to the dataset:\n
![\"Showing](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/11\/powerbi-create-report-from-streaming-dataset-2.png\")
Showing real time data in Power BI dashboard<\/p><\/div><\/li>\n<\/ol>\n
Note:<\/strong> To see real-time data, pin a visual to a dashboard. Live data can be seen only in a dashboard since only there data is refreshed when a new piece of data is received. However, it does not work with reports – if you pin a whole report to a dashboard it won’t update data in real-time. <\/div><\/div>\nConclusion<\/h2>\n
You learned how to push data into a Power BI streaming dataset directly from SQL Server. It was possible to achieve that with ZappySys Data Gateway and SQL Server Linked Server option. At first, we created a streaming dataset in Power BI, then configured a datasource in the Gateway, followed with adding a Linked Server to the Gateway, executed T-SQL query in SSMS and pushed some data to the dataset and finally created a Power BI report to check the received data.<\/p>\n
When we set Historic data analysis<\/strong> option when creating a dataset, we, in turn, created a dataset of type Push <\/strong>(if we left it off, it would be of type Streaming<\/strong>). It allowed us\u00a0to create\u00a0a report easily on top of it, but it has certain limitations<\/a>. Just be aware when using this option and check Power BI service documentation<\/a> for more info.<\/p>\n
Finally, in SSMS, we executed a query with predefined data. Therefore in real life, if you want to push data from an existing table, you will have to build JSON from it. Make sure you construct a valid JSON<\/a> and escape the symbols that need to be escaped (such as a double-quote or a backslash<\/a>).<\/p>\n
References<\/h2>\n
https:\/\/powerbi.microsoft.com\/en-us\/<\/a><\/p>\n
https:\/\/docs.microsoft.com\/en-us\/rest\/api\/power-bi\/<\/a><\/p>\n
https:\/\/docs.microsoft.com\/en-us\/power-bi\/service-real-time-streaming<\/a><\/p>\n
- Execute the query.<\/li>\n<\/ol>\n
- \r\n For SQL Server 2022 or higher<\/strong>, use the Microsoft OLE DB Driver for SQL Server<\/em> as the Provider.\r\n <\/li>\r\n <\/ul>\r\n<\/div><\/li>\r\n \t
- Then click Edit<\/strong> to modify the data source:
- Search \"Gateway\" in your start menu and click ZappySys Data Gateway\r\n
- Then select API<\/strong> and click Next<\/strong>:\r\n
- SQL Server Management Studio<\/a> (SSMS) installed.<\/li>\n