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

Stream Dataset for local SQL Server Database Query

Hi,

 

how to create Power BI Stream Datasets and Push Data from local SQL Server Query Data to that Stream datasets.

 

 

-- Ragu Thangavel

-- Ragu Thangavel
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Stream Dataset for local SQL Server Database Query

@raguyazhin

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.

Capture.PNGCapture2.PNG

 

$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();

 

View solution in original post

7 REPLIES 7
Moderator Eric_Zhang
Moderator

Re: Stream Dataset for local SQL Server Database Query

@raguyazhin

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.

Capture.PNGCapture2.PNG

 

$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();

 

View solution in original post

mrslyfox Regular Visitor
Regular Visitor

Re: Stream Dataset for local SQL Server Database Query

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?

 

 

 

mrslyfox Regular Visitor
Regular Visitor

Re: Stream Dataset for local SQL Server Database Query

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?

Moderator Eric_Zhang
Moderator

Re: Stream Dataset for local SQL Server Database Query

@mrslyfox

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.

 

blackshep01 Frequent Visitor
Frequent Visitor

Re: Stream Dataset for local SQL Server Database Query

@Eric_Zhang  Is there a tutorial on how to do?

Moderator Eric_Zhang
Moderator

Re: Stream Dataset for local SQL Server Database Query

@blackshep01

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 

How do I query a SQL Server DB using PowerShell

Invoke-RestMethod

ravsha85 Member
Member

Re: Stream Dataset for local SQL Server Database Query

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,

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,642)