Import Azure Log Analytics data into Power BI
Power BI is a cloud based business analytics service from Microsoft that provides rich visualizations and reports for analysis of different sets of data. You can import the results of a Log Analytics log search into a Power BI dataset so you can take advantage of its features such as combining data from different sources and sharing reports on the web and mobile devices.
To import data from a Log Analytics workspace into Power BI, you create a dataset in Power BI based on a log search query in Log Analytics. The query is run each time the dataset is refreshed. You can then build Power BI reports that use data from the dataset. To create the dataset in Power BI, you export your query from Log Analytics to Power Query (M) language. You then use this to create a query in Power BI Desktop and then publish it to Power BI as a dataset. The details for this process are described below.
Start by creating a log search that returns the data from Log Analytics that you want to populate the Power BI dataset. You then export that query to Power Query (M) language which can be used by Power BI Desktop.
- Create the log search in Log Analytics to extract the data for your dataset.
If you're using the log search portal, click Power BI. If you're using the Analytics portal, select Export > Power BI Query (M). Both of these options export the query to a text file called PowerBIQuery.txt.
Open the text file and copy its contents.
Import query into Power BI Desktop
Power BI Desktop is a desktop application that allows you to create datasets and reports that can be published to Power BI. You can also use it to create a query using the Power Query language exported from Log Analytics.
- Install Power BI Desktop if you don't already have it and then open the application.
Select Get Data > Blank Query to open a new query. Then select Advanced Editor and paste the contents of the exported file into the query. Click Done.
The query runs, and its results are displayed. You may be prompted for credentials to connect to Azure.
Type in a descriptive name for the query. The default is Query1. Click Close and Apply to add the dataset to the report.
Publish to Power BI
When you publish to Power BI, a dataset and a report will be created. If you create a report in Power BI Desktop, then this will be published with your data. If not, then a blank report will be created. You can modify the report in Power BI or create a new one based on the dataset.
Create a report based on your data. Use Power BI Desktop documentation if you're not familiar with it. When you're ready to send it to Power BI, click Publish. When prompted, select a destination in your Power BI account. Unless you have a specific destination in mind, use My workspace.
When the publishing completes, click Open in Power BI to open Power BI with your new dataset.
Configure scheduled refresh
The dataset created in Power BI will have the same data that you previously saw in Power BI Desktop. You need to refresh the dataset periodically to run the query again and populate it with the latest data from Log Analytics.
Click on the workspace where you uploaded your report and select the Datasets menu. Select the context menu next to your new dataset and select Settings. Under Data source credentials you should have a message that the credentials are invalid. This is because you haven't provided credentials yet for the dataset to use when it refreshes its data. Click Edit credentials and specify credentials with access to Log Analytics.
Under Scheduled refresh turn on the option to Keep your data up to date. You can optionally change the Refresh frequency and one or more specific times to run the refresh.