In a previous blog post, I have talked about the various diagnostic data that Power BI Desktop has available for troubleshooting issues. One of the interesting files generated is ASPerfCounters.JSON.
I originally thought the name came from ASP.NET performance counters but I am guessing that it actually has its roots with Analysis Services.
I was curious to see what data was contained in this file so I used Power BI Desktop to open and parse the file. Power BI Desktop has a JSON file connector so it was relatively simple to crack the file open and see what was inside. I was pretty surprised to see that the file actually had PERFMON data from the time that the file was generated to an hour earlier. The data is collected every 60 secs for the following performance counters:
PagedMemorySize64inBytes & PeakPagedMemorySize64inBytes VirtualMemorySize64inBytes & PeakVirtualMemorySize64inBytes WorkingSet64InBytes and PeakWorkingSet64inBytes PrivateMemorySize64inBytes SystemAvailableDiskSpaceInBytes SystemAvailableMemoryInMB SystemCPUUsagePercentage TotalProcessorTimeInMillisecs
To load the data into Power BI Desktop, I did the following.
- Use the process in the earlier blog post to generate a Frown Snapshot File. Open the zip file generated and copy out the the ASPerfcounters.json file that has been generated.
- Use the JSON File connector to ‘Get Data’ and open the ASPerfCounters.json file.
3. The Power Query Editor will open and it will show a list of records. *Note – the following steps are how I imported and cleaned the file, no doubt there are other ways that it can be done as well.*
4. First we need to convert the list to table. If you right click the list column, you will see an option called ‘To Table’.
Select ‘To Table’ and you will get a confirmation dialogue asking for delimiters and how to handle extra columns.
I left everything as default as shown and selected ‘Ok’.
5. At this point, your list has been converted to a table. I selected the expand icon and selected ‘ok’ to the dialogue that appeared:
6. Once expanded, Column1.p is yet another list that needs to be expanded. When you select the expand icon, you get the option to expand to new rows. Select that and will now see that Column1.p contains records that need to be expanded. Expand Column1.p once more time and select both the key and value. You will most likely want to ensure that ‘Use original column name as prefix’ is unchecked.
7. At this point, you have all of your data expanded and accessible but there are some more things to be done to get at least a basic set of useful data.
First, delete columns ‘Column1.i’, ‘Column1.n’ and ‘column1.u’. Nothing useful there.
Next you will want to pivot the key column. Select the key column and choose ‘Pivot Column’ from the Transform section of the ribbon as shown:
When the dialogue pops up, select the default which should have the ‘Value’ column chosen as the ‘Values Column’.
This will display the data in a nice familiar format, date in the first column and columns for each performance counter. Of course, the data looks pretty odd:
The date is displayed in what is apparently a native JSON date format. I had to go hunting on the Power BI Forum to find out how to handle this format. Luckily, Greg Deckler had this one in the bag. The date is stored as milliseconds from 1/1/1970. He lists a way to convert it using a calculated column but I wanted to convert it in power query so I used the following steps.
I selected the date column, then chose ‘Extract’ from the Transform section of the ribbon. I chose ‘Text Between Delimiters’ and used the open and close parentheses as delimiters as shown.
Now I was dealing with a column that was in milliseconds so I set about parsing out the date and time. I first renamed ‘Column1.t’ to ‘DateMill’, converted that column and all of the perf counter columns to actual numbers and then to get the current date, I added a custom column with the following code:
Date.AddDays(#date(1970,1,1), Number.IntegerDivide([DateMill], 86400000))
To get the time value in GMT, I created the following custom column:
= Table.AddColumn(#”Added Custom”, “Custom.1”, each Time.From(([DateMill]/86400000) – Number.IntegerDivide([DateMill],86400000)))
At this point, you can spend more time cleaning up the data and charting it as you wish:
So, why is this useful? Well, its cool that you can use Power BI Desktop to parse JSON files. This file might prove useful if you have a large Power BI model that takes hours to refresh. In case of a crash, you can look at this file and see what was going on with your desktop performance. You could also use these metrics to review performance during a refresh in case Perfmon is not your cup of tea.