In a word: sweat! Day 2 started with a long run between buildings when I realized that I was late for my first assignment of the day. I felt like a salmon swimming upstream because I was running against a human tide. Weaving and dodging, bypassing escalators for stairs, and other tricks worthy of a pro football player (well not really, but a girl can pretend!), I arrived 5 minutes late, in a lather. Fortunately my fellow MCT was very gracious. Needless to say, I will be there ultra-early on Day 3.It is just too dang hot in Atlanta to run, even indoors. (I am pining for the cool weather in Chicago.)
Today I concentrated on Excel, Power BI, and Power Pivot. I had a chance to get face time with the data visualization gurus on the show floor. I had a personalized, hands on look at Power Pivot and its’ links with Excel. So here are some things I learned.
1) Despite the name “Power Pivot”, this add-on program does not have any meaningful relationship to pivot tables in Excel. Using Power Pivot starts you down the path of creating your own ‘relational’ databases out of existing data in Excel. This self-created database is called a “data model”.
2) “Power BI” is the next step on that progression. “BI” stands for Business Intelligence. Using Power BI allows you to create some wonderful data visualizations and dashboards using data that you have gathered and merged into a “data model”.
So now, you are thinking–wow, province of geeks and nerds. Not for me! But hang on a minute. Let me sketch a scenario in which you could benefit from knowing how to use Power Pivot and Power BI. Imagine if you will that you are an individual with a compromised immune system. You need to be careful where and what you eat, how much exposure you have to germs, and generally live a more germ free life than most of us. You are preparing to move, and you want to pre-qualify your future environment as much as possible. So you want to check for a few things: prevalence of flu outbreaks, rates of allergy symptoms, and prevalence of non-vaccination rates.
Where to start? Lots and lots of data is publicly available. In our example, you could get data on vaccinations on websites like that of the Center for Disease Control, and other public health sites. In my theoretical scenario, you could download as many data files (in a comma separated value (csv) format) as you think are relevant. This is already a huge difference: you do not have to have access to a specific database, in which someone has collected exactly the data that you are interested in or need.
Once you have collected the data sets (fancy word for an Excel workbook) that you need, you are ready to combine them. The key to success here is finding a common field (or fields) between all the various data sets you have collected. In my theoretical example, a common field would be zip code–remember, we are evaluating a new place to live. The need to have a common field makes sense if you think about it. If you get a bunch of people together in a room, they will mix and mingle more readily if they have something in common–maybe they all went to the same school.
Using Power Pivot, or better yet, Power BI, you will be able to connect the various data sets you have collected and create the relationships between the various files. Continuing with our example, you can tell Power Pivot or Power BI that the file on vaccinations from the CDC and the file on flu incidence have a zip code field in common. Power BI (or Power Pivot) will then act as if all the data in both of these files has been combined into one workbook. Now you can see if there are any patterns or commonalities between incidences of flu and non-vaccination patterns. You can create amazing graphs and other visualizations that will let you ‘see’ your data in a whole new way. For example, you could create a map that shows areas with higher flu incidence as ‘hot spots’.
The day is going to come when we all will have access to an incredible amount of data. Think of wearables and all the data that they will generate. Think of the data that your FitBit is generating already! To make use of this avalanche of data, you will also need to master the tools that will enable you to use it. Start with Power Pivot and Power BI. If you are already using Excel and pivot tables, you have a head start.
