In this blogpost I want to show you how you can easily create a PowerBI dashboard based on Storage Spaces Direct performance metrics. PowerBI is great in visualizing data and reports are easy to create. Before you can execute the steps in this blogpost you will need to create a PowerBI account on https://www.powerbi.com. I’ve tested the blogpost below with PowerBI Pro account but based on this page it should also work with a PowerBI free account. Looking to Storage Spaces Direct this blogpost is based on Windows Server 2016. I’ve not tested this on earlier versions and I expect that this is only working on 2016 and later. I’ve created this blogpost to monitor my S2D environment hosting the Remote Desktop Service User Profile Disks, so expect that this dashboard is focusing on delivering an overview for that purpose.
So let’s start with getting the data, without the data we can’t show anything in PowerBI. From Windows Server 2016 we can use the Windows Health service to retrieve data about Storage Spaces Direct. Microsoft is describing the New Service as: ‘With the Health Service in Windows Server 2016, we’re opening up the powerful monitoring APIs we built for the likes of System Center and Server Management Tools to you, developers, and especially our solutions partners. You know your customers better than anyone. Build the user experience they’ll love, atop a tried and tested foundation from us.’More information about this can be found here: https://msdn.microsoft.com/windowshealthservice/healthservice.
For Storage Spaces Direct this means that there is a PowerShell cmdlet which gives you the actual performance values. The cmdlet is: Get-StorageHealthReport.When you execute this cmdlet for your cluster you will receive output like this:
So the next step was to export this data to a CSV file which we could import into PowerBI. But here 2 challenges were appeared:
- I was not able to easily get the Names and the Values from this cmdlet. With some great help of Ben Gelens (PowerShell MVP) this was solved pretty fast and easy J.
- The outputted values from my S2D cluster instance could not be translated to INT or DECIMAL values within PowerBi. After some searching and testing this was because of the regional settings on the server. After changing the regional settings to English I was able to convert the values from the CSV to INT and DECIMAL values in PowerBI.
But I wanted to automate the process of exporting the performance information to the CSV file so it can be run on the server from the Task Scheduler. I made the following script and scheduled it on my S2D instance.
Note: The switch within this script can be deleted, currently I’m not using the switch. In the original script/cmdlet the switch is used to translate the values. But currently I’m doing this in PowerBI.
The script can be found here: https://github.com/arjanvroege/PowerBIReports/blob/master/S2D%20Dashboard/s2d_perf.ps1
Now we have the performance information within a single CSV file and based on this file I created a PowerBI dashboard. I used the PowerBi desktop application to create the report. I executed the following steps:
- Download PowerBi Desktop: https://powerbi.microsoft.com/en-us/desktop/
- Start PowerBI desktop and the first step is manipulating the dataset based on the CSV file. I did the following manipulations:
- Configured the right data types of the columns
- Divided columns to get the right value which I wanted in my dashboard (in the cmdlet this is done through the switch)
- Rounded some values.
- The next step is to create the dashboard based on this dataset. You can view the picture of the end result below.
- The last step is optional but I recommend it, upload your report (including the dataset) to the PowerBI cloud service. You can use the share option for that:
This will result in the following dashboard:
I want to make the following notes:
- Data Manipulation is done based on my scenario and my lab environment, in other environments you should adjust it to the right values.
- The data refresh is static, so If you want to add new data to the report you need to copy the new CSV file from your S2D instance and hit refresh in PowerBI. Final step is to upload the dashboard to the PowerBI cloud service and select ‘Replace‘ during the process.
I’ve published my PowerBI dashboard to my GitHub account. You can find the file here: https://github.com/arjanvroege/PowerBIReports/blob/master/S2D%20Dashboard/S2D_Dashboard.pbix?raw=true.