Archive for category T-SQL Tuesday

T-SQL Tuesday #137 – Jupyter Notebooks for the win?

It’s T-SQL Tuesday!!

T-SQL Tuesday #134

T-SQL Tuesday #137

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 Steve Jones (Blog | Twitter). Steve says:

“For this month’s T-SQL Tuesday, I want you to write about how you have used, or would like to use, a Jupyter notebook. This seemed to be exciting for many people at first, but I haven’t seen a lot of uptake from users in general. So I’m curious if you are using them.”

The original post is here.

My contribution

Jupyter Notebooks and me

In my write-up for T-SQL Tuesday #135, I wrote the following:

“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.”

By now, I actually did start on reforming some of my scripts into Jupyter Notebooks, with the emphasis on using them in an explanatory context. Maybe even use it an x step plan to execute certain tasks that can not be automated against the APIs. Maybe even turn them into guidebooks for clients to start working on some processes of their own.

I have to admit, I’m liking them a lot so far. My plan is to make this into a series of blog posts, and a community session to present. But, I’m having issues finding time and motivation to stride forward on this.

What kept me from actually getting started with this was the initial step and learning curve I had to take to rewrite my scripts (on top of my natural talent for procrastination). But then, we had Aaron Nelson (twitter) over for a user group session at dataMinds.be, on how he uses PowerShell (and Jupyter Notebooks) to aid in some of the processes. I voiced my intent on getting started, and Aaron graciously replied to me how easy it actually is to rewrite an existing script into a Jupyter Notebook running PowerShell. The only thing I ran into, is that I had some issues with the way I use comments, and how they’re transformed into Markdown. But, nothing I wasn’t able to solve easily with the help of my trusty friend Mr. Bing.

 

Extracting the Power BI Activity Log with a Jupyter Notebook

Right now, the only Jupyter Notebook I have ready destined for public eyes, is one to get started with extracting the Power BI Activity Log, and storing them into a .csv file on a location of your choice. I’m convinced every Power BI Admin should set up extraction of the Activity Log as soon as possible, to be able to backtrack to historical usage and analysis. This log only returns data for the past 30 days, so it’s not all that long.

Why did I opt for PowerShell and .csv files, when there’s other solutions out there like using Azure Data Factory as Just Blindbaek (blog post) did? I have a mix of clients scheduling tasks on-premises, or through Azure Automation with Azure Functions. The solution I came up with, had to be easily changeable in between the different environments without having to rewrite the entire thing.

This scenario is written in 2 flavours, being a regular and simple version. The simple version doesn’t have any of the frills added to it, and is purely destined for explaining the happy path that can occur with this. The version I use myself is the regular one, and this comes with some logging and simple error handling, to counter some of the scenarios I ran into when testing.

You can find the notebooks on my GitHub repo, and can easily get started with this. All you need is a user that has Power BI Service Admin privileges, and you’re good to go!

What notebooks do I have coming up?

The notebook I’m currently rewriting is to bulid up a Power BI Metadata inventory of your most commonly used artefacts, through the Read-only Admin APIs. In addition, I’m working on assembling a lineage view of your artefact through the scanning APIs that were released when the Azure Purview solution came into preview.

I have some more things brewing for on-premises Enterprise Data Gateways, and using the Graph API for licenses, group memberships, .. Finding the time for those is something I’m really struggling with right now.

Is there anything else brewing for Power BI and Jupyter Notebooks?

The Power Platform 2020 Release Wave 2 stated Embedding of Power BI Reports in Jupyter Notebooks was coming to GA in March 2021. I think it got moved back, but I’m hoping to see it come to life soon. Purely for Data Analyst purposes, there’s some cool things that can be done with this 😃.

“Scientists, journalists, researchers, developers, and other Jupyter notebook users will be able to embed Power BI reports, and then use Power BI features to improve their data storytelling. Once embedded, adding Power BI bookmarks will make it possible to show different views of the same report and order those views to facilitate data storytelling.”

Conclusion

This T-SQL Tuesday was exactly the kick I needed to start finalising some of the things that had been brewing for a longer time, and came at exactly the right time! I’m absolutely convinced Jupyter Notebooks have a place for explaining some of the more practical things in Power BI Governance, because of the capabilities of adding virtually any object in Markdown. The initial learning curve I feared, wasn’t that hard at all, and it actually made me realise these things should be used more often.

