Welcome to the third installment of a series demonstrating the use of PowerPivot with Fukushima Incident Radiological Air Sample Data from the US Department of Energy. Following is a recap of the installments in this series:
- Design of the data model (click here to read)
- Development in PowerPivot for a dashboard (click here to read)
- Deployment of the data to a dashboard (this post)
- A start-to-finish video
The last post left off with a PowerPivot data source that was ready to deploy to dashboards. Let’s pick up where we left off. Under Home on the ribbon in PowerPivot, and under the PivotTable drop-down option, Chart and Table (Horizontal) was selected for the first dashboard:
Once deployed, the PowerPivot reporting data will be available in the form of Pivot Tables in Excel 2010. In fact, the Pivot Tables appear nearly indistinguishable from traditional Excel Pivot Tables. So if you are used to traditional Excel Pivot Tables, you can get to work building dashboards. If not, I’ll go into further detail regarding the specific methods to drag’n’drop measures and attributes onto PivotTables and PivotCharts in the final post in this series, which will contain a video.
Now remember, without much knowledge about comparing radioactivity measurements amongst different nuclides I decided to avoid calculations that would sum or average [Results] that were not for the same nuclide. With this simple data model, the Fukushima data can still be displayed in a dynamic manner on a dashboard:
The screenshot above is a slice-and-dice BI dashboard with four simple parts:
- A vertical slicer on the left for the [Nuclide] attributes. This slicer is connected to the PivotCharts and the PivotTable. It is intended for “single select only” since I do not intend to aggregate different [Nuclide] members. The screenshot is filtered for the slicer selection of “Gross Beta.”
- The PivotChart at the top-center of the dashboard is a Radar Graph that displays the average radioactivity [Result], per [Nuclide] selected in the slicer, per direction from the disaster site. It appears that the highest average [Result] for this [Nuclide] is South-Southwest (SSW) relative to the disaster site.
- The PivotChart at the bottom-center of the dashboard displays the Top 10 highest [Result] readings, for the [Nuclide] selected in the slicer, organized by direction from the disaster site. 7 of the 10 highest readings were also taken in the SSW direction.
- The PivotTable on the right side of the dashboard contains transactional level data for the [Nuclide] selected in the slicer. Individual test results at the granularity of [Sample #] and [Direction] can be reviewed, and also filtered by the slicer on the dashboard.
As you will see in the tutorial video that wraps up this series of posts, once a PowerPivot data model has been designed and developed building dashboards can be done quickly and with great flexibility. While the dashboard from above attempts to graph out sample result distribution while providing transaction level information, it doesn’t take distance from the Fukushima disaster site into account. Using the same PowerPivot data model, another dashboard with a different approach for displaying the data can be built:
The above dashboard has several parts that are built from PowerPivot:
- Two different attributes are added to the slicer on the left hand side: 1) a slicer for [Unit] that filters the top PivotChart and 2) a slicer for [Nuclides] that filters both PivotCharts.
- Another vertical slicer is added to the right of the first slicer set: 1) [Direction] slices the lower PivotChart, and both [Method Code] and [Unit] slice the bottom PivotChart.
- The top PivotChart displays Average Result by Direction.
- The bottom PivotChart displays the top 100 results, with [Result] plotted against [Distance] with a trendline.
Did you notice that I included [Unit] twice in the dashboard? While not necessary, it demonstrates that slicer attributes can be individually connected to any other PivotTables or PivotCharts from the same PowerPivot data model on the dashboard with a simple right-click.
While the first three posts of this series don’t detail every step in the process of bringing flat file raw data to a dashboard with PowerPivot, hopefully they are a good start. In three short blog posts the journey from CSV file to slice-and-dice interactive BI dashboard has been summarized, and hopefully is a good kickstart for analysts experienced with Excel to begin an expansion into PowerPivot.
I want to conclude this post by reiterating that I’m not an expert with radioactivity data, nor am I trying to draw any conclusions with these dashboards. There will be more content from GNet Group later this week, and be sure to check back Monday for a video tutorial and recap of this blog series.