Archive for April 13th, 2021
It’s T-SQL Tuesday!!
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.
“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.
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.
I stand corrected. Apparently the illustrious @jamesoneill has simplified the situation 😃
Doug pointed out that it's now as easy this:
dir *.ps1 | ConvertTo-PowerShellNoteBook
— Aaron Nelson #PowerBI 👨💻 👨🔬 📊 (@SQLvariant) January 22, 2021
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.”
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!