In this video we are going to incorporate the downtime information
from the PI Integrator event view into the Power BI report. Let's get started.
Let's begin by adding turbine downtime view to the report.
We will select get data but for convenience you can also use recent sources.
On the get data screen that appears select the database category
and choose SQL server database from the list and now click connect.
Let's choose server pisrv01,
database PI_World and data connectivity mode import.
In the navigator window that appears select turbine downtime table and click on load.
Now that we added the turbine downtime table to our report, we can connect it to our model.
So let's navigate to the model section on the left bar.
Here we have to verify that the turbine downtime table has a relationship with
the turbine information table using the wind turbine field. As you can see in my
case the tables are joined by the id column which has no relationship between tables.
So let's delete it and create a new relationship.
Now let's go back to our report and we will start by creating a chart of downtime by wind farm.
So in the report view add a clustered column chart and select
wind farm from turbine information for the axis
and event frame duration from turbine downtime for the values.
Notice that the duration is properly split between the four wind farms.
Now we will create a date table. First try to address the date slicer to choose a different
time range. You can notice that the downtime duration in the previous chart
doesn't update. This happens because the slicer is configured with the timestamp
from the turbine data one hour summary table, so it doesn't affect the turbine downtime table.
To create a date slicer that will alter all the tables it is best to create a separate date table.
So first let's enter the data section by clicking the data icon on the left side.
In the ribbon click on new table
and enter the definition for the table you see on the screen.
And now hit enter, so this will create a table with dates for the last 90 days.
This table can now be joined to any table that has date data. To make the joining process smoother
let's add a date column to both the turbine data one hour summary and turbine downtime tables.
First we will add a date column to turbine data one hour summary.
To do it first click transform data in the home ribbon to open the query editor.
Select the table turbine data one hour summary from the left. Now select the timestamp column
and in the ribbon navigate to the add column tab and choose date date only.
This will create a new column called date.
Now we will do the same for the turbine downtime table.
For event frame tables the process is very similar; however unlike the time
data summary event frames only have a start and end time and not individual timestamps.
For this exercise we will use the start time column. So let's select the start
time column and in the ribbon navigate to the add column tab and again choose date date only.
Now click on close and apply in the home tab.
Now we can create relationships between the date table and other tables.
So let's navigate to the model tab and create the relationships.
Now update the date slicer to use the date column
from the date table and watch as the duration updates properly.
This concludes the video on incorporating downtime data into the Power BI report. To
achieve this report we first added the downtime data published by the PI Integrator to Power BI.
We then added a relationship to join the turbine downtime table to the turbine information table.
Then we created a chart to show downtime for each wind farm and at the end we created a date
table to allow filtering of the data. Thanks for watching.