ECAT diagnostic and statistical information with Power BI Desktop

Overview

We periodically read EtherCAT diagnostic and statistical information and visualize it with Power BI Desktop

Power BI Desktop
Power BI Desktop can be downloaded and used free of charge on your own PC

Power BI service
Use the Power BI service when you want to use it as a cloud-based service, for example to publish on a network

Comparing Power BI Desktop and the Power BI service

Flow until display in Power BI Desktop

1. Downloading Power BI Desktop
2. Starting Power BI Desktop
3. Creating a report
4. Step 1: Create a trend graph for node 1
5. Step 2: Show the latest data for node 1
6. Step 3: Nodes 2 to 4
7. Done

Downloading Power BI Desktop

Download and install Power BI Desktop from the following site
https://powerbi.microsoft.com/en-us/desktop/

Creating a report

New report

1. New report

   Clicking New report shows the following
   
   Here we assume the EtherCAT diagnostic and statistical information is in SQL Server, so select "Import data from SQL Server"
   




2. SQL Server database

   
   Enter the SQL Server to connect to and the database name
   For the data connectivity mode, choose "Import" to bring the data to your PC, or "DirectQuery" to show the report while connected to the database




3. Navigator

   
   Select the tables "MsterStat" and "SlaveStat"




4. New report

   

Step 1: Create a Trend Graph for Node 1

1. Select "Line chart" under Visualizations

   

2. Select "ErrCnt0", "ErrCnt1", "ErrCnt2", "ErrCnt3" and "RecDT" from Data

   "RecDT" is set on the X axis and "ErrCnt0" to "ErrCnt3" on the Y axis

3. "ErrCnt0" to "ErrCnt3" on the Y axis are set to Sum, so change them to Maximum

   This is so that when the aggregation span is longer (e.g. 1-hour intervals), the maximum within each interval is shown instead of the sum
   

4. To show only node 1, drag "NodeAdr" from Data and drop it on Filters

   Select "is" as the filter type and enter "1"
   "is" means equals (NodeAdr = 1)
   Press "Apply filter" to apply it

Step 2: Show the Latest Data for Node 1

Table

1. Select "Table" under Visualizations

   

2. Select "ErrCnt0", "ErrCnt1", "ErrCnt2" and "ErrCnt3" from Data

   

3. "ErrCnt0" to "ErrCnt3" on the Y axis are set to Sum, so change them to Maximum

   

4. To show only node 1, drag "NodeAdr" from Data and drop it on Filters

   Select "is" as the filter type and enter "1"
   Press "Apply filter" to apply it
   

5. To show the latest record, drag "RecDT" from Data and drop it on Filters

   Select "Top N" as the filter type and enter "Top" and "1"
   Drag "RecDT" to By value and choose "Latest" to show the latest RecDT
   Press "Apply filter" to apply it
   

6. The column names such as "Max of ErrCnt0" are long, so use "Rename for this visual" to change them to "ErrCnt0"

   

7. Finally, to add a title, turn on "Title" under "Format your visual" in Visualizations and enter "Node1" as the text

   

8. Finished table

   

Step 3: Nodes 2 to 4

Likewise...

Create nodes 2 to 4 in the same way and add them to the report

1. Copy the table and line chart, change the "NodeAdr" filter to 2 and change the title to "Node2"

   

1. Since there is not enough vertical display area, set "Page view" to "Fit to width", select "Custom" for the canvas settings in Visualizations and change the height to a large number such as "1440"

   

Done

Finished report

Finally

I created a report from the ECAT diagnostic and statistical data, but this is just one example, so try various aggregation methods