PowerBI — how the data makes it into a dashboard
PowerBI is powerful and allows for interactive reports and way more than that.
A basic question you will face early on when dealing with PowerBI: How does my data actually end up being sucked into a PowerBI dashboard? (Or PowerBI data set, to be more accurate.)
Well. Here is a quick overview.
You have a choice between these methods, further explained below:
- Real Time Integration (recurring sync)
- OneDrive or Sharepoint Teams (recurring sync)
- One-off uploads via BI Studio: not further explained (one-off)
- Programmatic integration using APIs (one-off or recurring)
Real Time Integration
PowerBI comes with a selection of data sets you can utilize to ingest data and show in real-time.
There are three types of real-time datasets which are designed for display on real-time dashboards:
>> Push dataset
>> Streaming dataset
>> PubNub streaming dataset
You can add Power BI as an output within Azure Stream Analytics (ASA), and then visualize those data streams in the Power BI service in real time. This section describes technical details about how that process occurs.
ASA and streaming datasets are an excellent choice for IOT scenarios and real time dashboarding. A word of caution is however due.
Do note this statement, as of December 19:
Power BI can be called roughly once per second. Streaming visuals support packets of 15 KB. Beyond that, streaming visuals fail (but push continues to work). Because of these limitations, Power BI lends itself most naturally to cases where Azure Stream Analytics does a significant data load reduction. We recommend using a Tumbling window or Hopping window to ensure that data push is at most one push per second, and that your query lands within the throughput requirements.
What you need for Azure Stream Analytics:
An Azure Stream Analytics job consists of an input, query, and an output. Stream Analytics ingests data from Azure Event Hubs, Azure IoT Hub, or Azure Blob Storage. The query, which is based on SQL query language, can be used to easily filter, sort, aggregate, and join streaming data over a period of time. You can also extend this SQL language with JavaScript and C# user defined functions (UDFs).
There are some practical examples of how ingesting data via the three options mentioned works.
The Blob Storage option is in particular easy to use and cost efficient.
To process the data as a stream using a timestamp in the event payload, you must use the TIMESTAMP BY keyword. A Stream Analytics job pulls data from Azure Blob storage input every second if the blob file is available. If the blob file is unavailable, there is an exponential backoff with a maximum time delay of 90 seconds.
OneDrive or Sharepoint Teams
By far the easiest option to push data into PowerBI is going via OneDrive or Sharepoint Teams.
Beware of this constraint though:
The maximum file size you can import into Power BI is 1 gigabyte.
Another constraint: Updates are no more recent than 1h!
OneDrive — Business — If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, this is by-far the most effective way to keep your .CSV file and your dataset, reports, and dashboards in Power BI in-sync. Because both Power BI and OneDrive are in the cloud, Power BI connects to your file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI.
OneDrive — Personal — If you save your files to your own OneDrive account, you’ll get many of the same benefits as you would with OneDrive for Business. The biggest difference is when you first connect to your file (using Get Data > Files > OneDrive — Personal) you’ll need to sign in to your OneDrive with your Microsoft account, which is usually different from what you use to sign in to Power BI. When signing into your OneDrive with your Microsoft account, be sure to select the Keep me signed in option. This way, Power BI will be able to connect to your file about every hour and make sure your dataset in Power BI is in-sync.
SharePoint Team-Sites — Saving your Power BI Desktop files to SharePoint — Team Sites is much the same as saving to OneDrive for Business. The biggest difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.
Programmatic integration via APIs
Of course there are APIs for Bulk Import:
https://docs.microsoft.com/en-us/rest/api/power-bi/imports
This way you can import up to 10GB max.
If you need more than that, consider what you really need for creating a dashboard and rather think of data streaming.
Some advice on when to use which approach:
https://senturus.com/blog/power-bi-four-ways-to-connect-to-and-prep-data/
