Monthly Archives: October 2019

Where do people get stuck with Power BI?

I was recently talking to a potential customer about getting started in Power BI. A question they asked me was essentially “What are the difficult parts of Power BI, how do these engagements usually go?”. This was a really interesting question and for me a lot of customer engagements fall under two broad categories.

The lost and confused. A lot of potential customers are just starting to look into Power BI. They cans see what it can do, they understand what it is for, but they have questions. Generally these questions are all about governance and implementation. How do we deploy Power BI? How do we license Power BI? How do we secure it?

This is a reasonable challenge because Power BI has a lot of nooks and crannies. It has a bunch of ways to deploy it and a bunch of ways to license it, and it seems like that add a new one every 6 months. For these customers they generally just seem to be concerned about the unknown unknowns out there. Usually after that first hour, that seems to be all the help they need.

The out of control. These customers have made an investment in Power BI. These customers have started creating reports and seeing result. The problem here is scaling their reports. M and DAX have goofy learning curves. Both start out easy, but get incredibly difficult when you try to do more complex things. Maybe reading CSV files was easy, but now you need to work with a web api now. Maybe your report was fast when it was 60 MB. But now your measure or more complex and you have 250 MB of data and it is slow. These customers need help applying good data modeling and optimizing those models.

What convinced me that diversity is important?

T-SQL Tuesday Logo
For this month’s T-SQL Tuesday, we are to write about a time we changed our minds on something. For me, something  I think about often is that 12 years ago I didn’t value diversity. Today I do value diversity, and if we want to persuade others, we have to figure out what changed.

Where was I 12 years ago?

Twelve years ago, I was going to college and I experienced a bit of a culture shock. The county I’ve lived my entire life in, Beaver County, is 92.5% white and 6% black. The local college I went to, Penn State Beaver, was decidedly more diverse from a racial standpoint.

I remember quite clearly thinking at that point that there had only been 3 times in my life when I had seen that contrast: college, travel stations, and visiting Washington D.C. The rest of my life was in this monoculture.

And when I say that I didn’t value diversity at the time, it wasn’t that I was against it or had negative feelings. It was just that I didn’t see a lack of diversity as a problem. I had a vague awareness that my IT classes were heavily white and heavily male, but that didn’t register as an issue.

Looking back, I think I figured that if different people had different natural talents or natural interests, then what was the harm in that? Part of the reason I was in IT was the things I was bad at. I was bad at people and interacting with them.

Bound up in the previous paragraph were a whole host of assumptions and naïve ideas. The idea that life was a meritocracy. The idea that people freely chose their profession, without discrimination.

What is your ethical structure?

On subjects like this, we often talk past each other. Sometimes in bad faith, but often because we are starting from different ethical precepts. In college I took an Ethics in Computer Science course and learned of two big terms: Utilitarian Ethics and Deontological Ethics.

Essentially, are things right or wrong because of the consequences or because of something inherent to the action? I think one example was “Is it wrong to clean your toilet with the American flag?”. Many people would say yes, but they would have trouble pointing to how anyone was visibly harmed by the act.

I mention this because I think a number of people that are supporting diversity do so from a strongly deontological perspective. So if less than 50% of CEOs are women, then that is in its very nature unjust or unfair. Regardless of the specific consequences.

I think this is a valid and reasonable viewpoint. But I also don’t think that this alone would have changed my mind. Generally I was more swayed by empathy for individuals and seeing concrete negative consequences.

What changed my mind?

I think there were a number of things that all changed my mind in little bits and pieces.

Monoculture leads to failure

Something that planted the seeds early on for me were stories of how having a monoculture of life experiences lead to failure. How HP webcams couldn’t follow black people. How Apple stores had glass staircases. How websites often don’t support names with umlauts or other markings because of assumptions about how names work.

I also learned about groupthink and the often exaggerated Asch conformity experiments. So often these failures occurred either because everyone thought the same or had the same background. Or people were afraid to speak up and stand out.

Life is not a meritocracy

For all of my schooling, I did fairly well. And there was a fairly stable correlation between how good I was at a given task and the grades I got. It seemed to me like school was a meritocracy and I expected work to be the same. I was woefully wrong.

I got a job at Bayer Material Science after college and after 8 months I was fired. I deserved to be fired and wasn’t particularly good at my job. Still, as someone used to getting A’s this was pretty shocking to me.

I learned a number of things during that process. I learned that how well we excel in certain areas can depend on a number of secondary factors, like our ability to work with others, to communicate, etc.

I learned that my skills at being a student didn’t necessarily translate into being a good worker, and I could infer the reverse must be true. There were undoubtedly people who flunked out of school but where crushing it in the workplace.

Violence, discrimination and bias are common

As far as I’m aware, I’ve never been racially or sexually discriminated against. If I have then the person did a terrible job of it, because I didn’t notice. Every police ticket I’ve gotten has been valid. I rarely have to worry about my safety when walking alone at night.

