CODEWITHSUNDEEP
powerbi
Ravi
Ravi

Reputation: 45

Push Dataset in Power BI

I'm looking for a sample code which get data from SQL Server and push this to PowerBI in real time, This is basically using the Push Dataset option. I am not sure how to Push the datas from SQL

Thanks

Upvotes: 1

Views: 6124

Answers (4)

Kiran Madake
Kiran Madake

Reputation: 21

You Can push the data by using power shell which where you need to add the your api link and you have to put your sql connection string and you and you ca fire a query to same data set by declaring it into code you can refer the below code which will help you to understand how to push the data into your data set once you run your power shell script then data will be pushed to power bi data set and you can see your live

$SqlServer = ''; #your server name
$SqlDatabase = ''; #your database name
$uid =""   #User id
$pwd = "*****" # your password
$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI;uid=$uid;Password=$pwd' -f $SqlServer, $SqlDatabase;

$SqlQuery = "SELECT * FROM abc;";

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText = $SqlQuery;
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;
$SqlCommand.Connection = $SqlConnection;
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCommand
$SqlConnection.Open();
$SqlDataReader = $SqlCommand.ExecuteReader();

##you would find your own endpoint in the Power BI service
$endpoint = "" ## add your api link middle of endpoint ""


#Fetch data from your table and write out to files
while ($SqlDataReader.Read()) {
    $payload =  
    @{
    "Date" =$SqlDataReader['Date']
    "First Name" =$SqlDataReader['Name']
    "Production" =$SqlDataReader['prdt']
    } 
    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
}


$SqlConnection.Close();
$SqlConnection.Dispose();

## every time you run script data will automaticaly pushed from sql server to your power bi report

e streaming chart

Upvotes: 0

Alibek Jakupov
Alibek Jakupov

Reputation: 620

Why not creating a custom streaming dataset and 'pushing' your sql data directly. In this case you may use either Power apps (create a flow and a trigger on insert) or simply right some code to push your data in a form of a post request.

For instance you have your sql table containing a value you want to push. Thus the steps should the following:

  1. Create a dashboard

  2. Add tile

  3. Choose 'Custom Streaming Dataset' as a source

  4. Define the data colums to be pushed (for instance train_number and departure_time)

  5. Copy the API
  6. From your code (Python for example) get the data, convert it to json and publish
  7. Go back to power bi, add a tile from newly created streaming dataset and chose the visual type. Important: the visuals are quite limited

Here is a sample code in python:

def data_generation(counter=None):
    # get your SQL data and save it into 2 variables (row by row)
    return [train_number, departure_time]
while True:
    data_raw = []
    # simple counter increment
    counter += 1
    for i in range(1):
       row = data_generation(counter)
       data_raw.append(row)
    # set the header record
    HEADER = ["train_number", "departure_time"]
    # generate a temp data frame to convert it to json
    data_df = pd.DataFrame(data_raw, columns=HEADER)
    # prepare date for post request (to be sent to Power BI)
    data_json = bytes(data_df.to_json(orient='records'), encoding='utf-8')
    # Post the data on the Power BI API
    req = requests.post(PowerBI_REST_API_URL, data_json)
    print("Data posted in Power BI API")
    print(data_json)
    # wait 5 seconds
    time.sleep(5)

Upvotes: 2

TJ_
TJ_

Reputation: 647

You can't 'push' data from SQL, but you can use DirectQuery instead of Import. Then your data will always be actual.

Just connect to a SQL Server, and choose for 'Direct Query' and you'll be ready to go.

Edit: With @Alexander Volok, of course, with an application and/or API calls you can push data into Power BI. My bad.

Upvotes: 0

Alexander Volok
Alexander Volok

Reputation: 5940

Microsoft published similar walk-through. It has to be slightly expanded with SQL Server calls though:

Push data into a Power BI dataset

---> Create Dataset

Upvotes: 1

Related Questions