Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
raguyazhin
Regular 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
Eric_Zhang
Microsoft Employee
Microsoft Employee

@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

8 REPLIES 8
Eric_Zhang
Microsoft Employee
Microsoft Employee

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

 

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

@Eric_Zhang  Is there a tutorial on how to do?

@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
Not applicable

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?

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

 

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?

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.