Push Dataset in Power BI

power bi push dataset limitations
power bi streaming dataset
power bi api
hybrid dataset power bi
push dataset power bi embedded
power bi login
power bi on line
power bi ser

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

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)

Push data into a dataset, Deletes all rows from the specified table within the specified dataset from "My Workspace". Note: this API supports only Push datasets. Required scope: Dataset. REST API Limitations: See Power BI REST API limitations. Required scope: Dataset.ReadWrite.All To set the permissions scope, see Register an app. Datasets PostRowsInGroup: Adds new data rows to the specified table, within the specified dataset, from the specified workspace. Note: This API supports only Push datasets. REST API Limitations: See Power BI REST API limitations.

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

Push Datasets (Power BI Power BI REST APIs), Today, I am happy to announce an exciting new update to the Power BI connector for Microsoft Flow. Coming hot on the heels of our data alert  To make a call to a REST resource, you use a url that locates the resource, and you send a JavaScript Object Notation (JSON) string, which describes the dataset, to the Power BI service resource. A REST resource identifies the part of the Power BI service you want to work with. To push data into the dataset, the target resource is a Dataset.

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.

Push data to Power BI streaming datasets without writing any code , Hi, I need to Create a Live Dashboard, I have gone through the tutorials and I find Push Dataset as a suitable noe for my requirement. Is there  To push data into a Power BI dataset, you need to reference the table in the dataset. To reference a table in a dataset, you first need to get a Dataset ID . You get a Dataset ID using the Get Datasets operation.

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

Live Report Using Push Dataset, You can easily use something like PowerShell to push data into the Power BI streaming Duration: 5:46 Posted: Feb 6, 2019 Now, we’re at the last step of the Flow: we’re going to push this data into the Power BI streaming dataset that we created earlier. Go to “New step,” then “Add an action,” and then enter “Power BI” into the search box. Select “Add row to streaming dataset” from the actions list.

Create a Power BI streaming dataset for real-time dashboards , 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  Adds new data rows to the specified table within the specified dataset from "My Workspace". Note: This API supports only Push datasets. REST API Limitations: See Power BI REST API limitations. Required scope: Dataset.ReadWrite.All To set the permissions scope, see Register an app.

Push Dataset in Power BI, Push datasets are stored in Power BI online and can accept data via the Power BI REST API or Azure Streaming Analytics. One of the most  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

Power BI Streaming Data Sets: The Good, the Great, and the Gotchas, I have already covered how to create an API Enabled (Push DataSet) Power BI Dataset here and here. The datasets are created using our  HI I finally managed to create a report in my PBI Desktop with the push dataset I defined in the PBI service. however, there are a lot of features I want to use which have greyed for me. I want to know what I should do (i have a pro trial now, if its a matter of the license please tell me) and also,

Comments
  • Hi TJ, I have given a thought about Direct Query, but Direct Query might cause Performance Issues as my DB size is huge and filtering data would hang my report or Dashboard. I agree with Alexander Volok...I am looking for the Sample code that will fetch the SQL tabels and create Dataset in Power BI