How to use Power BI to span multiple custom log types from Log Analytics
This is the fifth in a six part series which will provide details on a solution I have been working on which will gather information from a variety of sources to forecast how much electricity will be generated by a residential solar panel system. This example shows how OMS can be used to gather data from multiple sources, how to visualize this data in Power BI and how to use a variety of data sources in OMS to forecast likely future results. This series includes the following posts:
- Gathering weather data into Microsoft OMS (the script details are available here)
- Using Power BI to visualize weather data from Microsoft OMS
- How to add weather forecasting information into Microsoft OMS
- How to add SolarCity electricity generation information into Microsoft OMS
- How to use Power BI to cross multiple custom log types
- How to bring this all together and forecast electricity generation
In this blog post we will see an example of how Power BI can be used to visualize data across multiple custom log types in OMS.
Getting the data out of Log Analytics:
We have three custom lists from previous posts (SolarCity_CL, OpenWeather_CL, OpenWeatherForecast_CL). To create a Power BI report or dashboard which spans data from these custom log types we want to write a single query which spans these custom logs.
TIP: Simply exporting data from multiple custom logs isn’t necessarily going to give you what you need in Power BI. You need to have common fields which exist in each of the custom log types. For my sets of data I use the built-in TimeGenerated field plus I added a common field called “City” which lists as “City_s” since it is a string value. These common fields make it possible to show the relationship between these different types of data as shown later in this blog post.
We also want to restrict this query to gather only one days’ worth of data and then schedule the data to update on a daily basis. Here’s the query which I used to export the data using the new KQL query language: (note the union at the end which brings together each of these custom lists into a single query)
let SolarCity=SolarCity_CL | where TimeGenerated >= ago(1d);
let OpenWeather=OpenWeather_CL | where TimeGenerated >= ago(1d);
let OpenWeatherForecast=OpenWeatherForecast_CL | where TimeGenerated >= ago(1d);
SolarCity | union OpenWeather, OpenWeatherForecast
To see how to get this data into Power BI see this previous blog post.
Scheduling the dataset to update in Power BI:
Once we have the data available as a dataset we need to configure it to update on a scheduled basis (for details see this blog post).
Creating the report in Power BI:
As with most reports in Power BI, I recommend starting with a slicer. In the example below I use two of them – one to set the boundaries for the time of the data which will be displayed and a second which chooses which city data will be represented for. This single report is now showing data from each of the custom log types.
- The map graphic which shows the various locations of the cities (common to all three custom log types).
- The top right chart shows the temperature average (OpenWeather_CL)
- The middle right chart shows the energy which is generated (SolarCity_CL)
- The table in the bottom left shows the weather forecast information (OpenWeatherForecast_CL)
If we choose only the Frisco city value, it updates each of the items below to show only Frisco data. The Energy Generated chart is still populated because the records for Energy Generated are from the city of Frisco. The map updates to only show a single icon, and the temperature average now shows for only the single location.
If we choose a location which does not have Energy Generated then the Energy Generated graph is not populated and if we choose a location which does not have weather forecast information then the table for weather forecasting is blank as well.
Over time the amount of data which is available increases and you can see the patterns which emerge like in the example below.
Summary: In this blog post we have shown how you can take data from multiple custom lists in OMS and make them available to Power BI. Once this data is in Power BI we can create reports and dashboards for these various custom lists. Key lessons learned on this were to have common fields for each custom list (TimeGenerated and City_s in this example). Once this data is in place we can query and visualize the data as shown above. In the next part of this series we will bring this all together and showcase some really cool integrations which can now be done with this data!