That said, I hope you enjoyed my ramblings.

Stay safe, take care!

, , , ,

2 Comments

T-SQL Tuesday #135 – My Tools for the Trade

It’s T-SQL Tuesday!!

T-SQL Tuesday #134

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.
  • 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!

3 Comments

T-SQL Tuesday #134 – Give me a break!

It’s T-SQL Tuesday!!

T-SQL Tuesday #134

T-SQL Tuesday #134

This is actually the first time I’m contributing to T-SQL Tuesday, after having read many of the entries in the years before.

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 James McGillivray ( Blog | Twitter ). James wants to know how we’re managing to give ourselves some breaks, to keep ourselves from going even more bonkers.

The original post is here.

What do you do to take a break when you’re stuck at home?

Arguably so, I’ve always been terrible at setting aside time for breaks when working on my own. When working at a customer site, or in the office, things flowed a bit more naturally when grabbing a coffee, and having a chat. The reality of the past few months has been that I’ve been stuck behind my desk for hours on end, mostly being dragged into numerous Teams Meetings or Zoom Calls. I quickly realised that I needed to get this under control, to be able to last.

These days, my break times are mostly consumed by the doggo of the house, Pixie. She’s a nearly 2 years old Briard, who have a heritage as French shepherd dogs. Long story short, these dogs are incredibly active and fun to have around. Most days, I take her for a walk around the block before getting started. Then, during one of my coffee breaks, I go out in the garden and kick around some of the Jolly Balls I bought for her. At times like these, I can tell that she’s got some frustrations of her own to kill as well.

Playing with Pixie in the garden

Playing with Pixie in the garden

But in the weekends, when I have some more time on my hands, we usually go out for some bigger walks of up to 10-12 in the woods near where I live. This gives me some more time to clear my head, and gives the doggo some direly needed attention. Especially with the weather being a bit colder and some more rain, it’s mostly her diving head first into the first puddle she sees, and then continuing to do so for the rest of the walk. All good fun, but a long haired dog takes a looooong time to dry off :).

Walking the dog at Drieboomkesberg

Walking the dog at Drieboomkesberg

Pixie refusing to come out of a puddle

Pixie discovering some local water

Then, at evening times, I try to switch between doing some reading or studying for things I’m working on, or simply blowing up stuff in a video game. I’m keeping my sh*t together, but I’m frantically counting down the days for when I can go back to my regular activities to get some peace of mind.

In normal times, I’d have a Shin Kyokushin Karate training 2 – 3 times a week, depending on my schedule for that week. It’s a full contact sport, which means it’s physically demanding and exhausting, and I’ve never ever even had as much as a lost thought on stuff I was working on.
Ahhh, to be able to hit some people again 😃

If money was no issue, what would be your bucket list vacation?

I’d have to split it into two, and I’d be torn if I ever had to choose between those two.

I’ve always wanted to go to Japan to visit some of the heritage sites and the Honbu Dojo. Preferably in the same time span as the World Championship to be able to witness the insane atmosphere in the Tokyo Dome with 15000 spectators watching some of the greatest kumite matches our sport has to offer. Followed by a tour of some cities like Kyoto, Nagasaki, .. to make this a splendid trip with some of my karate friends.

Then, competely on the other end of the spectrum, and I’m doubtful that I’ll ever be able to do this. For years, we’ve always got together with some of my Chiro friends (compare it to Boy Scouts, to a certain extent), and do a weekend where we pull the same idiotic stunts we used to do when we were younger. We’re all a bit older now, and many of us settling in to work/life situations. Getting together isn’t as easy as it used to be. For that reason, I’d be thrilled if we were able to get away for a week or so, and go camping with the boys only. Pure nostalgia, to some of the most fond memories I have.

Conclusion

I’m managing to keep things afloat, but I’m ready for things to slowly get back to normal. Especially because I’ve not been able to go the practice 2-3 times a week, I’ve felt some built up frustrations that needed to get out.
Here’s to hoping we can all reconvene soon, and get ready for some more epic stories!

Stay safe, take care!

1 Comment