en en ms fi da da da da ca da da da ms hbs_lat ms ms ms en da da ms ms da es da de ro en da da da da de da da de en it da da da es da da en da da da en da sv de da en da en en en en en da pt fr fr en de en sv da da da da sv da da en en en de en en en en de en en en en en en en en en en en en en en en en ceb en fa en fr en en pt en tr en en kn en en en en en en en en en fr en en en en fr fr fi pt en de en en en en en en en it en en en en en en en en en en en en en en en en en en en en fr de en id en fr en da da da da da en da da da da da en tr da ca en en id en en en en en en en en ms ms ms en en ml da en en nl nl da en da de de en en nl en en nl nl sv en da de de en en nl en en nl nl sv en da de de en en nl en en sv nl da en da de da en en nl en en nl nl sv en da da da en en nl en en nl nl sv en da da da en en nl en en nl nl sv en da de de en en nl en en nl nl da en da de de en en nl en en nl nl da en da de de en en nl en en nl en da ms en en en sv da fr da it de en ms ms en it da da de en en en sv da en da en da da en da da da hbs_lat da de de de el de en en tr en en en en en en en es es fi fr fr fr fr hu it it ja ko da nl nl pl pt pt ru sv th tr zh zh ar he
Monitor vehicle traffic using Power BI | Microsoft Power BI-blog | Microsoft Power BI
Office 365 + Power BI
Today we have Spyros Sakellariadis, joining us again to continue our series of how to use Power BI to monitor real-time IoT events.  Take it away Spyros!
Who hasn’t wanted to have a personalized view of te vehicle traffic on te route to and from their office, for example? Let’s do it by getting data from a public data feed into Azure and Power BI! At te same time, this will serve as an example of how to get data from thousands of public data feeds and visualize them with Power BI.
In a previous post, I showed how to get data from IoT sensors into Azure and Power BI based upon a simple infrastructure that we published in an open source project called ConnectTheDots. Te constraint in that scenario is that those sensors need to be ones which you can access and program, and probably own. There is lots of valuable information, however, in public data sources which it would be great to be able to munge and view.
To keep things simple, I’m going to use te same basic infrastructure to get te traffic data into Power BI, as I used in te previous post – with a twist. Instead of connecting te data sources (sensors in te previous blog, te WSDOT web service in this one) to a gateway running on a Raspberry Pi, we will run that same gateway code in Azure. Once we have pulled data from te WSDOT web service and sent it to an Azure Event hub, te rest is basically te same as we did last time. Easy as pie.
Since I live in te Seattle area, I am interested in knowing about te traffic in te state of Washington. I really, really care about te traffic on te two bridges separating Seattle and Bellevue (on highways I-90 and I-520), as I have to drive over them daily. I thought it might be a daunting task to collect that data, but fortunately te Washington State Department of Transportation provides data for 170 permanent traffic recorder locations in Washington State, including those two highways. Most are updated hourly, some as frequently as every 20 seconds. This data is accessed through a single gateway and API, as described on their website at https://wsdot.wa.gov/Traffic/api/. If you want to understand te traffic on te roadways, selecting te Traffic Flow link shows te function calls and fields retrieved, and te Help link shows how to call te service:
Te site also contains a button to request te access code to include in te web service call. If you just paste that URL into your browser, you will get a batch of data similar to te following, refreshed every 20 seconds:
76, 4/29/2015 3:36:22 PM, Olympic, 005es13330, 005, Tacoma Ave, 133.3, 47.231150214,-122.441859377, 4, NB
267, 4/29/2015 3:36:22 PM, Olympic, 016es00965, 016,36th St., 9.65, 47.291790549, -122.564682487, 1, EB
282, 4/29/2015 3:36:22 PM, Olympic, 016es00418, 016, Jackson DS, 4.18, 47.258535035, -122.527057529, 1, EB
At this point, we have our data source. Now it is just a matter of getting it into Azure and Power BI.
Getting te data into Azure
To get te data from te WSDOT web service we wrote a simple Azure worker role that pulls data from te WSDOT URL shown above every 20 seconds, parses te data stream, and immediately sends it to an Azure instance of te ConnectTheDots gateway. Sample code, which you will need to modify, is available on te TrafficFlow branch of ConnectTheDots on GitHub.
In te TrafficFlow worker role we parsed te data as JSON and shortened te names provided by te WSDOT schema. Te first row of te data shown above would be parsed and formatted as follows:
“LocationDescription”:  “Tacoma Ave “,
And here is an eksport to Excel of a few of te 5,742,180 records received in 3 weeks (yes, 5.7M records!):
Now we just have to get this data into Azure, but it is worth going on one last tangent before showing how to construct te Power BI dashboard. As I mentioned earlier, I am interested in te traffic on te I-520 bridge as I have to commute over it. We can take te data for te last few weeks and filter it for (Road Name = 520 AND Location Description = Midspan), map te Flow Value to a color, and voila, te eastbound traffic pattern emerges:
Note one thing, though, that will be relevant when we create te Power BI dashboard – te time reported by te WSDOT web service and forwarded to our Event Hub is UTC, so te above table is really showing traffic around 1pm Seattle time, not 8pm.
Creating te Power BI dashboard
Once our Azure worker role has sent te data to an Azure Event Hub, we can create a Stream Analytics job that queries te Event Hub with an Output to Power BI, just like we did in te IoT example from te previous blog. In this case, te query we have created is as follows:
WHEN Value <2 THEN ‘Light’
WHEN Value <3 THEN ‘Moderate’
WHEN Value <4 THEN ‘Heavy’
DATETIMEFROMPARTS(DATEPART(year,TimeCreated),DATEPART(month,TimeCreated), DATEPART(day,TimeCreated), DATEPART(hour,TimeCreated)-7,DATEPART(minute,TimeCreated), DATEPART(second,TimeCreated),00) AS TimeCreatedPST
Te only tricky thing in te SQL query is to convert te UTC time into PST and adjust for daylight savings time. It would be more flexible to do this in te worker role in C#, where we could query te difference between UTC and PST and apply that instead of hard-coding 7 hours into te statement above, but we decided it was best to keep everything in UTC in te worker role to make it easier to combine with results from multiple time zones.
When creating te output to Power BI you need to specify a DATASET NAME, which will appear in te Dataset list on te Microsoft Power BI dashboard. I chose to call te database “WSDOTdata”, and te corresponding table “WSDOTtable”.
For detailed instructions on creating a Power BI dashboard, please refer to te previous blog. Te essential steps to create a first report are repeated here:
1. Log in to https://app.powerbi.com, then create a dashboard by clicking “+” in te left menu and save. I created te Dashboard “TrafficFlow”
2. Select te Dataset, in my case “WSDOTdata”. You will see a blank dashboard with a field picker on te right. Given te way we formatted JSON after te Azure worker role pulled it from WSDOT, this is how it appeared in te field picker in Power BI:
3. Add fields Axis = timecreatedpst and Value=”∑ Max of value”.
5. On te same page, create three slicers, one for roadname, one for locationdescription, and one for direction. Save te report.
6. Using te slicers, select various roads, locations, and directions and pin each of them successively to te dashboard. For my first chart, I selected I-5 at 40th Ave W, going Northbound, and got te following chart:
This corresponds to almost equal ‘Light’ and ‘Moderate’ traffic, as you might expect for te early rush hour timeframe.  My nemesis, I-520, is somewhat worse, showing mostly ‘Moderate’ traffic:
Here is a dashboard with four locations shown:
Now this is beginning to tell me something useful! From my office, if I want to leave now to catch te ferry at Mukilteo, I can either go west on I-520 (Moderate traffic) or I-90 (mostly Stop and Go), then north either on I-405 (also mostly Stop and Go) or on I-5 (mostly Moderate). Decision clear… I-520 to I-5. Mind you, if I wait an hour, I-520 will be jammed too, and I might as well wait till after dinner before heading out.
There are thousands of public data sources you can access, and getting that data into Power BI is really not that different from accessing sensor data. Find a few, and start working with them along te lines shown in this post to see how easy it is to extract and present valuable insights. Even better, start thinking about how you can do more with te insights from te data, for example, sending alerts or notifications based upon various criteria, or spawning actions such as dispatching a technician, placing an order, or changing te settings on a thermostat.
As I mentioned last time, for further reading, here are some useful links on te Power BI side:
Tilmeld dig Power BI-nyhedsbrevet
Tilmeld dig nedenfor for at modtage de seneste nyheder fra Power BI direkte i din indbakke.
Land Afghanistan Albanien Algeriet Amerikansk Samoa Andorra Angola Anguilla Antarktis Antigua og Barbuda Argentina Armenien Aruba Aserbajdsjan Australien Bahamas Bahrain Bailiwick of Jersey Bangladesh Barbados Belgien Belize Benin Bermuda Bhutan Bolivia Bonaire Bosnien-Hercegovina Botswana Bouvetøen Brasilien Brunei Darussalam Bulgarien Burkina Faso Burundi Cabo Verde Cambodja Cameroun Canada Caymanøerne Chile Cocosøerne (Keelingøerne) Colombia Comorerne Congo Cookøerne Costa Rica Côte d'Ivoire Curaçao Cypern Danmark De Amerikanske Jomfruøer De Amerikanske oversøiske øer De Britiske Jomfruøer De Forenede Arabiske Emirater Den Centralafrikanske Republik Den Demokratiske Republik Congo Den Dominikanske Republik Det Britiske Territorium i Det Indiske Ocean Det palæstinensiske selvstyre Djibouti Dominica Ecuador Egypten El Salvador Eritrea Estland Etiopien Falklandsøerne Fiji Filippinerne Finland Frankrig Fransk Guyana Fransk Polynesien Franske Oversøiske Områder Færøerne Gabon Gambia Georgien Ghana Gibraltar Grenada Grækenland Grønland Guadeloupe Guam Guatemala Guernsey Guinea Guinea-Bissau Guyana Haiti Heard- og McDonaldøerne Honduras Indien Indonesien Iran Irland Island Isle of Man Israel Italien Jamaica Jan Mayen Japan Jordan Juleøen Kasakhstan Kenya Kina Kirgisistan Kiribati Korea Kosovo Kroatien Kuwait Laos Lesotho Letland Libanon Liberia Liechtenstein Litauen Luxembourg Madagaskar Malawi Malaysia Maldiverne Mali Malta Marokko Marshalløerne Martinique Mauretanien Mauritius Mayotte Mexico Mikronesien Moldova Monaco Mongoliet Montenegro Montserrat Mozambique Namibia Nauru Nederlandene Nepal New Zealand Nicaragua Niger Nigeria Niue Nordmakedonien Nordmarianerne Norfolk Island Norge Ny Kaledonien Oman Pakistan Palau Panama Papua Ny Guinea Paraguay Peru Pitcairn Polen Portugal Puerto Rico Qatar Reunion Rumænien Rwanda Saba Saint Barthélemy Saint Kitts og Nevis Saint Lucia Saint Martin Saint Pierre og Miquelon Saint Vincent og Grenadinerne Salomonøerne Samoa San Marino São Tomé og Príncipe SAR Hongkong SAR Macao Saudi-Arabien Schweiz Senegal Serbien Seychellerne Sierra Leone Singapore Sint Eustatius Sint Maarten Slovakiet Slovenien Somalia South Georgia og De Sydlige Sandwichøer Spanien Sri Lanka St. Helena, Ascension, Tristan da Cunha Storbritannien Sudan Surinam Svalbard Sverige Swaziland Sydafrika Sydsudan Syrien Tadsjikistan Taiwan Tanzania Tchad Thailand Timor-Leste Tjekkiet Togo Tokelau Tonga Trinidad og Tobago Tunesien Turkmenistan Turks- og Caicosøerne Tuvalu Tyrkiet Tyskland Uganda Ukraine Ungarn Uruguay USA Usbekistan Vanuatu Vatikanstaten Venezuela Vietnam Wallis og Futunaøerne Yemen Zambia Zimbabwe Ækvatorialguinea Østrig Ålandsøerne
Jeg vil gerne modtage Power BI-nyhedsbrevet. Erklæring om beskyttelse af personlige oplysninger.
Deltagelse kræver, at dine personlige oplysninger overføres til andre lande, som Microsoft driver forretning i, herunder USA. Når du sender denne formular, accepterer du overførslen af dine data uden for Kina. Erklæring om beskyttelse af personlige oplysninger.
Hvad er Power BI?
Power BI er en pakke med virksomhedsanalyseværktøjer til dataanalyse og vidensdeling. Overvåg din virksomhed, og få hurtigt svar med omfattende dashboards, der er tilgængelige på alle enheder.
Få de seneste nyheder fra Power BI-blog
Abonner via mail Du skal angive en gyldig mailadresse Du har angivet en personlig mailadresse. Angiv din arbejds- eller skolemailadresse. Noget gik galt. Prøv igen senere.
Beskyttelse af personlige oplysninger og cookies