topic Stream Dataset for local SQL Server Database Query in Developer

Stream Dataset for local SQL Server Database Query

raguyazhin — Fri, 30 Dec 2016 03:54:55 GMT

Hi,

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

-- Ragu Thangavel

Re: Stream Dataset for local SQL Server Database Query

Eric_Zhang — Fri, 30 Dec 2016 07:49:07 GMT

@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.

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

Re: Stream Dataset for local SQL Server Database Query

mrslyfox — Mon, 27 Feb 2017 07:29:07 GMT

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?

Re: Stream Dataset for local SQL Server Database Query

mrslyfox — Mon, 27 Feb 2017 07:31:26 GMT

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?

Re: Stream Dataset for local SQL Server Database Query

Eric_Zhang — Mon, 06 Mar 2017 08:50:33 GMT

@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.

Re: Stream Dataset for local SQL Server Database Query

blackshep01 — Tue, 04 Apr 2017 16:05:42 GMT

@Eric_Zhang Is there a tutorial on how to do?

Re: Stream Dataset for local SQL Server Database Query

Eric_Zhang — Wed, 05 Apr 2017 03:18:17 GMT

@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

Re: Stream Dataset for local SQL Server Database Query

Anonymous — Mon, 02 Apr 2018 07:17:48 GMT

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,

Re: Stream Dataset for local SQL Server Database Query

XIOFEN_LIM — Wed, 31 Mar 2021 02:00:16 GMT

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??