Or concatenating if you will, but I don’t always remember that word which is probably why the solution didn’t come to me at once when I ran into the problem.
Concatenation is taking two or more values and chaining them, often with a delimiter. Like the first and last name of a person, or a zip code and the name of the area.
This demo data set is made up by looking at a persons grades in different classes they have taken. So if we want to show that information for all persons we would typically show it in a table like this.
Now what if we didn’t care to much about the grades for now, and wanted to show which classes a person had taken. We could of course just remove the Grade column and keep the table, but what if we only want to have one row per person? My gut feeling was playing with the matrix in Power BI, but that only shows the first value since this is a text column so it’s not what I am after at all.
The answer? Concatenation in a measure. Very often concatenation is done between two columns, but here I need it done on rows so the CONCATENATEX function is what we want. First I made this measure
Classes non distinct = CONCATENATEX(Example,Example[Class],”, “)
which gave this result
Looks ok, but the total row has duplicates in it and if we had grades for several years we would have it on each person as well if they had taken the same class several times and I only want the distinct values. Luckily we can then use the VALUES function to only get distinct values in a column so the measure now looks like this.
Looking back at projects I have been a part of in my work life there are some that have been successful right away, some that have needed more time to be successful, and some projects, well they haven’t really been all that successful looking back at them. Thinking about why some are successes right away and some never get there I believe that one of the main reasons that separates these projects is time. Not necessarily time to do actual development, but an actual time commitment from the business side of an organization, time to plan and time to actually think, reflect and make changes as the projects where moving along.
Tech Is Easy, People Are Not
I am of the believe that tech is easy, while people are hard. It is a lot faster to change an application or some code, than to change the behavior of an actual human being. And this is why I believe time is one of the most important things to early on in a project make into a priority. If we want to be successful the project needs time from actual business users. They need to be a part of making the specifications, and they need to help test, iterate, use and dream about what a solution can be and grow into. And they need time to adapt to whatever is being made as change can be scary and without enough time people might resist it. This may sound easy, but if someone from the business does not get a percentage of their time allocated specifically to use on a project they very quickly become “busy” and down prioritize helping out on a solution that is actually being created to help them in their job moving forward.
Reflection Is Under Appreciated
My favorite part of doing scrum is the retrospective. It is made so the scrum team can have time to sit down and reflect on what went well, what can be improved and look for things that they perhaps isn’t doing at all. I believe we all need to spend more time reflecting, but we need time to do it. In a stressful work day we too rarely sit down and think about how the last meeting we organized went. Did everyone understand why they where there? Was I clear enough in my communication? Did we all really agree on what to do when the meeting was over? If we reflect more on things I believe we also see behavior/patterns throughout our day that can be discussed the next time you reflect, either alone or together with others. We see the world in a wider view than just our own, hectic life one meeting, or task, at a time. Reflecting is all about having time to see where we can further improve.
Failing Is Necessary
We need time to fail. Failures can be though, both for an individual, a team and an organization, and it might feel like a disappointment and a waste of time if things doesn’t work out. But if you never fail, have you really pushed yourself to your limits? Have you really learned absolutely everything you could from an experience? If you fail, what can you learn from it? Do a mini retrospective and see what you and others think about it. Failing is hard, but we need time to do it in order to not be afraid of experimenting and see how far we can go.
Everyone Needs to Be Able to Talk to Everyone Involved
Communication takes a lot of time. Making sure all stakeholders are updated about the progress. Making sure everyone on the team knows about changes to the end product. Making sure a problem can be solved quickly by running a question through the correct person who knows the answer. The more people involved in a communication chain the longer it will take to get a message across, or an answer to a question. It can also turn into a game of whispers where the original question/answer gets lost in the chain. By allowing everyone to talk to everyone you basically get more time since you can cut directly to the source of an answer. Not every decision needs to be taken in a scheduled meeting. Five minutes by the coffee machine with the correct person might be all another person needs to keep working, but if we always have to scheduled a meeting two weeks in advance things will take time. And there is probably better stuff to use that time for.
Time is important. Both at work and in our private lives. And time management is really hard! My experience is that if you want to succeed make sure both you, and others involved, have enough dedicated time to use on what you are working on. If dedicated time is not given it is to easy to say that you are “too busy” to help out when others ask. So make time to plan, reflect and fail. Plan to have time to reflect and fail, and reflect and fail in order to further improve!
I’ve become quite obsessed with feedback over the last year and a half maybe. Mainly because I believe it is really hard to get useful feedback, and especially create a culture around giving feedback. For those who like podcasts I can highly recommend episode 1 of the podcast “Worklife” by Adam Grant which is called “How to love criticism”. The episode talks about a company called Bridgewater Associates who has taken this to the extreme. Now, I am not sure if every workplace can have, or should have, a culture like that, but I believe we still can learn a lot from it. And after listening to that episode myself I got myself a new obsession, feedback.
So far feedback is turning out to be as hard to specify as I expected, and even though I’ve read a lot I still have a million question about it. So I did an experiment and just wrote down a bunch of questions I could think of around feedback. Some I have clear thoughts about, others I have no idea. If you have any thoughts on them I’d love to hear it. Otherwise, maybe this list of questions will get you thinking as well. But let me ask you this; Can you remember the last time you got some really useful, maybe even unexpected, feedback?
Why is feedback so hard to get right?
What does good/bad feedback look like?
How do we measure quality of feedback over time?
What is the best way of asking for feedback?
Are people so good at self reflecting they don’t need it?
Are we too afraid to give feedback as it might affect how others think about us for it to work?
Are we too afraid of receiving bad news for feedback to work?
When is the best timing to give, or ask for, feedback?
Are some areas of expertise, or communities, better at working continuously with feedback?
How do we ensure that feedback is not a one-way dialog, and that feedback ends up being a power play from the givers perspective?
How do we build a culture for lowering the threshold for giving feedback?
Is it possible to receive actionable feedback from strangers?
Where would you even go if you wanted feedback on a personal project, or started out doing something new?
How do we teach how to receive feedback?
How can we learn our body not to go into a flight or flee position when receiving negative/constructive feedback?
Does more trust in a relation equal more feedback, or at least lowering the threshold for giving?
How can we make someone hungry for feedback?
Is a request for feedback really just a request for getting a recognition of something we do correct and want to hear it from others?
Do you every feel like you just want to write something? Doesn’t matter if someone reads it or not, just to get some thoughts down on a paper or a computer? I usually write for three reasons. Things ends up here when I feel like I have done something smart and want to have it more easily available the next time I end up with the same issue. Things end up in OneNote or a PowerPoint whenever it is something I want to share at work. But the third setting I usually write is different, that’s when I don’t manage to sleep because I am either irritated or to excited about something. Writing usually calms me down, and sleep comes more easily afterwards.
For some reason I wish I wrote more. It feels calming when I do, like now, and it doesn’t have to be to calm myself down because of irritation or excitement. Most of the time however, stuff ends up in my personal OneNote, in the “Draft” section. But why? I’ve never been much of a perfectionist, but when it comes to writing and putting stuff here, for some reason it stops in a draft pahse unless I really take the time to do the write-up. I think that’s a shame, not because what I write is super interesting, but because I clearly have done some work when I look back at the drafts. Too often it feels like what I am writing is not “new” enough. It has nothing to do with the fact it is not good enough, but I notice I find myself thinking “someone has already written about this”. I do hope to put more out, I think all of us has our own perspectives on stuff we write even if it is not ground breaking. It doesn’t have to be, and that is fine. By putting it out there for everyone to read, and not kept in the dark, it might inspire or teach someone else something.
As with everything we get better when we write, so by writing this maybe the next piece I write becomes just a little better, or at least the bar for finishing it and publishing it gets lower. Writing is fun and at some point I hope to be able to tick of all these things:
Write a children’s fairy tale. I’ve already drafted one called “The Snail Princess” as my daughter loves snails, worms and spiders.
Write more “kåsseri”, I have no idea what this is in English, and google translate didn’t help me, but it’s basically a more humouristic type of short essay. Again, I have a draft for this called “Analogization”.
Create a bad guy for a thriller. Doesn’t have to end up as a book or anything. I just really want to see if I am able to create a character like Moriarty from Sherlock Holmes. Let’s be honest, a good bad guys is very often more interesting than the good guy!
Write more here. I think I to often think this space has to be work related. I think it’s because it started out as that, but that doens’t mean that’s all it can be.
Time to get even more average, and put out more average content, mostly for me and if someone else finds it interesting that is purely a bonus.
Ever used shapes or images in Power BI and wanted a more flexible way of moving things forward and backwards on your canvas? Yeah, me to!
Under “View” there is a pane called “Selection Pane” and when checked you will see all the different elements on our page, and you can arrange them as you like where the ones on top of the list will be in the front of the elements below on your page. So if you use a shape for a background as me below, just pull it all the way to the bottom, and hepp! It is correctly placed as a background, and not covering the important stuff.
2018, another year where I got a new job, a year where my list of projects I’d like to do some time kept growing and a year where Twitter surprised me as a great discussion platform. To everyone I’ve talked to, everyone creating content I have seen, read or listened to and everyone that has read or heard anything I’ve done: Thank you for a great year!
New Job… Again…
When I graduated doing technical stuff was the best part of my job, digging into data and writing code and to this day I enjoy this a lot. However at some point I realized that what’s intriguing to me about the data and analytics domain is that it needs both the technical side, and the more human side where culture, change and organization comes in. Every job change I have done from my first one I have mentioned this, but when I started looking back earlier this year I realize that this has probably been taken as a positive trait in a developer, and not that I was interested in moving more towards the less technical side. I believe there is a huge advantage coming from the technical side, but I was never able to get a project where I could work a lot more on the non-technical side, and even though I have tried sometimes it comes down to the fact that a technical resource don’t always have the mandate to go as deep into the business side as I’d like. And in the end I found out it was time to move on, and search for something a bit different than what I have done so far.
After a lot of thinking and a lot of interviews I ended my career as a consultant and started in a new job on the 1st of October as Head of BI & Analytics in Sector Alarm Group. A position I can shape a bit like I want and since we are a small team, for now, I can still do technical stuff when needed or if I feel like my hands are getting to clean and needs some real data shuffling. So far it has been all about finding the baseline for where we are, and where we need to go, but I have high dreams of what we can to in 2019, and also as we move further into the future!
Talks and Presentations
I’ve always enjoyed doing talks and presentations about topics I am interested in. 2018 started out with a presentation for the Microsoft Data Platform User Group here in Oslo, and several other presentation has followed that both internally and externally. #SQLSatOslo is always a joy to attend and speak on, and I have never spoken as much about data visualization as I have done in 2018, both at projects with clients and internally in my previous employer. I still believe data visualization is one of the best ways of making data more human, and I will continue to spread the importance of it in my new job, and at external events where I can.
Working With Graduates
In 2018 I’ve been able to work quite a lot with graduates, and this is one thing I’m going to miss by not working as a consultant in a relatively big company. Seeing how hungry graduates are to learn and jump into new things is extremely inspiring. And even though I sometimes feel like I am doing absolutely nothing because they do incredible work I hope I have been able to provide them with a good understanding for data as a domain and a good foundation for their continued career. I hope the best for every graduate I have been able to work with this year!
As most people that know me know I love data visualization and Twitter is my go to source for this, #dataviz. Where I earlier only where looking for cool visualizations this year I have noticed something else about this community. They have some really high quality discussions on this forum where you can’t write more than 280 characters in one tweet! I am astound that some of the best discussions and feedback loops comes in this forum. Other forums, such as LinkedIn, to often becomes a place where everyone agrees on everything and it is all about trying to praise the poster and what they have done. The data visualization community can one day salute a great visualization, and the next day have an intense discussion where no one agrees, but are still responding in a way that shows resepct and it feels like they truly want to help out, and push both the poster and the data visualization field forward!
The biggest thing I know for 2019 is that we are expecting a new child in the start of February, and it is going to be great! Although I wonder how much we can have forgotten over the past 3,5 years about babies. At work it is time to stop talking about what we should do and start doing some actual work and deliver new, user friendly, great data products and platforms while building a business culture that is thirsty for data, and where data is an asset that everyone knows the importance of and uses every day in their work. High dreams? Hello! But we will start small both on the technical side and the business side to make sure they both can feed the other side with what they need.
Other than that I have hopes of crossing out more of the projects in my list of projects that I never get to do, I really wish the day had 27 hours because cutting sleep isn’t really an option. I’d like to do more custom data visualization and perhaps even mix it with 3D printing. I hope to write a lot more, both technical stuff here, but also more in the form of fiction. I’d like to learn how to play the guitar better, I bought a harmonica at an auction which I’d like to use more and I got a jaw harp for christmas I have to larn to play. And to top of the music part I am still working on turning datasets into music, not by using machine learning, but more as an art project. And the list goes on most likely long into 2020.
2018 has been a great year when I look back and here’s for 2019 to be ever better! And I hope you will be able to cross of some things on your list of things you’d like to do as well!
Have you ever been in a situation where you just know the next word to come out of a person’s mouth is the word “but”? In a meeting where someone says “I totally agree with you, but…” and then end up in a rant that shows they don’t agree with you at all? In a feedback situation where your manager starts out with “You’re doing a great job, but…”. This small word is often misused and we end up feeling confused about what the message of the person in front of us actually was. It is time to be more aware of when we use it, and to completely stop using it in feedback situations.
Cambridge Dictionary defines “but” as a word “used to introduce an added statement, usually something that is different from what you have said before”. “But” can be compared to a mental eraser that simply removes whatever you said before. What happens in a feedback situation, or a meeting, is that when we use the word “but” people start to build up their defenses so that they stop paying attention to what you are saying and might miss the most important points.
When I studied I also did improvisational theater, and after each show we had a feedback round. In these session we had some basic rules:
Everyone can give everyone feedback.
If you hear something for the first time forget about it, if it is the second or third time you hear you might want to concider doing something with it.
You are not allowed to seperate two sentences by the word but. Stop the first sentence, then start the second as a new one.
To this day I really like these simple rules and I try to use them for example when we have a retrospective in a project. It is hard to see yourself from all angles so everyone has to be able to provide feedback for you to grow. If you hear something for the first time it might be a one time mistake so you shouldn’t focus to hard on changing something right away if it won’t happen again. now, Why did we try not to use the word “but”? My main reason is that it often nullifies both sentences seperated by the word “but”. It makes us go from two potentially constructive feedbacks a person can work with, to no feedback they can, or want to, work with. In addition you can end up making yourself less trustwhorty in the future.
I’ve come across articles saying that you can swap swap out the word “but” with “and”. For example “you are good at x, but you’re bad at y” can become “you are good at x, and if you keep working at y, you’ll be even better”. Now in this case I don’t really mind, but they are already rephrasing the second part of the sentence so why can’t we just say part one, take a pause to let the receiver absorb what was said and then say part number two? A direct substitution of “but” with “and” is not recommended as you’ll have to think about how you say it in order to not make it sound like a “but”. Does “you are good at x, and you’re bad at y” really sound any better than if we used “but”?
During the rest of the day, or in your next meeting, think about how often you, or others say something like “yes, but…”. It is surprisingly often and if someone says it as a response to something you said think about what you feel when they say it. Do you feel respected? Understood? Do you feel good or bad? Try to make yourself more aware of when you use this little word and it might make you communcation towards others more clear. Maybe you can even feel an attitude shift in the people around you by just using this small word less.
We should strive to make our communication, and especially feedback, as clear as possible, so don’t let the word “but” hold you back!
A very normal calculation we do is to compare a value, for example sales, against how we did last year. The easiest way to do this is by using the SAMEPERIODLASTYEAR function to create a measure that look like this
As shown in the image above this works fine for a given date, but what if we want to look at how one week compared to last year? For retailers this is a very normal demand. ow can we compare monday in one week to monday in the same week last year? After all it is a big difference in how much sales is being done on a Saturday compared to a Sunday, the shop might even be closed on sunday. For this we need to create a calculated column to help us out. We are going to call it DWY (for DayWeekYear) and we can do it like this
DWY = WEEKDAY(DimDate[Date])*1000000
Note: For people not using American calender you may need to prep week number otherwise to get ISO week, for example in your date dimension. Then you can do the calculation. The important part is that is lines up with your definition of day in week, week number and year.
Here you can see that 17th of September 2017 is a Monday, but in 2018 it is a Tuesday so we are unable to compare the two. Now, the beauty here with our new DWY column is that if we subtract one from a DWY date this year we get the same day in the same week last year. So, DWY Last Year = DWY -1. Which is great! So we can use this to create a measure that compares day in week vs the same day in week last year.
This works great when we look at a specific date, but you will see that the total row for the new measure is the same as the last value, and not the sum, so we have to extend it a little bit to make it sum up all the days we are showing. To do this we will use the SUMX function to create this measure
There you have it. But can we do more? In general I don’t like having more than one measure saying the same thing. We are looking at sales for last year in both measures we have created, it is just that one is comparing with a specific date and one is comparing with day in week. I would like to just have one measure that is Sales Last Year and then we can say that if the user is filtering on a week or a weekday we will show them the day in week last year value and otherwise we will show them the specific date. Now, this might be on a case to case scenario, but if you keep both measures available to they users they will have to remember that they cannot use the Sales LY Week Total measure if they want to look at a specific date, they have to use the “normal” Sales LY measure. In my experience this is often a source for user errors that might be confusing so we can hide this complexity by combining the measure we have created.
Sales LY Total =
ISFILTERED(DimDate[Week]) || ISFILTERED(DimDate[DayInWeek])
;FactSales[Sales LY Week Total]
The finished result will behave as shown below. The same measure is used in both tables, but the one on the left is filtered on week while the one on the right is filtered by dates. If you have more ways of showing week or week day in your date dimension, like the week day name remember to include them in the IF statement to make them work as we want them to.
A while back Stephen Few argued that data can not be described as being beautiful. At that point in time I did not agree to this statement. I do think data can be beautiful, but as the term “smart data” is popping up more and more I find myself unable to use this term to describe data. Am I being a hypocrite? Can data be beautiful, but not smart?
To me data is one of the dumbest things out there, it just exists. Data on it’s own creates absolutely nothing, it wouldn’t even have existed if someone didn’t create it. So even though I find myself saying that data can be beautiful I don’t like the term smart data. Data can be structured, organized and formatted beautifully, but being smart? No!
By stating that data is smart we take away the credit from many people who works with data each day. Smart people, really smart people! They are the ones that deserves the credit for all the amazing things data can be used for, not the data itself. Do we call trees smart just because we can do amazing things with them? No, but the inventors of paper, housing and so on they were geniuses!
Give credit where credit is due. Data is lazy, it would lie on the couch all day if it could. The people that works with data on the other hand and gets the data up from the couch and creates amazing products and services with it, they do all the dirty work and should also get all the glory!
In a project we are using Talend to load a lot of data each night and we are experiencing randomly getting “Connection does not exists” error messages during our data load. This can happen at any time both during the connection phase, and so far we have been unable to see any real signs of why it is happening and when. In addition this leads connection reset often leads to our data being corrupt and unusable meaning we have to start all over. We have therefore set up an error handling when reading from this data source.
Setting up a try/catch in Talend
Create a context variable that we call continueLooping. This boolean will be used to end our loop when we reach our maximum number of attempts or the connection has been successful
Add a tJava where you initialize the variable to true
Then add a tLoop as a While and condition context.continueLooping
Now we add a tJavaflex where our try/catch block will be. Put the try block in the start code and the catch block in the end code. Mine look something like this. Feel free to add some logging in here as well so you can keep track of where the error is happening. in the main code I make the job sleep for a little while in order to give our connection some time to get back up.
Add a tJava with a “On component Ok” trigger on your database connection. Here set the continueLooping to false to stop the loop.
In the end it should look something like this:
Extending the error handling
In our case we are already looping our read by using a job above this one to read data one month at a time. The output of this job is large .csv files which we then upload to Azure blob storage in order to use Polybase to finally move the data into our SQL DWH data warehouse. Since we know we can loose connection in the middle of our read we need to clean up our .csv files before starting the loop all over for the month that we are reading. This is done by adding an If trigger on the tJavaflex where our trigger is to check which iteration we are in. If we are not in the first iteration of our loop something has gone wrong and we need to do some cleanup to make sure our data is correct in the end. We therefore remove all rows for the month we are supposed to read before we let the loop start over. Now, the only way I have been able to do this is by creating a copy of our existing file, filter out rows for current month and then write it back as the original file. In the end it looks like this:
Overall it seems to work very nicely when we are unable to trust that our data source will keep our connection open for the whole duration.