Voice of the DBA

Tag: data analysis

Learning some DAX

I’ve not done much BI work in my career, other than simple aggregates in reports. I’ve wanted to tackle projects, but I’ve rarely had a chance to spend time in this area at work.

Recently I had a bit of time and decided to look at some Power BI courses. At Pluralsight, when searching Power BI, I got a learning path that showed a number of courses from different authors. In the intermediate track, the first course was from Eugene Meidinger.

I met Eugene last year at SQL Saturday Pittsburgh and decided to give his course on Getting Started with DAX Formulas in Power BI, Power Pivot, and SSAS. Not quite what I wanted, but since I was curious about DAX, I decided to give it a try.

Understanding DAX

The intro is interesting. Eugene makes a good case for why DAX is important in the Microsoft stack. I tend to agree as I see this being incorporated into many products, including Excel. I’ve felt somewhat out of touch in that I haven’t known much about it and how it works. Even when I flip through our Stairway series, I’m somewhat skimming and haven’t really understood what it means.

I think Eugene does a good job of trying to explain DAX as different than, but similar to Excel. Thinking of this as an expression language, rather than a query language, isn’t something I’ve considered. Maybe I’ve heard this, but it didn’t stick.

He also lets me know this isn’t easy, which is what I’ve thought. I’ve seen Bill Pearson and Reza Rad talk some DAX, and it wasn’t quite clear to me. It felt clear when they were speaking, but not later. I’m hoping Gene teaches me a bit more in my office rather than in a 1 hour session.

Going Through the Overview

I went through the first few modules, which give you an overview and basic understanding. Eugene tries to explain the differences between DAX and SQL, which is a good way to approach the problem. Many of us are familiar with SQL, so this is helpful.

The third module starts to get into data governance and where you use DAX with your data. This is interesting, and it’s a good look at what type of organization you deal with for data. How is your data managed, which is something that most companies don’t really have a good, or consistent, data governance strategy.

One good thing is that Eugene explains the different places where DAX is supported for queries. That’s good since I’ve been thinking of DAX as the Power BI language, but it’s not. It can be used in Excel with PowerPivot.

The fourth module is the one where we start to understand column storage and the value this gives us. Still no DAX, but it’s a good module for analysts and relation people.

In getting through 4 modules, no real technical stuff that’s helped me, but it’s given me a little better baseline.

March 16, 2020
The Data Request

Spotify implemented a way to get your data from your profile. This looks at the request and initial data download.

The Request

When you go there, you see a “Manage Your Data” section.

Below this is a “Download your data” section, with three steps. The first is a request to verify your identity. Clicking this will send an email to your registration email. I like the idea of verifying your identity somehow rather than just letting you get the data. Minor security hurdle, but still one.

After clicking on the email, step two lights up and shows you that it can take up to 30 days to get data. Similar to a GDPR request, I guess this sets some expectations if they get overloaded, but for now it’s annoying. We’ll see when the data comes.

Step 3 is the download link, that you can resend.

Overall, a good process to get what might be private data. It will be interesting to see what they keep on me and what songs and artists appear, both in popularity and unpopularity.

Getting the Link

About nine (9) days after sending my request, I woke up and found this in my email:

I clicked the link and went to the same manage data page shown in the first section, but before I could get the download, I saw this. Nice to see they are verifying access to get data.

When I verified my password, a file downloaded immediately. As expected, it’s zipped.

What’s in here? A number of JSON files, of various sizes. There are some items that are less interesting, like the plan and payments.

If I open the playlist.json file, I see this:

One cool thing here, is that this first playlist is one I created yesterday, the day before I received this link. Meaning, I think the notification was someone processing my request, not the time to get the data. The data was assembled with a process, that used data as of yesterday. I could be wrong, but I have a list of songs in a playlist.

Going on to StreamingHistory, which is where I thought I might see interesting data, I see this:

This looks like I only have a bit of history, not a lot. The first row is April 2019, the last row is July 2019, when I got the download. Disappointing, as I’d assume I’ll have a much different set of history this year than last year. I’ll have to load this into a database and try to decode it. That’s for another post.

