It’s T-SQL Tuesday!!

T-SQL Tuesday #135

After making my first contribution to T-SQL Tuesday last month, I figured I couldn’t stay behind when it’s a topic I have loads to share on.

T-SQL Tuesday is the brainchild of Adam Machanic (Blog | Twitter). December 2009 was the first T-SQL Tuesday invitation that went out by Adam. It is a monthly blog party on the second Tuesday of each month. Currently, Steve Jones (Blog | Twitter) organises the event and maintains a website with all previous posts. Everyone is welcome to participate in this monthly blog post.

The Ask

This month’s T-SQL Tuesday is hosted by Mikey Bronowski( Blog | Twitter ). Mikey says: “Without tools, most of the work would be much harder to do or could not be done at all. Write a blog post about the most helpful and effective tools you use or know of.”

The original post is here.

My contribution

Which tools are essential to my working day?

When thinking about which tools I use most often, I quickly drew the conclusion that my job content changed drastically over the past few months. Yes, some spiky microscopic creature causing havoc with a global pandemic naturally contributes to this, but it’s also the nature of projects I’ve been taking on lately. These days, I spend the majority of my time in Microsoft Teams, hopping through tenants & accounts, and jumping from one call/conversation to the other. And yes, I’ve lost many moments of my life figuring out in what channel I was at exactly, and who I was supposed to be talking to.

Microsoft Teams, Edge and Outlook

Bluntly put, Teams has some room for improvement to allow users to easily change between tenants and accounts, and without wreaking havoc on the related Azure ADs. Right now, my best working solution is that I have a Microsoft Edge-profile setup for every customer, and other types of organisational accounts I have (ie. dataMinds.be). Every single one of these profiles is synced in Edge, and tied into my LastPass vault. Each of those profiles usually has a Teams Tenant coupled to it, that I install as an Edge Desktop Application. It works pretty smooth, for most of the use cases you might have. The main reason for switching over to the ‘fat client’ of Teams is when I need to take over screens in a call, or to go dig deep into settings windows. Marc Lelijveld has written an excellent write-up on how to set this up, although I didn’t go as far as creating custom icons for each single one.

One thing I’ve also started doing for some clients (the ones I work for on a very regular basis), is to set up e-mail forwarding from that customer account to my own work account, with an extra incoming Outlook rule to triage that to a separate folder immediately. It saves me a lot of time when I quickly need to check if something has been sent to that account, rather than jumping through the hoops of VPN, Multi-factor authentication, etc. Most clients remember to include my work account if they want a quick response from me, but it still helps to track down some things that I may otherwise miss.

The Office Suite

When I mentioned my job contents changed a lot, it also means that I find myself more in the ‘writing documents’ part of the job. Writing out assessment, audit, governance, presales, .. documents is an important part of what I do these days. So yes, my best friends are Word, Excel, PowerPoint and OneNote. Each has their specific use in what I need to do, and over time I’ve built a whole library of stuff I can reuse. OneNote has definitely grown on me, and it’s now an inevitable part of my process.

And do I do some ‘actual’ work?

Define ‘actual’ work 😉. I still get plenty of opportunities to build out some technical stuff, or help some colleagues/clients when they’re in a jam. For the sake of time, I’ll just limit myself to some of the Power BI things I do, and leave out the rest of the Microsoft Data Platform stack. For once, I’d like to publish a blog post under 3000 words 😊.

When External Tools were released (release blog) to the general audience in July 2020, a shock wave hit the BI landscape. Before, a lot of things were ‘kinda’ possible, but they mostly resulted into going on unsupported terrain, and potentially tricky results. All fun and games when you’re a more technical person trying to keep up with things, but definitely nothing to hand over to a client that is learning their first steps with Power BI.

The External Tools (and the Enhanced Metadata format enabling it) allow end users of Power BI Desktop to call on custom built applications, scripts, .. to augment their developer/designer experience. These days, there’s over 40 (I stopped counting) external tools available, each with their own use case and focal area. When showing off some of the capabilities to my clients, it amazes me to see how quickly they pick up these things, and start building out their own ways of working.

