Stream Dataset for local SQL Server Database Query

[raguyazhin]

Hi,

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

-- Ragu Thangavel

[Eric_Zhang · Accepted solution]

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

[XIOFEN_LIM]

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

[blackshep01]

@Eric_Zhang  Is there a tutorial on how to do?

[Eric_Zhang]

@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

[Anonymous]

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,

[mrslyfox]

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?

[Eric_Zhang]

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

[mrslyfox]

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?