It is nice to get some of this data, and I wonder what it looks like if I download it again in a week or two. Especially after another trip or two.

If I lose the data, I can go back and get the file again, at least for 30 days.

I’m glad that companies are starting to give us some data, at least for us data people. Certainly most people might just like a visualization of analysis of data, but allowing users to get their data means that *anyone*, or more *anyplace* can do this analysis for you. Opportunities that your data gatherer might not create.

If you use Spotify, I’d be curious what you think of your data. Feel free to write your post and leave me a comment on how you loaded and examined this data. I’ll do some follow-ups on this later.

July 26, 2019
We Don’t Have Perfect Information

I was discussing the PASS Summit with someone and they were wondering about building their schedule. Actually, they wanted to pick sessions, but see the choices in a calendar format, but the schedule wasn’t out. My suggestion was to just build the schedule and then sort out conflicts later.

A few people have mentioned over the years that they want to build a schedule and be ready for the event to maximize their experience and be efficient. I think that’s a common, normal, technical person thing to do. We’re Type-A, we like knowing and having a set schedule.

The problem is that we don’t have perfect information. Even if the descriptions and abstracts included perfect information about the agendas, what is covered, and to what depth, including demos, we’d still not necessarily assimilate and recognize all that data. We’d think a session on database design covered fourth normal form, even when the text said third normal, or we’d expect that an SSIS data load talk included something on CSVs when the presenter described the talk as being with flag text files.

We’re human, and that means we have flaws in how we deal with the world. This includes the ways in which we model and analyze data. While we can make mistakes in our analysis, we often may simplify our view of a problem to the point where our analysis is inherently flawed.

I try to remember this when I write reports from systems that others will use. I won’t have every piece of information that might affect a system, but I try to ensure I have the most important, or significant, data. At least, the data I (and the users) feel is significant. The important thing to remember is that out data is always incomplete, and it’s entirely possible that we have missed a valuable piece of data.

When that happens, we have to adapt and adjust our systems, just like our conference schedule. We’ll learn more across time and we can use that information to change our system. I know that my view of a conference like the PASS Summit today, or even a week before the event, will be different than what I know, and how I feel, at the event. I should have a plan, but be willing to flex as circumstances change.

Steve Jones

September 1, 2018
Super Nerds

I enjoy the game of baseball, playing as a kid and then for a decade as a 40 year old adult. I gave it up a few years ago, worried about the wear and tear of sporadic play. I’m happy now coaching kids and participating in individual sports for exercise, but I still love the game.

A few years back I read the book, Moneyball. The book is about the use of metrics and analysis in addition to the human scouting evaluation in the Oakland As organization. There was a movie made as well, and both are worth consuming. Since that time, other baseball teams have adopted some of the ideas, and I’ve been hearing that both basketball, NFL football, and other sports in the US are using more metrics. There’s even a Revisionist History episode that talks about football/soccer and the way that makes the most sense to improve your team.

Recently I ran across an article with a great headline: Jayson Werth rails against ‘super nerds’ that are ‘killing the game’. In the article, a player rants about the way in which data and statistics are being used to make decisions. Instead of allowing players to just play, analytics become a part of the decision to play a certain way. For example, bunting and stealing have dramatically declined, mainly do to the analytics that show these are lower percentage actions compared to other choices.

It’s interesting to hear players rant against the user of statistics. I completely get the annoyance at losing control of your choices, and certainly appreciate that the game becomes less exciting at times. However, I also know that players, and even coaches, may make emotional decisions, or base decisions on poor information. Most of us humans can’t remember all the tendencies and likelihoods. In a modern world where skill levels have dramatically increased in many ways, there are often better ways to build a strategy.

Data is valuable, and certainly can help in sports. It isn’t the end-all be-all, and it can be misleading when applied to individual humans. At the professional level, where more data is available, I think it makes sense to use data more as a significant part of your decisions, though not the only factors. For me, as a coach of younger kids where I have relatively little data, I still use the eye-test for most things, relying on data to double check my thoughts. The world moves fast, and it can be easy to forget how individual players have performed across an event, especially when I’m trying to manage the game as well. Data helps me remember how the day is going.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 21, 2018