Depending on the client, their IT Compliancy rules, the business and technical requirements, my actual tool belt tends to vary. Not every IT organisation allows user to freely install an application, digitally signed or not, so this is definitely an important one to take into your conversations early on.
In a nutshell, my possible weapons of choice are:

• Power BI Desktop & SQL Server Management Studio
• Tabular Editor: My main go to when I need some more flexibility for ie. setting up calculation groups, content for documentation, advanced scripting, mass editing objects, .. This allows
  • PowerBI.tips has a 4-part series with Daniel Otykier, the main developer.
• DAX Studio: Whenever I need to write a more complex DAX measure, or go digging into how DAX measures are performing against my model, DAX Studio comes to my aid. Simply put, this has saved me many hours when figuring out things.
  • PowerBI.tips has a 2-part series with Darren Gosbell and Marco Russo (Part 1, Part 2)
  • SQLBI.com has a full playlist on DAX Studio and Vertipaq Analyzer
• Vertipaq Analyzer (Excel): Yes, I’m aware DAX Studio holds a version of Vertipaq Analyzer. Yet, the Excel version allows you to go a bit more into detail on encoding specifics etc.
  • SQLBI.com has a full playlist on DAX Studio and Vertipaq Analyzer
• ALM Toolkit: When working with Incremental Refresh or larger models, ALM Toolkit allows you to compare and publish metadata changes to the Power BI Service. Deploying datasets without needing to do a full refresh every single time, that’s where the magic is at ♥
  • PowerBI.tips held a webinar with Christian Wade on ALM Toolkit
• Power BI Helper: Reza Rad has put incredible effort into this tool,
• Power BI Cleaner: Lightweight solution by Imke Feldmann (The BIccountant) that checks which fields are used in your .pbix file.
• Power BI Sentinel: PBI Sentinel is a paid tool, but a downright impressive one. The sheer fact that they are able to capture a tremendous amount of information on how Power BI is used in your organisation can be a YUUUGE! timesaver. On top of that, they’re able to perform table and column level lineage for some data sources in Power BI. The fact you can’t even do this (yet) with the tools at hand by Microsoft, is a very impressive feat.
  • Reid Havens held a livestream with Alex Whittles in December 2020, going through some of the most important features.
• Power BI Field Finder v2: Lightweight solution by Stephanie Bruno to help analyze how a Power BI file is constructed, and how visualisations are used.
• DAX Beautifier: 1 click, 1 call to DAXFormatter.com to format every piece of DAX code you have in your model. Because yes, my eyes burn when I see poorly formatted code 😂

Which tools am I going to add to my toolbelt? (Soon, I promise ..)

After seeing some folks like Julie Koesmarno (twitter), Aaron Nelson (twitter) and Rob Sewell (twitter) show off Notebooks in Azure Data Studio, I’m keen in rewriting some of my scripts and processes into a fancy Jupyter Notebook. I’m convinced this will really help me in some of the assessments I do, and easily share my work and results. Other than that, it’s fancy toys I want to try out for myself.

Something I’m looking improve upon fairly soon is my remote whiteboarding setup. When in workshop meetings where I had actual people in the same room, I usually ended up at a whiteboard to quickly draw out some things. For me, this is still the hardest thing to adapt to in our remote way of working we have these days. I’m currently digging into some options for external drawing pads, to allow these to sync to the Teams Meeting I’m in at that time. Yes, my drawing skills will still be terrible, but it’ll be huge improvement of what I’m doing now.

I’ve been trying out some options for a to do list, and I’ve not found one that actually works for me. Currently I’m experimenting with Microsoft To Do, but it’s not catching on as I expected. For some odd reason, I keep ending back up at the pieces of paper that are always lying in front of me at my desk. The physical act of writing it down helps me to remember it best, which has been an issue with some of the tools I’ve tried. Who knows, I may even try to build out an actual kanban board on my wall as a next experiment ..

Conclusion

Writing out my train of thoughts made me realise I use plenty of different tools, as there’s still quite a few I haven’t touched upon. I’ll need to reflect on how I’m using most of them, and if it’s to their proposed strengths. If there’s option for improving my process, it’ll definitely be worth it. But, I’m looking forward to reading some other posts and see what other gems people are using.

Stay safe, take care!