Again, in college I had some vague awareness of this. I understood the idea that my female friends might appreciate someone walking them to their car when it is dark. But I didn’t appreciate just how constant it was for some people. Heck, if I’m being honest, the #MeToo movement was shocking for me to see how many people close to me had been sexually assaulted.

It was helpful too to realize that bias can be invisible, implicit or even well-intentioned. I remember reading about implicit bias in a book and being frustrated about the implicit bias I had in regards to African Americans. I kept failing the test.

I remember meeting an internal salesperson at my last job and saying “Oh so you work in staffing?”, because I had this idea that all sales people were pushy and that women had better people skills, so clearly this person must be a staffing salesperson, not hardware or IT Services. That was 5 years ago and I still feel like an ass to this day.

Representation matters

So I’m going to talk about a type of diversity that may sound strange, but is very meaningful to me. I’m a Type 1 diabetic. And when I was diagnosed, in many ways I felt like my life was over. There were so many things I wasn’t going to be able to do. For the entirety of my last job, I told very few people because I feared I would get fired.

But one of the best things I did was subscribe to Diabetes Forecast magazine. Because in every issue they had a story of someone with diabetes doing something badass. Like racing, or mountain climbing or even winning a beauty pageant. These people weren’t letting diabetes stop them from living their lives.

For this same reason, Scott Hanselman is a hero of mine. He is very openly a diabetic and will present to hundreds of people without letting his diabetes stop him. If he has to check his blood sugar on stage or drink some orange juice, then so be it.

I say all of this to say that representation matters. My life was impacted because I saw myself in successful people. I saw myself in others.

It doesn’t take a colossal leap of empathy to imagine how I might feel if roles were reversed, if all the speakers at a tech conference were female or non-white. If that happened enough, I would quickly internalize the idea that I would never succeed, that I would never make it, that I would never get up on that stage.

If you optimize for individuals, you optimize for assholes

When I see a backlash against people trying to re-balance the scales, a lot of that backlash makes perfect sense under a certain set of assumptions or axioms.

If you believe that 1) the world is or should be a meritocracy and 2) we have an objective way to measure “skill” and 3) we should focus solely on the merits of the individual, then it’s reasonable to conclude that picking anyone but the “best” for a job is an injustice.

And while I think I believed all of those things in college, given my experiences since then, I no longer believe those things.

I’ve learned that often people fail for reasons completely outside of their control. I’ve learned that technical prowess is a minimal part of my job, and there are a plethora of other factors that make me good at my job. I’ve learned that when you narrowly look only at the individual, there are negative consequences both inside the team and outside of the team.

When you optimize for individuals, you optimize for assholes. We’ve all heard the story of person X who delivers results but is inherently toxic and drives others away. Throwing other people under the bus can be a great way to get results in the short term. We have to look beyond the individual.

I think any person in a place of power such as a manager or conference organizer, needs to look not just at the talent or popularity of the individuals, but the mix as a whole and the benefits diversity brings. We can disagree on to what degree, but it needs to be done.

Summary

I won’t pretend I have all the answers here. I still haven’t figured out when being pro-diversity swerves into being pro-tokenism, for example. I haven’t figured out that line where in trying to promote a group you accidently reduce them to a name, an identifier, a statistic.

But I can say that these are the things that have helped me value and appreciate diversity. And I hope we can all promote it, even if in small ways.

Query folding workaround for Azure Devops and Power BI

Query folding is one of the most powerful tools in Power Query and Power BI. It is the automatic process of pushing down filters and other transformations back to the data source. This can dramatically improve performance for your queries.

Unfortunately, OData is not guaranteed to support query folding. According to the Power BI documentation on incremental refresh.

Most data sources that support SQL queries support query folding. However, data sources like flat files, blobs, web, and OData feeds typically do not. In cases where the filter is not supported by the datasource back-end, it cannot be pushed down. In such cases, the mashup engine compensates and applies the filter locally, which may require retrieving the full dataset from the data source.

I recently did some tests on this for the OData source for Azure Devops. When I tested with the sample Northwind database, query folding was working. I was able to see with Fiddler that my date filter was getting pushed back down.

clip_image001

However, when I tried to the same with Azure Devops, none of my filters where getting pushed down to the source. As a workaround, I was able to put my filters in the URL. So to filter based on date, I used the following url:
https://analytics.dev.azure.com/eugene1234/TestingOdata/_odata/v2.0/WorkItems?$select=WorkItemId,WorkItemType,Title,State,ChangedDate&$filter=ChangedDate%20gt%202018-12-30T23:59:59.99Z

clip_image001[5]

In this case, I was manually specifying the date filter in the URL. But it should be possible to use M code to dynamically generate the URL. Another option might be to create a custom data connector for oData that supports query folding.