Coming up on today's show, you may have seen some of the dashboards and
visualization using Excel with Power BI, but you might not be sure how to get it all working.
Today Lotus F1 team walks us through the steps they took to
visualize their operational data so they can speed up the time it takes to get new
car parts to the track. We'll show you their shift from predefined reporting
with SQL Server Reporting Services, to data discovery in interactive
exploration with Power BI. Including preparing the data, building
relationships and enabling Q&A so that anyone can query the data. Keep watching to see
what they're doing with car telemetry and learn their longer-term aspirations
for predictive analytics.
Microsoft Office
Office Mechanics
Before I visited the team to catch up on their hands-on implementation
I caught up with COO Thomas Mayer to hear their goals.
This is an organization that generates a ton of data right?
Absolutely correct. We collect 2.5 billion data points, per car, pre race.
They all come from the telemetry system which all the teams use, which have been around for many years now.
But beyond this already existing system, we are trying to correlate actual data from the car
combining it with external sources to give us insight which ultimately leads to us to
be able to predict data into the future.
Beyond what we even see at the track, bringing us back to the operational side of things,
What are your goals around analytics on the operational side?
Now we have to make this data visible because it's a lot of data and people need to be able to abstract
the data. So we make it visual to them so they can make faster decisions and bring the parts faster to the car
which ultimately leads to performance of the car.
For me obviously, it's important that I can write my own reports without being dependent on IT department
So I can just write a query and have the report I need.
Excellent. That's the view from the top. Let's roll up our sleeves though and catch up with the IT team
to see how they are delivering on this.
So Kerrie, Carolyn, I know you've been tasked with visualizing Lotus F1
operations and manufacturing. How's that going?
Yeah, really well, actually.
we've made some really good progress over the last four or five weeks.
This is our dashboard that we've built. It's got some pretty powerful and interactive views
of our operations
I know a lot of people Carolyn, are really wondering how
these are made cause you see a lot of the finished product of Power BI.
Why don't you tell us how you got started with Power BI here.
We've got a lot of data both in our current and legacy ERP systems
and our first step was to talk to the business and find out
what the key requirements were.
And that's always a first step when you think about gathering the business requirements but
it changes a little bit with Power BI. So Kerrie, you've actually got a SQL DBA
background. How did that go?
Most of my knowledge is around writing SQL queries and using SQL server reporting services
Now I'm in charge of business intelligence. We chose to look at Power BI. But I was not so
familiar with data modelling in Excel or certainly the new Power BI tools.
And how did you set out learning this?
My first inclination was to start using SSRS, write the SQL queries and give
Thomas the view that he would want of data. That would've been pretty easy.
Although the downside, he wouldn't then be able to explore the data any further
and interrogate in different ways. So it was a mindset shift for me.
Here's an SSRS report that I have used before. As you can see it's not a visual. I had to a
predefined the data that we brought into this report and you can't really
drill into it or interact with it in any way.
If they had any of those 'what if' questions and wanted
to explore things a little bit further, I then had to go back to the drawing board,
write a new query and build a new SSRS report which would
have all been quite time consuming.
So less interactive in those 'what if' scenarios which are hard to kind of cater towards.
So how did that change with Power BI?
So the shift for me was in realizing that with
Power BI you're bringing in much more data and you're exploring that data with
the visualization tools and it's pretty easy to create visualizations and start
to identify patterns in the data
Great. Let's start walking through some of the steps.
So Carolyn, why don't you kick this off.
First of all, we translated our reporting requirements
and found a dataset that included that but more. We didn't want to just restrict it
to the initial requirements of the business gave us.
Okay. Kerrie, how do you go about doing that?
So now with the Power BI desktop app
I brought in all the data directly from our SQL database. You could also do
this in Excel using power query the functionality is very much the same between Excel
and the desktop application.
And Carolyn how do you choose you data set?
Because we are not using pre defined queries
we didn't limit our dataset we were optimizing for more data discovery
and exploration. We want to overshoot the data that the stakeholders initially
requested because that's going to help us later on as we develop the dashboard.
And how do you go about preparing and modeling your data?
So step two is really
about building those data relationships and building your data model unlike SSRS
you spend a lot time up front connecting the tables and building that
model then you don't need to prepare data every time you want a new view.
First we connect to our SQL server database and choose which tables or views we want to connect to
Ok, now we've brought our data in and we can see that we've got two tables and it's discovered that there is
a relationship between those two tables on the table ID. You can also go in and manage
those relationships, create new ones or edit the relationship if you need to.
Carolyn, how did you go about bringing in data from multiple sources?
You had legacy and current ERP systems; how did you match that together?
We started with our current ERP system, we looked at the legacy data and see how
we could transform and align that with our initial data set. We are now bringing
in data from both systems and that means that we can go back for five years.
How do you go about doing that Kerrie?
We then used SQL queries to help us bring the old data and the new data
together and to bring that into our data model. You could also do that using power query or DAX.
DAX is really designed to work with relational data, preform dynamic aggregation
also so that you can find patterns more easily. I know that DAX is really a big part in your
adopting Power BI. Why don't you explain how easy it was to learn DAX?
We primarily used it to group relevant data together using DAX.
So I'm going to create a new calculated column here this will be for working out
estimated hours. I know this is made up of our calculated process field
and our calculator set up field. So it's very quick to create. I can then change the data
type of that field. We're going to make into a decimal and then I can create new
calculation based on that new field that I've just created. Which will be a total of the estimated hours
Which we'll just use the SUM feature. So you can see this has got some similarities the Excel formulas.
Once you bring in the data that you've built all the relationships can start to visualize that now?
Yeah that's right. So step three is really about exploring the
various relationships and starting to build up the dashboard at this
stage it's all just drag-n-drop. We can just bring in our total estimated hours
calculation that we've just created and we can then slice that by operation in this
example and it gives us a nice bar graph. We could also just bring in that total
estimated hours and just view it has a number, if that's how we would want to see it.
So Thomas or Ian could really interact with this and build new views on the data however they
want to see it really.
So now you are almost ready to actually move on to the next step of publishing right?
Yeah. We need to publish this up to PowerBI.com before we can move on. So I'll hit publish.
Now you'll see see in PowerBI.com we've got our new dataset in there which is
the file we've just created and we've got reports in there as well. If we go
and explore that we can see the visualizations that we just created in
the desktop application. I'll then create a new dashboard.
Which at the moment is just blank.
if I go back into the reports that I just created I can then pin those to
that dashboard and it will tell me that it's been pinned into to the dashboard.
We caught up with Thomas earlier and he was really impressed with Q&A and how to really create
custom views against the data. Do you have to do anything special to do that?
Yes. The importance of that was back in step two when we were building the data model we have to
make sure that all our columns are named correctly that then builds a vocabulary that
Q&A uses so that when you start to interrogate that data it knows what you are taking about, really.
Now that you've got the dashboard published, how do you go about sharing that out and setting permissions?
It's pretty straight forward just from
the dashboard in PowerBI.com. You can choose share and you can just literally put in the
email addresses of the people you want to share it with
and you can choose whether they have the ability to then share it on with other people.
So this is another connected service. You can see through the Active Directory who's there
or it will automatically populate those names.
So you've got everything working, you've got all of the five steps gone through
in terms of what you've down to build out Power BI and really get it published out.
What were some of the people's reactions when you were starting to first show Power BI to people?
It's been really good actually. Manufacturing particularly have been particularly pleased. I think this is going
to be a real help for them to improve the efficiency of their operations so it's been good.
Initial impressions is pretty amazing.
Before we had many different programs we had to use. Now we get the the overview
on the dashboard. Which is great and then you drill in as much as you need. For example this is an
actually versus estimate on hours we consume to make a part. We choose that graph
wer open the graph, it goes into the full screen. I can then drill down on any of the resources on
the graph to fine-tune exactly what I'm looking for the information I need.
In a month the team was able to visualize their operations data. Thomas also
discussed interest in other power BI scenarios such as car telemetry. I met
with Mark Everest who oversees Lotus F1 team's advanced telemetry systems to find out more.
So Mark, we saw how the data models are created with Power BI and we've got
some seriously big data here with the Formula One cars. How have you been looking at Power BI?
So we've imported a lot of the telemetry data into Excel. And when I say 'a lot' I mean were talking about
millions of rows. What we can do with that, is we can correlate different data
sources and try and spot patterns and can create different views of the data
that we might not been able to before.
You've got some of that running here?
Yes certainly. So this example here shows the GPS
data from two cars for the same race plotted on a map. So we can see there a
familiar track map that most people might recognize. We can also put in individual channels
of data over a period of time. So looking here we can compare brake temperatures
for different setups, so different cars and we can see there that the brake bars might
have been set differently in each car. And again we can say that the behavior is different.
So this is Excel but what about Power BI itself, where do you see that coming in?
We can look at this as a way to expose data to users that might not be able to use our
existing tool set. Most people are familiar with Excel so picking up a tool like
this is very simple for them to do.
And beyond what we're seeing here with Excel and Power BI, where do you see this going?
What's the future of scaling these insights?
Well, we have an awful lot of data. So for example a ninety-minute race for a
single-car generates about two and a half billion rows of data. So finding
somewhere to host all of this information for us to be able to use is what we're looking for
so to me the obvious option is to use the Azure Data lake. That's where we can
import different types of data for example track conditions or whether data.
We're also looking at machine learning and predictive analytics to spot patterns in
the data that we might not have seen before.
So lots of progress since out last visit a few months ago
where Power BI was planned but not yet implemented. Now it's time to go back
and get Thomas' reaction.
We're back with Thomas. Now Thomas, we just heard from the IT team.
What do you think about their progress?
Really pleased with the progress. They did an amazing job. Now I have a tool at my hand
where I don't rely on their capabilities of writing reports. Now have a Q&A tool
where I can query the data myself and get the
report instantly like work orders from Monaco by year as a treemap.
Right. We also met with Mark who is also looking at ways to use Power BI with car telemetry data.
Yes. That in fact opens a completely new way of approaching data analytics. It will let us
visualize correlated data in a way we never have done before and it will be
ultimately bring us performance to the car. And you need to think in a very
different way at the moment because now we this saying 'if you can think it, you can visualize it'
Great closing note. That's about all the time we have for today's show.
Next time we visit Lotus F1 team we're going to look at their journey to modernize their
travel logistics app from something paper based and static to something
integrated with Office 365 email and calendar. Of course you can watch previous
shows for Lotus F1 team on Office Mechanics. Thank you for watching and
goodbye for now.