Together Microsoft Power BI and Microsoft Azure services form a very strong partnership. Today Power BI – a cloud-based business analytics services – delivers on its promise to address common Business Intelligence (BI) challenges by easily providing the right data for the right user at the right time.
Peter Myers is an independent BI Expert and SQL Server MVP based in Melbourne, Australia. .
The purpose of this article isn’t to detail everything that Power BI can achieve (that would require many articles!). Rather, the purpose is to explain the integration with familiar Microsoft products, with commitment for scale and availability in Azure. In combination, Microsoft Azure and Microsoft Power BI have built-in connectivity and integration to bring compelling BI efforts to life.
Let’s explore four scenarios that describe how Power BI and Azure can work together: Direct Connect, Power BI Desktop Solutions, Real-time Dashboards, and Advanced Analytics.
Within the Power BI site, by clicking “Getting Data”, and then with just a few more clicks the user arrives at the Get Data page, and can then connect to an Azure SQL Database, an Azure SQL Data Warehouse, or Spark on Azure HDInsight (a fast and general engine for large-scale Big Data processing with Azure HDInsight).
Once valid connection details and credentials have been entered, direct connect datasets are made added to the user’s workspace to enable the creation of reports, and the expression of visualizations on dashboards. It is important to understand that direct connect datasets do not need to be refreshed, as when users explore reports, query results are retrieved from Azure. Dashboard tiles automatically refresh every 15 minutes.
Notably these experiences are intended for users who are familiar with the databases and entities they connect to, and are available only with the Power BI Pro license.
Power BI Desktop Solutions
Power BI Desktop is a visual data exploration and reporting tool, and a freely downloadable 32- or 64-bit desktop application optimized for use with the Power BI service. It enables skilled users, like analysts, to:
- Create queries to filter, cleanse and reshape data
- Configure relationships to establish the foundations of a model
- Enhance the model with calculation and presentation logic
- Design interactive reports with a broad range of modern data visualizations
- Publish solutions directly to the Power BI service
There are literally dozens of supported data source types, including file, database and Azure sources (and the list is growing with each monthly update!). Of relevance to the purpose of this article, Power BI Desktop supports connecting to and retrieving (caching) data from Azure services. Specifically these services are: Azure SQL Database, Azure Marketplace (for the discovery and consumption of commercial datasets), Azure HDInsight (Big Data), Azure Blob Storage and Azure Table Storage.
A Power BI Desktop file can define queries to source data from Azure, and then integrate them with other queries, perhaps sourced from internal or other external data. Once published to the Power BI service, the file is available as a dataset which may be configured for data refresh by providing valid credentials. All Azure source types can be scheduled to refresh.
Use Azure Stream Analytics to push live, streaming data to Power BI, enabling real-time dashboards at scale, over data from devices and applications. The streaming service has the ability to ingest millions of events per second, and can perform aggregation over time windows (for example, compute the number of web requests received in the last minute and display the result a dashboard tile, updating each second).
Azure Stream Analytics can ingest events from Azure Service Bus event hubs.
Lastly, more complex scenarios can be achieved by coalescing Azure services together. Harness the potential of Azure HDInsight (Big Data processing), Azure Machine Learning (predictive analytics), and Azure Data Factory (orchestration at scale), and also Azure Stream Analytics, to drive truly advanced analytics delivered with Power BI.