Since 2013, I have been experimenting with family data science – or the process of drawing deeper understanding and insights to help my wife and daughters grow, stay healthy and be happy.
I generate and maintain household datasets that record key aspects of my family’s day-to-day lives. I use these datasets to drive my family data science experiments and they can be grouped in one of two ways.
The first group is mostly transactional in nature. Credit card purchases, blood test results or dance practice attendance are just a few examples of datasets in this group.
The second group is mostly analytical in nature. These datasets aggregate, or count the different aspects recorded in the day-to-day transactional events.
During the first few years of running my family data science experiments, I happily collected and aggregated this information. For example, I could tell you the number of hours my daughters spent at dance practice,how many times my wife and oldest daughter suffered from bronchitis during winter months, or the months during which I sent the most work-related emails – no major insights but clarity nonetheless.
These analytics can be amusing for a little while, but as dad and husband, I wanted discover and answers to more probing questions. I wanted to know what happens to my daughter’s respiratory issues when we cut lactose for a period of six months? How do my wife’s sleep patterns improve when she goes swimming on a regular basis? Does my standing heart rate decrease during weeks of healthy eating and intense exercise?
In recent months, I’ve been using Microsoft Excel 2016, and its powerful Get and Transform features, to automatically connect to my household datasets in the Microsoft Azure cloud. Once in Excel, I map these datasets to a Power Pivot data model. Preparing the data this way gives me the freedom to discover and correlate seemingly independent observations. For example, how weeks with increased workout activity improve the family’s sleep patterns and mood.
Excel’s potential for this kind of analysis is great. Unfortunately the Get and Transform and PowerPivot features are clouded by the traditional spreadsheet features. When they coexist in one monolithic software package, the result is slow processing an instability. Excel crashed on me quite a number of times as it connected and imported 10MB from fourteen datasets in the Azure cloud. The process of analyzing and refreshing the data model was simply too slow for the type of experimentation I was trying to perform.
This is where Power BI desktop comes to the rescue. Power BI repackages Excel’s Get and Transform and PowerPivot features into an intuitive report authoring environment. Where Excel buries the discovery and visualization features among its traditional spreadsheet capabilities, Power BI brings them front and center.
The end result is that Power BI lets you quickly visualize your data in numerous and diverse ways. This is a much needed improvement over similar approaches using Excel 2016.