A #dataOp ramblings

Adventures in Business Intelligence

PBIPT #41 - Solving Wordle with Power BI

Yesterday I had the pleasure to be back to in-person to give a small fun presentation about bending Power BI to help us solve Wordle puzzles. The content is in Portuguese but I will upload an English version during this week. Here’s the link on GitHub where you can download the file: https://github.com/fpvmorais/presentations If you have any questions please reach me. Have fun!

JavaException: Must have Java 8 or newer installed.

While creating a new machine to be the Integration Runtime for Purview and after I have installed the mandatory JRE that allows the connection to Snowflake I kept getting this error: Error: (3913) JavaException: Must have Java 8 or newer installed. This puzzled me because I had installed version 17. Started troubleshooting and followed this guide to check if the installation was correct. Everything seemed alright but the error was still there.

Python Azure Functions Tips

Here are some tips of the things I’ve learned while creating them. App Plan If you already have an Windows ‘App Service Plan’ you’ll need a different one for running Python. You can’t host Linux and Windows apps in the same resource group. If you have an existing resource group named AzureFunctionsQuickstart-rg with a Windows function app or web app, you must use a different resource group. Python version If you need to use ‘azure-identity’ you’ll need to use python 3.

Accessing local settings while unit testing Azure Functions

There’s a lot to chew while unit testing Azure Functions. I going to be quite liberal with the terminology because technically some of this will be in fact integration testing and not unit testing per se. Either way, Azure Functions load the local.settings.json on startup, creating several environment variables that then we can use in our code. In C# we would access them like so: tenantId = configRoot["TenantId"]; appId = configRoot["AppId"]; or in Python:

Dealing with orphanated deployment pipelines

Someone on my team was trying to delete a Power BI workspace but couldn’t. It said there was some deployment pipeline associated that prevented that action. The tricky part was that no one could see any pipeline. This lead me down the rabbit hole to find, that, if an account would be deleted from the active directory, and that same account was the only user of a pipeline that pipeline would turn an orphan.

Add multiple scans using the Purview API

As I explained in a previous post about Purview with the official Snowflake connector you need to create a scan per database/schema pair. In the customer where I was working there are a few hundred databases plus their schemas. Doing this by using the UI would mean that we will be wasting a lot of time clicking away and be prone to forget some database/schema pair. I’ve explored the documentation about the Scans - REST API and the rest of this post is just some PowerShell code to automate the creation of scans and their schedules in Purview.

Add user(s) to multiple Power BI workspaces quickly

A few days ago I had to add a new team member to more that 50 workspaces. I started dying inside just thinking of going to each workspace and add it using the portal ui. Here’s a little snippet that saved me a few hundred clicks: Login-PowerBI $workspaces = Get-PowerBIWorkSpace $user = "mySpecial.User@MyDomain.com" foreach($workspace in $workspaces) { Write-Host "Adding User to : " $workspace.Name try { Add-PowerBIWorkspaceUser -Id $workspace.Id -UserPrincipalName $user -AccessRight Member } catch { Write-Host "Message: [$($_.

Snowflake Connector for Purview

In the last few weeks I’ve been working with Purview doing some code to integrate Snowflake entities because there wasn’t an official connector. You can imagine my surprise when I’ve noticed last friday that there’s a new unannounced preview connector that does the work for us. PyApacheAtlas If you already read or watched something about the recommended way to create custom entities on Purview you have heard something about this library.

Deprecating Google Spreadsheets Custom Connector

There’s now an official connector to Google Spreadsheets on Power BI With the November 2021 Power BI release, came the official connector support for Google Spreadsheets from Microsoft. As such there’s no more need for my custom connector. The repository on GitHub is now archived. No more Relationship tag contains incorrect attribute. Line 2, position 86 During the last week I’ve tested it so see if it had the same limitations as mine had and I’m happy to report the XML errors when trying to import data from a Google Sheet with a pivot table don’t occur in the new one.

Advent of Code 2020 - Day 16

Today’s puzzle is called ‘Ticket Translation’. We have a bunch of fields in a language we do not understand and we need to figure out which is which. There’s a set of rules we collected in several ways to a document that will serve as the puzzle input. If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions. Part 1 Our input file is divided into three sections separated by a blank line: