Microsoft Power BI. Analyze your glucose data and make data-driven decisions
(English translation of the article in Spanish: «Microsoft Power BI. Analiza, obtén conclusiones y toma decisiones basadas en los datos de tus glucemias.«)
Thinking these days about everything I have managed to do thanks to technology, I think the basis of all this is that, thanks to Nightscout, we can store our data on our website and that they are our property. This allows us to build on this data within many applications to make life much easier for parents of children with diabetes. For example, they allow us to see the glycemia readings on our smart watch, have a clock on a table or nightstand with these readings using M5STACK, and even to be able to share the data with other relatives through XDrip, Diabox, Shuggah, Dexcom Follow, etc. depending on your medical devices and cell phone type.
But something that we have not yet done and that I think is fundamental is to be able to analyze and exploit the data that we stored to make decisions about the day-to-day of our children. These decisions can range from modifying diet and carbohydrate intake to modifying the insulin regimen to get the optimal glucose range as long as possible.
However, to achieve this decision-making, I lacked a tool that would allow us to import the Nightscout data and then analyze it, understand it, and base it on the conclusions obtained.
Well, browsing Facebook, I found a fantastic group called Nightscout Power BI Reporting, where they had found a solution to analyze and exploit the data that I have been uploading to Nightscout over time. Thank you very much!
To achieve this, they used the Microsoft Application Microsoft Power BI (PBI), a very common tool used in the business world to represent a large amount of data in a way «easily understood by humans» through graphs and summary tables. This is done in the business world by using data visualizations called Dashboards.
Honestly, I thought it was a genius. And after tinkering for several days, I found that it has an incredible power to interpret my daughter’s tens of thousands of glucose readings over a few months in a simple and effective way. This new visibility of the large data sets serves as a wonderful decision-making tool to adjust insulin amounts to improve glucose control and the comfort level we’re making the correct changes when doing them.
That is why in this article, I am going to teach you to install Microsoft Power BI on your computer (which is free), to download a file created by Nightscout of Power BI with the Dashboards already created, to connect it to your Nightscout database and to interpret the dashboards so that you can make decisions «data-driven».
The sections that we are going to see in this article are the following:
- What is Nightscout Power BI for?
- Install Microsoft Power BI
- Nightscout Power BI file download
- Connecting to your Nightscout website
- NS Profile & User Info
- Measurements of the last 4 days (Last 4 Day SGVs)
- Daily Review
- Hourly Review
- Weekly & Monthly Analysis
- Days of the Week Review
- The Nightscout Power BI Reporting Team
What is Nightscout Power BI for?
I don’t know if any of you have a co-worker who is «Data Science» or who works analyzing «Big Data». Well, if you have a colleague who is dedicated to these tasks, you will know that their main function in a company is to analyze large volumes of data, interpret them, understand them, and show them using graphs and tables so that based on this analysis decisions can be made.
To do this, they use «Business Intelligence» tools that allow them to «ingest» all that large amount of data, process it, represent it with graphs and interpret it. Well, in this case the tool that will allow us to become Data Scientists of our blood glucose data, is called Microsoft Power BI (yes, you have guessed it, «BI» means Business Intelligence :))
Our friends at Nightscout Power BI Reporting have created for us a Microsoft Power BI file that will do the whole process of ingesting and processing data to show them to us in a series of graphs and tables that we will have to understand and that will be worth us to make decisions of the day to day of our children.
This file is divided into a series of Analyses distributed as tabs / sheets of an Excel workbook and that will allow us to do the following:
- Last 4 Day SGVs: Check our child’s daily measurements over the past 4 days and see what % of time has been in or out of range.
- Daily Review: analyzes the daily data of the period that we determine by seeing the time it has been in range or out of range and seeing the average curves as well as the maximum and minimum.
- Hourly Review: analyzes during the period of time that we determine what the average values have been every hour, as well as the maximums and minimums.
- Weekly & Monthly Analysis: analyzes the time in range and out of range by making weekly and monthly averages, as well as the graphs of maximum, minimum and average values.
- Days of the Week Review: Analysis of each day of the week in the given time to see the periods you have been in range and out of range.
As you can see each of these analyzes has a great value because it will be able to help us detect certain patterns in the behavior of the values of the glycemia of our child that will help us make decisions about their diet, as well as about the insulin guidelines to be administered.
In short, it will become a fundamental tool for us on a day-to-day basis.
Install Microsoft Power BI
But first of all, the first thing we are going to have to do is install on our PC the desktop version of Microsoft Power BI by entering the Microsoft Download Center through this link. For Mac users there is also the possibility of installing MS Power BI, I leave here a link to the instructions on how to do it.
As soon as you are on the Microsoft Download Center website, select the language to «Spanish» and click on «Download» (or select you own language, of course!)
As soon as you have done so, another screen will open where you will be given the option to select two files to download:
- PBIDesktopSetup_x64.exe: For Computers with 64-bit Operating System
- PBIDesktopsSetup.exe: For Computers with 32-bit Operating System
I’m sure that at this point you tell me «I have no idea what kind of operating system my computer has!». Well, I make it easy for you to find out. Click on the magnifying glass that is in the lower toolbar of your computer and in the menu that opens, write «System,» and click on the System application that finds you below.
The window will open with the system data that you have installed on your computer. In my case, you can see that it is a 64-bit system, so I will have to click on downloading the file «PBIDesktopSetup_x64.exe» from the previous screen.
At that moment you will download the Microsoft Power BI installation file that, after a few seconds, after downloading, you can find in your Downloads folder.
If you run the file «PBIDesktopSetup_x64.exe» (in the case that your system is 64-bit, if it will not be PBIDesktopsSetup.exe) you will begin the installation of Microsoft Power BI on your computer. Select that you want the installation to be done in «Spanish» (or your own language) and go forward in the installation by clicking on «Next».
Accept the Microsoft license terms and indicate in which destination folder you want to install it (this does not need to be changed, if you do not want to). Then click on «Install» and we will proceed to install Microsoft Power BI on your computer. As soon as it finishes installing, it will show you a message indicating that it is finished and that you can run it.
Once you have finished the installation, to start Microsoft Power BI, you can click on the magnifying glass that is in the bottom bar of the Windows desktop and type Power BI. The Power BI Desktop icon will appear, which is the one you have to click to open it.
As soon as we run the Power BI Desktop program, the main screen of the program will open. If you click on «Get started» you will start to be able to use the fantastic Microsoft Power BI software that will allow you to work with several dashboards generated from the glucose data stored in Nightscout.
For now, we are going to leave this software momentarily parked here to continue with it later. We need to download our Microsoft Power BI file that contains the dashboards and that has been created by friends at Nightscout Power BI Reporting. But we’ll see that in the next section!
Nightscout Power BI file download
This section is very simple, we are just going to download the Power BI Nightscout file that has the dashboards and that is called «NS-SGVs-PC-R03.04.pbix». To do this you will have to download it from one of these two links, clicking on them:
- One Drive: link (you’ll need to have a One Drive account to download it)
- Google Drive: link (you’ll need to have a Google Drive account to download it)
Once you have downloaded the file (which you will have in your Downloads folder), we will open it with Microsoft Power BI. For which we return to this program that we opened before and click on the top menu the option «File». And in the side menu that opens, we select «Open Report» and the «Browse Reports» button. We then look for our file «NS-SGVs-PC-R03.04.pbix» in the Downloads folder and open it.
You will see that the file opens, and we will see a screen with the Nightscout logo #WeAreNotWaiting and with the instructions in English to connect this file with the dashboard reports with our Nigthscout website. But don’t worry! I will explain how to do it easily in the next section of this article.
Connecting to your Nightscout website
To connect the Microsoft Power BI file with our Nightscout website and download the data stored there, we will have to tell our Power BI file what the address of our website is and if we have configured any access token or user, to be able to access the data we have stored.
To do this, on the screen where we have stayed in the previous section we click on «Transform Data» and then «Edit parameters» to open the screen where we can enter the parameters of the import of our data. That is, how we want them to be imported.
On the screen that opens below you will have to enter the import parameters. First of all, in the «UsersNS_URL» field enter the URL of your Web Nightscout and in the «UserNS_Token» field enter the Token you have created for one of your users. If you have the Web open to the world (it is not desirable) you will not have to enter the Token, but the most normal thing is that you do have to do it.
In case someone does not know what the token is or where to find it, I leave you a link to my article «Deploy Nightscout in Google Cloud for free as an alternative to Heroku» where you can find how these users were generated with Token in this link.
The rest of the parameters can be configured as follows:
- UserTgtLow: It your lower limit PBI Dashboard target for Hypoglycemia (usually 70 mg/dl or 3.9 mmol/l)
- UserTgtHigh: It is the upper limit PBI Dashboard target for Hyperglycemia (usually 170 mg/dl or 9.4 mmol/l)
- UseImportDays: these are the days of your data that you want to import into Power BI so that the dashboards are calculated on them (usually the last 30 – 90 days)
- USerPBI_App: In this option leave «desktop» which is where your Power BI is installed
- UserUnits: These are the units in which you are measuring glucose and in which your Nightscout data is (the usual is mg/dL)
Once you have all the parameters filled, click on «Update» at the top and your Power BI will start connecting with Nightscout and you will be able to see the following screen as the data is incorporated into your Power BI.
Once you finish importing the data and the update screen disappears. «Voila» you can say because you have already imported the data from Nightscout to your Microsoft Power BI. Now it’s time to analyze your data with Power BI!
To do this you will see that at the bottom there are different tabs like those usually in an Excel sheet. Well, each of them will allow us to make a different analysis of our glucose data. That is why it is important that you look at them carefully and understand each of the graphs and data that are shown what they mean.
The truth is that I am not a data scientist (in fact I am a civil engineer! :)), but I will do my best to explain what each graph means based on what I understand in each of them. So, if someone sees that something is not correct or if you think that the data can be interpreted in another way, do not hesitate to write to me and I will update each of the sections to collect your indications.
Well, let’s play! as that «FAIR Price» TV presenter used to say…
This tab is very simple, and I will not dedicate a great explanation, because I have told everything in the previous section. Basically, they are the instructions in English to connect your Nightscout Web Site with your Power BI file.
As we have already done this, we move on to the next section. Has been easy? 🙂
NS Profile & User Info
This is the last tab on the right. I skipped the others, although later I will return to them.
This screen is not very important for the users and is to confirm you entered the Parameters correctly. It simply informs you of the values you have defined to import the data from your Nightscout website:
- Last Refresh: when was the last time the data was imported
- Import Days: The last days of imported data within PBI
- SGV Units: Your selection of mg/dL or mmol/L for PBI Dashboards
- Tgt Low: Your target mg/dL or mmol/L hypoglycemia value for PBI Dashboards
- Tgt High: Your target mg/dL or mmol/L hyperglycemia value for PBI Dashboards
Measurements of the last 4 days (Last 4 Day SGVs)
Now we start to «get into flour» and start analyzing the data in our Power BI to get «all the meat» out of it.
This tab shows you the data of the last four days, painting us 4 graphs, one for each day. Surely many times you have wondered: «how has my daughter been this week in school?». Well, this tab will show you precisely how your child has been during the last 4 days. Putting one graph for each day on top of the other so you can compare each day with each other.
In each graph, you will see two horizontal dashed green lines. These are your target hyperglycemia and hypoglycemia parameter values entered within PBI parameters. There is another green line that is the trend line and marks you depending on the slope of the straight line if, throughout the day, you have tended to raise your glucose level (ascending) or lower (descending).
Each of these graphs has a pie chart and a table with the values. The pie chart tells us the % of the time that our daughter has been in the green range (between the levels of Hypo and Hyperglycemia, in our case between 70 and 170), the time she has been above the level of Hyperglycemia in yellow (values greater than 170) and the time she has been below the level of Hypoglycemia in red (values less than 70).
The table that is under each pie chart indicates the following values:
- Date: The date to which the table and graph refer
- Cnt: Number of daily readings stored in Nightscout (this is my guess). The maximum value is 288 (readings every 5 minutes for 24h). If the value is low, it indicates that your daughter’s mobile has been out of sensor coverage for a long time. If it is close to the value of 288 is that your daughter has been monitored most of the day by the mobile.
- Avg: Average of the glucose value you had that day.
- Std Dev: Standard deviation of the values of that day. It tells you if the values have moved far from the daily average. That is, a high value means that there are many peaks and many valleys far from the average value. A high value means that there are not many variations in the measurements per day.
- Lw% – In% – Hi%: Tells us the % of the time you have been in Hypoglycemia, in range, and Hyperglycemia. Ideally, in «In%» which is the time in range that is above 70%.
In short, this graph is very useful to see how the carbohydrate menu and insulin doses have performed over the past 4 days. If the green pie slice is above 70% of the time, according to our endocrine, it is good and is what it should be. This means you’ve been in range 70% of the day, which is good.
Seeing what has happened to my son the last four days is all well and good. But what if I want to know day by day how it has been for a longer period of time? How do I do it? Well, this tab of the Power BI file will allow you to analyze day by day the period of time you want.
To do this, this graph is configurable, because it allows you to define in it the number of days on which you want to make the analysis. You can do this in the box that is up in the center that is called «Date». Select the number of days you want Power BI to draw the charts for you. In this case I have selected 30 days.
Once the period of analysis has been defined, we will be able to differentiate 3 separate zones in this graph.
First of all, to the right of the sheet, you can see the graph of the average, minimum and maximum values that our daughter has had in the last 30 days. As you can see in the graph, the average value goes down until it is within the minimum (70) and maximum (170) values. Also, the maximum values are going to approach 170 and the minimum value is moving away from 70. Which means that the changes we have made in your carbohydrate intake or insulin regimen have made it improve.
Secondly on the left you can see a table called «Glucose Measures by Date» with the values that we mentioned in the previous section corresponding to each day, and to which are added the values of the SVG Min (the minimum value that your daughter’s glycemia have reached that day) and the SVG Max (the maximum value).
You can also see that there is a color code on the table. The better the values, the greener they are and the worse they are, the redder they will look. The optimal values are in the «% in range» the time they are above 70%. And in the standard deviation those below 30%.
And finally, there is the third area called «Glucose Count Range by Date» that indicates with a graph of horizontal bars the % of time that each day they have been in range (green), in Hyperglycemia (yellow) and in Hypoglycemia (red). This graph is good to simply look at it to see if our daughter has improved (she has more and more green) with the modifications we have made in the intake of carbohydrates or in the insulin regimen.
In summary, this graph allows you to make a detailed analysis of each day how your daughter has behaved and compare it with other days, to see improvements or not that are occurring every day.
But what happens when I want to compare two specific time periods? Imagine that I want to compare the month of September (where my daughter at the beginning of school was not very well adjusted) with the new insulin regimen that I have defined in October. Well, this sheet will allow you to make that daily comparison in the period of time you want.
That is why I find this graph very interesting. It allows you to compare the hourly behavior of your daughter’s glucose measurements over two different periods that you can set up as you like. For example, when you are interested in zooming in on a specific period of time and being able to compare it with another.
To achieve this, first in the two yellow boxes at the top of the two charts on the sheet, you will have to configure the two periods you want to compare. In my case I wanted to compare the month of September with what I have of the month of October.
The first thing I can see on the chart is that the time in range in September has been 59% compared to 77% in October. That means that in October we are able to control our daughter better.
We can also see that the line of black strokes in October is almost always between the upper (170) and lower (70) limits, which is very good. That’s why it’s longer in range in October. However, more hypoglycemia phenomena occur in October. It is seen on the chart that we have values below 70 around 10:00 a.m. and at night at 11:00 p.m.
This is a very, very useful analysis when comparing different periods of time.
Weekly & Monthly Analysis
In this sheet we will be able to carry out a weekly and monthly analysis to see the evolution of our daughter over a longer period of time. Surely many times you have wondered if this month your child is better or worse than the previous one. Was he better off in August or September when she’s back in school? How has my child been evolving week by week in recent months? Well, thanks to this sheet you will be able to compare months with each other.
The graphs on this sheet are configurable in the upper right corner, where you can indicate the number of weeks on which you want to perform the analysis. In my case I have selected 16 weeks (4 months, from June to the present).
On the right side of the sheet, you will be able to see curves that represent what is the average glycemic curve during these 4 months of your daughter and how it is in accordance with the minimum and maximum limits. This curve is represented by a black line in strokes. Above and below those curves you will be able to see two other gray curves of strokes, which represent the maximum variation above and below the average curve of 75% of the values. This gives you an idea of the dispersion of values on both sides of the mean curve. At the top you can also see a «pie chart» that tells you how long our daughter has been in range (in green) and how long she has been above the upper limit (yellow). Being in range values above 70% is pretty good. As you can see my daughter has been above the limit of 170 in the month of September, because it was difficult for us to adjust the pattern again in the return to school after coming on vacation.
On the left side of the sheet, you can see a summary table by months. At the top are the parameters indicated in the previous sections, but in this case referred to each month: Cnt (number of measurements), In Range % (% of the time in range), Low % (% of the time below the range), High% (% of the time above the range), SGV Std Dev (the standard deviation, or what fluctuate the values on both sides of the mean), SVG Avg (the average values), SVG Min (minimum value) and SVG max (maximum value). The color coding is very useful because it allows us to see if the data is good (in green) or bad (in red). And yes, the month of September, apart from the summer ending, was a month where we did not have our daughter very well adjusted. Another harmful effect of going back to school!
Also, on the left side of the sheet, now at the bottom, you will be able to see a bar graph that in colors tells us for weeks the % of the time that our daughter has been in range (green), has been high (yellow) or has been low (red). The truth is that the summer holidays of July and August for our daughter were great! because you can see that the % in range was much higher than the month of September. This supports the thesis that the best control is when you’re on vacation 😀
Days of the Week Review
And the last sheet of our Power BI (which is the last, it is not the least important) will allow us to make a very interesting analysis. Have you not considered many times when your child goes to school, why some days are better than others? And that many times you do not know why Monday turns out to be very low and Tuesday is not, and you suspect that it may be because he does gymnastics that day? Well, this graph will allow you to analyze separately each day of the week during the period of time you define. That is, if you want to analyze a month (4 weeks), you can analyze the grouped the values of the four Mondays of the month, the four Tuesdays, the four Wednesdays, etc. This will allow you to see if there are any patterns that have to do with a specific day of the week.
You will see that as in the previous sheets, in the upper left corner you will be able to define the time interval you want to analyze indicating the number of weeks to consider.
On the right side of the dashboard, you will see an analysis by day of the week of the time your child has been in range (green), has been high (yellow) or low (red).
And on the left side of the page, you will see a table where a detailed analysis of each day of the week is made, separating in each line the day of the week considered. As in the previous section, the values of % in range, % high, % low, etc. are indicated using a color code to mark the good values in green and the bad ones in red.
The Nightscout Power BI Reporting Team
When posting this article within the Facebook Nightscout Power BI Reporting Group, the creator & lead developer, Steve Hainey, and I connected about this article and its glucose dashboards. After messaging a little about these topics, we wanted to give some information about the development team and send a big thank to these key individuals who contributed to it over the last few years.
Steve has been a T1D diabetic since 18 years old, before the CGMs & insulin pumps were even available, and then an early user of the do-it-yourself (DIY) artificial pancreas Loop App with the Nightscout platform in 2017. He also has three decades of experience with supply chain planning systems & leading ERP enhancement and big-data analytics projects with large international companies. With this professional background and some experience with PBI, Steve started building the first Nightscout PBI Report for his glucose & Loop App parameter settings in 2018. Then in 2019, he created the Facebook Nightscout Power BI Reporting Group to share his Nightscout PBI Desktop file to help other T1Ds, which has grown to +1,500 individuals from 78 countries.
When Steve started developing the first PBI Report, he placed numerous posts within T1D Groups to get help building it out. Jonathan Chan, a non-diabetic engineer working with a wide range of technologies, volunteered to help him with it. He came to this & other communities with an interest in building software to help others. The early part of his career focused on diabetes & analytical software for T1Ds. Jonathan’s experience was a natural fit for this community, and his passion for helping others!
In March of this year, Steve decided to complete a rewrite of the Nightscout Power BI with its enhanced capabilities released since the first version of the report he shared. He again reached out to the different groups to get help from people with PBI coding expertise for a complete PBI rewrite.
The first one to help was Emmanuel Sánchez Tovar, a PBI data analyst diagnosed with T1D in June of 2021, which was a significant change in his life! The idea of controlling his blood sugar using a CGM was something he was glad to have access to utilize. However, most CGM companies don’t provide a straightforward or open solution to get the sensor data. Due to this and wanting a more detailed analysis of his Blood glucose levels as a data analyst by profession, he joined Steve in the team for this project.
Shortly after announcing the Nightscout Power BI reports rewrite, Mateusz Wachowiec (Matt) from Poland was the next one to join Steve & Emmanuel to create the current releases. Matt is a Business Intelligence developer who has worked with Power BI for five years. He was diagnosed with T1D in 2016, had his first contact with Nightscout in 2018, and started using the Loop App that same year. Currently, he is using AndroidAPS, a different T1D close loop app. In addition, he also has experience using FreeAPS and FreeAPS X. He contributes his technical skills and time helping people with diabetes as a member of a technical team of the Warsaw Foundation for Children with Diabetes. He even found a connection between diabetes within his Master’s studies in quantitative methods. His Master’s thesis was on using new technology for diabetes and how it can improve their quality of life. Matt joined the Nightscout Power BI Reporting Team, giving him another opportunity to use his professional knowledge to help others.
With Steve never having formal Power BI training, he wanted to take this opportunity to thank Emmanuel & Matt for helping him with the re-coding and tuning of these Nightscout Power BI Reports, which wouldn’t have happened without the two of them. Steve learned more about PBI configuration best practices from these two team members than from his business professional experiences within international Fortune 500 Companies!
Well. I hope I haven’t made you dizzy with so many tables, graphs, and numeric analyses! But believe me, they seem to be the best way to interpret the data and draw conclusions not based on personal guesstimates but the actual glucose data.
For me, the possibility of being able to analyze the data allows me to understand how my daughter is affected by both the carbohydrates she ingests every day and the insulin I put on her based on the defined guideline. And what is more important is to act on it to try to achieve the goal that she is always as long as possible in range, between the minimum value (70) and the maximum value (170).
That is why I think that the Nightscout Power BI Reporting tool is great for being able to make decisions based on data. Being able to configure it according to different periods of time, allows us to analyze the moment we are interested in doing it.
And without further ado, I want to say goodbye to all of you. I am waiting for your inactions & feedback on this and the following Articles of Digital Diabetes.
All the best,