Voice of the DBA

Tag: data analysis

  • End of the Year Stats

    I track some data from my life, some purposefully, some randomly from various services. Here are some areas to look back at this year.

    Reading

    I read a lot. It’s an escape for me from a busy life and a good hobby. I track my books on Goodreads and tag the ones I finish with the year in which I finish them. As of a couple days before Christmas, I was at 117 in the 2021 tag. Quite a bt of growth in books read:

    • 2021 – 117
    • 2020 – 82
    • 2019 – 128
    • 2018 – 79

    Before this I wasn’t tagging.

    Fitness

    I try to work out regularly. A few items from 2021

    • 328 workouts
    • 118,736 kcal burned
    • 225:32 hours working out
    • 319 miles of distance. Unfortunately yoga and weightlifting don’t cover distance Winking smile

    Not a great year, but not bad. I was hoping to be above 365. I tend to do do things on weightlifting days, so I’ve missed too many days this year.

    Music

    I love listening to music. I’ve had lots of music players, and went with an iPhone a few times specifically because the music support was better. These days I am mostly on Spotify, which is a fantastic service for me. It’s in the Tesla, on my devices, and I’ve got stuff playing often.

    I love the wrap up from Spotify this year. A few stats:

    • 33,449 minutes listening
    • Top song – 3 Nights (36 listens) (also Old Town Road, California Dreaming, Who Says, and Short Skirt)
    • Top artists – John Mayer (2,243 minutes), with Beatles, Jay-Z, Notorius BIG and Stevie Wonder rounding out the list.
    • Top genre – Classic Rock (lots of guitar riffs I like here)

    Language

    • 30,043 XP (experience points)
    • Top 1% of learners
    • French and Japanese
    • 5978 minutes learning (99.6 hours)
    • 85 minutes on one day
    • 1074 words learned, probably more Japanese
    • 334 days when the stats came, 575 days on a streak. I used 2 streak savers in 2021, 3 in 2020, so not really all those days.
    • 258 days in the Diamond league (top league)

    I got this icon/image/prize:

    2021-12-06 09_44_49-YEAR IN REVIEW

    Travel

    With the pandemic still going on and not many events, I didn’t think I’d travel much, but I ended up with a good year.

    • 12 trips for me
    • 1 new country (Belgium)
    • 1 UK trip
    • 3 trips to Florida
    • 5 work trips
    • 48 trips according to Google

    The spread in the US:

    2021-12-20 15_41_25-Timeline

    We ended up with 4 or 5 trips in the living quarters, camping out in various places.

    20210727_202028

    Not bad, and hoping for more in 2022.

    I also renewed my United 1K status. With a few trips and some promotions from them, I ended up back at the top tier that can be earned. That’s good as I hope to get back to more travel in 2022 and would prefer to get upgrades across the ocean.

  • Enhancing Client Experiences with Data and Analysis

    I love sports. The idea of competing and testing yourself against others (and against yourself) is something I’ve enjoyed for most of my life. Often it hasn’t even been important who wins; it’s the experience of competing. I don’t compete much anymore, but I enjoy watching and coaching sports. Teaching a group of younger people to work, train, win, lose, and enjoy sports is very fulfilling.

    I take statistics when I coach, and I provide those to my athletes regularly. The data is helpful for them to understand their performance and how they compare to others. The numbers don’t drive my decisions, or theirs, but they do provide feedback for them about overall performance. Sometimes stats help me keep perspective; sometimes they remind me of something I’ve forgotten. Sometimes they help me realize a mistake is an outlier and not where I ought to focus.

    I ran across an article on how data is being used in more professional sports. This post isn’t about the numbers of the game, but rather the ways in which data enhance the experience for fans. From resource needs to marketing to predictions, data is important to success in large events. The data can also make the fan experience better.

    Most of us are data professionals, and we know the power of data. Many business people know that data can be helpful, after all, they usually give us a never-ending queue reports to build. I think many of these business professionals can struggle with thinking more widely about how to proactively use data in planning, preparation, and prediction. These are things that data professionals have been doing for years, before the current AI/ML fad. We often used past data to help us prepare for the future. I’m not against AI/ML, but I don’t think they are necessary for all predictions.

    Data professionals know that we can use data in many areas of life outside of sports to enhance software for the client experience. I know I enjoy seeing some of the technology advancements that add to the experience from software, and I appreciate those that provide excitement to an experience, as well as those that just ensure things run smoothly. I look forward to seeing what the future might bring as some of the incredibly creative people out there begin to use data and technology to try out new ideas.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Cleaning Up Stats Data for Volleyball

    One of the things I’ve done the last few years is collect statistics as a coach for the kids I work with. Helping them understand how they are performing in competition is important, and it’s a good way to see if we are actually improving play.

    I use paper and pencil to gather data, because it’s reliable and quick. Most of the apps I see try to prevent bad data by enforcing certain flows of data, but I find this often means a mis-press on a tablet screen results in lost data because the game moves too fast. The apps aren’t flexible, either, so it’s hard to grab practice data.

    Last year I collected data like this:

    2021-06-01 14_23_21-TeamStatsByGame.xlsx - Excel

    I’d total data and then drop it in the spreadsheet, with a formula to calculate percentages. I report this to athletes and parents, which worked well. However, I wanted to build some graphs, which this format doesn’t make easy to consume.

    Putting Data in a Table

    My kids and parents don’t have a database tool, so I need to give them raw data in Excel. That works well for all of them and they can read the data. This year, I decided to give them a similar “dashboard” of data for each competition. The format I chose looks like this:

    2021-06-01 14_30_42-Stats2021_15Select.xlsx - Excel

    Not the best format, but it is easy to print and discuss with players.

    For more extensive reporting across the season, however, I need to capture data in a different format.

    As a result, when I take data from my paper and enter it, I do so in a separate worksheet in the same file, but in a tabular format. Here’s a sample of one of my sheets. This one captures Serve data.

    2021-06-01 14_32_57-Stats2021_15Select.xlsx - Excel

    For a particular tournament, I use a formula to add up the data for each player. Since I store the data for players in the same order each time, I can easily create a formula for 1 player and then copy/paste that for other players. It’s a little manual work, but it goes quite quickly as I go through the data after a tournament.

    Next Steps

    The goal from here was to publish this data as an interactive report. I want to build a PowerBI report and then make it available, but that’s for another post.

  • Data Analysis Without a Server

    Most of us that subscribe to this site are data professionals, and we work with large amounts of data for our organizations that is usually stored on a server class system, with TB sized, high performance storage systems. Whether on premises, in the cloud, or another data center, our employers have made an investment to provide high quality data services for our clients. This large investment is often a big decision, and setting up a new system to handle lots of data as an experiment with data analysis can often seem to take ages.

    The R language has been popular for data analysis for years, though the data sets examined were often limited in size, usually because of workstation limitations. One of the reasons Microsoft added R services to the data platform was to move analysis closer to big stores of data and increase the ability of organizations to “operationalize” or deploy their analysis and models to a wider audience.

    Deciding when to make that investment can be tricky, but the more that someone can prove some value from a smaller experiment, the more likely it is that an organization might decide to move forward. Recently, I ran across an interesting article, where the author had analyzed a billion row dataset on a commodity laptop. In this case, a Macbook Pro costing US$4000, but that’s a pittance compared to deciding to invest in HDFS storage, a Big Data Cluster, or even a large cloud experiment.

    What caught my eye here is that the analysis tool used, OmniSciDB, was engineered to run on CPUs, not GPUs, and performed very well in analyzing the data. I haven’t found the time or set up the disk space to try and load the billion rows into a SQL Server columnstore index, but I’d be curious how that might perform on the same data. The queries run are fairly simple aggregations, and my guess is SQL Server would perform extremely well once the index was built. If someone else wants to try it and take notes, I’d love to read the experiment as an article on SQLServerCentral.

    It has become more and more likely that before we embark on any large project in an enterprise that we perform some sort of prototyping and development on a small system. I think that’s true whether we’re building a web app or setting up a data science experiment that might drive our business forward. I always enjoy reading when someone has run tried a large scale analysis experiment on a workstation, not a server, and I hope we continue to see more people doing this and sharing their results in the future.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.