Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
how to create Power BI Stream Datasets and Push Data from local SQL Server Query Data to that Stream datasets.
-- Ragu Thangavel
Solved! Go to Solution.
It would require quite some coding skill.
You can find the API sample to push data to your stream dataset in Power BI service. Then you can query your databases and send data via a http request in any language you master. Eg in this case, I test it in Powershell.
$SqlServer = 'yourSQLserverName'; $SqlDatabase = 'databaseName'; $SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase; $SqlQuery = "SELECT * FROM streamData;"; $SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand; $SqlCommand.CommandText = $SqlQuery; $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString; $SqlCommand.Connection = $SqlConnection; $SqlConnection.Open(); $SqlDataReader = $SqlCommand.ExecuteReader(); ##you would find your own endpoint in the Power BI service $endpoint = "https://api.powerbi.com/beta/72f98xxxxxxx011db47/datasets/d685398b-86xxxxx47f95a9b20e4/rows?key=E5sABoNsS3uxxxxcdwr6QVpJSAgOA6juxxxxxvNYRBjtuLfIg%3D%3D" #Fetch data and write out to files while ($SqlDataReader.Read()) { $payload = @{ "product" =$SqlDataReader['product'] "sales" =$SqlDataReader['sales'] "datetime" =$SqlDataReader['datetime'] } Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload)) } $SqlConnection.Close(); $SqlConnection.Dispose();
It would require quite some coding skill.
You can find the API sample to push data to your stream dataset in Power BI service. Then you can query your databases and send data via a http request in any language you master. Eg in this case, I test it in Powershell.
$SqlServer = 'yourSQLserverName'; $SqlDatabase = 'databaseName'; $SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase; $SqlQuery = "SELECT * FROM streamData;"; $SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand; $SqlCommand.CommandText = $SqlQuery; $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString; $SqlCommand.Connection = $SqlConnection; $SqlConnection.Open(); $SqlDataReader = $SqlCommand.ExecuteReader(); ##you would find your own endpoint in the Power BI service $endpoint = "https://api.powerbi.com/beta/72f98xxxxxxx011db47/datasets/d685398b-86xxxxx47f95a9b20e4/rows?key=E5sABoNsS3uxxxxcdwr6QVpJSAgOA6juxxxxxvNYRBjtuLfIg%3D%3D" #Fetch data and write out to files while ($SqlDataReader.Read()) { $payload = @{ "product" =$SqlDataReader['product'] "sales" =$SqlDataReader['sales'] "datetime" =$SqlDataReader['datetime'] } Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload)) } $SqlConnection.Close(); $SqlConnection.Dispose();
Hi @Eric_Zhang ,
I am new to this API with PowerShell. May I know we still need the data gateway if using this method to puch data for BI??
It is a Powershell script, so if you'd like to learn Powershell, you'll find the tutorial in the Internet everywhere.
As to this case, the shortcuts can be
Hi Eric,
I Just went through your Ideas, Is it possible to use this case for a Push Dataset instead of a Streaming Dataset?
Regards,
Hello,
Could you please describe a little bit more your API?
If I have a local MS SQL with ip 10.0.0.1 and db name 'production_db'
with constantly updated Fact_Produced table, with a two columns DateTime and ProducedKg
how your query will looks like and should I use some additional MS app to get connection to the my DB?
The sample PowerShell actually retrieves data from SQL Server and send data to the streaming dataset.
When saying contantly updated, I think you could use a while loop and change the code accordingly.
Hello,
Could you please describe a little bit more your API?
If I have a local MS SQL with ip 10.0.0.1 and db name 'production_db'
with constantly updated Fact_Produced table, with a two columns DateTime and ProducedKg
than how your query will looks like and should I use some additional MS app to get connection to the my DB?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |